Re: OR mappers slowing things down

Post #157,248 by ChrisR 5/27/04 2:51:27 PM Reply	Re: OR mappers slowing things down That is what I keep saying about relational and tables: higher abstraction may cost some in performance. Notes, the fact that you elided the last part of the comment (some gains wrt cacheing, and there some other optimizations possible as well, such that you may actually get better performance). Notes also, that Procedural abstraction have the same concerns as an OO one, in terms of creating a procedural abstraction layer. Procedural languages are no more related to the relational calculus as OO languages. Some do, but as I understood it, many recommend more direct access to the RDBMS or a creating a lite-layered custom OR-mapper that fits that particular app. Notes, that OO languages are just as capable of using raw SQL commands as procedural ones. By the way, what do you think's the reason for poor OODBMS sells? Probably because of all the hype generated by the table programmers.
Post #157,261 by tablizer 5/27/04 3:32:44 PM Reply	Re: OR mappers slowing things down and there some other optimizations possible as well, such that you may actually get better performance May be true of TOP-techniques also. Why scotts "600" table couldn't cache in RAM more effectively, who knows. Maybe they just couldn't find the right Oracle tweak. Procedural languages are no more related to the relational calculus as OO languages. It is just that OO and databases tend to fight over territory. In p/r, the "noun attribute model" is mostly in the DB, not code structures; but in OO you have classes that tend to mirror the noun attribute model, fighting with the DB over that duty. Notes, that OO languages are just as capable of using raw SQL commands as procedural ones. True, but it starts to look rather procedural in design if you do that. Probably because of all the hype generated by the table programmers. I wish it were true. Fight fire with fire :-) ________________ oop.ismad.com
Post #157,266 by ChrisR 5/27/04 3:52:22 PM Reply	Procedural abstraction In p/r, the "noun attribute model" is mostly in the DB, not code structures; but in OO you have classes that tend to mirror the noun attribute model, fighting with the DB over that duty. You use these words as if they actually mean something? I'm thinking you don't quite understand the concept of abstraction - you seem to use it as if it's a "bad" word. The point of abstraction is to hide implementation details at a lower level of code such that the code built upon top of that abstraction need not worry about it. Specifically, if you try to abstract the fact that you don't care how, when and where a method (or procedure or function) goes about it's business of doing a request, then you are half way there to abstraction. Now build a procedural model that doesn't care about how data is stored (any number of database vendors or persistance or text files or ....). You soon find that building an abstraction in the Procedural code is just as hard (if not harder since you limit your toolbox). The fact that you assume that Procedural and Relational go hand in hand mean that you miss the obvious fact that you are tightly coupled to a specific modus operandi. Now if what you want is to not build an abstraction of the storage mechanism, I'd say that most OO languages are more than happy to oblige. After all, OO languages are a superset of procedural ones since they always have the ability to stuff all the code into a single static method.
Post #157,286 by tablizer 5/27/04 4:59:40 PM Reply	My abstraction can beat up your abstraction Relational is about much more than JUST "storage". That is what OO'ers don't get. They use it JUST for storage, but then end up reinventing all the other stuff in their OO app anyhow. They have to reinvent it because OO does not provide enough power out-of-the-box. To add it requires reinventing a (navigational) database. Relational provides a fairly standardized way to manage state and noun attributes that OO lacks. Everybody ends up doing it so differently. Plus, OO often hard-wires access paths into the design. If I wanted to be able to easily swap database engines, then I could just use lowest-common-denominator SQL. But why don't I do this? because I want to use the rest of the DB features also. BTW, SQL is an interface, not an implementation. Ponder that. The only way OO systems get out of vendor lock is to have a translation layer. There is no reason an equally-powerful (and maybe equally flawed) intermediate query language could not be built for procedural. The fact that it does not exist likely means the need for it is not as great as OO'ers claim. Plus, the OO frameworks tend to be language-locked. Thus the choice is DB vendor lock or language lock so far at this stage in the swap wars. Pick your poison. If you can clearly demonstrate that OO is higher abstraction without fuzzy zen talk, be my guest. ________________ oop.ismad.com
Post #157,296 by ChrisR 5/27/04 5:23:52 PM Reply	re: Relational is more than storage (new thread) Created as new thread #157295 titled [link\|/forums/render/content/show?contentid=157295\|re: Relational is more than storage]
Post #157,302 by ben_tilly 5/27/04 5:44:43 PM Reply	It wasn't a caching issue It was CPU spinning due to searching through the table for data. Though I admit to being puzzled as to why IF/THEN written in PL/SQL would beat a hash lookup from adding the right index to a table. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #157,311 by admin 5/27/04 6:36:08 PM Reply	No file IO In memory always beats file IO. The algorithm produced a hard-coded binary search in IF/THENs. :-) Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #157,314 by ben_tilly 5/27/04 6:42:57 PM Reply	I understand how it worked It just surprises me that binary search in PL/SQL beats an index lookup. After all index lookups can be implemented many ways, including binary search or a hash lookup. Personally with 2000 things I'd expect a properly coded hash lookup to beat a binary search. Oh well. Optimization often has little surprises like that for obscure implementation reasons. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #157,317 by admin 5/27/04 6:51:28 PM Reply	Index lookup in code, or table index? Table index requires file IO. If you're talking about using string keyed hashes in the programming language, keep in mind that this is v8 PL/SQL. There ain't no sich beastie. Integer index only. 9i has associative arrays, but there are still some deficiencies to them. Even if we had decent hashes, since the connection state is blown away between pages there's no place to keep the hash without it being recreated every time. Persistence in this situation requires that the data be represented by code. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #157,323 by ben_tilly 5/27/04 7:14:44 PM Reply	That requirement would shock me Table index requires file IO. Why? Oracle should be smart enough to cache a frequently used table in RAM. If you change it, then you have to hit disk. But if you don't, then there is no reason in the world for them to be so stupid, and I don't think that they are stupid. Furthermore your problem was that the query was spinning lots of CPU. Slowness from hitting I/O won't show up on your CPU usage statistics. If I was going to guess the cause of the problem, I'm going to bet on low-level implementation details. An index lookup is fast. But before Oracle gets there the execution path has to include getting a latch (that's a kind of lock), look for the cached query plan for the current query, find it there (let's ignore the parse route since most of the time the common query has a parse in cache), release the latch, interpret that plan, realize that the plan says to do an index lookup, locate the appropriate index, realize that it is in cache, do the index lookup, look for the appropriate row, find it in cache, read it and return it. I've probably missed something that it does. You'll note that several of these steps involve string comparisons that are going to take CPU time. That's the overhead which I think makes it possible to beat an index lookup using straight PL/SQL. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #157,332 by admin 5/27/04 10:10:43 PM Reply	Re: That requirement would shock me Why? Oracle should be smart enough to cache a frequently used table in RAM. And if they're all frequently used? :-) Furthermore your problem was that the query was spinning lots of CPU. If I gave that impression, it was erroneously. Performance was decreased, but that doesn't mean more CPU necessarily. Basically the question you ask the profiling tool is "how much time is being spent doing foo?" Whether that time is spent doing IO or spinning the CPU doesn't matter. It's still time spent. And if the time spent is 15% of the overall time spent across the system then it's a good candidate for optimization. I'll have to talk to the DBA on Tuesday to get the particulars. The developer who rewrote it was a little fuzzy on why it was so slow in the first place (this was two years ago). He just remembered that it had something to do with file IO, and pinning the table made no difference. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #157,334 by ChrisR 5/27/04 10:33:28 PM Reply	Question about Oracle tables Is it possible to define a memory based partition that holds selectable tables? Not that this is a question about the problem/solution you are talking about, but I've always thought that allowing the programmer to set up memory based tables for optimization purposes might be a useful optimization technique for certain lookup tables that you know are used frequently.
Post #157,341 by admin 5/27/04 11:06:22 PM Reply	You can pin them in memory. Assuming you have enough memory. As I said, I'm going to have to take it up with the DBA as to why that wasn't sufficient. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #158,339 by admin 6/3/04 10:56:28 PM Reply	Oracle tables pinned in memory. As it turns out, I was wrong all around, and right for the wrong reasons. The actual problem was lock contention. The table was pinned, but Oracle places micro locks for reads, effectively serializing reads on single blocks. The CPU was churned by grabbing and releasing locks on the parm data repeatedly. Since we make extensive use of that data (as I indicated, this is Bryce's dream architecture), the lock management became a significant consumer of CPU. Why Oracle needed to lock read-only data I neglected to find out. This is also a significantly dumbed-down version of the explanation I was given. :-P Also I was misremembering the %cpu being used. The actual figure was MUCH higher. The DBA estimates that we would have been maxed out at 25% of our current capacity had the change not been made. An interesting comment he made: Oracle considers the heavy use of a single parm table such as we were doing to be an application design flaw. :-) Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."
Post #157,338 by ben_tilly 5/27/04 11:04:10 PM Reply	You did give the impression that CPU was the issue In your description at [link\|http://z.iwethey.org/forums/render/content/show?contentid=157019\|http://z.iwethey.org...?contentid=157019] it said 15% of CPU time was spent on this query. I've been working from the assumption that this was the problem that needed solving. If that is wrong, then reasonable theories to explain what didn't happen are, of course, superfluous. Cheers, Ben To deny the indirect purchaser, who in this case is the ultimate purchaser, the right to seek relief from unlawful conduct, would essentially remove the word consumer from the Consumer Protection Act - [link\|http://www.techworld.com/opsys/news/index.cfm?NewsID=1246&Page=1&pagePos=20\|Nebraska Supreme Court]
Post #157,340 by admin 5/27/04 11:05:24 PM Reply	Whoops, my mistake. Regards, -scott anderson "Welcome to Rivendell, Mr. Anderson..."

Welcome to IWETHEY!