none
Slow foxpro performance over OLEDB suddenly. RRS feed

  • Question

  •  

    I have 2 large tables:

    HEADER, and ITEMS

     

    and 2 smaller tables:

    COL_TAB, and PAT_TAB

     

    when I join them all together, it's very very slow... even with the correct indexes in place. I've also tried re-creading the indexes and re-indexing the tables.

     

    If i take out COL_TAB out of the query, it takes 1 second..

     

    the wierd part is, it's only for 1 company... and this company has the exact same indexes as all the other companies.

     

    COL_TAB.COLOR_CODE = ITEMS.COLOR  is where the problem lies..

     

    I have an index in COLTAB called COLOR, and its indexing the field COLOR_CODE (the primary key)

     

    in items I have one in the field COLOR wich is a foreign key..

     

     

    this is the query:

     

    Select header.reference

     

    FROM ITEMS, HEADER, COL_TAB, PAT_TAB

    where

    HEADER.REFERENCE = ITEMS.REFERENCE AND

    ITEMS.REFERENCE + STR(ITEMS.SEQUENCE,3,0) = RUNS.REFERENCE + STR(ITEMS.SEQUENCE,3,0) AND

    PAT_TAB.PAT_CODE = ITEMS.PATTERN AND

    COL_TAB.COLOR_CODE = ITEMS.COLOR

     

    I have all of these indexes in each table, for the join to take advantage. 

    also this was not happening a day ago, so i don't think its the query at all.

    up until now, this query has taken like 3 seconds and now this with no explanation...

     

    Tuesday, June 17, 2008 1:12 AM

Answers

  • The causes is still unknown but here's what i did to fix it:

     

    Copy the DBF and CDX files from a known good company

    Zap them

    Append From OldTable

     

    Move Old Table somewhere else as a backup.

     

    It's gotta be a bug in the OLEDB Provider. Foxpro sees these indexes and can use them no prob.

    and even re-indexing didn't help, neither did deleting and re-creating the indexes.

    Monday, July 21, 2008 5:49 PM