Just wrote the WORST TSQL ever! Help!

• nitrox

  SSC Veteran

  Points: 270

  More actions

  June 5, 2002 at 2:13 pm

  #78579

  Hey all-

  been asked to do something, and am having problems. First, my sql takes forever to execute, so I am looking for suggestions on how to speed things up a bit. I really would like to keep it all in T-SQL and and run it as a job once a day. It sends out email notices when people should be contacted. Any help would be great.

  Also I am having problems with a process on my server (SQL). It is almost like I wrote a hunge loop or something, but anyways, I have not been able to "Kill process". It just keeps going.

  *******

  DECLARE @U_ID INT,

  @U_FNAME VARCHAR(20),

  @U_LNAME VARCHAR(20),

  @U_EMAIL VARCHAR(75),

  @S_ID INT,

  @S_DUEDATE DATETIME,

  @S_LASTDATE DATETIME,

  @S_SPAN VARCHAR(15),

  @S_SENTCOUNT INT,

  @C_ID INT,

  @C_PREFIX VARCHAR(30),

  @C_FNAME VARCHAR(30),

  @C_LNAME VARCHAR(30),

  @C_PHONE VARCHAR(20),

  @C_EMAIL VARCHAR(75)

  --NOW DECLARE LOCAL VARIABLES

  DECLARE @URL VARCHAR(100),

  @MSG VARCHAR(250),

  @TITLE VARCHAR(100)

  DECLARE SCHEDULECURSOR CURSOR FOR

  SELECT GD_USER.U_ID, GD_USER.U_FNAME, GD_USER.U_LNAME, GD_USER.U_EMAIL, SCHEDULE.S_ID, SCHEDULE.S_DUEDATE,

  SCHEDULE.S_LASTDATE, SCHEDULE.S_SPAN, SCHEDULE.S_SENTCOUNT, CUSTOMER.C_ID, CUSTOMER.C_PREFIX,

  CUSTOMER.C_FNAME, CUSTOMER.C_LNAME, CUSTOMER.C_PHONE, CUSTOMER.C_EMAIL

  FROM SCHEDULE

  INNER JOIN GD_USER ON SCHEDULE.U_ID = GD_USER.U_ID

  INNER JOIN CUSTOMER ON SCHEDULE.C_ID = CUSTOMER.C_ID

  WHERE GD_USER.U_ID <> 2

  FOR UPDATE

  --OPEN THE CURSOR

  OPEN SCHEDULECURSOR

  --OPEN MAIL SESSION TO BEGIN THE EMAIL PROCESS

  EXEC MASTER.DBO.XP_STARTMAIL

  WHILE (0=0) BEGIN

  FETCH NEXT FROM SCHEDULECURSOR

  INTO

  @U_ID,

  @U_FNAME,

  @U_LNAME,

  @U_EMAIL,

  @S_ID,

  @S_LASTDATE,

  @S_DUEDATE,

  @S_SPAN,

  @S_SENTCOUNT,

  @C_ID,

  @C_PREFIX,

  @C_FNAME,

  @C_LNAME,

  @C_PHONE,

  @C_EMAIL

  --BREAK LOOP IF END OF RS

  IF ( @@FETCH_STATUS <> 0 ) BREAK

  --GET THE URL AND THE MESSAGE READY TO GO

  SELECT @URL = 'http://marion-web/dept_marketing/contacts/cust_profile.asp?customer=' + CAST(@C_ID AS VARCHAR(10))

  SELECT @TITLE = 'Need to contact ' + @C_FNAME + ' ' + @C_LNAME

  SELECT @MSG = @U_FNAME + ',' + CHAR(13) + @C_FNAME + ' ' + @C_LNAME + ' needs to be contacted within the next few days. Please take a few moments to review their file on the contact system. ' + CHAR(13) + @URL

  --SEND EMAIL

  EXEC MASTER.DBO.XP_SENDMAIL

  @RECIPIENTS = @U_EMAIL,

  @MESSAGE = @MSG,

  @SUBJECT = @TITLE

  PRINT CAST(@S_DUEDATE AS VARCHAR(50)) + ' ' + @S_SPAN

  --UPDATE RECORD

  END

  EXEC MASTER.DBO.XP_STOPMAIL

  CLOSE SCHEDULECURSOR

  DEALLOCATE SCHEDULECURSOR

• danw

  SSCommitted

  Points: 1842

  More actions

  June 5, 2002 at 3:27 pm

  #430395

  Here is a code revision for you, but it sounds like you might have some mapi issues too. You might want to try starting mail and sending an email first to make sure mapi isn't all hosed up. Once you have done that leave mail running instead of starting and stopping each time you run this code.

  DECLARE @U_ID INT,

  @U_FNAME VARCHAR(20),

  @U_LNAME VARCHAR(20),

  @U_EMAIL VARCHAR(75),

  @S_ID INT,

  @S_DUEDATE DATETIME,

  @S_LASTDATE DATETIME,

  @S_SPAN VARCHAR(15),

  @S_SENTCOUNT INT,

  @C_ID INT,

  @C_PREFIX VARCHAR(30),

  @C_FNAME VARCHAR(30),

  @C_LNAME VARCHAR(30),

  @C_PHONE VARCHAR(20),

  @C_EMAIL VARCHAR(75)

  --NOW DECLARE LOCAL VARIABLES

  DECLARE @URL VARCHAR(100),

  @MSG VARCHAR(250),

  @TITLE VARCHAR(100)

  -- Changed to a fast forward cursor for speed increase

  DECLARE SCHEDULECURSOR CURSOR fast_forward FOR

  SELECT GD_USER.U_ID, GD_USER.U_FNAME, GD_USER.U_LNAME, GD_USER.U_EMAIL, SCHEDULE.S_ID, SCHEDULE.S_DUEDATE,

  SCHEDULE.S_LASTDATE, SCHEDULE.S_SPAN, SCHEDULE.S_SENTCOUNT, CUSTOMER.C_ID, CUSTOMER.C_PREFIX,

  CUSTOMER.C_FNAME, CUSTOMER.C_LNAME, CUSTOMER.C_PHONE, CUSTOMER.C_EMAIL

  FROM SCHEDULE

  INNER JOIN GD_USER ON SCHEDULE.U_ID = GD_USER.U_ID

  INNER JOIN CUSTOMER ON SCHEDULE.C_ID = CUSTOMER.C_ID

  WHERE GD_USER.U_ID <> 2

  --FOR UPDATE -- It doesn't appear that you are doing any updates in your cursor, this will just slow things down.

  --OPEN THE CURSOR

  OPEN SCHEDULECURSOR

  --OPEN MAIL SESSION TO BEGIN THE EMAIL PROCESS

  -- Do not start and stop you mail service just leave it running

  --EXEC MASTER.DBO.XP_STARTMAIL

  FETCH NEXT FROM SCHEDULECURSOR

  INTO @U_ID, @U_FNAME, @U_LNAME, @U_EMAIL, @S_ID, @S_LASTDATE, @S_DUEDATE, @S_SPAN, @S_SENTCOUNT, @C_ID, @C_PREFIX, @C_FNAME, @C_LNAME, @C_PHONE, @C_EMAIL

  --BREAK LOOP IF END OF RS

  -- No Need to break to loop it will break itself

  --IF ( @@FETCH_STATUS <> 0 ) BREAK

  WHILE @@FETCH_STATUS = 0

  BEGIN

  --GET THE URL AND THE MESSAGE READY TO GO

  SELECT @URL = 'http://marion-web/dept_marketing/contacts/cust_profile.asp?customer=' + CAST(@C_ID AS VARCHAR(10))

  SELECT @TITLE = 'Need to contact ' + @C_FNAME + ' ' + @C_LNAME

  SELECT @MSG = @U_FNAME + ',' + CHAR(13) + @C_FNAME + ' ' + @C_LNAME + ' needs to be contacted within the next few days. Please take a few moments to review their file on the contact system. ' + CHAR(13) + @URL

  --SEND EMAIL

  EXEC MASTER.DBO.XP_SENDMAIL

  @RECIPIENTS = @U_EMAIL,

  @MESSAGE = @MSG,

  @SUBJECT = @TITLE

  PRINT CAST(@S_DUEDATE AS VARCHAR(50)) + ' ' + @S_SPAN

  --UPDATE RECORD

  FETCH NEXT FROM SCHEDULECURSOR

  INTO @U_ID,@U_FNAME,@U_LNAME,@U_EMAIL,@S_ID,@S_LASTDATE,@S_DUEDATE,@S_SPAN,@S_SENTCOUNT,@C_ID,@C_PREFIX,@C_FNAME,@C_LNAME,@C_PHONE,@C_EMAIL

  END

  --EXEC MASTER.DBO.XP_STOPMAIL

  CLOSE SCHEDULECURSOR

  DEALLOCATE SCHEDULECURSOR