Search for ROWID
- Thread starter enoon
- Start date
With a rowid always do a find and not a find first. It is silly to program a find first in this case. Either the record is there or not. a rowid is the "physical" location of the record in the area where the table resides. A rowid addresses the exact database block and position within this database block of the record.
Casper.
Casper.
TomBascom
Curmudgeon
FIND FIRST is almost always the wrong thing to code.
It is a horrible bad habit, usually mindlessly adopted from ancient and poorly conceived code which may be multiple generations away from the current code base that doesn't work the way anyone in the chain of miscreants who have copied it forward believe that it works.
If the WHERE clause resolves to an index which will return a unique value then only the minimum number of logical reads will occur. Whether you use FIRST or not. FIND by RECID and ROWID are always unique. There is no point in using FIRST.
If the WHERE clause does not resolve to a unique find then using FIRST is almost always a band-aid that introduces an implied and hidden ordering to your query that can induce some extremely nasty and subtle bugs.
The performance problems that are "solved" by FIND FIRST come from either the lack of appropriate indexes or incompletely formed WHERE clauses. It is far better to address the root problem than to apply a band-aid that will later turn the wound gangrenous.
There is a very small class of appropriate uses for FIND FIRST. It should, however, never be a semi-automatic suffix to all instances of FIND.
It is a horrible bad habit, usually mindlessly adopted from ancient and poorly conceived code which may be multiple generations away from the current code base that doesn't work the way anyone in the chain of miscreants who have copied it forward believe that it works.
If the WHERE clause resolves to an index which will return a unique value then only the minimum number of logical reads will occur. Whether you use FIRST or not. FIND by RECID and ROWID are always unique. There is no point in using FIRST.
If the WHERE clause does not resolve to a unique find then using FIRST is almost always a band-aid that introduces an implied and hidden ordering to your query that can induce some extremely nasty and subtle bugs.
The performance problems that are "solved" by FIND FIRST come from either the lack of appropriate indexes or incompletely formed WHERE clauses. It is far better to address the root problem than to apply a band-aid that will later turn the wound gangrenous.
There is a very small class of appropriate uses for FIND FIRST. It should, however, never be a semi-automatic suffix to all instances of FIND.
At the risk of being beaten, laughed at, and forever scarred :awink:, what would be an example of an appropriate use for FIND FIRST?
TomBascom
Curmudgeon
Frankly I have a hard time dreaming up appropriate uses 
This might be a stretch and it certainly isn't the best way to loop through records (when you don't care about their order) but this is at least relatively harmless
This might be a stretch and it certainly isn't the best way to loop through records (when you don't care about their order) but this is at least relatively harmless
Code:
FIND FIRST customer no-lock no-error.
do while available( customer ):
/* do something with the customer record */
find next customer no-lock no-error.
end.TomBascom
Curmudgeon
No.
It is a complete waste of bytes.
FIRST does not impact the speed of a unique FIND. Not even by a micro-second. People who think that it does do not understand either FIND or FIRST.
FIRST impacts the speed of a non-unique FIND but only at the cost of obfuscating the fact that the FIND is not unique. This obfuscation, usually inserted out of habit and for no benefit, has been a significant source of nasty bugs over the course of my career.
In those very few cases where it is actually making a performance difference the correct course of action is to specify a WHERE clause with appropriate criteria and to ensure that a proper index supporting that query exists. (Which is what Casper has done.)
FIND FIRST should never, ever be an automatic coding convention. It is wrong and harmful to do so.
USE-INDEX will have to wait for another thread.
Well, I will
Not that I'm not believing you, but I want to see it with my own eyes.
Thanks for the answers.
And yes a discussion about USE-INDEX pros and cons would be great.
GregTomkins
Active Member
I think USE-INDEX is OK every now and then. Sometimes its necessary to be able to enforce use of an index based on things PSC doesn't track, like the optimization stats that SQL uses (eg. the fact that birthdates will be distributed a lot differently than genders, for example).
It's also nice for the next guy to know which index is being used without having to run a compile-xref and/or wade through the multi-page explanation of how P4GL picks indices. This isn't a good enough reason on its own though.
The con would be, I guess, if you add an index that would make the query more efficient, it won't try to use it. I have never seen that happen in real life. But I would also guess I actually use USE-INDEX maybe .001% of the time. To use it on every query ... that would be worse than FIND FIRST!
It's also nice for the next guy to know which index is being used without having to run a compile-xref and/or wade through the multi-page explanation of how P4GL picks indices. This isn't a good enough reason on its own though.
The con would be, I guess, if you add an index that would make the query more efficient, it won't try to use it. I have never seen that happen in real life. But I would also guess I actually use USE-INDEX maybe .001% of the time. To use it on every query ... that would be worse than FIND FIRST!
tamhas
ProgressTalk.com Sponsor
The documentation aspect is adequately solved with a comment ... radical notion, I know.
When this discussion comes up, a lot of the time I find that people fail to notice that there are very different cases. In the vast majority of cases, not only is the WHERE clause quite simple, but in all probability some attention has been paid to providing indexes for common queries and so the relationship between the query and a corresponding index is both simple and obvious. So much so, in fact, that even documenting the relationship is questionable unless one has an automated system for doing so (theme and variations on databasing COMPILE XREF data).
Thus, there are actually only a small percentage of queries where the relationship is even complex enough to be interesting. I think these fall into two categories, intentionally interesting and unintentionally interesting. The intentionally interesting category is the sort of thing one runs into in analytical reporting where one want to slice and dice the data in many and unpredictable ways. There are strategies for that sort of thing, but they need to be carefully considered on a case by case basis. This is a good domain for dynamic queries and one certainly doesn't want to be tying the hand of the engine there, but rather letting it fit the tool to the problem. This is also a good type of problem for some considered documentation to help the next person understand why a particular strategy has been used ... not just the index ... especially since requirements may change over time and the strategy may need to be adjusted.
Where things get interesting are the unintentionally complex cases. In many cases, this is a question of asking the wrong question. A really classic case of this is a database design which includes a company code or some such as the leading field on many indexes, but at a particular site there is only one company so someone writes a query which does not include the company code in the selection criteria. Presto, full table scan. Of course, USE-INDEX isn't going to help this any and what really needs to happen is intelligent thought about the query and its relation to database design. Sometimes, it turns out to be a limitation in the database design which is really at fault. E.g., a series of multi-component indexes are provided, but the combination of fields doesn't quite match the current case, perhaps because there is no selection on some relatively high component of the best index and thus no further bracketing. These are cases that can't be solved by USE-INDEX either ... one might make them slightly less horrid, but the real answer is to recognize that the universe of ways that one needs to get at the data is now larger than it was before and that the indexing strategy needs to change.
When this discussion comes up, a lot of the time I find that people fail to notice that there are very different cases. In the vast majority of cases, not only is the WHERE clause quite simple, but in all probability some attention has been paid to providing indexes for common queries and so the relationship between the query and a corresponding index is both simple and obvious. So much so, in fact, that even documenting the relationship is questionable unless one has an automated system for doing so (theme and variations on databasing COMPILE XREF data).
Thus, there are actually only a small percentage of queries where the relationship is even complex enough to be interesting. I think these fall into two categories, intentionally interesting and unintentionally interesting. The intentionally interesting category is the sort of thing one runs into in analytical reporting where one want to slice and dice the data in many and unpredictable ways. There are strategies for that sort of thing, but they need to be carefully considered on a case by case basis. This is a good domain for dynamic queries and one certainly doesn't want to be tying the hand of the engine there, but rather letting it fit the tool to the problem. This is also a good type of problem for some considered documentation to help the next person understand why a particular strategy has been used ... not just the index ... especially since requirements may change over time and the strategy may need to be adjusted.
Where things get interesting are the unintentionally complex cases. In many cases, this is a question of asking the wrong question. A really classic case of this is a database design which includes a company code or some such as the leading field on many indexes, but at a particular site there is only one company so someone writes a query which does not include the company code in the selection criteria. Presto, full table scan. Of course, USE-INDEX isn't going to help this any and what really needs to happen is intelligent thought about the query and its relation to database design. Sometimes, it turns out to be a limitation in the database design which is really at fault. E.g., a series of multi-component indexes are provided, but the combination of fields doesn't quite match the current case, perhaps because there is no selection on some relatively high component of the best index and thus no further bracketing. These are cases that can't be solved by USE-INDEX either ... one might make them slightly less horrid, but the real answer is to recognize that the universe of ways that one needs to get at the data is now larger than it was before and that the indexing strategy needs to change.
Similar threads
- Locked
- Locked
