March 11, 2010 at 5:53 pm
So, if I have this clear in my head, if I have a query that joins several tables and and I'm selecting columns from a few of those tables, I might consider putting covering indexes on the columns I am selecting from a table in conjunction with it's SARG from the where clause.
As soon as I add another column to my query that is not in one of the covering indexes I would expect my execution time to return to the pre-covering index speed as the SQL engine is now required to go all the way back to the table for the extra column data.
Makes sense to me
September 23, 2011 at 7:48 am
Hi,
I've a follow up question regarding this discussion. Query below which is called 10K a day causes of long PAGEIOLATCH_SH waits.
UPDATE table1.list_item
SET first_name =
CASE
WHEN ls.has_first_name = 1 THEN ls.first_name
ELSE li.first_name
END,
last_name =
CASE
WHEN ls.has_last_name = 1 THEN ls.last_name
ELSE li.last_name
END,
address_1 =
CASE
WHEN ls.has_address_1 = 1 THEN ls.address_1
ELSE li.address_1
END,
address_2 =
CASE
WHEN ls.address_2 is not null THEN ls.address_2
ELSE li.address_2
END,
city =
CASE
WHEN ls.has_city = 1 THEN ls.city
ELSE li.city
END,
state =
CASE
WHEN ls.has_state = 1 THEN ls.state
ELSE li.state
END,
zip_code =
CASE
WHEN ls.has_zip_code = 1 THEN ls.zip_code
ELSE li.zip_code
END,
country =
CASE
WHEN ls.has_country = 1 THEN ls.country
ELSE li.country
END,
home_phone =
CASE
WHEN ls.has_home_phone = 1 THEN ls.home_phone
ELSE li.home_phone
END,
work_phone =
CASE
WHEN ls.has_work_phone = 1 THEN ls.work_phone
ELSE li.work_phone
END,
cell_phone =
CASE
WHEN ls.has_cell_phone = 1 THEN ls.cell_phone
ELSE li.cell_phone
END,
got_money =
CASE
WHEN ls.got_money = 1 THEN ls.got_money
ELSE li.no_money
END,
has_mansion =
CASE
WHEN ls.has_mansion = 1 THEN ls.has_mansion
ELSE li.no_mansion
END
FROM @list_items ls
INNER JOIN table1.list_item li
ON ls.list_item_id = li.list_item_id
WHERE li.org_id = @org_id
The table1 is partitioned by org_id which is also a clustred index. I've created a non-clustered index on table1 like so
CREATE NONCLUSTERED INDEX [IX_list_item_list_item_id_org_id] ON [table1]
(
[list_item_id] ASC,
[org_id] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, FILLFACTOR = 80, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
and I still see some performance issues.
I'm considering #1. adding list_item_id to the clustred index, so I'll have org_id and list_item_id as a composit clustered index or #2. add some INCLUDE columns to IX_list_item_list_item_id_org_id index like has_work_phone, has_cell_phone since they are bit types. I can't add all the columns in the "INCLUDE" that are involved in the update (First Name, address, etc.) due to size and that would be too much. What do you think?
Thanks in advance
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply