Logic Room

Creating Multiple Clustered Index On Table in Sql Server

2008-12-18T17:05:00.000-08:00

Its true. Does not look so?

Before few days while I was struggling with our huge database for management and performance issues, I decided to write a series which will deal with creating high performant web application. The motto is to share my ideas which I developed and found useful in achieving better performance during course of various project I worked on.

The main platform I am using is ASP.NET/C#/SqlServer Trio. But the ideas equally apply to any web development platform and tool.

Problem Defined :
Creating Multiple Clustered Index on Sql Server Table

Why it is needed ?

Our one table is very big (contains around 30 million records currently) and having more than 80 columns (of course not normalized since normalization will force joining for most of the reports which does cut performance to a great extent). We have multiple reports to come from the same table. Some time the report is as short as 10 records.

When we query for some 10 records in this big table, we face performance issue. The query executes in 2-10 minutes.

Initial Solution
So first we created one clustered index on this table which contains two columns. And come out with very fast performing queries which now take 0-1 seconds instead of 2-10 minutes for the same set of records.

But our joy was only for short span. Since we had 15 more reports having different key columns. Multiple indices are possible only through non clustered index(logically ordering of records). We can normally create only one clustered index ( physical ordering of records). The clustered indices used to be much more faster than non clustered once due to obvious reasons. And we need clustered one do get maximum performance.

Sql Server does not allow us to create multiple clustered indices.

Final Solution
We thought and found the way out of this issue as following. And it really worked.

First we created multiple views on the table as was required by our various reports.
Then we added Unique Clustered Indices to these views based on the key fields of our reports. And finally we wrote queries on the table (not on view). The indices we have created on views are actually associated with the table and since they are clustered they give same performance as a clustered index on the table.

And thats what we wanted. Now all our reports are performing very well and fast enough to satisfy us.

All Suggestions/Feedback/Queries highly appreciated.

Sql Server - Tips & Tricks

2008-12-16T10:07:00.000-08:00

Requirement : Creating Identity Column on a table which already contains data.

Need: There may be various reasons. One possible reason could be to create an Index on view of a table.

Problem Described: In one of our project we have a table which contains around 30 million records. There are different reports where we display the records from the same table. Suppose I have ten different reports which uses different columns from the table. In order for better performance for such heavy table I must create appropriate indexes. But if we create ten different indexes including multiple columns it will again become performance bottleneck.

Solution Approached:
So we decided to create multiple VIEWs based on our reports on the same table and decided to create unique clustered indexes for those views. Since clustered indexes store data physically and will update the index on change in base table. It will boost application performance. But here comes another hurdle. Since our table does not contain a primary key as well as no unique column was introduced ( when that table was created ), it was not possible to create index on VIEWs since it requires unique column or key.

It is possible to alter table and add one more column as identity column but it will internally create another table and will move all records from current table to new table with values in identity column and finally delete the previous table. It was not possible because our table contains huge amount of data and we had less disk space for the table to be copied

For that the solution to used is as follows.

1. Alter the table(eg mytable) to add one extra column (eg newintcolumn) which should of type int.
2. use the following code to populate this column for existing data.

declare @cnt int
set @cnt = 1
update mytable set newintcolumn = @cnt , @cnt = @cnt+1

3. Alter the newintcolumn to identity.

This has solved my problem.

All Suggestion/Feedback highly appreciated.

THERE IS ONLY ONE ALLAH, AND MOHAMMAD (PBUH) IS TRUE PROPHET

2008-03-14T19:32:00.000-07:00

Entire Universe, Nature is busy in worship of ALLAH.

2008-03-14T19:28:00.000-07:00

Proof of Allah

2008-03-14T19:24:00.000-07:00

Thirsty Crow

2008-03-11T20:28:00.000-07:00

Nature spreading its arms for you

2008-03-11T20:22:00.000-07:00

Smile of Nature

2008-03-11T20:18:00.000-07:00

Smile Please 2

2008-03-11T20:15:00.000-07:00

Smile please

2008-03-11T20:04:00.000-07:00

Sql Query : Performance

2008-02-17T08:53:00.000-08:00

Improving Sql Query performance,
Here is tips to boost your sql queries when you feel tables are very heavy.
1. Avoid using temporary table.
2. Avoid using unions. joins.
3. Try to query one table only once . Take help of case statement in select clause and where clause.
4. Use Index. put table field in same order in where clause and group by clause , as they are in
Index.
5. Always check for execution plan. Check execution plan for different order of select queries.
6. In execution plan look for index being used or not.
7. In execution plan check which part is taking maximum time and try to reduce that.
8. Avoid group by and grouping clauase.
9. If index is take more time then Rebuild your indexes.
10. Dont totally depend on database utilities to prepare report format. Divide your work so that
some filtering, ordering, grouping, aggregating be done from dot net code behind side.