Running Multiple Queries

[Jonathan Wood]

Greetings,

I'm developing an ASP.NET site and need a couple of queries to create
various content. As it stands, I'm running each query separately, reading
the result into a SqlDataReader.

It occurs to me that one thing that consumes a fair amount of time is the
round trip to the database server. Could I benefit by sending multiple
queries at the same time, separated by a semicolon? Is this much faster? How
can I retrieve multiple resultsets this way?

Note: This particular data is handled manually--I'm not sending it to any
databound server controls.

Thanks.

Jonathan

 

[sloan]

I do this quite often, and I think its worth the effort.

http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry

Download the code (I think I've posted that example before for you) and find
the CustomerController.

It gets
Customers
Orders
OrderDetails

with 1 query and 3 result sets.

 

[Jonathan Wood]

Not really.

Hmm. It would seem like tacking on a bit longer result would be a lot faster
than adding another round trip.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.nextresult.aspx

Thanks.

Jonathan

 

[Jonathan Wood]

I do this quite often, and I think its worth the effort.

http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!140.entry

Download the code (I think I've posted that example before for you) and
find the CustomerController.

It gets
Customers
Orders
OrderDetails

with 1 query and 3 result sets.

For me? I don't recall discussing this here before.

I'll check out the code.

Thanks.

Jonathan

 

[Jonathan Wood]

It really isn't - nanoseconds, maybe...

If anyone could shed some light on this, I'd be very interested.

As far as I'm concerned, when the database is on a separate server, the
request itself would take more than nanoseconds.

Jonathan

 

[Jonathan Wood]

I didn't say that the request itself would take only a few nanoseconds - I
said that the difference between the two methods would be a matter of
nanoseconds...

The question I asked related to the difference between sending two queries
as separate database requests vs. combining them into a single request. If
you send them as two separate requests, then the time associated with the
request would be included in the difference between the two. Therefore, if
the total difference was a few nanoseconds, that would require that the
request portion of the time would be equal to or less than a few
nanoseconds.

Jonathan

 

[sloan]

Ok......... sorry, I must have "mis-remembered" (in my Roger Clemens voice).

Nobody lies or bears false testimony anymore, they just "mis-remember" it !!

Stunningly, they usually only remember mis-remembering it when they are
caught in a lie.