Re: Data binding

Obviously, I was way out of line here. Sorry about that. Now that Art and David explained how the bind variable works, it makes a lot of sense. Thank guys. I always learn better from my mistakes. Again, sorry for the wrong pointer.... Thanks, Raja Art S. Kagel <kagel@bloomberg.com> wrote in article <35633F2A.399F@bloomberg.com>... > 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 >