Performance Issue - 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

General SQL Server Forums

New to SQL Server Programming

Performance Issue

Author

Topic

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-12 : 12:11:39

I have 2 tables of the same structure. One is a cumulative table(PACTG) and the other is a Daily table(PACTGDAILY).
I'm trying to populate the Daily table with all records from the cumulative table for the current day.
Both the tables have a Primary Key which is nothing but a combination of Date,Time and Some other information which we dont have. So I will access it in this way.

SELECT *
FROM PACTG WITH (NOLOCK)
WHERE (ACTG_KEY4 >= @FROM_DATE + @FROM_TIME + '00000000' + '00000000' + '00000000'
AND ACTG_KEY4 <= @TO_DATE + @TO_TIME + '99999999' + '99999999' + '99999999' AND COMPANY_CODE IN ('01','02') )

This query takes about 15-20 minutes to execute against more than a million records.

However when I hard-code the From date and From Time like below,

SELECT *
FROM PACTG WITH (NOLOCK)
WHERE (ACTG_KEY4 >= '20090205' + '01315171' + '00000000' + '00000000' + '00000000'
AND ACTG_KEY4 <= @TO_DATE + @TO_TIME + '99999999' + '99999999' + '99999999' AND COMPANY_CODE IN ('01','02') )

It takes only about 10 seconds.

This is how i get the FROM_date and FROM_time.

SELECT @FROM_DATE = DATE_REVERSED,
@FROM_TIME = REPLICATE('0',8-LEN(TIME_REVERSED)) + SUBSTRING(CAST(TIME_REVERSED AS CHAR(8)),1,LEN(TIME_REVERSED))
FROM PACTG WITH (NOLOCK)
WHERE SUBSTRING(ACTG_KEY0,1,14) = '02 '
This query as a standalone takes only about 2 secs.

Can anyone help with this.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 12:15:25

What is the difference in EXECUTION PLAN?

Doron
Starting Member

6 Posts

Posted - 2009-02-12 : 12:45:01

It seems that the SQL Server engine is running the string connection seperately for each of the million rows.

Therefore, I suggest to use another variable:

DECLARE @FROM_STR AS varchar(100), @TO_STR AS varchar(100);
SET @FROM_STR = @FROM_DATE + @FROM_TIME + '00000000' + '00000000' + '00000000';
SET @TO_STR = @TO_DATE + @TO_TIME + '99999999' + '99999999' + '99999999';

SELECT *
FROM PACTG WITH (NOLOCK)
WHERE (ACTG_KEY4 >= @FROM_STR AND ACTG_KEY4 <= @TO_STR AND COMPANY_CODE IN ('01','02') )

Doron Saar,

Nob Hill Software - tools for database people (+ free stuff!) Nob Hill Software - tools for database people (+ free stuff!)
www.nobhillsoft.com

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-12 : 13:22:34

Sodeep, The SELECT with hardcoded values result in a Index Seek and Key Look up however the the one with the variables used has a Clustered Indes Scan followed by Parallelism(Gather Streams). Any ideas?

And Doron, using another variable doesn't help. It results in the same performance.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 13:34:15

The reason is your hard-coded value is using index on ACTG_KEY4 Column where other one is not using due to String Functions and Expressions .

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-12 : 13:36:53

Could also be parameter sniffing - see an explaination in this similar thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118807&SearchTerms=execution

Be One with the Optimizer
TG

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-12 : 15:48:51

TG, I'm getting this issue even when i run it as two simple selects. Could that also be due to parameter sniffing? I was under the impression that parameter sniffing could cause problems only when I have this as part of this procedure and its cache is not cleared or the proc is not re-compiled.

Anyways, for this issue, I have explicitly used the INDEX in SELECT and the reults are better now

SELECT *
FROM PACTG WITH (NOLOCK,INDEX(ACTGINDX04))
WHERE (ACTG_KEY4 >= @FROM_DATE + @FROM_TIME + '00000000' + '00000000' + '00000000'
AND ACTG_KEY4 <= @TO_DATE + @TO_TIME + '99999999' + '99999999' + '99999999' AND COMPANY_CODE IN ('01','02') )

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-02-12 : 16:13:27

>>Could that also be due to parameter sniffing
Well it may not be called "parameter sniffing" but I have seen cases when constants vs. variables result in different execution plans outside the context of a stored procedure. Plus you've complicated the scenario by making your values expressions. Also, not sure what datatype ACTG_KEY4 is but there is probably an implicit conversion happening as well. Each is possible a layer of obfuscation for the optimizer when trying to devise a plan. With a specific statement there can be times when the developer knows best and should use an index hint.

Be One with the Optimizer
TG

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 16:35:12

quote:
Originally posted by vijayisonly

Sodeep, The SELECT with hardcoded values result in a Index Seek and Key Look up however the the one with the variables used has a Clustered Indes Scan followed by Parallelism(Gather Streams). Any ideas?

And Doron, using another variable doesn't help. It results in the same performance.

You could enhance your performance with Covering Index for your search columns as Plan shows Book-mark lookup? Test it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources