June 19, 2009 at 7:07 am
All,
I'm new to the forums, so I hope you're all the genius' I'm hoping for!
I'm trying to tune a table and UPDATE statement.
TABLE
The table has 300,000 rows and only 6 columns/fields.
There is a clustered index on the first three fields.
The table is heavily used ALL day long. Lots and lots of SELECTS and UPDATES
UPDATE statement
A job runs every 30mins and updates this table. An explain analysis shows that the UPDATES are particularly slow (0.4-1.8 seconds per UPDATE, all on a single row).
We found that when this table was large (around 1 million rows) SQL Server automatically escalated to Page Level locking, so we've added the WITH ROWLOCK hint to the UPDATE statement. This has stopped a problem with had with other users being locked while this job runs and while we know there is a higher admin on row-level locking, we still think this UPDATE time is far too high. UPDATEs on other tables are measured in microseconds, not seconds!
The WHERE clause in this UPDATE statement references only ALL three fields from the clustered index.
We've tried re-running stats and re-running the job
We've triend reorganising the table (through SELECT INTO another, DROP, SELECT INTO back to recreate).
Nothing so far has worked.
Has anyone any other thoughts?
I'm struggling now to think of further ways to tune this and I would really welcome some helpful insights from experienced SQL gurus!
Regards
Jamie Neilan
June 19, 2009 at 7:18 am
Please provide us with ur table structure; and the Update Query that you are using.
Check if the indexes need reindexing or reorganising on the table.
June 19, 2009 at 7:30 am
TABLE STRUCTURE
Name: TABLE
FIELD1 varchar 3
FIELD2 tinyint 1
FIELD3 varchar 12
FIELD4 varchar 1
FIELD5 varchar 8
FIELD6 varchar 6
FIELD7 varchar 50
INDEX STRUCTURE
Name: TABLE~0
Clustered: Yes
Cols: FIELD1, FIELD2, FIELD3
UPDATE STATEMENT
UPDATE
TABLE WITH (UPDLOCK ROWLOCK)
SET
FIELD4 = 'X',
FIELD6 = ''
WHERE
FIELD1 = '400',
AND FIELD2 = 10
AND FIELD3 = '0004141278'
It's been recently recreated (reorg, including recreation of the table from a copy and recreation of the index from script)
June 19, 2009 at 8:11 am
Please run the below to see how the indexes are for that table. Also, worth looking at the execution plan of the Update script.
Check to see if it needs reorgainising.
DECLARE @db_id SMALLINT;
DECLARE @object_id INT;
SET @db_id = DB_ID(N'@dbname');
SET @object_id = OBJECT_ID(N'@tablename.dbo.@tablename');
IF @db_id IS NULL
BEGIN;
PRINT N'Invalid database';
END;
ELSE IF @object_id IS NULL
BEGIN;
PRINT N'Invalid object';
END;
ELSE
BEGIN;
SELECT a.index_id, name, avg_fragmentation_in_percent,page_count
FROM sys.dm_db_index_physical_stats (@db_id, @object_id,NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
where a.avg_fragmentation_in_percent>5 and a.avg_fragmentation_in_percent30
END;
GO
June 19, 2009 at 8:34 am
Invalid object name 'sys.dm_db_index_physical_stats'
Invalid object name 'sys.indexes'
tried running it in both the relevant DB and in the master DB....
June 19, 2009 at 8:35 am
You want details like density, fill level, depth, etc. on the index?
I can get some stats data if that's what you're after?
June 19, 2009 at 2:44 pm
Yes if you can provide me that.
June 19, 2009 at 3:30 pm
Statistics for INDEX 'ZVNDTRANSACTIONS~0'.
Updated Rows Rows Sampled Steps Density Average key length
==================== ==================== ==================== ====== ==================== ====================
Jun 18 2009 10:40PM 303013 303013 1 0 14
All density Average Length Columns
==================== ====================
1 3 MANDT
0.33333334 4 MANDT, VND_INT_NUMBER
3.3001884E-6 14 MANDT, VND_INT_NUMBER, DOCUMENT_NUMBER
RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
============ ==================== ==================== ==================== ====================
400 0 303013 0 0
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
June 19, 2009 at 3:59 pm
May depend on some locking issues with other transactions. Try to use a WHILE loop around your UPDATE statement and work in 1000 row blocks. You can use "SET ROWCOUNT" to restrict the count of affected rows on SQL Server 2000.
If the problem remains please have a look to Gail's article about needed information depending on performance problems here:
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
It also explains how to collect SQL Server 2000 information.
Flo
June 21, 2009 at 3:51 am
Here is the UPDATE statement. It's in SAP Advanced Business Application Programming (ABAP) language, but you can get the gist. You can see that there is a COMMIT on every UPDATE statement.
LOOP AT t_zvndtransactions INTO w_zvndtransactions.
MOVE: w_zvndtransactions-document_number TO w_doc_num,
w_zvndtransactions-vnd_file_name TO w_file_name.
EXEC SQL.
UPDATE ZVNDTRANSACTIONS WITH (UPDLOCK ROWLOCK)
SET PROCESSED_INDIC = 'X',
VND_FILE_NAME = :W_FILE_NAME
WHERE MANDT = :SY-MANDT
AND VND_INT_NUMBER = :P_INTNUM
AND DOCUMENT_NUMBER = :W_DOC_NUM
COMMIT
ENDEXEC.
ENDLOOP.
June 21, 2009 at 4:27 am
neilan (6/21/2009)
Here is the UPDATE statement. It's in SAP Advanced Business Application Programming (ABAP) language, but you can get the gist. You can see that there is a COMMIT on every UPDATE statement.
LOOP AT t_zvndtransactions INTO w_zvndtransactions.
MOVE: w_zvndtransactions-document_number TO w_doc_num,
w_zvndtransactions-vnd_file_name TO w_file_name.
EXEC SQL.
UPDATE ZVNDTRANSACTIONS WITH (UPDLOCK ROWLOCK)
SET PROCESSED_INDIC = 'X',
VND_FILE_NAME = :W_FILE_NAME
WHERE MANDT = :SY-MANDT
AND VND_INT_NUMBER = :P_INTNUM
AND DOCUMENT_NUMBER = :W_DOC_NUM
COMMIT
ENDEXEC.
ENDLOOP.
Hi neilan
I never worked with ABAP but I see the problem within your code. You run a client-side cursor to handle your 300K rows. Since you are using SQL Server 2000 unfortunately there is XML or UDT way to handle this, but a classic way working with a temp table.
Try to change your code like this:
I bet you will end up with about 1 minute or less.
Flo
June 21, 2009 at 5:03 am
Thanks for the suggestions Florien, I'll try out these suggestions and get back to you. I'm not sure whether t_zvndtransactions is temp already (I think it is) but I think the LOOP is working directly to the table (problably with pseudoname w_zvndtransactions), so I'll work through these suggested improvements with the developer and see how we go. I guess we might be able to remove the ROWLOCK hint then too, we'll see how that goes.
Is there a points awarding system on this forum?
June 21, 2009 at 5:17 am
neilan (6/21/2009)
Thanks for the suggestions Florien, I'll try out these suggestions and get back to you. I'm not sure whether t_zvndtransactions is temp already (I think it is) but I think the LOOP is working directly to the table (problably with pseudoname w_zvndtransactions), so I'll work through these suggested improvements with the developer and see how we go. I guess we might be able to remove the ROWLOCK hint then too, we'll see how that goes.
If your t_zvndtransactions already represents a temp table it would be great you can just use this to handle a bulk update like this (hope I caught all criteria 😉 ):
DECLARE
@YourP_INTNUM INT,
@YourSY_MANDT VARCHAR(10)
SELECT
@YourP_INTNUM = -1,
@YourSY_MANDT = 'Dont know'
UPDATE z SET
z.PROCESSED_INDIC = 'X',
z.VND_FILE_NAME = zt.vnd_file_name
FROM ZVNDTRANSACTIONS z
JOIN t_zvndtransactions zt
ON z.DOCUMENT_NUMBER = zt.document_number
WHERE
z.VND_INT_NUMBER = @YourP_INTNUM
AND z.MANDT = @YourSY_MANDT
Is there a points awarding system on this forum?
Nope, there is no points awarding here. But it's always great to get feedback if something was helpful 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply