Timeout expired

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
%>
itortuAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

itortuAuthor Commented:
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
Jim HornMicrosoft SQL Server Data DudeCommented:
>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
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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))
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

itortuAuthor Commented:
it takes a lot more than 5 seconds. about 2 minutes.
Jim HornMicrosoft SQL Server Data DudeCommented:
>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.  
itortuAuthor Commented:
how can this be done? i f you don't mind me asking
itortuAuthor Commented:
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.
itortuAuthor Commented:
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 : )
Anthony PerkinsCommented:
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

Anthony PerkinsCommented:
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)
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
itortuAuthor Commented:
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
itortuAuthor Commented:
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! : )
Anthony PerkinsCommented:
>>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?
itortuAuthor Commented:
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.
Anthony PerkinsCommented:
And again:
"And the error is ... ?"

"Have you tested with that exact value?" In other words "2006-4-14"
itortuAuthor Commented:
sorry nevermind, the problem was fixed already.
I am closing the question finally.

Thank you for your help : )



It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.