OpenXML Woes - 2Qs

Hi everybody.

I don't know if anyone can help me but I have two issues with SQL Server 2000 SP4 (version 8.00.2039 - Desktop Engine) running on W2K and W2K3. I'm also running SQLXML 3.0 (msxml2.dll version is 8.30.9530.0).

Is it me or is sp_xml_preparedocument a crippled fat dog that is blind?...not that I have anything against crippled fat dogs that are blind :)

In all the stored procs I have developed, I pass a text var as an input parameter and return an IStream to ADO (using an sqlxml provider) in COM+. All has been very well and fine...until the passed text parameter resembles a data object of any decent size.

The first error I was noticing was a "XML Parsing Error: not enough storage is available to complete this operation". Well, I thought I would debug logically in a step fashion and just prepare the doc first and then do a return and then do a return on the next segment of code to find out where the issue is. I was amazed to find that sp_xml_preparedocument is taking 7 seconds to load a simple 1MByte text input var and around three minutes to load a 7 MByte file.

I believe these long load times are causing issues with transaction timouts etc so I thought I would try to solve the speed issue with sp_xml_preparedocument and then see if the "XML Parsing Error" continues.

So, my first question is:

Should sp_xml_preparedocument take 7 seconds to load a 1MByte text variable and nearly three odd minutes to load a 7 MByte file? Surely there is something wrong somewhere?

I'm also running these tests on two machines - one is 2 GHz and the other is 2.4 GHz P4's.

Cheers and thanks for any info.

ErronHaven't got much experience with it, but did notice the non performance of this while running some test a few monhs ago. Should be much better in SQL 2005, but I haven't tested that at all, so can't share any experiences on that with you ... yet.

Gr,
Yveau|||Thanks Yveau for the response.

If anyone else has any info or optimization tips, it would be muchly appreciated.

Cheers

Erron