我如何调试慢NHibernate选择查询？ [英] How do I debug slow NHibernate Select query?

问题描述

我在数据库上做了一个简单的查询，在两列上搜索。我在列上有一个索引。当我在SQL Server Management Studio中进行搜索时，只需要几毫秒即可完成（总是少于10个）。当我在NHibernate中执行相同的查询时，需要30秒以上。我已经分析了查询，生成的SQL很好。我正在使用NHibernate Profiler，当我在NHibernate Profiler中选择显示查询结果时，获取结果所用的时间不到一秒钟。编辑：所以，我决定这样做使用session.CreateSQLQuery（），它的工作速度非常快。为什么这会比其他方法更快？编辑：这似乎是使用查询参数是问题。我创建了一个没有参数的HQL查询，并没有问题。

TABLE SCHEMA：

一旦添加了命名参数，查询执行时间就会大幅增加。 >  CREATE TABLE [dbo]。[CRDefendant]（
 [Id] [int] NOT NULL，
 [FirstName] [varchar]（30）NULL，
 [姓氏] [varchar]（30）NULL，
 [MiddleName] [varchar]（30）NULL，
 [Race] [char]（1）NULL，
 [Sex] [char] （1）NULL，
 [BirthDate] [char]（10）NULL，
 [Social] [int] NULL，
 [BadData] [varchar]（50）NULL，
 CONSTRAINT [PK_CRDefend__3214EC073B95D2F1] PRIMARY KEY CLUSTERED 
（
 [Id] ASC 
）WITH（PAD_INDEX = OFF，STATISTICS_NORECOMPUTE = OFF，IGNORE_DUP_KEY = OFF，ALLOW_ROW_LOCKS = ON，ALLOW_PAGE_LOCKS = ON）ON [ GROUP3] 
）ON [GROUP3] 
 
 GO 
  

QUERY ：

pre $ SELECT this_.Id as Id16_0_，this_.FirstName as FirstName16_0_，this_.LastName as LastName16_0_，this_.MiddleName as MiddleName16_0_， this_.Race为Race16_0_，this_.Sex为Sex16_0_，this_.BirthDate为BirthDate16_0_，this_.Social为Social16_0_，this_.BadData为BadData16_0_ FROM [CRDefendant] this_WHERERE this_.LastName = @ p0 and this_.FirstName like @ p1


$ b

INDEX：

$ pre $ CREATE INDEX IX_CRDefendant_Name_DOB $ b $ CR ON CRDefendant（LastName ASC，名字ASC，BirthDate ASC）
INCLUDE（MiddleName，Race，Sex，Social）
ON GROUP3

首先，发布探查器显示的确切查询和在SSMS中运行的查询。没有冒犯，但是对你来说看起来很好，可能会向训练有素的人们透露丰富的信息。其次，发布你的表的确切模式，包括所有的索引。

一个可能的问题的例子可能是由于数据类型优先级（使用 nvarchar 变量在一个 varchar 索引将导致一个完整的扫描）。

至于更一般的问题，如何解决这个问题，答案是：应用像等待和排队。您需要的所有信息都可以在各种DMV中使用，例如 sys .dm_exec_query_stats ， sys.dm_exec_requests ， sys.dm_db_index_usage_stats 。执行计划也揭示了很多关于正在进行的事情，但要正确地解释执行计划中的信息是非常困难的。

I'm doing a simple query on the database, a search on two columns. I have an index on the columns. When I do the search in SQL Server Management Studio, it takes only a few milliseconds to complete (always less than 10). When I do the same query in NHibernate, it takes over 30 seconds. I've profiled the query, and the generated SQL is fine. I'm using NHibernate Profiler, and when I select "show query results" in NHibernate Profiler, it takes less than a second to get the results. Where do I go from here debugging this?

EDIT: So, I decided to do this using session.CreateSQLQuery(), and it works very fast. Why would this be faster than the other method?

EDIT: It seems as if using query parameters is the problem. I created an HQL query without parameters, and it was fine. As soon as I added named parameters, query execution time went up dramatically.

TABLE SCHEMA:

CREATE TABLE [dbo].[CRDefendant](
[Id] [int] NOT NULL,
[FirstName] [varchar](30) NULL,
[LastName] [varchar](30) NULL,
[MiddleName] [varchar](30) NULL,
[Race] [char](1) NULL,
[Sex] [char](1) NULL,
[BirthDate] [char](10) NULL,
[Social] [int] NULL,
[BadData] [varchar](50) NULL,
 CONSTRAINT [PK__CRDefend__3214EC073B95D2F1] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [GROUP3]
) ON [GROUP3]

GO

QUERY:

SELECT this_.Id as Id16_0_, this_.FirstName as FirstName16_0_, this_.LastName as LastName16_0_, this_.MiddleName as MiddleName16_0_, this_.Race as Race16_0_, this_.Sex as Sex16_0_, this_.BirthDate as BirthDate16_0_, this_.Social as Social16_0_, this_.BadData as BadData16_0_ FROM [CRDefendant] this_ WHERE this_.LastName = @p0 and this_.FirstName like @p1

INDEX:

CREATE INDEX IX_CRDefendant_Name_DOB
ON CRDefendant (LastName ASC, FirstName ASC, BirthDate ASC)
INCLUDE (MiddleName, Race, Sex, Social)
ON GROUP3

解决方案

To start with, post the exact query shown by the profiler and the query you run in SSMS. No offense, but what looks 'fine' to you may reveal a wealth of information to a trained eye. Second, post the exact schema of your tables, including all the indexes.

An example of possible problem could be nvarchar coercion due to datatype precedence (a search predicate with an nvarchar @variable on an varchar index will results into a full scan).

As for the more general question, how does one approach such an issue, the answer is: apply a method like Waits and Queues. All the information you need is available in various DMVs like sys.dm_exec_query_stats, sys.dm_exec_requests, sys.dm_db_index_usage_stats. The execution plan also reveals a lot about what s going on, but is significantly more difficult to interpret correctly the information in the execution plan.

这篇关于我如何调试慢NHibernate选择查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！