friends,

I have the following table with atleast 4,00,000+ records & i want to
update some of its fields with the below logic.


STOCKEXCDOWNLOAD

I've ran the below query in query analyzer with no indexes on this
table.  the time taken is about 40 secs.

UPDATE StockExcDownload SET CalCheqAmt = Quantity * (CASE WHEN Price =
9999.00
THEN (SELECT MktPrice FROM Issue) ELSE Price END), BSENSEFlag = 'B',
EditDate = getdate()

But the index tuning wizard has suggested the following index to be
created.

CREATE
  INDEX [StockExcDownload1] ON [dbo].[StockExcDownload] ([Price],
[Quantity])
WITH
    DROP_EXISTING
ON [PRIMARY]


But after implementing,  the above index it the query has taken me
about 7 mins to execute.

what is the reason for this?

rameshsa***@gmail.com wrote:
Show quoteHide quoteThe subquery:

   (SELECT MktPrice FROM Issue)

is invalid unless Issue contains no more than ONE row. I suspect this
may be part of the problem but without more info I can only guess what
the solution is. Read my signature.

Why do you want to calculate an amount on the table if it can already
be derived from other tables in the database? Don't store calculated
results if you can avoid it. Put the calcs in a view or query.

Thanks a lot david.

At any time the issue table contains only one record.

i've modified the query to

UPDATE StockExcDownload SET CalCheqAmt = Quantity * (CASE WHEN Price =
9999.00 THEN 34 ELSE Price END), BSENSEFlag = 'B',
EditDate = getdate()

It has boosted a little bit (its coming down to 4.45 Mins). But i still
want to reduce the time as it is too costly for me to use this query.

And also it is one of the queries in the stored procedure.  below is
the list of queries preceding above query.  I've tried the above query
for indexing.

UPDATE StockExcDownload SET CatgNm = (SELECT DISTINCT TOP 1 CAT.CatgNm
FROM Category CAT, InvType INV
WHERE CAT.CatgNm = 'EMP' AND CAT.CatgCD = INV.CatgCD AND INV.ITCD =
StockExcDownload.InvstNm AND StockExcDownload.InvstNm = 'EMP'
ORDER BY CAT.CatgNm)  WHERE StockExcDownload.CatgNm IS NULL AND
StockExcDownload.BSENSEFlag = 'B'

UPDATE StockExcDownload SET CatgNm = 'RETAIL' WHERE CalCheqAmt <=
100000 AND StockExcDownload.BSENSEFlag = 'B'
AND StockExcDownload.CatgNm IS NULL

UPDATE StockExcDownload SET CatgNm = (SELECT DISTINCT TOP 1 CAT.CatgNm
FROM Category CAT, InvType INV
WHERE StockExcDownload.CalCheqAmt BETWEEN CAT.MinAmt AND CAT.MaxAmt AND
CAT.CatgCD = INV.CatgCD
AND INV.ITCD = StockExcDownload.InvstNm ORDER BY CAT.CatgNm)
WHERE StockExcDownload.CatgNm IS NULL AND StockExcDownload.BSENSEFlag =
'B'

UPDATE StockExcDownload SET Catgnm = (SELECT DISTINCT TOP 1 CAT.CatgNm
FROM Category CAT
WHERE StockExcDownload.CalCheqAmt BETWEEN CAT.MinAmt AND CAT.MaxAmt
ORDER BY CAT.CatgNm)
WHERE StockExcDownload.CatgNm IS NULL AND StockExcDownload.BSENSEFlag =
'B'

UPDATE StockExcDownload SET CatgNm = 'RETAIL' WHERE CatgNm IS NULL AND
BSENSEFlag = 'B'

UPDATE StockExcDownload SET SyndNm = T.SyndNm, Name = T.BrokerNm,
CityNm = T.CityNm
FROM Terminal T WHERE T.BrokerID = StockExcDownload.UserCD AND
T.TerminalCD = StockExcDownload.BranchCD
AND StockExcDownload.BSENSEFlag = 'B'

UPDATE StockExcDownload SET BrokerFlag =  LEFT(CompNm,1) FROM Company
A, Broker B
WHERE B.BrokerID = StockExcDownload.UserCD AND B.DSPML = 1 AND
StockExcDownload.BSENSEFlag = 'B'

UPDATE  StockExcDownload SET CleanDirty = 'C' WHERE OrderNo IN(SELECT
TOP 1 OrderNo FROM StockExcDownload STI
WHERE STI.ApplNo1 = StockExcDownload.ApplNo1
ORDER BY STI.ApplNo1 ASC,STI.CalCheqAmt DESC,STI.Quantity DESC)

UPDATE StockExcDownload SET CleanDirty = 'D' WHERE CleanDirty IS NULL

UPDATE StockExcDownload SET SyndNm = (Case WHEN BSENSEFlag = 'B' THEN
UserCD ELSE BrokerCD END) WHERE SyndNm IS NULL

--UPDATE StockExcDownload SET SyndNm = BrokerCD  WHERE SyndNm IS NULL
AND BSENSEFlag = 'N'
--UPDATE StockExcDownload SET SyndNm = UserCD  WHERE SyndNm IS NULL AND
BSENSEFlag = 'B'

UPDATE StockExcDownload SET Name = BranchCD  WHERE Name IS NULL

EXEC ActualCleanBidProcess

rameshsa***@gmail.com wrote:
Show quoteHide quoteIndexes on StockExcDownload aren't going to help because all these
updates will perform a scan of the entire table/clustered index anyway.

Make sure you have indexes in the other tables on the columns used in
your joins.

Combine as many of the UPDATEs as you can. Using joins rather than
subqueries in the UPDATE statements may help. IMPORTANT: Make sure you
only join on keys that are unique in the table you are making the
update from.

Of those UPDATEs, seven of them don't join to any other table. Going by
the table name it looks like this is a "staging" table for
pre-processing before you move the data elsewhere. If that's the case
then you could eliminate those 7 UPDATEs altogether. Do the work in the
INSERT statement when you load to the production tables. Even if that
isn't possible, you should be able to combine those 7 UPDATEs into one,
which will be a very significant improvement.

Where you have a very large update against data that is otherwise
static, it will often help to batch the UPDATE into smaller
transactions. For example:

SET ROWCOUNT 100000

WHILE 1=1
BEGIN

UPDATE StockExcDownload
  SET y = T.y
  FROM tbl AS T
  WHERE StockExcDownload.x = T.x
   AND StockExcDownload.y IS NULL ;

IF @@ROWCOUNT=0
  BREAK

END

SET ROWCOUNT 0

Experiment with the SET ROWCOUNT option to see what size of batch works
best for you.

Hope this helps.

Based on the UPDATE statement in the original post, the advice to add an
index does not make sense, because there is no WHERE clause in this
query, so no index will speed up the data retrieval.

Looking at the other UPDATE queries, a clustered index on (BSENSEFlag)
might increase performance.

For futher comments/suggestions, see inline.

If you have chosen to create a clustered index on (BSENSEFlag), then it
might be worth your while to split the above query in the following two:
  UPDATE StockExcDownload
  SET CalCheqAmt = Quantity * (CASE WHEN Price = 9999.00 THEN 34 ELSE
Price END)
  ,   BSENSEFlag = 'B'
  ,   EditDate = getdate()
  WHERE BSENSEFlag <> 'B'

  UPDATE StockExcDownload
  SET CalCheqAmt = Quantity * (CASE WHEN Price = 9999.00 THEN 34 ELSE
Price END)
  ,   EditDate = getdate()
  WHERE BSENSEFlag = 'B'

Is there some kind of generator that created this query? Why not write
something like this:

  UPDATE StockExcDownload
  SET CatgNm = 'EMP'
  WHERE StockExcDownload.CatgNm     IS NULL
  AND   StockExcDownload.BSENSEFlag = 'B'
  AND   StockExcDownload.InvstNm    = 'EMP'
  AND EXISTS (
    SELECT 1
    FROM       Category CAT
    INNER JOIN InvType  INV
      ON INV.CatgCD = CAT.CatgCD
    WHERE CAT.CatgNm = 'EMP'
    AND   INV.ITCD   = StockExcDownload.InvstNm
  )

There is one slight difference with your original: it will not set
CatgNm to NULL when there is no match. But since you are only updating
rows where this column is already NULL, this is actually a good thing.

The ORDER BY clause in the subquery matches the Selection List. So you
don't need to use TOP, and when you use TOP 1, there is definitely no
use for the DISTINCT keyword. You could write SELECT MIN(..) instead of
SELECT DISTINCT TOP 1 .. ORDER BY ..

This query can be combined with one of the later UPDATEs. It just needs
an UPDATE that will cover the entire table, and a CASE expression to
retain non-NULL CatgNm values.

Ouch! This is no good. The table Company is not joined to Broker or
StockExcDownload, making it a cross join, which could be very expensive!
Also, if the column CompNm originates from table Company, then the
BrokerFlag could be set to any random Company first letter.


I would change "IN" to "=".

Please note, that if there are multiple OrderNo for a particular
combination of (ApplNol, CalCheqAmt, Quantity), then the query engine
will select one of these OrderNo 'randomly'. If you want consistent
results, you might want to add OrderNo to the end of the ORDER BY
clause.

If CleanDirty is NULL for all rows, prior to the previous query, then
you can merge these two queries to something like this:

  UPDATE StockExcDownload
  SET CleanDirty = CASE WHEN OrderNo = (
    SELECT ... ) THEN 'C' ELSE 'D' END

You could also last "CatgNm = 'RETAIL'" query to this one, which would
make it something like

  UPDATE StockExcDownload
  SET CleanDirty = CASE WHEN OrderNo = (
    SELECT ... ) THEN 'C' ELSE 'D' END
  ,   CatgNm = CASE WHEN (CatgNm IS NULL AND BSENSEFlag = 'B') THEN
'RETAIL' ELSE CatgNm END

You can add this one to the previous query as well.

And this one as well.

Hope this helps,
Gert-Jan

Show quoteHide quote

How to SELECT data among 4 tables?
Help with constraint
Table design (search with ranking)
How does engine decide whether to scan or seek index
Nightmare Query