recursive result fetching

, ,
1

Hi!! I’m a very new user of Webmethods6.0
I want execute a series of sql queries from single table and append their result into a single variable…
Can someone tell me the way to do that???

2

Not sure why you wouldn’t do this with a more sophisticated SQL statement that uses a union operator to join multiple result sets.

You could write a separate adapter service for each that returned the same columns and write a Flow that called each adapter service using something like pub.list:appendToDocumentList to combine the result sets. I would offer that this is a bad idea, since you are doing something in Flow that appears to be better done in SQL.

Mark

3

Thanks for the quick reply… i tried with the SQL queries and it’s working too but it will put too much pressure on the data base and the fetching might become slow… so can u please give bit more details on the other option

4

This doesn’t make sense to me. The DB is the tool to do this and you’re saying that multiple selects from IS will be “better?”

“…fetching might become slow.” In testing, has it actually become slow? IMO, you may be optimizing your solution too soon–eliminating “anticipated” bottlenecks doesn’t work out very often.

If you decide to not use the DB to do this work (would strongly encourage using the DB) then here are the high level steps:

  • Select your first data set
  • Save it to a temp var
  • Select your next data set
  • Save it to another temp var
  • repeat as needed
  • Append all the result sets to a new list

If you implement both and measure the execution time, my guess is that the single DB query using join/union/whatever works will be faster.

5

Database management systems are optimized to join related data very efficiently. Doing this in Flow is possible, but much more resource intensive not to mention harder to design, code test, and maintain. If I were your manager and you brought me a cobbled together solution to join relational data in IS instead of using the database, I’d send you back to rewrite it.

Mark

6

Rob and Mark,

I have a question for you that I think is in line with the discussion taking place in this thread. If you think I should break this out into another thread, let me know and I’ll do that.

Suppose I have a source system from which I have to retrieve, let’s say, order data directly from the database. Now, suppose that the order data is divided into two tables: a header table (call it HDR) and a detail table (call it DTL).

Would you say that retrieving the data with one SQL statement, such as SELECT * FROM HDR a, DTL b WHERE a.orderId = b.orderId, is preferred over selecting all headers first (ex. SELECT * FROM HDR) and then selecting the details header by header (ex. SELECT * FROM DTL WHERE orderId = )?

I’d think it is because databases are built to efficiently join data. Plus, it eliminates the overhead of having to issue multiple queries versus one. However, what if the header consists of a significant amount of data and/or there are several detail records for each header? Then, executing the join above could potentially have a serious impact on memory consumption given that the header information will be duplicated for each detail record within the result set.

I’ve seen both approaches implemented, so I was wondering what your opinion was on the matter.

Thanks,
Percio

7

I too have seen both implemented. Which is “better” depends on the specific situation.

My ideal scenario is that IS isn’t doing database interaction at all and is either querying an app for XML data via HTTP posts (or SOAP calls) or exposes a service for an app to push the XML data to. How the app manages its data and provides it in XML form becomes an item for the app team to manage rather than the integration team.

Alas, things can’t always be done that way so we end up with the integration layer having more knowledge about a database than it should. If DB interaction is necessary, and I’m faced with a scenario you describe I’d use the single SQL statement approach as the default approach, switching to the multiple select approach if necessary (memory concerns, performance, complexity, etc.).