performance

Fast and Simple Database App Development and Deployment to Linux in RAD Studio

The Devart company released UniDAC with support for Linux 64-bit platform. UniDAC makes application development and maintenance easier and simpler because the use of the Direct mode in a custom application does not require the installation of client libraries, additional drivers, etc. This helps to avoid the overhead when accessing a DBMS, hence increasing performance.

(more…)

SQL Server Execution Plans

One of the best methods to analyze the query performance is to explore the query execution plan. A query plan or query execution plan is an ordered set of steps utilized to access data in a SQL relational database management system. In this article, we will discuss the basics of the Query Execution Plan feature available in SQL Server. (more…)

What is faster inside SUM & AVG: 0 or NULL?

There are three aggregate functions that are most often used in practice: COUNT, SUM and AVG. The first one has already been discussed previously, while the other two have interesting performance nuances. But first, some theory…

(more…)

How to reduce MSDB size from 42Gb to 200Mb

Recently I’ve got a spare minute to see why an old test server was running too slow… I had nothing to do with this, but I was very anxious to find out what was wrong with the server.

First thing, I opened Resource Monitor and looked at the overall load. The sqlserv.exe process took up 100% of CPU and generated a large disk queue exceeding 300… whereas the number greater than 1 is considered problematic. (more…)

Increasing SQLite Performance

One the major issues a developer encounters when using the SQLite DBMS in his applications is its performance issue.

Perhaps, a classic case everyone gets into when using SQLite for the first time is very slow execution of multiple INSERT/UPDATE/DELETE operations. Indeed, sequential executions of not even thousands, but hundreds of INSERTs into a table may take too long. (more…)

XML, XQuery & Performance Issues

In this article, we will discuss some pitfalls related to XML and XQuery that may lead to performance issues.

The following code will generate a test XML file:
(more…)

What is the fastest way to calculate the record COUNT?

I have always liked simple questions with lots of pitfalls. Here is the one: how do you count the total number of records in a table? At first sight, it’s a snap, but if you dig a little deeper, you can reveal lots of peculiar nuances.

So, let’s start from a simple thing. Do the following queries differ in terms of the end result?

(more…)

SQL Server Typical Maintenance Plans: Automatic Statistics Update

Some time ago, we reviewed the automation of index defrag. Now, it is time to look at statistics.

First of all, what do we need statistics for?

During execution of any query, query optimizer is trying to build an optimal execution plan (within the limits of available information). The plan constitutes the operations sequence, by means of which you can get the result described in the query.

While selecting one or another operation, the query optimizer considers statistics, that describes value distribution for columns within a table or index, as one of the most significant input data resources.

(more…)

Instant File Initialization – Killer Feature for SQL Server

When SQL Server reserves new space on a disk, it initializes the space with zeros. This behavior can be switched off thereby reducing the execution time of some operations and the load on the disk subsystem.

Disk space reservation without initialization is called Instant File Initialization. This feature is not widely known, although its use has become possible starting with SQL Server 2005. (more…)

Using Batch Updates in Delphi Data Access Components

Data amount processed by modern databases grows steadily. In this regard, there is an acute problem – database performance. Insert, Update and Delete operations have to be performed as fast as possible. Therefore Devart provides several solutions to speed up processing of huge amounts of data. So, for example, insertion of a large portion of data to a DB is supported in the Loader. Unfortunately, Loader allows to insert data only – it can’t be used for updating and deleting data.

(more…)