Re^3: dbi: prepare vs prepare_cached -- which is best ?

I think this is a poor example since if you were executing the same SQL repeatedly in a loop you would prepare it once and execute it many times.

I am not executing the same SQL. I said "For example, I am having a function which will select a row from a table, depending upon the argument passed to that function."

It depends upon the arguments to the fucntion

Comment on Re^3: dbi: prepare vs prepare_cached -- which is best ?

Replies are listed 'Best First'.
Re^4: dbi: prepare vs prepare_cached -- which is best ? by mje (Curate) on Dec 19, 2008 at 13:24 UTC
If you are not executing the same SQL with the same attributes passed to prepare each time then each call to prepare_cached will give you a new statement handle and your SQL will be parsed each time. You only get an advantage from prepare_cached when the SQL and attributes to prepare are the same as a previous call to prepare_cached. You said If the function is called in a loop, then it will be in-efficient and all I was attempting to say was if you are working with SQL in a loop you can prepare the SQL once and execute it multiple times in the loop but that does not necessarily suggest using prepare_cached instead of prepare. Where prepare_cached might speed things up (although with risks) would be if you called the sub containing the loop repeatedly. The second and subsequent times the sub was called the SQL would not be parsed. I hope see the distinction I was trying to make. prepare_cached is a useful tool but also dangerous unless used properly. I've see code where someone used prepare_cached everywhere just because they thought it was quicker but it a) created hundreds of statement handles and b) used loads of resource in the database and c) did not work reliably because the same statement was used in multiple places when one of the previous instances was still active.	[reply]

Replies are listed 'Best First'.

Re^4: dbi: prepare vs prepare_cached -- which is best ?
by mje (Curate) on Dec 19, 2008 at 13:24 UTC

If you are not executing the same SQL with the same attributes passed to prepare each time then each call to prepare_cached will give you a new statement handle and your SQL will be parsed each time. You only get an advantage from prepare_cached when the SQL and attributes to prepare are the same as a previous call to prepare_cached.

You said If the function is called in a loop, then it will be in-efficient and all I was attempting to say was if you are working with SQL in a loop you can prepare the SQL once and execute it multiple times in the loop but that does not necessarily suggest using prepare_cached instead of prepare. Where prepare_cached might speed things up (although with risks) would be if you called the sub containing the loop repeatedly. The second and subsequent times the sub was called the SQL would not be parsed.

I hope see the distinction I was trying to make.

prepare_cached is a useful tool but also dangerous unless used properly. I've see code where someone used prepare_cached everywhere just because they thought it was quicker but it a) created hundreds of statement handles and b) used loads of resource in the database and c) did not work reliably because the same statement was used in multiple places when one of the previous instances was still active.

[reply]