Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Difference between Composite Index and Single Column Indexes ? Expand / Collapse
Author
Message
Posted Friday, October 02, 2009 8:59 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 8:42 AM
Points: 481, Visits: 318
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
Post #796983
Posted Friday, October 02, 2009 9:00 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #796984
Posted Friday, October 02, 2009 9:07 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 8:42 AM
Points: 481, Visits: 318
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
Post #796990
Posted Friday, October 02, 2009 9:09 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:34 PM
Points: 15,442, Visits: 9,588
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
Post #796992
Posted Friday, October 02, 2009 9:26 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 8:42 AM
Points: 481, Visits: 318
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
Post #797017
Posted Friday, October 02, 2009 9:56 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 41,531, Visits: 34,448
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 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 #797045
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse