Query Hangs when Parameters are scaled down.

I have a weird problem in Sql Server 2005 that I need some help on.
Below
you'll find two queries. Both queries are exactly the same; they query
the
same database, query for the same info, same sql statement, same where
clause... The first query (with the longer date range) runs perfectly
against my databases. The
second query is as I've said exactly the same, the only difference is
that
the date range is much smaller and is infact included in the first
query. The
problem with this query is that it seems to churn away but do nothing.
It
seems to execute my query but it never finishes. Last time I cancelled
it after +2 hours. Does anybody why I see this behavior? Is there
something that I can do about it? Since it never seems to finish, I'm
not able to generate a execute plan for it to see what the problem is.
Thanks in advance :P
THE SANE QUERY:
------
select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
sum(T2."TRANSACTION_AMT") AS "c5"
from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT " T2,
"Accounting"."ACCOUNTING"."TIME_DIM" T1,
"Accounting"."ACCOUNTING"."GL_DIM" T4,
"Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
"Accounting"."ACCOUNTING"."LOCATION_SUPER_TABL E" T3 on
substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" =
T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" =
2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
('430', '431') and T2."TRANS_POST_DATE" between '2006-07-01
00:00:00.000' and '2006-12-31 00:00:00.000'
group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
order by 1 asc , 2 asc , 3 asc
------
THE BAD QUERY:
------
select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
sum(T2."TRANSACTION_AMT") AS "c5"
from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT " T2,
"Accounting"."ACCOUNTING"."TIME_DIM" T1,
"Accounting"."ACCOUNTING"."GL_DIM" T4,
"Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
"Accounting"."ACCOUNTING"."LOCATION_SUPER_TABL E" T3 on
substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" =
T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" =
2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
('430', '431') and T2."TRANS_POST_DATE" between '2006-12-01
00:00:00.000' and '2006-12-31 00:00:00.000'
group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
order by 1 asc , 2 asc , 3 asc
P.S.
I have already reindexed the tables, cleared the cached query plans and
updated the statistics with no change in the outcome.
|||swaroop.atre@.gmail.com wrote:
> I have a weird problem in Sql Server 2005 that I need some help on.
> Below
> you'll find two queries. Both queries are exactly the same; they query
> the
> same database, query for the same info, same sql statement, same where
> clause... The first query (with the longer date range) runs perfectly
> against my databases. The
> second query is as I've said exactly the same, the only difference is
> that
> the date range is much smaller and is infact included in the first
> query. The
> problem with this query is that it seems to churn away but do nothing.
> It
> seems to execute my query but it never finishes. Last time I cancelled
> it after +2 hours. Does anybody why I see this behavior? Is there
> something that I can do about it? Since it never seems to finish, I'm
> not able to generate a execute plan for it to see what the problem is.
> Thanks in advance :P
> THE SANE QUERY:
> ------
> select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
> T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
> sum(T2."TRANSACTION_AMT") AS "c5"
> from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT " T2,
> "Accounting"."ACCOUNTING"."TIME_DIM" T1,
> "Accounting"."ACCOUNTING"."GL_DIM" T4,
> "Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
> "Accounting"."ACCOUNTING"."LOCATION_SUPER_TABL E" T3 on
> substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
> where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" =
> T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" =
> 2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
> ('430', '431') and T2."TRANS_POST_DATE" between '2006-07-01
> 00:00:00.000' and '2006-12-31 00:00:00.000'
> group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
> order by 1 asc , 2 asc , 3 asc
> ------
> THE BAD QUERY:
> ------
> select T1."FISCAL_YEAR" AS "c1", T2."DOC_TYPE" AS "c2",
> T3."PJ1_OPER_SECTOR_CODE" AS "c3", count(T2."TRANS_DOC_SFX") AS "c4",
> sum(T2."TRANSACTION_AMT") AS "c5"
> from "Accounting"."ACCOUNTING"."TRANSACTION_DETAIL_FACT " T2,
> "Accounting"."ACCOUNTING"."TIME_DIM" T1,
> "Accounting"."ACCOUNTING"."GL_DIM" T4,
> "Accounting"."ACCOUNTING"."PROJECT_DIM" T5 LEFT OUTER JOIN
> "Accounting"."ACCOUNTING"."LOCATION_SUPER_TABL E" T3 on
> substring(T5."PROJECT_CODE_1",1,4) = substring(T3."PJ1_LOC_CODE",1,4)
> where T2."TIME_KEY" = T1."TIME_KEY" and T2."PROJECT_KEY" =
> T5."PROJECT_KEY" and T2."GL_KEY" = T4."GL_KEY" and T1."FISCAL_YEAR" =
> 2007 and T2."DOC_TYPE" in ('IV', 'WI', 'PC') and T4."GL_ACCT_CODE" in
> ('430', '431') and T2."TRANS_POST_DATE" between '2006-12-01
> 00:00:00.000' and '2006-12-31 00:00:00.000'
> group by T1."FISCAL_YEAR", T2."DOC_TYPE", T3."PJ1_OPER_SECTOR_CODE"
> order by 1 asc , 2 asc , 3 asc
>
Compare the ESTIMATED execution plans for the two queries - what's the
difference between them? Where is the time being spent?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Tracy,
They both have the exact same execution plan (which they should
considering its just a change in the value for a conditional
expression). The expected usage is largely with the lookup for the
transaction data table which uses a clustered index.
Swaroop
Tracy McKibben wrote:
> swaroop.atre@.gmail.com wrote:
> Compare the ESTIMATED execution plans for the two queries - what's the
> difference between them? Where is the time being spent?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||UPDATE:
The execution plans changed once I updated the statistics on the dbases
again.
swaroop.atre@.gmail.com wrote:[vbcol=seagreen]
> Tracy,
> They both have the exact same execution plan (which they should
> considering its just a change in the value for a conditional
> expression). The expected usage is largely with the lookup for the
> transaction data table which uses a clustered index.
> Swaroop
>
> Tracy McKibben wrote:
|||swaroop.atre@.gmail.com wrote:
> UPDATE:
> The execution plans changed once I updated the statistics on the dbases
> again.
>
Did the performance change as well?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Adding in the index on one of the tables altered the performance.
However on two other queries with the same problem they have no effect.
All tables being used are indexed.
Tracy McKibben wrote:
> swaroop.atre@.gmail.com wrote:
> Did the performance change as well?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
|||swaroop.atre@.gmail.com wrote:
> Adding in the index on one of the tables altered the performance.
> However on two other queries with the same problem they have no effect.
> All tables being used are indexed.
Review the execution plans. The queries may "appear" to have the same
problem, i.e. slow performance, but their indexing needs may be totally
different.
A good index will allow the WHERE clause to filter out unwanted rows as
quickly as possible, and may even provide all of the data necessary to
prevent a second trip to the table.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||The query is actually making a single trip to each table that it needs
information from.
The query in a jist gathers information from six tables the biggest one
being 150M records or so with a clustered unique index while the other
5 have non clustered non unique indexes. As long as the query looks for
data for the month of dec it never returns (the date constraint is
contained in the big table) however if i expand the range to include
dec but not just dec it comes back with the data i seek. The execution
plans for the two are very different and I think that might be because
the new one uses the indexes while the old one in the cache dosnt.
Tracy McKibben wrote:
> swaroop.atre@.gmail.com wrote:
> Review the execution plans. The queries may "appear" to have the same
> problem, i.e. slow performance, but their indexing needs may be totally
> different.
> A good index will allow the WHERE clause to filter out unwanted rows as
> quickly as possible, and may even provide all of the data necessary to
> prevent a second trip to the table.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com