May 21, 2008 at 12:41 pm
Hi, Should I put an Clustered Index on a temp table if all I am ever doing with it is scrolling through it with a cursor? I am creating the table with four columns for example, and using all four in my cursor. I am not going back to update, delete, etc in the temp table.
May 21, 2008 at 12:48 pm
Not to sound abrasive, but why not write it without using a cursor?
Post your code and sample data and maybe we can give you a set-based NON CURSOR solution. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 21, 2008 at 1:07 pm
Since all you are doing is running a cursor over the temp table - I cannot see how having any indexes on the temp table would help.
I would be more concerned with the requirements to use a temp table and a cursor.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 21, 2008 at 1:19 pm
Well, my proc is currently 3422 lines long (no joke) so I don't think I can just post a chunk of it and have it make sense.
Essentially I am sending out emails to a variety of people. The emails are being built dynamically based on who the recipient is, what it will contain, and what the conditions are. So I have a master table containing all who I have to contact in addition to the information that needs to be sent to them. Each recipient might have multiple rows of data that needs to be sent to them.
I am using a cursor to find the next recipient who I haven't built an email for, loading all of his records into a temp table, creating a second cursor to go through each of those records to build an unordered list in html, and once done, updating the records used in my master table that they have been used, truncating the temp table, and then fetching the next recipient that I haven't created an email for, and repeat the process. The end result is an email in html with a list of users and software that needs to be recertified, and in a specific format.
I am skipping through a lot, and I do have cursors all over the place depending on what is going on, and while most people seem to argue against using them, my proc so far is extremely quick considering all that I am doing. There are a lot of IFs in this, so not every record goes through all of the code.
But I did notice during an execution plan that as I was pulling a record out of a table for a cursor, it was doing a table scan. I didn't think I should have a index on the temp table since I was never joining or updating anything in it, and having an index can actually slow the insert of data into is since it might have to be sorted.
May 21, 2008 at 1:27 pm
From the sound of things, the inner cursor may be removable. Can you post just that bit please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 21, 2008 at 1:38 pm
Ah - I see, this is one of those cases where I would have to agree that the use of a cursor is warranted. The only way to avoid using a cursor for this would be to use another application to generate the emails.
So, with that said - I would not worry too much about adding an index to the temp table unless you are loading a lot of data into the temp table and later filtering the results. It does not sound like you are doing that.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 21, 2008 at 1:43 pm
I wouldn't say that a cursor is the best way, just that it would be a huge amount of work to re-write through a site like this. Not to mention a lot of work for those of us doing this pro-bono.
I would still think that a re-write without cursors would be more efficient, but if it isn't a huge concern for you right now, then stick with what you have. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 21, 2008 at 1:45 pm
GilaMonster (5/21/2008)
From the sound of things, the inner cursor may be removable. Can you post just that bit please?
Sure, but I think the poster above is correct. This is an example of one part where I do this:
IF @EMAIL_STATUS_CDForGROUPINGCursor = 59 --RECERTIFICATION START NOTIFICATION
--NOTE! We use REVIEWER_UI from SOFTWARE_USER rather than LM. . .
BEGIN
SET @SOFTWARE_LIST = ''
--Build Software list. . .
--Admins want AppQuestID (or Software ID), Software_NM, Days till Escalation
DECLARE SoftwareListCursorR59 CURSOR FOR
SELECT DISTINCT
REMINDER_DAYS,
SOFTWARE_ID,
APPQUEST_ID,
SOFTWARE_NM
FROM #tempGroup
ORDER BY APPQUEST_ID
OPEN SoftwareListCursorR59
FETCH NEXT FROM SoftwareListCursorR59
INTO @REMINDER_DAYSForSoftwareListCursor,
@SOFTWARE_IDForSoftwareListCursor,
@APPQUEST_IDForSoftwareListCursor,
@SOFTWARE_NMForSoftwareListCursor
WHILE @@FETCH_STATUS = 0
BEGIN
--Find the number of days before ESCALATION
DECLARE @LINE_MGR_RECERT_REMINDER1_ForSoftwareListCursor int
SELECT @LINE_MGR_RECERT_REMINDER1_ForSoftwareListCursor = LINE_MGR_RECERT_REMINDER1_DS
FROM SOFTWARE_NOTIF_CONFIG
WHERE SOFTWARE_ID = @SOFTWARE_IDForSoftwareListCursor
SET @DaysTillEscalation = @LINE_MGR_RECERT_REMINDER1_ForSoftwareListCursor - @REMINDER_DAYSForSoftwareListCursor
--Sofware list
IF LEN(@SOFTWARE_LIST) < 5000 --Just to be sure so whole process doesn't blow up
BEGIN
SET @SOFTWARE_LIST = @SOFTWARE_LIST + ' ' +
@SOFTWARE_NMForSoftwareListCursor + ' (AppQuestID - ' +
@APPQUEST_IDForSoftwareListCursor + ') - ' +
CONVERT(varchar(1000),@DaysTillEscalation) + ' day(s) till ESCALATION '
END
FETCH NEXT FROM SoftwareListCursorR59
INTO @REMINDER_DAYSForSoftwareListCursor,
@SOFTWARE_IDForSoftwareListCursor,
@APPQUEST_IDForSoftwareListCursor,
@SOFTWARE_NMForSoftwareListCursor
END
CLOSE SoftwareListCursorR59
DEALLOCATE SoftwareListCursorR59
--AFTER THE LIST IS BUILT, CONSTRUCT THE EMAIL
--Find who is getting the email, in this case the REVIEWER_UI
--**Will need to get his email by getting his ID from USER
DECLARE @REVIEWER_UI_EMAIL_R59 varchar(100)
DECLARE @REVIEWER_FIRST_NM_R59 varchar(50)
DECLARE @REVIEWER_LAST_NM_R59 varchar(50)
DECLARE @REVIEWER_COMPLETE_NM_59 varchar(100)
--First assign the Reviewer's name and email address
SELECT @REVIEWER_UI_EMAIL_R59 = INTERNAL_EMAIL_AD,
@REVIEWER_FIRST_NM_R59 = RTRIM(USER_FIRST_NM),
@REVIEWER_LAST_NM_R59 = RTRIM(USER_LAST_NM)
FROM USER
WHERE STANDARD_UI = @REVIEWER_UIForGROUPINGCursor
--Set the Reviewer's complete name
SET @REVIEWER_COMPLETE_NM_59 = @REVIEWER_FIRST_NM_R59 + ' ' + @REVIEWER_LAST_NM_R59
--Subject and message text
SELECT @MESSAGE_NM = MESSAGE_NM, @MESSAGE_TX = MESSAGE_TX FROM [MESSAGE]
WHERE MESSAGE_ID = 12
--Replace 'Colleague' with the Reviewer's name
SET @MESSAGE_TX = REPLACE(@MESSAGE_TX, 'Colleague', @REVIEWER_COMPLETE_NM_59)
--**Customize the Email template here
SET @SOFTWARE_LIST = ' '
--Update the template message
SET @MESSAGE_TX = REPLACE(@MESSAGE_TX, '***DYNAMIC CONTENT***', @SOFTWARE_LIST)
--Insert the values into #Emails as a list to be sent
INSERT INTO #Emails (
EMAIL_REVIEWER_UI,
EMAIL_TO,
EMAIL_CC,
EMAIL_SUBJECT,
EMAIL_COMPLETE_EMAIL,
GROUP_NO,
COMPLETE,
SENT)
VALUES (
@REVIEWER_UIForGROUPINGCursor,
@REVIEWER_UI_EMAIL_R59 ,
'',
@MESSAGE_NM,
@MESSAGE_TX,
@GROUP_NO,
1,
0)
END
#tempGroup is the table I was wondering if I needed a cursor for, but I don't think it would help.
May 21, 2008 at 2:00 pm
You may be able to remove the inner cursor. The outer one, no.
WHILE @@FETCH_STATUS = 0
BEGIN
--Find the number of days before ESCALATION
DECLARE @LINE_MGR_RECERT_REMINDER1_ForSoftwareListCursor int
SELECT @LINE_MGR_RECERT_REMINDER1_ForSoftwareListCursor = LINE_MGR_RECERT_REMINDER1_DS
FROM SOFTWARE_NOTIF_CONFIG WITH (NOLOCK)
WHERE SOFTWARE_ID = @SOFTWARE_IDForSoftwareListCursor
SET @DaysTillEscalation = @LINE_MGR_RECERT_REMINDER1_ForSoftwareListCursor - @REMINDER_DAYSForSoftwareListCursor
--Sofware list
IF LEN(@SOFTWARE_LIST) < 5000 --Just to be sure so whole process doesn't blow up
BEGIN
SET @SOFTWARE_LIST = @SOFTWARE_LIST + ' ' +
@SOFTWARE_NMForSoftwareListCursor + ' (AppQuestID - ' +
@APPQUEST_IDForSoftwareListCursor + ') - ' +
CONVERT(varchar(1000),@DaysTillEscalation) + ' day(s) till ESCALATION '
END
FETCH NEXT FROM SoftwareListCursorR59
INTO @REMINDER_DAYSForSoftwareListCursor,
@SOFTWARE_IDForSoftwareListCursor,
@APPQUEST_IDForSoftwareListCursor,
@SOFTWARE_NMForSoftwareListCursor
END
I'm not going to rewrite the entire thing, but in SQL you can build up strings within a select statement, as follows:
DECLARE @SoftwareList VARCHAR(8000)
SET @SoftwareList = ''
SELECT @SoftwareList = @SoftwareList + SOFTWARE_NM + ' '
FROM #tempGroup
ORDER BY APPQUEST_ID
SELECT @SoftwareList
Maybe that helps a little.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply