选择查询非常慢 [英] Select Query is very slow

问题描述

大家好



[SQL Server 2008]

我有一张表（DATALOG_4），有10百万条记录。它包含18列。列DATE_TIME是主键列。我们在DATE_Time上创建了非聚集索引，因为我们在WHERE子句中使用此列。



问题是，即使在我创建之后也需要20秒以上非聚集索引。



我们使用以下查询：

Hi All

[SQL Server 2008]
I have a table(DATALOG_4) with 10 Millions records. It contains 18 columns. The column DATE_TIME is a primary key column. We have created Non-Clustered Index on DATE_Time because we are using this column in WHERE Clause.

The issue is, It takes more than 20 seconds for even after I created Non-Clustered index.

We are using the following Query:

SELECT DATE_TIME, [794] AS CT, [61] AS BID,
         ROW_NUMBER() OVER (PARTITION BY [61] ORDER BY  DATE_TIME DESC) AS Row_No
   FROM DATALOG_4 (NOLOCK)
   WHERE
        DATE_TIME >= '2012-11-01 12:00:00' and DATE_TIME <= '2012-12-31 11:59:59'

请有人建议我，如何快速选择查询？

Please anyone suggest me, How to make the select Query fast?

推荐答案

我'之前我没有自己使用它，但你是否试过删除'Over / Partition by'逻辑以查看查询如何预先形成？



我的理解是它应该只计算选定的每一行上的rownumber - 但这可能会降低你的性能。

I''ve not used it myself before, but have you tried removing the ''Over / Partition by'' logic to see how the query preforms then?

My understanding is that it should only calculate the rownumber on each row selected - but it could be this that is degrading your performance.

为列[61]添加索引（非群集，非唯一） ]。

Add an index (non-clusterd, non-unique) for column [61].

始终将日期存储为日期时间，而不是字符串。这将使它更快。然而，1000万条记录的20秒似乎对我来说是合理的。你需要多快的速度？

Always store dates as a datetime, not as a string. That will make it faster. 20 seconds for 10 million records seems reasonable to me, however. How fast do you need it to be ?

这篇关于选择查询非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！