June 4, 2008 at 7:26 am
I need to update certain records in a table on DB1. This table then joins with another table on DB2. And it uses a function to trim the fields from leading spaces.
I have written the query but it takes forever to execute. I has been running for 55 minutes now and still it isn't finished.
I am pasting the script here - maybe someone can tell me why it is so slow? Maybe you have an alternative/faster option?
UPDATE DB1.dbo.Table1
SET column1 = Table2.column1,
FROM DB1.dbo.Table1 AS Table1 with (nolock)
INNER JOIN DB2.dbo.Table2 AS Table2 with (nolock)
ON dbo.func_Trim(Table1.PKcolumn) = dbo.func_Trim(Table2.FKcolumn)
WHERE
Table1.AnotherColumn > value --normal condition needed in this scenario
AND dbo.func_Trim(Table1.Name) <> dbo.func_Trim(Table2.Name) --another condition of this scenario
June 4, 2008 at 7:30 am
Let me also add that the UPDATE statement is only suppose to update about 4,000 records based on the Join and the Conditions. So it's not that many records....
Thanks
June 4, 2008 at 8:20 am
Get an estimated execution plan for the query. It won't be terribly accurate, but it should give you some information as to why you're seeing things run so incredibly slow.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 4, 2008 at 8:21 am
BTW, running the functions against the columns means you're only going to get table scans. The indexes will not be used.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply