Запрос, сгенерированный Entity Framework с использованием PatIndex, не работает
У меня есть запрос, сгенерированный кодом EF5, где я использую SqlFunctions.PatIndex, и выполнение запроса занимает 5 минут. Если я внесу изменения в SQL, чтобы использовать вместо него оператор like, то он будет работать меньше секунды. Из того, что я прочитал, предполагается, что PatIndex работает как минимум с оператором like. Похоже, что это некоторое взаимодействие с частью row_number() OVER, потому что если вы выполняете только самый внутренний выбор с предложением patindex, он также возвращается в течение секунды. Может кто-нибудь сказать мне, почему row_number() OVER убивает запрос PATINDEX, но не тот, который использует like?
Этот работает:
exec sp_executesql N'SELECT TOP (10)
[Project1].[C1] AS [C1],
[Project1].[PersonId] AS [PersonId],
[Project1].[FirstName] AS [FirstName],
[Project1].[MiddleName] AS [MiddleName],
[Project1].[LastName] AS [LastName],
[Project1].[Suffix] AS [Suffix],
[Project1].[DateOfBirth] AS [DateOfBirth],
[Project1].[Ssn] AS [Ssn],
[Project1].[CenterNumber] AS [CenterNumber],
[Project1].[GuarantorNumber] AS [GuarantorNumber],
[Project1].[GUNumber] AS [GUNumber],
[Project1].[IndustrialClientNumber] AS [IndustrialClientNumber],
[Project1].[Employer] AS [Employer]
FROM ( SELECT [Project1].[PersonId] AS [PersonId], [Project1].[CenterNumber] AS [CenterNumber], [Project1].[GuarantorNumber] AS [GuarantorNumber], [Project1].[GUNumber] AS [GUNumber], [Project1].[IndustrialClientNumber] AS [IndustrialClientNumber], [Project1].[Employer] AS [Employer], [Project1].[FirstName] AS [FirstName], [Project1].[MiddleName] AS [MiddleName], [Project1].[LastName] AS [LastName], [Project1].[Suffix] AS [Suffix], [Project1].[DateOfBirth] AS [DateOfBirth], [Project1].[Ssn] AS [Ssn], [Project1].[C1] AS [C1], row_number() OVER (ORDER BY [Project1].[FirstName] ASC) AS [row_number]
FROM ( SELECT
[Extent1].[PersonId] AS [PersonId],
[Extent1].[CenterNumber] AS [CenterNumber],
[Extent1].[GuarantorNumber] AS [GuarantorNumber],
[Extent1].[GUNumber] AS [GUNumber],
[Extent1].[IndustrialClientNumber] AS [IndustrialClientNumber],
[Extent1].[Employer] AS [Employer],
[Extent2].[FirstName] AS [FirstName],
[Extent2].[MiddleName] AS [MiddleName],
[Extent2].[LastName] AS [LastName],
[Extent2].[Suffix] AS [Suffix],
[Extent2].[DateOfBirth] AS [DateOfBirth],
[Extent2].[Ssn] AS [Ssn],
''0X0X'' AS [C1]
FROM [dbo].[vGuarantor] AS [Extent1]
INNER JOIN [dbo].[vPerson] AS [Extent2] ON [Extent1].[PersonId] = [Extent2].[PersonId]
WHERE ([Extent1].[CenterNumber] = @p__linq__0) AND ***([Extent1].[GuarantorNumber] like @p__linq__1)***
) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[FirstName] ASC
',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',@p__linq__0=N'2',@p__linq__1=N'13100%'
Этот не:
exec sp_executesql N'SELECT TOP (10)
[Project1].[C1] AS [C1],
[Project1].[PersonId] AS [PersonId],
[Project1].[FirstName] AS [FirstName],
[Project1].[MiddleName] AS [MiddleName],
[Project1].[LastName] AS [LastName],
[Project1].[Suffix] AS [Suffix],
[Project1].[DateOfBirth] AS [DateOfBirth],
[Project1].[Ssn] AS [Ssn],
[Project1].[CenterNumber] AS [CenterNumber],
[Project1].[GuarantorNumber] AS [GuarantorNumber],
[Project1].[GUNumber] AS [GUNumber],
[Project1].[IndustrialClientNumber] AS [IndustrialClientNumber],
[Project1].[Employer] AS [Employer]
FROM ( SELECT [Project1].[PersonId] AS [PersonId], [Project1].[CenterNumber] AS [CenterNumber], [Project1].[GuarantorNumber] AS [GuarantorNumber], [Project1].[GUNumber] AS [GUNumber], [Project1].[IndustrialClientNumber] AS [IndustrialClientNumber], [Project1].[Employer] AS [Employer], [Project1].[FirstName] AS [FirstName], [Project1].[MiddleName] AS [MiddleName], [Project1].[LastName] AS [LastName], [Project1].[Suffix] AS [Suffix], [Project1].[DateOfBirth] AS [DateOfBirth], [Project1].[Ssn] AS [Ssn], [Project1].[C1] AS [C1], row_number() OVER (ORDER BY [Project1].[FirstName] ASC) AS [row_number]
FROM ( SELECT
[Extent1].[PersonId] AS [PersonId],
[Extent1].[CenterNumber] AS [CenterNumber],
[Extent1].[GuarantorNumber] AS [GuarantorNumber],
[Extent1].[GUNumber] AS [GUNumber],
[Extent1].[IndustrialClientNumber] AS [IndustrialClientNumber],
[Extent1].[Employer] AS [Employer],
[Extent2].[FirstName] AS [FirstName],
[Extent2].[MiddleName] AS [MiddleName],
[Extent2].[LastName] AS [LastName],
[Extent2].[Suffix] AS [Suffix],
[Extent2].[DateOfBirth] AS [DateOfBirth],
[Extent2].[Ssn] AS [Ssn],
''0X0X'' AS [C1]
FROM [dbo].[vGuarantor] AS [Extent1]
INNER JOIN [dbo].[vPerson] AS [Extent2] ON [Extent1].[PersonId] = [Extent2].[PersonId]
WHERE ([Extent1].[CenterNumber] = @p__linq__0) AND ***(PATINDEX(@p__linq__1, [Extent1].[GuarantorNumber]) > 0)***
) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[FirstName] ASC
',N'@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000)',@p__linq__0=N'2',@p__linq__1=N'13100%'
Вот две таблицы:
/****** Object: Table [Person] Script Date: 5/30/2013 8:17:15 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [Person](
[PersonId] [uniqueidentifier] NOT NULL,
[FirstName] [nvarchar](255) NULL,
[MiddleName] [nvarchar](255) NULL,
[LastName] [nvarchar](255) NULL,
[DateOfBirth] [datetime] NULL,
[DateOfBirthIsGuess] [bit] NULL,
[NickName] [nvarchar](255) NULL,
[Suffix] [nchar](10) NULL,
[Gender] [char](1) NULL,
[SSN] [numeric](9, 0) NULL,
CONSTRAINT [PK__Person__04E4BC85] PRIMARY KEY CLUSTERED
(
[PersonId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Index [PK__Person__04E4BC85] Script Date: 7/18/2013 8:19:16 AM ******/
ALTER TABLE [PatientFirst].[Person] ADD CONSTRAINT [PK__Person__04E4BC85] PRIMARY KEY CLUSTERED
(
[PersonId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
GO
/****** Object: Index [IDX_Person.LastName] Script Date: 7/18/2013 8:19:24 AM ******/
CREATE NONCLUSTERED INDEX [IDX_Person.LastName] ON [PatientFirst].[Person]
(
[LastName] ASC
)
INCLUDE ( [DateOfBirth],
[FirstName],
[MiddleName],
[PersonId],
[SSN],
[Suffix]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IDX_Person.FirstName] Script Date: 7/18/2013 8:19:37 AM ******/
CREATE NONCLUSTERED INDEX [IDX_Person.FirstName] ON [PatientFirst].[Person]
(
[FirstName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IDX_Person.DOB] Script Date: 7/18/2013 8:19:47 AM ******/
CREATE NONCLUSTERED INDEX [IDX_Person.DOB] ON [PatientFirst].[Person]
(
[DateOfBirth] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Table [Guarantor] Script Date: 5/30/2013 8:17:11 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [Guarantor](
[GuarantorId] [uniqueidentifier] NOT NULL,
[CenterNumber] [nvarchar](10) NOT NULL,
[GuarantorNumber] [nvarchar](10) NOT NULL,
[IndustrialClientNumber] [int] NULL,
[Employer] [varchar](4000) NULL,
[ImportDate] [datetime] NULL,
[GUNumber] AS (([CenterNumber]+'*')+[GuarantorNumber]),
CONSTRAINT [PK__Guarantor__44FF419A] PRIMARY KEY CLUSTERED
(
[GuarantorId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [Guarantor] ADD CONSTRAINT [DF__Guarantor__Guara__45F365D3] DEFAULT (newid()) FOR [GuarantorId]
GO
ALTER TABLE [Guarantor] ADD CONSTRAINT [DF_Guarantor_ImportDate] DEFAULT (getdate()) FOR [ImportDate]
GO
ALTER TABLE [Guarantor] WITH CHECK ADD CONSTRAINT [FK_Guarantor_Person] FOREIGN KEY([GuarantorId])
REFERENCES [Person] ([PersonId])
GO
ALTER TABLE [Guarantor] CHECK CONSTRAINT [FK_Guarantor_Person]
GO
/****** Object: Index [PK__Guarantor__44FF419A] Script Date: 7/18/2013 8:17:43 AM ******/
ALTER TABLE [PatientFirst].[Guarantor] ADD CONSTRAINT [PK__Guarantor__44FF419A] PRIMARY KEY CLUSTERED
(
[GuarantorId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
GO
/****** Object: Index [IDX_Guarantor_CenterNumber] Script Date: 7/18/2013 8:16:40 AM ******/
CREATE NONCLUSTERED INDEX [IDX_Guarantor_CenterNumber] ON [PatientFirst].[Guarantor]
(
[CenterNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IDX_Guarantor_CenterNumberGuarantorNumber] Script Date: 7/18/2013 8:16:45 AM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IDX_Guarantor_CenterNumberGuarantorNumber] ON [PatientFirst].[Guarantor]
(
[CenterNumber] ASC,
[GuarantorNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
GO
/****** Object: Index [IDX_Guarantor_GuarantorNumber] Script Date: 7/18/2013 8:17:01 AM ******/
CREATE NONCLUSTERED INDEX [IDX_Guarantor_GuarantorNumber] ON [PatientFirst].[Guarantor]
(
[GuarantorNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Index [IDX_Guarantor_GUNumber] Script Date: 7/18/2013 8:17:09 AM ******/
CREATE NONCLUSTERED INDEX [IDX_Guarantor_GUNumber] ON [PatientFirst].[Guarantor]
(
[GUNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
GO
/****** Object: Index [IDX_Guarantor_ImportDate] Script Date: 7/18/2013 8:17:21 AM ******/
CREATE NONCLUSTERED INDEX [IDX_Guarantor_ImportDate] ON [PatientFirst].[Guarantor]
(
[ImportDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
GO
/****** Object: Index [IDX_Guarantor_IndustrialClientNumber] Script Date: 7/18/2013 8:17:30 AM ******/
CREATE NONCLUSTERED INDEX [IDX_Guarantor_IndustrialClientNumber] ON [PatientFirst].[Guarantor]
(
[IndustrialClientNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
GO
Представления, используемые в запросах, просто выбираются * из базовых таблиц.
План выполнения для подобного оператора:
https://docs.google.com/file/d/0B4uS27e7ZMfCOTl5LS1UaHBSOTQ/edit?usp=sharing
План Patindex: https://docs.google.com/file/d/0B4uS27e7ZMfCVTljNml4R0xjT28/edit?usp=sharing
1 ответ
Я подозреваю, что производительность обоих запросов для поиска "содержит" (например: %3%00%) будет одинаково плохо. Однако, если большинство ваших запросов включают префикс (например: 131%00%), вы можете улучшить производительность, добавив StartsWith состояние.
Например:
var query = context.Guarantors.Where(g => g.CenterNumber == centerNumber);
int index = guarantorNumber.IndexOfAny(new[]{ '%', '_', '[' });
switch (index)
{
case -1:
{
query = query.Where(g => g.GuarantorNumber == guarantorNumber);
break;
}
case 0:
{
// Nothing we can do here. The query performance will suck.
query = query.Where(g => SqlFunctions.PatIndex(guarantorNumber, g.GuarantorNumber) > 0);
break;
}
default:
{
string prefix = guarantorNumber.Substring(0, index);
query = query.Where(g => g.GuarantorNumber.StartsWith(prefix));
if (index != guarantorNumber.Length - 1 || guarantorNumber[index] != '%')
{
query = query.Where(g => SqlFunctions.PatIndex(guarantorNumber, g.GuarantorNumber) > 0);
}
break;
}
}
NB: Вы также можете попробовать обновить свою статистику. План выполнения показывает значительную разницу между оценочными и фактическими строками для обеих таблиц.
- Поручитель: оценочный 7188, фактический 107345
- Человек: Расчетный 7102, фактический 6858694
Из огромной разницы, я думаю, у вас есть Auto Update Statistics опция отключена, и вы не запланировали задание на ручное обновление статистики.
Попробуйте следующие команды:
UPDATE STATISTICS Person;
UPDATE STATISTICS Guarantor;
Это должно дать SQL достаточно информации, чтобы выбрать более разумный план выполнения.