Hi all,
I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.
This started to really annoying me, any thoughts or solution?
Thanks very much guys
I'm interested in some more details: what's the meaning of different results? Returning different data (rows)? Or same data (rows) in different orders? Can you post the stored proceudre if it does not contain too many statements?
Since recomplie the stored procedure can solve the issue, it seems SQL optimizer chooses different execution plans, thus may lead to different results. One work around is to call the stored procedure with recomplie:
EXECUTEyourSPName WITH RECOMPILE
However this is not a so good solution, as RECOMPLIE at each execution will impact SQL performance.
|||The result that come back seems to be from the old version of the stored procedure, or could be it's joining differently.
I am suspecting that .NET SQL provider has a seperate query plan cache. The actual select statement is like this, I removed few lines in the where clause and column selections.
SELECT
main.*,fa.*
FROM [NetFare] main
INNER JOIN dbo.Agent fa
ON main.FareId = fa.FareId
LEFT OUTER JOIN travel.dbo.currencies c
ON c.Code = main.FareCurrency
Where
AND (PriceReturnType = @.ReturnType)
And main.PortSetID in (Select Distinct PortSetID
From FarePortSetMember Where PortCode = @.DestinationPort Or PortCode = @.DestinationCity)
And main.OriginPortSetId in (Select Distinct FarePortSetId
From FarePortSetMember Where PortCode = @.OriginPort Or PortCode = @.OriginCity)
AND ((DateFrom <= @.EarliestDepartureDate) AND (DateTo >= @.EarliestDepartureDate))
AND (DATEDIFF(day, TicketDateFrom,GetDate()) >= 0 AND DATEDIFF(day, TicketDateTo, getdate()) <=0)
And status in ('Active','Updating')
Option (KEEPFIXED PLAN)
I just added the OPTION (KEEPFIXED PLAN) and have been monitoring to see if the problem happens again.
So there is no fancy stuff in the query, but it seems to me that the .NET SQL provider are using different query plan.
Could MVP dudes verify this for us, please?
I am using SQL 2000 by the way and .NET 1.1 component called from ASP.NET 2.0
