Concatenated fields

[Former Member]

We currently have indexed fields that are concatenated fields with up three values.  We currently are able to parse the fields with Left, Right and Mid commands using native FoxPro with little or no speed degradation.  The field are all Char.

An example is a field called OD_CustItm where the first 6 characters are Store number, then next 6 are the Customer number, and the last 6 are the Item number.

When we access the fields with a Left, Right or Mid command, Sybase takes a considerable amount of time.   How can improve the speed to be equal or better than native FoxPro.

[Former Member]

I am assuming you are using SQL with a WHERE clause specifying left,mid, right or a filter of some sort?

You will need to create indexes to match the where clause / filter.  Typically ADS optimizes the best if you have an index on each, but in some cases you can get away with a single index as long as you order your filter / where clause to  match the index.

If you are using SQL you can also use ARC to run the query and choose the Show Plan option.  Look at the tab execution plan (graphical) and anywhere you see a red dot hover over it and it should give you some clues as to what indexes it is able to use to optimize.

One a side note.  Assuming you are using the REMOTE server, ADS is a bit slower than non client-server drivers for a single user.  Just having one other user opening the table and you should see ADS as faster.