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.

asked 09 Jan '12, 08:25

David's gravatar image

David
21191120
accept rate: 50%

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?

(09 Jan '12, 09:51) RussC_FromSAP
Replies hidden

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.

(10 Jan '12, 05:32) David
2

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.

(10 Jan '12, 10:04) RussC_FromSAP
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×278
×241

question asked: 09 Jan '12, 08:25

question was seen: 680 times

last updated: 10 Jan '12, 10:04