Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Optimizing delete SQL
Message
 
 
À
17/02/2011 14:27:08
James Blackburn
Qualty Design Systems, Inc.
Kuna, Idaho, États-Unis
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Versions des environnements
SQL Server:
SQL Server 2008
Application:
Web
Divers
Thread ID:
01500559
Message ID:
01500595
Vues:
46
>In this case, I don't see a lot of queries unless I misunderstand what a hit table is.
>

Take a look at this short blog post

Best Practice: Every table should have a primary key


>>To speed up queries :-)
>>But it takes its price when you insert/delete big amount of data.
>>
>>>Maybe a dumb question but why have a clustered index?
>>>
>>>>>I am looking to see if there could be a way to optimize a delete SQL I have.
>>>>>
>>>>>Basically, I do something like this every 15 minutes:
>>>>>
>>>>>
>>>>>DELETE FROM Hit WHERE AddDate<something
>>>>>
>>>>>
>>>>>As this is executed every 15 minutes, usually, this is executed in a flash. So, all records from the Hit table which are older than the parameter will be removed. The situation is when I run this command on a test server, where this procedure hasn't been executed since a while, the process may take several minutes.
>>>>>
>>>>>Is it because I am working on a date field? If yes, I can do a SQL at first to get the primary key and then remove anything older than that primary key.
>>>>>
>>>>>Or, is it that it is because there are a lot of records? If this is the case, what can I do to optimize?
>>>>
>>>>As I saw from the Execution plat the most part of the operation if for recreating the clustered index.
>>>>Could you try to remove the index first then DELETE and then recreate it?
If it's not broken, fix it until it is.


My Blog
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform