Justin Doh
8/15/2008 8:48:00 PM
I think I know how to analyze the query.
Beside what the recommendation stated here (mostly index), is there any
other improvements I could do at the ASP page?
Thanks.
"Justin Doh" wrote:
> Thanks for reply.
>
> Are you referring to the Database Engine Tuning Advisor?
> If it is, how do I go about analyzing a stored procedure with selecting
> multiple tables?
> I am trying to figure out where the Query Tuning tool is located.
>
> Thanks.
>
> "Bob Barrows [MVP]" wrote:
>
> > OK, I repeat: is there any chance of optimizing this stored procedure? SQL
> > 2005 has a great Query Tuning tool that may make some useful
> > recommendations. Give it a try.
> >
> > You should ask for assistance with optimizing the procedure at the
> > ..sqlserver.programming newsgroup.
> >
> > Justin Doh wrote:
> > > It appears that it was hitting the stored procedure, and then it
> > > gives this stats.
> > >
> > > CPU: 145569
> > > Reads 76727637
> > > Writes 125593
> > > Duration 214490
> > >
> > > - Is there any tool in the SQL Profiler that analyze each Trace?
> > >
> > > On the web, it stills give the error message.
> > >
> > > "Active Server Pages error 'ASP 0113'
> > >
> > > Script timed out
> > >
> > > The maximum amount of time for a script to execute was exceeded. You
> > > can change this limit by specifying a new value for the property
> > > Server.ScriptTimeout or by changing the value in the IIS
> > > administration tools."
> > >
> > > - I do not have an authority to change any setting at the IIS, so I
> > > need to do it at ASP.
> > >
> > > Thanks.
> > >
> > > "Bob Barrows [MVP]" wrote:
> > >
> > >> You don't use it to run the stored procedure. You use it to trace
> > >> commands being run by sql server while your asp page is attempting
> > >> to run the stored procedure.
> > >>
> > >> In other words, start sql profiler, create a new trace and start it,
> > >> open your browser and navigate to the asp page that is giving you
> > >> the problem. Look at the trace to see if you asp page successfully
> > >> connected to the server and issued the command to execute the stored
> > >> procedure.
> > >>
> > >> Justin Doh wrote:
> > >>> Hi Bob,
> > >>>
> > >>> Wow, I did not know there was SQL Profiler at SQL 2005.
> > >>> How do I use SQL Profiler at SQL 2005 to run the stored procedure?
> > >>>
> > >>> Thanks in advance.
> > >>> Justin
> > >>>
> > >>> "Bob Barrows [MVP]" wrote:
> > >>>
> > >>>> You are attempting to call it from an ASP page, correct? You need
> > >>>> to verify that when you run the ASP page, that the procedure is
> > >>>> actually getting called. The best way to do that is via SQL
> > >>>> Profiler.
> > >>>>
> > >>>>> How do I use "on error resume next"?
> > >>>> ???
> > >>>> I meant for you to look in your vbscript code in your .asp file and
> > >>>> verify if an "on error resume next" statement exists in your code.
> > >>>> If it does, comment it out so you can see any errors that occur.
> > >>>>
> > >>>> Justin Doh wrote:
> > >>>>> Actually the stored procedure works fine because it retrieve data
> > >>>>> while I execute the sp at the SQL Server 2005.
> > >>>>> I don't think I need to check at the SQL Profiler because I am
> > >>>>> testing at the SQL 2005 level.
> > >>>>> How do I use "on error resume next"?
> > >>>>>
> > >>>>> Thanks in advance.
> > >>>>>
> > >>>>> "Bob Barrows [MVP]" wrote:
> > >>>>>
> > >>>>>> Justin Doh wrote:
> > >>>>>>> Hello.
> > >>>>>>> I moved my stored procedure and tables from SQL 2000 to SQL
> > >>>>>>> 2005.
> > >>>>>>> I am having an issue to retrieve a large amount of data using
> > >>>>>>> previous stored procedure (sp) over web (ASP page).
> > >>>>>>> Other sp works fine, but one sp that retrieves large amount of
> > >>>>>>> data does not even get executed over ASP page.
> > >>>>>>
> > >>>>>> Have you verified this with SQL Profiler? If not, you should.
> > >>>>>>
> > >>>>>>>
> > >>>>>>> First,
> > >>>>>>> I tried to increase the server timeout by doing this way.
> > >>>>>>> <% server.ScriptTimeout = 40000 %>
> > >>>>>>>
> > >>>>>>> Second,
> > >>>>>>> I also included ConnectionTimeout and CommandTimeout to see if
> > >>>>>>> it would help any such as..
> > >>>>>>>
> > >>>>>>> With rsReport
> > >>>>>>> .ConnectionString = ConnOLAP
> > >>>>>>> .ConnectionTimeout = 4800
> > >>>>>>
> > >>>>>> This one is irrelevant to this issue
> > >>>>>>
> > >>>>>>> .CommandTimeout = 4800
> > >>>>>>
> > >>>>>> 4800 is ridiculous, especially if the procedure is not getting
> > >>>>>> executed.
> > >>>>>>
> > >>>>>>> .Load(strSQL)
> > >>>>>>> End With
> > >>>>>>>
> > >>>>>>> When I executed sp at SQL Server 2005 level, I got the data fine
> > >>>>>>> (after long period of process), but when it gets executed over
> > >>>>>>> web (ASP), no data gets retrieved.
> > >>>>>>>
> > >>>>>>> Is there any suggestions to fix this problem?
> > >>>>>>>
> > >>>>>>
> > >>>>>> If you have "on error resume next" anywhere, comment it out so
> > >>>>>> you will see errors.
> > >>>>>>
> > >>>>>> --
> > >>>>>> Microsoft MVP - ASP/ASP.NET
> > >>>>>> Please reply to the newsgroup. This email account is my spam trap
> > >>>>>> so
> > >>>>>> I don't check it very often. If you must reply off-line, then
> > >>>>>> remove the "NO SPAM"
> > >>>>
> > >>>> --
> > >>>> Microsoft MVP - ASP/ASP.NET
> > >>>> Please reply to the newsgroup. This email account is my spam trap
> > >>>> so
> > >>>> I don't check it very often. If you must reply off-line, then
> > >>>> remove the "NO SPAM"
> > >>
> > >> --
> > >> Microsoft MVP - ASP/ASP.NET
> > >> Please reply to the newsgroup. This email account is my spam trap so
> > >> I don't check it very often. If you must reply off-line, then remove
> > >> the "NO SPAM"
> >
> > --
> > Microsoft MVP - ASP/ASP.NET
> > Please reply to the newsgroup. This email account is my spam trap so I
> > don't check it very often. If you must reply off-line, then remove the
> > "NO SPAM"
> >
> >
> >