Manual on-breaks...an interesting puzzle

[John B Wells <John.B.Wells@ALLTEL.COM>]

     Hello.  My first post to the group.  Let me tell you, finding this
     group was a lifesaver.

     Anyway, on to my problem.  I'm running SQR from a unix env. against a
     Sybase Adaptive Server 11.5 database.

     My problem is this...we are running a SQR report against a table of
     1,200,000 records joined with a table of over 31,000,000 records.  The
     query looks something like this:

     begin-select

     b.AreaCd
     b.BillingNbr
     b.Name
     b.CompanyName
     a.NodeNbr
     a.Amt
     a.Amt30
     a.Amt60
     a.Amt90
     a.Amt120
     a.Amt150
     a.Amt180
     b.CreditClassCd
     b.CustTypeCd
     from small_table a, big_table b
     where a.acctnbr=b.acctnbr
     and (a.Amt     != 0 or
             a.Amt30   != 0 or
             a.Amt60   != 0 or
             a.Amt90   != 0 or
             a.Amt120  != 0 or
             a.Amt150  != 0 or
             a.Amt180  != 0)

     order by b.AreaCd,
      b.BillingNbr,
      a.Amt180 desc, a.Amt150 desc,
               a.Amt120 desc, a.Amt90 desc,
     a.Amt60 desc,  a.Amt30 desc,
               a.Amt desc


     We have two break fields defined (AreaCd Level=1, BillingNumber
     Level=2, both defined as After).

     The report was not designed by me...I am tasked with making it run
     more quickly.  As it is, runtimes are typically anywhere from 20-25
     hours.  This is, as you can guess, unacceptable.

     The odd thing is, I can run this query from Sybase itself and it will
     run in around 30 minutes.  It's only when I use SQR that run time
     jumps so much.

     Through a number of different tests I pinpointed the "order by" clause
     as the culprit causing this extreme runtime.  It makes a HUGE
     difference to run the report without this statement, so I decided to
     order the data before running the report and drop this statement.

     After testing a number of different index/optimizer hint combos, I ran
     the query manually, pulling all data into a temporary table and
     building a clustered index on AreaCd,BillingNbr.  This 'should' cause
     the records to be physically ordered by these two fields.  However,
     when I modified my report to hit the temp table and return records,
     they WERE NOT IN THE SAME ORDER.  Maybe I'm missing something here...

     My second attempt was to BCP the data (a utility delivered with Sybase
     used to create delimited flat files) out to a flat file and use SQR to
     read this data in order, effectively simulating a table read but using
     a file instead.

     This second method works like a charm, except for one small,
     infinitely important detail: the on-break option will not allow you to
     use before or after outside of a select paragraph.  Since I am reading
     in a file and not querying a table, I am between a rock and a hard
     place.

     I've tried to recreate break logic myself, as the following code
     demonstrates:

     !-------------------------------------------------------------------

     ! Set up break functions.

             if $billingnumber != $save_billingnumber !stored billingnumber

                     if #pass =1 ! Set this to 1 when loop has run throuh
                                 ! at least once
                             show 'Calling break for billingcyclenbr:'
                                $save_billingnumber
                             do AreaCdBreak ! Function that totals and
     resets values
                             do BillingNumberBreak ! Function that totals
     and resets values
                     end-if
             end-if


             if $areacd != $save_areacd
                     if #pass =1 ! Set this to 1 when loop has run throuh
                                 ! at least once
                             show "Calling break for market:' $areacd
                             do AreaCdBreak ! Function that totals and
     resets values
                     end-if
             end-if

     !-------------------------------------------------------------------

     However, this code for some reason doesn't always break on these
     fields.  I'm sure there's a logic error there, but after 72 hours with
     no sleep I can't see it.  I store the $save_areacd and
     $save_billingnumber at the end of the loop, so they are populated
     after one pass through.

     At this point, I'm perplexed.  My big questions are 1: why are the
     tables being pulled in out of order, when they are 'supposedly'
     physically ordered in the temp table, and 2: why ain't this break
     logic working.

     Anyone have any comments/suggestions?  I'd love to get any input on
     this matter.  Anyone else seen a query return out of order, or has
     anyone had to implement their own break logic in the past?!?!

     Thanks in advance!

     John Wells