XML Performance?

[jpritchard]

XML and performance

I am struggling with the performance of an xml application and would like somebody more experience than myself to give a view/perspective etc.

I have been asked to help in moving an application from our dev area onto our production servers. The app was written outside my control and done via an agency. It is a jscript/asp/xml app - webpages posting to asp-scripts and data being recorded on local .xml files. - no probs so far.

At this point we couldnt the app to our prod servers. We have rules that the webserver have no writeable directories. The app breaking at this point as it updates the local .xml files.

(we did some work using virtual directories but this is dis-allowed as well.)

So here's where I jumped in - I did some hacking into the app - I decided that I would move the data off the server on our main oracle DB. I do not (politically I cannot) want to re-write the app - I therefore elected to create a table in the following format

tab-key - 20 chr
tab-sequence - number
tab-data - 1024 chr

when the xml structure is to be saved, I want to cut it into blocks 1K and dump it

<bigdata><firststuff>........morexml.......</laststuff></bigdata>

would be saved as
database -

key - seq - data

bigdata - 0001 - <bigdata><firststuff>..
bigdata - 0002 - ....morexml............
bigdata - 0001 - ..</laststuff></bigdata>
.
.

and on reading the reverse happens.

what I did is intercept all the "doc.loadXML(strxml)" and run my load from db. On the saves, intercept all the "doc.save(Server.MapPath("data/users.xml")".

Got it all working and all ok - all except the performance.

Its a dog - my code seems to be fast enough but the speed across the network/dbase seems to be the main part of the response.

My question is this - if you are writing updating xml application, do most people have the .xml file local on the server.
(from a security position - are letting the iuser_ account update your server? - isnt this bad practice - my organisation dis-allows it fullstop...)

My solution of lobbing in over in a db is too slow as the whole xml message has to be recalled and rebuilt then given to the ms-xml parser etc (microsoft.xmldom).

I would like to limit the size of the xml data to speed things up but understand that whole structures are loaded, processed then saved.

Eg - if using local xml files - these holding employee data - the following would happen

- user enters the update employee function (say - emppost.asp runs....)
this script does

1- doc.loadXML("data/emp.xml")
2- uses the object to add/change the relevant employee structure
3- doc.save("data/emp.xml")

ok - works - what happens if the file holds hundreds of employees - we are loading & saving now a very large file just to do a small change. For me this is ok if local to the server and the file is updateable. Xml over to the db is just to slow.

My only thought is *not* to use xml structures and create a set of properly structured tables where I can just add/change the employee data as per emp key (one by one).

does xml scale (at least when using the ms objects)? - im not sure!

It seems a retrograde step to move from xml or am I reading it wrong.

any input would be greatly appreciate (hopefully I am missing something obvious and my performance prob easily fixed??? xml isnt a strong area for me - my background is s.a.p)


cheers A.J.P.
Senior AP

[Cudabean]

As I understand the problem,
You've got an ap that is not allowed to write to the server, but it needs to write this XML, but when directing the ap to write to another server that it does have access to, for some reason causes the performance to drop unacceptably.

Here's an idea, create a simple Oracle table that just has a CLOB or something like that and put it on the server where the ap lives and have it use the SQL table as it's "flat file".

cudabean

[jpritchard]

hi,

me again - pulling out my hair in stress

interesting idea - created a table using a clob

create table master_elearning_control2
(elrn_id varchar2(12),
elrn_data clob,
elrn_datime varchar2(14),
constraint elrn2_id_key primary key (elrn_id)
);

elrn_data to hold the xml structure.... - using this i encountered the following message

"ORA-01704: string literal too long"

had a look across various newsgroups and this seems a well known problem - using ado on oracle, it look like the max size U can pass is 2000chr with oracle 7 (& 4k with oracle 8). event though a clob/blob etc can be 4 gig.

see ms knowledgebase http://support.microsoft.com/default...;EN-US;q233515

I think I could try stored-procedures and passing the longfield etc etc but am unsure that this will really get me anywhere fast....

Like you suggested I would love to put the data on the same server as the webserver - I cannot - Our DB is oracle running on an IBM-SP2. (our SP2 being in 3 stacks (8 foot high - huge!) and each stack being a rack of rs/6000's all running Aix....). Our webservers are multi-processor prolients running IIS.... We connect via the std oracle odbc drivers....

The db should handle the work, ditto IIS - I just think its the bandwidth across our lan and I expect to much of it?

The problem is that datasize and as xml structures are always whole-ly loaded and whole-ly saved, this causes the larger than necessary load on the lan etc.

I was brought into this a bit late but am now going to recommend that we do now work with and agency that produces application based on XML. This seems ludite and a retrograde step to me....

Thanks for the comments. anything else would be appreciated..........

AJP

[Cudabean]

Had another idea--a workaround on the same vein.

Set up the database so that instead of a clob, just have a varchar and call it line and then add the multiple lines comprising your XML. Throw in a line number so that when you retrive it you can order your results by the line number. A little extra work, true, but as long as none of your lines exceed 2000 characters, at least you'll know that you've probably got a direct path to a solution.

cudabean