What exactly does PostgreSQL (or other databases) do internally when you “prepare” a query rather than just running it directly?

When I face a huge number of repeated INSERTs in a loop, I tend to first create a “prepare skeleton query” prior to the loop, and in the loop, I simply “execute” this prepared query and send it all the values. I heard long ago, and can understand in some abstract sense, that this is more optimized than just having the loop with a normal parameterized query in it.

However, I don’t understand what exactly PG is doing that makes it so much faster. If it even is so much faster. I frankly have never done any real benchmarks to compare the two different methods.

Also, shouldn’t PG be “warmed up” to a query once you repeat it several (let alone many) times in very short succession, and perhaps do the same thing as I do manually, but internally, when I just use a normal parameterized query?

I find myself constantly second-guessing what the database is up to internally. I have no real grasp of how “smart” it is. I fear that I’m doing a lot of things which are meaningless because it’s already taken care of internally by its smart code.

Maybe “preparing” and “executing” is an archaic practice which has no real benefit these days?

It sounds like PG is basically allocating resources to “prepare” for upcoming huge amounts of similar INSERT queries, but I don’t understand what exactly it would do differently compared to just executing them one by one. I also don’t understand what the point would be of a non-INSERT prepared query/statement.

PS: Just to clear up any confusion: I never use non-parameterized queries, whether they are prepared or not. A lot of people confuse “parameterized queries” and “prepared statements”. Even I called the latter “prepared queries” in this question…

Go to Source
Author: Jevontay Rohler

How to Overcome a MYSQL Temporary Write File Failure

If you’re upgrading your website and you keep getting a “Temporary write file failure” when you run a website upgrade script and you know you did everything right, what can be done inside of your PHPmyAdmin MYSQL dashboard to make the upgrade script work? I’ve enabled necessary file permissions and the upgrade script is still saying I have a MYSQL temporary write file failure.

Anyone ever experience this before and overcame it? If so, what steps did you take to overcome it?

Go to Source
Author: DNN