asked on

Optimize select statement

When I ran this stored proc the first time, it takes about 4-5 minutes but after that, it only takes a second. Any ideas?

ALTER PROCEDURE dbo.ShowExistingPeriods @mycountry varchar(2)
AS
SELECT convert(varchar(10), DNLD_DATE,101) as DnldDate, COUNT(REC_ID) AS RecCnt,
dbo.GetFormattedString(SUM(FY_REVENUE), 'N') AS ORev,
dbo.GetFormattedString(SUM(FYD_REVENUE), 'N') AS RRev
FROM SQLtbldownload
WHERE (Country = @mycountry)
GROUP BY DNLD_DATE
ORDER BY DNLD_DATE DESC

obahat

The proc and all underlying objects recompile when you run it first. They are also loaded into the SQL server cache.
This should not take 4-5 minutes, unless there is a really complicated execution plan.

After that - the proc does not get compiled again, and the cached execution plan is used (if in the cache).

Other than that, your query looks ok and does not have anything special. Clearly, the content of the functions can affect the execution plan dramatically and contribute to the first slow run. If you wish - post the content of the function GetFormattedString.

IceMan713

ASKER

thanks, here is the function

ALTER FUNCTION dbo.GetFormattedString (@Number Money, @Format char(1))
RETURNS nvarchar(30) AS
BEGIN

declare @String nvarchar(30), @strlen INT, @negadjust INT

If @Number is null
      Set @Number = 0

If @Format = 'N'
      BEGIN
      Set @String = Cast(Convert(BIGINT,@Number) as nvarchar(20))
      Set @strlen = len(@String)
      If @Number <0 Set @negadjust = 1 ELSE Set @negadjust = 0
      if @strlen - @negadjust>3
            Set @String = stuff(@String,@strlen - 2,0,',')
      if @strlen - @negadjust>6
            Set @String = stuff(@String,@strlen - 5,0,',')
      if @strlen - @negadjust>9
            Set @String = stuff(@String,@strlen - 8,0,',')
      END
      ELSE
      If @Format = 'P'
            IF ABS(@Number) <999999999
                  Set @String = Cast(Convert(Numeric(30,1),@Number*100) as nvarchar(14))+'%'
            ELSE
                  SET @String = 'OVERRUN'
      ELSE
            If @Format = 'M'
                  Set @String = Cast(Convert(Numeric(30,2),@Number) as nvarchar(15))
            ELSE
                  If @Format = 'O'
                        Set @String = Cast(Convert(Numeric(30,1),@Number) as nvarchar(15))
                  ELSE
                        If @Format = 'T'
                        Set @String = Cast(Convert(Numeric(30,0),@Number*100) as nvarchar(14))+'%'
Return @String

END

ASKER CERTIFIED SOLUTION

obahat

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial