Ron, Sorry, I should have been more clear. My test ran for 7 minutes and my test processes far fewer transaction records than the actual production run. The actual production run takes 5 hours and processes about 700,000 transactions. Regards, Mike On Mon, Feb 7, 2011 at 10:03 PM, Ron Crisco <ron.crisco@xxxxxxxxxxxx> wrote: > Michael, > > If the entire task takes 5 hours to complete, and this one query only > takes 7 minutes at worst case, why are you focusing on optimizing this > query? > I would advise looking at a trace of the entire task and finding out > what was happening during the other 293 minutes. > > Of course, it's very possible that the same problem will show itself > with all of the other queries in that task, but why guess? > > Ron Crisco > > > >> Then I run the SQL, but the first time I run it, it can take as much as > 7 > >> minutes. On the 2nd, 3rd, and 4th runs, it takes > >> 40 sec, 49 sec, 35 sec respectively. > >> > >> So my question is: What might account for the huge difference in run > time > >> between the first run and successive runs? > >> > > > I wouldn't worry about the 7 minute run if this SELECT statement ran > several > > times a day, but it does not. It runs once a month and the actual job is > taking > > about 5 hours. I'm afraid that the 7 minute (first time) test is more > analogous > > to what is likely to happen in prod. Anyway, I'll wait until tomorrow > morning and > > see if I can reproduce the 7 min run. >