SQL Query vs Query Node

The advantage to using the SQL query is that you're not limited to Lawson's predefined relationships on the tables. Lawson really meant for the SQL query to be used on tables outside of the Lawson system - as an integration tool. Of course, it works both internally and externally, as you know.

It's really a matter of preference.

I do prefer SQL. I just need to to remember to use RTRIM and alias on strings.

Thanks,
Jude

I think it also depends on what you're going to do the with the data you are querying and the volume. Both are iterative nodes and have overhead just in the iterations themselves. I have sometimes opted for the much more difficult task of writing a very involved SQL query, and then wrapping it in a System Command to have it dump out a csv file. I've seen anywhere from a 10-50x performance difference vs iterations of thousands of records.

But generally I think for basic queries its your comfort level.

Hi Jude -

Your choice really depends on whether you're doing an ETL type thing OR needing to integrate the SQL data iterations into the FLOW itself to execute business logic. Most clients I've worked with need to boost their JVM Heap to handle the overhead from the SQL Iterations - so I recommend really treating the PFLOW more like a Web Presentation Layer and do all your heavy lifting on the DB itself using SP's or Functions or like George recommends with a Command where possible for ETL stuff. I've also witnessed the SQL Node treating SQL Alias a bit weird too - they wouldn't execute in the PF SQL Node, but the same code was fine in Enterprise Manager (on SQL 2005 / SQL 2000 concurrent flow).

RC

Thanks for the advice guys.

How did you get the SQL node to dump to a csv file?  I always have to put a file access node in between to write the data our to a file or msg depending on what I need to do the the results.

Amy - that's exactly how you do it. You have to write each record returned by the query.

You can also run the sql from a command line and provide an option to have the sql output to a csv - actually faster than the iterative approach. Then you can manipulate the csv file from inside the flow.

The way I look at it is, I use the SQL query for more complex joins and find it is faster. Now for a simple join and a low volume data collection like a record at a time. the DME query is fine and easier to set up.