Skip to main content
12 votes

Microsoft SQL Server: Prepared Statements

Prepared statements are supported in SQL Server, but you are probably having a hard time finding examples of using them because they aren't really necessary. You could call sp_prepare to get a ...
Solomon Rutzky's user avatar
7 votes

Getting Error : [Err] 1615 - Prepared statement needs to be re-prepared with MySQL 5.6.30

This seems to be an on-going issue Views are messy to handle with Dynamic SQL Earliest Bug was Cannot create VIEWs in prepared statements from 11 years ago. There was a patch put in to address it. ...
RolandoMySQLDBA's user avatar
4 votes
Accepted

Tracking errors from prepared SQL statements

The code you have does return errors from prepared statements. The below PowerShell executes a prepared statement against SQL Server that will intentionally fail. This appears in Profiler with RPC:...
HandyD's user avatar
  • 9,982
4 votes
Accepted

Preparing query in postgreSQL with variable table name?

To write procedural code and use variables with PostgreSQL, the most common way is to use the plpgsql language, in a function or in a DO block. Executing Dynamic Commands in the documentation has all ...
Daniel Vérité's user avatar
4 votes
Accepted

Prepared statements with pgBouncer

The error in session pooling mode indicates that you are doing something different that is not connected with pgBouncer: if the application has the same session all the time, the only explanation for ...
Laurenz Albe's user avatar
  • 53.5k
4 votes
Accepted

Should a partial index with WHERE NOT NULL be used if queries use a JDBC prepared statement?

You can easily try it out with the following script: CREATE TABLE my (col1) AS SELECT * FROM generate_series(1, 100000); ANALYZE my; CREATE INDEX my_null ON my (col1) WHERE col1 IS NOT NULL; /* ...
Laurenz Albe's user avatar
  • 53.5k
3 votes
Accepted

Catching slow queries with prepared statements

It's In There Extended events has the rpc completed class: SELECT * FROM sys.dm_xe_objects AS dxo WHERE dxo.name = 'rpc_completed'; If you need to show the parameters used, you may need to try one ...
Erik Darling's user avatar
  • 41.9k
3 votes
Accepted

Persisting Prepared Statements

If I understand you correctly, you want to use functions. Those are stored (with their code), their execution plans (may be) cached and so on. Plan caching is not so simple as it first sounds. ...
András Váczi's user avatar
3 votes

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

Actually, the difference between a parameterized statement and a prepared statement is not that big in PostgreSQL: the former is internally an unnamed prepared statement, and the main difference is ...
Laurenz Albe's user avatar
  • 53.5k
2 votes
Accepted

Alternative to Dynamic SQL inside trigger?

If the list things to check is constant, then spell them out rather than constructing the tests. (not viable) But, presumably that is not the case... Plan A: Replace the INSERT that needs to call ...
Rick James's user avatar
  • 78.5k
2 votes

Why would a Greenplum procedure not recognize a view change?

Not using Greenplum, but guessing from its PostgreSQL heritage it's probably due to the way PL/pgSQL treats SQL statements: like prepared statements. Those are planned and cached on their first use. ...
Erwin Brandstetter's user avatar
2 votes
Accepted

Preparing CREATE statement

DDL queries, including CREATE statements, cannot be prepared. Moreover, even in DML queries that can be prepared, parameters cannot be used for identifiers. They're allowed at places in the query ...
Daniel Vérité's user avatar
2 votes

Microsoft SQL Server: Prepared Statements

example: declare @p1 int exec sp_prepare @p1 output,N'@P0 int',N' select * from sys.tables where object_id>@P0' exec sp_execute @P1,'0' EXEC sp_unprepare @P1;
slade.liu's user avatar
2 votes
Accepted

Performance Monitoring for Prepared SQL?

You would most likely need to trap statement-level events since the "completed" RPC call for sp_execute isn't going to show the batch itself. Also, you need more than just the SPID. Session IDs get ...
Solomon Rutzky's user avatar
2 votes

Is there a way to run parameterized update statements using only DB2 commandline and bash?

If you are on 10.1 or later you can use INGEST. It is almost as fast as load, more feature rich and has less impact on a running system. First, you need to enable it by creating control tables (if you ...
Lennart - Slava Ukraini's user avatar
2 votes

Speed up Postgres OLTP - eliminate plan time

Just because it is parameterized doesn't mean it uses prepared plans. It is up to the driver to implement that. For example, some drivers will never use prepared plans and will just interpolate the ...
jjanes's user avatar
  • 40.2k
2 votes

Prepared statements with pgBouncer

With transaction pooling, you should disable prepared statements in the application, says the faq
Gerard H. Pille's user avatar
2 votes
Accepted

Error: type of parameter n (X) does not match that when preparing the plan (Y)

It doesn't matter the data type, the ELSE block will always fail. Actually, the assignment in the ELSE block works fine. The error message CONTEXT: PL/pgSQL function test(integer) line 15 at RETURN ...
Erwin Brandstetter's user avatar
2 votes
Accepted

MySQL statement to update repeating events

DEMO CREATE TABLE `original_shows_24` ( `id` int NOT NULL AUTO_INCREMENT, `date_start` date NOT NULL, `date_end` date NOT NULL, `name` varchar(200) NOT NULL, `slug` varchar(128) NOT NULL, ...
Akina's user avatar
  • 20k
1 vote

Do prepared statements give a performance advantage when running multiple times with similar parameters?

Reusing a prepared statement saves your the SQL parsing and compilation overhead. For simple statements it is quite small, so it wouldn't be surprising to not see much improvement on a lightly loaded ...
mustaccio's user avatar
  • 26.2k
1 vote
Accepted

Postgres changes query plan for prepared statement to something poor after multiple invocations

It thinks the generic plan you show (with the $1 in it) will be slightly faster than the custom plan (with the actual value in it), 104.57 vs 105.26. So after running a number of custom plans and ...
jjanes's user avatar
  • 40.2k
1 vote
Accepted

How to create dynamic columns using prepared statements in MySQL?

This is a possible solution in PHP: $link = mysqli_connect("p:192.168.3.2", "ghp", "azerty", "test"); if (!$link) { error_log ( "Error: Unable to ...
Gerard H. Pille's user avatar
1 vote
Accepted

Postgres sometimes uses sequential scan instead of an index only scan for data that doesn't exist

After 5 consecutive invocations of execute p1('fevent20', 1), it decides to stop coming up with custom plans as they don't seem to be doing any good and so not worth the planning time. And from then ...
jjanes's user avatar
  • 40.2k
1 vote

Python MySQL prepared statements slower than regular queries

Much faster is to build a multi-row insert: INSERT INTO t (a,b) VALUES (1,2), (11,23), (111,234), ... On the MySQL side, this runs about 10 times as fast (for 100-1000 rows) than ...
Rick James's user avatar
  • 78.5k
1 vote

MySql Error #1054 when adding new column to table using prepared statement

Remember that building SQL in this way is just building a String that happens to contain some text that your DBMS understands. Your new column name, >>lot<<, is just part of that SQL: SET @...
Phill  W.'s user avatar
  • 8,850
1 vote

When do I need to DEALLOCATE a prepared statement in a MySQL stored procedure?

[https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html] says: A prepared statement is specific to the session in which it was created. If you terminate a session without ...
pifor's user avatar
  • 1,166
1 vote

Multiple statement conditions using OR AND within the query

For a start: You are missing a number of parentheses (in any case). Then: You need to combine different records into a single result. One way has been shown by @Akina. Alternatively, you can run ...
Abecee's user avatar
  • 610
1 vote
Accepted

Multiple statement conditions using OR AND within the query

I have this survey table and I'm trying to gather the information of userid who answer Strongly agree to the question of I'm trying to watch my weight OR I love drinking Coffee Black AND must have ...
Akina's user avatar
  • 20k
1 vote

Reset global MySQL status variables

After you are finished with a prepared statement, and executing it, be sure to deallocate_prepare_statement. I think this will keep the counter from ever climbing to the limit.
Rick James's user avatar
  • 78.5k

Only top scored, non community-wiki answers of a minimum length are eligible