Index woes with select (AREV Specific)
At 05 FEB 2001 03:33:23PM matt sorrell wrote:
I am having problems with a select statement, and I don't think it is necessarily the index. This is very similar to an earlier thread.
If I issue the command:
SELECT EMP WITH GROUP EQ "01" AND WITH STATUS EQ "A" AND WITH FULL_PART EQ "F", the system returns 361 rows.
If, instead, I issue:
SELECT EMP WITH GROUP EQ "01"
SELECT EMP WITH STATUS EQ "A"
SELECT EMP WITH FULL_PART EQ "F"
the system returns 1830 rows, which is correct.
GROUP and STATUS are both indexed fields, and I have dropped and recreated the indexes several times.
A couple of notes. In the all-in-one select, if I reorder the GROUP and STATUS criteria, it return 422 rows. Also, this exact same select works correctly in our test environment. There are no relational indexes on either of these fields.
I tried replacing the SYSOBJ file in production with the one in test, but that didn't make a difference.
This just started on or around 02/02/2001, as near as we can tell. No major changes have been made in the production system or environment, except that RIP, an IP routing protocal, was replaced with a different routing protocol. No changes were made to the IPX/SPX protocols.
We are using ARev 3.02, running on a Novell 4.x server, with NLM 1.5a, using Windows 95 desktops with the Novell 3.1.0.0 client.
If anyone has any ideas, I would greatly appreciate. Users are starting to get frustrated, as am I.
/me looks for his "Bang Head Here" mouse pad.
At 06 FEB 2001 03:14AM [url=http://www.sprezzatura.com" onMouseOver=window.status=Click here to visit our web site?';return(true)]The Sprezzatura Group[/url] wrote:
What happens if you remove indexes all together and retry the selects? You could also extend the F1 through F5 definitions in VOC to F99 for @RECORD - enough to cover your three fields - and retry your selects using the Fxx definitions. This test would eliminate indexing as being the cause. Then it's down to the nuances of AREV's select functionality and your data.
World Leaders in all things Revsoft
At 06 FEB 2001 12:59PM matt sorrell wrote:
If I don't use the indexes at all, which I did by issuing a SELECT EMP first, then the correct number of rows are returned.
The interesting thing is, I created a dummy table, copied all of the rows into the dummy table, added just the three fields that I was selecting off of, built the two indexes, and everything worked fine.
This leads me to believe that it is an issue with the table itself, but I could be off base here.
At 08 FEB 2001 11:23AM Matt Sorrell wrote:
Well, I have fixed my indexes. I thought I would share what the problems were in my particular instance, hopefully to help other developers in the future. I realize most of this is already known to everyone, as it was to me, but I forgot to think about these things.
1)I had a record with a null key. I found this by accident when I included into my key test program logic to validate entries in the indexed columns.
2)I had a record with a key value that was longer than the default length specified in the dictionary. The interesting thing is the reason I did not find this earlier. We have a synonym for the key field, EMP_NO, that was defined as a length of 10. This is the field we always use, and so I didn't think key length was the culprit. However, this field was not flagged as the MASTER for FMC 0. The entry flagged as MASTER was only set for a length of 6. I updated all of our key field entries to a length of 10 and everything is now fixed.
Just thought I would share.
At 11 FEB 2001 12:52PM [url=http://www.sprezzatura.com]The Sprezzatura Group[/url] wrote:
I don't think it was the null key, as that should leave a null pointer in the index so the keys would be seperated cleanly. Theoretically, based on the index node structures anyway. The problem would be if the r/basic code did not handle it correctly, and we'd have to check every option for that. I'd be more likely to assume it's the keylength.
World Leaders in all things RevSoft