本文介绍了如何使用索引提高此查询的性能?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

这是我的查询:





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中的查询来执行此操作。以下是有关该主题的详细信息:

[]



这篇关于如何使用索引提高此查询的性能?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-05 16:46
查看更多