better method of running the procedure below:

[kizziebutler]

I was wondering if the procedure below could be changed to improve the performance.

CREATE PROCEDURE spGetMailQueue (@CurrentDate datetime, @HOD varchar(3))

AS

CREATE TABLE #PrintQueue (QueueNo int primary key, PCDID int, Stationary varchar(50))
INSERT INTO #PrintQueue
SELECT tblCheque.ChqSerialNo as QueueNo, tblControl.PCDID, tblSystem.Stationary
FROM tblCheque, tblSystem, tblControl
WHERE tblCheque.PCDID = tblControl.PCDID
AND DatePart(d,SessionDate) = DatePart(d, @CurrentDate)
AND DatePart(m,SessionDate) = DatePart(m, @CurrentDate)
AND DatePart(yy,SessionDate) = DatePart(yy, @CurrentDate)
AND tblSystem.SystemID = tblControl.SystemID
AND tblSystem.HOD = @HOD



SELECT tblCheque.*, tblQueuePouch.PouchNo, tblControl.UserID,#PrintQueue.Stationary,tblControl.PCDID_Value
FROM tblCheque, tblQueuePouch, tblQueueMail, #PrintQueue, tblControl
WHERE tblQueuePouch.PCDID = #PrintQueue.PCDID
AND #PrintQueue.QueueNo >= tblQueueMail.RangeFrom
AND #PrintQueue.QueueNo <= tblQueueMail.RangeTo
AND tblQueueMail.QueuePouchID = tblQueuePouch.QueuePouchID
AND tblControl.PCDID = #PrintQueue.PCDID
AND tblCheque.ChqSerialNo = #PrintQueue.QueueNo

DROP TABLE #PrintQueue
GO

 

[vongrunt]

DATEPART() conditions cause table/index scans. Does @CurrentDate have only date or date+time?

And insert into temp table is very likely not necessary...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

one thing
instead of
AND DatePart(d,SessionDate) = DatePart(d, @CurrentDate)
AND DatePart(m,SessionDate) = DatePart(m, @CurrentDate)
AND DatePart(yy,SessionDate) = DatePart(yy, @CurrentDate)

do
AND SessionDate >= @CurrentDate
AND SessionDate < @CurrentDate +1

asumming date without time

declare @CurrentDate datetime
select @CurrentDate = '20060120'
select @CurrentDate,@CurrentDate +1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

you might want to change to ANSI joins since it will be more readable, and like vongrunt said you probably can eliminate the temp table

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[kizziebutler]

Thank you for your help, yes it is only the date (one date) that needs to run.

 

[kizziebutler]

Hello could you clarify on the "ANSI joins" since it will be more readable, and like vongrunt said you probably can eliminate the temp table.

I have tried to rewrite this but the temporary table seems to give it a better peformance I cannot get it to perform slightly better.

Any suggestions

 

[vongrunt]

This is first part with ANSI joins:

INSERT INTO #PrintQueue
SELECT tblCheque.ChqSerialNo as QueueNo, tblControl.PCDID, tblSystem.Stationary
FROM tblCheque
INNER JOIN tblControl ON tblCheque.PCDID = tblControl.PCDID
INNER JOIN tblSystem ON tblControl.SystemID = tblSystem.SystemID
WHERE tblSystem.HOD = @HOD
	AND SessionDate >= @CurrentDate AND SessionDate < @CurrentDate + 1

That way you see what serves for JOINing tables and what for filtering (WHERE)... and also change join types with ease - when necessary.

Try this first - I guess SARGable condition on SessionDate can make some difference.


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

ANSI
------------------------------------------------------------

INSERT INTO #PrintQueue 
SELECT tblCheque.ChqSerialNo as QueueNo, tblControl.PCDID, tblSystem.Stationary
FROM tblCheque t
JOIN tblControl c ON t.PCDID = c.PCDID
JOIN tblSystem s ON s.SystemID = c.SystemID 
WHERE DatePart(d,SessionDate) = DatePart(d, @CurrentDate) 
AND DatePart(m,SessionDate) = DatePart(m, @CurrentDate) 
AND DatePart(yy,SessionDate) = DatePart(yy, @CurrentDate)
AND tblSystem.HOD = @HOD

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/