Prev: Having divide by zero error
Next: Best way to persist alter table changes in my local DB to web host
From: Michael Coles on 16 Jan 2010 11:15
Try the temp table again, and put a PK constraint on (ItemCode, StoreCode,
ColorCode, SizeCode, BatchSerialNo, DocumentYear, BranchCode, DocumentType,
SeriesCode, DocumentNo, ItemSno) columns in the temp table. If it errors
out this means you are generating duplicates, which means your update
statement is updating some rows two or more times.

If you want to see exactly which rows are being updated twice you can create
the temp table without the PK constraint and run a query like this:

SELECT ItemCode, StoreCode, ColorCode, SizeCode, BatchSerialNo,
DocumentYear, BranchCode, DocumentType, SeriesCode, DocumentNo, ItemSno,
COUNT(*)
FROM #MyTempTable
GROUP BY ItemCode, StoreCode, ColorCode, SizeCode, BatchSerialNo,
DocumentYear, BranchCode, DocumentType, SeriesCode, DocumentNo, ItemSno
HAVING COUNT(*) > 1;

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

"SANJAY SHAH-MICROBRAIN COMPUTERS PVT. LTD." <microbrain(a)vsnl.com> wrote in
message news:uHIfDgplKHA.5520(a)TK2MSFTNGP06.phx.gbl...
> Dear Sir,
>
> I am using SQL Server 2005, Visual Basic 6 (SP6) & Microsoft ActiveX Data
> Object 2.5.
>
> If I run following query through SQL Server 2005 Management Studio through
> QUERY only then it works FINE. But if I run from application using Execute
> method of Command Object of ADO then it process maximum 49 records in
> CURSOR and query completes without any errors.
>
> I had checked by inserting records in a Temporary Table. If I do Not Use
> UPDATE statement then it INSERT maximum 65 records in Table and if I use
> UPDATE statement then it INSERT & UPDATE maximum 49 records.
>
> PLEASE NOTE I DON'T WANT TO USE STORED PROCEDURE.
>
> Please reply me after proper reading of this query.
>
> Sanjay Shah
>
>
> --- Declare Scalar Variables
> DECLARE @cDocumentType AS nVarChar(3), @dDocumentDate AS DATETIME,
> @cEntryType AS nVarChar(3), @cStoreCode AS nVarChar(6), @cItemCode AS
> nVarChar(15), @nItemSno AS INT, @cValuation AS nVarChar(1), @cColorCode AS
> nVarChar(4), @cSizeCode AS nVarChar(4), @cBatchSerialNo AS nVarChar(15),
> @nRDocumentYear AS INT, @cRBranchCode AS nVarChar(3), @cRDocumentType AS
> nVarChar(3), @cRSeriesCode AS nVarChar(4), @cRDocumentNo AS nVarChar(6),
> @nRItemSno AS INT, @nQuantity AS FLOAT, @nQuantity1 AS FLOAT, @nRate AS
> FLOAT, @nPer AS INT, @nAmount AS FLOAT, @cTaxCode AS nVarChar(3),
> @nTaxableAmount AS FLOAT, @nTaxAmount AS FLOAT;
>
> --- Define Cursor with Transaction Ledger
> DECLARE Ledger_Cursor CURSOR FOR
> SELECT DocumentType, DocumentDate, EntryType, StoreCode, ItemCode,
> ItemSno, Valuation, ColorCode, SizeCode, BatchSerialNo, RDocumentYear,
> RBranchCode, RDocumentType, RSeriesCode, RDocumentNo, RItemSno, Quantity,
> Quantity1, Rate, Per, Amount, TaxCode, TaxableAmount, TaxAmount FROM
> FaItemLedger WHERE DocumentDate >= ('04/01/2003') AND DocumentDate <=
> ('04/30/2003') ORDER BY DocumentDate, CASE WHEN Quantity > 0 OR Quantity1
> > 0 OR Amount > 0 THEN 1 ELSE 2 END ;
>
> --- Open Cursor Rows
> OPEN Ledger_Cursor;
>
> --- Get Values of Cursor Columns into Scalar Variables
> FETCH NEXT FROM Ledger_Cursor INTO
> @cDocumentType, @dDocumentDate, @cEntryType, @cStoreCode, @cItemCode,
> @nItemSno, @cValuation, @cColorCode, @cSizeCode, @cBatchSerialNo,
> @nRDocumentYear, @cRBranchCode, @cRDocumentType, @cRSeriesCode,
> @cRDocumentNo, @nRItemSno, @nQuantity, @nQuantity1, @nRate, @nPer,
> @nAmount, @cTaxCode, @nTaxableAmount, @nTaxAmount;
>
> -- Perform till Fatch Status is 0 means (Not EOF)
> WHILE @@FETCH_STATUS = 0
> BEGIN
> --- Initialise RDocumentNo if Valuation is Not Bill Wise
> IF @cValuation NOT IN ('B','P')
> BEGIN
> SET @nRDocumentYear = 0;
> SET @cRBranchCode = '';
> SET @cRDocumentType = '';
> SET @cRSeriesCode = '';
> SET @cRDocumentNo = '';
> SET @nRItemSno = 0;
> END
>
> --- Update Stock Master with Values of Scalar Variables
> UPDATE FaStockMaster SET
> FaStockMaster.StockQuantity =
> Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) ,
> FaStockMaster.StockQuantity1 =
> Round(FaStockMaster.StockQuantity1 + @nQuantity1 * 1, 3) ,
> FaStockMaster.Used = 'True',
> FaStockMaster.Month1TotalQuantity =
> ROUND(FaStockMaster.Month1TotalQuantity + @nQuantity * 1, 3),
> FaStockMaster.Month1TotalQuantity1 =
> ROUND(FaStockMaster.Month1TotalQuantity1 + @nQuantity1 * 1, 3),
> FaStockMaster.Rate = CASE WHEN
> Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) > 0 AND CASE WHEN
> @cDocumentType IN ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU')
> AND @nQuantity > 0 AND @cValuation IN ('W','F') THEN
> Round(FaStockMaster.StockValue + @nAmount * 1, 2) ELSE
> Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) * CASE WHEN
> @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity
> > 0 AND @cDocumentType IN
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE
> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND @nQuantity
> > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per
> END , 2) END > 0 AND @cDocumentType IN
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 0
> AND @cValuation IN ('W','F') THEN Round(CASE WHEN @cDocumentType IN
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 0
> AND @cVa
> luation IN ('W','F') THEN Round(FaStockMaster.StockValue + @nAmount * 1,
> 2) ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) *
> CASE WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity
> > 0 AND @cDocumentType IN
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE
> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND @nQuantity
> > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per
> END , 2) END / Round(FaStockMaster.StockQuantity + @nQuantity * 1, 3) *
> CASE WHEN @cValuation IN ('B','P') AND @nQuantity > 0 AND
> FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per END , 6)
> WHEN @cValuation IN ('L','B','P','A','N') AND (@dDocumentDate >=
> FaStockMaster.LastReceiptDate OR @cValuation IN ('B','P')) AND @nQuantity
> > 0 AND @cDocumentType IN
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN Round(@nRate
> / CASE WHEN @cValuation IN ('B','P') AND
> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE
> FaStockMaster.Per END * FaStockMaster.Per, 6) ELSE FaStockMaster.Rate END
> ,
> FaStockMaster.Per = CASE WHEN @cValuation IN ('B','P') AND
> @nQuantity > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE
> FaStockMaster.Per END ,
> FaStockMaster.StockValue = CASE WHEN @cDocumentType IN
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') AND @nQuantity > 0
> AND @cValuation IN ('W','F') THEN Round(FaStockMaster.StockValue +
> @nAmount * 1, 2) ELSE Round(Round(FaStockMaster.StockQuantity + @nQuantity
> * 1, 3) * CASE WHEN @cValuation IN ('L','B','P','A','N') AND
> (@dDocumentDate >= FaStockMaster.LastReceiptDate OR @cValuation IN
> ('B','P')) AND @nQuantity > 0 AND @cDocumentType IN
> ('GIN','PUR','CPU','SCN','PRE','EXC','RET','CCN','ISU') THEN @nRate ELSE
> FaStockMaster.Rate END / CASE WHEN @cValuation IN ('B','P') AND @nQuantity
> > 0 AND FaStockMaster.StockQuantity = 0 THEN @nPer ELSE FaStockMaster.Per
> END , 2) END,
> FaStockMaster.TaxCode = CASE WHEN @cValuation IN ('B','P')
> AND @nQuantity > 0 THEN @cTaxCode ELSE FaStockMaster.TaxCode END ,
> FaStockMaster.TaxableAmount = CASE WHEN @cValuation IN
> ('B','P') AND @nQuantity > 0 THEN @nTaxableAmount ELSE
> FaStockMaster.TaxableAmount END ,
> FaStockMaster.TaxAmount = CASE WHEN @cValuation IN ('B','P')
> AND @nQuantity > 0 THEN @nTaxAmount ELSE FaStockMaster.TaxAmount END ,
> FaStockMaster.LastReceiptDate = CASE WHEN @cDocumentType IN
> ('GIN','PUR','CPU','ISU','PRE') AND @cEntryType <> 'GIR' AND @nQuantity >
> 0 AND @dDocumentDate > FaStockMaster.LastReceiptDate THEN @dDocumentDate
> ELSE FaStockMaster.LastReceiptDate END
> WHERE
> FaStockMaster.ItemCode = @cItemCode AND
> FaStockMaster.StoreCode = @cStoreCode AND
> FaStockMaster.ColorCode = @cColorCode AND
> FaStockMaster.SizeCode = @cSizeCode AND
> FaStockMaster.BatchSerialNo = @cBatchSerialNo AND
> FaStockMaster.DocumentYear = @nRDocumentYear AND
> FaStockMaster.BranchCode = @cRBranchCode AND
> FaStockMaster.DocumentType = @cRDocumentType AND
> FaStockMaster.SeriesCode = @cRSeriesCode AND
> FaStockMaster.DocumentNo = @cRDocumentNo AND
> FaStockMaster.ItemSno = @nRItemSno ;
>
> --- Get Next Values of Cursor Columns into Scalar Variables
> FETCH NEXT FROM Ledger_Cursor INTO
> @cDocumentType, @dDocumentDate, @cEntryType, @cStoreCode,
> @cItemCode, @nItemSno, @cValuation, @cColorCode, @cSizeCode,
> @cBatchSerialNo, @nRDocumentYear, @cRBranchCode, @cRDocumentType,
> @cRSeriesCode, @cRDocumentNo, @nRItemSno, @nQuantity, @nQuantity1, @nRate,
> @nPer, @nAmount, @cTaxCode, @nTaxableAmount, @nTaxAmount;
> END
>
> --- Close Cursor
> CLOSE Ledger_Cursor;
>
> --- Release Cursor
> DEALLOCATE Ledger_Cursor;
>
> --- *** End of Procedure
>
>

From: Gert-Jan Strik on 16 Jan 2010 11:37
Plamen Ratchev wrote:
>
> Hugo did interesting tests and concluded using STATIC achieves best performance:
> http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/curious-cursor-optimization-options.aspx
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com

I guess that both STATIC and FAST_FORWARD are good choices.

You may also have seen the final note of Hugo himself in the comments at
the end, that says "I found that there are cases where the FAST_FORWARD
option is faster than the STATIC option", which conforms with the
"wisdom" about cursors from 2000 to 2009.

--
Gert-Jan
From: Plamen Ratchev on 16 Jan 2010 11:46
Yes, and in his last comment Hugo explains:

"In situations where all the data to be processed by the cursor fits into the cache, STATIC always wins. In cases where
the amount of data is way too large to fit into cache, FAST_FORWARD has the edge."

--
Plamen Ratchev
http://www.SQLStudio.com
From: Erland Sommarskog on 16 Jan 2010 15:01
Michael Coles (admin(a)geocodenet.com) writes:
> Try the temp table again, and put a PK constraint on (ItemCode,
> StoreCode, ColorCode, SizeCode, BatchSerialNo, DocumentYear, BranchCode,
> DocumentType, SeriesCode, DocumentNo, ItemSno) columns in the temp
> table. If it errors out this means you are generating duplicates, which
> means your update statement is updating some rows two or more times.
>
> If you want to see exactly which rows are being updated twice you can
> create the temp table without the PK constraint and run a query like
> this:
>
> SELECT ItemCode, StoreCode, ColorCode, SizeCode, BatchSerialNo,
> DocumentYear, BranchCode, DocumentType, SeriesCode, DocumentNo, ItemSno,
> COUNT(*)
> FROM #MyTempTable
> GROUP BY ItemCode, StoreCode, ColorCode, SizeCode, BatchSerialNo,
> DocumentYear, BranchCode, DocumentType, SeriesCode, DocumentNo, ItemSno
> HAVING COUNT(*) > 1;

He could also use the OUTPUT clause of the UPDATE statements to see
which rows he updates multiple times.

But what he really should to is to scrap the cursor entirely, and write
the whole thing as a single UPDATE statement.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Michael Coles on 17 Jan 2010 00:32
"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9D02D5EB65A48Yazorman(a)127.0.0.1...
>
> He could also use the OUTPUT clause of the UPDATE statements to see
> which rows he updates multiple times.
>
> But what he really should to is to scrap the cursor entirely, and write
> the whole thing as a single UPDATE statement.
>

True, but after spending 10 mins trying to edit his query to make it
readable I scrapped the idea and went hunting through it for a primary key
:)

--
Thanks

Michael Coles
SQL Server MVP
Author, "Expert SQL Server 2008 Encryption"
(http://www.apress.com/book/view/1430224649)
----------------

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Having divide by zero error
Next: Best way to persist alter table changes in my local DB to web host