High cost query... - 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)

High cost query...

Author

Topic

xpandre
Posting Yak Master

212 Posts

Posted - 2008-10-23 : 07:13:10

Hi,

I have a proc..which access a table having around 2 crore record..

proc has no input parameter..the query in the proc scans through the database for a specific date..the startdate and enddate is derived within the proc itself..enddate = start date of the current month and tsratdate = dateadd(month,-1,enddate)

Now when i see the estimated cost of the proc, when my proc has in the where clause

table_date >= @startdate and table_date < @enddate

the cost is 1436.

When I change the where clause to

table_date >= getdate()-52 and table_date < getdate()-32

the est cost is 0.014.

I even tried assigning getdate()-22 and getdate()-52 directly to @enddate and @statdate respectively, but still the cost comes to 1495.

The table has an index in the date column.
When I pass the variables to the where clause, it does a full table scan on that 2 crore table..while when I pass getdate()-22 and getdate()-52, it uses the index.

Thanks
Sam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 07:30:35

can you try putting two datetime variables inside and just assign parameter values to them and use them instead in where clause and see if there's any improvement.

xpandre
Posting Yak Master

212 Posts

Posted - 2008-10-23 : 07:39:42

My proc is somethign like this

proc p1 is
DECLARE @BeginDate smalldatetime
DECLARE @EndDate smalldatetime
begin
-- Set EndDate to beginning of the first day of the current month
SET @EndDate = CONVERT(varchar(10), DATEADD(day, 1-DATEPART(day, GETDATE()), GETDATE()), 101)

-- Set BeginDate to beginning of previous month
SET @BeginDate = DATEADD(month, -1, @EndDate)

select a.c1,b.c2
from a,b
where a.id - b.id and
a.date >= @BeginDate and a.date < @EndDate
end

Can you tell what you asked me to change in this?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-23 : 07:40:30

Hi Visakh16

I think he said that the stored proc has no input parameters. So parameters sniffing can't be an issue here?

xpandre: Can you post the code of your sp?

Regards,

NB: Nevermind, you already did when I was writing this!

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-23 : 07:43:41

What happens if you change the datatype of your variables to DATETIME rather than SMAILLDATETIME?

GETDATE returns a DATETIME so if your query was returning the right results with GETDATE in the where clause it's worth a try.

If the columns on your tables are DATETIME then that may explain why GETDATE() uses the index and your variables do not.

Regards,

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-23 : 07:55:06

Also

is it supposed to be

WHERE
a.[Id] = b.[Id] (Inner Join)

Rather than
WHERE
a.[Id] - b.[Id]

Or is that some sort of old school join I'm unaware of?

-------------
Charlie

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-23 : 08:32:12

Also beginning of the month can be set
SET @EndDate = dateadd(month,datediff(month,0,getdate()),0)

Madhivanan

Failing to plan is Planning to fail

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-23 : 08:56:31

Why would it do a table scan for getdate() and an index seek for getdate()-something??

Do you have a clustered or a non clustered index on the column ?? I am thinking you have a non clustered. Is this right ?

xpandre
Posting Yak Master

212 Posts

Posted - 2008-10-23 : 09:02:08

No..it does a index seek for getdate() as well as getdate()-something..

It does a fts when I use the variable instead of getdate() directly..

Yes..its a a non clustered index..

I tried it with smalldatetime as well...no luck:(

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-10-23 : 09:18:01

Have you tested by forcing the index?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 09:58:39

quote:
Originally posted by Transact Charlie

Hi Visakh16

I think he said that the stored proc has no input parameters. So parameters sniffing can't be an issue here?

xpandre: Can you post the code of your sp?

Regards,

NB: Nevermind, you already did when I was writing this!

-------------
Charlie

sorry i missed that. thought it was parameter seeing @startdate,@endate...
anyways thanks for pointing out.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-23 : 10:44:06

quote:
Originally posted by xpandre

No..it does a index seek for getdate() as well as getdate()-something..

It does a fts when I use the variable instead of getdate() directly..

Yes..its a a non clustered index..

I tried it with smalldatetime as well...no luck:(

I believe it's right what Charly said.
Assuming that the columns in your table are DATETIME.
Index is build with DATETIME values.
Your @vars are SMALLDATETIME.
SMALLDATETIME cannot be exactly converted to DATETIME.
But DATETIME can be converted to SMALLDATETIME.
In this case (converting DATETIME to SMALLDATETIME) there is no chance to use the index!
Change your @vars to DATETIME would help.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

xpandre
Posting Yak Master

212 Posts

Posted - 2008-10-29 : 06:19:15

Sorry for late reply..

I tried with datetime, smalldatetime..but still the performance was bad..

I had actually given you guys only part of the where clause...sorry for that...its actually something like this

select a.c1,b.c2
from a,b
where a.id - b.id and
a.date >= @BeginDate and a.date < @EndDate and col1 = 3 and col2 = 6 and col3 = 5

All columns in the where clause has a individual non-clustered index on it...
So I suppose it was using just one of the index..and using that index rowid and doing a full table scan for other columns.(just a thought..not very sure).

Only option remaining was to create a composite non-clustered index on all the columns in the where clause...because of which now the proc runs inside 15 secs:-).

Thanks for all the replies...
Sam

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-10-29 : 12:09:29

quote:
Originally posted by xpandre

Sorry for late reply..

I tried with datetime, smalldatetime..but still the performance was bad..

I had actually given you guys only part of the where clause...sorry for that...its actually something like this

select a.c1,b.c2
from a,b
where a.id - b.id and
a.date >= @BeginDate and a.date < @EndDate and col1 = 3 and col2 = 6 and col3 = 5

All columns in the where clause has a individual non-clustered index on it...
So I suppose it was using just one of the index..and using that index rowid and doing a full table scan for other columns.(just a thought..not very sure).

Only option remaining was to create a composite non-clustered index on all the columns in the where clause...because of which now the proc runs inside 15 secs:-).

Thanks for all the replies...
Sam

Can you please explain this:
where a.id - b.id and

Never seen that before in SQL...
Or it should be: where a.id = b.id and

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-29 : 12:19:51

quote:
Originally posted by webfred

Can you please explain this:
where a.id - b.id and

Never seen that before in SQL...
Or it should be: where a.id = b.id and

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

seems like a typo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources