我们正在开发一个具有超过10个子实体(继承自它)的基本实体的应用程序。
当我们使用Linq向基本实体发出任何请求时,我们都会得到一个SQL语句,每个子实体都有一个"UNION ALL“。要对基本实体执行Count(),需要近一秒的时间,而只获取一行可能需要两秒。
对于此代码:
public bool Exists(int appId, string loginName, DateTime userRegDate, long ahsayId)
{
var backupsets = from backupset in _entities.AhsayBackupSets
where
backupset.User.Appliance.Id == appId &&
backupset.User.LoginName == loginName &&
backupset.User.RegistrationDate == userRegDate &&
backupset.AhsayId == ahsayId
select backupset;
return backupsets.Count() > 0;
}
,我们得到这个SQL语句:
exec sp_executesql N'SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT(1) AS [A1]
FROM [dbo].[AhsayBackupSets] AS [Extent1]
LEFT OUTER JOIN (SELECT
[UnionAll9].[C1] AS [C1]
FROM (SELECT
[UnionAll8].[C1] AS [C1]
FROM (SELECT
[UnionAll7].[C1] AS [C1]
FROM (SELECT
[UnionAll6].[C1] AS [C1]
FROM (SELECT
[UnionAll5].[C1] AS [C1]
FROM (SELECT
[UnionAll4].[C1] AS [C1]
FROM (SELECT
[UnionAll3].[C1] AS [C1]
FROM (SELECT
[UnionAll2].[C1] AS [C1]
FROM (SELECT
[UnionAll1].[Id] AS [C1]
FROM (SELECT
[Extent2].[Id] AS [Id]
FROM [dbo].[AhsayOracleBackupSets] AS [Extent2]
UNION ALL
SELECT
[Extent3].[Id] AS [Id]
FROM [dbo].[AhsaySystemStateBackupSets] AS [Extent3]) AS [UnionAll1]
UNION ALL
SELECT
[Extent4].[Id] AS [Id]
FROM [dbo].[AhsayMysqlBackupSets] AS [Extent4]) AS [UnionAll2]
UNION ALL
SELECT
[Extent5].[Id] AS [Id]
FROM [dbo].[AhsayMssqlBackupSets] AS [Extent5]) AS [UnionAll3]
UNION ALL
SELECT
[Extent6].[Id] AS [Id]
FROM [dbo].[AhsayFileBackupSets] AS [Extent6]) AS [UnionAll4]
UNION ALL
SELECT
[Extent7].[Id] AS [Id]
FROM [dbo].[AhsayExchangeServerBackupSets] AS [Extent7]) AS [UnionAll5]
UNION ALL
SELECT
[Extent8].[Id] AS [Id]
FROM [dbo].[AhsayDominoBackupSets] AS [Extent8]) AS [UnionAll6]
UNION ALL
SELECT
[Extent9].[Id] AS [Id]
FROM [dbo].[AhsayNotesBackupSets] AS [Extent9]) AS [UnionAll7]
UNION ALL
SELECT
[Extent10].[Id] AS [Id]
FROM [dbo].[AhsayShadowProtectBackupSets] AS [Extent10]) AS [UnionAll8]
UNION ALL
SELECT
[Extent11].[Id] AS [Id]
FROM [dbo].[AhsayWindowsSystemBackupSets] AS [Extent11]) AS [UnionAll9]
UNION ALL
SELECT
[Extent12].[Id] AS [Id]
FROM [dbo].[AhsayExchangeMailBackupSets] AS [Extent12]) AS [UnionAll10] ON [Extent1].[Id] = [UnionAll10].[C1]
LEFT OUTER JOIN [dbo].[AhsayUsers] AS [Extent13] ON [Extent1].[AhsayUserId] = [Extent13].[Id]
INNER JOIN [dbo].[AhsayUsers] AS [Extent14] ON [Extent1].[AhsayUserId] = [Extent14].[Id]
WHERE ([Extent13].[ApplianceId] = @p__linq__0) AND ([Extent13].[LoginName] = @p__linq__1) AND ([Extent14].[RegistrationDate] = @p__linq__2) AND ([Extent1].[AhsayId] = @p__linq__3)
) AS [GroupBy1]',N'@p__linq__0 int,@p__linq__1 nvarchar(4000),@p__linq__2 datetime,@p__linq__3 bigint',@p__linq__0=2,@p__linq__1=N'antonio',@p__linq__2='2009-10-22 18:07:17',@p__linq__3=1256305376226
正如您可以想象的那样,这需要很多时间(在本例中是1秒,但还有一个句子要长得多,需要4秒),并且这个查询被多次执行。
有什么方法可以减少SQL开销吗?我们知道我们可以使用存储过程来处理繁重的句子,但我们不想失去Linq的灵活性。
提前谢谢。
发布于 2009-11-28 17:05:02
Wow..that是一个很长的查询string..Have,你可以在.NET 4.0中尝试一下,看看会发生什么。也许你会得到一个更短的sql字符串。EF团队进行了许多性能改进
https://stackoverflow.com/questions/1795758
复制相似问题