SQL Server Performance Testing

Comments 0

Share to social media

Clustered indexes and primary keys

The company I work is for is implementing a custom system, built in Visual Studio and running against SQL Server 2000, to replace some old mainframe applications. It’s an all new database design that will meet all of the old application data requirements and a whole slew of new ones. I was responsible for the initial design, which I completed and handed over to a development team. I had been working quite closely with them for some time on the application, but I got pulled in other directions and the app and database had proceeded without me. One day, the development team raised a concern that the database was far too slow to support the anticipated transaction load. I began my investigation.

The problem

The application in question has a few data requirements that are different than the average application. Basically, at its heart, it is a document management system. However, every change to large sections of the data has to be stored historically, so that we have multiple versions of pieces of a given document. Not all of the data stored is recreated for each version. We needed to know that change ‘A’ was included in version ‘1’, but that change ‘B’ was in version ‘3’ and, on a completely different piece of data, read another table, and know that change ‘Q’ was included in version ‘2’. The data looks something like this:

Table 1

 

Version

Value

1

‘A’

2

 

3

‘B’

Table 2

 

Version

Value

1

‘Some other value’

2

‘Q’

3

 

When queries are run, the latest data in Table 1 is ‘B’ from version three and the latest data in Table 2 is ‘Q’ from version 2.

Load comments

About the author

Grant Fritchey

See Profile

Grant Fritchey is a Data Platform MVP with over 30 years' experience in IT, including time spent in support and development. He has worked with SQL Server since 6.0 back in 1995. He has also developed in VB, VB.NET, C#, and Java. Grant has written books for Apress and Simple-Talk. Grant presents at conferences and user groups, large and small, all over the world. Grant volunteers for PASS and is on the Board of Directors as the Immediate Past President. He joined Redgate Software as a product advocate January 2011.

Grant Fritchey's contributions