November 15, 2006 at 9:07 am
I have a stored procedure that looks like this:
CREATE Proc dbo.DailyInvProc
@StartDatedatetime,
@EndDatedatetime
AS
Begin
SELECT * FROM DailyInvFunc(@StartDate, @EndDate)
End
Here is the DailyInvFunc:
CREATE FUNCTION dbo.DailyInvFunc(
@StartDatedatetime,
@EndDatedatetime
)
RETURNS @TempPlayCmb TABLE
(
PaytableNamevarchar(60) DEFAULT '',
SkinNamevarchar(60) DEFAULT '',
Denominationint default 0,
EPSNameint default 0,
Creditsint default 0,
Bet1int default 0,
Configurationvarchar(60) default '',
MacCountint default 0,
ChangeDatedatetime default '1Jan1971 0:0:0',
RemovalDatedatetime default '31Dec2100 11:59:59 pm',
TheoreticalHoldintdefault 0,
ZoneNamevarchar(21) default ''
)
AS
BEGIN
DECLARE @TempPlayCfg TABLE
(
PaytableNamevarchar(60) DEFAULT '',
SkinNamevarchar(60) DEFAULT '',
Denominationint default 0,
EPSNameint default 0,
Creditsint default 0,
Bet1int default 0,
Configurationvarchar(60) default '',
MacCountint default 0,
ChangeDatedatetime default '1Jan1971 0:0:0',
ZoneNamevarchar(21) default '',
--TheoreticalHoldintdefault 0,
RemovalDatedatetime default '31Dec2100 11:59:59 pm'
)
DECLARE @RecCnt int
DECLARE @test-2 int
SET @test-2 = 0
SELECT @RecCnt=count(*) FROM Bingo.dbo.Play p
WHERE ((p.TestMode=@Test) And (p.CurrentDate >= @StartDate) And (p.CurrentDate <= @EndDate)) IF (@RecCnt > 0)
BEGIN
INSERT into @TempPlayCfg (p.PaytableName, p.SkinName, p.Denomination, EPSName,
Credits, Bet1, Configuration, MacCount, ChangeDate,
ZoneName, RemovalDate/*, TheoreticalHold*/)
SELECT RTrim(p.PaytableName), RTrim(p.SkinName), p.Denomination, CAST (p.EPSName AS INTEGER) AS EPSName,
Sum(p.TotalWin) As Credits, Sum(p.TotalBet) As Bet1, '',
0,
MAX(p.CurrentDate) As ChangeDate, e.ZoneName, '2Jan1971'
FROM Bingo.dbo.Play p INNER JOIN Bingo.dbo.EPSConfiguration e ON p.EPSName = e.EPSName
WHERE ((p.TestMode=@Test) And (p.CurrentDate >= @StartDate) And (p.CurrentDate <= @EndDate)
AND (p.CurrentDate < e.RemovalDate) AND (p.CurrentDate > e.ChangeDate))
GROUP BY RTrim(p.PaytableName), RTrim(p.SkinName), p.Denomination,
CAST (p.EPSName AS INTEGER), e.ZoneName
END
/* add game configurations */
INSERT into @TempPlayCfg (PaytableName, SkinName, Denomination, EPSName,
Credits, Bet1, Configuration, MacCount, ChangeDate, RemovalDate,
ZoneName/*, TheoreticalHold*/)
SELECT RTrim(e.PaytableName), RTrim(e.SkinDescription), e.Denomination,
CAST (e.EPSName AS INTEGER) AS EPSName, 0 As Credits,
0 As Bet1, RTrim(e.ConfigurationName) As Configuration, 0 As MacCount,
e.ChangeDate, e.RemovalDate, e.ZoneName
FROM Bingo.dbo.EPSConfiguration e
WHERE (CAST(e.EPSName AS INTEGER) <> 0) And (e.RemovalDate >= @StartDate) And (e.ChangeDate <= @EndDate)
GROUP BY RTrim(e.PaytableName), RTrim(e.SkinDescription), e.Denomination, CAST (e.EPSName As INTEGER),
e.ChangeDate, e.RemovalDate, RTrim(e.ConfigurationName), e.ZoneName--, v.TheoreticalHold
ORDER BY RTrim(e.PaytableName), RTrim(e.SkinDescription), e.Denomination, CAST(e.EPSName As INTEGER)
INSERT INTO @TempPlayCmb
SELECT RTrim(r.PaytableName),
RTrim(r.SkinName) As SkinName,
r.Denomination As Denomination,
CAST(r.EPSName AS INTEGER) As EPSName,
Sum(r.Credits) As Credits,
Sum(r.Bet1) As Bet1,
MAX(RTrim(r.Configuration)) As Configuration,
0 As MacCount,
MAX(r.ChangeDate) As ChangeDate,
MAX(r.RemovalDate) As RemovalDate, r.ZoneName, v.TheoreticalHold
FROM @TempPlayCfg r INNER JOIN Bingo.dbo.viewPayTableParse v ON r.PaytableName = v.PaytableName
GROUP BY RTrim(r.PaytableName), RTrim(r.SkinName), r.Denomination,
CAST(r.EPSName AS INTEGER), r.ZoneName, v.TheoreticalHold
UPDATE @TempPlayCmb
set
Configuration = 'Invalid',
MacCount = 0
WHERE (ISNULL(Configuration, '') = '')
UPDATE @TempPlayCmb
set
Configuration = 'Virtual',
MacCount = 0
WHERE (RemovalDate <= @EndDate)
UPDATE @TempPlayCmb
set
Configuration = 'Configuring',
MacCount = 1
WHERE (ChangeDate <= '1Jan1971 0:0:0') And (RemovalDate > @EndDate)
UPDATE @TempPlayCmb
set
Configuration = 'Initial',
MacCount = 0
WHERE (ChangeDate <= '1Jan1971 0:0:0') And (RemovalDate <= @EndDate)
RETURN
END
Now, when I call the function from Query Analyzer..SELECT * FROM DailyInvFunc(11/4/2006, 11/5/2006)...it returns in 6 seconds. However, when I call the stored proc...EXEC DailyInvProc '11/4/2006', '11/5/2006'....it takes almost 2 minutes to return. Is there a way I can speed up the stored procedure or change my function around to make it run faster?...If anyone needs table schema's I can provide them...Thanks in advance for your help!
Steve
November 15, 2006 at 9:35 am
Hi,
Try this statement
SELECT * FROM dbo.DailyInvFunc (@StartDate, @EndDate)
Please always selects the columns by name rather then *.
Let me knwo why have you choose the function in place of stored procedure it self.
Cheers
cheers
November 15, 2006 at 10:51 am
We're using a C# Windows app which calls the stored proc and returns the data to a crystal reports viewer. We're using the function instead of the stored procedure itself because the original person who wrote the code did it this way and he's no longer with the company and our app will soon be obsolete but in the meantime I have to build this one last report.
Thanks,
Steve
November 16, 2006 at 3:27 pm
You may be running into a problem with Parameter Sniffing. Good details in this article:
As a workaround, you could try assigning some local variables with the values passed from the variables passed into the SP. I've seen it make a difference.
For example:
CREATE PROCEDURE sp_MyProc
@SPVAR VARCHAR(10)
AS
DECLARE @LOCALVAR VARCHAR(10)
SET @LOCALVAR = @SPVAR
SELECT... etc. WHERE fieldvalue = @LOCALVAR
November 17, 2006 at 7:43 am
Thanks it seemed to help with setting the date parameters to local variables....=)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply