Which is fastest? Data retrieval...

Question

Is it quicker to make one trip to the database and bring back 3000+ plus rows, then manipulate them in .net & LINQ or quicker to make 6 calls bringing back a couple of 100 rows at a time?

Answer 1

It will entirely depend on the speed of the database, the network bandwidth and latency, the speed of the .NET machine, the actual queries etc.

In other words, we can't give you a truthful general answer. I know which sounds easier to code :)

Unfortunately this is the kind of thing which you can't easily test usefully without having an exact replica of the production environment - most test environments are somewhat different to the production environment, which could seriously change the results.

Answer 2

Well, the answer is always "it depends". Do you want to optimize on the database load or on the application load?

My general answer in this case would be to use as specific queries as possible at the database level, therefore using 6 calls.

Answer 3

This does not directly answer your question, but I asked a question about link "How fast is Linq".

Perhaps the answers will help you.

Answer 4

Thx

I was kind of thinking "ball park", but it sounds as though its a choice thing...the difference is likely small.

I was thinking that getting all the data and manipulating in .net would be the best - I have nothing concrete to base this on (hence the question), I just tend to feel that calls to the DB are expensive and if I know i need all the data...get it in one hit?!?

Answer 5

Part of the problem is that you have not provided sufficient information to give you a precise answer. Obviously, available resources need to be considered.

If you pull 3000 rows infrequently, it might work for you in the short term. However, if there are say 10,000 people that execute the same query (ignoring cache effects), this could become a problem for both the app and db.

Now in the case of something like pagination, it makes sense to pull in just what you need. But that would be a general rule to try to only pull what is necessary. It's much more elegant to use a scalpel instead of a broadsword. =)

Answer 6

If you are talking about a query that has already been run by SQL (so optimized by SQL Server), working with LINQ or a SqlDataReader might actually have the same performance.

The only difference will be "how hard will it be to maintain your code?"

LINQ doesn't query anything to the database until you ask for the result with ".ToList()" or ".ToArray()" or even ".Count()". LINQ is dynamically building your query so it is exactly the same as having a SqlDataReader but with runtime verification.

Answer 7

I always stick to the rule of "bring in what I need" and nothing more...the problem I have here is that I need it all, I just need it displayed separately.

So say... I have a table with userid and typeid. I want to display all records with a userid, and display on the page in grids say separated by typeid.

At the moment I call sproc that does "select field1, field2 from tab where userid=1", then on the page set the datasource of a grid to from t in tab where typeid=2 select t;

Rather than calling a different sproc "select field1, field2 from tab where userid=1 and typeid=2" 6 times.

??

Answer 8

Rather than speculating, why don't you try both and measure the results?

Answer 9

Is this for one user, or will many users be querying the data? The single database call will scale better under load.

Answer 10

Speed is only one consideration among many.

How flexible is your code? How easy is it to revise and extend when the requirements change? How easy is it for another person to read and maintain your code? How portable is your code? what if you change to a diferent DBMS, or a different progamming language? Are any of these considerations important in your case?

Having said that, go for the single round trip if all other things are equal or unimportant.

You mentioned that the single round trip might result in reading data you don't need. If all the data you need can be described in a single result table, then it should be possible to devise a query that will get that result. That result table might deliver some result data in more than one row, if the query denormalizes the data. In that case, you might gain some speed by obtaining the data in several result tables, and composing the result yourself.

You haven't given enough information to know how much programming effort it will be to compose a single query or to compose the data returned by 6 queries.

As others have said, it depends.

Answer 11

the problem I have here is that I need it all, i just need it displayed separately...

The answer to your question is 1 query for 3000 rows is better than 6 queries for 500 rows. (given that you are bringing all 3000 rows back regardless)

However, there's no way you're going (to want) to display 3000 rows at a time, is there? In all likelihood, irrespective of using Linq, you're going to want to run aggregating queries and get the database to do the work for you. You should hopefully be able to construct the SQL (or Linq query) to perform all required logic in one shot.

Without knowing what you're doing, it's hard to be more specific.

* If you absolutely, positively need to bring back all the rows, then investigate the ToLookup() method for your linq IQueryable< T >. It's very handy for grouping results in non-standard ways.

Oh, and I highly recommend LINQPad (free) for trying out queries with Linq. It has loads of examples, and it also shows you the sql and lambda forms so you can familiarize yourself with Linq<->lambda form<->Sql.

Answer 12

If you know which 6 SQL statements you're going to execute beforehand, you can bundle them into one call to the database, and return multiple result sets using ADO or ADO.NET.

http://support.microsoft.com/kb/311274

Answer 13

It depends

1) if your connector implementation precaches a lot of objects AND you have big rows (for example blobs, contry polygons etc.) you have a problem, you have to download a LOT of data. I've optimalized once a code that had this problem and it was just downloading some megs of garbage all the time via localhost, and my software runs now 10 times faster because i removed the precaching by an option

2) If your rows are small and you have a good chance that you need to read through all the 3000, you're better going on a big resultset

3) If you don't use prepared statements, all queries have to be parsed! Big resultset might be better.

Hope it helped