sp_executesql导致我的查询非常慢 [英] sp_executesql causing my query to be very slow

问题描述

在数据库表上运行sp_executesql时遇到一些问题.我使用的是ORM(NHibernate)，它会生成一个SQL查询，在这种情况下查询一个表.该表中大约有700万条记录，并且已被高度索引.

I am having some issues when running sp_executesql on a database table. I am using an ORM (NHibernate) that generates a SQL query that queries one table in this case. This table has about 7 million records in it and is highly indexed.

当我运行ORM在不使用sp_executesql的情况下吐出的查询时，它运行非常快，分析器显示它具有85次读取.当我使用sp_executesql运行相同的查询时，它的读取次数约为201,828.

When I run the query that the ORM spits out without the sp_executesql, it runs very quickly and profiler shows it has 85 reads. When I run the same query using sp_executesql, it has around 201,828 reads.

我是否需要在SQL Server上做一些事情以提高在没有sp_exectuesql的情况下运行查询的性能?似乎它没有使用我的索引.

Is there something I need to do on my SQL Server to improve the performance of running the query without sp_exectuesql? It seems as though its not using my Indexes.

解决此问题的最佳方法是什么?如果可能的话，我宁愿不更改ORM生成SQL的方式，而是在SQL Server/数据库级别解决问题，因为这似乎是问题所在.我猜想我需要对数据库做更多的优化以解决这个问题，我只是不知道什么.

What is the best way to fix this issue? If possible, I would rather not change the way the ORM is generating the SQL but instead fix the problem at the SQL Server/database level because it seems that is where the problem is. I'm guessing I need to do more optimization on the database to fix this problem I just don't know what.

exec sp_executesql N'SELECT top 20 
                            this_.Id as Id0_0_, 
                            this_.Application as Applicat2_0_0_, 
                            this_.[Context] as column3_0_0_, 
                            this_.Logger as Logger0_0_, 
                            this_.Message as Message0_0_, 
                            this_.Exception as Exception0_0_, 
                            this_.Thread as Thread0_0_, 
                            this_.[Level] as column8_0_0_, 
                            this_.LogDate as LogDate0_0_, 
                            this_.SessionId as SessionId0_0_ 
                       FROM LogMessages this_ 
                      WHERE this_.[Context] = @p0',
                   N'@p0 nvarchar(2)',
                   @p0 = N'55'

上下文是varchar(255).此字段是非常自由的形式.它并不总是整数，长度可能非常大.在这种情况下，我要查询的值是"55"，但查询"Foooooobaaaarrr"也很容易

Context is a varchar(255). This field is very free form. It isn't always an integer and the length may very. In this case, I am querying for a value of '55' but it could just as easily be querying for 'Foooooobaaaarrr'

推荐答案

什么是数据类型.[上下文]使用相同的数据类型

what is the data type of .[Context] use the same datatype

现在您正在使用nvarchar(2)，但是对于类似55这样的东西来说似乎很奇怪，如果您使用的数据类型不同，则会得到转换，从而引起扫描

right now you are using nvarchar(2) but that seems odd for something like 55, if you are not using the same data types you will get conversions which then cause scans

根据您更新的问题，它看起来像是varchar(255)，然后执行此操作

based on your updated question, it looks like it is varchar(255), then do this

WHERE this_.[Context] = @p0',N'@p0 varchar(255)',@p0='55'

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