如何使用索引提高此查询的性能？

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

这是我的查询：

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' = apany_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） [ ^ ]

检查解决方案 webxperts.co.in/home/articles [ ^ ]