March 9, 2007 at 11:29 am
Experts,
I need some help in tuning this query.
UPDATE TableA
SET flag = NULL, core = NULL, staging = GETDATE()
WHERE inn_pref in (SELECT inn FROM TableB WHERE root = 1 AND status = 'A')
AND status = 'A'
Table A: has clustered index on inn_pref
Table B: Has clustered index on inn
No other indexes exisit on these tables. I know this is vague..but it possible to add any index that would speed up these update statements.
March 9, 2007 at 12:55 pm
Without knowing data volumes and data distribution of the values in the indexed columns, I would try variations that remove the IN (SELECT ...)
Update A
Set flag = NULL, core = NULL, staging = GETDATE()
From TableA As A
Where Status = 'A'
And Exists (
Select *inn FROM TableB As B
Where B.root = 1
And B.status = 'A'
And B.inn = A.inn_pref
)
Update A
Set flag = NULL, core = NULL, staging = GETDATE()
From TableA As A
Inner Join
(
Select Distinct inn
From TableB
Where Status = 'A'
And root = 1
) dt
On (dt.inn = A.inn_pref)
Where A.Status = 'A'
March 9, 2007 at 1:09 pm
PW's correct. It is difficult without knowing your data. Along with changing your UPDATE, you may also benefit from a covering index on TableB Root, Status. Here's an example that you can run in QA and watch the exectuion plans. Run the first section with the DDL stuff first, then run the 2 updates (with the index create) next and look at the execution plans. Using a JOIN along with the covering index seems to work the best here with limited data. I would suggest playing around with the UPDATE and the use of a covering index on your system with your data to find the best answer.
-- RUN THIS SECTION FIRST
DROP TABLE tablea
CREATE TABLE tableA (inn_pref int, flag int, core int, staging datetime, status CHAR(1))
CREATE CLUSTERED INDEX IDX_TableA_Inn_Pref ON tableA (Inn_pref)
INSERT INTO TableA
SELECT 1,0,0,DATEADD(dd,-1,GETDATE()),'A' UNION ALL
SELECT 1,0,0,DATEADD(dd,-1,GETDATE()),'A' UNION ALL
SELECT 1,0,0,DATEADD(dd,-1,GETDATE()),'A' UNION ALL
SELECT 1,0,0,DATEADD(dd,-1,GETDATE()),'A'
DROP TABLE tableb
CREATE TABLE tableB (inn int, ROOT int, status CHAR(1))
CREATE CLUSTERED INDEX IDX_TableB_Inn ON tableb (inn)
INSERT INTO tableb
SELECT 1, 1, 'A'
--RUN THIS SECTION NEXT
UPDATE TableA
SET flag = NULL, core = NULL, staging = GETDATE()
WHERE inn_pref in (SELECT inn FROM TableB WHERE root = 1 AND status = 'A')
AND status = 'A'
CREATE INDEX IDX_TableB_Root_Status ON tableB (ROOT,status)
UPDATE TableA
SET flag = NULL, core = NULL, staging = GETDATE()
WHERE inn_pref in (SELECT inn FROM TableB WHERE root = 1 AND status = 'A')
AND status = 'A'
UPDATE A
SET flag = NULL, core = NULL, staging = GETDATE()
FROM TableA A
INNER JOIN TableB B
ON A.inn_pref = B.Inn
WHERE B.root = 1 AND A.status = 'A' AND B.status = 'A'
March 12, 2007 at 6:51 am
ok - so I can't see the data but run a plan and if the select within the IN statement is scanning the clustered index or doing a bookmark index create a secondary index on the three columns.
It depends on many things but ultimate performance is usually gained by using covered indexes, pointless for the table being updated ( here ) but if the second table is much wider than the three columns in the select then a covered index will aid performance.
You might get a boost by creating a secondary index on the clustered index for your first table, try it and see if the io decreases - check the plan.
Updates can be indexed just as you would a select - don't quite follow all the query rewrites ??
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 12, 2007 at 9:53 am
'don't quite follow all the query rewrites ?? '
Colin, I played around with re-writing the IN clause to a JOIN out of habit. Although the improvement is only slight, I have seen many instances where a JOIN performs better than the IN clause and vice-versa so I tend to compare both. If you review the query plans for this as well as the I/O stats, you'll see that once the covering index is created, the IN clause version of the query want to use a working table where the JOIN does not. The I/Os in terms of logical reads are better in the version with the JOIN and the covering index than in the original query with the covering index. Here is the same example, but with the statistics on and the buffers being cleaned up (I know that you know how to do this, but I am re-posting for the possible benefit of others less skilled than yourself).
-- RUN THIS SECTION FIRST
DROP TABLE tablea
CREATE TABLE tableA (inn_pref int, flag int, core int, staging datetime, status CHAR(1))
CREATE CLUSTERED INDEX IDX_TableA_Inn_Pref ON tableA (Inn_pref)
INSERT INTO TableA
SELECT 1,0,0,DATEADD(dd,-1,GETDATE()),'A' UNION ALL
SELECT 1,0,0,DATEADD(dd,-1,GETDATE()),'A' UNION ALL
SELECT 1,0,0,DATEADD(dd,-1,GETDATE()),'A' UNION ALL
SELECT 1,0,0,DATEADD(dd,-1,GETDATE()),'A'
DROP TABLE tableb
CREATE TABLE tableB (inn int, ROOT int, status CHAR(1))
CREATE CLUSTERED INDEX IDX_TableB_Inn ON tableb (inn)
INSERT INTO tableb
SELECT 1, 1, 'A'
--RUN THIS SECTION NEXT
DBCC dropcleanbuffers
DBCC freeproccache
SET STATISTICS IO ON
UPDATE TableA
SET flag = NULL, core = NULL, staging = GETDATE()
WHERE inn_pref in (SELECT inn FROM TableB WHERE root = 1 AND status = 'A')
AND status = 'A'
CREATE INDEX IDX_TableB_Root_Status ON tableB (ROOT,status)
DBCC dropcleanbuffers
DBCC freeproccache
UPDATE TableA
SET flag = NULL, core = NULL, staging = GETDATE()
WHERE inn_pref in (SELECT inn FROM TableB WHERE root = 1 AND status = 'A')
AND status = 'A'
DBCC dropcleanbuffers
DBCC freeproccache
UPDATE A
SET flag = NULL, core = NULL, staging = GETDATE()
FROM TableA A
INNER JOIN TableB B
ON A.inn_pref = B.Inn
WHERE B.root = 1 AND A.status = 'A' AND B.status = 'A'
March 13, 2007 at 2:28 am
as I initially said it depends upon the data. I agree that sometimes a joined update will be better, however it just depends. The simplest solution without changing the code was to add a covered index, depending upon the width of the table and the number of rows being updated this may or may not give significant gains.
Like many aspects of tuning there often isn't an absolute answer.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply