• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Performance of a stored procedure (XML Vs List)

 
roshan thomas
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I am in the process of writing a stored procedure that is expected to return thousands of record. Can you please suggest whether the results returned as a list is better in performance or an XML being returned is better? Will we run into any performance issue because db is trying to generate a big xml response?

The result currently looks like following in a list



XML of following structure is the one I have in mind.



Please help me decide.

Thanks,
Roshan
 
Wendy Gibbons
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
welcome to the Ranch Roshan. this is just my best estimating but the biggest bottleneck is often sending the data down the wire.
So the less data you send the better, the xml is a lot more data than the raw results.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Wendy is right: you should try to keep the volume of data being passed around as small as possible, so XML is probably not a good idea here.

It might also be worth asking why you are returning thousands of records from the database anyway.

If you are just looking for summary data, then obviously you should simply fetch the summary data from the database and let the database do all the hard work of creating the the summary - that's what it's designed for.

If you are doing extra processing on the returned data, is this the kind of thing that could be done in a stored procedure instead, to reduce the volume of data you have to send back from the database?

This will depend on your requirements and your local architectural/design choices, but having made the decision to use stored procedures, it is worth trying to maximise the benefits you gain from them.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic