>>>>I was looking over an old program the other day. There were a series of SQL select statements that looked pretty solid. However, before these statements there was a Set Relation To between the same tables that the SQL is being preformed on. Will this degrade the performance of the SQL since the record pointer for the second table is now moving each time the parent table record pointer is moved?
>>>
>>>Actually, in certain circumstances, I'm pretty sure it may make the SQL faster. Since Fox's SQL is so smart, it likes to think, but Fox is not a fast thinker, it's a fast worker. The more you tell it, the less it thinks and the more it works....(What the hell does that mean???)
>>>
>>>It means, Fox will create indices and relations on the fly, if they don't already exist and they are required by the SQL. If all the necessary indices and relations are already set, then Fox recognizes them quickly and utilizes them and skips over the 'thinking' part of the SQL. I KNOW this for a fact for indices, particularly IDX's which Fox doesn't know who the owner is unless you tell it. I THINK this is true for relations, but I've never done an official benchmark.
>>>
>>>Matt
>>
>>Fox likes to act, not think. Got it. Hey, my girlfriend says I'm the same way! ;) The following bit of code is the actual statement. I'm looking for ways to make it faster. One possibility I see is replacing the inline function with an SQL, but I'm not sure whether I would see any gains using an SQL select then a count. DLPOINTS is about 40K records or so. Any suggestions?
>>
>>Select ;
>> Novellid.NW_Team as Team,;
>> Ld_Product as Product,;
>> Ld_Booked, ;
>> Ld_Bdate, ;
>> LD_EstCost as Est_Cost, ;
>> Ld_CstOut as Act_Cost, ;
>> R_LTL( 'L', ld_Quote, ld_load ) as LTL, ;
>> LD_CstOut - LD_EstCost as Var_Cost ;
>> From Loads, Novellid ;
>> Where Not Empty(Loads.LD_booked) And ;
>> Between( Loads.Ld_Bdate, xFrom, xto ) ;
>> And ! InList( Loads.LD_Ctype, 'H','D' ) ;
>> Into Table (app_udir + 'Booked')
>>
>>
>>****************************************************************************
>>Procedure R_LTL &&@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
>>****************************************************************************
>>Parameter pSource, pltlQte, pltlLoad
>>
>>Private xArea, xRes
>> xArea = Select()
>> xRes = 00000
>>
>>if pSource = 'L'
>> Select DLPoints
>> Count to xRes for Qo_Quote=pltlQte and Qo_Load=pltlLoad And Qo_Resyn = 'Y'
>>Else
>> Select HDPoints
>> Count to xRes for Qo_Quote=pltlQte and Qo_Load=pltlLoad And Qo_Resyn = 'Y'
>>Endif
>>
>>if ! Empty( xArea )
>> Select ( xArea )
>>Endif
>>
>>Return xRes
>
>Mike,
>
>I wouldn't be surprised if your result set is wrong. Take the UDF out of the SQL and see if the number of rows returned is the same. UDF's that select a different work area play havoc with SQL-SELECTs.
>
>Try putting ld_Quote, ld_load and 00000 AS LTL into the select and put it in a temp table instead of a cursor. Then once the select is done... REPLACE LTL WITH R_LTL( 'L', ld_Quote, ld_load ) ALL
>
>HTH
>
>Matt

Well, as a matter of fact, we were getting some odd data. Thanks for the info! I'll try it right now.