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 |
|
plugsharma
Starting Member
2 Posts |
 Posted - 2008-12-17 : 07:00:55
|
Hi,I have one table having more than 3 millions rows with following columns:[SITE_ID][USER_NAME][ACTIVITY_NAME][PAGE_TYPE][ACTION][DOC_ID][FROM_PAGE][TO_PAGE][ORG_NAME][LOG_DATE_TIME][SESSION_ID][IP][MACHINE_ID]I am using 8 different queries, all thru stored procedure, to fetch data.SITE_ID, USER_NAME, ORG_NAME and ACTION are in all queries WHERE clause.I have created SITE_ID as non-unique clustered index and USER_NAME, ORG_NAME and ACTION as non-unique non-clustered index.Problem is that data fetching without indexs is faster than when creating idex.Is there any problem in index columns.Can you please suggest me a better index plan.Images file described columns called details (http://xs434.xs.to/xs434/08513/queries-columns997.jpg or http://xs.to/xs.php?h=xs434&d=08513&f=queries-columns997.jpg)Please help..ThanksSharma |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-12-17 : 07:13:45
|
| Assuming your queries get your results, what's the problem. Is it too slow? Not sure what you're after here.If you are thinking that there is some magic rule that using an index is always the best way, then you are mistaken. Please provide more info. |
 |
|
|
slada
Starting Member
2 Posts |
Posted - 2008-12-17 : 07:51:40
|
| When building indexes, I always consider the primary way the table is searched and build a clustered index based on that search because that is how it will sit on the disk. I've seen huge performance gains on tables of 200 million rows plus where the original clustered index was based on the auto incrementing identity column. Simply changing it to be clustered on the group of columns in the order they were searched gave a huge performance boost. Queries that ran two to three minutes now take less than a second.If you are clustering on SITE_ID but your searches are all based on ORG_NAME and USER_NAME then I would build a clustered index on those two columns even if SITE_ID is the primary key. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-17 : 09:44:42
|
| You should always have Clustered index in your table so that you can Defrag index whenever needed. Remember Heap table can't be Rebuild and Defragged. |
|
|
|
|
|
|
Clustered and Non-clustered Index){kind=link}