Re: Data binding - question

As long as we are on the subject, A function gets called about 20,000 times a day, uses same select statements over and over with different value and this is how I have implemented it. static int x = FALSE, counter = 0; func () { if ( !x ) PREPARE DECLARE x = TRUE; if ( counter%50 == 0) OPEN else OPEN WITH REOPTIMIZATION FETCH counter++ } Am I on the right track? I do not CLOSE cursor assuming that next OPEN will re-open the cursor. What is the overhead of not freeing the statement or cursor? The function is part of a 24x7 server. Thank in advance.... Raja Idiot <rajam@worldnet.att.net> wrote in article <6k2055$ktr@bgtnsc02.worldnet.att.net>... > 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 > > >