expensive query | SQL Server Performance Forums

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 tran
I’ve moved to this Forum. How about execution plan?. What cost each step in execution plan?
Luis Martin
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
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.