Re: Execute SQL UD Function call timeout, 1-3 seconds in query ana

We did build a little ASP.NET page yesterday that lets us put a given sql
query into a text box and then return the execution plan (using Showplan_Text
ON through the ADO.NET command) which we just display in a datagrid.

Unfortunately the plan was exactly the same as if I run Showplan_Text ON in
Query Analyzer. I'm guessing there is more going on then that we don't know
about yet.

Any advice as to what else we should look for in profiler? We also saw the
same plan come accross there, but I wonder what else we should be looking for.

Thanks for the reply Bill.

"William (Bill) Vaughn" wrote:

> I would turn on the profiler to see the difference between what QA and the
> ADO.NET interface is executing. I would do this and see if David Sceppa
> (from MS) pops up to pick up this issue.
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
>
> "LG614" <LG614@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:AEE7DE7A-B554-44EA-9262-496B7C8C684C@xxxxxxxxxxxxxxxx
> > Okay,
> > I have been at this for 2-3 days now and all over the internet. I have
> > found many people with near identical to similar issues and no solutions.
> > I
> > am fairly certain about what is not the problem and strong suspicion of
> > what
> > is. What I need now is a solution.
> > I have a fairly complex SQL statement that I have compiled in a User
> > Defined
> > Function. The results of this query fill a report. It is many Sums of
> > Case
> > statements returning about 25 columns. The majority of the content comes
> > from a table with about 2.5 mil records and there is one subquery that
> > calculates one column based off a table with about 1.3 million records.
> > The
> > execution plan shown in Query Analyzer is pretty efficient and the whole
> > thing can run in 1-3 seconds. When called from my ASP.NET page it returns
> > a
> > timeout error, but only 90-95% of the time, somtimes it runs(very slowly)!
> >
> > What I know:
> > - if I literally copy the SQL string generated by the VB code and run it
> > in
> > QA it is blazing fast. I can run SQL that calls the function or I can run
> > the sql inside the function w/ or w/out parameters. No matter which way I
> > spin it in QA it is fast.
> >
> > -when I run the work in QA Perfmon barely shows a processor blip and
> > that's
> > it.
> >
> > -when I run the work from our ASP.NET page perfmon has heavy processor
> > load
> > throughout and the disk activity slams to the top and stays floored til it
> > times out.
> >
> > -additionally I experimented with the content of the UD Function and found
> > that the one subquery I run (3 varied instances of to produce 3 of the
> > columns) is the culprit. When I comment out the subqueries the process
> > runs
> > super fast from ASP/ADO.NET. In particular it is a specific limiter in
> > the
> > where clause that compares a column in the subquery table to a column in
> > the
> > main/outer table such that they are equally limited to the same values for
> > these columns. A join of sorts "where subTable.Col = OuterMainTable.Col".
> > QA is fine with this and does very well yet ADO.NET is apparently very
> > confused and cannot create a plan that works.
> >
> > Obviously ADO.NET is doing something very different from QA (ODBC vs OLEDB
> > right?). What I need/want is to force ADO.NET to be smarter and run my
> > query
> > efficiently. I have tried adding Index hints to the functions and they
> > run
> > fine that way from QA but ADO.NET then errors out and tells me that it
> > cannot
> > create a plan due to query hints and please remove them.
> >
> > I have updated statistics, recreated indexes, defragged drives, changed
> > query memory settings, etc etc. Nothing makes a difference and also none
> > of
> > that addresses the fact that ADO just doesn't seem as smart as QA.
> >
> > I have also seen some things about settings that can affect execution
> > plans
> > that QA does for you behind the scenes, but I can't find anywhere as to
> > what
> > settings are the ones that will fix this issue.
> >
> > Does anyone know what causes this? It is very very frustrating that I
> > have
> > efficient, correct SQL that just can't be run correctly by the ADO.NET
> > product. Seems to me like it's broken. We've now lost 2-3 days of
> > development due to this too. Tomorrow I will be using a phone incident
> > with
> > Microsoft and definitely if they get me a solution I will post it here.
> >
>
>
>
.