RE: 4GL Database Feature Question

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 versino 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. > > Yours, > Jonathan Leffler (johnl@informix.com) #include <witticism.h> > 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. 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 =3D 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 =3D ?" 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 =3D ? 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 =3D "SELECT * FROM customer WHERE cust_id =3D ", 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 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. 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 are set up to scan the whole table. But when that missing piece of = information comes in, some of those threads will be given an early mark = ;-) I hope that helps (and geez I hope I'm right, because I wouldn't ordinarily question anything Mr. Leffler said....) RET +------------------------------------------+ | Richard Thomas | | DBA - Marketing Information Systems | | Optus Communications | | email: richard_thomas@yes.optus.com.au | | Ph: +61 2 9342 7188 | | "My opinions are my opinions" | +------------------------------------------+