Skip to Content

Dynamic SQL for repserver. Is is stable? cache_size settings?

A few questions about "Dynamic SQL" (which is where you "prepare" an SQL statement once, then submit only the literals for each execution. See "Dynamic SQL for Enhanced Replication Server Performance" )

Given that most a single delete/update operation on the primary db (er, active) can translate to thousands of per row commands at the DSI connection, this seems like it could be really helpful in those situations.

Has anyone one experienced any stability problems using this? Any other gotchas?

I notice the at repdefs have Dynamic SQL enabled by default, but then there's also the option of enabling "dynamic_sql" at the server or DSI connection level, but Dynamic SQL is off by default at a server and connection level.

I'm guessing that having "Dynamic SQL" on at the repdef level means it's only on if it's also turned on at the server or connection level (the doc's don't have info on this) Is that a good guess?

Any ideas about setting dynamic_sql_cache_size? How do you know if you've set it too big? (Ie., what do you look for in ASE?)

Also, it seems like setting dynamic_sql_cache_management to MRU would be better than the default of "fixed" (fixed="specifies that once the dynamic_sql_cache_size is reached, allocation for new dynamic SQL statements stops")

Thanks in advance

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Aug 23, 2017 at 09:55 PM

    Can't recall any issues using dynamic sql ... then again ... can't remember the last time I even considered it (99% of my RDBs have been ASE, with the vast majority of ASE's in recent years being ASE 15/16 with statement-cache/literal-autoparam enabled).


    Assuming replication into ASE 15.x/16.x, a big question (for me) would be ... which is faster and/or more efficient ... SRS/dynamic-sql vs ASE/statement-cache/literal-autoparam. [I've always leaned towards letting ASE use statement cache; probably wouldn't hurt to run some tests with the latest/greatest versions of ASE and SRS to see if there's any noticeable diff in performance.]

    Obviously (?) if ASE doesn't have statement cache (and literal autoparam) enabled then enabling dynamic sql in SRS should improve performance from an ASE perspective (ie, elimination of compilation overhead for invocations #2+), leaving the question of what kind, if any, performance hit would be seen in SRS (additional memory usage - perhaps; additional cpu - ??).

    If the RDS is something other than ASE, and the equivalent of statement cache doesn't exist, but the equivalent of prepared statements is supported, then (again, obviously) enabling SRS dynamic sql sounds like an easy decision.


    I'd expect the server-wide setting to have the lowest precedence, followed by the DSI/connection level setting, followed by the repdef level setting (ie, DSI/connection setting overrides server; repdef overrides DSI/connection and server). [Easy enough to test eh.]


    dynamic_sql_cache_size is going to depend on the number of distinct commands coming through the DSI ("Duh, Mark!" ?), so would likely have to tweak based on the various SRS counters associated with dynamic sql: DSINoDsql*, DSIEDsql* and DSIENoDsql* counters.

    And, yeah, MRU would definitely be better than fixed.

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 05, 2017 at 02:33 PM

    Thanks for the answer.

    FYI, I have an environment that's in progress migrating from ASE 15.5 to 16. We have a few primary databases for database replication definition/subscriptions where the databases replicate a lot of large varbinary fields. We recently noticed that the large varbinary values were getting included in the "where" clauses of the replicated transactions. Ie., the repserver (15.7) wasn't automatically figuring out the proper primary key for the table in the absence of a table specific repdef.

    It turns out you need an ASE 16 primary db for the repserver to automatically figure out primary keys for tables in a db repdef. And to make matters worse, on the replicate server, the statement cache auto literal param feature wasn't parameterizing the varbinary literals causing huge SQL statements to get cached (including huge varbinary literals in the where clauses)

    We also had problems where a regular DSI subscription replicate connection, running a simple update statement against a table with a 2 field clustered index, occasionally decided not to use the index for the update (this is for a large table). These table scan updates essentially caused replication to hang (every 2-3 weeks). When we turned off the statement cache, the problem stopped. So statement caches are off for now.

    So I'm going to wait until we're on ASE 16 for the primary databases, then I'll retry statement caching on the subscription replicates. If we still have problems with statement caching, maybe we'll try the repserver dynamic sql option.

    Add comment
    10|10000 characters needed characters exceeded