SQL Question: When no WHERE is faster having WHERE

[markphsd]

When dealing with around less than a thousand records, I find that it's faster to filter out the records i need through code rather than a WHERE statement.

The tables have about 50 to 75 fields and are properly are indexed, would anyone have any disagreements or ideas?

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[jsteph]

Assuming you're using ADO or pass-thru, then at the client app level, you want to filter it because of the traffic schlepping records across the wire.

However, most dbs don't use an index seek on that small of a set because 2 I/O's (index then datapage) take more time than going direct to the data and doing a simple full-table scan of a small table.

So in your case, it's the extra data being schelpped over the wire that would cost the most.
--Jim

 

[markphsd]

So you are saying that I don't want to pass about 50kbs across a local network, because I'd rather pass maybe 10kb, because when my users hit 100 i'll be passing around 5000kbs?

Well that would make sense, but then again,I'm working off about 5 to 10 users. However, that's definately something to keep in mind. What you are saying would definately apply to a large number of users.

However, I messed up when i said 50 to 75 tables, I'm actually only pulling 6 columns.



Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[jsteph]

Yes, that's the gist. Certainly if the tables are small the difference may not be noticeable, but it's good habit to get into.
--Jim