Is there a better way for this code?

Question

Post reply

Is there a better way for this code?

Wilfred van Dijk

SSCrazy Eights

Points: 8980
More actions
November 12, 2008 at 2:52 am

#196190

The code below does an update on a 17 million rows table (table is from an Axapta 3 database)
updateD_BomCalcTrans_t05_t
setC_ParentRecID =
(
selectTOP 1 a.RecID
fromD_BomCalcTrans_t06_t AS a
wherea.DataAreaId = D_BomCalcTrans_t05_t.DataAreaId
ANDa.BOMID = D_BomCalcTrans_t05_t.BOMID
ANDa.PriceCalcID = D_BomCalcTrans_t05_t.PriceCalcID
ANDa.Level_ = D_BomCalcTrans_t05_t.Level_ - 1
ANDa.LineNum <= D_BomCalcTrans_t05_t.LineNum
ORDERBY a.LineNum Desc
)
whereLevel_ <> 0
I already made a t06 clone which only contains the columns needed, with a clustered index on those columns. But this seems to be a temporary solution, as the processing time is increasing.
Before I had the t06 clone, this update frequently causes SQL to hang. A restart fixed the problem, but that's not a solution for a nightly batch. Server is SQL2005 EE 32bit, SP2, CU9. 6GB internal memory (AWE)
Wilfred
The best things in life are the simple things

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

i think this part of the query is contributing to the slowness:

AND a.LineNum <= D_BomCalcTrans_t05_t.LineNum

because that can't be resolved as a true/false condition, the execution plan is probably using a table scan for the whole 17Million row table.

is it possible to refine? will a.linenum be the minimum line number, or be equal to the other table "D_BomCalcTrans_t05_t.LineNum" minus one? that would help the query a lot.

an index on that column would help, if it's not already there.

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!

krayknot SSCertifiable Points: 7998 More actions · Answer 2

IS there any use of Order By clause here

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Lowell SSC Guru Points: 323482 More actions · Answer 3

his query comes from a different system originally, not all dbms can use the UPDATE...FROM syntax, and instead use the style you see here....set somevalue = ([subquery referenncing the updating table])

and because he's using TOP 1...ORDER BY as part of the query, that's

as far as i know that's a table scan for every row that meets the criteria in the subselect.

it's GOT to be much easier to find the recid.

without knowing anything esel at this point, this is my first guess:

update D_BomCalcTrans_t05_t

set C_ParentRecID = a.RecID

from D_BomCalcTrans_t06_t AS a

where a.DataAreaId = D_BomCalcTrans_t05_t.DataAreaId

AND a.BOMID = D_BomCalcTrans_t05_t.BOMID

AND a.PriceCalcID = D_BomCalcTrans_t05_t.PriceCalcID

AND a.Level_ = D_BomCalcTrans_t05_t.Level_ - 1

AND a.LineNum = D_BomCalcTrans_t05_t.LineNum -1 --is my wag right?

and D_BomCalcTrans_t05_t.Level_ <> 0

maybe if the line number is supposed to be the MIN line number for a recid:

update D_BomCalcTrans_t05_t

set C_ParentRecID = a.RecID

from D_BomCalcTrans_t06_t AS a

INNER JOIN(SELECT MIN(LINENUM) as LineNum,Recid FROM D_BomCalcTrans_t06_t GROUP BY RecID) X

ON a.RecId = X.RecID AND a.LineNum = X.LineNum

where a.DataAreaId = D_BomCalcTrans_t05_t.DataAreaId

AND a.BOMID = D_BomCalcTrans_t05_t.BOMID

AND a.PriceCalcID = D_BomCalcTrans_t05_t.PriceCalcID

AND a.Level_ = D_BomCalcTrans_t05_t.Level_ - 1

and D_BomCalcTrans_t05_t.Level_ <> 0

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!

Wilfred van Dijk SSCrazy Eights Points: 8980 More actions · Answer 4

Thanks for the replies. On t05 is a clustered index on (datareaid, itemid, recid), on T06 is a clustered index on (dataareaid,bomid,pricecalcid,level_,linenum,recid)

My version does a clustered index scan on t05 and a clustered index seek on T06, see atachment

Wilfred
The best things in life are the simple things

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

Can you please post the table structure, some sample data and what you want as a result?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Wilfred van Dijk SSCrazy Eights Points: 8980 More actions · Answer 6

Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation

ITEMIDnvarcharno60 no(n/a)(n/a)Latin1_General_CI_AS

CostGroupIdnvarcharno20 no(n/a)(n/a)Latin1_General_CI_AS

LEVEL_intno410 0 no(n/a)(n/a)NULL

QTYnumericno1328 12 no(n/a)(n/a)NULL

COSTPRICEnumericno1328 12 no(n/a)(n/a)NULL

COSTMARKUPnumericno1328 12 no(n/a)(n/a)NULL

SALESPRICEnumericno1328 12 no(n/a)(n/a)NULL

SALESMARKUPnumericno1328 12 no(n/a)(n/a)NULL

TRANSDATEdatetimeno8 no(n/a)(n/a)NULL

LINENUMnumericno1328 12 no(n/a)(n/a)NULL

KEY1nvarcharno40 no(n/a)(n/a)Latin1_General_CI_AS

KEY2nvarcharno20 no(n/a)(n/a)Latin1_General_CI_AS

KEY3nvarcharno40 no(n/a)(n/a)Latin1_General_CI_AS

KEY4nvarcharno40 no(n/a)(n/a)Latin1_General_CI_AS

CONSUMPTIONVARIABLEnumericno1328 12 no(n/a)(n/a)NULL

CONSUMPTIONCONSTANTnumericno1328 12 no(n/a)(n/a)NULL

BOMintno410 0 no(n/a)(n/a)NULL

OPRNUMintno410 0 no(n/a)(n/a)NULL

CALCTYPEintno410 0 no(n/a)(n/a)NULL

PRICEUNITnumericno1328 12 no(n/a)(n/a)NULL

COSTPRICEQTYnumericno1328 12 no(n/a)(n/a)NULL

SALESPRICEQTYnumericno1328 12 no(n/a)(n/a)NULL

COSTMARKUPQTYnumericno1328 12 no(n/a)(n/a)NULL

SALESMARKUPQTYnumericno1328 12 no(n/a)(n/a)NULL

BOMIDnvarcharno160 no(n/a)(n/a)Latin1_General_CI_AS

ROUTEIDnvarcharno60 no(n/a)(n/a)Latin1_General_CI_AS

PRICECALCIDnvarcharno40 no(n/a)(n/a)Latin1_General_CI_AS

DEL_CONFIGIDnvarcharno20 no(n/a)(n/a)Latin1_General_CI_AS

NUMOFSERIESnumericno1328 12 no(n/a)(n/a)NULL

OPRPRIORITYintno410 0 no(n/a)(n/a)NULL

INVENTDIMIDnvarcharno40 no(n/a)(n/a)Latin1_General_CI_AS

CONSUMPTIONINVENTnumericno1328 12 no(n/a)(n/a)NULL

KEY4INVENTDIMIDnvarcharno40 no(n/a)(n/a)Latin1_General_CI_AS

VENDIDnvarcharno50 no(n/a)(n/a)Latin1_General_CI_AS

CONSUMPTYPEintno410 0 no(n/a)(n/a)NULL

ACCSKSPECIALCOSTSOFPRODUCTIONnumericno1328 12 no(n/a)(n/a)NULL

DataAreaIdnvarcharno6 no(n/a)(n/a)Latin1_General_CI_AS

C_ParentRecIDintno410 0 yes(n/a)(n/a)NULL

C_QTY_TLnumericno1328 12 yes(n/a)(n/a)NULL

C_QTY_LLnumericno1328 12 yes(n/a)(n/a)NULL

C_Costs_TLnumericno1328 12 yes(n/a)(n/a)NULL

C_Costs_LLnumericno1328 12 yes(n/a)(n/a)NULL

C_Namevarcharno100 yesnoyesLatin1_General_CI_AS

C_Dimension3varcharno100 yesnoyesLatin1_General_CI_AS

RECIDintno410 0 no(n/a)(n/a)NULL

Wilfred
The best things in life are the simple things

Garadin One Orange Chip Points: 29613 More actions · Answer 7

Please refer to the link in my signature for how to post table structure/sample data here.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

epriddy Old Hand Points: 343 More actions · Answer 8

Are you hoping to populate all rows with the one value or is it a different value for each row?

epriddy Old Hand Points: 343 More actions · Answer 9

Depending on the uniqueness of the data, I'd recommend a non-clustered index on both tables for the following fields at a minimum: DataAreaId , BOMID, PriceCalcID

When I say "uniqueness" I mean, if there are 17 million rows and 250,000 different ID's then it's likely worth indexing; if there are 17 million rows and 3 different ID's then it's not necessarily worth indexing.