如何使用索引提高此查询的性能？ [英] How can I improve the performance of this query using indexes?

问题描述

这是我的查询：

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.

推荐答案

实际上，我不会直接给你答案，但我会告诉你我会做什么案件。首先加入两个表（event_log，events）。没有转换，没有其他表，没有订购。然后逐步添加其他表，然后应用其他条件，排序，转换等。哪些更改会降低您的查询性能，关注该更改并尝试查看如何改进它。 （或问这里:)）



例如：

1-测试一下：

Actually, I won't give you the direct answer, but I'll tell you what I would do in such a case. Start with joining two tables (event_log, events). No conversion, no other tables, no ordering. Then step by step add other tables, then apply other criteria, ordering, conversion, etc. Which change decreases your query performance, focus on that change and try to see how to improve it. (or ask here :))

for example:
1- test this:

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

显示图形执行计划（SQL Server Management Studio） [ ^ ]

检查解决方案



http://webxperts.co.in/home/articles [ ^ ]

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