"Ryan Hunt" <ryan.hunt@highwoods.com> wrote in message news:...
> Alan, your question is very vague - which may be leading to the lack of
> answers. When you say you are fetching 5000 objects, do you mean records?
>
> What is going to be best depends on a lot. In general, yes, continually
> opening and closing connections is going to incur overhead on both your DB
> server and your app server. Your concerns about the small queries can be
> somewhat allayed by the efficient plan reuse from SQL 2000 and 2005. Once
a
> query is run, subsequent queries with similar paramters (same column args
> and similar cardinality) will use the same compiled plan from the cache.
> So, that part doesn't disprove your boss.
>
> Now, where your argument comes into play is in network traffic. 50 query
> calls of 100 records each will require 49 more ack/syn TCP handshakes
> (server and network CPU) and network traversal (network latency)than is
> necessary - not to mention that you are also sending a simiar query
command
> (as text) to the server 50 times which does take up additional network
> bandwith. Furthmore, 50 different queries will result in many more sql
> server RPC's than just one query - which again increases overall CPU.
Also,
> if you are trying to break up 5000 records into smaller symetrical chunks
> (but in the end you need all 5000), then your queries become more complex.
> You are now selecting records in a pseudo ordinal format by assigning a
> numbering sequence - which is likely to increase the complexity of the
SQL,
> require more logic, and require record sorts - which will all increase the
> CPU.
>
> In my experience with web apps, its better to increase the memory on your
> web/application server by a gig or so and simply return all 5000 records
in
> one fell swoop (as long as all records are required). Let the web server
> render multiple pages and separate the records into workable chunks for
the
> users.
>
> If you are looking for a very active SQL forum, check out
> http://www.sqlservercentral.com/Forums/Default.aspx
>
> Best of luck.
>
> Ryan
>
> "Allan Ebdrup" <ebdrup@noemail.noemail> wrote in message
> news:%23kIhprXWGHA.3332@TK2MSFTNGP02.phx.gbl...
> > I just had a discussion with my boss, we are running a query that
fetches
> > about 5000 objects from the database using MSSQL and DotNet 2.0.
> > My boss thinks that the recommendation of keeping the connections open
for
> > as short a time as possible means that one shoule open a connection,
fetch
> > one row, close the connection, open another connection, fetch one row
and
> > close the connection and so on.
> > I belive that the extra overhead of opening and closing connections all
> the
> > time, of generating execution plans for each query, and running the
> queries
> > many times gives his solution a large performance hit. My recommendation
> > will be to fetch all the rows needed in one large SQL query.
> > I've done several other projects where I have proven this to be true,
> there
> > is indeed a large performance hit from generating many small queries
> instead
> > of one large. But my boss just says "no" and disagrees.
> >
> > Can you give me some good arguments and/or point me to some best
practice
> > documents that describe this so I can convince my boss he's wrong?
> >
> > Kind Regards,
> > Allan Ebdrup
> >
> > FUT: microsoft.public.sqlserver.programming
> >
> >
>
>

"Ryan Hunt" <ryan.hunt@highwoods.com> wrote in message
news:eULzeERXGHA.4324@TK2MSFTNGP03.phx.gbl...
>
> "Ryan Hunt" <ryan.hunt@highwoods.com> wrote in message news:...
>> Alan, your question is very vague - which may be leading to the lack of
>> answers. When you say you are fetching 5000 objects, do you mean
>> records?
>>
>> What is going to be best depends on a lot. In general, yes, continually
>> opening and closing connections is going to incur overhead on both your
>> DB
>> server and your app server. Your concerns about the small queries can be
>> somewhat allayed by the efficient plan reuse from SQL 2000 and 2005.
>> Once
> a
>> query is run, subsequent queries with similar paramters (same column args
>> and similar cardinality) will use the same compiled plan from the cache.
>> So, that part doesn't disprove your boss.
>>
>> Now, where your argument comes into play is in network traffic. 50 query
>> calls of 100 records each will require 49 more ack/syn TCP handshakes
>> (server and network CPU) and network traversal (network latency)than is
>> necessary - not to mention that you are also sending a simiar query
> command
>> (as text) to the server 50 times which does take up additional network
>> bandwith. Furthmore, 50 different queries will result in many more sql
>> server RPC's than just one query - which again increases overall CPU.
> Also,
>> if you are trying to break up 5000 records into smaller symetrical chunks
>> (but in the end you need all 5000), then your queries become more
>> complex.
>> You are now selecting records in a pseudo ordinal format by assigning a
>> numbering sequence - which is likely to increase the complexity of the
> SQL,
>> require more logic, and require record sorts - which will all increase
>> the
>> CPU.
>>
>> In my experience with web apps, its better to increase the memory on your
>> web/application server by a gig or so and simply return all 5000 records
> in
>> one fell swoop (as long as all records are required). Let the web server
>> render multiple pages and separate the records into workable chunks for
> the
>> users.
>>
>> If you are looking for a very active SQL forum, check out
>> http://www.sqlservercentral.com/Forums/Default.aspx
>>
>> Best of luck.
>>
>> Ryan
>>
>> "Allan Ebdrup" <ebdrup@noemail.noemail> wrote in message
>> news:%23kIhprXWGHA.3332@TK2MSFTNGP02.phx.gbl...
>> > I just had a discussion with my boss, we are running a query that
> fetches
>> > about 5000 objects from the database using MSSQL and DotNet 2.0.
>> > My boss thinks that the recommendation of keeping the connections open
> for
>> > as short a time as possible means that one shoule open a connection,
> fetch
>> > one row, close the connection, open another connection, fetch one row
> and
>> > close the connection and so on.
>> > I belive that the extra overhead of opening and closing connections all
>> the
>> > time, of generating execution plans for each query, and running the
>> queries
>> > many times gives his solution a large performance hit. My
>> > recommendation
>> > will be to fetch all the rows needed in one large SQL query.
>> > I've done several other projects where I have proven this to be true,
>> there
>> > is indeed a large performance hit from generating many small queries
>> instead
>> > of one large. But my boss just says "no" and disagrees.
>> >
>> > Can you give me some good arguments and/or point me to some best
> practice
>> > documents that describe this so I can convince my boss he's wrong?
>> >
>> > Kind Regards,
>> > Allan Ebdrup
>> >
>> > FUT: microsoft.public.sqlserver.programming
>> >
>> >
>>
>>
>
>