question about replace function
in [Programming SQL]
|
From: Roy Goldhammer on 11 Jul 2010 06:01 Hello there I have table of 100,000 records I made a replace on each record in some fields. It works fine but it cost almost 100% cpu. does someone knows why?
From: Bob Barrows on 11 Jul 2010 09:11 Roy Goldhammer wrote: > Hello there > > I have table of 100,000 records > > I made a replace on each record in some fields. > > It works fine but it cost almost 100% cpu. does someone knows why? Why not?
From: Roy Goldhammer on 11 Jul 2010 10:56 Whell bob I need to build an automatic process which do this every week. I'm thinking that this process should not be done by SQL but by app or clr. to explain this i need some documentation which describes why when doing replace on huge table cause 100% cpu. "Bob Barrows" <reb01501(a)yahoo.com> wrote in message news:Zoj_n.7356$Zp1.3759(a)newsfe15.iad... > Roy Goldhammer wrote: >> Hello there >> >> I have table of 100,000 records >> >> I made a replace on each record in some fields. >> >> It works fine but it cost almost 100% cpu. does someone knows why? > > Why not? >
From: Bob Barrows on 11 Jul 2010 11:45 Again, I have to ask: why wouldn't it? It can be a pretty cpu-intensive operation, especially if the updates cause index rebuilds, and even worse if the process causes transaction log/data file resizes. Take a look at the execution plan to see where the cost is. If using SQL 2005+, take a look at the Disk Usage report to see if the operation causes data/log file resizes. How long does the process take? It can't be too long for only 100,000 rows. Why do you care if cpu hits 100 % for a minute or so, especially if it's scheduled for off-hours? And why would you think the operation would be less cpu intensive if done by an external application? The same updates have to occur in the database ... Perhaps you can reduce the impact by updating the data in batches, so as to reduce the impact on the transaction log size (if you have the database set to Simple Recovery) Roy Goldhammer wrote: > Whell bob > > I need to build an automatic process which do this every week. > > I'm thinking that this process should not be done by SQL but by app > or clr. > to explain this i need some documentation which describes why when > doing replace on huge table cause 100% cpu. > > "Bob Barrows" <reb01501(a)yahoo.com> wrote in message > news:Zoj_n.7356$Zp1.3759(a)newsfe15.iad... >> Roy Goldhammer wrote: >>> Hello there >>> >>> I have table of 100,000 records >>> >>> I made a replace on each record in some fields. >>> >>> It works fine but it cost almost 100% cpu. does someone knows why? >> >> Why not?
From: Erland Sommarskog on 11 Jul 2010 17:57 Roy Goldhammer (royg(a)yahoo.com) writes: > I need to build an automatic process which do this every week. > > I'm thinking that this process should not be done by SQL but by app or > clr. > > to explain this i need some documentation which describes why when doing > replace on huge table cause 100% cpu. I'm with Bob, why not? After all, all we know is that you have told us that when you drive 500 km, you car consumes a lot of petrol. This replace operation, does it affect all values in the table? Does the replace operation change the size of the values? That is, if there are for instance varchar, do they become longer? Is there a trigger on the table? Are the columns affected by the operation indexed? Clustered index? Does the operation include the primary key? In such case, are there referencing cascading foreign keys? How complex is the replace operation? Just a single replace()? Or are there tons of replace nested? In the latter case, using a CLR function could help. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|
Pages: 1 Prev: system view for functions Next: setting [] in like operator |