?
Solved

Optimize select statement

Posted on 2005-04-21
6
Medium Priority
?
183 Views
Last Modified: 2012-06-22
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
0
Comment
Question by:IceMan713
  • 2
3 Comments
 
LVL 5

Expert Comment

by:obahat
ID: 13835506
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.

0
 
LVL 1

Author Comment

by:IceMan713
ID: 13835631
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
0
 
LVL 5

Accepted Solution

by:
obahat earned 2000 total points
ID: 13835910
There is nothing special or suspicious in the function. Can you look at the execution plan and see if there is anything fishy there?

Do me a favor and try this query instead:

ALTER PROCEDURE dbo.ShowExistingPeriods @mycountry varchar(2)
AS
SELECT convert(varchar(10), DNLD_DATE,101) AS DnldDate,
      RecCnt,
      dbo.GetFormattedString(SUM1, 'N') AS ORev,
      dbo.GetFormattedString(SUM2, 'N') AS RRev
FROM (

      SELECT DNLD_DATE,
            COUNT(REC_ID) AS RecCnt,
        SUM(FY_REVENUE) AS SUM1,
        SUM(FYD_REVENUE) AS SUM2
      FROM         SQLtbldownload WITH (NOLOCK)
      WHERE     (Country = @mycountry)
      GROUP BY DNLD_DATE) RS
ORDER BY 1 DESC


If that doesn't help:
Can you tell more about the setup that you have? What is the server running? Is it a busy server? Memory allocation in the server? etc.

If there's nothing fishy about the execution plan, then I would argue that you have recompilcation/caching issues.

It is not simple to do these things remotely :)

0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question