当C#EF core调用一个挂起的select查询时,MSSQL management studio会很好地执行该查询。为什么?

我不能给你足够的数据来重现错误,所以我会给你尽可能多的数据。

我有一个select语句从EF core执行。

var bookings = context.Booking
                      .Where(booking => booking.ConsigneeNumber == customer.GetCustomerTarget().Code 
                             && booking.CreatedAt >= from 
                             && booking.CreatedAt < to 
                             && booking.BookingLine.Any(b => b.BookingLineSpecification
                                                        .Any(c => c.CurrencyCode == code))
                            )

                     .Include(booking => booking.BookingLine)
                         .ThenInclude(bl => bl.BookingLineSpecification)
                         .ThenInclude(bls => bls.UnitType)
                     .Include(booking => booking.BookingLine)
                         .ThenInclude(bl => bl.BookingLineAddress)
                         .ThenInclude(bla => bla.Country)
                     .Include(booking => booking.BookingLine)
                         .ThenInclude(bl => bl.BookingLineAddress)
                         .ThenInclude(bla => bla.PostalCode)
                     .Include(booking => booking.BookingLine)
                         .ThenInclude(bl => bl.BookingLineSpecification)
                         .ThenInclude(bls => bls.RelBookingLineSpecificationSalesInvoiceDetail)
                         .ThenInclude(Rel => Rel.SalesInvoiceDetail);

MSSQL服务器上挂起的SQL查询本身将变为:

     (@__GetCustomerTarget_Code_0 bigint,@__from_1 datetime2(7),@__to_2 datetime2(7),@__code_3 varchar(255))
        SELECT [booking].[Id], 
    [booking].[booking_provider_id], 
    [booking].[booking_status_id], 
    [booking].[consignee_name], 
    [booking].[consignee_number], 
    [booking].[created_at], 
    [booking].[created_by], 
    [booking].[currency_code], 
    [booking].[deliveryNumber], 
    [booking].[description], 
    [booking].[destroyed_at], 
    [booking].[destroyed_by], 
    [booking].[inter_company_number], 
    [booking].[invoicee_name], 
    [booking].[invoicee_number], 
    [booking].[is_create], 
    [booking].[location_id], 
    [booking].[location_name], 
    [booking].[maturity_level_id], 
    [booking].[number], 
    [booking].[order_number], 
    [booking].[provider_key], 
    [booking].[shipment_id], 
    [booking].[system_responsible_id], 
    [booking].[updated_at], 
    [booking].[updated_by]  
    FROM [Integration].[booking] AS [booking]  
WHERE ((([booking].[consignee_number] = @__GetCustomerTarget_Code_0) 
AND ([booking].[created_at] >= @__from_1)) 
AND ([booking].[created_at] < @__to_2)) 
AND EXISTS (      
SELECT 1      
FROM [Integration].[booking_line] AS [b]      
WHERE EXISTS (          
SELECT 1          
FROM [Integration].[booking_line_specification] AS [c]          
WHERE ([c].[currency_code] = @__code_3) AND ([b].[Id] = [c].[booking_line_id])) AND ([booking].[Id] = [b].[booking_id]))

在MSSQL management studio中执行时,此语句的执行时间为零秒(但有些毫秒)。 但是,C#应用程序会遇到超时。

然而,原因似乎正在改变。一开始是由于IO_完成。然后它的SOS_产生了一些东西,最后是PAGEIOLATCH_SH 这是它所处的最终状态

一、 就我个人而言,我无法理解为什么MSSQL可以毫无问题地执行查询。

我简直没有主意了。有人能给我指出一个可能有用的方向吗?

不。MSSQL似乎拒绝EF Core使用索引。

根据MSSQL management studio中的执行计划,我已经完全重建了高效执行此查询所需的索引。

任何进一步的信息,可能需要让我知道,我会看看我能做什么,尽我最大的努力。

更新实际执行计划:

更新2: 我想指出的是,这是目前正在使用的开发,因此这个数据库,我的软件和任何介于两者之间的,是在我的“控制”。

就我看来毫无经验的头脑所能控制的一切而言:)

因此,任何关于如何更好地调试问题的建议,或对更多数据的请求,都将受到热烈欢迎。而且很有可能,特别是如果暗示如何提供给你!(还有我)

SQL探查器:正在打开与数据库的连接:

set quoted_identifier on
set arithabort on
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed