Limiting query results to a single record

[Rob Richardson]

Greetings!

My company makes furnaces to anneal (a form of heat-treating) coils of
steel. Our database contains a table named "inventory" that has every
coil that has ever been annealed since our application was installed.
It is quite likely that I will want to know about a single coil that
is being annealed, and that I will know the coil's ID before I ask the
database about it. If I set up a data adapter with the query "select
* from inventory" and then fill a DataTable from the adapter, I will
get records for all 50,000 coils. Once I have the table, I can create
a DataView object and apply a filter to it to get only the row I care
about, but I'll already have wasted the time to read 49,999 records.
If I want to limit myself to only the coil I'm interested in, I could
change the query to "select * from inventory where coil_id = 'ABCD' ",
but that seems a very clumsy way of doing things. Similarly, if I do
want all of the coil records but I want them sorted, I could just use
the plain query to load the table and then use the view object's Sort
property, but I would think it would be faster to sort them when I
originally read them. Is there a better way to limit or sort my
records at the time they're originally read than building an SQL
statement that will do what I want?

Thanks very much!

RobR

 

[Rick]

You can try a timed test to see which is quicker - a query like "Select *
from Inventory order by..." or "Select * from Inventory" and then sort after
you receive the records.

My guess it that letting the database do the sort is going to be much
quicker. It's sorting is tuned the the indexes in your DB while the sorting
of a DataTable is not.

Your commonly sorted columns should all be indexed in the DB with either a
foreign key or an index.

Rick

 

[William Vaughn \(MVP\)]

Frankly, I doubt it. Even when the index is applicable, it adds an extra
step to the process of returning the rows. In some cases an ORDER BY causes
a sort to be executed far in advance of the final WHERE clause filter that
returns a subset of the sorted rows. I prefer to sort on the client where
the client can decide how the rows are to be ordered. Given the number of
DataGridView and DataView options, I think it makes more sense to use the
client's CPU to do the work--especially since the application is designed
properly and only returns a max of a couple hundred rows...

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

 

[Rick]

Bill,

Maybe, however in this case Rob says he wants to return all 50,000 rows.
This is hardly the classic definition of proper design although perhaps it
is necessary.

Anyway only a test will really give the answer since it depends on a lot of
variables that we do not know - the DB server speed, the client CPU, network
speed etc.

No doubt anyway that the DataGridView ability to sort when you click on a
column header is a big bonus for developers who no longer have to worry
about coding for this option.

Rick