SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Difference between Composite Index and Single Column Indexes ?


Difference between Composite Index and Single Column Indexes ?

Author
Message
Swarndeep
Swarndeep
SSC Eights!
SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)

Group: General Forum Members
Points: 815 Visits: 377
I have one poor performing procedure with couple of queries in it.

I have identified few temp table queries that does scanning of temp table. I decided to add index on temp table to avoid table scanning. I have noticed that there are multiple columns of temp table which are being used in where clause. However, I am not sure whether I should include all columns in single index (composite index) or multiple indexes with one column each index to gain the maximum performance.

I would appreciate your inputs on this.

Thanks

Swarndeep

http://talksql.blogspot.com
GSquared
GSquared
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54311 Visits: 9730
One index with all the columns in the query is best.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Swarndeep
Swarndeep
SSC Eights!
SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)

Group: General Forum Members
Points: 815 Visits: 377
Thanks for the reply.

After I posted my question, In the mean time I tested this scenario, I noticed some difference, when I added individual columns with individual indexes, it showed me result in 59 seconds, on the other hand it pulled the records in 84 seconds if I run query with composite index.

I am not questioning your opinion, rather I have less knowledge and I would like to know the technical difference between both of the scenarios.

Thanks for your time.

Swarndeep

http://talksql.blogspot.com
GSquared
GSquared
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54311 Visits: 9730
I would need to see your actual code to tell you what's going on.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Swarndeep
Swarndeep
SSC Eights!
SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)SSC Eights! (815 reputation)

Group: General Forum Members
Points: 815 Visits: 377
It is a huge procedure, and I am copying the stuff related to temp table only....


create table #tmpReport1 (
record_key numeric(18,0) identity,
l1_type_id int null,
l1_type_name varchar(50) null,
l1_bu_id int null,
l1_bu_name varchar(50) null,
l2_type_id int null,
l2_type_name varchar(50) null,
l2_bu_id int null,
l2_bu_name varchar(50) null,
l3_type_id int null,
l3_type_name varchar(50) null,
l3_bu_id int null,
l3_bu_name varchar(50) null,
l4_type_id int null,
l4_type_name varchar(50) null,
l4_bu_id int null,
l4_bu_name varchar(50) null,
l5_type_id int null,
l5_type_name varchar(50) null,
l5_bu_id int null,
l5_bu_name varchar(50) null,
carrier_name varchar(50) null,
product_name varchar(50) null,
business_unit_name varchar(50) null,
business_unit_id numeric(18,0) null,
app_agent_number varchar(9) null,
app_agent_name varchar(50) null,
payment_method varchar(30) null,
commission_option varchar(30) null,
trans_type varchar(30) null,
premium_amount float null,
commission_amount float null,
Date_Modified datetime null,
submission_date datetime null,
account_number varchar(30) null,
money_form varchar(30) null,
application_id varchar(25) null,
product_type varchar(64) null,
customer_name varchar(255) null,
app_status varchar(50) null,
cusip varchar(25) null,
Contract_number varchar (25) null,
Transmission_Date datetime null,
Transaction_ID int,
Annuitant varchar(255)Null,
STATUS_NAME_ID INT
)



Then I am inserting few hundreds to few thousand rows in temp table (depending on the parameters).

Now I am adding index on temp table.


CREATE NONCLUSTERED INDEX IDX_1_TMPReport1
on #tmpReport1(record_key)
CREATE NONCLUSTERED INDEX IDX_2_TMPReport1
on #tmpReport1(app_status)
CREATE NONCLUSTERED INDEX IDX_3_TMPReport1
on #tmpReport1(l1_bu_name)
CREATE NONCLUSTERED INDEX IDX_4_TMPReport1
on #tmpReport1(trans_type)
CREATE NONCLUSTERED INDEX IDX_5_TMPReport1
on #tmpReport1(contract_number)
CREATE NONCLUSTERED INDEX IDX_6_TMPReport1
on #tmpReport1(application_id)




After this is done, there are couple of updates, deletes on temp table.


--runs in a cursor
update #tmpReport1 with(rowlock)
set l1_type_id = @l1_type_id, l1_bu_name = @l1_bu_name, l1_type_name = @l1_bu_type_name, l1_bu_id = @l1_bu_id,
l2_type_id = @l2_type_id, l2_bu_name = @l2_bu_name, l2_type_name = @l2_bu_type_name, l2_bu_id = @l2_bu_id,
l3_type_id = @l3_type_id, l3_bu_name = @l3_bu_name, l3_type_name = @l3_bu_type_name, l3_bu_id = @l3_bu_id,
l4_type_id = @l4_type_id, l4_bu_name = @l4_bu_name, l4_type_name = @l4_bu_type_name, l4_bu_id = @l4_bu_id,
l5_type_id = @l5_type_id, l5_bu_name = @l5_bu_name, l5_type_name = @l5_bu_type_name, l5_bu_id = @l5_bu_id
where record_key = @rec

update.....

delete......

select * FROM #tmpReport1 where app_status <> 'DELETED'



Thanks

Swarndeep

http://talksql.blogspot.com
GilaMonster
GilaMonster
SSC Guru
SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)SSC Guru (210K reputation)

Group: General Forum Members
Points: 210776 Visits: 46251
Please post the execution plan of the query in question, both where there are multiple single-column indexes and where there's one composite index.

I don't see any query in your posted code with multiple columns in the where clause.

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search