SQL Server Performance Forum – Threads Archive
expensive query
Hey guys,<br /><br />I don’t know which part of the forum I should post this question. I wrote a query and found out that my CPU is hitting almost 100%. We are on Win2k. The query updates a TEXT column inside a loop. On the same loop, I am also updating another table.<br /><br />What could be the reason why this query is eating up so much CPU? What should I modify in the code?<br /><br />Thanks guys.<br /><br />V1rt<br /><br />————–SQL CODE—————–<br />if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[audit_bitem_nwtc]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)<br />drop table [dbo].[audit_bitem_nwtc]<br />GO<br /><br />CREATE TABLE [dbo].[audit_bitem_nwtc] (<br />[loanno] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />[status] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL <br />) ON [PRIMARY]<br />GO<br /><br />– DECLARE variables<br />DECLARE @ptrval binary(16)<br />DECLARE @loanno as varchar(10)<br />DECLARE @tempcomment as varchar(255)<br />DECLARE @tempconfdate as smalldatetime<br />DECLARE @current_dt as datetime<br /><br />– DECLARE CURSOR for temporary table in memory<br />DECLARE bitem_cursor cursor for<br />SELECT k.loanno,s.confdate<br />FROM temp_bitem_flo k, nwtc_nonull_nodups_final s<br />where s.loanno=k.loanno and k.dconfirm is null<br />for update<br /><br />open bitem_cursor<br /><br />FETCH NEXT FROM bitem_cursor<br />INTO @loanno, @tempconfdate<br /><br />BEGIN TRAN<br /><br />WHILE @@FETCH_STATUS = 0<br />BEGIN<br /><br /> set @current_dt = getdate()<br /> SELECT @ptrval = TEXTPTR(k.comment),<br /> @tempcomment = convert(varchar(10),@current_dt,101) + ‘ ‘ + convert(varchar(10),@current_dt,10<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> + ‘ AUTOUPDATE DCONFIRM ‘ + convert(varchar(10),@tempconfdate,101) + ‘ ‘ + s.comment + char(13) + char(10)<br /> FROM temp_bitem_flo k, nwtc_nonull_nodups_final s<br /> where [email protected] and<br /> k.dconfirm is NULL — and<br /> –k.client=’flo'<br /> UPDATETEXT temp_bitem_flo.comment @ptrval 0 0 @tempcomment<br /><br /> UPDATE temp_bitem_flo <br /> SET temp_bitem_flo.dconfirm = nwtc_nonull_nodups_final.confdate<br /> from nwtc_nonull_nodups_final <br />INNER JOIN temp_bitem_flo <br /> ON temp_bitem_flo.loanno=nwtc_nonull_nodups_final.loanno <br /> AND [email protected]<br /> AND temp_bitem_flo.dconfirm is NULL<br /> — AND bitem.client = ‘flo'<br /><br />– update <br /> insert into audit_bitem_nwtc values (@loanno,’Converted’) <br /><br />– retrieve the next row from the cursor and put it into the variables<br /> FETCH NEXT FROM bitem_cursor<br /> INTO @loanno, @tempconfdate<br /><br />END<br /><br />CLOSE bitem_cursor<br />DEALLOCATE bitem_cursor<br /><br />–commit tranI’ve moved to this Forum. How about execution plan?. What cost each step in execution plan?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
WHat is the recovery model obtained on the database during this operation? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>