Clustered and Non-clustered Index

Last Post 23 Feb 2009 10:26 AM by LeonOrlov. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
plugsharma
New Member
New Member

--
17 Dec 2008 03:13 AM
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..


Thanks

Sharma
LeonOrlov
New Member
New Member

--
23 Feb 2009 10:26 AM
Please post table create script together with indexes and any partition information.

And your queries.

Cheers

~Leon
MattiasLind
New Member
New Member

--
20 May 2009 04:37 AM
Well, I would approach this like...

Do you have any natural range that gives you a relative unique set of rows which are relevant in all eight queries?

Well, in order to help you we need your queries and table preferences preferrable together with column statistics, i.e. used for ranges/unique values and so on.

How many rows do you fit per data page, how many rows are SITE_ID per SITE_ID?
Is the clustered index (table) internally/externally defragmented? I.e avg_fragmentation in sys.dm_db_index_physical_stats() above 30 percent/datafile fragmented on the disk.

For example the natural range could be SITE_ID AND ACTION, or SITE_ID AND USER_NAME, or ORG_NAME AND ACTION, or what ever... Right now we're only guessing.

If you can see you natural range, in relatively small sets of rows, fitting a single data page, or a UNIFORM extent, while meeting your need of returning rows and all eight queries, try that för a CLUSTERED INDEX.

Or you might even consider creating multible non-clustered indexes with included columns.
For example:
CLUSTERED ON (SITE_ID, ORG_NAME) -- Do they express the same thing?

Qry1/Qry2/Qry3/Qry4, Qry8: ON (USER_NAME) INCLUDE (ACTION, ACTIVITY_NAME, FROM_PAGE, LOG_DATE_TIME, SESSION_ID)

Qry5/Qry6/Qry7: ON (ACTION) INCLUDE (ACTIVITY_NAME, LOG_DATE_TIME)

But then again, just guessing right now...
You are not authorized to post a reply.

Acceptable Use Policy