Results 1 to 2 of 2

Thread: time out error

  1. #1
    Join Date
    Jun 2007

    Unanswered: time out error

    while i am trying to search in mssql server2000 through vb 6.0 coding,after some times it shows time out error.
    plz help me.
    what will be the reason?
    'conTrack.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TRACKBASE;Data Source=tpcblr"
    conTPCMast.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;pwd=tts;Initial Catalog=TRACKBASE;Data Source=TPCBLR" 
     If Option11.Value = True Then
     ORGIN = " AND POD_NO NOT LIKE '" & Trim(Text14.Text) & "%' "
     ElseIf Option12.Value = True Then
     ORGIN = " AND POD_NO LIKE '" & Trim(Text14.Text) & "%' "
     End If
     sqlstr = "select DISTINCT POD_NO,IOMNO,ACTDESTN,WEIGHT,REMARKS,ACTORIGIN,SYS_DT,typeofdoc from IOMMAST where   typeofdoc='" & Trim(Combo2.Text) & "' AND SYS_DT>='" & Format(Text9.Text, "MM/dd/yyyy") & "' AND SYS_DT<='" & Format(Text10.Text, "MM/dd/yyyy") & "'  aND   REMARKS='ND'" & ORGIN & " ORDER BY POD_NO"
    Set trackrs = conTrack.Execute(sqlstr)
    Last edited by loquin; 06-01-07 at 13:50.

  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    The reason is, that it times out.

    The reason why it times out?
    • Number of records
    • What fields are indexed
    • The order of where clauses might have an impact. SQL Server processes multiple WHERE clauses separated by Ands from Left to Right. Put the clauses which reference indexed fields before clauses which do not. That way, the substring clauses (which aren't indexed) won't be run as many times. Within a class (indexed or non-indexed,) place the clauses which most greatly restrict the data set first. This serves to most greatly reduce the number of records for any subsequent clauses. (Oracle is the reverse of this, BTW)
    • If you don't actually need the DISTINCT clause, don't use it.

    If all else fails, You may have to increase the value of the connection CommandTimeout property.

    Ref SQL Server Performance Tips
    Last edited by loquin; 06-01-07 at 14:21.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts