NHibernate and SQLite in-memory database

I’m implementing and in-memory database-based unit test framework for an application. I hope it speeds up unit tests. Unfortunately I have to redesign my entity repositories to support in-memory behavior. As far as I can learn from SUT behavior, SQLite will create a brand new database when

• you close the session
• you commit or close a transaction

It means you can’t use using(ISession) and using(ITransaction transaction){ … transaction.Commit() } constructs in the repository.

Improve SQL performance

I’m using Microsoft SQL Server 2008 Enterprise Edition on my developer machine. I used to work with SQL Server 2005 Developer Edition before.

I recognized that my machine replied unusually slowly with version 2008. The solution was setting a memory limit and processor affinity for the server. Now it is limited to 400 MB RAM and I assigned the IO and computation to different cores. It works fine and the performance has increased.

PS: If you are using Entity Framework, consider the warm-up time of the framework. Just like .NET, the first queries will be ~10 times slower and the subsequent queries will be much faster.

Importing lists to MSSQL2008

I had to import a list of country names to an SQL table using MSSQL 2008. I found a list in CVS format here:

http://tobiasconradi.com/geography/

Good, MSSQL 2008 can import from CVS. I set the language codes, delimiters, headers, even the column lengths, and… the import failed. I tried to parse CVS with different setting, but I was unable to execute the task without errors.

Finally I tried Excel 2007. I opened the CVS file with Excel, but character encoding wasn’t right. So I found the Data tab and the From text button. It’s a very convenient and fast way to import CVS. I managed to import the whole CVS file to Excel and I imported the list with the Excel file provider. Now I have beautiful list of countries in numerous languages.

You can download the country list script for SQL Server 2008. It contains two views for English and Hungarian country names.

Link: Country list (.sql)

SQL Server service not starting

I had a strange experience recently. I tried to connect to a SQL Server instance and Management Studio stopped working and finally Windows Vista crashed. I couldn't start SQL Server after restart. I got the following messages in Windows Application Error Log:

1. TDSSNIClient initialization failed with error 0xffffffff, status code 0x80.
2. TDSSNIClient initialization failed with error 0xffffffff, status code 0x1.
3. Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
4. SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Solution:

1. Set SQL instance to run as Local System Account
2. Stop SQL Server
3. Open "SQL Server Surface Area Configuration" and disable remote connection
4. Start SQL Server

Now it should work.

Resolution:

There are very useful blog entries in SQL Protocols blog: Error Messages of SQL Server 2005 Start Up Failure, and Understanding server-side protocol initialization error codes

Messages from 2 to 4 are generic start up failure messages, you can ignore them. The only meaningful message is:

TDSSNIClient initialization failed with error 0xffffffff, status code 0x80.

Error code 0xffffffff means nothing. If error code was 0x7e, we could do the following:

1. 0x7e = 126
2. c:\> net helpmsg 126
   
   The specified module could not be found.

We have the reason now. Let's see what is status code 0x80.

Status Code (Hexa)	Status Code (Dec)	Protocol Area
0x03 0x40-0x4F	3 (0x03) 64-79 (0x40-0x4F)	Shared Memory
0x09-0x1E	6-30 (0x09-0x1E)	TCP/IP
0x1F-0x23	31-35 (0x1F-0x23)	DAC
0x35 0x50-0x5F	53 (0x35) 80-95 (0x50-0x5F)	Named Pipes
0x36 0x60-0x6F	54 (0x36) 96-111 (0x60-0x6F)	VIA
0x70-0x7F	112-127 (0x70-0x7F)	HTTP
0x38 0x80-0x8F	56 (0x38) 128-143 (0x80-0x8F)	SSL
0x90-0x9F	144-159 (0x90-0x9F)	General

So it means I have a problem with SSL. Some typical, more specific status codes:

Status Code	Description
0x03	Error starting shared memory support
0x04	All protocols disabled
0x0A	Unable to initialize the TCP/IP listener
0x1C	Server configured to listen on a specific IP address in a cluster environment
0x1E	Duplicate IP address detected in network
0x35	Error starting named pipe support
0x36	Error starting VIA support
0x38	Error obtaining or using the Certificate for SSL
0x3A	Unable to initialize the communication listeners
0x40	Unable to initialize the Shared Memory listener
0x50	Unable to initialize the Named Pipe listener
0x60	Unable to initialize the VIA listener
0x70	Unable to initialize the HTTP listener
0x80	Unable to initialize SSL support

DB optimization

Someone asked me to help him optimize a SQL operation. He said he is using SQLite database but his query is very slow. He tried indices as well, but it takes 1 hour to insert 20 users into his database. It's just 1 minute to insert 7 users without indices. Finally he sent me a query that runs awfully slow (for hours):

SELECT SUM(size) AS s FROM (SELECT DISTINCT hash, size FROM files);

Indices

If you define an index on a column, it takes time to maintain it. In this case it took a lot of time to insert new data into the index structure (usually something like a B* tree). He had to insert hundreds of users in one batch. It can worth disabling or deleting indexes before inserting such an amount of new records once. You can recreate or regenerate the indices after running the whole batch.

DISTINCT

The SQL query above ran very slow because of the DISTINCT keyword. I advised to try it without DISTINCT and the query ran very fast.

Conclusion

Keep in mind that indices can make insert operations very slow and projections and joins can make a select very slow. It worths to measure performance and use real usage statistics to tune your database and queries.