Hi Naomi,

Consider switching to a cdx (as much as possible or as you go along) with the next update. You can build the cdx and delete the idx's as a part of an update.exe The version of your app that you distribute in the update will contain the code with the changes to use the cdx where necessary. Only do a portion (those you absolutely need immediately) and perhaps you can do a gradual change through the next few updates to get as many switched over as possible.




>>Naomi,
>>
>>do you mean that the application uses separate IDX files? If so, create identical index tag in the CDX, and delete the IDXes. You must also change all the lines where these indexes are mentioned. Remember that an IDX is not automatically updated, they must be specifically included to be used and updated.
>>
>
>Tore,
>
>How can I create CDX? I don't have exclusive access to customer's data.
>As for changing IDX to CDX, it's impossible. The application is huge. I would rather re-write it from scratch, but if I need to do this alone it would take at least 6 months. I'm not sure I can present the idea of re-writting to my manager.
>
>Given the current situation and IDX indexes, what should I do to speed up select statements?
>
>>>Hi everybody,
>>>
>>>I have a huge application which looks like it was converted from FoxBase into VFP8. It uses IDX indexes. I'm wondering if these indexes can be used in SQL Select?
>>>
>>>I'm trying the following select statement, which takes too long to execute:
>>>

>>>SELECT Styles.Style, ;
>>>sum(IIF(Orders.Acc = "HOUSE" and (not Approved == "" OR not App_Date = {}), ;
>>>Or_Items.qty-Or_Items.shiped-Or_Items.can_qty, 000000000.00)) as nHouseQty, ;
>>>sum(IIF(Orders.Acc = "HOUSE" and (not Approved == "" OR not App_Date = {}), ;
>>>000000000.00, Or_Items.qty-Or_Items.shiped-Or_Items.can_qty)) as nCustomerQty ;
>>>from Styles INNER JOIN Or_Items ON Styles.Style = Or_Items.Style ;
>>>INNER JOIN Orders ON Or_Items.PON = Orders.PON ;
>>>WHERE &lcWhere ;
>>>group by 1 INTO CURSOR Dummy readwrite

>>>
>>>where lcWhere is

>>>[Styles.In_Stock <=1 ] + ;
>>>[and not Styles.Style IN ("F","S","X","D","M", "CD", "PK") ] + ;
>>>[and not "." $ Styles.Style and Styles.DisContinu = "A"]

>>>
>>>AFAIK Styles has an index (IDX) on Style, OR_Items has an index on Style, Orders has an index on PON. I also tried to first select info from Styles, then join with two others - it is still very slow.
>>>
>>>What can I do to speed this select?
>>>
>>>Thanks in advance.