SSE 2005 stored Procedure working in management studio but not in C# DAL

Question

I have created a stored procedure

CREATE PROCEDURE GetCustomerWiseSales(@StartDate nvarchar(10), @EndDate nvarchar(10))
AS

SELECT C.cCode, min(C.cName) as Customer, sum(P.BeerValue) as BeerValue, sum(P.RestGroup)as RestGroup
from Customers C
Join
(
SELECT     Sales.CustomerID, SUM(SalesLog.Quantity * SalesLog.Price) as BeerValue, 0 RestGroup
FROM         Sales INNER JOIN
                  SalesLog ON Sales.MemoNo = SalesLog.MemoNo
WHERE     (pGroup=8 and pSize>500) and Sales.Billdate>=@StartDate and Sales.Billdate<=@EndDate
group by  Sales.CustomerID

union all

SELECT     Sales.CustomerID, 0 BeerValue,SUM(SalesLog.Quantity * SalesLog.Price) AS RestGroup
FROM         Sales INNER JOIN
                  SalesLog ON Sales.MemoNo = SalesLog.MemoNo
WHERE     (pGroup!=8) and Sales.Billdate>=@StartDate and Sales.Billdate<=@EndDate
group by  Sales.CustomerID
)P
on P.CustomerID=C.cCode 
group by C.cCode

this SP is working nicely in Management Studio and outputs results before I blink my eyes. But when I added this SP in C# App through Data Access Layer and previewed Data using TableAdapter preview window, in first run it took 8-10 seconds to show data, in second run Preview windows threw Time Out exception. I also added a new TableAdapter with regular SQL to confirm this issues but regular SQL GetData function is working nicely.

I don't understand how a same procedure which is working nicely under same user-name and password in Management Studio, not working in DAL.

Any help would be appreciated.

Answer 1

You should capture an execution plan of the stored procedure running under the DAL and compare it to the one running under Management Studio express - you can do this using Sql Server Profiler.

Also make sure that you are running the stored procedure using exactly the same parameters (capture a trace of the procedure being executed from your DAL using Sql Server profiler and copy and paste the query into SQL server management studio)

Finally (this is a shot in the dark) you might want to try parameter masking your inputs as I have found it to have some effect in the past:

CREATE PROCEDURE GetCustomerWiseSales(@StartDate nvarchar(10), @EndDate nvarchar(10))
AS

DECLARE @MaskedStartDate NVARCHAR(10)
SET @MaskedStartDate = @StartDate
DECLARE @MaskedEndDate NVARCHAR(10)
SET @MaskedEndDate = @EndDate 

-- Rest of query with @StartDate replaced with @MaskedStartDate etc...

I've seen this make all the difference in certain situations.