Re: Any way to avoid using SP_EXECUTESQL in ADO.NET?

Tech-Archive recommends: Speed Up your PC by fixing your registry

Thanks for the response,

When we run the queries in Query Analyzer we are directly inserting the
parameter values into the query. From what I can tell, the parameter
values themselves are typical for the data we are working with. Does
SP_EXECUTESQL use statistics differently than running the raw query in
Query Analyzer?

Thanks,

Brad P.

David Browne wrote:
"Brad P" <bradp@xxxxxx> wrote in message
news:1152818503.486755.310220@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi,

Our development team has encountered some performance problems when
using ADO.NET (using the SqlCommand and SqlDataAdapter objects) to
perform queries on large data tables. We've drilled into the issue
considerably, and the problem seems to be related to how ADO.NET uses
the SP_EXECUTESQL when running text queries. I say this because when we
run our query in Query Analyzer by itself the performance is less than
a second, but when running it with the SP_EXECUTESQL command (which is
what Profiler is telling us is being used), it takes nearly half a
minute.
. . .

It is unlikely that SP_EXECUTESQL is the real cause of your problem. It is
more likely that you are getting a sub-opitmal execution plan when running
SP_EXECUTESQL because of the different amount of statistical evidence
available to the optimizer.

When you run the queries in Query Analyzer are you using local variables and
parameter markers the same way SP_EXECUTESQL is? What are the parameter
values passed, and are they statistically typical or atypical?

David

.

Relevant Pages

  • Re: Any way to avoid using SP_EXECUTESQL in ADO.NET?
    ... Our development team has encountered some performance problems when ... the SP_EXECUTESQL when running text queries. ... run our query in Query Analyzer by itself the performance is less than ... parameter markers the same way SP_EXECUTESQL is? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: COM+ and ADo and performance.
    ... Change CursorLocation to clUseServer and CursorType to ctOpenForwardOnly it ... > queries and there is no more than 5-10 records at a time. ... I simply write the same query in Query Analyzer. ... But When I execute the same insert query from TClientDataSet, ...
    (borland.public.delphi.database.ado)
  • Re: embedded in strings
    ... The thing that had me going was that the same queries worked fine insql ... query analyzer if you want to see what I'm talking about. ... > Can you explain a little better how it throws a null reference exception? ... > | I'm having problems with queries like the following in sqlserverce. ...
    (microsoft.public.sqlserver.ce)
  • Re: iis/asp + sql2000 a bit slow
    ... > I have a performance problem, but I don't really know where the exact ... > I ran every single on of these queries with the SQL Query Analyzer, ... All queries run superfast, so I'm ...
    (microsoft.public.inetserver.asp.db)
  • Re: embedded in strings
    ... queries with vaues longer than the column being queried. ... I guess I could send you the sqlce database so you can test it. ... > | the null reference exception. ... > | query analyzer if you want to see what I'm talking about. ...
    (microsoft.public.sqlserver.ce)