Replacing Cursor with UDF slower...

Question

Post reply

Replacing Cursor with UDF slower...

Farrell Keough

SSCoach

Points: 17414
More actions
April 21, 2006 at 7:17 am

#167975

We have an old SP we use for our final output in reporting. Basically, it concatenates various International Codes, Names, Percentages, and Markers to each Project.
It was initially coded as a Cursor. I wrote a UDF which accomplishes the same thing working under the assumption that the UDF would be faster...
I am not including data as this is pretty basic stuff (Brazil, 231, 15%, Marker 3) and would include a huge amount of data to include. Suffice to say, one Project could easily have none, one, or many International Code records to concatenate.
This is the cursor. I put the basic International data, (from two tables) into a #TemporaryTable called #intl1. This is joined to the #Final table which contains the data for the final output into the report:
       --------------------------------------------------------

-- International Codes
SELECT C.Country_Name, i.id_intl, i.award_id, i.country_code, i.pct, i.intl_marker
INTO #intl1
FROM tb_intl i
     LEFT JOIN Countries C ON( i.country_code = C.NIH_State_Country_Code)

CREATE CLUSTERED INDEX [CIX_id_intl] ON [#intl1]( [id_intl] ASC)
CREATE INDEX [IX_award_id] ON [#intl1]( [award_id])

DECLARE int_csr CURSOR FAST_FORWARD FOR
       SELECT i.id_intl
       FROM #intl1 i
            INNER JOIN #final f ON( i.award_id = f.award_id)
       GROUP BY i.id_intl
       ORDER BY i.id_intl

OPEN int_csr
FETCH int_csr INTO @id_intl
WHILE @@fetch_status = 0
BEGIN
       UPDATE #final SET
              intl_codes1 = ISNULL( f.intl_codes1, '') + ' ' +
                           CONVERT( varchar, ISNULL( i.country_code, '')) +
                           ' ' + ISNULL( i.country_name, '') + ' ' +
                           CONVERT( varchar, ISNULL( i.pct, '')) + ' ' +
                           CONVERT( varchar, ISNULL( i.intl_marker, ''))
       FROM #final f
            INNER JOIN #intl1 i ON( f.award_id = i.award_id
                           AND i.id_intl = @id_intl)
FETCH int_csr INTO @id_intl
END
CLOSE int_csr
DEALLOCATE int_csr

       --------------------------------------------------------
The next section is the UDF I wrote to replace this cursor, followed by the use of the UDF within the stored procedure.
       --------------------------------------------------------

( @AwardID integer)
RETURNS varchar(1000)
AS
BEGIN
     DECLARE @IntlCodes varchar(1000)
     SET @IntlCodes = 'INTL '
     SELECT @IntlCodes = CASE @IntlCodes
                              WHEN ''
                              THEN COALESCE( @IntlCodes, '')
                              ELSE COALESCE( @IntlCodes, '')
                         END + ' ' +
                         COALESCE( CONVERT( varchar, tb_intl.country_code), '') + ' ' +
                         COALESCE( Countries.country_name, '') + ' ' +
                         COALESCE( CONVERT( varchar, tb_intl.pct), '') + ' ' +
                         COALESCE( CONVERT( varchar, tb_intl.intl_marker), '')
     FROM tb_intl
          LEFT JOIN Countries ON( tb_intl.country_code = Countries.NIH_State_Country_Code)
     WHERE tb_intl.award_id = @AwardID

     RETURN( @IntlCodes)
END

       --------------------------------------------------------

-- International Codes
UPDATE #final SET
     intl_codes1 = dbo.GetInternationalCodes( award_id)

       --------------------------------------------------------
While the UDF returns the same results, it is substantially slower. I tested it by running one of our programs with numerous International Codes 50 times each and got the following results:

       --------------------------------------------------------

DECLARE @Counter integer
SET @Counter = 1
WHILE @Counter <= 50 -- 0:26:04 seconds
BEGIN
        EXECUTE _africa_aids '2005', '_africa_aids' – with cursor
        SET @Counter = @Counter + 1
END

       --------------------------------------------------------

DECLARE @Counter integer
SET @Counter = 1
WHILE @Counter <= 50 -- 0:33:22 seconds
BEGIN
        EXECUTE _africa_aids_UDF '2005', '_africa_aids_UDF' -- with UDF
        SET @Counter = @Counter + 1
END

Any advice would be greatly appreciated!
tia

Farrell
I wasn't born stupid - I had to study.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eddie Wuerch SSChampion Points: 12536 More actions · Answer 1

When using the udf like this:

UPDATE #final SET

intl_codes1 = dbo.GetInternationalCodes( award_id)

you're still performing row-by-row actions, and hopping off to a separate function each time to do it. It should be slower.

Can you build this into a set-based operation? Something like:

SET CONCAT_NULL_YIELDS_NULL ONUPDATE #final    SET intl_codes1 = 'INTL '-- what does that CASE statement do? IsNull(...+ ' ' , '') +       IsNull(CONVERT(varchar(size), tb_intl.country_code) + ' ' , '') +       IsNull(Countries.country_name + ' ' , '') +       IsNull(CONVERT(varchar(size), tb_intl.pct + ' ' ), '') +       IsNull(CONVERT(varchar(size), tb_intl.intl_marker), '')   FROM #final f INNER JOIN        owner.tb_intl   ON f.award_id = tb_intl.award_id LEFT JOIN        owner.Countries ON tb_intl.country_code = Countries.NIH_State_Country_Code

-Eddie

Eddie Wuerch
MCM: SQL

Farrell Keough SSCoach Points: 17414 More actions · Answer 2

Thanks! I tried it and although over 50 iterations it should a slight improvement, it wasn't that much faster; which really surprised me!

I will keep messing with approaches. (I had not realized the UDF would still deal with each record independently rather than en masse..., but that makes sense).

I wasn't born stupid - I had to study.

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

I would like to keep this alive for a bit longer as I have replaced yet another cursor in the same fashion that Eddie Wuerch suggested, but I still get only seconds improvement when I run this 50 times.

Anybody have an idea as to why a Cursor can run almost as quickly as a set based approach?

tia

Farrell

I wasn't born stupid - I had to study.

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Because your "set based approach" is actully a hidden cursor.

_____________
Code for TallyGenerator