#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Posts
    5
    Rep Power
    0

    Querying on multiple databases


    Any help on this would be appreciated.

    I am developing a web-based control panel for a university that has student applications in both a SQL Server Database and a CARS (Informix) database.

    The SQL data was obtained from students applying for admission online. The CARS data would be that students lead information, etc for record keeping.

    My webapp is going to use data from both SQL and CARS. Each student record in SQL has a RowID, and when a student applies online, pieces of thier information gets loaded into CARS along with the SQL_ID (Which is the RowID of the record in SQL)...this will alow us to tie the records together.

    Ok now...thats the background...here's the problem.

    In my ASP page that displays the students that have applied, I want to grab ALL the students from SQL that have a SQL ID in CARS.

    I did the following...

    1. Setup a connection and recordset object to SQL.
    2. Do-Looping through the records, and checking each SQL
    RowID by doing another SQL Query on the CARS Informix database.
    3. If the recordset is EOF, then I want to immediately move to the next SQL record, if the SQL_ID is found in CARS, it will output the student data in the HTML Table.

    Problem is, it's taking so long the script is timing out....any suggestions?

    Heres the main part of the code:
    <%
    Sql = "SELECT * FROM AdmissionApplications ORDER BY SignedDate DESC, Stu_LastName ASC"
    RS.Open Sql, CN, 1, 3

    Do Until RS.EOF
    Sql = "SELECT COUNT(sql_id) AS count FROM lead_rec WHERE sql_id = " & RS("ID")
    Set RS1 = CARScn.Execute(Sql)
    If RS1("count") <> 0 Then

    %>
    HTML TABLE DATA
    <%

    End If
    RS.MoveNext
    Loop
    %>

    Regards,
    Matt
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2001
    Location
    NJ
    Posts
    428
    Rep Power
    17
    I would imagine its pretty slow. How many records are you dealing with? You can always extend the server timout.

    ' Set timeout to 10 minutes
    server.scripttimeout = 600

    are you sure you code is working good? why not stop it after a hundred records or so and make sure its acheive the appropriate effect.
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    NJ, USA
    Posts
    11
    Rep Power
    0
    2 ideas:
    Your actual results may vary depending on the size of data, so try both to see if they are worth it.

    1)
    You could use all of the results of query 1 to populate a temporary table of student id's in database 2, then query database 2 using the merged data. This may be better in the reverse direction too, but I'll leave it to you to work out.

    2) Use an IN clause in the second query to pull a record set of all matches discovered in query 1. I have seen problems with long lists of "IN" members so beware.


    Finally, If the databases have stored procedures or views, you may gain some improvement there too.


    Ray

IMN logo majestic logo threadwatch logo seochat tools logo