Re: Data binding

Steve Romankiw <sromankiw@execrisk.com> wrote in article <6js97t$ro3$1@news.xmission.com>... > > 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? The answer is no. > Q: Are factors such as selectivity and distributions affected when Bind > Variables are used? No. > Q: When the does the value of the Bind Variable get resolved? Before passing the SQL to the engine. > Q: Does your answer change depending on your communication layer, ODBC > vs Native drivers? Not that I know off. Let me know if you find differently. > > Because of our SQLBase days, we have taken the direction of avoiding > bind variables when going against our Informix ODS. > You can safely use them in Informix. Welcome. > SteveR > > -- > ----------------------------------------------------------------- > Steve Romankiw + > Executive Risk Inc. + > DBA + email: sromankiw@execrisk.com > 82 Hopmeadow Street + work: (860) 408-2474 > Simsbury,CT 06070 + fax: (860) 408-2139 > ----------------------------------------------------------------- > > >