Why I'm a fan of ActiveRecord and NHibernate

We had an email thread going on the quality of queries that ActiveRecord and NHibernate can produce. It had some good info it so I figured I'd post it. Start at the beginning.

 

From: Carter, ChrisJ
Sent: Thursday, December 13, 2007 11:33 AM
To:
Subject: RE: SQL question

It uses a current_timestamp when the end user(me) does a lame query that's similar to select * from [some huge table].   Currently there are 699,463 records in fundmanger.contract_payment.   Let's say i want only results 100 through 300 of all participants, excluding owners, with business type code of 02. I write this C# code:   ContractPayment[] contractpayments = ContractPayment.SlicedFindAll(100, 200,
new Order[] { Order.Desc("Id") },
Expression.Eq("BusinessTypeCode", "02"),
Expression.EqProperty("ParentTaxIdentification", "TaxIdentification"),
Expression.EqProperty("ParentTaxIdentificationTypeCode", "TaxIdentificationTypeCode"));  

It produces this SQL(extra columns were removed for clarity):

WITH query AS (
SELECT TOP 300 ROW_NUMBER() OVER (ORDER BY this_.contract_payment_id desc) as __hibernate_row_nr__
FROM contract_payment this_
WHERE this_.business_type_code = '02'
and this_.parent_tax_identification = this_.tax_identification
and this_.parent_tax_identification_type_code = this_.tax_identification_type_code
ORDER BY this_.contract_payment_id desc)
SELECT *
FROM query
WHERE __hibernate_row_nr__ > 100
ORDER BY __hibernate_row_nr__

 

It orders by the clustered index, and look, no TOP performance hit. All of this for free.     Normally a dba is not going to see a developer's inline queries unless there's an issue, such as performance, or if the dba wrote the query in the first place. So looking this closely to queries coming out of an application will almost never happen.   However, this is pretty good sql code being generated. I'm pretty sure most devs on this list haven't messed around with Common Table Expressions(the WITH clause), so it seems like a pretty big win if you get that for free behind the scenes.   I can write a crappy ActiveRecord query just as easily as I can write a crappy SQL query. Why I'm a fan of ActiveRecord and NHibernate, and other similar tools(SubSonic, LLBLGEN Pro), is it takes me out of the equation of writing sql, executing a query, and then mapping up the results to whatever I'm doing on the backend. Well tested mature tools like NHibernate, are way better at writing sql and mapping up results than I am. And when they're not, I step in and write SQL appropriately to solve the problem.    
From:
Sent: Wednesday, December 12, 2007 5:45 PM
To: Carter, ChrisJ - Fort Collins
Subject: RE: SQL question
 

Also, you should do the order by on a clustered key. Do you know why it uses current timestamp?

From: Carter, ChrisJ - Fort Collins, CO
Sent: Wednesday, December 12, 2007 3:57 PM
To:
Subject: RE: SQL question

It's the top 300, regardless of the rows in the table since in the example I'm looking for everything between 100 and 300. Actually, without putting in that top clause, like in the BOL example, the WITH block selects the entire contents of the table rather than just what you need.

Here's the query plan using TOP

Here's the one using the between:

Which one's better?

-----Original Message-----
From:
Sent: Wednesday, December 12, 2007 3:20 PM
To: Carter, ChrisJ
Subject: RE: SQL question

I think TOP is more expensive to use. What will it look like for 100,000 rows? Will it be TOP 100,000?


-----Original Message-----
From: Carter, ChrisJ - Fort Collins, CO
Sent: Wednesday, December 12, 2007 3:16 PM
To:
Subject: RE: SQL question

For those using Castle ActiveRecord, SlicedFindAll does this for you:

GrantAgreement[] agreements = GrantAgreement.SlicedFindAll(100, 200);

Generates sql that looks like this:

WITH query AS (SELECT TOP 300 ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__,
this_.agreement_id as agreement1_2_8_
FROM grant_agreement this_)
SELECT *
FROM query
WHERE __hibernate_row_nr__ > 100
ORDER BY __hibernate_row_nr__


________________________________

From:
Sent: Wednesday, December 12, 2007 2:53 PM
To: Carter, ChrisJ
Subject: FW: SQL question


I think Tham's idea is the prevalent one. New in SQL 2005. The WITH syntax makes it look pretty simple.

The following example from BOL returns rows with numbers 50 to 60 inclusive in the order of the OrderDate.

USE AdventureWorks;
GO
WITH OrderedOrdersAS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;

-----Original Message-----
From:
Sent: Wednesday, December 12, 2007 1:32 PM
To:
Subject: RE: SQL question

Thanks!

-----Original Message-----
From:
Sent: Wednesday, December 12, 2007 1:26 PM
To:
Subject: RE: SQL question

You should check the article below. Don't know it will help or not.

Paging Records Using SQL Server 2005 Database - ROW_NUMBER Function

-----Original Message-----
From:
Sent: Wednesday, December 12, 2007 1:24 PM
To:
Subject: SQL question

Someone asked me this, and I don’t think it is possible, but told him I would ask around.

Is it possible in SQL to return a subset of data from a query, like row 100 to row 200? Know you can use the TOP statement to get the first x records, but they want to "page" through the results 100 rows at a time, first 100 rows first time, second 100 rows second time, etc.

 
Author: , 0000-00-00
Source