James Wiseman

I love it when I discover something like this…

I’m querying a table in SQL Server that has over 200 million rows. I’m just wanting the row count. So, naturally, I tried the following:

select count(*) from TABLE_NAME

The query took one minute when it was run initially. And select (1) performed no better. (See: SQL count(1) is not better than count(*))

To be fair, I was quite shocked that it took so long to get this value. Surely there was some value stored internally that I could just pluck out in the blink of an eye?

I searched around for this information, and stumbled across this: How can I get sysindexes information in Sql 2005?

So here it is. Takes no time at all to run:

select object_name(i.object_id) as objectName, sum(p.rows) as rowCnt, i.name as indexName
from sys.indexes i
join sys.partitions p on i.object_id = p.object_id
and i.index_id = p.index_id
where i.index_id <= 1
and i.object_id = object_id('TABLE_NAME')
group by i.object_id, i.index_id, i.name


And, if I need, I can strip out the second-last line to get it for all tables.

As it turns out, you can do the equivalent in SQL Server 2000:

select object_name(id), rowcnt
from sysindexes
where indid < 2
and id = object_id('TABLE_NAME')
order by 2

Which will run in 2005 as well, although its legacy, so we must discourage that! You can tell this, because SQL Management Studio doesn’t colour in sysindexs green, but SQL 2000 Query Analyzer does.

Learned something really useful today….

The Problem

Recently I encountered a problem whereby an early-morning database merge background process kept overrunning

A series of stored procedures where taking a series of records from some SQL Server tables, and then merging them across to related table, i.e. applying all records marked as ‘inserts’, ‘updates’ and ‘deletes’. In other words:

MERGEDATA_TABLE1 —Merge–> TABLE1
MERGEDATA_TABLE2 —Merge–> TABLE2

On some days, the stored procedures that were merging larger tables were starting to take significantly longer. Merging 200,000 records into a table 180,000,000 in size took 40 seconds (on a pretty high spec server). However, merging 400,000 (double) into the same table took 40 minutes – sixty times as long for twice the number of records.

The Cause

When the stored procedures were applied to the database, a query plan was generated for them. The optimisations for these plans were based on the content of the tables at that time.

In particular, each of the MERGEDATA_ tables would have been empty, so the optimisations and query plans would have been completely unsuitable.

Static query plans are more suited to stored procedures that are run frequently, and that query tables that do not have vast fluctuations in size.

The Solution

Placing WITH RECOMPILE at the top of each stored procedure to force a recompile whenever it was executed seemed fixed the problem.

This was fine as we were only going to call the procedures once a day. If we were going to call them once a second then we may have had to rethink.

Code

CREATE PROCEDURE Table1_Merge

WITH RECOMPILE

AS

Have you ever found your PC running slowly after playing around with a particularly large SQL Database on you local machine? Well then, it’s possible that you have left the SQL Server ‘Maximum server memory’ setting at its default.

The default is 2,147,483,647 MB – Yes thats  bytes 2,251,799,812,636,672, or about 2 Petabytes.

SQL Server Configure Maximum Memory Usage

I want a PC with that amount of memory!

To configure max memory usage for a particular instance of SQL Server 2005/2008 (and possibly later versions as and when they come ), simply adjust this setting in the above dialog, which can be located by right-clicking your database instance in SQL Management Studio, and selecting Properties

Alternatively, you might sneer at the novice user interface shortcut and decide you want to script it:

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'max server memory (MB)', N'300' RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE


The above code reconfigures the setting to 300 as shown in the second line.

Before .NET 4.0 there was no explicit method to clear a C# StringBuilder object.

We had to resort to one of three approaches:

1. Remove from the start of the StringBuilder by its length:

    myStringBuilder.Remove(0, myStringBuilder.Length);

2. Store the contents in another string and call Replace upon the stringbuilder using the string as a parameter:

    string s = myStringBuilder.ToString();
    myStringBuilder.Replace(s, "");

3. Or simply just set the Length property to zero. This property is not read-only.

    myStringBuilder.Length = 0;

But which is the fastest? Crude (and in no way scientific) tests on my laptop at home gave the following results when looping round this operation 50 million times. All numers are in milliseconds:

1. 5585,    5601,     6037
2. 14945,   14679,    15094
3. 4898,    4805,     5023

So there we have it. Setting the Length directly was the best

C# .NET 4.0

But all this is immaterial if you are using C# .NET 4.0, as Microsoft have kindly extended the StringBuilder obect to include this method:

http://blogs.msdn.com/wriju/archive/2010/03/01/net-framework-4-0-newbie-stringbuilder-new.aspx

But what of its relative performance?

5772,   5476,   5226

So, it would seem from my crude and unscientific tests that for C# 4, the quickest way to clear a StringBuilder object is actually avoid the inbuilt clear method, and set the length directly to zero.

I’ve put the sample code here so that you can test it for yourself:

ClearStringBuilder.cs

The use of

select count(1) from mytable

instead of

select count(*) from mytable

is shrounded in rumour, myth and folklore.