Query Tuning -- Where Exists?

Hello World!
The following query takes more than one hour to complete. Assuming the
tables are properly indexed, if this can be tuned for quicker return?
Besides, I wonder if WHERE EXISTS is always/usually more efficient than
INNER JOIN? Can WHERE EXISTS be implemented in this case and how? Many THANK
S.
SELECT DISTINCT count(*)
FROM FACT_SALES fs INNER JOIN DIM_DEPARTMENT dt
ON fs.Dept_ID=dt.Dept_ID INNER JOIN DIM_DATE dd
ON fs.Date_ID=dd.Date_ID INNER JOIN DIM_MINUTE dm
ON fs.Minute_ID=dm.Minute_ID INNER JOIN TLOG t
ON
Convert(datetime,SUBSTRING(t.[Date],3,2)+'/'+SUBSTRING(t.[Date],5,2)+'/20'+LEFT(t.[Date],2))=d
d.DATE_FULL
AND dm.MINUTE_NUMBER_24=t.[Time]
AND dt.HQ_ID=t.HQ_ID
AND dt.Store_ID=t.Store_ID
AND dt.Dept_ID=t.Dept_ID
AND Convert(bigint,fs.Invoice_No)=(
CASE WHEN t.TransactionType='CreditCard' THEN
Convert(bigint,LEFT(t.JoinKey,4) )
ELSE Convert(bigint,t.JoinKey)
END
)
WHERE ISNUMERIC(fs.Invoice_No) = 1 AND ISNUMERIC(LEFT(t.JoinKey,4)) = 1Hi
Have a look at execution plan of the query. Does it use any indexes?
I have my doubt ,because in WHERE condition a LEFT function would not
probably allow to optimyzer to use the index.
How is selective the columns that participate in WHERE condition?
"C TO" <CTO@.discussions.microsoft.com> wrote in message
news:35BD39C0-F97C-4A56-AC84-125B55D8F488@.microsoft.com...
> Hello World!
> The following query takes more than one hour to complete. Assuming the
> tables are properly indexed, if this can be tuned for quicker return?
> Besides, I wonder if WHERE EXISTS is always/usually more efficient than
> INNER JOIN? Can WHERE EXISTS be implemented in this case and how? Many
THANKS.
>
> SELECT DISTINCT count(*)
> FROM FACT_SALES fs INNER JOIN DIM_DEPARTMENT dt
> ON fs.Dept_ID=dt.Dept_ID INNER JOIN DIM_DATE dd
> ON fs.Date_ID=dd.Date_ID INNER JOIN DIM_MINUTE dm
> ON fs.Minute_ID=dm.Minute_ID INNER JOIN TLOG t
> ON
>
Convert(datetime,SUBSTRING(t.[Date],3,2)+'/'+SUBSTRING(t.[Date],5,2)+'/20'+L
EFT(t.[Date],2))=dd.DATE_FULL
> AND dm.MINUTE_NUMBER_24=t.[Time]
> AND dt.HQ_ID=t.HQ_ID
> AND dt.Store_ID=t.Store_ID
> AND dt.Dept_ID=t.Dept_ID
> AND Convert(bigint,fs.Invoice_No)=(
> CASE WHEN t.TransactionType='CreditCard' THEN
> Convert(bigint,LEFT(t.JoinKey,4) )
> ELSE Convert(bigint,t.JoinKey)
> END
> )
> WHERE ISNUMERIC(fs.Invoice_No) = 1 AND ISNUMERIC(LEFT(t.JoinKey,4)) = 1|||Hi
Have you tried to look at the execution plan? You should get a good idea
where/how its spending its time. Optimizing without the plan is rather
difficult.
You can use the SQL Query Analyzer that comes with the Enterprise
Manager's client-side tool set.
-David
C TO wrote:
> Hello World!
> The following query takes more than one hour to complete. Assuming the
> tables are properly indexed, if this can be tuned for quicker return?
> Besides, I wonder if WHERE EXISTS is always/usually more efficient than
> INNER JOIN? Can WHERE EXISTS be implemented in this case and how? Many THA
NKS.
>
> SELECT DISTINCT count(*)
> FROM FACT_SALES fs INNER JOIN DIM_DEPARTMENT dt
> ON fs.Dept_ID=dt.Dept_ID INNER JOIN DIM_DATE dd
> ON fs.Date_ID=dd.Date_ID INNER JOIN DIM_MINUTE dm
> ON fs.Minute_ID=dm.Minute_ID INNER JOIN TLOG t
> ON
> Convert(datetime,SUBSTRING(t.[Date],3,2)+'/'+SUBSTRING(t.[Date],5,2)+'/20'+LEFT(t.[Date],2))
=dd.DATE_FULL
> AND dm.MINUTE_NUMBER_24=t.[Time]
> AND dt.HQ_ID=t.HQ_ID
> AND dt.Store_ID=t.Store_ID
> AND dt.Dept_ID=t.Dept_ID
> AND Convert(bigint,fs.Invoice_No)=(
> CASE WHEN t.TransactionType='CreditCard' THEN
> Convert(bigint,LEFT(t.JoinKey,4) )
> ELSE Convert(bigint,t.JoinKey)
> END
> )
> WHERE ISNUMERIC(fs.Invoice_No) = 1 AND ISNUMERIC(LEFT(t.JoinKey,4)) = 1|||Your query relies on doing calculations to join between tables and to filter
results. Because of this, SQL Server cannot use indexes to do quick lookups
and will instead do a full table scan, running the calculation on each row
to try to get a match. Try taking the calculations from the query and
perhaps using them to produce indexable calculated column(s).
In news:35BD39C0-F97C-4A56-AC84-125B55D8F488@.microsoft.com,
C TO <CTO@.discussions.microsoft.com> said:
> Hello World!
> The following query takes more than one hour to complete. Assuming the
> tables are properly indexed, if this can be tuned for quicker return?
> Besides, I wonder if WHERE EXISTS is always/usually more efficient
> than
> INNER JOIN? Can WHERE EXISTS be implemented in this case and how?
> Many THANKS.
>
> SELECT DISTINCT count(*)
> FROM FACT_SALES fs INNER JOIN DIM_DEPARTMENT dt
> ON fs.Dept_ID=dt.Dept_ID INNER JOIN DIM_DATE dd
> ON fs.Date_ID=dd.Date_ID INNER JOIN DIM_MINUTE dm
> ON fs.Minute_ID=dm.Minute_ID INNER JOIN TLOG t
> ON
>
Convert(datetime,SUBSTRING(t.[Date],3,2)+'/'+SUBSTRING(t.[Date],5,2)+'/20'+L
EFT(t.[Date],2))=dd.DATE_FULL
> AND dm.MINUTE_NUMBER_24=t.[Time]
> AND dt.HQ_ID=t.HQ_ID
> AND dt.Store_ID=t.Store_ID
> AND dt.Dept_ID=t.Dept_ID
> AND Convert(bigint,fs.Invoice_No)=(
> CASE WHEN t.TransactionType='CreditCard' THEN
> Convert(bigint,LEFT(t.JoinKey,4) )
> ELSE Convert(bigint,t.JoinKey)
> END
> )
> WHERE ISNUMERIC(fs.Invoice_No) = 1 AND ISNUMERIC(LEFT(t.JoinKey,4)) =
> 1
Stevesql