Speed Up Creating Temp Table

Question

Speed Up Creating Temp Table

scotsditch

Ten Centuries

Points: 1020
More actions
September 15, 2015 at 11:27 am

#321247

Hi I'm trying to create a temp table using the script below and it's taking forever. I was hoping someone might have some tips on hows I could rewrite the code to speed things up. Thanks in advance.
Select ProductName,
Convert(date, messagereceived) as ProcessDate,
datepart(hour,messagereceived) as ProcessHour,
Pipename,Partnername,
DaTEDIFF(SECOND,MessageCreated,MessageReceived) as TransitTime,
DaTEDIFF(SECOND,MessageReceived,ProcessingStarted) as WaitTime,
DaTEDIFF(SECOND,ProcessingStarted,ProcessingFinished) as ProcessingTime
into #Temp
from Table ml
inner join MessagingProduct mp on ml.MessagingProductId=mp.ProductId
where ml.processingstarted > '2015-09-08' and ml.processingstarted < '2015-09-09'
AND
ml.MessagingSystemId=1

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

scotsditch (9/15/2015)
Hi I'm trying to create a temp table using the script below and it's taking forever. I was hoping someone might have some tips on hows I could rewrite the code to speed things up. Thanks in advance.
Select ProductName,
Convert(date, messagereceived) as ProcessDate,
datepart(hour,messagereceived) as ProcessHour,
Pipename,Partnername,
DaTEDIFF(SECOND,MessageCreated,MessageReceived) as TransitTime,
DaTEDIFF(SECOND,MessageReceived,ProcessingStarted) as WaitTime,
DaTEDIFF(SECOND,ProcessingStarted,ProcessingFinished) as ProcessingTime
into #Temp
from Table ml
inner join MessagingProduct mp on ml.MessagingProductId=mp.ProductId
where ml.processingstarted > '2015-09-08' and ml.processingstarted < '2015-09-09'
AND
ml.MessagingSystemId=1

it's not the creation of the temp table, it's the query itself.

you have two tables there, one of which was obfuscated but had the alias [ml]

we'd probably ask to see what indexes exist on [ml] and your two tables [MessagingProduct]

because of the join criteria, i'd wild guess that an index on ml.processingstarted , maybe with a filter ON MessagingSystemId=1 might help., if one doesn't already exist. what columns to include, i don't know, the columns in the query are not qualified witht he table alias (ml.messagereceived or mp.messagereceived? which table?)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ScottPletcher SSC Guru Points: 101105 More actions · Answer 2

Instead of solving queries against this table one at a time, fix them all in one shot.

Cluster the ml table on processingstarted if that is how you typically query against this table. If you needed to, you could add other columns to the clustering key to make the clustering key unique, but that's not critical initially.

Edit: Rephrased for clarity.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

Also, the following comparison should be >= instead of just >.

where ml.processingstarted > '2015-09-08'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)