问题描述
这是我的查询:
Here's my query:
SELECT 'Id' = el.id,
'DB Time Stamp' = convert(nvarchar(50), el.db_datetime),
'Local Time Stamp' = convert(nvarchar(50), el.local_datetime),
'Event' = e.name,
'Sub Campaign' = sc.title,
'Company' = a.company_name,
'Contact' = c.first_name + ' ' + c.last_name,
'User' = u.fullname,
'Parameter 1' = el.param1,
'Parameter 2' = el.param2,
'Parameter 3' = el.param3,
'Parameter 4' = el.param4,
'Parameter 5' = el.param5,
'Parameter 6' = el.param6
FROM event_log el WITH(NOLOCK)
JOIN [events] e WITH(NOLOCK) ON el.event_id = e.id
LEFT JOIN subcampaigns sc WITH(NOLOCK) ON el.subcampaign_id = sc.id
LEFT JOIN accounts a WITH(NOLOCK) ON el.account_id = a.id
LEFT JOIN contacts c WITH(NOLOCK) ON el.contact_id = c.id
LEFT JOIN users u WITH(NOLOCK) ON el.[user_id] = u.id
ORDER BY el.id DESC;
我做了以下工作:
1.使用event_log id添加复合索引,包括列:local_datetime,db_datetime,param1-param6
2.我在表事件中添加了外键索引,subcampaigns ,帐户,联系人,用户
3.我在event_log表中添加了subcampaign_id,account_id,contact_id,user_id的各个索引。
性能(运行时)没有任何改进。我仍然得到50秒翻身。
I did the following:
1. Added composite index using event_log id, including columns: local_datetime,db_datetime, param1-param6
2. I added foreign key indexes in tables events, subcampaigns, accounts, contacts, users
3. I added individual indexes for subcampaign_id, account_id, contact_id, user_id inside my event_log table.
There is no improvement in the performance (runtime). I still get 50 seconds turn over.
推荐答案
SELECT el.id, el.db_datetime, el.local_datetime, e.name
FROM event_log el
JOIN [events] e ON el.event_id = e.id
2-如果可以的话,请测试一下:
2- if it is ok, then test this:
SELECT el.id, el.db_datetime, el.local_datetime, e.name, sc.title
FROM event_log el
JOIN [events] e ON el.event_id = e.id
JOIN subcampaigns sc ON el.subcampaign_id = sc.id
...
一步一步,改善你的查询。
另一种选择是检查前执行计划。您可以通过右键单击SSMS中的查询来执行此操作。以下是有关该主题的详细信息:
[]
这篇关于如何使用索引提高此查询的性能?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!