x

Select statement vs StoredProcedure

Hi, It is a SQL 2008 R2 server. I have a very large table with more than 2 million rows.

I have a procedure:

BEGIN TRAN

CREATE PROCEDURE Test
@StartDay DATE ,
@EndDay DATE ,
@xCreateDate DATETIME ,
@xUpdateDate DATETIME
AS 
BEGIN  
 -- SET NOCOUNT ON added to prevent extra result sets from  
 -- interfering with SELECT statements.  
    SET NOCOUNT ON ;  

-- Insert statements for procedure here  
--declare @s smalldatetime;set @s = cast('2011-03-01' as smalldatetime);  
--declare @f smalldatetime;set @f = cast('2011-03-31' as smalldatetime);  

    SELECT  *
    FROM    Testtable
    WHERE   ( InvoiceDate >= @StartDay
              AND InvoiceDate < @EndDay
              AND ( ( @xCreateDate = '' )
                    OR ( xCreateDate > @xCreateDate )
                  )
              AND ( ( @xUpdateDate = '' )
                    OR ( xUpdateDate > @xUpdateDate )
                  )
            )  
END  

--**SQL statement:**

SELECT  *
FROM    Testtable
WHERE   ( InvoiceDate >= '7/1/2008'
          AND InvoiceDate < '8/1/2008'
          AND ( ( '' = '' )
                OR ( xCreateDate > '' )
              )
          AND ( ( '' = '' )
                OR ( xUpdateDate > '' )
              )
        )


ROLLBACK

Below are the execution details when the SP is run as opposed to the select query being run:

When it is run for July data,

sql statement - 2273991 rows - 9 min 18 secs - Execution plan:table scan

stored proc - 2273991 rows - 2 min 35 sec - Execution plan:RID lookup, index seek (invoice date)

When it is run for Aug data,

sql statement - 1920308 - 2.26 - RID lookup, index seek (invoice date)

stored proc - 1920308 - 2.23 - RID lookup, index seek (invoice date)

Questions Why is there such differences in the execution times and execution plans?

There is an non-clustered index of invoicedate which is used when running the statements for Aug data.

Can someone help me on this??

Thank you in advance..
more ▼

asked Aug 24, 2011 at 11:59 PM in Default

Mandar Alawani gravatar image

Mandar Alawani
376 32 37 44

What are the columns for the RID lookup? You may get some performance gain by INCLUDEing these in the invoicedate index. What are the output columns for the table scan? The table is a bit large for a heap if you don't have a clustered index.
Aug 25, 2011 at 01:07 AM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

It can be cause as follow:

When the procedure was executed for the first time, it created a execution plan, which was cached. For the first time parameters which produced an index seek were passed. Then when you subsequently uses the procedure it is reusing existing plan and doing always an index seek and not table scan.

In case of SQL statement, when the query is not parametric but you simply write the values, then the statements differ and for each query a new execution plan is being created. Therefore some values are causing the table scan and some index seek.

The execution time difference is only a consequence of the plan. As the seeks takes less time that full table scan.

You can have not up to date statistics. Try to update statistics and then try the select statements again.

Also if you really want to compare the behavior of stored proc and the query, you need to clear the plan cache prior executing each of the commands using DBCC FREEPROCCACHE so you start the comparison from the same point.
more ▼

answered Aug 25, 2011 at 01:30 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

can some administrator help me here...I wanted to VOTE UP this post/answer...and its showing -1... :(
Aug 25, 2011 at 02:51 AM Mandar Alawani
you should be able to un-vote the down vote, tick the orange 'down thumb' and it will revert your downvote
Aug 25, 2011 at 02:53 AM Kev Riley ♦♦
any votes can be cancelled within one day of being cast - after that they are set in stone!
Aug 25, 2011 at 02:55 AM Kev Riley ♦♦
even if I click on the orange 'down thumb' or green 'up thumb'..its increasing the value negatively...like now its -6..
Aug 25, 2011 at 02:56 AM Mandar Alawani
try refreshing the page between each click
Aug 25, 2011 at 02:59 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1840
x412
x41

asked: Aug 24, 2011 at 11:59 PM

Seen: 3749 times

Last Updated: Aug 25, 2011 at 01:13 AM