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.
| Author |
Topic |
|
fundu118
Starting Member
2 Posts |
 Posted - 2011-08-01 : 07:11:28
|
Hi,I've a Jobs table that contains start,stop times and root job ID.I want to delete certain number rows whose stop time is less than particular time. Basically it is a cleanup activity to clear old records.The simple statement,quote:
DELETE from Jobs where stoptime < @deleteDate
would not work because of following reasons.1. A Constraint on the root_job_id column.i.e., root jobs cannot be deleted until all child jobs are deleted.(This could be solved by ordering)2. Sometimes a root job starts inside the time limit and its children span outside the time. From the above query, even if it is ordered, the query fails because few child jobs are outside the scope of the result set.So, I've modified the Query to,quote:
DELETE Jobs FROM(SELECT TOP 100 PERCENT * FROM Jobs WHERE (stoptime < @deleteDate ANDroot_job_id NOT IN (SELECT root_job_id FROM Jobs WHERE stoptime >= @deleteDate AND root_job_id IS NOT NULL)) ORDER BY job_id DESC) Jobs
But it is taking very long time[5 minutes] to execute the query.The result set contains around 25000 records.Most of the tests with 10000 records complete with in 10 seconds.Could someone help me with optimizing the above statement or any other query that works better?Thank you, |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-08-01 : 07:25:44
|
| NOT IN on columns having NOTNULL values run very slow.That part you need to look into.Try replcaing NOT IN with NOT ExistsPBUH |
 |
|
|
fundu118
Starting Member
2 Posts |
Posted - 2011-08-01 : 08:04:30
|
| Sorry, I didn't get it.I need to check whether the root_job_Id previous to @deleteDate exists after that time.i.e., I need to check root_job_id in the same table but with different stoptimes.How can I use Exists here. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-08-01 : 08:09:05
|
| I said use NOT EXISTS instead of NOT IN .PBUH |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-08-01 : 09:32:35
|
| Something like:DELETE J FROM Jobs JWHERE stoptime<@deleteDateAND NOT EXISTS(SELECT * FROM Jobs WHERE root_job_id=J.root_job_id AND stoptime>=@deleteDate) |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-08-01 : 15:28:02
|
| Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you? Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL. Without that basic Netiquette, we cannot help; be polite and we can help you. Be vague and rude, and we will be vague and rude. Oh, rows are not records -- you might want to read a book on RDBMS and SQL. --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-08-01 : 19:32:14
|
quote:
Originally posted by Sachin.Nand
NOT IN on columns having NOTNULL values run very slow.That part you need to look into.Try replcaing NOT IN with NOT ExistsPBUH
Sorry for the typo error.I meant NOT IN works slow on columns having NULL values.PBUH |
|
|
|
|
|
|
|
|
Need Optimization for DELETE statement