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:



Regards,
Matt

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.

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