| Author |
Topic |
|
rtutus
Aged Yak Warrior
522 Posts |
 Posted - 2007-09-27 : 11:56:49
|
| Hi,We have a delete query , we run the query and it takes many seconds, the seond time we run the query it takes only 3 seconds (away faster)How do we know what s going on with sql server at a specific moment? weither other requests are making it slow or what exactly causes the random slowdown while runing a query. Someytimes it runs fast and other times it takes for everThanks. |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-09-27 : 11:58:32
|
| Run profiler while running the query to see if any other process is blocking it.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2007-09-27 : 12:05:14
|
| can u give more details pls? where I shoudl look in profiler to see if another process is blocking it?Pls, Any suggestion is more than welcome, thanks a lot. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-27 : 13:05:58
|
"We have a delete query , we run the query and it takes many seconds, the seond time we run the query it takes only 3 seconds (away faster)"That's the opposite way round to your Subject: "query that runs slowly the second time"  Assuming that the first time is slower that may be because of the time to build the query plan 9whcih is then cached for the second time) and the time to retrieve the data, which may then be cached in memory for the second instance.Kristen |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2007-09-27 : 13:14:49
|
quote:
Originally posted by Kristen
"We have a delete query , we run the query and it takes many seconds, the seond time we run the query it takes only 3 seconds (away faster)"That's the opposite way round to your Subject: "query that runs slowly the second time" Kristen
thanks Kristen, I changed the title of the subjectThanks guys |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-27 : 13:19:51
|
| Wouldn't you expect it to run faster the second time, since you already deleted the rows the first time?CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-27 : 13:56:28
|
| I hope that isn't the answer, otherwise I'll feel really stupid! |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-09-27 : 14:17:49
|
| Just sayin'CODO ERGO SUM |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2007-09-27 : 19:24:36
|
quote:
Originally posted by Kristen
I hope that isn't the answer, otherwise I'll feel really stupid!
True, supposed to run faster (according to your posts guys), but we re talking 15 minutes and 45 minutes for a delete query. So that s a lot.Kristen, don t worry, cause if u feel stupid, how should I feel :) :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-27 : 20:08:08
|
</FeelingMuchBetterNow > |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2007-09-28 : 09:33:21
|
| Thanks for the advice:Our environnement is SQL 2000. And here is the query:delete from MyTable1 where ALIQUOTSID in(select MyTable1.ALIQUOTSID from (select MyTable3.FOLDERNO, MyTable3.LOGDATE, Logaliquots.aliquotsid from (select FolderDEL.*, orders.ORDNO from (select FOLDERNO, LOGDATE from FOLDERS where LOGDATE < '2006-01-01') FolderDEL left join orders on FolderDEL.FOLDERNO = orders.FOLDERNO) MyTable3left join Logaliquots on MyTable3.ORDNO = Logaliquots.ORDNO) MyTable2 left join MyTable1 on MyTable2.aliquotsid = MyTable1.aliquotsidwhere MyTable1.ALIQUOTSID is not null)2/ also, what do u think about the query, is it good? my colleague mage it.Thanks for brainstorming |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2007-09-28 : 10:07:15
|
quote:
Originally posted by harsh_athalye
Run profiler while running the query to see if any other process is blocking it.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED"
Another question pls:Please, how do I know if an other process is blocking it, where in the profiler trace can I see that?Thank you |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-28 : 10:59:28
|
| "what do u think about the query, is it good?"I think that all those nested selects make it hard to read, and thus to maintain. And it make sit hard for me to understand what it is doing, and to help ... which may also be considered a bad thing, perhaps!!Can't you just use JOINS instead?Kristen |
|
|
|
rtutus
Aged Yak Warrior
522 Posts |
Posted - 2007-10-01 : 10:43:54
|
| Thanks amigo :) |
|
|
|
|
query that runs faster the second time