查询花费太多时间 [英] Query taking too much time

问题描述

我创建了一个Azure SQL实例(数据库名称:sstk/fulfillment_manager).现在，如果我在查询下面运行，则需要5分钟多的时间才能返回300,000个结果.当数据库较小时(记录总数为30万)，则查询 表现还不错.但是现在总记录约为180万，并且性能非常差.查询中的where子句列也已建立索引.我曾尝试过自动调整功能，但没有运气.我已经检查了性能建议，但一无所获 正在显示.我正在使用标准帐户，20 DTU和250GB磁盘作为数据库.

查询:从release_request_id = 100的implementation_manager.dbo.fulfillment_unit选择*.

请检查一下.

此致

坦美

博士+ 91-8452867502

mail:[e-mail]

解决方案

release_request_id上的非聚集索引中，如果所需的这些列的数量和大小为相当小.

Hi,

I have created a Azure SQL instance(name of the database : sstk/fulfillment_manager). Now if I run below query, it is taking more than 5 minutes to return 300,000 results. When the database size was small(total number of records 0.3 million), then the query performance was good. But now the total records are ~1.8million, and the performance is pretty bad. The where clause column in the query is indexed as well. I have tried auto tuning feature, but no luck. I have checked for performance recommendation, but nothing is showing there.  I am using standard account, 20 DTU and 250GB disk for database.

Query : select * from fulfillment_manager.dbo.fulfillment_unit where release_request_id=100.

Please check on this.

Regards,

Tanmay

Ph. [phone]

mail : [e-mail]

解决方案

Were you returning all 300,000 rows when query performance was good with the 300,000 row table? Because you are selecting all columns and a large percentage of the rows, it would be most optimal for the index on release_request_id to be the clustered index. I would expect elapsed time to be proportional to the amount of data returned in this case.

If you don't really need all columns, specify only those you actually need and consider adding those columns to a non-clustered index on release_request_id if the number and size of those columns needed is reasonably small.

这篇关于查询花费太多时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！