Re: Data binding

Steve Romankiw wrote: > > Before converting to Informix ODS, our main database platform was > SQLBase. In the SQLBase world, we noticed that the optimizer sometimes > took different paths depending upon the query you submit. For instance, > take the two examples below: > > Ex1: select name, phone_number from customer where name = 'JOHNSON'; > Ex2: select name, phone_number from customer where name = :BindVariable; > > Ex1 explicity lists the literal value. Ex2 uses a bind variable. > > Q: When a query is submitted and goes through the Prepare and Compile > steps, does the optimizer treat the two queries differently? same? Yes. Versions before 7.3 resolved the query path at PREPARE time so, except for an EXECUTE IMMEDIATE (which prepares and executes at the same time), these two queries MUST be treated differently. Since the actual value of the bind variable is unknown at PREPARE time, when it is represented in the PREPAREd statement by a question mark ('?'), the optimizer CANNOT use data distributions to decide on which indexes to use or to make any other query path decisions like fragment elimination or table ordering in joins. It decides as best it can based on the available distributions for other tables and columns. Version 7.3's optimizer does try to defer some of the decision process until the data is bound and the CURSOR OPENed. I have no details about what parts that includes and how successful it is. > Q: Are factors such as selectivity and distributions affected when Bind > Variables are used? See above. > Q: When the does the value of the Bind Variable get resolved? When the CURSOR is opened. > Q: Does your answer change depending on your communication layer, ODBC > vs Native drivers? Only if the comm driver is performing parameter replacement before preparing the statements. I know of none that defer the prepare until after binding in this way. > Because of our SQLBase days, we have taken the direction of avoiding > bind variables when going against our Informix ODS. Do not avoid them, they can make many queries run faster and use fewer resources when used wisely. If you are using the same statement, with different values, over and over and do not use table fragmentation, the time and cycles saved by not PREPARING the statement repeatedly is very significant. Art S. Kagel