SQL query comparisons, Do I need an array?

[SuperComputing]

I have 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. There has to be a way to do it with just one. 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 that I can:
Select my customers from the local table
Select my customers from the remote table
Compare the results locally
Display the differences?

Thank you in advance.

 

[jmeckley]

Is there a way that I can:
Select my customers from the local table
Select my customers from the remote table
Compare the results locally
Display the differences?

of course the question isn't whether it's possible, but how can the problem be solved given the business and technical constraints.

the first thing that would improve preformance is solving the select n+1 problem. instead of querying the "2nd" database for a single row at a time querying for a set of ids.

some other things which will help solve the problem:
1. query a page of data instead off all the rows at once
2. run the process "off-line" and allow the user to review the results at later. depending on the amount of data this may be required.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[SuperComputing]

No, the question isn't whether it's possible. But neither did I want help optimizing my already inefficient code. So let me rephrase.

Will someone please be gracious enough to provide me with some example code snippets that I can insert into my existing asp page with English layman explanations that will guide me in the proper direction of how to compare a single local query with a single remote query in the local session and display the differences of that comparison?

 

[ca8msm]

I think you should post your question in the forum333 forum, as ASP.NET is entirely different to classic ASP so our examples wouldn't be of any use to you.

Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[SuperComputing]

Thank you! I will.