Query Performance

Good Morning All!
I have a couple questions regarding query performance:
#1 - I have a VB6 program that allows the user to build
the where clause of a query to be ran against a SQL 2000
database. When the where clause is established, it is
sent into a stored procedure where there are 3 different
queries that can be executed based on the criteria being
used. Basically, the join structure of each query is
different. I created an index for a query that had been
running slow that executes 1 of the 3 queries in the
stored procedure. However, now whenever 1 of the other
queries are executed for a different set of criteria it is
3X longer to run. I remove the index, and it is fast
again, but my other query is slow again. Can someone
please explain to me why this happens, and why SQL chooses
a less optimized plan for some of the queries when a new
index is added?
#2 - In the same type of environment as explained above
(i.e. VB6, SQL 2000, and stored procedures), we have a
single user that tests query speed to determine what
indexes need built. We can get a query to run in 5
seconds, but when multiple users try to execute the same
or different queries together, it takes nearly 5X longer.
Once the first user gets results, then the others come
back one right after the other in no time. Does SQL only
allow 1 person to execute a stored procedure at any given
time? What effect does the mult-user environment have in
this scenerio.
Any help would be greatly appreciated, as I am new to
working with indexes.
Thank you,
Heidi
for number 1...
Posting the text of the procedure would allow a more precise answer, but I
suspect the following info will help you...
Procedure plans are cached and are not recompiled if there is a plan already
in cache that can be reused. (I'm greatly simplying, but that's accurate
enough for this discussion...)
In your case, you have a proc that might run a variety of different queries
and for each query it sounds like the parameters might be quite different
from run to run. A good plan for one of the queries might not be a good plan
for the other queries. However, if a plan is already cached, it may be
re-used even if it's not the best plan. You might want to experiment with
having the 'top level' procedure call one of 3 other procs where each of the
child procs accepts the parameters.
for number 2...
It's difficult to say without more data. The most likley scenarios are a)
blocking. Do the queries update,delete,insert data? or b) waiting on some
type of resource. Search www.sqlmag.com archieves for an article (with
scripts) by Tom Davidson from MS. Use the script to run dbcc
sqlperfwaitstats and see if you have a high wait in any area. I suspect you
may be having a high wait of pageiolatch_sh. ALso, while the queries are
'running' and are not returning data untilt he first query is done... you
could look at the row in master..sysprocesses for each of the 'waiting'
queries to see what the waittype is.
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:756b01c494dc$6bf12be0$a301280a@.phx.gbl...
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi
|||Also have a look at
http://www.sommarskog.se/dyn-search.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:756b01c494dc$6bf12be0$a301280a@.phx.gbl...
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi
|||hdsjunk wrote:
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi
If a query takes 5 seconds to run on a server with low utlization, then
the query is unlikely running efficiently as it is consuming 5 seconds
of duration and likely something similar in CPU. I would say you need to
performance tune the query to run in much less than 5 seconds. Something
in the 10's of milliseconds should be your goal.
As other posters have mentioned, if it's taking 5 seconds, it's likely
locking pages on the same tables other callers to the same procedure
require.
While 5 seconds may be an appropriate time for an individual end-user to
wait for a response from the application, it usually won't do in a
concurrent database environment. Use Profiler and Query Analyzer to see
what is taking so long in the query to execute and try and tune it.
Also, as others have said, you are risking recompiles sending 3
different where clauses for execution to the same stored procedure. The
way to eliminate this is to use sp_executesql inside the procedure to
execute the dynamic SQL.
David G.

Query Performance

Good Morning All!
I have a couple questions regarding query performance:
#1 - I have a VB6 program that allows the user to build
the where clause of a query to be ran against a SQL 2000
database. When the where clause is established, it is
sent into a stored procedure where there are 3 different
queries that can be executed based on the criteria being
used. Basically, the join structure of each query is
different. I created an index for a query that had been
running slow that executes 1 of the 3 queries in the
stored procedure. However, now whenever 1 of the other
queries are executed for a different set of criteria it is
3X longer to run. I remove the index, and it is fast
again, but my other query is slow again. Can someone
please explain to me why this happens, and why SQL chooses
a less optimized plan for some of the queries when a new
index is added?
#2 - In the same type of environment as explained above
(i.e. VB6, SQL 2000, and stored procedures), we have a
single user that tests query speed to determine what
indexes need built. We can get a query to run in 5
seconds, but when multiple users try to execute the same
or different queries together, it takes nearly 5X longer.
Once the first user gets results, then the others come
back one right after the other in no time. Does SQL only
allow 1 person to execute a stored procedure at any given
time? What effect does the mult-user environment have in
this scenerio.
Any help would be greatly appreciated, as I am new to
working with indexes.
Thank you,
Heidifor number 1...
Posting the text of the procedure would allow a more precise answer, but I
suspect the following info will help you...
Procedure plans are cached and are not recompiled if there is a plan already
in cache that can be reused. (I'm greatly simplying, but that's accurate
enough for this discussion...)
In your case, you have a proc that might run a variety of different queries
and for each query it sounds like the parameters might be quite different
from run to run. A good plan for one of the queries might not be a good plan
for the other queries. However, if a plan is already cached, it may be
re-used even if it's not the best plan. You might want to experiment with
having the 'top level' procedure call one of 3 other procs where each of the
child procs accepts the parameters.
for number 2...
It's difficult to say without more data. The most likley scenarios are a)
blocking. Do the queries update,delete,insert data? or b) waiting on some
type of resource. Search www.sqlmag.com archieves for an article (with
scripts) by Tom Davidson from MS. Use the script to run dbcc
sqlperfwaitstats and see if you have a high wait in any area. I suspect you
may be having a high wait of pageiolatch_sh. ALso, while the queries are
'running' and are not returning data untilt he first query is done... you
could look at the row in master..sysprocesses for each of the 'waiting'
queries to see what the waittype is.
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:756b01c494dc$6bf12be0$a301280a@.phx.gbl...
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi|||Also have a look at
http://www.sommarskog.se/dyn-search.html
--
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:756b01c494dc$6bf12be0$a301280a@.phx.gbl...
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi|||hdsjunk wrote:
> Good Morning All!
> I have a couple questions regarding query performance:
> #1 - I have a VB6 program that allows the user to build
> the where clause of a query to be ran against a SQL 2000
> database. When the where clause is established, it is
> sent into a stored procedure where there are 3 different
> queries that can be executed based on the criteria being
> used. Basically, the join structure of each query is
> different. I created an index for a query that had been
> running slow that executes 1 of the 3 queries in the
> stored procedure. However, now whenever 1 of the other
> queries are executed for a different set of criteria it is
> 3X longer to run. I remove the index, and it is fast
> again, but my other query is slow again. Can someone
> please explain to me why this happens, and why SQL chooses
> a less optimized plan for some of the queries when a new
> index is added?
> #2 - In the same type of environment as explained above
> (i.e. VB6, SQL 2000, and stored procedures), we have a
> single user that tests query speed to determine what
> indexes need built. We can get a query to run in 5
> seconds, but when multiple users try to execute the same
> or different queries together, it takes nearly 5X longer.
> Once the first user gets results, then the others come
> back one right after the other in no time. Does SQL only
> allow 1 person to execute a stored procedure at any given
> time? What effect does the mult-user environment have in
> this scenerio.
> Any help would be greatly appreciated, as I am new to
> working with indexes.
> Thank you,
> Heidi
If a query takes 5 seconds to run on a server with low utlization, then
the query is unlikely running efficiently as it is consuming 5 seconds
of duration and likely something similar in CPU. I would say you need to
performance tune the query to run in much less than 5 seconds. Something
in the 10's of milliseconds should be your goal.
As other posters have mentioned, if it's taking 5 seconds, it's likely
locking pages on the same tables other callers to the same procedure
require.
While 5 seconds may be an appropriate time for an individual end-user to
wait for a response from the application, it usually won't do in a
concurrent database environment. Use Profiler and Query Analyzer to see
what is taking so long in the query to execute and try and tune it.
Also, as others have said, you are risking recompiles sending 3
different where clauses for execution to the same stored procedure. The
way to eliminate this is to use sp_executesql inside the procedure to
execute the dynamic SQL.
David G.

Query over name/value table

Hello NG,
In my program users should be able to manage their documents. To allow users
to search for documents they can use predefined attributes like Title or
Description or they can assign their own custom attributes. I'm using the
following tables for this.
Document (Stores the predefined document attributes)
- Id
- Title
- Description
- ...
Attributes (Stores the custom attributes)
- Id
- Name
AttributeValues (Stores the custom attribute values assigned to a document)
- DocumentId
- AttributeId
- Value
Users should be able to do complex queries to find documents (and/or/not,
nested parenthesises). They should not only be able to query for the
predefined attributes, but also for the custom attributes. I also want to
have all attributes (predefined or custom) present for each document that
matches the query conditions to load them into a document-object in my
application.
What is the best way to do such queries in SQL? The problem that I have is
with the custom attributes. When I want to query for the predefined
attributes, I could just do a:
SELECT * FROM Document, AttributeValues WHERE Document.Title like '%bla%'
AND AttributeValues.DocumentId = Document.Id ORDER BY Document.ID
The result would give me all attribnutes of every document that contains
"bla" in the title, with the custom attributes in seperate rows.
However I can't come up with a good query that would return a similar result
set for searching custom attributes.
Can you help me?
Thanks a lot!
Thomas Krause
P.S. The user should be able to query an infinite number of custom or
predefined attributes at the same time and combine them with nested
ANDs/ORs/etc!Ok. Thank you very much.
What's about the performance? Is there a better/faster way than having this
subqueries.
We are not talking about a very big dataset here, but if there is a
noticible faster way for doing this I would prefer it anyway.
For example: I think you could do the same with multiple joins of the
AttributeValues-table. Would this be faster?
Thanks,
Thomas Krause
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote:
> How about (add an exists per custom attribute):
> SELECT * FROM Document
> WHERE Document.Title like '%bla%'
> and exists (select 1 from AttributeValues
> where AttributeValues.DocumentId = Document.Id
> and AttributeValues.AttributeId = '@.AttributeID'
> and AttributeValues.Value like '%blablabla%'
> )
> ORDER BY Document.ID
> This sounds like it will require dynamic SQL to handle N number of custom
> attributes, but this should give you an idea of the simplified approach.
> If
> you do use dynamic SQL, check out these links for some security concerns.
> Some reading on SQL Injection:
> http://www.sqlservercentral.com/col...qlinjection.asp
> Advanced Injection:
> http://www.nextgenss.com/papers/adv...l_injection.pdf
> http://www.nextgenss.com/papers/mor...l_injection.pdf
> Dynamic SQL and SQL injection:
> http://www.sommarskog.se/dynamic_sql.html#sp_executesql
>
> "Thomas Krause" <Forum.Thomas.Krause.RemoveThis@.gmx.de> wrote in message
> news:e37qq1$m2u$01$1@.news.t-online.com...
> users
> document)
> result
>|||You could do the same with multiple joins, and you would have to test each
aproach to see which is faster. I would expect the exists and the inner
join to perform pretty much the same, while the outer join would likely be
slower. In any case, proper indexes are critical (probably one on either
(AttributeId, Value) or (DocumentId, AttributeId, Value). The indexes may
determine whther SQL Server treats the exists and inner join differently or
not.
If the attribute has to be found, use an inner join:
SELECT * FROM Document
WHERE Document.Title like '%bla%'
inner join AttributeValues
on Document.Id = AttributeValues.DocumentId
and AttributeValues.AttributeId = '@.AttributeID'
and AttributeValues.Value like '%blablabla%'
ORDER BY Document.ID
If the attribute is optional, use an outer join:
SELECT * FROM Document
WHERE Document.Title like '%bla%'
left outer join AttributeValues
on Document.Id = AttributeValues.DocumentId
and AttributeValues.AttributeId = '@.AttributeID'
and AttributeValues.Value like '%blablabla%'
ORDER BY Document.ID
"Thomas Krause" <Forum.Thomas.Krause.RemoveThis@.gmx.de> wrote in message
news:e382bl$fvp$02$1@.news.t-online.com...
> Ok. Thank you very much.
> What's about the performance? Is there a better/faster way than having
this
> subqueries.
> We are not talking about a very big dataset here, but if there is a
> noticible faster way for doing this I would prefer it anyway.
> For example: I think you could do the same with multiple joins of the
> AttributeValues-table. Would this be faster?
> Thanks,
> Thomas Krause
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote:
custom
concerns.
http://www.sqlservercentral.com/col...qlinjection.asp
or
the
(and/or/not,
to
that
'%bla%'
contains
>|||Ok, thanks again.
If the performance is not that different, I'll go with the subqueries, since
they are a little bit easier to generate dynamically.
Thomas Krause
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote:
> You could do the same with multiple joins, and you would have to test each
> aproach to see which is faster. I would expect the exists and the inner
> join to perform pretty much the same, while the outer join would likely be
> slower. In any case, proper indexes are critical (probably one on either
> (AttributeId, Value) or (DocumentId, AttributeId, Value). The indexes may
> determine whther SQL Server treats the exists and inner join differently
> or
> not.
> If the attribute has to be found, use an inner join:
> SELECT * FROM Document
> WHERE Document.Title like '%bla%'
> inner join AttributeValues
> on Document.Id = AttributeValues.DocumentId
> and AttributeValues.AttributeId = '@.AttributeID'
> and AttributeValues.Value like '%blablabla%'
> ORDER BY Document.ID
> If the attribute is optional, use an outer join:
> SELECT * FROM Document
> WHERE Document.Title like '%bla%'
> left outer join AttributeValues
> on Document.Id = AttributeValues.DocumentId
> and AttributeValues.AttributeId = '@.AttributeID'
> and AttributeValues.Value like '%blablabla%'
> ORDER BY Document.ID
> "Thomas Krause" <Forum.Thomas.Krause.RemoveThis@.gmx.de> wrote in message
> news:e382bl$fvp$02$1@.news.t-online.com...
> this
> custom
> concerns.
> http://www.sqlservercentral.com/col...qlinjection.asp
> or
> the
> (and/or/not,
> to
> that
> '%bla%'
> contains
>