One of our screen enquiries uses the following select to return the correct records,

SELECT TABLE WITH AGENT=NNNNNN AND WITH PRINTED=0

The results do not contain records with Agent=NNNNNN but two entirely different numbers. Both field have Btree indexes applied.

If you run the select in two halves i.e.

SELECT TABLE WITH AGENT=NNNNNN and then

SELECT TABLE WITH PRINTED=0

the correct results are returned.

We have removed, added back and rebuilt the indexes for this table but the error persists.

Any idea what is going wrong?

This is not answer to your question, but

I can recomend … create symbolic F_AGENTTOPRINT

with

EQU CE$AGENT TO 1;EQU CE$PRINTED TO 38; *an example:)

@ANS=IF @RECORD=0 THEN ""

@ANS=@ANS:@RECORD

and use

'select TABLE WITH F_AGENTTOPRINT=<":NNNNNN'

I forgot to mention that you are supposed to index the field.

You're right it it not the answer to 'WHY'. Also I suspect that your suggestion will be slower than doing two select statements as at least one is using indexes.

Do I detect some knowledge of the application Mr Hippo?

I would remove the btree index from PRINTED.

I don't suggest a btree index on a column like PRINTED where there are only a few unique values. Does PRINTED always have a 0 and 1 value? Is it possible there is a third value of null? So, if 0 and null are equivalent I would say WITHOUT PRINTED instead of WITH PRINTED=0.

In my systems, where I'm looking for the UNPRINTED rows, and the length of the keys of those UNPRINTED rows (plus a delimiter) don't exceed the 64k byte limit, I start with a Relational index. You get all the keys quickly, then I'd read those rows for my AGENT value.

A few ideas.

Dave

This sounds simular to an index problem we had several years ago and were lucky enough to get some help thru this discussion area. For us we had to do two things. First, make sure the display length of the two indexed columns are 5 more than the longest value in each column. Second, check your data. We found that some of our indexed data had bad data, like unprintable characters, nulls (this one was on a key column and took some work getting rid of) or value marks, sub-value marks in the key column. Speaking of which, you may want to check to see if your data is defined as a multi-value or a single value column and make sure your data is actually that way in the column.

For us we found just a hand full of values in the offending columns that had bad data but it was enought to mess up the whole index for that column and thus mess up our Select/List statements.

I hope this helps.

Michael Slack

For what it's worth, I have not been able to use a single Select on multiple indexed fields either.

When the single Select does work, it seems to run slower than doing two Selects. Apparently indexed fields unions are something Arev can't deal with???

Run the second select from the active cursor the first select creates. If you can start with the btree field that will likely yield fewer records on the first select it can drastically speed things up too.

It would be interesting to learn if there is a special trick to performing unions when selecting on btree indexed fields.

Peter,

I had an occurence like that once, where selecting on multiple indexed fields returned spurious results but performing them as separate selects returned the correct results.

The problem ended up being a key-length issue. In your dictionary for the table, you will have a key column (look for the one that is defined as 'master' when you do a LISTDICT on the table). This field will have a defined length, let's say 10. If you have any records whose key is longer than 10, then this can corrupt the indexes.

In my case, the key field was defined at length 10, and I had a record with a length of 15. To identify the records, I created a symbolic (@Ans=Len(@ID)), then selected for any records with this value that was greater than that defined in the dictionary. To be safe, I updated not just the master, but any synonym fields as well.

Once I either got rid of the offending records or updated the key-length definition, I rebuilt the indexes and the problem was solved.

Also, if you have a multi-part key, don't forget to increase the lenght of the key by at least (n - 1), where n is the number of key-parts. This accounts for the '*' delimiter used between key-parts.

Finally, you might want to check for any records that have a system delimiter (@FM, @VM, @SVM, etc.) in the key. You cannot do this from the TCL but have to write a program to check the keys, and then either re-writing the records with a new key, or just deleting them from the table.

HTH,

msorrel@greyhound.com

Greyhound Lines, Inc.

Printed contains 0 for unprinted records, a staement number when the record is printed or an 'X' when the record is to be excluded from a statement.

Both these fields are used extensively around the system, but the point of the query is why do they return incorrect results when used together but correct results when used independently.

I have tried to test it … 2 selects or double select takes 10 to 16 seconds, time of symbolic select is much less than 1s.

(We cut the mentioned table some times but it has 309000 records now)

It takes some time to create the index, but it is good investment.

(Do I guess the EQUs right?)

May be I start with the same application as you.

Creating indexed single selects is always better as searching in one dimmension takes logarithmic time and searching in two dimensions takes time proportional to at least the size of one projection.

Unfortuantely such trick is impossible:(

(It seems to me the best you can hope for is $n^\Theta(1)$ time if $n$ points in the 2 dimensional square are randomly distributed.)

But often the repeated selects can be optimized (as I suggest here).

BTW: During the testing, all queries return the same set of 60 results.

Matt's suggestion seems logical.

I'm guessing you're looking at a COMMENTRY table, where the actual keys are composed of a hideous Date*Time*Station(_Counter) combination. Try defining the master {VERSION} key as length 40 and rebuilding the indexes.

Alternatively, restructure the entire application to use shorter keys wherever you see Date*Time*Station e.g. we use random alphanumeric keys which automatically optimise their length. (The coding is surpisingly easy - however, the data conversion on existing records can take a while!

Thanks to all who answered this question. We have now solved this as suggested by making the key fields greater than the largest value and ensuring that the @id length is the summ of the length of the three part key plus two for the delimiters.

Easy when you know how!!

I don't know how much are the applicatins simillar.

But the biggest speedup in our application comes from building

F_REASDATE index in table FUTURE.

(Again using onedimensional query instead of twodimensional query).

Does anyone else use this technique, too?

Try it.

As indexes are a "cheap" resource we use them for situations where we have multi level sorts and selects. It makes both a lot quicker. It's the AREV/OI equivalent of clustered indexes in other less flexible systems :)

support@sprezzatura.com

The Sprezzatura Group Web Site

World Leaders in all things RevSoft

Peter,

I'm glad that worked for you.

When I had that issue, it took me almost two weeks of 14 hour days to find it and resolve it. And, of course, the users were screaming because the couldn't run any of their reports.

msorrel@greyhound.com

Greyhound Lines, Inc.

View this thread on the forum...