• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

UPDATE slow?

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
zeBes
Asked:
zeBes
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
without the table structure and the query/script it's difficult to suggest any improvements...
0
 
iloyaCommented:
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
 
zeBesAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
zeBesAuthor Commented:
the indexes i created or already exists:

[grn_ref] - storeno, sku

[stockweightedavgcost] - storeno, sku_no

[grndetail] - storeno, cstockcode, grndate / storeno, cstockcode
0
 
zeBesAuthor Commented:
>>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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

    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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
zeBesAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
JAVAnewbieCommented:
let me try it out first, hopefully by thursday. i'm on my vacation actually...
0
 
zeBesAuthor Commented:
huh?
0
 
zeBesAuthor Commented:
try wat?
0
 
zeBesAuthor Commented:
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
 
zeBesAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
zeBesAuthor Commented:
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
 
zeBesAuthor Commented:
nvm, let me try out a few more things first...
0
 
zeBesAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see. then we have to replace that condition by a join condition on the storeno (I overlooked that)
0
 
zeBesAuthor Commented:
hahahaaa, i got it done in less than 3mins!!!

thank you, thank you!!
0
 
zeBesAuthor Commented:
you are amazing! ;-)

question closed
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
zeBesAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
zeBesAuthor Commented:
tks ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now