Could it be faster to create one visit to the database and produce back 3000+ plus rows, then manipulate them insinternet &lifier LINQ or faster to create 6 calls returning a few 100 rows at any given time?
It'll entirely rely on the rate from the database, the network bandwidth and latency, the rate from the .Internet machine, the particular queries etc.
Quite simply, we can not provide you with a truthful general answer. I understand which sounds simpler to code :)
Regrettably this is actually the type of factor that you simply can't easily test usefully without getting an exact replica from the production atmosphere - most test conditions are somewhat dissimilar to the development atmosphere, that could seriously alter the results.
Speed is just one consideration among many.
How flexible is the code? How easy could it be to revise and extend once the needs change? How easy will it be for someone else to see and keep your code? How portable is the code? let's say you alter to some diferent DBMS, or perhaps a different progamming language? Are these factors essential in your situation?
With that said, choose the only round trip if other situations are equal or trivial.
You pointed out the single round trip might lead to reading through data you do not need. If all of the data you'll need could be referred to in one result table, then it ought to be easy to devise a question which get that result. That result table might deliver some result data in several row, when the query denormalizes the information. For the reason that situation, you may gain some speed by acquiring the information in a number of result tables, and creating the end result yourself.
You have not given enough information to understand just how much programming effort it will likely be to compose just one query in order to compose the information came back by 6 queries.
As others have stated, it is dependent.
the issue I've here's which i require it all, i simply require it displayed individually...
The response to your real question is 1 query for 3000 rows is preferable to 6 queries for 500 rows. (given that you're getting all 3000 rows back regardless)
However, there is no way you are going (to wish) to show 3000 rows at any given time, can there be? In all probability, regardless of using Linq, you are likely to wish to run aggregating queries and obtain the database to complete the meet your needs. You need to hopefully have the ability to construct the SQL (or Linq query) to do all needed logic over night.
Not understanding what you are doing, it's difficult to become more specific.
* Should you absolutely, positively need to regenerate all of the rows, then investigate ToLookup() way of your linq IQueryable< T >. It is extremely handy for grouping leads to non-standard ways.
Oh, and that i recommend LINQPad (free) for testing out queries with Linq. It's lots of good examples, and in addition it teaches you the sql and lambda forms so that you can acquaint yourself with Linq<->lambda form<->Sql.
Knowing which 6 SQL claims you are likely to execute in advance, you are able to bundle them into one call towards the database, and return multiple result sets using ADO or ADO.Internet.
Well, the reply is always "it is dependent". Would you like to optimize around the database load or around the application load?
My general answer within this situation is always to use as specific queries as you possibly can in the database level, therefore using 6 calls.
This doesn't directly answer your question, however i requested an issue about link "How fast is Linq".
Possibly the solutions can help you.
I had been type of thinking "ball park", however it sounds as if its an option factor...the main difference is probably small.
I believed that getting all of the data and adjusting insinternet will be the best - I've nothing concrete to base this on (hence the question), I simply often believe that calls towards the DB are costly and when I understand i want all of the data...have it in a single hit?!?