0

I have a WPF application that uses NHibernate. The startup of the application, or any application that leverages this domain, is extremely slow. It takes ~30 seconds to validate the database schema, but I am not sure why. The database has ~230 tables. Below is a screen shot from dotTrace that shows where the major bottle necks are. Building the configuration was slow, but I have followed instructions here and elsewhere to cache the configuration and read it from a file. That has helped quite a bit. But now it seems there is some issue when it needs to talk to the database. Any insight would be greatly appreciated. Also, please let me know if additional information is necessary to help diagnose the problem.

dotTrace profile

enter image description here

TyCobb
  • 8,909
  • 1
  • 33
  • 53
Sidney Leake
  • 36
  • 2
  • Can you see if it sends dummy queries to get the actual database table schemas? I don't remember NHibernate doing this, perhaps another setting? The trace is just about 30ms, not 30 seconds. – Erik Hart Jan 17 '17 at 22:05
  • @ErikHart 33,000ms is 33 seconds. – TyCobb Jan 17 '17 at 22:07
  • The trace looks to be ~33k ms; wouldn't that be around 30 seconds? – Sidney Leake Jan 17 '17 at 22:08
  • OK, I understand, comma is for 1000, have been using another profiler recently. But the grey part (ADO.NET/SQL Server SqlConnection.GetSchema()) seems to make a schema query. When I checked those queries some years time ago, it was something like SELECT * FROM table WHERE 1=0. Can you see what your network latency to the database is? – Erik Hart Jan 17 '17 at 22:16
  • It looks like our latency is ~15ms – Sidney Leake Jan 17 '17 at 22:29
  • Possible duplicate of [Optimizing nhibernate session factory, startup time of webApp really slow](http://stackoverflow.com/questions/10766662/optimizing-nhibernate-session-factory-startup-time-of-webapp-really-slow) – Najera Jan 19 '17 at 13:49
  • That has to do with poor performance with regards to loading the configuration. This seems more to do with how the database schema is validated. – Sidney Leake Jan 19 '17 at 15:44

1 Answers1

0

It seems to use hbb2ddl, auto schema generation, validating the schema with schema queries for each table. Perhaps also doing other operations, not visible in the trace. Validation is probably the default setting, as said, for example here:

https://weblogs.asp.net/ricardoperes/nhibernate-pitfalls-schema-auto-action

I did not have this configured when working with NHibernate; I could have a different NHib and DB table schema, and it would not throw an error until I actually accessed the table. Still, building the session factory with about 100 tables took several seconds in the profiler (Ants).

Perhaps it's best to disable validation for debugging, as long as the schema hasn't changed.

If each schema query takes like 20ms, network latency plus processing in DB and client, that's at least close to 5 seconds for 230 tables. More when the actual roundtrip times are longer.

SQL Server profiler might also show what is incoming from the application, especially the timespan over which schema queries come in, and how much duration they take in the database.

Erik Hart
  • 1,114
  • 1
  • 13
  • 28
  • I have looked into disabling this, but I haven't found anything that describes how to do so. It seems like a SchemaAutoAction must be configured. – Sidney Leake Jan 17 '17 at 23:02