Kyle mentions he was playing with Oren's NHibernate Profiler and how excited he was that NHibernate Profiler was able to detect and thus help him reduce the number of queries for a single page. He mentions: “The target this time is NHibernate Profiler. As of this moment, I've had it "installed" for all of fifteen minutes and I've already reduced the query count of one page from 205 to 1.”
Kyle is just playing with NHibernate Profiler, but it brings up an excellent question for those who are new to using O/R Mappers - do you really know how your shiney O/R Mapper is communicating with the database? Is it eager loading or lazy loading those objects? It is making one roundtrip or 500 roundtrips to get the data?
I have talked to numerous developers in the past who have been surprised by the chattiness of their O/R Mapper. I mentioned quite awhile ago in a post about LINQ To SQL, LINQ To SQL - Query Tuning Appears To Break Down In *More Advanced* Scenarios, that you can run into those dreaded SELECT N+1 scenarios if you are not careful and end up creating 10's if not 100's of queries to fetch a graph of objects from the database. This also unexpectedly happens to developers who use lazy loading and then access that load-on-demand data 1 query at a time, causing once again 10's if not 100's of extra queries in the background.
As you use O/R Mappers you start to become more aware of these situations, but you always need to fire up SQL Server Profiler or even a profiler like ANTS Profiler or dotTrace to find those hotspots in your applications that may be caused by excessive database roundtrips by the O/R Mapper. I am not suggesting you go crazy with performance optimization, but it is important to understand what the O/R Mapper is doing in the background and act accordingly.
David Hayden
Related Posts: