RE: 4GL Database Feature Question

On 9 Jan 1998, Richard Thomas wrote: } Jonathan Leffler wrote: } > On 8 Jan 1998, Axel Granholm wrote: } > > We have a 4GL application that has been growing in size over the last 6 } > > years, and have a question about fragmentation aware SQL from within the } > > application. } > > } > > Many SQL statements in the 4GL application make reference to program } > > variables in order to return data from the database. Not very unique. } > > Our problem is that this application does not prepare many of these } > > statements, and therefore fragmentation elimination does not occur. That } > > is because the SQL is optimized at the prepare state, and then the } > > Dynamic SQL is opened with a USING clause. Too late for the optimizer to } > > know how to eliminate fragments. } > } > As Billy said, if you have to prepare the statements to avoid the USING } > clause so that the optimizer will do fragment elimination, then you have to } > revise the code to do the PREPARES. AFAIK (which is not as far as I'd } > like, in this case), there wouldn't be any way for an upgrade to do } > fragment elimination, unless an ESQL/C statement with variables listed in } > it would have fragment elimination done too -- and I don't think that } > happens. } > } > What I mean is: if the following ESQL/C code is optimized for fragment } > elimination: } > } > EXEC SQL SELECT value INTO :variable FROM ... WHERE somecolumn > } :input_variable; } > } > then the following, equivalent I4GL code should be optimized for fragment } > elimination too: } > } > SELECT value INTO variable FROM ... WHERE somecolumn > input_variable } > } > If the ESQL/C cannot be optimized for fragment elimination, neither can the } > I4GL. } > } > > We could invest time to go back through the application to change the } > > "Important" SQL that is likely to use fragmentation such that they are } > > PREPARED statements. (Ouch) } > > } > > Does Informix have any plans to keep the 4GL tool current with the } > > features of the database? } > } > More or less; the 6.10 release due out at the end of 98Q1 should be using } > the 7.2x ESQL/C, so you will automatically get whatever benefits there are } > from that version of ESQL/C. } > } > > Or, should I change my future development strategy to ALWAYS prepare my } > > SQL? } > } > If you have major time critical SQL statements, then yes, prepare them. If } > they are seldom executed statements, then any minor performance hit from } > not preparing them is probably outweighed by the more verbose coding } > necessary, which brings with it the possibility of errors. } } This is very interesting. We were told that the embedded products use a } two-phase optimization, and our experiences concur with this. I did a fair } amount of testing on this (under 7.14) some time ago. Then clearly I don't know as far as all that -- that's why I was cautious about being dogmatic. At least, I was trying not to be dogmatic; I may have failed, of course. And since I've not done the research which Richard clearly has done, treat his comments on this subject as more authoritative than mine. } When you have an SQL with a WHERE clause that could eliminate fragments, that } elimination does always occur, regardless of how the query is passed to the } engine. If you build up a string for the SQL with the program variables } embedded, your sqexplain.out will show fragments successfully eliminated. } Any other method will produce an sqexplain.out that shows "...fragments } (ALL)". However sqexplain.out is just doing the best it can with the } information at its disposal. It's actually wrong. } } There are three methods of writing the SQL: (1) embedding it with program } variables, (2) preparing with placeholders, and (3) preparing from a string. } } (1) Embedded SQL } eg SELECT * FROM customer WHERE cust_id = p_var } This method is what you have, and of course, the slowest. But it is only } slow because it has to re-optimise and parse the query with each invocation. } } (2) Preparing with placeholders. } eg PREPARE p_txt FROM "SELECT * FROM customer WHERE cust_id = ?" } This method prepares the SQL once - but the actual execution of the query is } identical to (1) above. If you don't believe me, run both and look at the } output of onstat -g sql 'session_id'. Both show the 'Last parsed SQL' as } something like: } SELECT * FROM customer WHERE cust_id = ? } In other words, the compilation of the 4GL seems to break the SQL in (1) down } to the same as (2) anyway! Which makes sense, when you think about it. } } (3) Preparing from a string. } eg LET sql_stat = "SELECT * FROM customer WHERE cust_id = ", p_var } PREPARE p_txt FROM sql_stat } } This method produces the most meaningful sqexplain.out, and _appears_ to } eliminate fragments more successfully than the others. But, of course, it means that the statement has to be prepared every time you change the value of p_var, just as it is when you use implicit or explicit placeholders (method 1 converts the query string to 'SELECT * FROM customer WHERE cust_id = ?', thereby implicitly using placeholders which method 2 uses explicitly). } But all three methods do eliminate fragments. It's just that the first two } cannot do it when the sqexplain.out is produced because the column on which } the table is fragmented is missing. Consequently the engine has to set up } all the necessary memory structures and control threads for any query - in } other words, one for every fragment. I think this means that the fragment elimination occurs even when I4GL is run against the 7.x engine, but that you will not necessarily see that this is the case by studying the SET EXPLAIN output. } But if you can look at the output of onstat -g ses 'session_id' whilst it is } actually running, you will see that only the necessary fragments are being } scanned - the others scan threads are eliminated when the variable is } provided. This is what I meant by two-phase optimisation. The threads will } look something like this: } } tid name rstcb flags curstk status } 740893 sqlexec 313ee120 Y--P--- 2612 cond wait(notify_MC1) } 743627 group_1. 313f3964 ------- 1828 sleeping(secs: 3) } 743628 scan_2.0 313f5b04 Y------ 1444 cond wait(await_MC2) } 743629 scan_2.1 313f30fc Y------ 1444 cond wait(await_MC2) } 743630 scan_2.2 37219d7c ----R-- 1444 running } 743631 scan_2.3 313ea214 Y------ 1444 cond wait(await_MC2) } 743632 scan_2.4 313f02c0 Y------ 1444 cond wait(await_MC2) } 743633 scan_2.5 313e780c Y------ 1444 cond wait(await_MC2) } 743634 scan_2.6 313ed484 Y------ 1444 cond wait(await_MC2) } 743635 scan_2.7 313ecc1c Y------ 1444 cond wait(await_MC2) } } Basically this shows that only fragment #2 is required for this query. All } others (#0,#1,#3 thru #7) have been immediately marked as "Done". I believe } 'await_MC2' means "Finished, waiting for other threads at level 2 to } complete." But don't quote me on that. } } In other words: } With only partial information, the threads are created and the control blocks@@NL