I have a sql statement:
SELECT A1.C_JOBBIDID, A2.ACCOUNTID A2_ACCOUNTID, A1.PROD_PR_GRP_C, A1.WON,
A2.DESCRIPTION A2_DESCRIPTION, A1.OPPORTUNITYID, A3.USERFIELD5 A3_USERFIELD5
,
A2.STATUS A2_STATUS, A1.SPEC_PR_PROD_C, A1.SPR_ITEM_ALLOW_Q, A1.DISTPRICE1ST
,
A1.SUBPRICE1ST, A1.EXPDATE1ST, A1.DISTPRICE2ND, A1.SUBPRICE2ND, A1.EXPDATE2N
D,
A1.DISTPRICE3RD, A1.SUBPRICE3RD, A1.EXPDATE3RD, A1.DISTPRICE4TH, A1.SUBPRICE
4TH,
A1.EXPDATE4TH
FROM C_JOBBID A1 INNER JOIN OPPORTUNITY A2 ON (A1.OPPORTUNITYID = A2.OPPORTU
NITYID)
INNER JOIN C_OPPORTUNITY_EXT A3 ON (A2.OPPORTUNITYID = A3.OPPORTUNITYID)
INNER JOIN C_OPPTOACCOUNT A4 ON (A4.OPPORTUNITYID = A1.OPPORTUNITYID)
WHERE A4.ACCOUNTID = 'A6UJ9A0069NH'
AND A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN
')
AND A1.PROD_PR_GRP_C = 'C167'
ORDER BY A1.OPPORTUNITYID ASC, A1.SPEC_PR_PROD_C ASC, A1.EXPDATE1ST ASC
This query runs slower than:
SELECT A1.C_JOBBIDID, A2.ACCOUNTID A2_ACCOUNTID, A1.PROD_PR_GRP_C, A1.WON,
A2.DESCRIPTION A2_DESCRIPTION, A1.OPPORTUNITYID, A3.USERFIELD5 A3_USERFIELD5
,
A2.STATUS A2_STATUS, A1.SPEC_PR_PROD_C, A1.SPR_ITEM_ALLOW_Q, A1.DISTPRICE1ST
,
A1.SUBPRICE1ST, A1.EXPDATE1ST, A1.DISTPRICE2ND, A1.SUBPRICE2ND, A1.EXPDATE2N
D,
A1.DISTPRICE3RD, A1.SUBPRICE3RD, A1.EXPDATE3RD, A1.DISTPRICE4TH, A1.SUBPRICE
4TH,
A1.EXPDATE4TH
FROM C_JOBBID A1 INNER JOIN OPPORTUNITY A2 ON (A1.OPPORTUNITYID = A2.OPPORTU
NITYID)
INNER JOIN C_OPPORTUNITY_EXT A3 ON (A2.OPPORTUNITYID = A3.OPPORTUNITYID)
INNER JOIN C_OPPTOACCOUNT A4 ON (A4.OPPORTUNITYID = A1.OPPORTUNITYID)
WHERE A4.ACCOUNTID = 'A6UJ9A0069NH'
AND ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE
USERID = 'ADMIN')
AND A1.PROD_PR_GRP_C = 'C167'
ORDER BY A1.OPPORTUNITYID ASC, A1.SPEC_PR_PROD_C ASC, A1.EXPDATE1ST ASC
As you can see, the only difference is checking the field for null and conve
rting
it to a blank string. You'd think the second one involving "more work" woul
d
take longer. Can someone explain why this is so?
Thanks in advance.
Jiho Han
jihohan@.yahoo.comWhat do you mean, slower? Considerably, or minimally? Can you check the
plan, or post the plan using SET SHOWPLAN_TEXT ON? It will probably reveal
the reason if it is considerable.
You might also try rewriting
> AND ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE
> USERID = 'ADMIN')
as
AND EXISTS ( SELECT 1
FROM C_USERMETROS
WHERE C_USERMETROS.USERID = 'ADMIN'
AND PRICINGMETRO = A3.METRO)
assuming that there is not a A3.METRO that actually equals ''
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Jiho Han" <jihohan@.yahoo.com> wrote in message
news:a19ab9b65ef08c806917750fbb2@.msnews.microsoft.com...
>I have a sql statement:
> SELECT A1.C_JOBBIDID, A2.ACCOUNTID A2_ACCOUNTID, A1.PROD_PR_GRP_C, A1.WON,
> A2.DESCRIPTION A2_DESCRIPTION, A1.OPPORTUNITYID, A3.USERFIELD5
> A3_USERFIELD5,
> A2.STATUS A2_STATUS, A1.SPEC_PR_PROD_C, A1.SPR_ITEM_ALLOW_Q,
> A1.DISTPRICE1ST,
> A1.SUBPRICE1ST, A1.EXPDATE1ST, A1.DISTPRICE2ND, A1.SUBPRICE2ND,
> A1.EXPDATE2ND,
> A1.DISTPRICE3RD, A1.SUBPRICE3RD, A1.EXPDATE3RD, A1.DISTPRICE4TH,
> A1.SUBPRICE4TH, A1.EXPDATE4TH FROM C_JOBBID A1 INNER JOIN OPPORTUNITY A2
> ON (A1.OPPORTUNITYID = A2.OPPORTUNITYID)
> INNER JOIN C_OPPORTUNITY_EXT A3 ON (A2.OPPORTUNITYID = A3.OPPORTUNITYID)
> INNER JOIN C_OPPTOACCOUNT A4 ON (A4.OPPORTUNITYID = A1.OPPORTUNITYID)
> WHERE A4.ACCOUNTID = 'A6UJ9A0069NH' AND A3.METRO IN (SELECT PRICINGMETRO
> FROM C_USERMETROS WHERE USERID = 'ADMIN')
> AND A1.PROD_PR_GRP_C = 'C167'
> ORDER BY A1.OPPORTUNITYID ASC, A1.SPEC_PR_PROD_C ASC, A1.EXPDATE1ST ASC
> This query runs slower than:
> SELECT A1.C_JOBBIDID, A2.ACCOUNTID A2_ACCOUNTID, A1.PROD_PR_GRP_C, A1.WON,
> A2.DESCRIPTION A2_DESCRIPTION, A1.OPPORTUNITYID, A3.USERFIELD5
> A3_USERFIELD5,
> A2.STATUS A2_STATUS, A1.SPEC_PR_PROD_C, A1.SPR_ITEM_ALLOW_Q,
> A1.DISTPRICE1ST,
> A1.SUBPRICE1ST, A1.EXPDATE1ST, A1.DISTPRICE2ND, A1.SUBPRICE2ND,
> A1.EXPDATE2ND,
> A1.DISTPRICE3RD, A1.SUBPRICE3RD, A1.EXPDATE3RD, A1.DISTPRICE4TH,
> A1.SUBPRICE4TH, A1.EXPDATE4TH FROM C_JOBBID A1 INNER JOIN OPPORTUNITY A2
> ON (A1.OPPORTUNITYID = A2.OPPORTUNITYID)
> INNER JOIN C_OPPORTUNITY_EXT A3 ON (A2.OPPORTUNITYID = A3.OPPORTUNITYID)
> INNER JOIN C_OPPTOACCOUNT A4 ON (A4.OPPORTUNITYID = A1.OPPORTUNITYID)
> WHERE A4.ACCOUNTID = 'A6UJ9A0069NH' AND ISNULL(A3.METRO, '') IN (SELECT
> PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN') AND
> A1.PROD_PR_GRP_C = 'C167'
> ORDER BY A1.OPPORTUNITYID ASC, A1.SPEC_PR_PROD_C ASC, A1.EXPDATE1ST ASC
> As you can see, the only difference is checking the field for null and
> converting it to a blank string. You'd think the second one involving
> "more work" would take longer. Can someone explain why this is so?
> Thanks in advance.
> Jiho Han
> jihohan@.yahoo.com
>|||Hello Louis,
I am attaching the plan. It's the first time I've used that option so, I di
dn't know if this will be in the right format.
Also, I've tried the EXISTS approach and the result is same (I mean the perf
ormance). The plain option takes ~150ms(duration) vs ISNULL takes ~30ms. S
o it's not a huge difference but
it's significant enough that it's noticeable.
Thank you
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com
> What do you mean, slower? Considerably, or minimally? Can you check
> the plan, or post the plan using SET SHOWPLAN_TEXT ON? It will
> probably reveal the reason if it is considerable.
>
> You might also try rewriting
>
> as
> AND EXISTS ( SELECT 1
> FROM C_USERMETROS
> WHERE C_USERMETROS.USERID = 'ADMIN'
> AND PRICINGMETRO = A3.METRO)
> assuming that there is not a A3.METRO that actually equals ''
>
> "Jiho Han" <jihohan@.yahoo.com> wrote in message
> news:a19ab9b65ef08c806917750fbb2@.msnews.microsoft.com...
>|||ISNULL (the function) is not equivalent to IS NULL (the syntax construct) in
SQL 2000 or 2005 to its optimizer.
You might try to use the latter since it is more completely supported.
Essentially, we're not reasoning about the output distribution on ISNULL().
That may cause suboptimal plans in complex queries.
Conor Cunningham
SQL Server Query Optimization Development Lead
"Jiho Han" <jihohan@.yahoo.com> wrote in message
news:a19ab9b661698c806aa7485348c@.msnews.microsoft.com...
Hello Louis,
I am attaching the plan. It's the first time I've used that option so, I
didn't know if this will be in the right format.
Also, I've tried the EXISTS approach and the result is same (I mean the
performance). The plain option takes ~150ms(duration) vs ISNULL takes
~30ms. So it's not a huge difference but it's significant enough that it's
noticeable.
Thank you
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com
> What do you mean, slower? Considerably, or minimally? Can you check
> the plan, or post the plan using SET SHOWPLAN_TEXT ON? It will
> probably reveal the reason if it is considerable.
> You might also try rewriting
>
> as
> AND EXISTS ( SELECT 1
> FROM C_USERMETROS
> WHERE C_USERMETROS.USERID = 'ADMIN'
> AND PRICINGMETRO = A3.METRO)
> assuming that there is not a A3.METRO that actually equals ''
> "Jiho Han" <jihohan@.yahoo.com> wrote in message
> news:a19ab9b65ef08c806917750fbb2@.msnews.microsoft.com...
>|||Ok, that one just went over me completely.
I understand that ISNULL and IS NULL may not be equivalent - I would hope
not.
What I wanted to know was why
A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN')
runs faster than
ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID
= 'ADMIN')
What I did notice when viewing the execution plan was that while using ISNUL
L
as above caused the query engine to use a hash match, the plain one caused
the engine to use a distinct sort followed by a nested loop.
I am very curious as to why it works this way and to determine whether I
need to employ ISNULL in all my other queries for query performance improvem
ent.
Thanks for your assistance!
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com
> ISNULL (the function) is not equivalent to IS NULL (the syntax
> construct) in SQL 2000 or 2005 to its optimizer.
> You might try to use the latter since it is more completely supported.
> Essentially, we're not reasoning about the output distribution on
> ISNULL(). That may cause suboptimal plans in complex queries.
> Conor Cunningham
> SQL Server Query Optimization Development Lead
> "Jiho Han" <jihohan@.yahoo.com> wrote in message
> news:a19ab9b661698c806aa7485348c@.msnews.microsoft.com... Hello Louis,
> I am attaching the plan. It's the first time I've used that option
> so, I didn't know if this will be in the right format.
> Also, I've tried the EXISTS approach and the result is same (I mean
> the performance). The plain option takes ~150ms(duration) vs ISNULL
> takes ~30ms. So it's not a huge difference but it's significant
> enough that it's noticeable.
> Thank you
> Jiho Han
> Senior Software Engineer
> Infinity Info Systems
> The Sales Technology Experts
> Tel: 212.563.4400 x216
> Fax: 212.760.0540
> jhan@.infinityinfo.com
> www.infinityinfo.com|||It would be interesting to see what the execution plan would by if you
changed that portion of the query to
A3.METRIO IN (SLECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'Admin'
AND PRICINGMETRO IS NOT NULL)
This is is just a wild guess, but maybe SQL is optimizing based on the fact
that ISNULL can't return a null result, and therefore can exclude null
results from the sub-query.
> What I wanted to know was why
> A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN')
> runs faster than
> ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERI
D
> = 'ADMIN')
> What I did notice when viewing the execution plan was that while using ISN
ULL
> as above caused the query engine to use a hash match, the plain one caused
> the engine to use a distinct sort followed by a nested loop.
> I am very curious as to why it works this way and to determine whether I
> need to employ ISNULL in all my other queries for query performance improvement.[/
color]|||Apologies - let me try to rephrase.
The query optimizer uses a tree model of operations that represent your SQL
statement. Additionally, statistical information (such as a histogram of
data for a column) is recorded for base tables and then pushed up through
the tree, being modified during each step. So, if you scan rows from a base
table and then filter, the filter operation would have an estimated output
distribution *after* the filter. This information is used to estimate
cardinality for each operator and to eventually cost various alternatives.
If the cardinality estimate is high, we may pick things like the hash join.
If it's low, we're more likely to pick a nested loops join. So, this
information is very important to picking an efficient plan.
Some constructs do not have full support in the optimizer. When we do not
have that information, we may not be able to come up with a good output
distribution for it, and thus the cardinality and cost may be incorrect.
ISNULL() is such an operator. When this happens, we may pick the loops join
when the hash join would have been better. This is when you see performance
issues.
In this case, you are using it to join with a subquery, and the distribution
is quite important here. I would recommend that you try to avoid this if
you are seeing plan issues. Once you start using the result of the function
is some other operation (a join, a subquery, a filter, etc), then I would
recommend that you consider avoiding it, if possible.
I hope that helps.
Thanks,
Conor
"Jiho Han" <jihohan@.yahoo.com> wrote in message
news:a19ab9b662c18c80742337e046d@.msnews.microsoft.com...
> Ok, that one just went over me completely.
> I understand that ISNULL and IS NULL may not be equivalent - I would hope
> not.
> What I wanted to know was why
> A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN')
> runs faster than
> ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE
> USERID = 'ADMIN')
> What I did notice when viewing the execution plan was that while using
> ISNULL as above caused the query engine to use a hash match, the plain one
> caused the engine to use a distinct sort followed by a nested loop.
> I am very curious as to why it works this way and to determine whether I
> need to employ ISNULL in all my other queries for query performance
> improvement.
> Thanks for your assistance!
> Jiho Han
> Senior Software Engineer
> Infinity Info Systems
> The Sales Technology Experts
> Tel: 212.563.4400 x216
> Fax: 212.760.0540
> jhan@.infinityinfo.com
> www.infinityinfo.com
>
>|||The option you specified came back slower actually. ~250 ms.
I've done several variations using IS NULL:
A3.METRO IS NOT NULL AND A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS
WHERE USERID = 'ADMIN')
A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID = 'ADMIN'
AND PRICINGMETRO IS NOT NULL)
A3.METRO IS NOT NULL AND IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE
USERID = 'ADMIN' AND PRICINGMETRO IS NOT NULL)
They are all slower and are around ~250 ms. It seems to me that by adding
IS NOT NULL check, it's actually adding more work.
Nothing beats:
ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID
= 'ADMIN')
which consistently executes around ~30ms.
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com
> It would be interesting to see what the execution plan would by if you
> changed that portion of the query to
> A3.METRIO IN (SLECT PRICINGMETRO FROM C_USERMETROS WHERE USERID =
> 'Admin' AND PRICINGMETRO IS NOT NULL)
> This is is just a wild guess, but maybe SQL is optimizing based on the
> fact that ISNULL can't return a null result, and therefore can exclude
> null results from the sub-query.
>|||Thanks Conor, that was very helpful although I can't say that I can absorb
everything you've said.
Just to be clear, and since I am wondering whether there is a misunderstandi
ng
here, I am reporting that the use of ISNULL() is performing better than the
lack of, or using IS NULL. If you see my other reply, you'll see some numbe
rs
I got trying to use IS NULL instead and they are all slower, even more so
than not using IS NULL.
Also, could you rephrase your last paragraph? Are you saying that I should
avoid using subqueries? or ISNULL()?
Are you suggesting that I use ISNULL() only in SELECT clause in your last
statement?
Thanks
Jiho
> Apologies - let me try to rephrase.
> The query optimizer uses a tree model of operations that represent
> your SQL statement. Additionally, statistical information (such as a
> histogram of data for a column) is recorded for base tables and then
> pushed up through the tree, being modified during each step. So, if
> you scan rows from a base table and then filter, the filter operation
> would have an estimated output distribution *after* the filter. This
> information is used to estimate cardinality for each operator and to
> eventually cost various alternatives. If the cardinality estimate is
> high, we may pick things like the hash join. If it's low, we're more
> likely to pick a nested loops join. So, this information is very
> important to picking an efficient plan.
> Some constructs do not have full support in the optimizer. When we do
> not have that information, we may not be able to come up with a good
> output distribution for it, and thus the cardinality and cost may be
> incorrect. ISNULL() is such an operator. When this happens, we may
> pick the loops join when the hash join would have been better. This
> is when you see performance issues.
> In this case, you are using it to join with a subquery, and the
> distribution is quite important here. I would recommend that you try
> to avoid this if you are seeing plan issues. Once you start using the
> result of the function is some other operation (a join, a subquery, a
> filter, etc), then I would recommend that you consider avoiding it, if
> possible.
> I hope that helps.
> Thanks,
> Conor
> "Jiho Han" <jihohan@.yahoo.com> wrote in message
> news:a19ab9b662c18c80742337e046d@.msnews.microsoft.com...
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:587012
Jiho Han wrote:
> Thanks Conor, that was very helpful although I can't say that I can absorb
> everything you've said.
> Just to be clear, and since I am wondering whether there is a misunderstan
ding
> here, I am reporting that the use of ISNULL() is performing better than th
e
> lack of, or using IS NULL. If you see my other reply, you'll see some num
bers
> I got trying to use IS NULL instead and they are all slower, even more so
> than not using IS NULL.
> Also, could you rephrase your last paragraph? Are you saying that I shoul
d
> avoid using subqueries? or ISNULL()?
> Are you suggesting that I use ISNULL() only in SELECT clause in your last
> statement?
> Thanks
> Jiho
I am not Conor, but I will answer it anyway.
The advice of Conor is to avoid using functions/expressions in
combination with subqueries, because the optimizer will have better
information if you only use the column.
However, if I understand you correctly, in your case, the optimizer is
picking a faster plan for the ISNULL() query. If I undestand you
correctly, the query
AND ISNULL(A3.METRO, '') IN (SELECT PRICINGMETRO FROM C_USERMETROS
WHERE USERID = 'ADMIN')
uses a hash match to finish in ~30ms
and the query
AND A3.METRO IN (SELECT PRICINGMETRO FROM C_USERMETROS WHERE USERID =
'ADMIN')
uses a distinct sort and nested loop to finish in ~150ms
So although the optimizer has better information for the second query,
it actually picks a query plan that performs worse. Aparently, in your
situation, the hash match is the fastest solution. Maybe the optimizer
is unable to accurately estimate the output of the subquery (maybe it
returns more distinct values than expected).
You could also try to rewrite it differently, and see if that helps.
For example, you could try this:
AND EXISTS (
SELECT *
FROM C_USERMETROS
WHERE USERID = 'ADMIN'
AND PRICINGMETRO = A3.METRO
)
If the subquery returns unique values for PRICINGMETRO, then you could
also try this:
INNER JOIN C_USERMETROS
ON USERID = 'ADMIN'
AND PRICINGMETRO = A3.METRO
If the optimizer is actually misjudging the result from the subquery,
then it could help if you added an index on C_USERMETROS (USERID,
PRICINGMETRO).
HTH,
Gert-Jan
Showing posts with label regarding. Show all posts
Showing posts with label regarding. Show all posts
Friday, March 23, 2012
Tuesday, March 20, 2012
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.
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
Hi,
I have some questions regarding query performances:
Here is a commented set of queries against a Table called ADDRESS.
I am using a daft sub query to highlight a point.
Any help much appreciated!
Tris
sp_helpindex Address
/*
index_name index_description
index_keys
----
--
ADDRESS.COUNTY nonclustered located on PRIMARY
County
ADDRESS.POSTCODE nonclustered located on PRIMARY
PostCode
ADDRESS.TOWN nonclustered located on PRIMARY
Town
*/
-- Using no Variable, executes in 0 seconds
select * from address where postcode in
(Select postCode from Address where postcode like 'KT22 7E%')
-- Using a variable ruins index usage and executes in about 15 seconds.
declare @.PC varchar(8)
set @.PC = 'KT22 7E%'
select * from address where (postcode in
(Select postCode from Address where postcode like @.PC))
-- Adding an index hint goes back to 0 seconds
declare @.PC varchar(8)
set @.PC = 'KT22 7E%'
select * from address with (index = [ADDRESS.POSTCODE]) where (postcode
in
(Select postCode from Address where postcode like @.PC))
-- Adding an OR ruins it completely running at about 1 minute!
declare @.PC varchar(8)
set @.PC = 'KT22 7E%'
select * from address with (index = [ADDRESS.POSTCODE]) where (postcode
in
(Select postCode from Address where postcode like @.PC) or @.PC = '')
-- WHY?Hi
Check out statistics on the column. Create a SP which accepts this value as
a parameter and see what is going on.
<Tris.Phillips@.gmail.com> wrote in message
news:1139834636.524975.39380@.g14g2000cwa.googlegroups.com...
> Hi,
> I have some questions regarding query performances:
> Here is a commented set of queries against a Table called ADDRESS.
> I am using a daft sub query to highlight a point.
> Any help much appreciated!
> Tris
>
> sp_helpindex Address
> /*
> index_name index_description
> index_keys
>
> ----
--
> ADDRESS.COUNTY nonclustered located on PRIMARY
> County
> ADDRESS.POSTCODE nonclustered located on PRIMARY
> PostCode
> ADDRESS.TOWN nonclustered located on PRIMARY
> Town
> */
> -- Using no Variable, executes in 0 seconds
> select * from address where postcode in
> (Select postCode from Address where postcode like 'KT22 7E%')
> -- Using a variable ruins index usage and executes in about 15 seconds.
> declare @.PC varchar(8)
> set @.PC = 'KT22 7E%'
> select * from address where (postcode in
> (Select postCode from Address where postcode like @.PC))
> -- Adding an index hint goes back to 0 seconds
> declare @.PC varchar(8)
> set @.PC = 'KT22 7E%'
> select * from address with (index = [ADDRESS.POSTCODE]) where (postcode
> in
> (Select postCode from Address where postcode like @.PC))
> -- Adding an OR ruins it completely running at about 1 minute!
> declare @.PC varchar(8)
> set @.PC = 'KT22 7E%'
> select * from address with (index = [ADDRESS.POSTCODE]) where (postcode
> in
> (Select postCode from Address where postcode like @.PC) or @.PC = '')
>
> -- WHY?
>|||Im sorry, Im not sure what you mean?
The crux of the question is : why does adding the OR make the query so
slow?
Thanks.
Tris|||Take a look at all execution plans and compare them
Try to avoid using '*' in your queries it may hurt your performance
<Tris.Phillips@.gmail.com> wrote in message
news:1139835301.074781.95290@.g44g2000cwa.googlegroups.com...
> Im sorry, Im not sure what you mean?
> The crux of the question is : why does adding the OR make the query so
> slow?
> Thanks.
> Tris
>|||It would appear that replacing the OR with an AND and reversing the
logic fixes the issue.
-- Changing an OR to an AND (and reversing logic) fixes it!
declare @.PC varchar(8)
set @.PC = 'KT22 7E%'
select * from address with (index = [ADDRESS.POSTCODE]) where ((@.PC is
not null) AND (postcode in
(Select postCode from Address where postcode like @.PC)))
VERY STRANGE?|||The two where clauses (and vs. or) are not equal.
The first one (using or) is selecting all record in the list of postcodes,
PLUS all records where the postcode variable is an empty string.
The second (using and) is selecting all records in the list of postcodes,
ONLY if the postcode variable is not null.
where
postcode in (Select postcode from Address where postcode like @.PC)
or @.PC = ''
where
@.PC is not null
AND postcode in (Select postcode from Address where postcode like @.PC)
@.PC = ''
and
@.PC is not null
are completely different comparisons. They are neither similar nor
opposite, so you really can't compare how one behaves vs. the other.
Also, in each of those comparisons you are comparing a variable to a value,
not a field to a value. It will not behave the same way if you substitute
an actual field in place of the variable, so your tests are probably not
testing what you want.
Regarding your original question, you should expect OR to slow down queries
as it requires the query engine to look at more rows in order to make a
decision. Granted, OR will not always slow things down, but used the wrong
way it can bring things to a grinding halt.
In the case of your query, I would guess that SQL server is using an index
without the OR and retrieving just the rows where postal code is a match.
Once you add the OR it is checking every single row, since the index is no
longer a valid way of determining which rows meet the criteria. The in
clause allows the index to be used, but the [or @.PC = ''] is not even going
against your table, so the index cannot be used against it. Basically, the
optimizer is ignoring the index because it would need to verify every row
against the [or @.PC = ''], so attempting to limit the rows returned would be
pointless.
Logically, we know that [or @.PC = ''] = [or 'KT22 7E%'=''] = [false] and
should be removed, but SQL server seems to be looking more at the [OR ...]
than the fact that it evaluates to false every time.
If you use OR and both sides of the OR are using columns from the same index
then I would expect SQL Server to still process it efficiently. If you use
OR and both sides of the OR are going against the same table, but different
indexes, then I would not expect indexes to be used, unless there was
another criteria (join perhaps) which was not affected by your OR. If,
however, you use OR against two different tables, you can expect SQL Server
to return every single row regardless of whether it matches your criteria,
and only perform the final filter at the end, and your performance will be
very inefficient.
Try checking out some of these sites for more information on performance
tuning...
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
http://www.sql-server-performance.com/
http://www.sql-server-performance.com/q&a132.asp
<Tris.Phillips@.gmail.com> wrote in message
news:1139841641.324707.140870@.f14g2000cwb.googlegroups.com...
> It would appear that replacing the OR with an AND and reversing the
> logic fixes the issue.
> -- Changing an OR to an AND (and reversing logic) fixes it!
> declare @.PC varchar(8)
> set @.PC = 'KT22 7E%'
> select * from address with (index = [ADDRESS.POSTCODE]) where ((@.PC is
> not null) AND (postcode in
> (Select postCode from Address where postcode like @.PC)))
> VERY STRANGE?
>
I have some questions regarding query performances:
Here is a commented set of queries against a Table called ADDRESS.
I am using a daft sub query to highlight a point.
Any help much appreciated!
Tris
sp_helpindex Address
/*
index_name index_description
index_keys
----
--
ADDRESS.COUNTY nonclustered located on PRIMARY
County
ADDRESS.POSTCODE nonclustered located on PRIMARY
PostCode
ADDRESS.TOWN nonclustered located on PRIMARY
Town
*/
-- Using no Variable, executes in 0 seconds
select * from address where postcode in
(Select postCode from Address where postcode like 'KT22 7E%')
-- Using a variable ruins index usage and executes in about 15 seconds.
declare @.PC varchar(8)
set @.PC = 'KT22 7E%'
select * from address where (postcode in
(Select postCode from Address where postcode like @.PC))
-- Adding an index hint goes back to 0 seconds
declare @.PC varchar(8)
set @.PC = 'KT22 7E%'
select * from address with (index = [ADDRESS.POSTCODE]) where (postcode
in
(Select postCode from Address where postcode like @.PC))
-- Adding an OR ruins it completely running at about 1 minute!
declare @.PC varchar(8)
set @.PC = 'KT22 7E%'
select * from address with (index = [ADDRESS.POSTCODE]) where (postcode
in
(Select postCode from Address where postcode like @.PC) or @.PC = '')
-- WHY?Hi
Check out statistics on the column. Create a SP which accepts this value as
a parameter and see what is going on.
<Tris.Phillips@.gmail.com> wrote in message
news:1139834636.524975.39380@.g14g2000cwa.googlegroups.com...
> Hi,
> I have some questions regarding query performances:
> Here is a commented set of queries against a Table called ADDRESS.
> I am using a daft sub query to highlight a point.
> Any help much appreciated!
> Tris
>
> sp_helpindex Address
> /*
> index_name index_description
> index_keys
>
> ----
--
> ADDRESS.COUNTY nonclustered located on PRIMARY
> County
> ADDRESS.POSTCODE nonclustered located on PRIMARY
> PostCode
> ADDRESS.TOWN nonclustered located on PRIMARY
> Town
> */
> -- Using no Variable, executes in 0 seconds
> select * from address where postcode in
> (Select postCode from Address where postcode like 'KT22 7E%')
> -- Using a variable ruins index usage and executes in about 15 seconds.
> declare @.PC varchar(8)
> set @.PC = 'KT22 7E%'
> select * from address where (postcode in
> (Select postCode from Address where postcode like @.PC))
> -- Adding an index hint goes back to 0 seconds
> declare @.PC varchar(8)
> set @.PC = 'KT22 7E%'
> select * from address with (index = [ADDRESS.POSTCODE]) where (postcode
> in
> (Select postCode from Address where postcode like @.PC))
> -- Adding an OR ruins it completely running at about 1 minute!
> declare @.PC varchar(8)
> set @.PC = 'KT22 7E%'
> select * from address with (index = [ADDRESS.POSTCODE]) where (postcode
> in
> (Select postCode from Address where postcode like @.PC) or @.PC = '')
>
> -- WHY?
>|||Im sorry, Im not sure what you mean?
The crux of the question is : why does adding the OR make the query so
slow?
Thanks.
Tris|||Take a look at all execution plans and compare them
Try to avoid using '*' in your queries it may hurt your performance
<Tris.Phillips@.gmail.com> wrote in message
news:1139835301.074781.95290@.g44g2000cwa.googlegroups.com...
> Im sorry, Im not sure what you mean?
> The crux of the question is : why does adding the OR make the query so
> slow?
> Thanks.
> Tris
>|||It would appear that replacing the OR with an AND and reversing the
logic fixes the issue.
-- Changing an OR to an AND (and reversing logic) fixes it!
declare @.PC varchar(8)
set @.PC = 'KT22 7E%'
select * from address with (index = [ADDRESS.POSTCODE]) where ((@.PC is
not null) AND (postcode in
(Select postCode from Address where postcode like @.PC)))
VERY STRANGE?|||The two where clauses (and vs. or) are not equal.
The first one (using or) is selecting all record in the list of postcodes,
PLUS all records where the postcode variable is an empty string.
The second (using and) is selecting all records in the list of postcodes,
ONLY if the postcode variable is not null.
where
postcode in (Select postcode from Address where postcode like @.PC)
or @.PC = ''
where
@.PC is not null
AND postcode in (Select postcode from Address where postcode like @.PC)
@.PC = ''
and
@.PC is not null
are completely different comparisons. They are neither similar nor
opposite, so you really can't compare how one behaves vs. the other.
Also, in each of those comparisons you are comparing a variable to a value,
not a field to a value. It will not behave the same way if you substitute
an actual field in place of the variable, so your tests are probably not
testing what you want.
Regarding your original question, you should expect OR to slow down queries
as it requires the query engine to look at more rows in order to make a
decision. Granted, OR will not always slow things down, but used the wrong
way it can bring things to a grinding halt.
In the case of your query, I would guess that SQL server is using an index
without the OR and retrieving just the rows where postal code is a match.
Once you add the OR it is checking every single row, since the index is no
longer a valid way of determining which rows meet the criteria. The in
clause allows the index to be used, but the [or @.PC = ''] is not even going
against your table, so the index cannot be used against it. Basically, the
optimizer is ignoring the index because it would need to verify every row
against the [or @.PC = ''], so attempting to limit the rows returned would be
pointless.
Logically, we know that [or @.PC = ''] = [or 'KT22 7E%'=''] = [false] and
should be removed, but SQL server seems to be looking more at the [OR ...]
than the fact that it evaluates to false every time.
If you use OR and both sides of the OR are using columns from the same index
then I would expect SQL Server to still process it efficiently. If you use
OR and both sides of the OR are going against the same table, but different
indexes, then I would not expect indexes to be used, unless there was
another criteria (join perhaps) which was not affected by your OR. If,
however, you use OR against two different tables, you can expect SQL Server
to return every single row regardless of whether it matches your criteria,
and only perform the final filter at the end, and your performance will be
very inefficient.
Try checking out some of these sites for more information on performance
tuning...
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
http://www.sql-server-performance.com/
http://www.sql-server-performance.com/q&a132.asp
<Tris.Phillips@.gmail.com> wrote in message
news:1139841641.324707.140870@.f14g2000cwb.googlegroups.com...
> It would appear that replacing the OR with an AND and reversing the
> logic fixes the issue.
> -- Changing an OR to an AND (and reversing logic) fixes it!
> declare @.PC varchar(8)
> set @.PC = 'KT22 7E%'
> select * from address with (index = [ADDRESS.POSTCODE]) where ((@.PC is
> not null) AND (postcode in
> (Select postCode from Address where postcode like @.PC)))
> VERY STRANGE?
>
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.
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.
Friday, March 9, 2012
Query Optimization
Question regarding performance.
If I have a query such as:
Select UserId, Firstname,lastname from members where country='can'
If I were to call this from an .net executable as straight SQL to the Database vs. encapsulating the command in a stored procedure and calling the procedure.
What would be the performance differences? Would their be any issues (outside of security) that would make me choose to place the call in a Procedure?
Thanks
It probably doesn't matter in this case. Executing the SELECT statement using a parameterized command object will provide same benefit as calling SP with slighly more overhead. RPC execution for SP calls provide better performance than sending the entire SQL text. The answer is that it depends on your needs. Changing SQL statements embedded in applications is often harder than modifyin a SP. You can also take a look at the whitepaper below for more information:
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
query optimisation question
I have a quick question regarding a query I'm working on. I was wondering why the first one runs much quicker than the second as I can't understand it myself.
Query 1:
SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1
Query 2:
SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp
WHERE pi.jjobno = :p_job_no
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1
The only difference is that in query 2 I have included a where clause in the subquery. The field jjobno is an indexed field so surely that by specifying an exact jjobno in an index field this would be quicker than specifying nothing? Could someone explain this to me? I'm using oracle version 7.3. Thanks in advance.You have (accidentally I presume) correlated the subquery to the main query in the second version by referring to "pi.jjobno". Alias "pi" is defined in the main query. Perhaps you mean to do this:
SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp pi2
WHERE pi2.jjobno = :p_job_no
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1|||Of course, the hint needs changing now!|||Good spot I missed that completely. Why would I have to change the hint?
thanks,|||Well, either change the hint to use alias pi2, or change the table alias from pi2 to jpost_insp. They have to be the same in both places!
Query 1:
SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1
Query 2:
SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp
WHERE pi.jjobno = :p_job_no
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1
The only difference is that in query 2 I have included a where clause in the subquery. The field jjobno is an indexed field so surely that by specifying an exact jjobno in an index field this would be quicker than specifying nothing? Could someone explain this to me? I'm using oracle version 7.3. Thanks in advance.You have (accidentally I presume) correlated the subquery to the main query in the second version by referring to "pi.jjobno". Alias "pi" is defined in the main query. Perhaps you mean to do this:
SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp pi2
WHERE pi2.jjobno = :p_job_no
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1|||Of course, the hint needs changing now!|||Good spot I missed that completely. Why would I have to change the hint?
thanks,|||Well, either change the hint to use alias pi2, or change the table alias from pi2 to jpost_insp. They have to be the same in both places!
Subscribe to:
Posts (Atom)