首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
社区首页 >问答首页 >Linq实体继承生成大型SQL语句

Linq实体继承生成大型SQL语句
EN

Stack Overflow用户
提问于 2009-11-25 17:39:29
回答 1查看 381关注 0票数 0

我们正在开发一个具有超过10个子实体(继承自它)的基本实体的应用程序。

当我们使用Linq向基本实体发出任何请求时,我们都会得到一个SQL语句,每个子实体都有一个"UNION ALL“。要对基本实体执行Count(),需要近一秒的时间,而只获取一行可能需要两秒。

对于此代码:

代码语言:javascript
代码运行次数:0
运行
复制
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语句:

代码语言:javascript
代码运行次数:0
运行
复制
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的灵活性。

提前谢谢。

EN

回答 1

Stack Overflow用户

发布于 2009-11-28 17:05:02

Wow..that是一个很长的查询string..Have,你可以在.NET 4.0中尝试一下,看看会发生什么。也许你会得到一个更短的sql字符串。EF团队进行了许多性能改进

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/1795758

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档