Compare sql queries locally, display results

[SuperComputing]

I have a asp page that queries a local psql database and a remotely stored mysql database. They both have a 'Customer' table with a 'CustomerNumber' column.

I am currently comparing each customer in the local table with the customers in the remote table, and if the remote customer is missing, I list that customer from the local table. Like such:
'abbreviated code'

Customers = "SELECT CustomerNumber, [etc..] FROM Customer WHERE [...] ORDER BY [...]"
Set rsCustomers = LocalConn.Execute(Customers)

do until rsCustomers.EOF
   compare = "SELECT CustomerNumber FROM Dealers WHERE CustomerNumber = '" & Replace(rsCustomers("CustomerNumber"), "'", "''") & "' "        
   Set rscompare = RemoteConn.Execute(compare)
    IF rscompare.EOF = TRUE THEN
     for each x in rsCustomers.Fields
       Response.Write(x.name)
     next
    END IF
rsCustomers.MoveNext
loop

As you can see, this sends hundreds of queries to the remote server. There has to be a way to do this with just one remote query. When under load, or certain times during the day, the operation can time out, it takes forever as it is. If I do a simple, 'select all from remote and display' it's fast.

Is there a way to:
Select my customers from the local table into a recordset
Select my customers from the remote table into a rs
Compare the results locally
Display the differences?

Example code snippets with layman explanations appreciated.
Thank you in advance.

 

[gmmastros]

I suggest you read this: thread222-1563984

It talks about VB6, but the concept is similar and the code should look familiar.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ggriffit]

if you have the customer numbers from the first query you can just use the IN clause in SQL, or a join if they are lots :

1/ Select all customers numbers from local db into RSLocal
2/ Create a Comma separated list of the customer numberss from RSLocal call it sCustIDs
3/ Run the following SQL on the remote db :

SELECT CustomerNumber FROM Dealers WHERE CustomerNumber not in ( sCustIDs )

e.g.

SELECT CustomerNumber FROM Dealers WHERE CustomerNumber not in ( 1,2,3,5446,345435 )

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[SuperComputing]

Thank you both so much for your help. I read most of the evening on ADO filters. Here is what I am using now. It has cut the display time of the page from 1 minute 5 seconds to just 1-2 seconds. And since this table should never have more than 2500 records, I'm happy with the performance. If you see anything that could be tweaked, or that is just plain bad practice, please let me know.

Dim rsLocal, rsRemote, str1

Set LocalConn = Server.CreateObject("ADODB.Connection")
LocalConn.Open = CONN_STRING_1
Set rsLocal = Server.CreateObject("ADODB.recordset")

Set RemoteConn = Server.CreateObject("ADODB.Connection")
RemoteConn.Open = CONN_STRING_4
Set rsRemote = Server.CreateObject("ADODB.recordset")

rsLocal.Open "SELECT CustomerNumber, [etc..] FROM Customer WHERE [...] ORDER BY [...]", LocalConn
rsRemote.Open "SELECT CustomerNumber FROM Dealers ORDER BY CustomerNumber", RemoteConn

rsLocal.Filter = "CustomerNumber <> " & rsRemote.GetString(,,," AND CustomerNumber <> ","''") & "''"
str1 = rsLocal.GetString(,,"</td><td>","</td></tr><tr><td>","&nbsp;")

<table border="1" width="90%">
  <tr>
    <td><%Response.Write(str1)%></td>
  </tr>
</table>

Set str1 = Nothing
Set rsLocal = Nothing
Set rsRemote = Nothing
LocalConn.Close
RemoteConn.Close