Retrieving Master/Detail Records

[garydbutler]

Hi,

I am trying to query approximately 600,000 master records each with detail
records in 4 other tables. I am dumping the contents of these master/detail
records into an XML file daily and the current approach - brute force - is
pretty slow as you might imagine.

I'm currently using an odbcDataReader on the master records, looping through
the datareader and querying the master's child records using the same data
reader approach. I am afraid to go with the dataset approach because of the
size of the tables but for all I know that may be the way to go...

Anyone have a good strategy for this scenario?

Thanks in advance,

Gary

[Pak-Ming Cheung - MSFT]

Could you give us more data about how many associated records (on average) of
those "child table" per each record in master table?

Also, how large is a row in master / child table? Any primary / forward key
dependencies? Are all 4 childs tables linked with a primary key in the master
table?

Could you share some SQL statements about retrieving those master / child
tables?

What kind of backend database server are you using? Different database
technologies may have some specific API for efficient retrieval (for example,
"bcp" in SQL Server).

Thanks,
Ming.
WDAC Team, Microsoft.
P.S. We recommend customers to use the forum to ask questions in the future,
where you can obtain a faster response (Forum is at:
http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/threads/)