Re: opinions on this SQL statement
Discussion of a huge, machine-generated SQL statement (written by an MS-SQL-minded developer) that ran very slowly on Informix, with claims that such queries run an order of magnitude faster on Oracle. Participants debated why: whether Informix re-executes IN subqueries per outer row due to isolation/consistent-read differences, versus the view that it's simply optimizer/rewrite maturity (DB2 turns IN into a left outer join allowing hash/merge joins). The poster later reported the real cause: a very large unindexed temp table assumed to stay in memory; the plan was to make it permanent with proper indexes, plus rework the SQL. No confirmed benchmark follow-up is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Never mind. All I wanted to say is that I encountered a class of SQL statements (of which this one was only superficially representative) which tend to run an order of magnitude slower on Informix compared to Oracle (to my great surprise), on very small database with identical indices, fresh statistics, similar buffer pool allocation and no special tuning. Bonzi ----- Original Message ----- From: "Hamilton, Jerry" <hamiltoj@fleishman.com> To: "'Dragi Raos'" <draos@pardus.hr>; "sumGirl" <emebohw@netscape.net>; <informix-list@iiug.org> Sent: Tuesday, May 25, 2004 8:29 PM Subject: RE: opinions on this SQL statement > Huh? > > -----Original Message----- > From: Dragi Raos [mailto:draos@pardus.hr] > Sent: Tuesday, May 25, 2004 12:42 PM > To: sumGirl; informix-list@iiug.org > Subject: Re: opinions on this SQL statement > > > All I can tell is that horrors like this (with average size 3GL program > masquerading as an SQL statement) indeed run much faster in Oracle than > Informix. I learned it hard way: one of my developers (insufficiently > supervised) wrote five-page selects like that. They were ten times slower on > Informix than Oracle... > > Bonzi sending to informix-list
Dragi Raos wrote: > Never mind. All I wanted to say is that I encountered a class of SQL > statements (of which this one was only superficially representative) > which tend to run an order of magnitude slower on Informix compared to > Oracle (to my great surprise), on very small database with identical > indices, fresh statistics, similar buffer pool allocation and no special > tuning. > > Bonzi You will always find such a class of statements going from any DBMS to any other. Just like any other area of a DBMS some areas in the runtime engine and the optimizer will receive more attention that others for various reasons. It would be scary if different, proprietary, codebases would be that aligned to show a similar profile. Cheers Serge -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab
Dragi Raos wrote: > Never mind. All I wanted to say is that I encountered a class of SQL > statements (of which this one was only superficially representative) > which tend to run an order of magnitude slower on Informix compared to > Oracle (to my great surprise), on very small database with identical > indices, fresh statistics, similar buffer pool allocation and no special > tuning. The IN in Informix must work VERY differently from Oracle. In Oracle your reads are always isolated from otheres writes. It's a basic principle in Oracle. This allows it to execute the "IN" only once and use their values in each cycle of the outside table. Informix works very differently. It must execute the subquery for every cycle of the outside table because it isn't isolated from others writes to the inner table... It's a concept completely different which has consequences. Not understanding this and using this as excuse (as the original post mentioned) is not IMHO correct. Regards.
Fernando Nunes wrote: > Dragi Raos wrote: > >> Never mind. All I wanted to say is that I encountered a class of SQL >> statements (of which this one was only superficially representative) >> which tend to run an order of magnitude slower on Informix compared to >> Oracle (to my great surprise), on very small database with identical >> indices, fresh statistics, similar buffer pool allocation and no special >> tuning. > > > The IN in Informix must work VERY differently from Oracle. > In Oracle your reads are always isolated from otheres writes. It's a > basic principle in Oracle. This allows it to execute the "IN" only once > and use their values in each cycle of the outside table. > > Informix works very differently. It must execute the subquery for every > cycle of the outside table because it isn't isolated from others writes > to the inner table... > > It's a concept completely different which has consequences. Not > understanding this and using this as excuse (as the original post > mentioned) is not IMHO correct. > > Regards. I doubt the isolation level has anything to do with that. There is no rule that states that data needs to be re-read within a single statement. Whether intermediate results are TEMP'ed or not is up to the DBMS' optimizer. E.g. in DB2 (which shares all isolation levels except read-commited with IDS) teh IN would be transformed into a LEFT OUTER JOIN with an "early out" semantics. Once that is done the optimizer will determine in which order to execute the joins and which startegie to take. This may include e.g. SORT MERGE or HASH JOIN. Both of which will only read the original IN list once. It would be interesting to see the optimizer plan for this insert :-) Cheers Serge -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab
Thanks all. The developer in question is a MS-SQL server developer at heart. Dont get me wrong, there not all bad, but this one is one of the card carrying, goose stepping kind and feels insulted anytime he cant do EVERYTHING he needs to do with via graphical interface. Anyway, for now I think a cuople of you have hit on something with the sql syntax. Also, this tmp_* table is a dynamic/in memory table that was not being indexed because this developer was assuming it would always remain resident in memory - but its HUGE! I suspect that its not always remaining resident and sometimes going out to disk because of its size so we are going to retool and make this a perm table with a bevy of proper indices. These details were not available to me when I posted, sorry. Troubleshooting this particular persons issue is like herding cats because he's so intent on it not having anything to do with anything he has done. Why can we all just get along? :)
Serge Rielau wrote: > I doubt the isolation level has anything to do with that. > There is no rule that states that data needs to be re-read within a > single statement. > Whether intermediate results are TEMP'ed or not is up to the DBMS' > optimizer. > E.g. in DB2 (which shares all isolation levels except read-commited with > IDS) teh IN would be transformed into a LEFT OUTER JOIN with an "early > out" semantics. Once that is done the optimizer will determine in which > order to execute the joins and which startegie to take. This may include > e.g. SORT MERGE or HASH JOIN. Both of which will only read the original > IN list once. > It would be interesting to see the optimizer plan for this insert :-) Well, I maybe wrong about Oracle. But I think it normally reads allways consistently. If a row is beeing changed it reads the REDO log image, getting the row before any change, and it doesn't abort with locks. I think this is a different "isolation level". Different of the "normal" ones. I would like to see some input about this from the Informix gurus... I'm used to see this kind of queries taking a lot of time in Informix, and I allways thought this was related to what I posted. Last one I got was doing a sub-query with the outside table itself. It was looking for rows where a date column was the MAX() from a certain type of row. With 300.000 and no appropriate index it was running for two days... With appropriate index it run under 5m (!) :) If there is no assumption or rule about this it would be a nice inprovement for the optimizer to make it understand this situations. Another issue that I have doubts is related to opening a cursor (let's assume it needs a sequential scan) and at the same time having other sessions inserting into the table rows which satisfy the cursor conditions. Would this rows be retrieved or not? And why? (Let's assume the rows are inserted within completed transactions. Again, I believe Oracle would not retrieve this rows... Any comments (maybe a new thread...?) are very welcome. Regards!
"Serge Rielau" <srielau@ca.eye-be-em.com> wrote > I doubt the isolation level has anything to do with that. > There is no rule that states that data needs to be re-read within a > single statement. > Whether intermediate results are TEMP'ed or not is up to the DBMS' > optimizer. > E.g. in DB2 (which shares all isolation levels except read-commited with > IDS) teh IN would be transformed into a LEFT OUTER JOIN with an "early > out" semantics. Once that is done the optimizer will determine in which > order to execute the joins and which startegie to take. This may include > e.g. SORT MERGE or HASH JOIN. Both of which will only read the original > IN list once. > It would be interesting to see the optimizer plan for this insert :-)
You should have the developer run his query through the SQL Query Analyzer Query-Plan while you watch and see how his query runs on SQL-Server. It might give you some insight into how it works. Just start SQL-Query-Analyzer ( the equivalent of ISQL or DB-Access ) and paste the query into the edit window, and then CTRL-L and the query plan will pop up below your query. ( The menu option is "Display Estimated Execution Plan" ) While the information will most likely be cryptic to you, it will show you what indexes if any his query is/is not using to help you understand how the query works on SQL-Server. Table scans will pop up too. :-) Good luck! "sumGirl" <emebohw@netscape.net> wrote in message news:a5e13cff.0405260428.2df5124a@posting.google.com... > Thanks all. The developer in question is a MS-SQL server developer at > heart. Dont get me wrong, there not all bad, but this one is one of > the card carrying, goose stepping kind and feels insulted anytime he > cant do EVERYTHING he needs to do with via graphical interface. > > Anyway, for now I think a cuople of you have hit on something with the > sql syntax. Also, this tmp_* table is a dynamic/in memory table that > was not being indexed because this developer was assuming it would > always remain resident in memory - but its HUGE! I suspect that its > not always remaining resident and sometimes going out to disk because > of its size so we are going to retool and make this a perm table with > a bevy of proper indices. > > These details were not available to me when I posted, sorry. > Troubleshooting this particular persons issue is like herding cats > because he's so intent on it not having anything to do with anything > he has done. > > Why can we all just get along? :)