Just briefly: Why do we even care for prepared statements? For stateless web applications the benefits are mainly protection against SQL injection and better readability. In some rare cases in theory also better performance if the same statement is executed multiple times in the same request. The disadvantage is that for most web applications queries are rarely executed more than once and therefore the separation of parsing/planning and execution just means that there is an additional roundtrip to the database. Also in very long running database sessions the prepared query plan could get outdated, which could also spell disaster for performance. or in other words: prepared statements are no silver bullet.

The problem described in the above blog post is of course since the idea behind prepared statements is to separate the parsing and planning stage from the actual data that is to be used in the WHERE clause of the query. Modern cost based optimizers keep detailed statistics about data distribution, which is difficult to leverage if there is no data available during the planning stage. This blog post however focuses on another aspect that is not yet relevant to MySQL, since there is no support for partial indexes in MySQL just yet. However RDBMS like PostgreSQL do support partial indexes. Again since the data is missing during the planning stage, the database is unable to determine if an existing partial index matches.

So what can be done to fix this if we do want to stick with prepared statements? On the user side one thing one can do is move the relevant filtering data into the prepared statement itself. This of course means that you loose the benefit of the prepared statement for the relevant pieces of data, but it might be an acceptable balance. Of course if this data contains user supplied data, you now need to manually make sure that the data is safe.

The other approach is simply switching to emulated prepared statements, which most database layers support (like PDO). This also fixes the issue of adding a second round trip to the database for every execution. However it means you put yourself at the mercy of the parser embedded in the database layer you are using. Actually in the case of PDO there is little chance to get around that anyways. Worse yet, the PDO parser is known to be buggy by design.

On the database one approach that has been taken by Oracle since I think 9i is to delay the planning stage until the first execution. The query plan is then optimized for the first data set. I do not know how this works in detail, as I presume that if the plan uses a partial index as a covering index and the next data set is not contained in the partial index, that this approach would fail. However it gets the job done for people that just want to use prepared statements for one shot execution to get SQL injection protection and better readability.

Another approach that I do not think any RDBMS has taken (probably because its either hard or not feasible .. or both) is to keep multiple query plans around. In this scenario in the initial planning phase the database would also store some simple parameters that define if an existing query plan will match or not. If the plan does not, then a new plan is generated, while the old plan is kept around. This way a single prepared statement could eventually keep multiple query plans around. This would also solve the issue with partial indexes being used as covering indexes.

Yet another approach, which would be less interesting for short running sessions as are common in PHP applications, would be that if the query is executed and the performance is slower than expected, that this then triggers a replanning. Potentially also keeping the old plan for old data sets. This would also solve the issue of long running sessions having out of date plans.

Overall it seems for stateless web applications the best approach is using a database layer with good prepared statement emulation support. I hope that PDO, will one day fit this criteria. Until then the MDB2 parser seems to do a much better job. Though MDB2 is of course implemented in PHP, which carries its own performance overhead and is not E_STRICT compliant due to the PHP4 compatibility.

Here is the original query (note that I am using PDO which supports Oracle style named placeholders for all drivers including MySQL):

INSERT INTO user_tab_portlets (tab_id, portlet_id, pos, col, is_open, config)
            (SELECT :tab_id, :portlet_id, COALESCE(MIN(utp.pos)-1, -1), :col, :is_open, :config
                FROM user_tabs ut LEFT JOIN user_tab_portlets utp ON (ut.id = utp.tab_id)
                WHERE ut.id = :tab_id AND (utp.col IS NULL OR utp.col = :col)
                GROUP BY utp.tab_id, utp.col)

I sort of expected the "utp.col IS NULL OR utp.col = :col" to work out fine for the case when there are no portlets yet in the left column on the given tab, since due to the LEFT JOIN the "IS NULL" part should fire (the col is not nullable). As it turns out a more readable and actually working version of my query would look like this:

INSERT INTO user_tab_portlets (tab_id, portlet_id, pos, col, is_open, config)
            (SELECT :tab_id, :portlet_id, COALESCE(MIN(utp.pos)-1, -1), :col, :is_open, :config
                FROM user_tabs ut LEFT JOIN user_tab_portlets utp ON (ut.id = utp.tab_id AND utp.col = :col)
                WHERE ut.id = :tab_id
                GROUP BY utp.tab_id, utp.col)

Anyways, if anyone has some pointers on how to better do an ordered list in SQL please let me know. I felt quite good when I figured this one out :)

Dieses Wochenende (26./27. April) war ich in Hamburg auf der PHP Unconference 2008, organisiert von der PHP Usergroup Hamburg. Tobias Struckmeier war so lieb mich und mindestens 6 andere Leute für das Wochenende bei sich aufzunehmen, wodurch mir dir nervige Hotelsuche oder das Übernachten in der Uni erspart blieb. Außerdem wurde der Abend etwas länger; nachts um 1 saßen wir nach der Heimfahrt von der Recycel Bar vor den Laptops und informierten uns gegenseitig über coole selbstgebaute Software :)

Sonnabend in der Eröffnungssession wurden die Sessions festgelegt - schön basisdemokratisch nach Anzahl der Wünsche der Teilnehmer. Meinen Vortrag über PEAR2 wollten nicht genug Leute (<8) hören, das Angebort für den SPARQL bekam volle 9 Punkte und wurde als auf den Sonntag 16:00 in den letzten Slot gesetzt.

Den so "gewonnen" Freiraum konnte ich sehr schön mit dem Beisitzen in den anderen Sessions füllen und hier und da was Neues aufschnappen. Das tolle an der Unconf sind eigentlich - wie überall - die Gespräche zwischen den Vorträgen, von denen es reichlich interessante gab.

Ich wurde allerdings immer mehr in dem Gefühl bestärkt, auf den falschen Gebieten tätig zu sein: Der PEAR-Talk wurde nicht genommen, das PEAR-Installerbuch in der Verlosung als zweitletztes genommen und mein SPARQL-Vortrag am Sonntagnachmittag wollten ganze drei Leute hören... Nach der Vorstellung von DBpedia entwickelte sich aber doch ein interessantes Gespräch über die Verfügbarkeit von Daten über die Menschen. Sie existieren sowieso im Netz, allerdings haben nur ... Organisationen die Mittel, sie zusammenzuführen. Ergebnis: Damit alle die gleichen Chancen haben, müssen Informationen für alle verfügbar und frei sein. Denn irgendjemand hat immer Zugriff darauf - warum also nicht alle?

Zusammenfassend war es ein sehr interessantes und schönes Wochenende. Als zum Schluß darum gebeten wurde, Kritik und Verbesserungsvorschläge einzureichen fiel mir absolut nichts ein - perfekt!