使用COLLATE SQL_Latin1_General_CP1_CS_AS是否有任何性能问题 [英] Is any performance issue on using COLLATE SQL_Latin1_General_CP1_CS_AS

问题描述

在我的一个SQL选择查询中，我需要区分大小写的搜索，并且在SP的where子句中使用了" COLLATE SQL_Latin1_General_CP1_CS_AS ".较早运行良好，并在2-3秒内获得了最大的结果.现在，该特定表中的条目增加到240万以上，并且当我从数据层调用SP时出现超时异常(我使用Microsoft.Practices.EnterpriseLibrary.Data访问数据库).有趣的是，超时异常仅在第一次调用时发生，并且在连续调用中将以秒为单位获取数据.

我的问题是为什么同一个SP在第二次调用时执行不同的操作(没有超时)? COLLATE语句对这种现象有影响吗?

请帮助我找到合适的解决方案.提前thnx
Renjith

Hi,
In one of my SQL select query I need a case sensitive search and I used "COLLATE SQL_Latin1_General_CP1_CS_AS" in the where clause of SP. This worked fine earlier and got the result maximum in 2-3 seconds. Now the entries in that particular table is increased above 2.4 million and I got time out exception when I call the SP from my data layer(I''m using Microsoft.Practices.EnterpriseLibrary.Data for accessing DB). Interesting thing is that the time out exception occur only for the first time call and will get data in seconds for successive calls.

My question is why the same SP performs differently(no timeout) on calling second time? Is the COLLATE statement has any effect on this phenomena?

Please help me for finding the apt solution. Thnx in advance
Renjith

推荐答案

此 [ ^ ]可能会解释您看到的结果.

有关执行计划的更多信息 [

This[^] might explain the results you see.

More on Execution Plans[^].

Hope this helps. :)

这篇关于使用COLLATE SQL_Latin1_General_CP1_CS_AS是否有任何性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！