Re: Nested begin-select blocks

[Ray Ontko <rayo@ONTKO.COM>]

John,

Although SQR is VERY efficient at doing procedure calls, your underlying
database may not be so efficient at parsing and opening queries as it
is at doing joins.  In general, I would lean toward the JOIN/BREAK
approach if you're worried about performance, but use a NESTED
approach if the data volumes are relatively small.

My experience is that if there are LOTS of master records, each with
a FEW details, it's better to do a join and break on the master.

If you have relatively FEW master records, each with MANY details,
you can generally save yourself the additional complexity of coding
the break.

Some other things to consider:

1) Network Traffic.  If your database and your SQR program are on
different boxes, and if you have LOTS of duplicated master data
for each detail record (as a result of doing the join), then
you may want to steer clear of the join approach.

2) Indexing.  I'm assuming that you have appropriate indexes
on the detail data to launch a separate query of the detail
table for each master record.  If your query is bizarre enough
that you don't have an appropriate index into the detail data,
a join approach might be better.  In this case, it's often
reasonable to create the index.

Your mileage may vary.

Ray

> Hi all.
>
> I'm writing a couple of reports that have nested begin-select blocks i.e..
>
>         begin-select
>         job_numbers
>                 !for each job number call procedure to select job details
>         from
>         some_table
>
> The details from each job number will make up individual *.lis files.
>
> I was just wondering if this would cause a performance issue and whether I
> would be better off joining the two select blocks together and breaking on
> job number to print out the individual details.  In terms of readability and
> ease of programming I find the nested route easier to develop.
>
> I would appreciate it if anyone could give me some advice (in regards to
> performance) based on prior experience.
>
> Thanks.
> John Milardovic
>

----------------------------------------------------------------------
Ray Ontko       |  Ray Ontko & Co  |  "Ask me about joining our merry troupe."
rayo@ontko.com  |  Richmond, In    |  See us at http://www.ontko.com/