Beta
×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

MSS Code Factory is moving right along

msobkow (48369) writes | more than 2 years ago

User Journal 1

As you can see from the MSS Code Factory project site, things are progressing steadily with my pet project. I've just finished spending a couple of weeks reworking the PostgreSQL database IOs to use PreparedStatements wherever possible instead of pure dynamic SQL. At this point, dynamic SQL is only used for cursor-based reads and index queries which reference nullable columns; all other queries and accessors use prepared statements (stat

As you can see from the MSS Code Factory project site, things are progressing steadily with my pet project. I've just finished spending a couple of weeks reworking the PostgreSQL database IOs to use PreparedStatements wherever possible instead of pure dynamic SQL. At this point, dynamic SQL is only used for cursor-based reads and index queries which reference nullable columns; all other queries and accessors use prepared statements (static SQL.)

I haven't tested the performance of this new layer with PostgreSQL, and don't intend to compare performance of dynamic and static SQL as it would require keeping copies of and debugging both versions of the code. I know from previous experience with DB/2 UDB that using PreparedStatements can result in an 80% overall performance improvement for something like loading a model into a relational database.

Unfortunately most of the performance benefits would be lost when using the code for a web server, because you have to releasePreparedStatements() at the end of each web page served, because there is the possibility that a particular vendor's implementation of PreparedStatements might have data associated with it on the server end of the connection, and the connection has to be released after serving the page.

One of the biggest advantages of switching to static SQL is that parameter binding with PreparedStatements can handle variables up to the maximum size for the type, whereas dynamic SQL is limited by the size of the statement buffer accepted by the database (which used to be a significant limitation with DB/2 UDB 7.2, though I've no doubt that limit has been expanded or eliminated.)

A key point of the use of static SQL is that the only difference between the different databases now is the specific SQL functions used to convert strings to date-time types, so I'm going to be rolling out the support for the commercial databases under GPLv3 after all, rather than trying to leverage them for profit. The differences are just too negligable for me to believe anyone would pay for the privelege of using a commercial database.

cancel ×

1 comment

Sorry! There are no comments related to the filter you selected.

Prepared Statements (0)

Anonymous Coward | more than 2 years ago | (#40666771)

A lot of the benefit in prepared statements is on the security side of things these days: no more escaping or filtering or other BS. Most of the db interface libraries using them Do The Right Thing without you having to jump through hoops these days (ah, memories of perl dbi turning the string "1997-10-10" into the integer 1977 and trying to store that as a date). Check to make sure that the library you use ACTUALLY uses the underlying database's prepared query system, or else it's just doing the escaping and filtering BS for you, and you get no speedup benefit at all. I ended up writing my own PHP db class to fix the bullshit in their postgresql implementation of prepared statements (mine accepts named parameters using :foo, can reuse named parameters, does all this by converting the mapping the named parameters to numbers for the underlying postgres PREPARE STATEMENT and EXECUTE commands, and most importantly, doesn't shit itself screaming about unused values when I pass it $_REQUEST). That I wrote it to do an entire prepare/query/fetch(one|all) in one line of code... that's just icing.

PostgreSQL releases all prepared statements either at the end of a session (not transaction) or when manually released. Even so, if you use the same query twice in a page, you benefit from the speedup. Especially so, for queries in loops.

Also, anyone paying to use a commercial database would probably be happy to pay you for a patch to set use_oracle=1. Especially when they can blame you if something goes wrong.

Check for New Comments
Slashdot Login

Need an Account?

Forgot your password?

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>