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...
zeBesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
without the table structure and the query/script it's difficult to suggest any improvements...
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?
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


10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

zeBesAuthor Commented:
the indexes i created or already exists:

[grn_ref] - storeno, sku

[stockweightedavgcost] - storeno, sku_no

[grndetail] - storeno, cstockcode, grndate / storeno, cstockcode
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
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 )
 

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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

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
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 ,
...
JAVAnewbieCommented:
let me try it out first, hopefully by thursday. i'm on my vacation actually...
zeBesAuthor Commented:
huh?
zeBesAuthor Commented:
try wat?
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?
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...
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 ...
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...
zeBesAuthor Commented:
nvm, let me try out a few more things first...
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...
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)
zeBesAuthor Commented:
hahahaaa, i got it done in less than 3mins!!!

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

question closed
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...
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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
zeBesAuthor Commented:
tks ;-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.