Different results when executing from .NET component compare to executing from SQL Managem

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

Different Execution Plan using COM+ of Query Analyzer.

Hi,
I have the following problem : one of our statements gives a different
execution plan if it is executed from a component in COM+, of directly
in Query Analyzer.
I checked this using the following scenario :
* Execute the business code and profile using SQL Profiler.
* I included the query plan in the trace.
* Copy/Paste the statement to Query Analyzer, to ensure that I have
exactly the same statement. Execute it, and compare the result from
SQL Profiler.
If I compare the two, he does a table scan coming from COM+, and an
Index S coming from Query Analyzer. The COM+ version takes a
minute, the Query Analyzer doesn't even last a second.
I have tried this scenario staring from a restart of the SQL Server
service, so I am sure that caching of the statement is not the cause.
If I restart and first execute from Query Analyzer, the COM+ version
still takes just as long.
Note that this is on a database where no one else is working on.
After adding an INDEX hint to the statement, both the query plans are
the same, and both execute quickly.
Of course I want to avoid having to add INDEX hints to statements, and
I want to be sure that if I request a Query Plan in Query Analyzer, I
get something that I can trust.
Any suggestions what might be the cause of this ?
JDAre you sending the statement from the client app or are you executing a
stored procedure?
AMB
"JanM" wrote:

> Hi,
> I have the following problem : one of our statements gives a different
> execution plan if it is executed from a component in COM+, of directly
> in Query Analyzer.
> I checked this using the following scenario :
> * Execute the business code and profile using SQL Profiler.
> * I included the query plan in the trace.
> * Copy/Paste the statement to Query Analyzer, to ensure that I have
> exactly the same statement. Execute it, and compare the result from
> SQL Profiler.
> If I compare the two, he does a table scan coming from COM+, and an
> Index S coming from Query Analyzer. The COM+ version takes a
> minute, the Query Analyzer doesn't even last a second.
> I have tried this scenario staring from a restart of the SQL Server
> service, so I am sure that caching of the statement is not the cause.
> If I restart and first execute from Query Analyzer, the COM+ version
> still takes just as long.
> Note that this is on a database where no one else is working on.
> After adding an INDEX hint to the statement, both the query plans are
> the same, and both execute quickly.
> Of course I want to avoid having to add INDEX hints to statements, and
> I want to be sure that if I request a Query Plan in Query Analyzer, I
> get something that I can trust.
> Any suggestions what might be the cause of this ?
> JD
>|||No stored procedure is used. All statements are executed from within
COM + transactions.
JanM
"examnotes" <AlejandroMesa@.discussions.microsoft.com> wrote in messa
ge news:<AA5F00BB-E11F-496F-8D69-DBE0BB3DAB5A@.microsoft.com>...
> Are you sending the statement from the client app or are you executing a
> stored procedure?
>
> AMB
>
> "JanM" wrote:
>