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

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.

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.