 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion GroupsGeneralPHPASPPerlColdFusionFlashHTML, CSS, ScriptsBrowsers |
Webmaster Forum / ASP / Database Access / August 2008Issue with retrieving large data over web using Stored Procedure
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. 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 .CommandTimeout = 4800 .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? I would appreciate for any help. Thanks. > 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. [quoted text clipped - 7 lines] > .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 [quoted text clipped - 4 lines] > > Is there any suggestions to fix this problem? If you have "on error resume next" anywhere, comment it out so you will see errors.  SignatureMicrosoft 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" 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. > > Hello. > > I moved my stored procedure and tables from SQL 2000 to SQL 2005. [quoted text clipped - 34 lines] > If you have "on error resume next" anywhere, comment it out so you will see > errors. 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. > Actually the stored procedure works fine because it retrieve data > while I execute the sp at the SQL Server 2005. [quoted text clipped - 49 lines] >> I don't check it very often. If you must reply off-line, then remove >> the "NO SPAM" SignatureMicrosoft 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" 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 > 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 [quoted text clipped - 59 lines] > >> I don't check it very often. If you must reply off-line, then remove > >> the "NO SPAM" 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. > Hi Bob, > [quoted text clipped - 74 lines] >> I don't check it very often. If you must reply off-line, then remove >> the "NO SPAM" SignatureMicrosoft 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" Thank you so much. I will try that. > 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 [quoted text clipped - 83 lines] > >> I don't check it very often. If you must reply off-line, then remove > >> the "NO SPAM" 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. > 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 [quoted text clipped - 83 lines] > >> I don't check it very often. If you must reply off-line, then remove > >> the "NO SPAM" 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. > It appears that it was hitting the stored procedure, and then it > gives this stats. [quoted text clipped - 119 lines] >> I don't check it very often. If you must reply off-line, then remove >> the "NO SPAM" SignatureMicrosoft 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" 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. > 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 [quoted text clipped - 126 lines] > >> I don't check it very often. If you must reply off-line, then remove > >> the "NO SPAM" 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. > Thanks for reply. > [quoted text clipped - 135 lines] > > >> I don't check it very often. If you must reply off-line, then remove > > >> the "NO SPAM" Frankly, no. ASP is really not designed for activities that take this long to complete. If the procedure cannot be tuned to return results in less than a minute, then I would suggest you move that functionality into a scheduled task that populates a table with the results from the procedure. Then create another procedure to be called from ASP to retrieve results from that table. It should be much quicker unless you are doing something silly like retrieving millions of rows of data. > 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? SignatureMicrosoft 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" Hi Bob, Thanks for all your help. You know what. I found a solution. I realized that the tables at the new server did not have have proper indexes. The indexes did not copy over properly to the new server. I will try that first. Then, if it still cause so much delay, then I would create a new procedure or try other approach. Thanks. > Frankly, no. ASP is really not designed for activities that take this long > to complete. If the procedure cannot be tuned to return results in less than [quoted text clipped - 8 lines] > > Beside what the recommendation stated here (mostly index), is there > > any other improvements I could do at the ASP page? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.