Help with slow query please...

I've inherited a piece of SQL that takes an age to run (it's dynamically
created, not in a stored procedure)
---
SELECT
DISTINCT tableA.fieldA,
tableA.fieldB
FROM
tableA
INNER JOIN tableB
ON tableB.fieldA =
tableA.fieldA
INNER JOIN tableC
ON tableC.fieldC =
tableB.fieldC
WHERE
tableC.fieldD IN ('para1', 'para2')
AND
tableC.fieldE = 'para3'
AND
ISNULL(tableC.fieldF, 0) = 0
ORDER BY
tableA.fieldB
---
I've checked the database when this runs and it shows object locks on tableB
and tableC (at the page and table level).
I changed the SQL so that each table name in the FROM clause has the 'WITH
(NOLOCK)' hint on, but this led to no obvious improvement, and the object
locks remained the same.
So, I looked at the execution plan. Nothing horrific...one nested loop
(55%) and one table spool/lazy spool (32%).
So, I ran an index analysis.
This suggested that I create the following index:
CREATE NONCLUSTERED INDEX [tableB_blah] ON [dbo].[tableB] ([fieldC] ASC,
[fieldA] ASC )
However, when I check tableB, it already has the following indexes:
1 - clustered index on fieldC
2 - non-clustered index on fieldA and fieldC
Okay, the for the existing non-clustered index, the left and right columns
have been swapped, and I know that the left column is the all important on,
but I'm not sure what to do here...
I could either:
a - re-jig the non-clustered index to swap the columns around, but I've no
idea whether this will improve things and presumably it may break it for
other queries?
b - remove the index altogether and replace it with a single column index
for fieldA (as there's already a single columned clustered index on fieldC).
Suggestions please....
Thanks
GriffTry,
SELECT DISTINCT
tableA.fieldA,
tableA.fieldB
FROM
tableA
INNER JOIN
tableB
ON tableB.fieldA = tableA.fieldA
INNER JOIN
tableC
ON tableC.fieldC = tableB.fieldC
and tableC.fieldD IN ('para1', 'para2')
AND tableC.fieldE = 'para3'
AND (tableC.fieldF = 0 or tableC.fieldF is null)
ORDER BY
tableA.fieldB;
Post tables definition including primary keys, DRI constraints and indexes
for a better analysis.
AMB
"Griff" wrote:

> I've inherited a piece of SQL that takes an age to run (it's dynamically
> created, not in a stored procedure)
> ---
> SELECT
> DISTINCT tableA.fieldA,
> tableA.fieldB
> FROM
> tableA
> INNER JOIN tableB
> ON tableB.fieldA =
> tableA.fieldA
> INNER JOIN tableC
> ON tableC.fieldC =
> tableB.fieldC
> WHERE
> tableC.fieldD IN ('para1', 'para2')
> AND
> tableC.fieldE = 'para3'
> AND
> ISNULL(tableC.fieldF, 0) = 0
> ORDER BY
> tableA.fieldB
> ---
> I've checked the database when this runs and it shows object locks on tabl
eB
> and tableC (at the page and table level).
> I changed the SQL so that each table name in the FROM clause has the 'WITH
> (NOLOCK)' hint on, but this led to no obvious improvement, and the object
> locks remained the same.
> So, I looked at the execution plan. Nothing horrific...one nested loop
> (55%) and one table spool/lazy spool (32%).
> So, I ran an index analysis.
> This suggested that I create the following index:
> CREATE NONCLUSTERED INDEX [tableB_blah] ON [dbo].[tableB] ([fieldC] ASC,
> [fieldA] ASC )
> However, when I check tableB, it already has the following indexes:
> 1 - clustered index on fieldC
> 2 - non-clustered index on fieldA and fieldC
> Okay, the for the existing non-clustered index, the left and right columns
> have been swapped, and I know that the left column is the all important on
,
> but I'm not sure what to do here...
> I could either:
> a - re-jig the non-clustered index to swap the columns around, but I've no
> idea whether this will improve things and presumably it may break it for
> other queries?
> b - remove the index altogether and replace it with a single column index
> for fieldA (as there's already a single columned clustered index on fieldC
).
> Suggestions please....
> Thanks
> Griff
>
>|||Griff wrote:
> ISNULL(tableC.fieldF, 0) = 0
This is a problem right here. Never apply a function against a table column
in the WHERE clause: it forces a table scan. Unfortunately in this case
(where you want either 0 or Null), it's going to be tough to create a
sargable criterion. You can try
(tableC.fieldF = 0 OR tableC.fieldF IS NULL)
but I suspect this will be non-sargable as well (due to the OR condition)
You can also try a subquery containing a union:
SELECT
DISTINCT tableA.fieldA,
tableA.fieldB
FROM
tableA
INNER JOIN tableB
ON tableB.fieldA = tableA.fieldA
INNER JOIN (
SELECT fieldC,fieldD,fieldE
FROM TableC
WHERE fieldF IS NULL
UNION ALL
SELECT fieldC,fieldD,fieldE
FROM TableC
WHERE fieldF = 0) C
ON C.fieldC =tableB.fieldC
WHERE
tableC.fieldD IN ('para1', 'para2')
AND
tableC.fieldE = 'para3'
ORDER BY
tableA.fieldB
Of course, the DISTINCT keyword isn't helping performance. Is there any way
to eliminate that?
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Tried your suggestion, but it actually took a bit longer...(3 minutes cf 2.5
minutes)
However, now got it down to sub 1 second by re-ordering the inner joins!
I think that this is due to the fact that first time I was getting a small
amount of data in tableA and inner joining it with a huge amount of data in
tableB (and a fairly huge amount of data in tableC).
I changed the order so that it now gets the big table first and inner joins
it with the smaller table. I'm not sure WHY it's faster as it's got to be
doing the same net amount of work, but the query plan is significantly
different.
Now, the only thing left to do is to change the ASP code that generates the
dynamic ASP code.
Thanks everyone...
Griff|||Have you consider running DBCC DBREINDEX and/or DBCC SHOWCONTIG against the
largest tables + DBCC INDEXDEFRAG?
"Griff" wrote:

> I've inherited a piece of SQL that takes an age to run (it's dynamically
> created, not in a stored procedure)
> ---
> SELECT
> DISTINCT tableA.fieldA,
> tableA.fieldB
> FROM
> tableA
> INNER JOIN tableB
> ON tableB.fieldA =
> tableA.fieldA
> INNER JOIN tableC
> ON tableC.fieldC =
> tableB.fieldC
> WHERE
> tableC.fieldD IN ('para1', 'para2')
> AND
> tableC.fieldE = 'para3'
> AND
> ISNULL(tableC.fieldF, 0) = 0
> ORDER BY
> tableA.fieldB
> ---
> I've checked the database when this runs and it shows object locks on tabl
eB
> and tableC (at the page and table level).
> I changed the SQL so that each table name in the FROM clause has the 'WITH
> (NOLOCK)' hint on, but this led to no obvious improvement, and the object
> locks remained the same.
> So, I looked at the execution plan. Nothing horrific...one nested loop
> (55%) and one table spool/lazy spool (32%).
> So, I ran an index analysis.
> This suggested that I create the following index:
> CREATE NONCLUSTERED INDEX [tableB_blah] ON [dbo].[tableB] ([fieldC] ASC,
> [fieldA] ASC )
> However, when I check tableB, it already has the following indexes:
> 1 - clustered index on fieldC
> 2 - non-clustered index on fieldA and fieldC
> Okay, the for the existing non-clustered index, the left and right columns
> have been swapped, and I know that the left column is the all important on
,
> but I'm not sure what to do here...
> I could either:
> a - re-jig the non-clustered index to swap the columns around, but I've no
> idea whether this will improve things and presumably it may break it for
> other queries?
> b - remove the index altogether and replace it with a single column index
> for fieldA (as there's already a single columned clustered index on fieldC
).
> Suggestions please....
> Thanks
> Griff
>
>|||Hi Bob
Thanks for the response. As you can see from one of my responses in this
thread, I've actually solved the problem.
Latest incarnation of the SQL simply changed the table from which the first
field is retrieved from:
> SELECT
> DISTINCT tableA.fieldA
To:

> SELECT
> DISTINCT tableB.fieldA
And the query gone from ~3 mins to sub 1 second. Amazing!
Not sure how I would get rid of the DISTINCT clause...it does exactly what
I need it to do.
Anyhow, I've learnt two things from your post [1 - not using a function; 2 -
the word "sargable" (which I had to look up)] so a day definitely not
wasted.
Thx
Griff|||Griff wrote:
> Hi Bob
> Thanks for the response. As you can see from one of my responses in
> this thread, I've actually solved the problem.
> Latest incarnation of the SQL simply changed the table from which the
> first field is retrieved from:
> To:
>
> And the query gone from ~3 mins to sub 1 second. Amazing!
> Not sure how I would get rid of the DISTINCT clause...it does
> exactly what I need it to do.
>
There may be another way to accomplish the same task. Without details about
your database structure, it is impossible to make any suggestions.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Griff,
You did not post DDL (another abbreviation to look up :-)), so we cannot
tell what the keys of the different tables are, and what the real
indexes are.
The fact that changing tableA.fieldA to tableB.fieldA increases the
performance with orders of magnitude indicates that you might not have
properly defined the keys (or unique indexes).
Also, in this case you should pay particular attention to the indexes of
tableC, especially if it contains many rows. Since you dit not post DDL,
I cannot give a real suggestion here.
Gert-Jan
Griff wrote:
> Hi Bob
> Thanks for the response. As you can see from one of my responses in this
> thread, I've actually solved the problem.
> Latest incarnation of the SQL simply changed the table from which the firs
t
> field is retrieved from:
> To:
>
> And the query gone from ~3 mins to sub 1 second. Amazing!
> Not sure how I would get rid of the DISTINCT clause...it does exactly wha
t
> I need it to do.
> Anyhow, I've learnt two things from your post [1 - not using a function; 2
-
> the word "sargable" (which I had to look up)] so a day definitely not
> wasted.
> Thx
> Griffsql