How to increase indexes performance for fast queries results

Question

Post reply

How to increase indexes performance for fast queries results

neellotus07

Ten Centuries

Points: 1245
More actions
September 27, 2012 at 1:17 am

#262216

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

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

MSzI SSCommitted Points: 1509 More actions · Answer 1

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.

Sumit Rastogi SSC Eights! Points: 838 More actions · Answer 2

Sumit Rastogi

SSC Eights!

Points: 838

September 27, 2012 at 1:45 am

#1542730

--

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 3

Create an Clustered index. Try to minimize the no of non clustered by creating include indexes.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

Are you facing any performane issues?

Indexes are query specific.

So, please post your SQL statement and execution plan.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 5

Use sys.dm_db_index_usage_stats to know the effectiveness of your existing indexes.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

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://qa.sqlservercentral.com/articles/Indexing/68439/

http://qa.sqlservercentral.com/articles/Indexing/68563/

http://qa.sqlservercentral.com/articles/Indexing/68636/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

neellotus07 Ten Centuries Points: 1245 More actions · Answer 7

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 8

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?

Suresh B. SSC-Insane Points: 22986 More actions · Answer 9

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?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 10

No index will help a SELECT * FROM <table>. That is a read of the entire table, it's a table scan, nothing else. Indexes are mainly to help locate rows when you have a where clause or join.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 11

neellotus07 (9/27/2012)
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.

And are those good choices for indexes? Are they useful for queries?

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.

Filter the data. Query and return just what you need, not the entire table

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

nitin.varshney SSC Eights! Points: 948 More actions · Answer 12

Hi,

Please try to create the non cluster index which you are selecting and using in the where clause.

If there are multiple columns, try to identify the main columns which will be main part of the non cluster index and other columns can be added in the Include clause of Non Cluster Index.

Thanks

Nitin Varshney.

Amit Pandey - Scalability Engineers Old Hand Points: 345 More actions · Answer 13

If you are talking about a query that you have tried to benchmarked it can be pretty straight forward.

Just script your table and post it.

Along with that send the query that you are using for your testing.

What columns should be included depends on the query there is no hard and fast rule.

Regarding clustered or any index they should be on a column which is more selective and with the little information that you have provided, it looks like the RowID that you are using may be the right candidate.

Cheers...

Amit Pandey

http://www.debugsql.com/

neellotus07 Ten Centuries Points: 1245 More actions · Answer 14

Hi GilaMonster,

Firstly lot of Thanxxxxxx for your suggestions.

I created clustered index on ROWID this column never used in Where Clause.

And i created non clustered index on those columns which are always used in Where Clause. And I have also other columns that are currently using in where clause like as MRN_CAT,MRN_NO,REF_NO,REF_DT,ORD_CAT,ORD_NO etc, but i dont create indexes on these columns due to slowness of insertion, updation & deletion operations.

As i already told you this table storing invoices detail so it contains records in Lacs and these records increasing day by day.

And mostly reports, pop-up & views are created on the basis of this table's record so i need to run it fast.

Should i create separate primary key & then clustered index on this key for better performance of clustered index.

If you have any other good suggestions pls tell me i will be very thankfull to you.

Thanx

Neel