Script runs too long and sometimes times out

[spacedeveloper]

Hi everyone,

I've been working on trying to track down a problem for days now and was wondering if someone may be able to help me on this one.

I am trying to make a call to a sql server stored proc that takes a property id (we work in the hotel industry) and pings the property's server to see if it is down.

when I run the stored proc directly from sql server the result comes back right away (including from a propertyid whose server is down). However, when I run the script that called the stored proc (with a propertyid that is down) the page takes several minutes to refresh and many times just times out. I wonder why? We can't ask a customer to wait that long on a page...they'll just go somewhere else.

Can anyone find the problem?

Thank you very much!!

P.S. The page works fine with properties whose server is up and running and returns a ping.

    '*************************************************  ********
    Dim ExecuteCodeBlock
    ExecuteCodeBlock = True 
    
    If ExecuteCodeBlock Then
    
        Dim propSelect, firstPar, secondPar, PropertyID, PropertyIP
        
        ' Get propid from selected property dropdown
        propSelect = request.form("HotID")
        firstPar = InStr(propSelect, "(")
        secondPar = InStr(propSelect, ")")
        PropertyID = Mid(propSelect, firstPar + 1, secondPar - firstPar - 1)

        If firstPar = "" Or secondPar = "" Or PropertyID = "" Then
            ' Selected dropdown property does not have any propid
        Else
            Dim pingsql, rsPing, connPing, down, strPingResults
            pingsql = "exec sp_PingProperty '" & PropertyID & "'"
            Set rsPing = Server.CreateObject("ADODB.RecordSet")
            Set connPing = Server.CreateObject("ADODB.Connection")
            connPing.ConnectionTimeout = 20
	        connPing.CommandTimeout = 30
            connPing.Open(strConn)
            down = True
            Set rsPing = connPing.Execute(pingsql)
                
            rsPing.MoveFirst
            
            Do While Not rsPing.EOF
                strPingResults = LCase(rsPing("output"))

                If InStr(strPingResults, "reply") Then
                    down = False
                    Exit Do
                Else
                    down = True
                End If
                
                rsPing.MoveNext
            Loop
                        
            rsPing.Close
            connPing.Close    
            Set rsPing = Nothing
            Set connPing = Nothing
            
            If down = True Then
                If system = "TEST" Then
                    Response.Write ("<script type='text/javascript'>alert('We apologize! The property location you selected is experiencing technical difficulties. \n\rWe are working to resolve the issue. Please try your online payment again later.'); window.location = '//[TEST Hotel]/[page].asp';</script>")
                Else
                    Response.Write ("<script type='text/javascript'>alert('We apologize! The property location you selected is experiencing technical difficulties. \n\rWe are working to resolve the issue. Please try your online payment again later.'); window.location = '[URL unfurl="true"]https://www.[/URL][LIVE Hotel].com/[page].asp';</script>")
                End If
            End If
        End If
        
    End If
    '*******************************************

 

[foxbox]

if you run direct from SQL, you have only the timeout values from that server:
connPing.ConnectionTimeout = 20 connPing.CommandTimeout = 30

a script on a webserver had his own timeout values, and it looks like that value is set shorter then SQL.

so maybe a liune with Server.ScriptTimeout=999 (or another huge amount) in the script would work...

 

[spacedeveloper]

Thanks Foxbox for the reply.

Many times the process happens successfully, but only after a few minutes. However, I need to determine WHY it's taking so long to process. Our db is not getting hit hard at all so I can't imagine that there's any sort of wait due to sql server overload.

I really can't have our customers wsait too long for the page to load. It's just taking waaaay too long for them and they're getting frustrated.

 

[foxbox]

i'm not sure how the timeouts work. A connPing.ConnectionTimeout = 20 and connPing.CommandTimeout = 30 could mean that that the SP is waiting 50 seconds.

The default IIS timeout for an ASP page is 90 seconds. I would expect that the SQL timesout sooner then the script, so a maximum wait of 50 seconds. You "write "few minutes", which looks like (50+90) = 140 seconds?

What is the max time you want them to wait? 5 seconds?
I suggest you experiment with the timeout settings. Eg:
connPing.ConnectionTimeout = 2
connPing.CommandTimeout = 2

Server.ScriptTimeout=5

 

[spacedeveloper]

Yeah, I've tried adjusting the timeouts to no avail. The very strange thing is that when a down server happens to be selected then NONE of the code on that page seems to get hit until after the timeout (or even AFTER the timeout is supposed to occur!)

I'm at a complete loss.

 

[foxbox]

maybe this applies to u:

http://support.microsoft.com/default.aspx?scid=KB;en-us;q188858

 

[spacedeveloper]

Thanks foxbox, but unfortunately that didn't work either.

 

[spacedeveloper]

Does anyone have any ideas as to why this script is running so long??

BTW, adjusting connPing.ConnectionTimeout and connPing.CommandTimeout have no affect on the amount of time the script takes to complete. The script almost always completes in about 110 sec. no matter what.

The stored proc runs through its script just fine and returns a result within 10-15 sec AT MOST (based upon a log that is written to from the stored proc that can be tracked in real-time).

So, anyone???

 

[gmmastros]

I would suggest that you schedule a SQL Server job to ping all your servers. Set the job to run at a fairly short interval (5 minutes ?). Store the results in a table. Then... just use the value in the table to determine connectivity.

Of course, this is a different approach and some users will occasionally get bitten, like when your scheduled job says a remote server is up, but it then goes down, and the user attempts a connection. Of course, this doesn't completely solve the problem, but in my opinion, it's a 99% solution.

when I run the stored proc directly from sql server the result comes back right away

You could be running in to a permissions problem. When you run the stored proc directly from sql server, are you using that same login that is used on the web page? I'm guessing it's a different user which would explain why you are getting different execution times. If you are using SQL2005 or up, you may want to investigate the "Execute As" clause.

http://technet.microsoft.com/en-us/library/ms188354.aspx

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[spacedeveloper]

Gmmastros, thanks for your reply. I'm doubting that there's a user permission issue, because I am writing to a text file from the stored proc (when using the webpage) and a result is output to the text file almost immediately (after the stored proc is completed). The problem seems to be immediately after the sp finishes...the vbscript seems to just "hang" for about 100 seconds after that and then sort of "turns back on" and completes the script. That's what's getting me and I can't for the life of me figure out why.

 

[wvdba]

i have run into a situation like this.
when there's a "deadlock". if a user has exclusive control of a table/row, then it will time out.

 

[gmmastros]

Can you post the code for the store procedure?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[spacedeveloper]

Sure, here it is:
***********************
<CODE>
CREATE PROCEDURE [dbo].[sp_PingProperty]
@propID nvarchar(6)

AS
SET NOCOUNT ON;
declare @cmd varchar(200);

create table #ping(returntext varchar(2000))
set @cmd='Ping -n 3 -w 2000 ' + @propID
insert into #ping
exec master.dbo.xp_cmdshell @cmd

set @cmd = 'ping -n 3 -w 2000 ' + @propID + ' > c:\Inetpub\osqloutput.txt'
exec master.dbo.xp_cmdshell @cmd

if not exists(select * from #ping with (nolock) where returntext like '%Reply from%')
begin
drop table #ping
return 0
end
else
begin
drop table #ping
return 1
end
GO

</CODE>
***********************

 

[gmmastros]

I'm a little surprised that your code works at all because your parameter is limited to 6 characters. If you pass in a value that is longer than 6 characters, it will silently truncate it for you.

You're pinging twice. Once to get it in to a temp table, and again to output to file. Ping is kinda slow. You'll get better performance (overall) if you only do it once.

What version of SQL Server are you using? SQL2000, 2005, 2008, or 2008R2? If you are using SQL2005 or newer, you may want to consider writing a CLR procedure for this.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[spacedeveloper]

Yeah, the parameter size is small, but hasn't been a problem due to the variable having really only 3 characters max and it is validated beforehand as well.

The double pinging doesn't seem to be a problem either b/c the 2nd pinging writes to a text file that almost immediately gets generated, so the "hanging" appears to be somehow associated with the vbscript itself. However, I can see your point about the sql pinging code being redundant and inefficient code. I've looked around to see how I may be able to local temp table data into a text file (the result of the 2nd pinging code) and can't find a way to do it.

Unfortunately, I'm currently using SQL Server 2000.

 

[gmmastros]

ping first to the file and then Insert/Exec from the file in to the temp table. the dos command would be:

type C:\inetpub\osqloutput.txt


Every time I try to put code, this crappy site times out or errors out, or whatever. The preview screen is blank (or a polar bear in a snow storm). Either way... I apologize that I cannot help you with code because this site is so flaky.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom