?
Solved

Timeout expired

Posted on 2006-04-12
17
Medium Priority
?
469 Views
Last Modified: 2008-02-01
I am trying to retrieve records from SQL server 200 and everytime that I run the page I get the error

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E31)
Timeout expired
/demographicsReport/wfla_2004.asp, line 31


line 31 :  set rs = conn.execute(sql)

when i run the query on qurey analyzer it gives me the records  the problem occurs when I do it thru the ASP, any help, ideas?

thank you  so much.

my code ion the page is:
Dim startDay,endDay

 'put the host name here
 host= "wfla"

 'put the start here
 startDay = "2004-02-27"

 endDay   = "2004-12-31"

 Dim oConn, oRS
  set oConn = GetSubscriberConnection
  set rs = oConn.execute("hm_newsubs_contest_rslts '" & host & "','" & startDay & "','" & endDay & "'")


  Dim conn, sql, rs, colorset, i, numcolors
  set conn = server.CreateObject ("ADODB.Connection")
  conn.ConnectionTimeout = 0
  conn.Open "Provider=SQLOLEDB;Data Source=192.168.5.14; Initial Catalog=myweather_replica; UID=sa; Password=myweather;"

  SQL = "SELECT *, a.description as age, o.description as occupation, i.description as income, l.city as city, l.state as state, l.zip as zip FROM subscriber s " & _
        "LEFT JOIN ages a on s.ageid = a.orderindex " & _
        "LEFT JOIN location l on l.subscriberID = s.subscriberkey " & _
        "LEFT JOIN occupations o on s.occupationid = o.id " & _
        "LEFT JOIN incomelevels i on s.incomelevelID = i.id " & _
        "WHERE host ='" & host &"' ORDER BY s.firstname"
  set rs = conn.execute(sql)
  function nz(val)
     if val = true then
         nz =1
     elseif val = false then
         nz = 2
     else
         nz = 0
     end if
 end function
%>
0
Comment
Question by:itortu
  • 9
  • 4
  • 2
  • +1
17 Comments
 

Author Comment

by:itortu
ID: 16439460
this is the code that makes up the stored procedure hm_newsubs_contest_rslts:


SELECT Distinct Subscriber.SubscribeDate, Subscriber.Firstname, Subscriber.Lastname, Subscriber.Email, Subscriber.DayPhone, Subscriber.EvePhone, Location.City, Location.State, Location.Zip, Subscriber.PromotionOK, LocationIcons.Caption AS 'PrimaryLocation', Location.Street
FROM Location Location, LocationIcons LocationIcons, Subscriber Subscriber
WHERE Subscriber.SubscriberKey = Location.SubscriberID AND LocationIcons.ID = Location.IconID AND ((Location.LocationType=1) AND (Subscriber.Host= @host) AND Subscriber.contactok=1)
ORDER BY Subscriber.SubscribeDate DESC
0
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 16439471
>SQL = "SELECT *,
The knee-jerk reaction of most DBA's would be to either retrieve fewer records or fewer fields.
Since you are returning all fields in the subscriber table, I suspect this would be a good place to start by removing the * and spelling out only the fields you need.

Hope this helps.
-Jim
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16439472
>when i run the query on qurey analyzer it gives me the records  the problem occurs when I do it thru the ASP, any help, ideas?
how much time does it take in the Query Analyzer?
how many rows get returned?

if it takes more than 5 seconds in QA, the query should be tuned (by adding indexes to the table(s))
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:itortu
ID: 16439558
it takes a lot more than 5 seconds. about 2 minutes.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16439618
>about 2 minutes.
Yep.  Most DBA's would not consider extending the timeout property greater than two minutes, so as angelIII states (along with my .02 about returning fewer records/fields) you'll need to tune your query so that it goes faster.  
0
 

Author Comment

by:itortu
ID: 16439673
how can this be done? i f you don't mind me asking
0
 

Author Comment

by:itortu
ID: 16440073
hey guys thank you fro your answers. i need to work on this deeply. if i get stuck while doing the tune up to the db
i will ask fro your advice definetly.

thanks,


itortu.
0
 

Author Comment

by:itortu
ID: 16440425
is ther a chance that you can show me how to create a stroed procedure from the query below?
it only needs to take a host parameter..

SELECT distinct firstname, lastname, email, city, state, zip, host,
subscribedate, a.description as age, o.description as occupation,
i.description as income            
FROM subscriber s
LEFT JOIN ages a on s.ageid = a.orderindex
LEFT JOIN location l on l.subscriberID = s.subscriberkey
LEFT JOIN occupations o on s.occupationid = o.id
LEFT JOIN incomelevels i on s.incomelevelID = i.id
WHERE host ='XXXX' and locationtype = 1
ORDER BY s.host, lastname


thanks : )
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16442239
Create Procedure usp_MyProcedure

As

SELECT      distinct
      firstname,
      lastname,
      email,
      city,
      state,
      zip,
      host,
      subscribedate,
      a.description age,
      o.description occupation,
      i.description income          
FROM      subscriber s
      LEFT JOIN ages a on s.ageid = a.orderindex
      LEFT JOIN location l on l.subscriberID = s.subscriberkey
      LEFT JOIN occupations o on s.occupationid = o.id
      LEFT JOIN incomelevels i on s.incomelevelID = i.id
WHERE      host ='XXXX'
      and locationtype = 1
ORDER BY
      s.host,
      lastname

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 16442260
This will not solve your timeout, problem.  You have a couple of choices:
A. Do as everyone has suggested and see if you cannot optimize the query or if you have done everything you can than
B. Change the CommandTimeout property for the Connection object or (if you use it) the Command object to a value greater than the defulat 30 seconds. For example, if you need 2.5 minutes:

cnn.CommandTimeout = 150     ' seconds

The reason that Query Analyzer executes is that by default it has an infinite timeout (CommandTimeout = 0)
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 16442833
I agree with acperkins that a stored procedure with only the sql will not solve the timeout problem.

you should run the SQL in the query analyser and activate the "Show Execution Plan" option.
besides the results you will get another pane with the execution plan.
if you see there any full table scans, you probably miss an index on that table.

here a list of indexes that you should have on the different table (each time 1 index) for this query
subscriber:
index 1: host, locationtype
ages:
clustered index 1: orderindex
locations:
clustered index 1: subscriberid
occupations:
clustered index 1: id
incomelevels:
clustered index 1: id

0
 

Author Comment

by:itortu
ID: 16447021
hi guys, beofre anything else, thank you for your help. I am so thankfuls for counting with you.
I will take your ideas and work them to fix my problem. I may not come back until tomorrow since I have to really
focus on what I need to do here.

I will let you knwo how things went.

Once again thank you. See you soon.

itortu
0
 

Author Comment

by:itortu
ID: 16457912
hi again guys,
i finally got the results I wanted. Had to create the stored procedure, create indexes, and get rid of duplicate records it was messy but after a day of work it was working nicely.

the only problem that i am facing is an error that is returned by the asp page.
the sp takes in 3 parameters (host, startDate, and endDate - todays date)

'put the host name here
 host= "wltv"

 'put the start here
 startDay = "2004-27-02"

 endDay =    Year(Now()) & "-" & Month(Now()) & "-" & Day(Now())

if i enter the endDate manually just as I do it for the startDate the page works without problems, but that is not what I intent to do.
I was wondering if any of you could give me some help. Here is a copy of my sp:

ALTER
procedure StationDemographics_sp (@host nvarchar(25), @start datetime, @end datetime)
AS
SELECT distinct firstname, lastname, gender, email, city, state, zip, PromotionOK,  host,  
subscribedate, a.description as age, o.description as occupation,
i.description as income            --, l.city as city, l.state as state, l.zip as zip
FROM myweather_replica.dbo.subscriber s
LEFT JOIN replica.dbo.ages a on s.ageid = a.orderindex
LEFT JOIN replica.dbo.location l on l.subscriberID = s.subscriberkey
LEFT JOIN replica.dbo.occupations o on s.occupationid = o.id
LEFT JOIN replica.dbo.incomelevels i on s.incomelevelID = i.id
WHERE host = @host and locationtype = 1 and subscribedate between @start and @end
ORDER BY s.host, lastname


Thank you! : )
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16458339
>>the only problem that i am facing is an error that is returned by the asp page. <<
And the error is ... ?

>>endDay =  Year(Now()) & "-" & Month(Now()) & "-" & Day(Now())<<
This gives you something like:
'2006-4-14'

Have you tested with that exact value?
0
 

Author Comment

by:itortu
ID: 16466875
yes, if i enter the date manually, just as i do it for the start date the page works fine.

this manually entered values is:
2006-04-14

thank you.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16474529
And again:
"And the error is ... ?"

"Have you tested with that exact value?" In other words "2006-4-14"
0
 

Author Comment

by:itortu
ID: 16474591
sorry nevermind, the problem was fixed already.
I am closing the question finally.

Thank you for your help : )



0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question