Limiting result set

  • Thread starter Thread starter Silva
  • Start date Start date
S

Silva

Hi
I'm wondering which solution is better to limit result: to set limit
directly in sql statement or in Fill method (of SqlDataAdapter)

1. Example ilustrating sql limiting:
....
// sql statement for select command: Select * From Customers Order by name
myDataAdapter.Fill( customers, 0, 10, "Customers");
...


2. Example ilustrating rather adonet limiting:
....
// sql statement for select command of dataAdapter: Select Top 10 * From
Customers Order by name
myDataAdapter.Fill( customers, "Customers");
....

Which is better for performance ???
Is the first solution worse?
Is sqlserver retrivig only 10 rows in the second solution??? or is selecting
all the appropriate rows but only 10 fills the restul set ???

Thanks
 
Hi Silva,

I think that TOP 10 is faster.
The Fill method executes the select and fetches only first 10 records while
SQL gets optimized for 10 records.
I am not 100% sure on this however looking at Sql Profiles it seems so.
 
Silva,
David Sceppa's book "Microsoft ADO.NET - Core Reference" from MS Press has a
write-up on this.

The SQL statement will almost always be faster, as you are letting your SQL
server do the work, where as in the second example, you are returning &
processing the ENTIRE result, but only keeping 10 records.

I don't remember if it was Sceppa's book or another article, I understand
the Fill method still reads the entire result set, although you are only
keeping the first 10.

Hope this helps
Jay
 
top 10 will definately work fast.

databases are optimized for set oriented results.

Rajesh Patel
 
Back
Top