Optimizing queries

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?rameshsaive@.gmail.com wrote:
> 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?
The 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.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||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|||rameshsaive@.gmail.com wrote:
> 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
Indexes 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.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||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.
"rameshsaive@.gmail.com" wrote:
> 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()
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'

> 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'
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.

> 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'
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 ..

> 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'
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.

> 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'
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.

> 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)
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.

> UPDATE StockExcDownload SET CleanDirty = 'D' WHERE CleanDirty IS NULL
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

> UPDATE StockExcDownload SET SyndNm = (Case WHEN BSENSEFlag = 'B' THEN
> UserCD ELSE BrokerCD END) WHERE SyndNm IS NULL
You can add this one to the previous query as well.

> --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
And this one as well.
Hope this helps,
Gert-Jan

> EXEC ActualCleanBidProcess