Re^2: perl dbi, prepare function using place holder

Replies are listed 'Best First'.
Re^3: perl dbi, prepare function using place holder by Marshall (Canon) on Aug 25, 2016 at 17:23 UTC
Yes. The prepare statement can potentially do a lot of work figuring out how to actually perform the SQL statement in the most efficient manner. Even in this simple INSERT example, say Table1 has an index based upon "ip" and Table2 doesn't or is indexed in some other way. The insert for Table1 requires a different set of functionality (also update the index) than for Table2. Using a different Table is a lot more involved than generically inserting different column values. A fancy SELECT statement could have vastly differing execution strategies depending upon exactly what table(s) it is operating upon. Essentially there are limits to what the DB can be "prepared" in advance to handle.	[reply]
Re^4: perl dbi, prepare function using place holder by chacham (Prior) on Aug 26, 2016 at 20:16 UTC
Well, that is sort of true. SQL Server has bind variable peeking which can change the plan. And Oracle recently introduced changing plans while a statement is running. Regardless, the gist of what you wrote is true. Part of the purpose of the preparation is to generate a (somewhat generic) query plan, which does not change very often. Other reasons include, query rewrite only engages once and a handle can be passed for each executions used instead of a statement since the same statement is reused.	[reply]
Re^4: perl dbi, prepare function using place holder by leostereo (Beadle) on Sep 02, 2016 at 14:25 UTC
Thanks for the explanaiton !!	[reply]