Solved

Interesting Challenge - retrieving large remote recordset quickly

Posted on 2004-09-13
4
251 Views
Last Modified: 2008-03-17
ok first what i'm trying to do - i have one server with records that contain row addresses.  let's say it's a customer and row address 1234.  i also have another db server that contains the application and the actual row addresses.  here that address for that customer should be 1234 - sometime it's not, and the original server is pointing to the wrong one.  i want to identify these records.

the page needs to do this -
get records from server1
get records from server2
compare records and display results.

i have tried this varying ways which resulted in different speeds.  it's working pretty good now but only when the webserver running this is in the same building.  unfortunately my other 2 servers are often located elsewhere, elsewhere being in europe and australia, while the webserver is in illinois.

i figure probably the quickest way to do the compare now is with sql to 2 text file result sets from the servers, (which i haven't mastered yet either) - but getting those resultsets is where my problem lies.  i tried pulling down the resultset and writing it to text, iterating with movenext through my rs.  it was taking forever, so it occured to me that i'm not pulling the whole rs to memory on my webserver, just one record at a time.  

i spoke to someone that recommend i try getting everything all at once as XML, and had high hopes  unfortunately i have some of those running in the background now, and writing that xml file seems to be taking just as long.  i opened my recordset and just did a .save after that, which seems like it may be just slowing streaming that line by line across the world as well.

i am hoping this program to handle up to 50k lines from each source.  each line is only 2 or 3 short fields.  i need to get this resultset into memory or a local file on my webserver to do the compare, i think best would be an sql connection and join onto 2 text files, but building them in a timely manner is really presenting quite a challenge to me.

i do not want to add/alter anything on the source databases, and i have high doubts that i am allowed to at all.  any ideas ??
0
Comment
Question by:taveirne
  • 2
  • 2
4 Comments
 
LVL 3

Expert Comment

by:wadalhag
ID: 12042670
Hi taveirne
If you use Saxon transformation engine for XSLT transformation. You can write a style sheet which interacts with both XML and databases.
The SQL extension supplied Saxon regarded to be the easiest, safest and the fastest for this type of jobs.

http://saxon.sourceforge.net/saxon7.8/sql-extension.html
   
You style sheet should connect to the two servers, store the results locally, then compare them and produce what you need    
0
 
LVL 2

Author Comment

by:taveirne
ID: 12042866
wadalhag, my problem really isn't doing the comparison or manipulation of data, it's doing the retrieval of it in a speedy fashion.  i originally thought the bottleneck was in the compare, but is in fact in the retrieval of the resultsets.  right now my working page iterates through one recordset and puts it into an array in memory, then iterates through the second and does a bin search in that array for the associated row, then matches the record.  takes ~70 seconds to do a report on 30k records, server in same datacenter, which is acceptable performance for this to me.  i think the compare can be made faster with an sql connection to 2 local text files and running a join on them as tables, i'd prefer CSV and the only reason i was trying to get the XML was to speed up my data resultset process, however this method does not appear to be any quicker.  in fact it may even be slower with the extra data that XML fluffs out the values with.  right now the best way seems to be to run the query on the source, save it to a file, and ftp it over to my webserver, which is not something i want to do myself (or automatically if i can help it)!
0
 
LVL 3

Accepted Solution

by:
wadalhag earned 500 total points
ID: 12042897

If you think this is the best method and your only concern is the automation then you should be able to create a batch file (*.bat) to do the job for you as follow
1-      run the query on the source
2-      save it to a file
3-      ftp it over to webserve
I use batch files for most of the jobs which require repeated tasks on the command line  
0
 
LVL 2

Author Comment

by:taveirne
ID: 12043062
i didn't want to go through that "fun" if i could avoid it.  a guy from p2p wrox forum actually suggested something that is working great for me - since both source servers are in that same remote location, this compares both and i just bring back the results on the webserver.  he recommended openrowset -

SELECT c.*, o.*
FROM Northwind.dbo.books AS c INNER JOIN
   OPENROWSET('SQLOLEDB','remote.server.com';'sa';'MyPass',
   'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname')
   AS o
   ON c.au_lname = o.au_lname

it's working great!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now