Overhead of Multiple SQL Calls

Hey folks,
I've got a client server C# Windows Form app that communicates with a SQL
Server database backend. The number of users is less than 50.
I'm wondering what the difference in overhead is when say:
a) A Stored Procedure is called ten times, returning 1 record each time
VS.
b) The same Stored Procedure is called once returning 10 records
Due to connection pooling I would think the overhead is somewhat negligible,
especially when there's less than 50 users. I know option 'b' would be the
preffered method when it comes to database communication, but is there
really a BIG difference between the two when dealing with this few users?
Thanks!
(also posted on windows.forms newsgroup on Feb 25 with no response)Hi John,
I would think the network traffic would be significantly greater when
submiting 10 calls to get 1 record each as opposed to 1 call to get 10
records. That's automatically 10X the client-to-server network traffic.
Constantly opening and closing connections, as well as maintaining
connections that aren't in use, can use up a lot of resources on SQL Server
also. Now whether this should cause problems with a small number of users
is another story... It probably depends on how they're using the server...
Are they running automated reports that are requesting records one at a time
and might include thousands of separate requests? Or maybe they're just
accessing 30 records/hour on average each?
Connection pooling helps, but it can't compensate completely for a design
flaw.
Are you using C#.NET? If so, you might look into ADO.NET. ADO.NET is
designed to handle a lot of the management issues for you.
Thanks,
Mike C.
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>|||You have that many today but what about tomorrow? Seriously you should
always keep scalability in mind when designing the app and the schema. In
your example you are incurring about 10 times the overhead on not only the
server but the network and the client. There certainly may be times when it
might make sense to do it the A way but if you can do it the B way then you
should.
Andrew J. Kelly SQL MVP
"John Smith" <js@.no.com> wrote in message
news:OHFsJQqHFHA.560@.TK2MSFTNGP12.phx.gbl...
> Hey folks,
> I've got a client server C# Windows Form app that communicates with a SQL
> Server database backend. The number of users is less than 50.
> I'm wondering what the difference in overhead is when say:
> a) A Stored Procedure is called ten times, returning 1 record each time
> VS.
> b) The same Stored Procedure is called once returning 10 records
> Due to connection pooling I would think the overhead is somewhat
> negligible,
> especially when there's less than 50 users. I know option 'b' would be
> the
> preffered method when it comes to database communication, but is there
> really a BIG difference between the two when dealing with this few users?
> Thanks!
> (also posted on windows.forms newsgroup on Feb 25 with no response)
>