Bardzo rozbudowane zapytania - automapper, ef

0

Witam,
Czy jest jakiś sposób by zoptymalizowac zapytania budowane przez automappera ?
Niestety w SQL Profiler jak podpatrzyłem zapytania to idą potężne zapytania które niestety działają 3-4 razy dłużej niż napisane zapytanie z palca.
Rozumiem że jest to "automat" i nigdy nie zrobi w pełni optymalnego zapytanai ale pytanie czy musi np. zwracać tak dużą liczbe niepotrzebnych kolumn ?
Przykładowo wyciąga "klucze" z podzapytań i zwraca je do aplikacji

SELECT 
    [Project3].[Id1] AS [Id], 
    [Project3].[Id2] AS [Id1], 
    [Project3].[Id3] AS [Id2], 
    [Project3].[Id4] AS [Id3], 
    [Project3].[Id5] AS [Id4], 
    [Project3].[Id6] AS [Id5], 
    [Project3].[Id7] AS [Id6], 
    [Project3].[Id8] AS [Id7], 
    [Project3].[Id9] AS [Id8], 
    [Project3].[Id10] AS [Id9], 
    [Project3].[Id11] AS [Id10], 
    [Project3].[Id12] AS [Id11], 
    [Project3].[C1] AS [C1], 
    [Project3].[Id] AS [Id12], 
    [Project3].[Number] AS [Number], 
    [Project3].[C2] AS [C2], 
    [Project3].[Name] AS [Name], 
    [Project3].[Name1] AS [Name1], 
    [Project3].[CopiesCount] AS [CopiesCount], 
    [Project3].[C3] AS [C3], 
    [Project3].[Number1] AS [Number1]
    FROM ( SELECT 
        [Project1].[Id] AS [Id], 
        [Project1].[Id1] AS [Id1], 
        [Project1].[Id2] AS [Id2], 
        [Project1].[Id3] AS [Id3], 
        [Project1].[Id4] AS [Id4], 
        [Project1].[Id5] AS [Id5], 
        [Project1].[CopyNumber] AS [CopyNumber], 
        [Project1].[Id6] AS [Id6], 
        [Project1].[ConsecutiveNumber] AS [ConsecutiveNumber], 
        [Project1].[Id7] AS [Id7], 
        [Project1].[Id8] AS [Id8], 
        [Project1].[Id9] AS [Id9], 
        [Project1].[Number] AS [Number], 
        [Project1].[CopiesCount] AS [CopiesCount], 
       [Project1].[Id10] AS [Id10], 
        [Project1].[Id11] AS [Id11], **
        [Project1].[Name] AS [Name], 
        [Project1].[Id12] AS [Id12], 
        [Project1].[Name1] AS [Name1], 
        [Project1].[C1] AS [C1], 
        [Project1].[C2] AS [C2], 
        [Limit1].[Number] AS [Number1], 
        CASE WHEN ([Limit1].[Step] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C3]
        FROM   (SELECT 
            [Filter1].[Id1] AS [Id], 
            [Filter1].[hireId] AS [hireId], 
           [Filter1].[Id2] AS [Id1], 
            [Filter1].[Id3] AS [Id2], 
            [Extent4].[Id] AS [Id3], 
            [Extent5].[Id] AS [Id4], 
            [Extent6].[Id] AS [Id5], 
            [Extent6].[CopyNumber] AS [CopyNumber], 
            [Extent7].[Id] AS [Id6], 
            [Extent7].[ConsecutiveNumber] AS [ConsecutiveNumber], 
           [Extent8].[Id] AS [Id7], 
            [Extent9].[Id] AS [Id8], 
            [Extent10].[Id] AS [Id9], 
            [Extent10].[Number] AS [Number], 
            [Extent10].[CopiesCount] AS [CopiesCount], 
            [Extent11].[Id] AS [Id10], 
            [Extent12].[Id] AS [Id11], 
            [Extent12].[Name] AS [Name], 
            [Extent13].[Id] AS [Id12], 
            [Extent13].[Name] AS [Name1], 
            1 AS [C1], 
            CASE WHEN ([Extent9].[CopyNumber] IS NULL) THEN N'''' ELSE  CAST( [Extent9].[CopyNumber] AS nvarchar(max)) END AS [C2]
            FROM            (SELECT [Extent1].[Id] AS [Id1], [Extent1].[hireId] AS [hireId], [Extent1].[CurrentReceiverOrganizationId] AS [CurrentReceiverOrganizationId], [Extent2].[Id] AS [Id2], [Extent2].[hireStatusId] AS [hireStatusId], [Extent3].[Id] AS [Id3]
                FROM   [dbo].[Transfer] AS [Extent1]
                INNER JOIN [dbo].[hire] AS [Extent2] ON [Extent1].[hireId] = [Extent2].[Id]
                INNER JOIN [dbo].[Copy] AS [Extent3] ON [Extent2].[CopyId] = [Extent3].[Id]
                WHERE ([Extent1].[TransferEnd] IS NULL) AND ([Extent2].[IsSigned] = 1) AND (0 = [Extent3].[IsDisposed]) AND (0 = [Extent3].[IsMistaken]) ) AS [Filter1]
            LEFT OUTER JOIN [dbo].[hireStatus] AS [Extent4] ON [Filter1].[hireStatusId] = [Extent4].[Id]
            LEFT OUTER JOIN [dbo].[hire] AS [Extent5] ON [Filter1].[hireId] = [Extent5].[Id]
            LEFT OUTER JOIN [dbo].[Copy] AS [Extent6] ON [Extent5].[CopyId] = [Extent6].[Id]
            LEFT OUTER JOIN [dbo].[Document] AS [Extent7] ON [Extent6].[DocumentId] = [Extent7].[Id]
            LEFT OUTER JOIN [dbo].[hire] AS [Extent8] ON [Filter1].[hireId] = [Extent8].[Id]
            LEFT OUTER JOIN [dbo].[Copy] AS [Extent9] ON [Extent8].[CopyId] = [Extent9].[Id]
            LEFT OUTER JOIN [dbo].[Document] AS [Extent10] ON [Extent9].[DocumentId] = [Extent10].[Id]
            LEFT OUTER JOIN [dbo].[DocumentType] AS [Extent11] ON [Extent10].[DocumentTypeId] = [Extent11].[Id]
            LEFT OUTER JOIN [dbo].[Sort] AS [Extent12] ON [Extent11].[SortId] = [Extent12].[Id]
            LEFT OUTER JOIN [dbo].[ClassType] AS [Extent13] ON [Extent10].[ClassTypeId] = [Extent13].[Id]
            WHERE ([Extent4].[StatusSymbol] IN (21,3,36,37,43,38)) AND ([Filter1].[CurrentReceiverOrganizationId] = @p__linq__0) ) AS [Project1]
        OUTER APPLY  (SELECT TOP (3) [Project2].[Step] AS [Step], [Project2].[Number] AS [Number]
            FROM ( SELECT 
                [Extent15].[Step] AS [Step], 
                [Extent15].[Number] AS [Number]
                FROM  [dbo].[hire] AS [Extent14]
                INNER JOIN [dbo].[Book] AS [Extent15] ON [Extent14].[CopyId] = [Extent15].[CopyId]
                WHERE [Project1].[hireId] = [Extent14].[Id]
            )  AS [Project2]
            ORDER BY [Project2].[Step] DESC ) AS [Limit1]
    )  AS [Project3]
    ORDER BY [Project3].[ConsecutiveNumber] DESC, [Project3].[CopyNumber] ASC, [Project3].[Id1] ASC, [Project3].[Id2] ASC, [Project3].[Id3] ASC, [Project3].[Id4] ASC, [Project3].[Id5] ASC, [Project3].[Id6] ASC, [Project3].[Id7] ASC, [Project3].[Id8] ASC, [Project3].[Id9] ASC, [Project3].[Id10] ASC, [Project3].[Id11] ASC, [Project3].[Id12] ASC, [Project3].[Id] ASC, [Project3].[C3] ASC',N'@p__linq__0 uniqueidentifier',@p__linq__0='65A88242-F755-E911-80C7-005056A4F5EF'

jaki macie sposób by sobie z tym radzić ? Mam sporo rzeczy do zaciągania z bazy i korzystanie z automappera jest wygodne ale niestety te zapytania mnie przerażają.

0

Pokaż przykład struktury klas i mapowań, może uda się coś wymyślić.

0

Użyć prawdziwego ORMa.

0

Profil

 CreateMap<Transfer, BaseCopyViewModel>()
                    .ForMember(dest => dest.Number, opts => opts.MapFrom(src => src.hire.Copy.Document.Number))
                    .ForMember(dest => dest.CopyNumber, opts => opts.MapFrom(src => src.hire.Copy.CopyNumber))
                    .ForMember(dest => dest.DocumentType, opts => opts.MapFrom(src => src.hire.Copy.Document.DocumentType.Sort.Name))
                    .ForMember(dest => dest.ClassType, opts => opts.MapFrom(src => src.hire.Copy.Document.ClassType.Name))
                    .ForMember(m => m.CopiesCount, o => o.MapFrom(s => s.hire.Copy.Document.CopiesCount))
                    .ForMember(dest => dest.BookNumberList, opts => opts.MapFrom(src => src.hire.Copy.Books.OrderByDescending(s => s.Step).Select(s => s.Number).Take(3).ToList()))

                ;

Wywołanie

var model = db.Transfer.Where(s => s.CurrentReceiverOrganizationId == OrganizationId
                    && s.TransferEnd == null
                    && s.hire.Copy.IsDisposed == false
                    && s.hire.IsSigned
                    && s.hire.Copy.IsMistaken == false
                    && ((s.hire.hireStatus.StatusSymbol == (byte)hireStatusEnum.NP)
                    || (s.hire.hireStatus.StatusSymbol == (byte)hireStatusEnum.ToReceive_Directhire)
                    || (s.hire.hireStatus.StatusSymbol == (byte)hireStatusEnum.DP)
                    || (s.hire.hireStatus.StatusSymbol == (byte)hireStatusEnum.PP)
                    || (s.hire.hireStatus.StatusSymbol == (byte)hireStatusEnum.RP)
                    || (s.hire.hireStatus.StatusSymbol == (byte)hireStatusEnum.FP)).ProjectTo<BaseCopyViewModel>().ToList();
.

0

Porownujesz prędkość z ręcznie pisanym sqlem czy linq?

0

Użyj procedur ~~wbudowanych~~ składowanych

0

Porownujesz prędkość z ręcznie pisanym sqlem czy linq?

Porównuje z kodem pisanym w sql, skomplikowanie jeszcze bym przebolał ale po co on te wszystkie niepotrzebne kolumny przesyła ?

Użyj procedur wbudowanych składowanych

procedury niestety nie zapewnią mi tego co chcę osiągnąć, przykładowo wykorzystanie ustalonego mapowania dla wielu klas :

interface INumber{
string Number{get;set;}
}

CreateMap<Transfer,INumber>()
 .ForMember(dest => dest.Number, opts => opts.MapFrom(src => src.hire.Copy.Document.Number))
;

CreateMap<Transfer, BaseCopyViewModel>()
.IncludeBase<Transfer, INumber>()

CreateMap<Transfer, ExtendedCopyViewModel>()
.IncludeBase<Transfer, INumber>()

To jest bardzo wygodne. Oczywiście w SQL też mógłbym się skusic o małe funkcje, procedury itd ale wolałbym uniknąć tego w SQL bo jest dużo trudniej z utrzymaniem tego po stronie bazy danych. Chodzi mi o raczej optymalizację EF,Automappera albo może czy są inne narzędzia o podobnym działaniu a nie budujące tak skomplikowanych zapytań

1

Są, np. NHibernate.

Bardzo rozbudowane zapytania - automapper, ef

1 użytkowników online, w tym zalogowanych: 0, gości: 1

Praca dla programistów

Forum dyskusyjne

Sprawy administracyjne

O nas

Skontaktuj się z nami