read xml file

[sineados]

Hi

Apologies if I am not in the right forum but I have a vb6 application which inserts an xml file into the database record by record. I make up the insert sql stmt and do an execute. however it takes 4 minutes to insert 4612 records. I had originally gone it using .NET and it takes 37 seconds to read the data from a dataset into the database.

Is there any way I can speeed up the vb code without having to return to .NET?. i have googled and found examples of reading it into recordsets but I have no control over the xml file, so they don't seem to work or I am missing something

thanks,
Sinead

 

[gmmastros]

What database? Access, SQL Server, etc...

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sineados]

Its SQL Server. I actually got it sorted though. thanks. My code had been reading the XMLDocument 4000 times and just taking the current index. I now use SelectNodes and iterate through that and it takes no time at all.

thanks for the reply anyway

 

[dilettante]

Hah! Good one.

Wish I could say I've never pulled a stunt like that in my life.

This is the sort of thing where you need either a second set of eyeballs or barring that a simple code profiler. Large scale snafus like yours will jump right off the screen.

Where practical maybe even try the profiler first. I find it saves me taking up colleagues' time on simple performance related goofs.

 

[gmmastros]

I've been out of town for the last couple of days, so my apologies for not responding sooner.

With Microsoft SQL Server, you could write a stored procedure that takes XML Data (as an input) that will insert the data for you. This would certainly be faster than inserting data record by record. I suspect that the performance is acceptable for you (with the 4600) records. Using the method described in the linked thread, performace should improve even more. I've used this technique many times and have always been pleased with the performance.

thread220-1249932

For others reading this thread... this applies to SQL Server only.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom