| Author |
Topic |
|
nr
SQLTeam MVY
12543 Posts |
 Posted - 2004-02-05 : 12:25:37
|
| Just thought I would share that I am having great fun today trying to optimise a query that has a temp table with 3 million rows which it left joins to 44 tables (ok 12 of these are a single table aliased).It currently crashes the server after 5 or 6 hours due to running out of space in tempdb.Written by the expert in charge of the database design and system architecure here.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-02-05 : 12:28:15
|
| OK, OK. Post it here.Brett will solve it. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-05 : 14:39:48
|
quote:
Originally posted by SamC
OK, OK. Post it here.Bret will solve it.
I'm glad you're talking about someone else....I have 2 t's...Nigel, do you have an expected result count?Is it SQL2k?Do you have the "luxury" to break it up?How big is the box?Brett8-)EDIT: Hey! Take that extra t out.....See the blog? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-05 : 15:05:58
|
| I'm breaking it up using another temp tables and simplifying it.The group by clause had 120 entries - most of which were calculated.I started off by moving the calculations into a temp table.At some point I'm going to come against the problem that I have no idea what this is trying to do.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-02-05 : 15:22:54
|
| If any of you guys feel up to a task, please see if you can help with my new post under the General SQL Server Forum.Regards,DuaneSorry,In case you didn't know Im reffering to this articlehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32308 |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-05 : 16:18:37
|
quote:
Originally posted by ditch
If any of you guys feel up to a task, please see if you can help with my new post under the General SQL Server Forum.Regards,DuaneSorry,In case you didn't know Im reffering to this articlehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32308
Well it's not a cross post....what would you call it?An Advertisement?Brett8-) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-06 : 09:37:42
|
| Yo Nigel,You still playing with spagehtti?What happened?Brett8-) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-06 : 09:45:51
|
| Cut it down a lot.Now running in a loop. For 4000 recs took 6 mins.None of the steps takes more than 9%.Trying it for 24000 recs - if it's linear then have to take another track.Running it per branch and there are 3 at around 700,000 recs.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-06 : 09:51:13
|
quote:
Originally posted by nr
Cut it down a lot.Now running in a loop. For 4000 recs took 6 mins.None of the steps takes more than 9%.Trying it for 24000 recs - if it's linear then have to take another track.Running it per branch and there are 3 at around 700,000 recs.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
6 Hours to 6 minutes!I'd say they're getting there money's worth...For a daily process, over a month, that's a savings of 7.375 days of processing time...Brett8-) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-06 : 10:13:02
|
| That's for 4000 recs - it has to process a total of 2.5 million.At least now it's getting a meaningful execution plan.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-02-06 : 11:08:03
|
| I always look for meaning. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-06 : 16:34:15
|
quote:
Originally posted by SamC
I always look for meaning.
OK....DECLARE @Weekend datetime SELECT @Weekend = 'Feb 6 2004 17:00:00' SELECT DATEDIFF(mi, GetDate(), @Weekend)/60.00 As Hours_till_MargaritavilleThat's got some meaning....Nigel, how many pints after this mess?Brett8-) |
|
|
|
|
Having fun