Help MS SQL Indexing: Table structure & Search criteria provided here

[sam4help]

Dears; Greetings

I am new to Indexing; I need a help in indexing this table for better search
I am using ms sql2005; and this is my table structure;

Table Name: trData
Column Name Data Type Allow Nulls
trID numeric(18, 0) NO
trProfileID int NO
trCategoryName nvarchar(50) NO
trLabelName1 nvarchar(300) YES
trLabelName2 nvarchar(300) YES
trLabelName3 nvarchar(300) YES
trLabelName4 nvarchar(300) YES
trLabelName5 nvarchar(300) YES
trLabelName6 nvarchar(300) YES
trLabelName7 nvarchar(300) YES
trLabelName8 nvarchar(300) YES
trLabelName9 nvarchar(300) YES
trLabelName10 nvarchar(300) YES
trLabelName11 nvarchar(300) YES
trOptionName1 bit YES
trOptionName2 bit YES
trExpiryDate datetime YES
trCreatedDate datetime YES
trModifiedDate datetime YES
trOwner nchar(25) YES
trType char(1) YES
trCode varbinary(256) YES

Now; Data is stored dynamically based on category; any of these field can be NULL based on the Category;
and accordingly the search criteria passed also leaves that NULL fields based on the category; see below eg.;

If data belongs to Category AAA then it may be
Category AAA
Name1 009
Name5 HHJHJ
Name6 HKKA
Name11 001

If data belongs to Category BBB then it may be
Category BBB
Name2 123
Name3 XYD
Name6 LLL
Name7 213121
Name8 HHH
Name10 YHJ
Name11 HJAH

Like this the other fields will be NULL in where data is not present;
Now my Search "Where" Clause will contain any of these fields based on Category selected; it can be any combination

trCategoryName
trLabelName1
trLabelName2
trLabelName3
trLabelName4
trLabelName5
trLabelName6
trLabelName7
trLabelName8
trLabelName9
trLabelName10
trLabelName11

I was wondering what would be the best index for this and what field needs to be included.
Would be highly grateful if someone with experience in indexing come forward with support,

Thanking you in advance,

Best Regards,

Sam

 

[gmmastros]

How many rows are in the table? How many distinct categories are there in the table?

Select Count(*) As TotalRows,
       Count(Distinct trCategoryName) As CategoryCount
From   trData

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sam4help]

thanks for the reply,
It is a new database and this will keep increasing;
because this is the main transaction table and the category is the group under which there will be many records.
I am maintaining the Category Master separately here

So we have Category Master

Category1
Category2
Category2

but in transaction table which is trData it will have many records under each Category

Best Regards,
Sam

 

[gmmastros]

It is a new database

Do you have the ability to change the table structure?

The problem here is that you will probably want to search on any of the LabelName columns which poses a big problem for indexes. If you would change your table structure so that the table is normalized, you will get better performance.

For example, instead of:

Id, Label1, Label2, Label3, Label4, etc....

If would be better to have an additional table for the labels, like this:

[tt]trDataLabels
trID Numeric
LabelNumber Int
LabelName VarChar(300)[/tt]

You could then put an index on this table that has labelName and trID (in that order) which will allow you to get much better performance.

The other benefit of this approach is that you are no longer limited to 11 labels, you could have 0 labels, 1 label, 10 labels, 100 labels, or even a million labels, all without having to change the structure of your table.

If you must keep your un-normalized table structure, you will likely NOT be able to use a single index to cover the query and will likely have performance problems when there are many rows in the table.




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sam4help]

Sounds Great George!
Now let me take you through some tables which I do have in this db and what role it plays;

prMaster -> master table to store the category
prID prName (column names)
1 category1
2 category2
3 category3


prData -> table where each category labels are stored
prID prName Label1 Label2 Label3 ..........Label11 (column names)
1 category1 Name Author NULL ExpiryDate
2 category2 Name NULL Submissiondate NULL


Now, i have a input form which captures the data for the trData (the above topic transaction table)
so, based on the category selected the label is displayed; and data is captured from user and saved.

Now, as per your advise you want me to create one more table called trDataLabel;
trID Numeric
LabelNumber Int
LabelName VarChar(300)

so, it goes like this
1. what all details i would be storing in trData then? this is trData entire table
trCategoryName nvarchar(50) Unchecked
trLabelName1 nvarchar(300) Checked
trLabelName2 nvarchar(300) Checked
trLabelName3 nvarchar(300) Checked
trLabelName4 nvarchar(300) Checked
trLabelName5 nvarchar(300) Checked
trLabelName6 nvarchar(300) Checked
trLabelName7 nvarchar(300) Checked
trLabelName8 nvarchar(300) Checked
trLabelName9 nvarchar(300) Checked
trLabelName10 nvarchar(300) Checked
trLabelName11 nvarchar(300) Checked
trOptionName1 bit Checked
trOptionName2 bit Checked
trExpiryDate datetime Checked
trCreatedDate datetime Checked
trModifiedDate datetime Checked
trOwner nchar(25) Checked
trType char(1) Checked
trCode varbinary(256) Checked

out of which user will be searching the details only on this
trLabelName1 nvarchar(300) Checked
trLabelName2 nvarchar(300) Checked
trLabelName3 nvarchar(300) Checked
trLabelName4 nvarchar(300) Checked
trLabelName5 nvarchar(300) Checked
trLabelName6 nvarchar(300) Checked
trLabelName7 nvarchar(300) Checked
trLabelName8 nvarchar(300) Checked
trLabelName9 nvarchar(300) Checked
trLabelName10 nvarchar(300) Checked
trLabelName11 nvarchar(300) Checked

You could then put an index on this table that has labelName and trID (in that order) which will allow you to get much better performance.

1. Then how & where the search query will be fired in this case?
2. Can you please help in putting the sql statement for creating the index for me based on your new table definition of trDataLabels

The other benefit of this approach is that you are no longer limited to 11 labels, you could have 0 labels, 1 label, 10 labels, 100 labels, or even a million labels, all without having to change the structure of your table.

This won't be required as the application itself doesn't support more than this the application has been already built on this database; coding part is completed still I will see how best I can work out this new table thing; I need to be very sure of what I am doing before I start modifying this though.


Really appreciate your kind support and thanks again for the valuable time spending with me; which is surely making me a better programmer; Almighty Bless You!

Best Regards,

Sam