hello all. quick question about indexing
suppose i have a non unique index containing 4 fields - A,B,C,D.
and i want to retrieve records where A = x, B = y and D = z.
which is likely to be more efficient ?
1 - for each ... where a = x and b = y and d = z
or 2 - for each ... where a = x and b = y
if d <> z then next.
ta,
Osme
both options you mentioned would have the same index bracket and would require reading all records for "where a = x and b = y".
you're looking for a skip scan (see the example below).
and it's about time we got it in the database. we don't use the database just for transactions.
I'd settle for a NO-INDEX option.
The engine supports it.
SQL-92 has it.
The engine crew has been bragging about it for nearly 5 years.
Why doesn't the 4GL support it?
This is just my opinion but a query optimizer and a redesign of the remote connection/queries (which is just not tolerable anymore and should have never been accepted), and features like NO-INDEX and other types of scans would easily have a 10x improvement on the day to day operations of almost all installations out there.
Far more then any type 2 storage areas performance improvements. Of course there are other improvements, even strategic ones to type 2 storage areas besides performance.
I would guess that after the Advance UI and OO wind down, query improvements are going to be the next big thing on the roadmap, vision etc. THEY SHOULD BE !
so if both options result in the same bracketing, is option 1 more efficient, or does it make no difference when both are compiled?
I wouldn't expect it to make much of a measurable difference. But in general I'd rather have the WHERE clause filtering records than have a separate bit of code doing it. I find that a better coding practice and you can always hope that someday the 4GL query engine will improve in such a way that your code will become magically smarter.
I'd settle for a NO-INDEX option.
...
Why doesn't the 4GL support it?
NO-INDEX is on the list of things to do, but from my chat with Salvador at Exchange 2007 - it's one of those things which requires touching everything that uses indexes in order to implement. It may be a small language change to add that keyword, but it's not a small thing to get working behind the scenes.
So, it is coming, but I'm not sure when. Maybe Salvador'll volunteer something more specific.
in your case the most efficient way and for the foreseeable future would be a skip scan, like, the example i posted.
especially if "where a = x and b = y" covers alot of records.
try it. post the results, if you can.
Another possibility would be to do
table.a = x and
table.b = y and
table.c <> ? and
table.d = z
I've done this in the past with good success, not sure how it would compare to a skip scan performance wise though.
You, Thomas M-H. and me talked for a looong time at Exchange 2007, and about many things! I recall discussing about features that from the "outside" sometimes look like they should be simple to implement and they are not. I do not recall making the assertion about NO-INDEX 'touching everthing that uses indexes'. If I did, it was unintended; it may or it may not. Only the engineers that know the code are qualified to make such statements.
I can confirm, however, that NO-INDEX is indeed in the list of features in the ABL roadmap to consider for an upcoming (yet unnamed) release.
Salvador
You, Thomas M-H. and me talked for a looong time at Exchange 2007, and about many things! I recall discussing about features that from the "outside" sometimes look like they should be simple to implement and they are not. I do not recall making the assertion about NO-INDEX 'touching everthing that uses indexes'. If I did, it was unintended; it may or it may not. Only the engineers that know the code are qualified to make such statements.
I can confirm, however, that NO-INDEX is indeed in the list of features in the ABL roadmap to consider for an upcoming (yet unnamed) release.
Salvador
You, Thomas M-H. and me talked for a looong time at Exchange 2007, and about many things! I recall discussing about features that from the "outside" sometimes look like they should be simple to implement and they are not. I do not recall making the assertion about NO-INDEX 'touching everthing that uses indexes'. If I did, it was unintended; Only the engineers that know the code are qualified to make such statements.
I can confirm, however, that NO-INDEX is indeed in the list of features in the ABL roadmap to consider for an upcoming (yet unnamed) release.
Salvador
You, Thomas M-H. and me talked for a looong time
Apparently, it was sooo looong it took three answers!
You, Thomas M-H. and me talked for a looong
time at Exchange 2007, and about many things! I
recall discussing about features that from the
"outside" sometimes look like they should
be simple to implement and they are not. I do not
recall making the assertion about NO-INDEX
'touching everthing that uses indexes'. If I did, it
was unintended; it may or it may not. Only the
engineers that know the code are qualified to make
such statements.
And you're a lot more likely to've talked to the developers than I am.
If I put words in your mouth that wasn't there, then I'll take them back.
To me, the important part is:
I can confirm, however,
that NO-INDEX is indeed in the list of features
in the ABL roadmap to consider for an upcoming (yet
unnamed) release.
Salvador
Any idea how far out that release is?
You, Thomas M-H. and me talked for a looong time
Apparently, it was sooo looong it took three answers!
And Salvador doesn't strike me as the kind of guy who likes to hear himself talk.
Sorry about that. I'm on the road and ... well I guess you know, the internet access is not as good as it should sometimes. Doesn't it drive you crazy when the web browsers just seem to hang?
Salvador
No. Not yet.
Salvador
10.1C seems logical to me
table.a = x and
table.b = y and
table.c <> ? and
table.d = z
The solution noted above will do a range match on C which is slightly better, but since the range is undefined it will still end up reading all the records.
If the value of C happens to have a defined set of values you will have great success by nesting an outer loop to loop through the defined values for C. That way you will have an equality match down to and including D. Depending on what you are doing (building a temp table for example) the multiple loops may work just fine. Consider C having values of '1' for active, '2' for inactive and '3' for cancelled. This code would get you the fastest results, especially if 'table' has a lot of records.
do iCnt = 1 to 3:
for each table where
table.a = x and
table.b = y and
table.c = iCnt and
table.d = z
no-lock:
end.
> Any idea how far out that release is?
You have entered the well known "black hole": its an interesting idea, we will work on it ;-)
> so if both options result in the same bracketing, is option 1 more efficient,
> or does it make no difference when both are compiled?
It probably doesn't matter, since the 4GL pulls the rows to the client anyway. You should do option 1 and add/alter an index when you need better performance.