从应用程序服务器执行时存储过程很慢 [英] Stored procedure slow when executed from application server
问题描述
在尝试优化查询的过程中，我遇到了一些奇怪的情况.在一个存储过程中，我有这个查询，它返回一个用户等级:
While in the process of trying to optimize a query, I'm facing a bit odd situation. In a stored procedure I've had this query, which return a user rank:
SELECT @p_rank = COUNT(*) + 1
FROM leaderboard ur
WHERE ur.score > (SELECT ur2.score FROM leaderboard ur2 WHERE ur2.id = @p_id);
基于此查询，我在 SP 中编写了第二个查询，以按国家/地区获得排名.我创建了一个带有国家/地区列的新排行榜表，并尝试了以下查询(基于第一个执行非常好的查询)
Based on this query I've written a second query in the SP, to get a rank by country. I've created a new leaderboard table with a country column, and tried the following query (based on the first query which performs very good)
SELECT @p_local_rank = COUNT(*) + 1
FROM leaderboard ur
WHERE country = @p_countryand and ur.score >
(SELECT ur2.score FROM leaderboard ur2 WHERE ur2.id = @p_id);
此查询效果不佳.我添加了一个国家/地区索引，它没有帮助，但实际上使事情变得更慢.查询处理器建议添加一个国家索引，其中也包括分数，我尝试了这个建议.添加它后，查询的性能比之前的所有查询都要好得多.我已经更改了 SP 并在 SSMS 中对其进行了测试，效果很好.
This query didn't perform well. I've added a country index, which didn't help, but actually made things much slower. The Query Processor suggested adding a country index which will include also score, and I tried this suggestion. After adding it the query performed much better than all prior queries. I've changes the SP and tested it in SSMS, and it worked very well.
当我在生产中尝试使用新 SP 时，CPU 几乎立即达到 95+%(通常约为 45%).只有删除此查询才能将 CPU 降低到正常值.请注意，此 SP 大约每分钟执行 100-150 次.
As soon as I tried the new SP in production the CPU reached 95+% almost immediately (usually it's around 45%). Only removing this query lowered the CPU back to it's normal values. Note that this SP is executed approximately 100-150 times per minute.
在阅读了很多关于相关问题的问题后，这些是我试图解决的问题:
After reading a lot of questions on related issues, These are the things I've tried to resolve it:
- 将 SP 更改为使用局部变量以防止参数嗅探
- 已清除执行计划缓存.这是一个天蓝色的数据库，所以我使用了
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; - 设置 ANSI_NULLS ON;按照此处 的建议
- 使用
sp_updatestats 更新统计信息
- Changed the SP to use local variables to prevent Parameter sniffing
- Cleared execution plan cache. it's an azure DB so I've used
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE; - SET ANSI_NULLS ON; as suggested here
- Updated statistics using
sp_updatestats
似乎没有任何帮助，一旦我将 SP 投入生产，CPU 就会达到 95%+.
Nothing seems to help and as soon as I move the SP to production the CPU reaches 95%+.
表格:
CREATE TABLE [dbo].[leaderboard](
[id] [int] NOT NULL,
[score] [int] NOT NULL CONSTRAINT [df_score_value] DEFAULT ((100)),
[level] [int] NOT NULL CONSTRAINT [df_level_value] DEFAULT ((1)),
[stage] [int] NOT NULL CONSTRAINT [df_stage_value] DEFAULT ((1)),
[insert_date] [datetime] NULL,
[update_date] [datetime] NULL,
[daily_score] [int] NOT NULL CONSTRAINT [DF_leaderboard_daily_score] DEFAULT ((0)),
[weekly_score] [int] NOT NULL CONSTRAINT [DF_leaderboard_weekly_score] DEFAULT ((0)),
[country] [nchar](45) NULL,
CONSTRAINT [PK_leaderboard] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
索引位于以下列:
- 得分
- daily_Score
- weekly_score
- 国家/地区 INCLUDE 分数
该表包含大约 300 万条记录.
The table contains about 3 Million records.
可以在此处
还有什么建议可以做的吗?`
Any suggestion what else can be done? `
推荐答案
参数和列之间似乎存在一些数据类型不匹配.这在大型数据集上可能会非常成问题，因为引擎必须转换每一行的数据，这可能会导致巨大的性能问题.此外，可能还会进行一些参数嗅探，但您似乎已经弄清楚了这一部分.
It seems there were some datatype mismatches going on between parameters and columns. This can be very problematic on large datasets because the engine has to convert the data of every single row which can cause huge performance problems. Also there may be some parameter sniffing going but it seems you figured that part out already.
我还建议为国家/地区添加一个表格，然后在您的表格中只包含 ANSI 国家/地区代码.从规范化的角度来看，这会更好.
I would also suggest adding a table for countries and then having only the ANSI country code in your table. This would be better from a normalization perspective.
这篇关于从应用程序服务器执行时存储过程很慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
