How do I improve performance? 1

[Wyldcard9]

The follow code is running across 1300+ records in a table, and may have 10-20 records in it. But it is taking 4 and a half minutes. Other stored procedures take like 10 seconds and have way more records (like 200K), and use the same structure.

CREATE PROC dbo.kv_updt_RECIP_AUTH_ONWAY2IBM AS
DECLARE @fundnum varchar(2)
DECLARE @scandate datetime
DECLARE @batchnum varchar(25)
DECLARE @ssn varchar(9)
DECLARE @local varchar(4)
DECLARE @sigdate datetime
DECLARE @annuity_doc_type varchar(50)
DECLARE @annuity_output_type varchar(3)
DECLARE @pension_doc_type varchar(50)
DECLARE @pension_output_type varchar(3)
DECLARE @health_doc_type varchar(50)
DECLARE @health_output_type varchar(3)
DECLARE @wpasid varchar(7)
DECLARE @firstname varchar(15)
DECLARE @lastname varchar(30)

DECLARE the_cursor CURSOR for

SELECT fundnum, scan_date, batchnum, ssn, local, sigdate, annuity_doc_type, annuity_output_type, pension_doc_type, pension_output_type, health_doc_type, health_output_type, wpasid, first_name, last_name

FROM [RECIP Authorization Forms].[dbo].[BACKfromIBM]
WHERE Last_name IS NOT NULL
OPEN the_cursor
FETCH NEXT FROM the_cursor
into @fundnum, @scandate, @batchnum, @ssn, @local, @sigdate, @annuity_doc_type, @annuity_output_type, @pension_doc_type, @pension_output_type, @health_doc_type,
@health_output_type, @wpasid, @firstname, @lastname

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE [RECIP Authorization Forms].[DBO].[ONWAY2IBM]
SET First_Name = @firstname,
Last_Name = @lastname,
Wpasid = @wpasid

WHERE Fundnum = @fundnum
AND Scan_Date = @scandate
AND SSN = @ssn
and not SSN = '999999999'
and not SSN = '000000000'

UPDATE [RECIP Authorization Forms].[DBO].[ONWAY2IBM]
SET First_Name = @firstname,
Last_Name = @lastname,
SSN = @ssn
WHERE Fundnum = @fundnum
AND Scan_Date = @scandate
AND Wpasid = @wpasid
and not WPASID = '9999999'
and not WPASID = '0000000'

FETCH NEXT FROM the_cursor into @fundnum, @scandate, @batchnum, @ssn, @local, @sigdate, @annuity_doc_type, @annuity_output_type, @pension_doc_type, @pension_output_type, @health_doc_type, @health_output_type, @wpasid, @firstname, @lastname

END
CLOSE the_cursor
DEALLOCATE the_cursor
GO



Any advice ya'll have would be much appreciated. I am a Senior Mainframe programmer, who has been maintaining the SQL DB's (on the PC side) on top of it. So I am not formally trained in the PC side. I have gotten things done through the school of hard knocks, and advice on this forum when it comes to SQL.

 

[gmmastros]

I suspect you can accomplish the same thing in a set based way without using a cursor. The cursor method would probably be much faster than 'those other stored procedures that only take 10 seconds'.

If you insist on keeping the cursor... at least get rid of the columns that you are not using. Within the loop, it appears as though you are only using 4 fields, but yet you are selecting about 15 fields.

If you want to accomplish this in a set based way, please show some sample data from both tables. Please... No real names or addresses, ssn's, etc....

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Wyldcard9]

gmmastros,
I am not familiar with the terminology. What do you mean by set? All the other stored procs also use a cursor, and do not use all the fields.

 

[gmmastros]

Before I begin explaining, let me mention that I don't know your skill level so if I explain something that you already know, please don't be offended.

Set based operations operate on a set of data. You can think of this as multiple rows (nearly simultaneously).

Cursor based operations usually operate on a single record at a time. This is also how many programers that are unfamiliar with set based operations are most comfortable doing things. After all... it is logical. Update each row setting some value = to some other value, etc...

Unfortunately/fortunately SQL Server is optimized for set based operations. It performs them very quickly and efficiently. Let's look at the following query as an example:

UPDATE ToIbm
SET    ToIbm.First_Name = FromIbm.First_Name,
       ToIbm.Last_Name = FromIbm.Last_Name,
       ToIbm.Wpasid = FromIbm.Wpasid
From   [RECIP Authorization Forms].[DBO].[ONWAY2IBM] [purple]As ToIbm[/purple]
       Inner Join [RECIP Authorization Forms].[dbo].[BACKfromIBM] [purple]As FromIbm[/purple]
         On  ToIbm.FundNum = FromIbm.FundNum
         And ToIbm.Scan_Date = FromIbm.Scan_Date
         And ToIbm.SSN = FromIbm.SSN
Where  ToIbm.SSN Not In ('999999999', '000000000')
       And FromIbm.Last_name IS NOT NULL

The part in [purple]purple[/purple] is simply an alias. It's a short hand notation so that you don't need to specify the database.owner.table all over the place.

The join part allows you to work with mutiple tables. This is considered a set based method because it works on a set of data. Specifically, it's all the data meeting the conditions of the ON clause and the Where clause.

I urge you to study this query. Before you run it, I urge you to make a backup of your database first. Then run it.

Also, this appears on only do 1/2 of what the current process is doing. It should be simple to change it to do the other 1/2.

If you have any other questions, please don't hesitate to ask.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Wyldcard9]

Thank you so much. I am learning a lot about this.

 

[gmmastros]

If you do run this... please post the execution time with the current 'cursor' method and the new 'set based' method. I'm curious to see what the performance difference is.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Wyldcard9]

gmmastros,

Sorry for the delay. I got way-layed with some other things.

I put it in today, and after going over the code with a fine toothed comb, and a few small modifications - I ran it. The improvement went from 4:50-4:55 (four minutes and 50 seconds to four minutes and 55 seconds range) to a run time of 1 second time range.

It is a drastic improvement.

Thank you so much.

 

[gmmastros]

SHAZAM! From approximately 250 seconds down to 1. Keep it up and you'll be removing all your progress bars and hour glass cursors (mouse cursor that is).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[vbkris]

one more thing,
if you are using a cursor for fetch operations only then the following attributes will improve performace:

DECLARE the_cursor CURSOR
FAST_FORWARD READ_ONLY
for

Known is handfull, Unknown is worldfull

 

[hmckillop]

george

A question - do you work fulltime on Tek-tips, I get a chance to review comments, with a particular interest in performance queries, and I always see you have responded in very quick time.
And credit where credit is due, your posts are always extremely helpful and well thought out - so I concluded that you must work full time on tek-tips (you know 2+2=5).
Keep up the good work!



"I'm living so far beyond my income that we may almost be said to be living apart

 

[gmmastros]

hmckillop,

I do not work full time on tek-tips. I do enjoy helping others. There was a time when I needed help, and others here at tek-tips have been kind to me.

Sometimes I do think that I spend too much time there, though.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[fredericofonseca]

gmmastros is not the only one that spends a huge time here helping people.

Just look at strongm, xlbo, phv, skipvought amongst many others (and this is only on the forums I use).

I sometimes manage to beat them to their answers, but its rare. they seem to "live" here!!!.

(an average of 4 hours a day browsing here myself.)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com