Celexsa Developer Knowledgebase

Tag: Performance

  • How to Reduce the CPU Overhead of Dynamic SQL

    Dynamic SQL is a good choice for catch-all type queries, but you have to be careful how you execute the dynamic string.

    For frequently executed queries, “EXEC sp_executesql @sql” is a good choice but at the risk of encountering parameter sniffing issues.

    For queries that aren’t executed very often, “EXEC (@sql)” can be used, but you could end up with a bloated plan cache with lots of single-use execution plans.

    Let’s compare the performance difference between “EXEC (@sql)” and “EXEC sp_executesql @sql” for a frequently executed query.

    (more…)

  • How to delete large records in batches

    How to delete large records in batches

    When performing long-running modifications, I’m sure many of you enjoy using batches to increase concurrency. But I want to talk about a pitfall to be aware of. If you’re not careful, the method you use to implement batching can actually worsen concurrency.

    Why Use Batches?

    Even without an explicit transaction, all SQL statements are atomic – changes are all or nothing. So when you have long-running modifications to make, locks on data can be held for the duration of your query and that can be too long. Especially if your changes are intended for live databases.

    (more…)