October 2, 2009 at 8:59 am
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
October 2, 2009 at 9:00 am
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
October 2, 2009 at 9:08 am
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
October 2, 2009 at 9:09 am
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
October 2, 2009 at 9:26 am
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
October 2, 2009 at 9:56 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply