>Hi Dan,
>
>>snip<
>>

>>
>>Select RampDrillDown
>>Set Order To RampCode
>>Seek "1"
>>
>>COPY FIELDS Ramp, Vendor, VendCity, VendSt, HourCount ;
>>     TO tRampDrillDown ;
>>     WHILE RampCode="1" NoOptimize
>>
>>

>>
>>The NoOptimize is the key. Without the NoOptimize, this same COPY takes about 20 to 25 seconds. In this case, for whatever reason, optimization fails.
>
>I'm afraid, i don't understand. The WHILE clause should prevent optimization at forehand so adding the NOOPTIMIZE clause should not make a difference. How long does it take with the NOOPTIMIZE clause. Please give me more information about your solution; I'd really like to know whats going on here.
>
>Walter,

You may very well be right. The tests where I was getting such a variation in speed was the dual P4 which I have not reliably been able to get it to release its cache. It's also getting hit every so often by webhits so the speed is variable.

On my machine with a much watered down version, I was able to bring it down to approximately 8.5. But I get the same speed with the SQL.

Whenever I copy 20000 records from a table, that is .5 gig with 1.2 million record, it is almost instantaneous. But once I put an index on that table it slows dramatically.

Because I am able to pre-digest this information for our web site, I have since come up with a much better solution, though it is very x-base. As I mentioned, there is a summary table which drills down to this detail table. I can pull only the information I need from the .5 gig table. This makes the size of the table about 108 mb. After populating the RampCode field, I place the table in RampCode order. I then copy this out to another table. Now that table is in RampCode order "naturally".

In the link( from the summary to the drill down) I know the RampCode and the number of records that I need from the detail. The code to get the records now looks something like this.

LParameter tcRampCode, tnRampRecords

This.OpenTable( "RampValues" )

Select RampValues
Locate For RampCode = tcRampCode

If Found()
    lcCopyCmd = ;
        [COPY FIELDS Ramp, Vendor, VendCity, VendSt, HourCount ] + ;
        [     TO tRampDrillDown ] + ;
        [     Next ] + AllTrim( Str( tnRampRecords ) )
    &lcCopyCmd
Else
    Response.WriteLn( "Unable to locate RampCode: " + tcRampCode + " in " + ;
       Program() + "." )
EndIf

This takes about .1 second on a one gig Athlon.