>Hi Naomi try the following
>create also an index on styles.DisContinu and then on the lcWhere swith the last line to the following
>
>[and Styles.DisContinu = "A" and not "." $ Styles.Style]
>
>this is a litle more optimized. The $ can't be optimezed so should be the last condition.
I'm not sure I can create indexes. Let me try again by filtering only Styles table on IN condition, which should be fully optimazible...
>>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.
If it's not broken, fix it until it is.
My Blog