<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Performance Tuning
»
How to increase indexes performance for fast...
20 posts, Page 1 of 2
1
2
»»
How to increase indexes performance for fast queries results
Rate Topic
Display Mode
Topic Options
Author
Message
neellotus07
neellotus07
Posted Thursday, September 27, 2012 1:17 AM
Grasshopper
Group: General Forum Members
Last Login: Yesterday @ 5:54 AM
Points: 20,
Visits: 143
Hi All,
I have a table named "Tbl_Inv" in my database that contains 4.7 Lacs records with six non clustered indexes on below mentioned columns:-
ROWID, DOC_CAT, DOC_NO, I_CODE, BOXNO, AMT+GL_CODE
This table does't have any clustered index.
So Please help me and tell me should i create more clustered indexes or i need to create clustered index for fast query results.
Thanx.
Neel
Post #1365059
MSzI
MSzI
Posted Thursday, September 27, 2012 1:42 AM
Say Hey Kid
Group: General Forum Members
Last Login: Thursday, April 04, 2013 8:11 AM
Points: 669,
Visits: 240
Hello,
First off all I would suggest to create a unique clustered index, on the column that is a primary key.
In Your case this would be the ROWID column.
Further IF is possible to change table schema structure, I would create a new column to act as the primary key, instead of ROWID, let's call it UID, using the most narrow data type SMALLINT, INT or BIGINT to further improve the unique cluster index efficiency.
Important to remember! :
You can create only one (1) clustered on a table, regardless the fact that is unique or not. Internally the db engine ensures the uniqueness of it.
Post #1365068
Sumit Rastogi
Sumit Rastogi
Posted Thursday, September 27, 2012 1:45 AM
Grasshopper
Group: General Forum Members
Last Login: Thursday, April 25, 2013 5:27 AM
Points: 18,
Visits: 79
--
Post #1365071
vyas
vyas
Posted Thursday, September 27, 2012 1:47 AM
Hall of Fame
Group: General Forum Members
Last Login: 2 days ago @ 4:02 AM
Points: 3,131,
Visits: 1,056
Create an Clustered index. Try to minimize the no of non clustered by creating include indexes.
Post #1365073
Suresh B.
Suresh B.
Posted Thursday, September 27, 2012 1:57 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:59 AM
Points: 1,075,
Visits: 5,114
Are you facing any performane issues?
Indexes are query specific.
So, please post your SQL statement and execution plan.
Post #1365079
Suresh B.
Suresh B.
Posted Thursday, September 27, 2012 2:15 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:59 AM
Points: 1,075,
Visits: 5,114
Use sys.dm_db_index_usage_stats to know the effectiveness of your existing indexes.
Post #1365087
GilaMonster
GilaMonster
Posted Thursday, September 27, 2012 3:37 AM
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 37,640,
Visits: 29,895
Clustered indexes don't have to be the primary key column. Sometimes there are better places to put the.
Take a read through these.
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1365116
neellotus07
neellotus07
Posted Thursday, September 27, 2012 5:52 AM
Grasshopper
Group: General Forum Members
Last Login: Yesterday @ 5:54 AM
Points: 20,
Visits: 143
Hello MSzI,
Firstly very thanx for your reply and According to your suggestions :-
I created clustered index on ROWID column that is a primary key and i include DOC_CAT, DOC_NO, I_CODE, BOXNO, AMT+GL_CODE into one non clustered index.
After this i running query "Select * from "Tbl_Inv" then it taking 1.24 minutes but before this it was taking 2.53 minutes to complete
Is it right or pls suggest some technique that i need to do something different for fast query result because i need to minimize result time near at 50 seconds.
Thanx
Neel
Post #1365167
Suresh B.
Suresh B.
Posted Thursday, September 27, 2012 6:02 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:59 AM
Points: 1,075,
Visits: 5,114
neellotus07 (9/27/2012)
...After this i running query "Select * from "Tbl_Inv" then it taking 1.24 minutes but before this it was taking 2.53 minutes to complete...
Have you cleared the buffer before doing this bench marking?
Post #1365171
Suresh B.
Suresh B.
Posted Thursday, September 27, 2012 6:17 AM
Ten Centuries
Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:59 AM
Points: 1,075,
Visits: 5,114
neellotus07 (9/27/2012)
After this i running query "Select * from "Tbl_Inv" then it taking 1.24 minutes but before this it was taking 2.53 minutes to complete
Why are you SELECTing all the 4,70,000 rows? Why don't you use WHERE condition?
Post #1365184
« Prev Topic
|
Next Topic »
20 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
