index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

index

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-08-16 : 08:17:37

Hi,
Why is it that when I hard code the date in the where clasue, the query runs very fast but if I replace the date with a select (i.e. select max...) then it takes a long time to retrieve data.
How can I optimize this query without needing to hard code the date.
There is a non clustered index on the date field (i.e. dateofdata)

Thanks

SELECT
vw_GcusGetData.DateOfData,
vw_GcusGetData.AccountNumber AS Account,
vw_GcusGetData.CUSIPNumber AS Cusip,
vw_GcusGetData.SettlementDateQuantity AS Quantity
FROM
vw_GcusGetData
WHERE
vw_GcusGetData.DateOfData = '13 aug 2004'
/*(SELECT Max(tDateOfData)
FROM T_Status
WHERE T_Status.nFileTypeID=30)
*/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-16 : 08:27:40

because the select max is a subquery that gets executed for each row you run. so in effect you have n tablescans, where n is the number of rows that your main query returns.

Go with the flow & have fun! Else fight the flow :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-16 : 08:33:54

this should be faster:

SELECT
vw_GcusGetData.DateOfData,
vw_GcusGetData.AccountNumber AS Account,
vw_GcusGetData.CUSIPNumber AS Cusip,
vw_GcusGetData.SettlementDateQuantity AS Quantity
FROM
vw_GcusGetData
inner join
(select Max(T_Status.tDateOfData) as tDateOfData from T_Status where (T_Status.nFileTypeID=30)) t1
on (vw_GcusGetData.DateOfData = t1.tDateOfData)

Go with the flow & have fun! Else fight the flow :)

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-08-16 : 09:15:21

This looks good and I tried it, but it still takes a long time. So I stopped it half way.
Thanks

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-16 : 09:25:45

What about


set nocount on 

declare @yourdate datetime
select @yourdate = Max(tDateOfData) 
FROM T_Status
WHERE T_Status.nFileTypeID=30

set nocount off
SELECT 
vw_GcusGetData.DateOfData, 
vw_GcusGetData.AccountNumber AS Account, 
vw_GcusGetData.CUSIPNumber AS Cusip, 
vw_GcusGetData.SettlementDateQuantity AS Quantity
FROM 
vw_GcusGetData
WHERE 
vw_GcusGetData.DateOfData = @yourdate

-------
Moo. :)

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-08-16 : 09:29:47

That was one of the first things that I tried. It took a long time too :-(
Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-16 : 09:35:20

maybe you should also put an index on the TStatus datefield... that's the one that needs indexing in my opinion.

Go with the flow & have fun! Else fight the flow :)

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-08-16 : 09:59:29

That field does already have a non-clustered index.
Thanks

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-16 : 10:05:46

What you really need is an index on T_Status.nFileTypeID. How many rows does this T_Status table have anyway? Maybe the statistics are grossly out of date, have you tried updating the stats?

OS

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-16 : 10:08:03

so what is a speed you're looking for???

Go with the flow & have fun! Else fight the flow :)

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2004-08-16 : 10:16:02

Well, when the date is hard codes, it takes about 16 seconds, otherwise it just goes on forever

Kristen
Test

22859 Posts

Posted - 2004-08-17 : 06:25:24

I can't see how SQL Server is going to make any distinction between

SELECT * FROM MyTable WHERE MyDate = '01 Aug 2004'

and

DECLARE @MyDate DATETIME
SELECT @MyDate = '01 Aug 2004'
SELECT * FROM MyTable WHERE MyDate = @MyDate

In fact the second should be a bit better because @MyDate is already in DateTime data type.

There is something else at play here ...

You might need to run a Query Plan of each of the two styles and see what it reckons it is going to do (Table Scan or Index Scan)

<Ding!> I wonder if the Index has lousy granularity for the "wrong reason" - e.g. you have loads of NULLs that cause the index to look like its a lousy choice. Whereas on the "hard wired" version SQL can check the index granularity for the actual value being used.

Is SQL Query Plan Cost Estimator that smart, or am I dreaming?

Kristen

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-17 : 07:07:51

Is "DateOfData" a datetime field?

-------
Moo. :)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-17 : 07:33:24

set nocount on

declare @yourdate datetime
select @yourdate = Max(tDateOfData)
FROM T_Status
WHERE T_Status.nFileTypeID=30

set nocount off
SELECT
vw_GcusGetData.DateOfData,
vw_GcusGetData.AccountNumber AS Account,
vw_GcusGetData.CUSIPNumber AS Cusip,
vw_GcusGetData.SettlementDateQuantity AS Quantity
FROM
vw_GcusGetData
WHERE
vw_GcusGetData.DateOfData = @yourdate

Run this piece again. When you run it pull up Profiler and track statements completed. Limit by the SPID. If you run it in query analyzer, before you execute the script, you can run SELECT @@SPID to capture the SPID.

Which piece runs the longest? When you look at the execution plan, are there any table scans at all when you execute the code? What about seeks vs. scans?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources