Friday, 15 April 2011

sql server - TSQL : Is replace better than ltrim/rtrim -



sql server - TSQL : Is replace better than ltrim/rtrim -

is replace improve ltrim/rtrim. have no spaces between words, because running on key column.

update [db14].[dbo].[s_item_60m] set [item_id]=ltrim(rtrim([item_id])) item_id having non-clustered index

shall disable index improve performance?

windows 7, 24gb ram , sql server 2014

this query running 20 hours , canceled it. thinking run replace instead of ltrim/rtrim performance reasons.

ssms studio crashed.

now can see running in activity monitor

error log says flushcache: cleaned 66725 bufs 25872 writes in 249039 ms (avoided 11933 new dirty bufs) db 7:0

please guide , suggest me.

the throughput of mass updates not depend on single phone call per row ltrim or rtrim. arbitrarily pick highly visible element of query , consider responsible bad performance. @ query plan see what's beingness done physically. also, create familiar mass update techniques (such dropping , recreating indexes).

note, contrary popular belief mass update rows in 1 statement fastest option. strategy can cause blocking , high log usage. has best throughput because optimizer can optimize dml executing in 1 plan. if splitting dml chunks thought sql server automatically part of plan.

sql-server tsql

No comments:

Post a Comment