very ineffienct update query

Question

Post reply

very ineffienct update query

garryurban

SSC-Addicted

Points: 443
More actions
June 22, 2006 at 8:39 am

#113549

UPDATE [v2_beta_small].[dbo].[beta]
set [v2_beta_small].[dbo].[beta].[prv_location] = [v2_lookup].[dbo].[nsg06may].[location]
from [v2_beta_small].[dbo].[beta]
join [v2_lookup].[dbo].[nsg06may].[col016] on replace(replace( [v2_lookup].[dbo].[nsg06may].[location],' ',''),' ','') = replace(replace([v2_beta_small].[dbo].[beta].[prv_location],' ',''),' ','')
I have tested the the above query with Index Tuning Wizard, it makes no suggestions as all the fields used are either ina clustered index or in an nc index on their own

I have looked at the execution plan and the join creates a hash join, but it doesn't look excessive

the databases referenced are each on their own disks, the logs are a seperate disk, as is tempDB

when the querythe most notable thing from perf monis that it reads from the drive where the [v2_beta_small] database resides for a few seconds, then it just reads and writes for a long time from the drive that tempdb is on, it never appears to read from the drive where [v2_lookup] resides

can anyone help, point me to anything, make any suggestions what to do next?

TIA (:,)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 1

I think 2 things can be done here.

1. access remote db from link server.

2. First dump all the records in #temp table from select & then update the main table from the #temp table.

------------
Prakash Sawant

http://psawant.blogspot.com

PW-201837 SSC-Insane Points: 20805 More actions · Answer 2

Question:

What exactly is [v2_lookup].[dbo].[nsg06may].[col016] ?

It is in the part of the query where a table is expected, but the 4-part naming indicates either a linked server (in which case [dbo] is in the wrong location) or a table column, in which case you should get a syntax error due to coding a column object where a table is expected.

Secondly, using a series of functions on the joined columns eliminates the possibility of an index seek operation.

garryurban SSC-Addicted Points: 443 More actions · Answer 3

Question:

What exactly is [v2_lookup].[dbo].[nsg06may].[col016]

an error in typing, in the actual query it reads [v2_lookup].[dbo].[nsg06may]

i will look at dealing with the functions as I did not realise they would affect the index seek!!!

Thanks for both your help so far...

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

You can also make things easier on yourself (and anyone else reading the code) by using table aliases:

UPDATE t1

SET [prv_location] = t2.[Location]

FROM [v2_beta_small].[dbo].[beta] As t1

INNER JOIN [v2_lookup].[dbo].[nsg06may] As t2

ON (

replace(replace(t2.[location],' ',''),' ','') =

replace(replace(t2.[prv_location],' ',''),' ','')

)

garryurban SSC-Addicted Points: 443 More actions · Answer 5

i agree with the table aliases, do ordinarily, but stopped on updates because of some problems referncing the update table, but will start again and see

as to the update query...

I removed the spaces from the join columns using an update and ran the query without the repalce functions - it now works a treat

thanks ever so much

stax68 SSChampion Points: 11711 More actions · Answer 6

Don't forget you will need to change the processes which populate the column. You should also enforce any check constraints you can ( eg not like '% %'). A temporary solution might be to define calc columns using the replace() function, then index those columns.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Gabor Nyul SSCrazy Eights Points: 9459 More actions · Answer 7

And as a general remark: As long as you have functions (replace, substring...) in your WHERE or ON clauses the optimiser would never use any indexes.

Bye
Gabor

garryurban SSC-Addicted Points: 443 More actions · Answer 8

yes, thank you I realise this now.

We are currently taking steps to redesign the database and the queries to deal with this.

Thanks again!