CODEWITHSUNDEEP
performancenhibernatecaching
Mauro Destro
Mauro Destro

Reputation: 766

Huge inserts and system slow down with NH3

i have to insert a child on an entity picked up from db and save the updated entity.

No. of entities: 10000 No. of childs: 17000 (one or more child per entity, not 17000 for each entity..)

I have tried with query cache but after first 1000 queries that runs in 15ms each, times doubles every 1000 inserts.

I have tried getting all entities and use query cache but getting 10000 also from cache is terribly slow.

How can I solve it? I have to get entity quering by Name, attach the new child and save it.

EDIT

I use one Session for all operations. I basically have a file as input with pairs of "Entity Name" | "Child Name"

In my UI I create a List of operation that's AddChildTo(entityName,childName). In my service behind WCF I enumerate all operations and for each I retrieve the entity based on entityName, create a new Child based on childName, add the child to entity and save entity.

Transaction contains all operations to do because if one fail all must be deleted.

I create a Stopwatch and take the time to do 1000 operations. Time doubles every 1000 operations.

Creating a Stopwatch inside method that do only the query to retrieve the entity I realize that the query is the part that increase the final grand total of elapsed time.

Those are the queries executed for each child:

INSERT INTO Child
       (Codice, EntityId, CabinaUid, ComuneUid, Nota1, Nota2, Nome,
        Descrizione, Master, ColoreMaster, ValidFrom,ValidUntil,
        Uid, EntityType)
VALUES     ('IT022E00355269' /* @p0 */,
        9333 /* @p1 */,
        '00000000-0000-0000-0000-000000000000' /* @p2 */,
        '00000000-0000-0000-0000-000000000000' /* @p3 */,
        NULL /* @p4 */,
        NULL /* @p5 */,
        'IT022E00355269' /* @p6 */,
        'IT022E00355269' /* @p7 */,
        0 /* @p8 */,
        0 /* @p9 */,
        '01/01/0001 00:00:00' /* @p10 */,
        NULL /* @p11 */,
        '5c40f801-489d-459f-81ca-0cb53655e50f' /* @p12 */,
        'Child')

select SCOPE_IDENTITY()

and

SELECT this_.Id           as Id14_0_,
       this_.Codice       as Codice14_0_,
       this_.Interruttore as Interrut4_14_0_,
       this_.CabinaUid    as CabinaUid14_0_,
       this_.ComuneUid    as ComuneUid14_0_,
       this_.Nota1        as Nota7_14_0_,
       this_.Nota2        as Nota8_14_0_,
       this_.Nome         as Nome14_0_,
       this_.Descrizione  as Descriz10_14_0_,
       this_.Master       as Master14_0_,
       this_.ColoreMaster as ColoreM12_14_0_,
       this_.ValidFrom    as ValidFrom14_0_,
       this_.ValidUntil   as ValidUntil14_0_,
       this_.Uid          as Uid14_0_
FROM   Entity this_
WHERE  this_.Nome = 186034 /* @p0 */
       and ((this_.ValidUntil is null 
              or this_.ValidUntil > '01/01/0001 00:00:00' /* @p1 */)
            and this_.ValidFrom <= '01/01/0001 00:00:00' /* @p2 */)

Upvotes: 1

Views: 176

Answers (2)

Diego Mijelshon
Diego Mijelshon

Reputation: 52745

There's not enough information regarding how you are doing it, but here are a few general tips for working with large datasets in NH:

  • If possible, split the work in multiple logical units of work (each one is a session)
  • Always change the default Flush Mode to something other than Auto
  • Consider using Stateless Sessions and HQL DML for bulk scenarios
  • Enable ADO.NET batching in databases that support it
  • Instead of doing 10000 queries for one entity each, use queries that return a bigger part of the dataset
  • Use a profiler to determine what your bottlenecks are

I don't think the query cache is going to help you (maybe I didn't understand your use case)

Upvotes: 0

Alex Burtsev
Alex Burtsev

Reputation: 12678

To diagnose performance problems:

  • First check the SQL query that get executed. (post it here)
  • Make sure you use inverse on one side of parent-child relationships, take a look at my post NHibernate parent-childs save redundant sql update executed
  • Execute queries in one session (you don't need to use some special cache if all queries are in one Isession)
  • Use Transaction, some db's like SQLite for example do insert | update operations faster in transaction.

Upvotes: 0

Related Questions