查询优化以减少执行时间 [英] Query Optimization for reduce Execution Time

问题描述

我正在办理出勤申请。在我的应用程序中，我有一个用于存储打孔/输出的表格

我为此表创建了以下列的索引（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

推荐答案

运行索引调整向导并优化索引：http://www.datasprings.com/ 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 : http://www.datasprings.com/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.

这篇关于查询优化以减少执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！