Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
duhaas
Constraint Violating Yak Guru
310 Posts |
 Posted - 2007-01-04 : 10:06:07
|
I have the following database structure, and we use it for reporting: I use sql reporting services to present the reports and use the view on the right called snapsraw. My question is, if the reports are driven off of two things [BranchState and MonthEnd] what would you recommend I use for an index, as of right now performance isnt a huge problem, but want to start planning for the future. I realize maybe its an obvious question and I just use those two fields, but dont have much experience with creating an index, and identifying what the best fields to use would be. |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-01-04 : 12:14:22
|
| Indexes should include the main fields included in your WHERE clauses...keep adding more until your performance is acceptable....and your insert/update statements don't degrade.More indices/fields in indices is a trade off between a slower update + faster reads...It's a time for experimentation....(and revision over time in case the data profile changes enough to warrant different columns indexed) |
 |
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2007-01-04 : 12:38:19
|
| Thanks for the advice, I will play around with some things. |
|
|
|
duhaas
Constraint Violating Yak Guru
310 Posts |
Posted - 2007-01-04 : 12:43:46
|
| Here is my question, where do I create the index? on the table that holds the state or on the table where the foreign key is? Branchid is the primary key in the branchcode table, and brandid is a fk in the customer value table? |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-04 : 13:07:54
|
| You already have an index on branchid where it is the primary key (primary keys automatically create an index to maintain uniqueness). You should pretty much always create an index on foreign key columns. And you must also create an index on BranchState in the RS_BranchCode table because I assume it is going to be used to find the branches in the selected states. If you have lots of branches and relatively low number of states, say for example you have 500 branches in 3 states, then it is probably not necessary to have an index on BranchState because using an index to find a third of all the records in a table is rarely more efficient than just scanning the entire table.The best place to start is by running the Index Tuning Wizard. It will create all the most important indexes for you and you can then tweak things as you get more experienced at analyzing query performance. |
|
|
|
|
|
|
Index? What Index???