Help Required with optimising Stored Proc
-
Hi All.
Can some one help me improve the following stored proc (SQL Server 2000). Currently it accessed the database three time. Ideally I would like to do it in one.
This should hopefully make it faster to run..right ?
The tblStaff holds the the staff id and the id of the last viewed tip. The tblTipholds the tip id and the tip description.
Thanks. CCB
CREATE PROCEDURE [dbo].[prc001GetTipOfTheDay]
(@intStaffID int)
AS
Declare @MaxTip int
Declare @intLastUsed int
Set @MaxTip = (Select Max(intTipID) from tblTips)
Set @intLastUsed = (Select IsNull(intLastTipID, 0) from tblStaff Where intStaffID = @intStaffID)
If @intLastUsed >= @MaxTip
Begin
Set @intLastUsed = 0
End
Select Top 1 intTipID,
strTipDesc
From tblTips
Where intTipID > @intLastUsed
Order by intTipID
GO
-
I'm not sure if this will work, but what about this:
--edited to add an inner join to get the staff id's last tip
CREATE PROCEDURE [dbo].[prc001GetTipOfTheDay]
(@intStaffID int)
AS
Select Top 1 tblTips.intTipID,tblTips.strTipDesc
From tblTips
inner join tblStaff
on tblTips.intTipId=tblStaff.intLastTipID
Where tblTips.intTipID > isnull((Select IsNull(intLastTipID, 0) from tblStaff Where intStaffID = @intStaffID),0)
Order by tblTips.intTipID desc
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
CREATE PROCEDURE [dbo].[prc001GetTipOfTheDay]
(@intStaffID int)
AS
Select Top 1 intTipID,strTipDesc
From tblTips
Where intTipID > isnull((Select IsNull(intLastTipID, 0) % (Select Max(intTip) from tblTips) from tblStaff Where intStaffID = @intStaffID),0)
Order by intTipID
* Noel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply