many small queries vs one large query

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"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?
>
Don't argue with your boss. Implement it both ways and show him.
David|||"Allan Ebdrup" <ebdrup@.noemail.noemail> wrote in
news:#kIhprXWGHA.3332@.TK2MSFTNGP02.phx.gbl:

> 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
I would have submitted one large bulk query as well.
Perhaps multiple small queries would reduce the time a table is locked?
Any other solutions? Maybe open a connection - execute a long running SP...
and fetch the results after the fact?|||> Don't argue with your boss. Implement it both ways and show him.
Hi David
Unfortunately it's him who's doing the implementing.|||My post just now seems to have got lost (wonder where it went).
To summarise what I said:
your boss is making a massive mistake. I've seen this tactic destroy a
large application.
it's fine at first, your queries all run nearly instantaneously, and
you'll load test it later, no need to worry about that right now.
then a table gets big. suddenly the instant query takes 50 milliseconds
to get the data. not a problem if you're only doing one select. if
you're doing 5000 separate ones it takes 5000x50 milliseconds.|||IF your query is executing 5000 times against the same tables and retrieving
the same columns with selection criteria against the same columns, then
running a single query to retreive all 5000 rows would be faster, without
question.
i.e.
select employee_id, employee_name from employees
Will have a connection open for much less time than cycling through each
employee with:
select employee_id, employee_name from employee where employee_id =
'SOMEID'
The first one will also use less CPU on the database, less IO, less network
traffic, etc. You can play with the code to control how you open the
dataset once you have retrieved it, but the first SQL will almost always be
faster. The only exception I can think of is if your client does not have
enough memory to handle the entire dataset.
If you have more complicated logic, and you are running against all
different tables, and combining this into one query means extensive joins
and business logic in the where clause, then you need to test each approach
and see what the performance gain is with the view.
Opening and closing connections for each communication wiht the database is
usually most efficient in my experience. Connection pooling manages the
resources really well. The connections do not really get opened and closed,
rather they stay open and available, but the client is able to free up some
of the resources associated with them between each call. This assumes that
you actually need to execute multiple queries to begin with.
Making 5000 seperate calls to the database will use much more network
resources than making a single call to the database to retreive the same
data. That part is a no brainer. Dependign on what you have for SQL, it
will not necessarily need to generate a plan every time (one would hope you
are using stored procedures with parameters and the plans are cached).
However, there would be some small overhead associated with running each
command.
"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
>|||SQL Server is quite capable of handling long connections, and as long as
they're actually doing something they make perfect sense. Many short lasting
connections may seem to put less stress on the server, but in fact the
overhead of many connections versus a single connection, when calls to the
server constitute a single business operation, must be multiplied with the
number of connections to give a true estimate of the actual (and expected)
stress on the server.
Will puts it very simply (and very true): 5000 connections where each lasts
50ms might kick the server pretty hard. 2nd grade mathematics.
ML
http://milambda.blogspot.com/|||If the calls to the database are made against isolated objects (i.e. against
different tables or to procedures that target different tables), then
individual connections may benefit from being initiated asynchronously. But
it would make more sense in issuing a single call to related objects for a
single business operation. At least IMHO.
ML
http://milambda.blogspot.com/|||All posts made a good point and I agree that your boss is wrong. It's a no
wonder he is a not a SQL Programmer but he is your boss. He signs your
paycheck so you may not have a choice. I often had to bow to what my boss
wants, hate it and it sucks but what can I do.
Grant
Who gives a {censored} if I am wrong.
"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
>|||If responsiveness of your application is important, you might consider to
fetch a certain amount of objects per fetch (say 100 objects, depending on
the size of your objects), instead of fetching 5000 objects together.
If you are going to get 5000 objects at once, while responsiveness is not a
problem, you should fetch all rows using one query.
Martin C K Poon
Senior Analyst Programmer
====================================
"Allan Ebdrup" <ebdrup@.noemail.noemail> bl
news:%23kIhprXWGHA.3332@.TK2MSFTNGP02.phx.gbl g...
> 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
>sql

many DISTINCT queries

Hi,

I have a table that contains log data, usually around a million records. The
table has about 10 columns with various attributes of the logged data,
nothing special. We're using SQL Server 2000.

Some of the columns (for example "category") have duplicate values
throughout the records. We have a web page that queries the table to show
all the unique columns, for example:

select distinct CATEGORY from table TEST

Obviously the server has to scan all rows in order to get all unique columns
which takes quite a while, especially since that web page contains several
of these types of queries. We also have a MAX(DATE) and MIN(DATE) query that
also add to the load.

I already created indexes on the CATEGORY (actually on all categories)
column which might help a little but I'm pretty sure that there has got to
be a better way.

I also create a view (select distinct CATEGORY from table TEST) and tried to
index it, but it won't let me index a query that contains a DISTINCT
statement.

Isn't there a way to create an index that contains only the distinct values?
Is there another way to speed this up?

Thanks for any hints!"Florian" <REMOVEUPPERCASEwizard_oz@.gmx.net> wrote in message
news:_cOUb.13574$F23.3296@.newsread2.news.pas.earth link.net...
> Hi,
> I have a table that contains log data, usually around a million records.
The
> table has about 10 columns with various attributes of the logged data,
> nothing special. We're using SQL Server 2000.
> Some of the columns (for example "category") have duplicate values
> throughout the records. We have a web page that queries the table to show
> all the unique columns, for example:
> select distinct CATEGORY from table TEST
> Obviously the server has to scan all rows in order to get all unique
columns
> which takes quite a while, especially since that web page contains several
> of these types of queries. We also have a MAX(DATE) and MIN(DATE) query
that
> also add to the load.
> I already created indexes on the CATEGORY (actually on all categories)
> column which might help a little but I'm pretty sure that there has got to
> be a better way.
> I also create a view (select distinct CATEGORY from table TEST) and tried
to
> index it, but it won't let me index a query that contains a DISTINCT
> statement.
> Isn't there a way to create an index that contains only the distinct
values?
> Is there another way to speed this up?
>
> Thanks for any hints!

If only you load/update the data relatively infrequently, then you could
create a 'lookup' table for each attribute, and populate them from the main
table after loading it (rather like the dimensions in a star schema):

insert into dbo.Categories (Category)
select distinct Category
from dbo.Test

Your client code could then query the lookup tables instead of the log
table. If you want to use indexed views, then you could create a view like
this:

create view dbo.Categories
with schemabinding
as
select Category, count_big(*) as 'Occurrences'
from dbo.Test
group by Category

That should be indexable, although there are quite a few other restrictions,
so you would need to check them out. But having multiple indexed views on
the table would make data modifications much slower, so if the data changes
frequently you might have to use some sort of lookup table approach anyway.

Simon|||> select distinct CATEGORY from table TEST
Try experimenting with group by instead of distinct. Also in query
analyzer, enable view execution plan, to get an idea what mssql is
doing. "select category from mytable group by category"|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:4023d837$1_1@.news.bluewin.ch...
> "Florian" <REMOVEUPPERCASEwizard_oz@.gmx.net> wrote in message
> news:_cOUb.13574$F23.3296@.newsread2.news.pas.earth link.net...
> > Hi,
> > I have a table that contains log data, usually around a million records.
> The
> > table has about 10 columns with various attributes of the logged data,
> > nothing special. We're using SQL Server 2000.
> > Some of the columns (for example "category") have duplicate values
> > throughout the records. We have a web page that queries the table to
show
> > all the unique columns, for example:
> > select distinct CATEGORY from table TEST
> > Obviously the server has to scan all rows in order to get all unique
> columns
> > which takes quite a while, especially since that web page contains
several
> > of these types of queries. We also have a MAX(DATE) and MIN(DATE) query
> that
> > also add to the load.
> > I already created indexes on the CATEGORY (actually on all categories)
> > column which might help a little but I'm pretty sure that there has got
to
> > be a better way.
> > I also create a view (select distinct CATEGORY from table TEST) and
tried
> to
> > index it, but it won't let me index a query that contains a DISTINCT
> > statement.
> > Isn't there a way to create an index that contains only the distinct
> values?
> > Is there another way to speed this up?
> > Thanks for any hints!
> If only you load/update the data relatively infrequently, then you could
> create a 'lookup' table for each attribute, and populate them from the
main
> table after loading it (rather like the dimensions in a star schema):
> insert into dbo.Categories (Category)
> select distinct Category
> from dbo.Test
> Your client code could then query the lookup tables instead of the log
> table. If you want to use indexed views, then you could create a view like
> this:
> create view dbo.Categories
> with schemabinding
> as
> select Category, count_big(*) as 'Occurrences'
> from dbo.Test
> group by Category
> That should be indexable, although there are quite a few other
restrictions,
> so you would need to check them out. But having multiple indexed views on
> the table would make data modifications much slower, so if the data
changes
> frequently you might have to use some sort of lookup table approach
anyway.

Thanks, I tried the indexed view and that seems to work OK now, pretty
fast - can't complain. Data shouldn't be updated that often so that should
be OK. Otherwise I might have to go with a lookup table - but it's not a
real good solution for our scenario for reasons I'm not going to bore
anybody with :)

Thanks!|||"louis nguyen" <louisducnguyen@.hotmail.com> wrote in message
news:b0e9d53.0402061216.5d4f6e56@.posting.google.co m...
> > select distinct CATEGORY from table TEST
> Try experimenting with group by instead of distinct. Also in query
> analyzer, enable view execution plan, to get an idea what mssql is
> doing. "select category from mytable group by category"

Yes, the group thing worked great - I also analyzed the execution plan and
now it's only returning the actual number of rows I'm getting - not the
whole table anymore.

Thanks.

manual for microsoft course 2071 (sql queries)

Hello,
I am trying to get the manual for the above course.
Can anybody help me?
Chris
You should be able to buy this from any MS Learning Center (used to be called CTEC).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lalala" <floatingonthebreeze@.hotmail.com> wrote in message news:captfa$j18$1@.news.storm.ca...
> Hello,
> I am trying to get the manual for the above course.
> Can anybody help me?
> Chris
>

manual for microsoft course 2071 (sql queries)

Hello,
I am trying to get the manual for the above course.
Can anybody help me?
ChrisYou should be able to buy this from any MS Learning Center (used to be calle
d CTEC).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lalala" <floatingonthebreeze@.hotmail.com> wrote in message news:captfa$j18$1@.news.storm.ca.
.
> Hello,
> I am trying to get the manual for the above course.
> Can anybody help me?
> Chris
>

manual for microsoft course 2071 (sql queries)

Hello,
I am trying to get the manual for the above course.
Can anybody help me?
ChrisYou should be able to buy this from any MS Learning Center (used to be called CTEC).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lalala" <floatingonthebreeze@.hotmail.com> wrote in message news:captfa$j18$1@.news.storm.ca...
> Hello,
> I am trying to get the manual for the above course.
> Can anybody help me?
> Chris
>

Management Studio Solution user-defined project folders?

When you create a Solution/Project in Management Studio, it creates 3 folders for you - Connections, Queries, Miscellaneous.

It does not appear that there exists the ability to create your own set of folders, either under the project or any of the 3 provided folders. Does anyone know of a way to do this?

If you are working on a project that has hundreds, perhaps thousands of stored procedures, views, etc., there currently seems to be no way to organize them. If this is true, this is an incredible MS oversight!

Thanks!

You have found the growing pain problems with the tool...maybe future versions will correct it.|||I would hope so! Since Mgmt Studio is based upon the Visual Studio shell, it doesn't seem like it would be that hard. In fact, I would think they would have had to specifically disable that functionality in this version, it seems so basic and fundamental to an IDE.|||

This is one of our most requested features. You can track the progress of this at http://connect.microsoft.com.

Here is the direct link to the issue: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124787

Vote on it and let us know that it is important to you.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/

|||

I already did. I also started my own post to this issue a long time ago.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127202

|||

Ahh, nice... even though your solution is not solved, can you mark the post as answered?

Management Studio Solution user-defined project folders?

When you create a Solution/Project in Management Studio, it creates 3 folders for you - Connections, Queries, Miscellaneous.

It does not appear that there exists the ability to create your own set of folders, either under the project or any of the 3 provided folders. Does anyone know of a way to do this?

If you are working on a project that has hundreds, perhaps thousands of stored procedures, views, etc., there currently seems to be no way to organize them. If this is true, this is an incredible MS oversight!

Thanks!

You have found the growing pain problems with the tool...maybe future versions will correct it.|||I would hope so! Since Mgmt Studio is based upon the Visual Studio shell, it doesn't seem like it would be that hard. In fact, I would think they would have had to specifically disable that functionality in this version, it seems so basic and fundamental to an IDE.|||

This is one of our most requested features. You can track the progress of this at http://connect.microsoft.com.

Here is the direct link to the issue: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124787

Vote on it and let us know that it is important to you.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server
http://blogs.msdn.com/sqlrem/

|||

I already did. I also started my own post to this issue a long time ago.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127202

|||

Ahh, nice... even though your solution is not solved, can you mark the post as answered?

|||I agree it is a huge MS oversight... fortunately theres a 3rd party solution available... Here's the link SQL Server Management Studio 2005 Project Plugin|||I agree it is a huge MS oversight... fortunately theres a 3rd party solution available... Here's the link SQL Server Management Studio 2005 Project Plugin