Many roundtrips to the database, or all data / filter?

Question

Which method would be fastest / most efficient?

A) Read all data (822 rows) including those I do not need then filter / output those I do

B) 3 Round trips to the db using params filled via the last query to select only the data I need

Thanks.

Answer 1

C) Perform paging at the database level and a single roundtrip to fetch only the records you need to show.

Answer 2

It depends, but it shouldn't require three round trips.

It's almost always faster to filter the desired results at the database level with a well-formed WHERE clause on your SQL statement (if it's an ad-hoc query). Better still is to build a stored procedure with parameters you can pass from the front end. This is faster because the SQL does not need to be recompiled each time and more secure because it prevents ad-hoc SQL injection etc.

However, for a full answer, we'll need more information on what you're trying to achieve i.e. are you going for some sort of paging mechanism?