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)

update D_BomCalcTrans_t05_t set C_ParentRecID = ( select TOP 1 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 ORDER BY a.LineNum Desc ) where Level_ <> 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

krayknot

SSCertifiable

Points: 7998

November 12, 2008 at 4:57 am

#897421

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

Wilfred van Dijk

SSCrazy Eights

Points: 8980

November 12, 2008 at 5:57 am

#897440

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_name	Type	Computed	Length	Prec	Scale	Nullable	TrimTrailingBlanks	FixedLenNullInSource	Collation

ITEMID	nvarchar	no	60	     	     	no	(n/a)	(n/a)	Latin1_General_CI_AS

CostGroupId	nvarchar	no	20	     	     	no	(n/a)	(n/a)	Latin1_General_CI_AS

LEVEL_	int	no	4	10   	0    	no	(n/a)	(n/a)	NULL

QTY	numeric	no	13	28   	12   	no	(n/a)	(n/a)	NULL

COSTPRICE	numeric	no	13	28   	12   	no	(n/a)	(n/a)	NULL

COSTMARKUP	numeric	no	13	28   	12   	no	(n/a)	(n/a)	NULL

SALESPRICE	numeric	no	13	28   	12   	no	(n/a)	(n/a)	NULL

SALESMARKUP	numeric	no	13	28   	12   	no	(n/a)	(n/a)	NULL

TRANSDATE	datetime	no	8	     	     	no	(n/a)	(n/a)	NULL

LINENUM	numeric	no	13	28   	12   	no	(n/a)	(n/a)	NULL

KEY1	nvarchar	no	40	     	     	no	(n/a)	(n/a)	Latin1_General_CI_AS

KEY2	nvarchar	no	20	     	     	no	(n/a)	(n/a)	Latin1_General_CI_AS

KEY3	nvarchar	no	40	     	     	no	(n/a)	(n/a)	Latin1_General_CI_AS

KEY4	nvarchar	no	40	     	     	no	(n/a)	(n/a)	Latin1_General_CI_AS

CONSUMPTIONVARIABLE	numeric	no	13	28   	12   	no	(n/a)	(n/a)	NULL

CONSUMPTIONCONSTANT	numeric	no	13	28   	12   	no	(n/a)	(n/a)	NULL

BOM	int	no	4	10   	0    	no	(n/a)	(n/a)	NULL

OPRNUM	int	no	4	10   	0    	no	(n/a)	(n/a)	NULL

CALCTYPE	int	no	4	10   	0    	no	(n/a)	(n/a)	NULL

PRICEUNIT	numeric	no	13	28   	12   	no	(n/a)	(n/a)	NULL

COSTPRICEQTY	numeric	no	13	28   	12   	no	(n/a)	(n/a)	NULL

SALESPRICEQTY	numeric	no	13	28   	12   	no	(n/a)	(n/a)	NULL

COSTMARKUPQTY	numeric	no	13	28   	12   	no	(n/a)	(n/a)	NULL

SALESMARKUPQTY	numeric	no	13	28   	12   	no	(n/a)	(n/a)	NULL

BOMID	nvarchar	no	160	     	     	no	(n/a)	(n/a)	Latin1_General_CI_AS

ROUTEID	nvarchar	no	60	     	     	no	(n/a)	(n/a)	Latin1_General_CI_AS

PRICECALCID	nvarchar	no	40	     	     	no	(n/a)	(n/a)	Latin1_General_CI_AS

DEL_CONFIGID	nvarchar	no	20	     	     	no	(n/a)	(n/a)	Latin1_General_CI_AS

NUMOFSERIES	numeric	no	13	28   	12   	no	(n/a)	(n/a)	NULL

OPRPRIORITY	int	no	4	10   	0    	no	(n/a)	(n/a)	NULL

INVENTDIMID	nvarchar	no	40	     	     	no	(n/a)	(n/a)	Latin1_General_CI_AS

CONSUMPTIONINVENT	numeric	no	13	28   	12   	no	(n/a)	(n/a)	NULL

KEY4INVENTDIMID	nvarchar	no	40	     	     	no	(n/a)	(n/a)	Latin1_General_CI_AS

VENDID	nvarchar	no	50	     	     	no	(n/a)	(n/a)	Latin1_General_CI_AS

CONSUMPTYPE	int	no	4	10   	0    	no	(n/a)	(n/a)	NULL

ACCSKSPECIALCOSTSOFPRODUCTION	numeric	no	13	28   	12   	no	(n/a)	(n/a)	NULL

DataAreaId	nvarchar	no	6	     	     	no	(n/a)	(n/a)	Latin1_General_CI_AS

C_ParentRecID	int	no	4	10   	0    	yes	(n/a)	(n/a)	NULL

C_QTY_TL	numeric	no	13	28   	12   	yes	(n/a)	(n/a)	NULL

C_QTY_LL	numeric	no	13	28   	12   	yes	(n/a)	(n/a)	NULL

C_Costs_TL	numeric	no	13	28   	12   	yes	(n/a)	(n/a)	NULL

C_Costs_LL	numeric	no	13	28   	12   	yes	(n/a)	(n/a)	NULL

C_Name	varchar	no	100	     	     	yes	no	yes	Latin1_General_CI_AS

C_Dimension3	varchar	no	100	     	     	yes	no	yes	Latin1_General_CI_AS

RECID	int	no	4	10   	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

Garadin

One Orange Chip

Points: 29613

November 13, 2008 at 7:58 am

#898105

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

epriddy

Old Hand

Points: 343

November 14, 2008 at 11:42 am

#898791

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.