December 6, 2011 at 5:48 am
Hi – I am using a Stored Procedure from where our SSRS report runs but now our report takes really huge time and we had to work on tuning of SPROC.
We are actually using CURSOR in our SPROC because we had to pull the calculated time fields
DECLARE RM_CURSOR CURSOR FOR
SELECT D.id,D.PeriodDesc,D.StartDate,D.EndDate
FROM #DateRanges D
ORDER BY D.id
OPEN RM_CURSOR
FETCH NEXT FROM RM_CURSOR INTO @id,@PeriodDesc,@StartDate,@EndDate
WHILE @@FETCH_STATUS = 0
BEGIN
**
**
FETCH NEXT FROM RM_CURSOR INTO @id,@PeriodDesc,@StartDate,@EndDate
END
CLOSE RM_CURSOR
DEALLOCATE RM_CURSOR
I read in most of the blogs that CURSOR will degrade the performance, and WHILE LOOP can be replaced instead of CURSORS.
Can someone please help me how can I use WHILE LOOP in above scenario?
Thanks !!
December 6, 2011 at 5:54 am
CURSORS and WHILE LOOPS are both generally bad, if you're tuning your query then maybe you should look at a more set-based approach?
If you provide readily consumable sample data, DDL scripts, the current code in your sproc and your expected result-set based on your sample data then I'm sure there will be a way to make your code perform much much better.
Please read this article[/url] about the best way to provide us with readily consumable sample data and DDL scripts, which will allow the unpaid volunteers of this site to provide you with working, tested code that fulfils your requirements.
Thanks.
December 6, 2011 at 5:57 am
DECLARE RM_CURSOR CURSOR FOR
SELECT D.id,D.PeriodDesc,D.StartDate,D.EndDate
FROM #DateRanges D
ORDER BY D.id
OPEN RM_CURSOR
FETCH NEXT FROM RM_CURSOR INTO @id,@PeriodDesc,@StartDate,@EndDate
WHILE @@FETCH_STATUS = 0
BEGIN
Your looping logic is based on Cursor fetch status. Declare a CounterVariable & get the number of the rows for your cursor query. Compare this CounterVariable in your loop & your are good to remove cursor.
December 6, 2011 at 5:58 am
Yes – my motto is to improve the performance of my Stored Procedure. Yes – I can provide you the required information, would you mind to provide me your personal email ID as I don’t want to share the script in the thread due to some security issues.
Hope you don’t mind.
Thanks !!
December 6, 2011 at 6:03 am
MSBI Learner (12/6/2011)
Yes – my motto is to improve the performance of my Stored Procedure. Yes – I can provide you the required information, would you mind to provide me your personal email ID as I don’t want to share the script in the thread due to some security issues.Hope you don’t mind.
Thanks !!
Sorry, no. Just obfuscate the data, so that it's similar to what you're using but not the same thus not violating any data protection laws.
For example, I use variations of the below script when I'm testing for performance
--Standard TestEnvironment of 1,000,000 rows of random-ish data
IF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN
DROP TABLE #testEnvironment
END
--1,000,000 Random rows of data
SELECT TOP 1000000 IDENTITY(INT,1,1) AS ID,
RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME) AS randomDate,
ABS(CHECKSUM(NEWID())) AS randomBigInt,
(ABS(CHECKSUM(NEWID())) % 100) + 1 AS randomSmallInt,
RAND(CHECKSUM(NEWID())) * 100 AS randomSmallDec,
RAND(CHECKSUM(NEWID())) AS randomTinyDec,
RAND(CHECKSUM(NEWID())) * 100000 AS randomBigDec,
CONVERT(VARCHAR(6),CONVERT(MONEY,RAND(CHECKSUM(NEWID())) * 100),0) AS randomMoney
INTO #testEnvironment
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3
It creates 1,000,000 rows of pseudo-random data of different data-types.
Can you do something similar for the set-up of your issue? I know it means taking some time out to provide the scripts required, but I promise that there are some extremely talented people that frequent these forums so you will end up with a much more efficient query.
December 6, 2011 at 6:03 am
Thank you Dev, could you please help me on how do I implement the same?
December 6, 2011 at 6:08 am
MSBI Learner (12/6/2011)
Thank you Dev, could you please help me on how do I implement the same?
If help means giving you a running piece of code then I can't help you. Please try to implement what I have already suggested to you. It's not very difficult so don't miss the opportunity to learn it.
December 6, 2011 at 6:10 am
🙂 will do then..
December 6, 2011 at 6:13 am
I'm going to re-iterate what I said before. . .
CURSORS and WHILE LOOPS are both generally bad, if you're tuning your query then maybe you should look at a more set-based approach?
If you use either, the chances are fairly good that you are not using the most efficient algorithm to perform your task.
If you provide readily consumable obfuscated sample data, DDL scripts, the current code in your sproc and your expected result-set based on your sample data then I'm sure there will be a way to make your code perform much much better. In fact, if the current code in your sproc is "secret", just provide the expected result based on your obfuscated sample data.
Please read this article[/url] about the best way to provide us with readily consumable sample data and DDL scripts, which will allow the unpaid volunteers of this site to provide you with working, tested code that fulfils your requirements.
December 6, 2011 at 6:35 am
Thank you, I have sent the script of my SPROC.
December 6, 2011 at 6:42 am
MSBI Learner (12/6/2011)
Thank you, I have sent the script of my SPROC.
One more try 😉
Sorry, but without DDL, sample data and expected results there isn't much I can do.
If the sproc is "secret", don't post it, but you need to knock up obfuscated (pretend) sample data and expected results otherwise there's not really any way to help with your issue.
At the very least, you should replace your looping with a tally table, see here for details[/url].
December 6, 2011 at 7:11 am
Yeah, I have sent... hope that helps you to work on my issue.
Please let me know if you need any more information on the same.
December 6, 2011 at 7:19 am
MSBI Learner (12/6/2011)
Yeah, I have sent... hope that helps you to work on my issue.Please let me know if you need any more information on the same.
As I've said repeatedly, please post the information in the thread. Don't send them to me, it limits the responses that you'll get and makes it more difficult for others that are facing similar problems to figure out how to replicate the solution in their environment.
I've posted a link to this article a couple of times now, but here it is again. It shows you how best to write out your sample data so that it is readily consumable which helps the unpaid volunteers of this website to provide you with working, tested code that fulfils your requirements --> http://www.sqlservercentral.com/articles/Best+Practices/61537/%5B/url%5D.
Feel free to obfuscate your data in any way you wish to keep it secure, but make sure you also include your expected result based on the sample data you provide.
December 6, 2011 at 9:12 am
MSBI Learner (12/6/2011)
Yeah, I have sent... hope that helps you to work on my issue.Please let me know if you need any more information on the same.
There are others out here who would be willing to help you if you posted the requested information on the thread. If you are concerned about security, then obfusicate the data, change column names so that they don't match your live system (keep a map of the changes so you know what needs to be changed back), and modify your code to match the new column names.
We are volunteers, not paid help, on this site. Please help us help you.
December 6, 2011 at 9:14 am
Lynn Pettis (12/6/2011)
MSBI Learner (12/6/2011)
Yeah, I have sent... hope that helps you to work on my issue.Please let me know if you need any more information on the same.
There are others out here who would be willing to help you if you posted the requested information on the thread. If you are concerned about security, then obfusicate the data, change column names so that they don't match your live system (keep a map of the changes so you know what needs to be changed back), and modify your code to match the new column names.
We are volunteers, not paid help, on this site. Please help us help you.
+1
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply