1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

SQL query taking too long in an asp search

Discussion in 'Software Development' started by khollow, Aug 3, 2004.

Thread Status:
Not open for further replies.
  1. khollow

    khollow Thread Starter

    Joined:
    Aug 3, 2004
    Messages:
    1
    Hello. I am fairly new to ASP and SQL. I have created a search against a sql database that is supposed to have 3 different fields in which the user can search by. After debugging several errors I have gotten the search to work but now I have two problems. 1. The search takes too long so when I open the form it never comes up and then my server gets locked up. I need to know how to optimize my query. 2. I need to know how to make it so that the query doesn't run until the submit button is click and then the results will be posted back to that same page. When I run my sql query just the way it is in my code it pulls 2,926,679 rows. When I try to add TOP 5 to my select statement it was taking over 8 minutes so I had to stop it. Please help me!
    Please help. Here is my code:

    <%@ Language=VBScript %>
    <% option explicit %>
    <% response.buffer = true %>
    <!--#include file="adovbs.inc"-->
    <html>
    <head>
    <title>Medical Records MPI</title>
    <!--#include file="dbConnection.asp"-->
    *********Contents of dbConnection**********
    <%
    Dim adoCon
    Set adoCon = Server.CreateObject("ADODB.Connection")
    adoCon.ConnectionTimeout = 0
    adoCon.CommandTimeout = 0
    Dim adoConString
    adoConString = "Provider=SQLOLEDB.1;" & _
    "Network Library=DBMSSOCN;" & _
    "Data Source=xxx.xxx.xxx.xxx,1433;" & _
    "Initial Catalog=dbName;" & _
    "User ID=UserID;" & _
    "Password=pwd;"
    *****************************

    adoCon.Open adoConString

    %>
    *****************************
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
    <link rel="stylesheet" href="/main.css" type="text/css">
    </head>

    <body bgcolor="#FFFFFF" text="#000000">
    <%

    Dim sql
    Dim adoRS
    Dim T1
    Dim T2
    Dim T3



    T1 = Request.Form("PatNum")
    T2 = Request.Form("PatName")
    T3 = Request.Form("PatSocial")


    sql = "select pt1.UnitNumber, pt2.Name, pt2.UniquePublicIdentifier, pt2.Address1," &_
    "pt2.Address2, pt2.City, pt2.State, pt2.PostalCode, pt2.Telephone, pt2.BirthDateTime," &_
    "pt2.MaidenOtherName, pt2.MothersName, pt2.PatientID, pt3.VisitDateTime, pt3.VisitType,"&_
    "pt3.VisitAccountNumber, pt3.Confidential, pt3.LocationID, pt3.DischargeDateTime," &_
    "pt3.DischargeDispositionID, pt3.ProviderID, pt3.Reason " &_
    "from MriPatientUnitNumbers As pt1 " &_
    "Inner Join MriPatients pt2 on pt1.PatientID = pt2.PatientID " &_
    "Inner Join MriPatientVisitEvents pt3 on pt1.PatientID = pt3.PatientID where (1=1)"

    Set adoRS = Server.CreateObject("ADODB.Recordset")
    adoRS.open sql, adoCon, adOpenStatic, adLockOptimistic

    if T1 <> "" Then
    sql = sql & " AND T1="'&T1&'""
    end if
    if T2 <>"" then
    sql = sql & " AND T2="'&T2&'"
    end if
    if T3 <> "" Then
    sql = sql & " AND T3="' &T3&'"

    end if



    %>



    <table width="100%">
    <tr>
    <td colspan="7" class="subHeading">Medical Records</td>
    </tr>
    <tr>
    <td colspan="7" class="subHeading">&nbsp;</td>
    </tr>
    <tr>
    <td colspan="7">
    <hr color="cccccc">
    </td>
    </tr>
    <tr>
    <td class="subHeading2" colspan="7">Patient MPI Form</td>
    </tr>
    <form name="form1" method="post" action="MedicalRecords/MedicalRecords.asp">
    <%
    Do While adoRS.EOF = False
    %>
    <tr>
    <td class="subHeadingBlue" colspan="2"> Patient </td>
    <td colspan="2">
    <input type="text" name="PatNum" value="<%=adoRS("UnitNumber")%>">
    </td>
    <td width="83" class="subHeadingBlue">Name</td>
    <td colspan="2">
    <input type="text" name="PatName" value="<%=adoRS("Name")%>">
    </td>
    </tr>
    <tr>
    <td class="subHeadingBlue" valign="top" colspan="2">Social Security</td>
    <td valign="top" colspan="2">
    <input type="text" name="PatSocial" value="<%=adoRS("UniquePublicIdentifier")%>">
    </td>
    <td valign="top" class="subHeadingBlue" width="83">Address</td>
    <td valign="top" colspan="2">
    <p>
    <input type="text" name="PatAddress" value="<%=adoRS("Address1")%>">
    <br>
    <input type="text" name="PatCity" value="<%=adoRS("City")%>">
    <input type="text" name="PatState" size="2" value="<%=adoRS("State")%>">
    <input type="text" size="10" name="PatZip" value="<%=adoRS("PostalCode")%>">
    </p>
    </td>
    </tr>
    <tr>
    <td class="subHeadingBlue" valign="top" colspan="2">Birthday Date</td>
    <td valign="top" colspan="2">
    <input type="text" name="PatBDate" value="<%=adoRS("BirthDateTime")%>">
    </td>
    <td valign="top" class="subHeadingBlue" width="83">Telephone</td>
    <td valign="top" colspan="2">
    <input type="text" name="PatTelephone" value="<%=adoRS("Telephone")%>">
    </td>
    </tr>

    <tr>
    <td class="subHeadingBlue" valign="top" colspan="2">&nbsp;</td>
    <td valign="top" colspan="2">&nbsp;</td>
    <td valign="top" class="subHeadingBlue" width="83">&nbsp;</td>
    <td valign="top" colspan="2">&nbsp;</td>
    </tr>
    <tr>
    <td class="subHeadingBlue" valign="top" colspan="2">Maiden/Other Name</td>
    <td valign="top" colspan="5">
    <input type="text" name="Maiden" size="40" value="<%=adoRS("MaidenOtherName")%>">
    </td>
    </tr>
    <tr>
    <td class="subHeadingBlue" valign="top" colspan="2">Mother's Name </td>
    <td valign="top" colspan="5">
    <input type="text" name="MotherName" size="40" value="<%=adoRS("MothersName")%>">
    </td>
    </tr>
    <tr>
    <td class="subHeading2" valign="top" colspan="7">Patient Visit Information</td>
    </tr>
    <tr>
    <td class="subHeadingBlue" valign="top" width="71" >Date</td>
    <td valign="top" width="60" class="subHeadingBlue">Type </td>
    <td valign="top" class="subHeadingBlue" width="130" >Account#</td>
    <td valign="top" class="subHeadingBlue" width="91" >CON </td>
    <td valign="top" width="83" class="subHeadingBlue">Location</td>
    <td valign="top" width="78" class="subHeadingBlue">Dis Date</td>
    <td valign="top" width="88" class="subHeadingBlue">Dis Disp</td>
    </tr>
    <tr>
    <td class="subHeadingBlue" valign="top" width="71">
    <input type="text" name="VisitDate" size="12" value="<%=adoRS("VisitDateTime")%>">
    </td>
    <td class="subHeadingBlue" valign="top" width="60">
    <input type="text" name="VisitType" size="6" value="<%=adoRS("VisitType")%>">
    </td>
    <td class="subHeadingBlue" valign="top" width="130">
    <input type="text" name="VisitAccount" value="<%=adoRS("VisitAccountNumber")%>">
    </td>
    <td class="subHeadingBlue" valign="top" width="91">
    <input type="text" name="VisitCon" size="6" value="<%=adoRS("Confidential")%>">
    </td>
    <td class="subHeadingBlue" valign="top" width="83">
    <input type="text" name="VisitLocation" size="12" value="<%=adoRS("LocationID")%>">
    </td>
    <td class="subHeadingBlue" valign="top" width="78">
    <input type="text" name="VisitDisDate" size="12" value="<%=adoRS("DischargeDateTime")%>">
    </td>
    <td class="subHeadingBlue" valign="top" width="88">
    <input type="text" name="VisitDisDisp" size="12" value="<%=adoRS("DischargeDispositionID")%>">
    </td>
    </tr>
    <tr>
    <td class="subHeadingBlue" valign="top" colspan="2" >Doctor</td>
    <td class="subHeadingBlue" valign="top" colspan="5" >Reason For Visit </td>
    </tr>
    <tr>
    <td class="subHeadingBlue" valign="top" colspan="2">
    <input type="text name="VisitDoctor" value="<%=adoRS("ProviderID")%>">
    </td>
    <td class="subHeadingBlue" valign="top" colspan="5">
    <input type="text" name="VisitReason" size="90" value="<%=adoRS("Reason")%>">
    </td>
    </tr>
    <tr>
    <td class="subHeadingBlue" valign="top" colspan="2">&nbsp;</td>
    <td valign="top" colspan="5">
    <input type="submit" name=B1 value="Search">
    </td>
    </tr>
    <%
    adoRS.movenext
    Loop
    %>
    <%
    asoRS.close
    set adoRS = nothing
    adoCon.close
    set adoCon = nothing
    %>
    </form>
    <tr>
    <td class="subHeadingBlue" colspan="2">&nbsp; </td>
    <td colspan="5">&nbsp; </td>
    </tr>
    </table>
    </body>
    </html>
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    You need to either have the form on another page or have the query not fire until the submit button is pressed. From the looks of the page you have it should not run but you might want to put in a conditional statement prior to your Query that checks to see if the submit has been pressed and if it hasn't it should do nothing. I see that your connection timout is also set to 0 and you are closing objects that are not even on your page "asoRS" did you hard code the criteria "where (1=1)" just to see what records are returned?
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/257515

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice