Please convert cursor based query to a set based query

  • Hi Gurus,

    Have been always scared of cursors...and here I am trying to convert a cursor based query to a set based query. Just wanted advice of experts on the exact syntax to be used in set based query. Please note that the cursor query takes around 110 secs for execution. The query is as follows:

    DECLARE @linkid int

    DECLARE @name varchar(50)

    DECLARE @num_of_comms int

    DECLARE @percent_confirmed varchar(10)

    DECLARE @avg_quizscore as money

    DECLARE @quizattempts as money

    DECLARE @avg_days as money

    DECLARE @stdev as money

    DECLARE @report table (groupid int, groups varchar(50), num_of_comms int, percent_confirmed varchar(10), avg_quizscore numeric(10,0), quizattempts numeric(10,1), avg_days numeric(10,1), stdev numeric(10,2));

    BEGIN

    DECLARE Links Cursor FOR SELECT LinkID, Name FROM CategoryValues WHERE CatID = @catid AND Retired = 0

    OPEN Links;

    FETCH NEXT FROM Links INTO @linkid, @name;

    WHILE @@FETCH_STATUS = 0

    BEGIN

    select @num_of_comms = isnull(count(distinct ccl.commsid),0), @percent_confirmed = case

    when isnull((count(DISTINCT c.subject + Convert(varchar(4),u.userid))),0) = 0 then '0%'

    else (cast(convert(numeric(10,0),((cast(count(distinct cr.id) as money)*100) / cast(count(DISTINCT c.subject + Convert(varchar(4),u.userid)) as money))) as varchar(10)) + '%')

    end,

    @avg_quizscore = isnull(convert(numeric(10,1),(select avg(quizscore) from commsreceipts cr join usercategorylink ucl on cr.userid = ucl.userid join communications c on c.id = cr.commsid where ucl.linkid = @linkid and cr.mandatory=1 and convert(datetime,c.datepublished,103) >= @startdate and convert(datetime,c.datepublished,103) <= @enddate)),0),

    @quizattempts = isnull(convert(numeric(10,1),avg(cr.quizattempts)),0),

    @avg_days = isnull(convert(numeric(10,1),(avg(datediff(ss,c.datepublished, cr.daterecorded)) / 86400.0)),0),

    @stdev = isnull(convert(numeric(10,2),(stdev(datediff(ss,c.datepublished, cr.daterecorded)) / 86400.0)),0)

    from usercategorylink ucl

    join commscategorylink ccl on ccl.linkid = ucl.linkid or ccl.linkid = 0

    join communications c on c.id = ccl.commsid and c.retire = 0 and c.datepublished is not null and c.confirmationType != 'N'

    join users u on u.userid = ucl.userid and (u.enddate is null)

    full outer join commsreceipts cr on cr.userid = u.userid and cr.commsid = c.id and cr.daterecorded is not null

    INSERT INTO @report VALUES (@linkid, @name, @num_of_comms, @percent_confirmed, @avg_quizscore, @quizattempts, @avg_days, @stdev)

    set @num_of_comms = 0

    set @percent_confirmed = '0%'

    set @avg_quizscore = 0.0

    set @quizattempts = 0.0

    set @avg_days = 0.0

    set @stdev = 0.00

    FETCH NEXT FROM Links INTO @linkid, @name;

    END

    CLOSE Links;

    DEALLOCATE Links;

    SELECT * FROM @report

    END

    Thanks,

    Learner

  • Aside from the subselect this is pretty straight forward. Just change this into a single select statement. I can't quite wrap my head around the whole thing. You need to change the subquery to another join to commsreceipts. You will also need a join to CategoryValues but I can't visualize what the join criteria is for either of them.

    If you can post some ddl, sample data and desired output I will be happy to help with this. Take a look at the first link in my signature.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • looking through the code it looks like you select LinkID and name then run them through individually getting one result for each LinkID and Name. you can deffinitly rewrite the query getting rid of all the variables which may speed things up a little. but it looks like we can rewrite the query with out the cursor pretty easily if we have some sample data to chew on.

    EDIT Im a little blind this morning, saw sean posted almost the same thing. hate waking up for work early.


    For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

    For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

    Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
    Jeff Moden's Cross tab and Pivots Part 1[/url]
    Jeff Moden's Cross tab and Pivots Part 2[/url]

  • Taking a shot in the dark...

    select

    num_of_comms = isnull(count(distinct ccl.commsid),0),

    percent_confirmed = case

    when isnull((count(DISTINCT c.subject + Convert(varchar(4),u.userid))),0) = 0 then '0%'

    else (cast(convert(numeric(10,0),((cast(count(distinct cr.id) as money)*100) / cast(count(DISTINCT c.subject + Convert(varchar(4),u.userid)) as money))) as varchar(10)) + '%')

    end,

    avg_quizscore = isnull(convert(numeric(10,1), avg(quizscore)), 0) ,

    quizattempts = isnull(convert(numeric(10,1),avg(cr.quizattempts)),0),

    avg_days = isnull(convert(numeric(10,1),(avg(datediff(ss,c.datepublished, cr.daterecorded)) / 86400.0)),0),

    stdev = isnull(convert(numeric(10,2),(stdev(datediff(ss,c.datepublished, cr.daterecorded)) / 86400.0)),0)

    from

    CategoryValues cv

    join usercategorylink ucl on cv.LinkID = ucl.linkid

    join commscategorylink ccl on ccl.linkid = ucl.linkid or ccl.linkid = 0

    join communications c on c.id = ccl.commsid and c.retire = 0 and c.datepublished is not null and c.confirmationType != 'N'

    join users u on u.userid = ucl.userid and (u.enddate is null)

    full outer join commsreceipts cr on cr.userid = u.userid and cr.commsid = c.id and cr.daterecorded is not null

    where cr.mandatory = 1 and Retired = 0

    ...is this close?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

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