May 1, 2012 at 8:43 am
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
May 1, 2012 at 8:58 am
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/
May 1, 2012 at 9:29 am
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 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]
May 1, 2012 at 9:37 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy