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

 

[Qik3Coder]

The only way to know what particular index you need is to run a query with the "Include Actual Execution Plan"

You will look for things like X Scans and you will replace them with Seeks, by either adding more parameters to your query, or by modifying your index list.

Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[sam4help]

Thanks for reply back, but I sorry whatever you said all went through above my head .. I am a newbie in this regards.. Sorry again,

Best Regards,

Sam

 

[Qik3Coder]

<cough>
A couple buttons to the right of the giant !EXECUTE button, there is one with 2 blue boxes and 1 green box. The mouse over for this box says "Include Actual Execution Plan"

Click this.
The button will remain depressed (it's okay, he has meds)

When you run your query you will have a 3rd tab.
Results|Messages|Execution Plan

The Execution Plan shows, via flowchart, what the SQL engine did to get your result set.


You use the particular activities to figure out what you need to fix. A Clustered Index Seek is as good as it gets, so if you're percentages are there, you need to look at other things.

Find the box with the biggest percentage and see what you can do to optimize that part of the query.


Lodlaiden

I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[sam4help]

thank you very much I will look into it.

All the very best wishes,

Sam