?
Solved

UPDATE slow?

Posted on 2006-04-09
25
Medium Priority
?
327 Views
Last Modified: 2006-11-18
hi, i have a update process in my program that will update an average of 48k rows of data, but it only update 2-4 rows every single minute and it is breaking my balls.
i created the index, clustered, non-clustered, it all the same.
i have test run the same process, table structure, indexes on MySQL and it only need about 10mins! whereas the SQL Server takes several hours! just for 48k rows?!
I'm sure MS SQL Server can do better than take, just that I might miss up sth (configuration on the buffer size or watsoever).
besides, my update involve updating date fields, can this causes problem?

i have set the auto-commit to false, and uses batch update that execute every 1000 rows of data then commit.

I am totally new to MS SQL Server, so please help...
0
Comment
Question by:zeBes
25 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16414884
without the table structure and the query/script it's difficult to suggest any improvements...
0
 
LVL 2

Expert Comment

by:iloya
ID: 16414923
You have to be more specific and explain the structure of your problem. I mean the table structure and what you are trying to update?
0
 

Author Comment

by:zeBes
ID: 16414971
The whole process involved 3 tables, the [grn_ref], [grndetail] and [stockweightedavgcost]
1.  it will select all from [grn_ref], the loop the result set to get the storeno and sku.

    SELECT * FROM grn_ref WHERE StoreNo = ?




2. search if the same storeno and sku exists in [stockweightedavgcost]

    SELECT * FROM StockWeightedAvgCost WHERE StoreNo = ? AND SKU_No = ?



3. if exists, then select data from [grndetail] based on the same storeno and sku and update the [stockweightedavgcost]

     SELECT StoreNo, cStockCode,
                    nOrdCost, nOrdTotalQty, nOrdTotalQty, nOrdFOCInvQty, nFOCPurchQty,
                    cGRNNo, dGRNDate
                    FROM GRNDetail WHERE
                    StoreNo = ?
                    AND cStockCode = ?
                    AND (dGRNDate BETWEEN ? AND ?)
                    ORDER BY dGRNDate DESC                (want the latest date to be the first row)

    double wgthavgcost=(nOrdCost * nOrdTotalQty) / (nOrdTotalQty+nOrdFOCInvQty+nFOCPurchQty);

    UPDATE StockWeightedAvgCost
                    SET WeightedAvgCost = ? (wgthavgcost) ,
                    GRNNo = ? (cGRNNo), LatestGRNDate = ? (dGRNDate ),
                    DateUpdated = ? (currentdate),
                    UpdatedBy = ? (user name)
                    WHERE StoreNo = ? AND SKU_No = ?


-----------------------------------------------------------------------------------------------
The reference table:
CREATE TABLE [grn_ref] (
      [storeno] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__grn_ref__storeno__55209ACA] DEFAULT (null),
      [sku] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF__grn_ref__sku__5614BF03] DEFAULT (null)
) ON [PRIMARY]
GO
-----------------------------------------------------------------------------------------------
The table that is to be updated:
CREATE TABLE [StockWeightedAvgCost] (
      [StoreNo] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [SKU_No] [varchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [WeightedAvgCost] [money] NULL ,
      [GRNNo] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [LatestGRNDate] [datetime] NULL ,
      [DateUpdated] [datetime] NULL ,
      [UpdatedBy] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
-----------------------------------------------------------------------------------------------
The source table which its data is used to update [StockWeightedAvgCost]
CREATE TABLE [GRNDetail] (
      [cGRNNO] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [nItemNo] [real] NULL ,
      [cPONO] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [nPOItemNo] [real] NULL ,
      [cSign] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [nUseColumn] [smallint] NULL ,
      [dGRNDate] [smalldatetime] NULL ,
      [dPODate] [smalldatetime] NULL ,
      [cStockCode] [nvarchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [cBarcode] [nvarchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [cStockDesc] [nvarchar] (70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [nOrdInvQty] [float] NULL ,
      [nOrdPurchQty] [float] NULL ,
      [nOrdTotalQty] [float] NULL ,
      [nOrdFOCInvQty] [float] NULL ,
      [nOrdFOCPurchQty] [float] NULL ,
      [nOrdFOCTotalQty] [float] NULL ,
      [nOrdCost] [money] NULL ,
      [nOrdRetail] [money] NULL ,
      [nOrdAmount] [money] NULL ,
      [nPartInvQty] [float] NULL ,
      [nPartPurchQty] [float] NULL ,
      [nPartTotalQty] [float] NULL ,
      [nPartFOCInvQty] [float] NULL ,
      [nPartFOCPurchQty] [float] NULL ,
      [nPartFOCTotalQty] [float] NULL ,
      [nRecInvQty] [float] NULL ,
      [nRecPurchQty] [float] NULL ,
      [nRecTotalQty] [float] NULL ,
      [nRecCost] [money] NULL ,
      [nRecRetail] [money] NULL ,
      [nRecAmount] [money] NULL ,
      [nRecAmount2] [money] NULL ,
      [nFOCInvQty] [float] NULL ,
      [nFOCPurchQty] [float] NULL ,
      [nFOCTotalQty] [float] NULL ,
      [nMisc] [money] NULL ,
      [cInvUnit] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [cPurchUnit] [nvarchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [nDiscPerc] [float] NULL ,
      [nDiscAmount] [money] NULL ,
      [nFactor] [smallint] NULL ,
      [cItemRemark] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [nFinalCost] [money] NULL ,
      [nPOLOCItemNo] [real] NULL ,
      [cLocationID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [cLocationDesc] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [nDiscPerc2] [real] NULL ,
      [nMarkOn] [real] NULL ,
      [nMarkOnSYS] [real] NULL ,
      [nSalesTax] [real] NULL ,
      [nRRPrice] [money] NULL ,
      [nDiscToMP] [float] NULL ,
      [StoreNo] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:zeBes
ID: 16414988
the indexes i created or already exists:

[grn_ref] - storeno, sku

[stockweightedavgcost] - storeno, sku_no

[grndetail] - storeno, cstockcode, grndate / storeno, cstockcode
0
 

Author Comment

by:zeBes
ID: 16414992
>>but it only update 2-4 rows every single minute and it is breaking my balls.
2-4 rows every second actually.
that will be an average of 180 rows a minutes, 10800 rows an hour
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 16415023

    UPDATE StockWeightedAvgCost
         SET WeightedAvgCost = (gd.nOrdCost*gd.nOrdTotalQty)/(gd.nOrdTotalQty+gd.nOrdFOCInvQty+gd.nFOCPurchQty),
                    GRNNo = gd.cGRNNo, LatestGRNDate = gd.dGRNDate,
                    DateUpdated = getdate(),
                    UpdatedBy = username()
   FROM StockWeightedAvgCost sw
   JOIN grn_ref gr
      ON gr.storeno = sw.storeno
    AND gr.sku_no = sw.sku_no
   JOIN GRNDetail gd
    ON gd.cGRNNO = sw.cGRNNO
  AND gd.dGRNDate = ( select max ( fi.dGRNDate ) from GRNDetail gi where gi.cGRNNO = sw.cGRNNO )
 
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16415032
to explain: I guess that this single update should do all the job...

indexes should be the following:

GRNDetail:
index1: cGRNNO + dGRNDate

StockWeightedAvgCost:
index1: storeno + sku_no

grn_ref:
index1: storeno + sku_no

0
 

Author Comment

by:zeBes
ID: 16415085
how do i check for zero value for the divide operation?
(gd.nOrdCost*gd.nOrdTotalQty)/(gd.nOrdTotalQty+gd.nOrdFOCInvQty+gd.nFOCPurchQty)

i tried using case to check if (gd.nOrdTotalQty+gd.nOrdFOCInvQty+gd.nFOCPurchQty) <> 0
then check (gd.nOrdCost*gd.nOrdTotalQty) <> 0
if both not zero, then only perform the calculation... but somehow, for some reason that i've forgot, i opt to put the condition in WHERE clause, which, my manager told me not to.


besides, there might be more than 1 record in [grndetail] that have the same storeno and cstockcode. but there's only one row of data that have the same storeno and sku(or cstockcode) in [grn_ref] and [stockweightedavgcost], so i dont think join can be used here..

the idea is, to identify the latest entry in [grndetail] of the same storeno and cstockcode, and get its values for
gd.nOrdCost
gd.nOrdTotalQty
gd.nOrdTotalQty
gd.nOrdFOCInvQty
gd.nFOCPurchQty
dgrndate
cgrnno
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16415212
>besides, there might be more than 1 record in [grndetail] that have the same storeno and cstockcode.
that for is the last line with the subselect, taking only the last ( as by dgrndate) line into the join:
  AND gd.dGRNDate = ( select max ( fi.dGRNDate ) from GRNDetail gi where gi.cGRNNO = sw.cGRNNO )

>how do i check for zero value for the divide operation?
>(gd.nOrdCost*gd.nOrdTotalQty)/(gd.nOrdTotalQty+gd.nOrdFOCInvQty+gd.nFOCPurchQty)

if you want to update the row anyway, put either the old value or a replacement value::
UPDATE StockWeightedAvgCost
         SET WeightedAvgCost =  CASE
               WHEN gd.nOrdTotalQty+gd.nOrdFOCInvQty+gd.nFOCPurchQty = 0 THEN WeightedAvgCost  -- replace this with 0 or some other value if you don't want to use the old value
               ELSE (gd.nOrdCost*gd.nOrdTotalQty)/(gd.nOrdTotalQty+gd.nOrdFOCInvQty+gd.nFOCPurchQty) END ,
...
0
 

Expert Comment

by:JAVAnewbie
ID: 16415233
let me try it out first, hopefully by thursday. i'm on my vacation actually...
0
 

Author Comment

by:zeBes
ID: 16415294
huh?
0
 

Author Comment

by:zeBes
ID: 16415300
try wat?
0
 

Author Comment

by:zeBes
ID: 16415344
angelIII, is there a way to put a range on the grndate?

eg: from 01-01-2003 to 28-02-2006
then select the latest date that fall within this range?
0
 

Author Comment

by:zeBes
ID: 16415426
ok, it has been running for 10 mins now, but when i select count from [stockweightedavgcost] to find out how many rows of data has been updated, i got 0...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16415468
>ok, it has been running for 10 mins now, but when i select count from [stockweightedavgcost] to find out how many rows of data has been updated, i got 0...

did you run with NOLOCK hint?:
select count(*) from stockweightedavgcost (NOLOCK) where ...
0
 

Author Comment

by:zeBes
ID: 16415508
now it is completed. ok, let me show you my code.

            String query1 = "UPDATE StockWeightedAvgCost " +
         "SET WeightedAvgCost =  CASE WHEN gd.nOrdTotalQty+gd.nOrdFOCInvQty+gd.nFOCPurchQty = 0 THEN 0 " +
               "ELSE (gd.nOrdCost*gd.nOrdTotalQty)/(gd.nOrdTotalQty+gd.nOrdFOCInvQty+gd.nFOCPurchQty) END, " +
                    "GRNNo = gd.cGRNNo, LatestGRNDate = gd.dGRNDate, " +
                    "DateUpdated = getdate(), " +
                    "UpdatedBy = 'Jerry' " +
   "FROM StockWeightedAvgCost sw " +
   "JOIN grn_ref gr " +
        "ON gr.storeno = sw.storeno " +
        "AND gr.sku = sw.sku_no " +
   "JOIN GRNDetail gd " +
        "ON gd.cGRNNO = sw.GRNNO " +
        "AND gd.dGRNDate = ( select max ( gi.dGRNDate ) from GRNDetail gi where gi.cGRNNO = sw.GRNNO )";
           
            selectgrnstmt = conn1.prepareStatement(query1);
           
             
            int num = selectgrnstmt.executeUpdate();
            System.out.println("Update: " + num);

the num shows about 58000 +/-
but when i select count(*)
select count(*) from stockweightedavgcost where
updatedby='jerry' and
storeno = '255'

it return 0...
0
 

Author Comment

by:zeBes
ID: 16415518
nvm, let me try out a few more things first...
0
 

Author Comment

by:zeBes
ID: 16415538
ok, i guess i know why already...
at the beginning, the grnno field in [stockweightedavgcost] does not have a value..
thus the query
      "ON gd.cGRNNO = sw.GRNNO " +

wont work...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16415553
I see. then we have to replace that condition by a join condition on the storeno (I overlooked that)
0
 

Author Comment

by:zeBes
ID: 16415559
hahahaaa, i got it done in less than 3mins!!!

thank you, thank you!!
0
 

Author Comment

by:zeBes
ID: 16415569
you are amazing! ;-)

question closed
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16415595
Glad I could help.

note that cursor processing is both usually slower itself and usually slowing down other processes, hence to avoid.
that does not mean that cursors don't have a right to exist, some solutions require a cursor...
0
 

Author Comment

by:zeBes
ID: 16415620
tks for the tips. btw, can u recommend me any SQL book?
me and my colleague totally amazed by the script u provided...
and i just hope i can learn more
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16415640
0
 

Author Comment

by:zeBes
ID: 16415676
tks ;-)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question