need faster query 4

[washaw]

In my little assignment, I am doing some data archival with the data older than 120 days

after I did archiving I delete data from these tables

I use the following query for each tables (they are 10 of 'em)

delete from table1
where DATEDIFF(DAY, CREATED_DATE, GETDATE()) > 120

But it is extreamly slow, Is there any way to make it faster

THanks

 

[gmmastros]

Try this...

delete from table1
where  CREATED_DATE < DateAdd(day, DateDiff(Day, 120, GetDate()), 0)

If this is noticeably faster, and you'd like me to explain why, just let me know.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[AlexCuse]

Out of curiosity, where did this "little assignment" come from?

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[adam0101]

If you would, I'd like to know why.

Adam

http://adameslinger.blogspot.com/

 

[gmmastros]

Ok.

The modified code may not speed up the delete, but it might. If there is an index on the CREATED_DATE column, the original query would NOT use the index to determine which rows to delete. The modified version would use the index.

Whenever you have a where clause that performs a function on column data, you will get a table/index scan. This means that each row will have to be evaluated to see if it should be filtered out or included in the results.

When the query engine can use an index, it will identify the records that need to get deleted a lot faster.

For further reading.... [google]SQL Server Sargable[/google]

Think of it this way. Suppose someone handed you a dictionary and told you to highlight (with a marker) all words that begin with 'tr'. What would you do? You would flip through the pages (very quickly) and find the first word that starts with TR. You would start highlighting them until you get to the first word that does NOT start with TR.

Now, imagine someone hands you a dictionary and says, highlight each word that contains TR. The only way to do this would be to start at the beginning and examine EVERY word. Obviously this would take a lot longer to accomplish.

By making the query sargable, it will use an existing index to identify the records faster, thus causing the entire operation to take less time. This ONLY works if there is an index.

For example, if someone hands you a 'normal' book and told you to highlight the words that start with TR, you would again have to examine every word. Again... this would take a long time to accomplish.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[adam0101]

Ok, I didn't realize you were assuming there was an index on the column. That makes sense.

I'm wondering then, would this be even faster?

Declare @cutOffDate datetime
Set @cutOffDate = DateAdd(day, DateDiff(Day, 120, GetDate()), 0)

delete from table1 where  CREATED_DATE < @cutOffDate

My thinking is that the DateAdd, DateDiff, and GetDate functions would only be executed once instead of for every row. Or is SQL Server smart enough to only execute the functions once?

Adam

http://adameslinger.blogspot.com/

 

[washaw]

Thank you george, It seems that your query perfoms better, and I am plannig to use that

Thanks

 

[gmmastros]

Well... I didn't assume there was an index on the column.

Declare @CutOffDate DateTime
Set 	@CutOffDate = DateAdd(Day, DateDiff(Day, 120, GetDate()), 0)

dbcc freeproccache

Select 	* 
From 	Calendar 
Where 	CalendarDate < @CutOffDate

dbcc freeproccache

Select 	* 
From 	Calendar 
Where 	CalendarDate < DateAdd(Day, DateDiff(Day, 120, GetDate()), 0)

dbcc freeproccache

Select 	* 
From 	Calendar 
where   DATEDIFF(DAY, CalendarDate, GETDATE()) > 120

Showing a SQL Server Trace....
[tt][blue]
Method Duration CPU Reads Writes
----------------- -------- --- ----- ------
Using Your Method 93 31 76 0
MY Method 110 47 76 0
Original Method 187 172 34 0
[/blue][/tt]

With numbers this small, the results are a little unreliable (50,000 rows really isn't enough to test this properly). Clearly, using a sargable where clause makes a big difference. Setting a local variable does appear to improve performance a little.

[!]***[/!] Do NOT use DBCC FreeProcCache on a production server.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[washaw]

clever,

the execution time now went down from 32 mints to 15 mints

more than 50% performance gain

Thank you all,

I didn't forget ur star

 

[SQLDenis]

George you forgot to drop the clean buffers in addition to freeproccache

DBCC DROPCLEANBUFFERS

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

That still seems like a lot of time for a simple delete. Do you have any triggers on the table that might by chance have cursors? We replaced a cursor with a set based trigger and went from 42 minutes to delete 40000 records to 32 seconds.

"NOTHING is more important in a database than integrity." ESquared

 

[washaw]

These tables don't have cursors, they are simply audit tables, most of the tables have 100K rows, the smallest with 80K

Thanks

 

[SQLDenis]

>>These tables don't have cursors

of course not
you might want to look at defragmenting those tables or rebuilding the indexes 100K tables should not take that long to delete some data

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

Of course... if you have too many indexes, then that could cause performance problems too.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

the other question is how wide are these tables?
if you have 60 columns then yeah that will take a while if you have 3 columns it will be a lot faster

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[washaw]

The tables are not that much wide, the max is with 22 columns

 

[SQLDenis]

>>the max is with 22 columns

depends how much data is in the columns of course, the more data the less rows per page/extent

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

I suppose a clustered index on a nonnumeric field could also slow things down in a large delete. But that seems way too long for a delete from a table of that size. Have you run profiler to see if something else is happening that is affecting it? Maybe you are getting some blocking casuing the delay.

"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

Part of the performance gain is not just that the query is sargable (though that's a crucial part) but that the date calculations only have to be performed once rather than on each row. That's a bucketload of CPU over hundreds of thousands of rows...

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]