优化数据库 [英] Optimization of database

问题描述

我有一个名为MetricTbl的表，其中的字段为User_Id，Entity，Metric，ModifiedDate，Value。值，UserId是整数。实体和指标是nvarchar。表有大约3000万行。



我想优化查询 -

I have a table named MetricTbl with fields as User_Id, Entity, Metric, ModifiedDate, Value. Value, UserId are integer. Entity and Metric are nvarchar. Table has around 30 million rows.

I want to optimize the query -

select SUM(Value) from MetricTbl where UserId = 1 and Entity = 'Room' and Metric = 'Temperature'

我必须为这种查询设计策略。我正在使用azure联合会。这是一个联合表。

我已经在User_Id，Entity，Metric，ModifiedDate上创建了唯一索引。由于它是azure联邦，所以我不能索引视图。

请建议一些策略。

如果有需要我可以创建新表。

谢谢

Manish

I have to design a strategy for this kind of query. I am using azure federation. And this is a federated table.
I already created unique index on User_Id, Entity, Metric, ModifiedDate. Since it is azure federation so I cant have indexed views.
Please suggest some strategy.
If there is need I am allowed to create new table.
Thanks
Manish

推荐答案

我建​​议你使用存储过程 [ ^ ]而不是查询;）

例如：

I would suggest you to use stored procedure[^] instead above query ;)
for example:

CREATE PROCEDURE GetSumOfUser
    @userid INT,
    @entity varchar(30),
    @metric varchar(30)
BEGIN
    SELECT SUM([Value])
    FROM MetricTabl
    WHERE userid = @userid AND entity=@entity AND metric =@metric
END

实用文章：

Windows Azure角色大小和数据库的优化技术 [ ^ ]

Windows Azure SQL数据库性能和弹性指南 [ ^ ]

Windows Azure应用程序中性能的最佳实践 [ ^ ]

Useful articles:
Optimization Techniques for Windows Azure Role Sizes and Databases[^]
Windows Azure SQL Database Performance and Elasticity Guide[^]
Best Practices for Performance in Windows Azure Applications[^]

这篇关于优化数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！