WesleySmith
New Member
Hi Everyone,
We are currently performing some analysis of our code base to see where we can improved performance and reduce database reads.
We have a table: Table1 which can contain a lot of records.
This table has an index, lets call it idx1, which has these fields: Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8
This index is not unique
Our query currently looks like this:
The value of Table1.Field4 will either be 11, 21 or 31
According to XREF the index being selected is idx1 but if my understanding of index usage is correct (and I may be wrong) it will only select the first 4 components of the index due to the range being used for Field4 - Please correct me if I am wrong.
So I am looking for ways to improve this.
One idea I have is to do this:
I think this will then use all 8 components of the index and the end result will be a faster and more efficient query.
Am I correct, or is there another way?
Thanks all in advance.
We are currently performing some analysis of our code base to see where we can improved performance and reduce database reads.
We have a table: Table1 which can contain a lot of records.
This table has an index, lets call it idx1, which has these fields: Field1, Field2, Field3, Field4, Field5, Field6, Field7, Field8
This index is not unique
Our query currently looks like this:
Code:
FOR EACH Table1 WHERE Table1.Field1 = Variable1
AND Table1.Field2 = Variable2
AND Table1.Field3 = Variable3
AND Table1.Field4 >= 11
AND Table1.Field4 <= 31
AND Table1.Field5 = Variable4
AND Table1.Field6 = Variable5
AND Table1.Field7 = Variable6
AND Table1.Field8 = Variable7
NO-LOCK:
<Do something>
END.
The value of Table1.Field4 will either be 11, 21 or 31
According to XREF the index being selected is idx1 but if my understanding of index usage is correct (and I may be wrong) it will only select the first 4 components of the index due to the range being used for Field4 - Please correct me if I am wrong.
So I am looking for ways to improve this.
One idea I have is to do this:
Code:
DO iLoop = 11 TO 31 BY 10:
FOR EACH Table1 WHERE Table1.Field1 = Variable1
AND Table1.Field2 = Variable2
AND Table1.Field3 = Variable3
AND Table1.Field4 = iLoop
AND Table1.Field5 = Variable4
AND Table1.Field6 = Variable5
AND Table1.Field7 = Variable6
AND Table1.Field8 = Variable7
NO-LOCK:
<Do something>
END.
END.
I think this will then use all 8 components of the index and the end result will be a faster and more efficient query.
Am I correct, or is there another way?
Thanks all in advance.