查询优化以减少执行时间

本文介绍了查询优化以减少执行时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在办理出勤申请。在我的应用程序中，我有一个用于存储打孔/输出的表格 我为此表创建了以下列的索引（EntryDate，EmployeeID） 我需要为所有员工阅读First Time In，因此我使用以下查询

Hi, I am doing attendance application. In my application I have a table for store Punch in/out I create indexing for this table for following columns (EntryDate, EmployeeID) I need to read First Time In for all Employees so I am using following query

Select (select min( TimeIn) from tbl_TimeInOutEntry WITH(INDEX( tmescangardindx) where EmployeeID = E.EmployeeID and EntryDate = '2015-03-02' group by EntryDate,EmployeeID) as TimeIn from tbl_employee E where E.CompanyID = 1

但是这个查询执行将近30秒。 一种情况是当我在tbl_TimeInOutEntry中有大量记录时，此查询执行速度非常慢。 您能否提出优化此查询的建议 谢谢 Siva Rm K 附件 执行计划 Timescangard详情 - > TimeInTimeout表

But this query Execution is nearly 30 sec. One scenario is when I have high number of records in tbl_TimeInOutEntry then this query execution is very slow. Can you give a suggestion for optimize this query Thanks Siva Rm K Attachments Execution Plan Timescangard detail -> TimeInTimeout Table

推荐答案

运行索引调整向导并优化索引：www.datasprings/ resources / dnn-tutorials / artmid / 535 / articleid / 53 / sql-server-2008-profiler-and-tuning-wizard？AspxAutoDetectCookieSupport = 1 [ ^ ] Run the index tuning wizard and optimize your indexes : www.datasprings/resources/dnn-tutorials/artmid/535/articleid/53/sql-server-2008-profiler-and-tuning-wizard?AspxAutoDetectCookieSupport=1[^]

正如我在问题的评论中所提到的，我建议基于单一选择语句创建查询（减少子查询的数量）。 如果你想获得所选日期和公司的第一次，试试这个： As i mentioned in the comment to the question, i'd suggest to create query based on single select statement (reduce the count of subqueries). If you want to get first time for the selected date and company, try this: SELECT MIN(TI.TimeIn) AS TimeIn FROM tbl_employee EM INNER JOIN tbl_TimeInOutEntry TI ON EM.EmployeeID = TI.EmployeeID WHERE TI.EntryDate = '2015-03-02' AND EM.CompanyID = 1

注意：请按照 Mehdi Gholam 的解决方案1进行操作[ ^ ]减少计数索引。

Note: Please, follow the solution1 by Mehdi Gholam[^] to reduce the count of indexes.