Mobilink SqlServer PreparedStatements performance.

[Former Member]

Hi, I’m using Mobilink 12.0.1, MS SQL Server 2005 Enterprise Edition as consolidated Database and Microsoft native ODBC driver version 2005.90.1399.99. Mobilink synchronization events are written using SQL language.

Monitoring Mobilink activity in SQL Server (using SQL Server Profile Tool) I have noted that every table script event is executed as a different PreparedStatement with a pair of sp_prepexec and sp_unprepare commands.

Considering that synchronization scripts are stable artefacts and that Mobilink keep a pool of open connections to consolidated database, I wonder if there are any way to tune Mobilink to reuse PreparedStatements, reducing, this way, server load.

Thanks in advance.

[Former Member]

Most modern RDBMSes shortcut the preparation of often-prepared statements by caching the prepared statement on the server side (eg. using a simple hash of the statement mapped to the preparation data structures). Can you provide specific performance data supporting your suggestion that MobiLink server should pool prepared statements?

[Former Member]

I don't have concrete performance figures for Statements reuse in SqlServer. Simply, I'm used to the statement-caching facility provided in java development environments (by JDBC drivers or middleware servers) where statement pooling is considered a relevant issue in performance tunning

More about, Microsoft plain documentation about sp_unprepare suggest that execution plan is discarded and should be recalculated in every new sp_prepexec.

[Former Member]

Both SQL Server 2005 (http://msdn.microsoft.com/en-us/library/cc966425.aspx#XSLTsection128121120120) and SQL Server 2008 (http://msdn.microsoft.com/en-us/library/ms181055.aspx) implement plan caching.

Our experiments with MobiLink, conducted years ago, indicated that caching prepared statements provided zero benefit while needlessly complicating our code, so we took the feature out.

Our research at the time confirmed the decision. We learned that caching prepared statements is a selfish thing for an RDBMS client to do, because only the server, with its central knowledge of load, is in a position to decide what plans should be cached for the greater good.