Performance issue
Joined: 04-Jan-2007
Joined: 08-Oct-2008
Joined: 04-Jan-2007
Thanks for your reply.
-
make SURE you're using the latest build of LLBLGen Pro and the runtime libraries. Yes I'm am. the latest version downloaded from the customer area.
-
enable tracing of the DQE through the config file and obtain the generated query. I did that I gess. But it was cropped from the initial post as it was too long... I'm going to attach it here
-
enable LinqExpressionHandler tracing (see documentation) at level 3 to obtain the expression tree in text. I have added that to the tracing. I was not tracing the linq expression
-
if you run into a crash, always provide the FULL stacktrace. Not a crash
- always provide the full query in linq code the full code is:
private static IPathEdge[] styleInfoPathEdge = new IPathEdge[]{ new PathEdge<SerieEntity>(StyleEntity.PrefetchPathStyleSeries, serie=>serie.SeriePictureId!=null && serie.SerieApproved, 4), };
public PagedResultDto<StyleInfoDto> FindStyleInfos(int pageIndex, int pageSize, StyleSortEnumDto sort, bool ascending, string queryText) {
if (pageSize <= 0)
throw new ApplicationException(ErrorMessages.PAGESIZE_MUST_BE_BT_0);
if (pageIndex < 0)
throw new ApplicationException(ErrorMessages.PAGEINDEX_MUST_BE_BT_0);
using (var adapter = new DataAccessAdapter())
{
var metaData = new LinqMetaData(adapter);
var query = (IQueryable<StyleEntity>)metaData.Style;
var filter = DefautlFilterOnStyle();
if (!string.IsNullOrEmpty(queryText))
filter = filter.And(x => x.StyleNameCi.Contains(queryText));
query = query.Where(filter);
var totalCount = query.Count();
var sortExpression = ToSortExpression(sort);
if (ascending)
query = query.OrderBy(sortExpression);
else
query = query.OrderByDescending(sortExpression);
var entities = query.WithPath(styleInfoPathEdge).Skip(pageSize * pageIndex).Take(pageSize).ToList();
return new PagedResultDto<StyleInfoDto>
{
PageIndex = pageIndex,
PageSize = pageSize,
TotalCount = totalCount,
Items = entities.ToStyleInfoDtos()
};
}
}
private Expression<Func<StyleEntity, bool>> DefautlFilterOnStyle() { if (CurrentUser.CanViewUnApproved()) return (Style => true); else return (Style => Style.StyleApproved || Style.StyleCreatedBy == CurrentUser.UserId); }
private Expression<Func<StyleEntity, object>> ToSortExpression(StyleSortEnumDto sort)
{
Expression<Func<StyleEntity, object>> sortExpression;
switch (sort)
{
case StyleSortEnumDto.Name:
default:
sortExpression = (x => x.StyleNameCi);
break;
}
return sortExpression;
}
- always provide information about inheritance structures of ALL entities involved in the query No inheritance.They are Flat entities.
- always provide DDL SQL for the tables involved. The tables are quit simple one foreign key from a table to another. But I'm gonna attach it here at well.
[Update] I had to split the Trace in two files because of the 1024 KB file limit on the forum I tried to play with the threshold but it had no real influence. I do not understand why it retreives the 4000 series for the 25 styles.
DDL
CREATE TABLE [dbo].[bd_Style]( [StyleId] [int] IDENTITY(1,1) NOT NULL, [StyleName] varchar NOT NULL, [StyleApproved] [bit] NOT NULL, [StyleScoreTotal] [int] NULL, [StyleScoreNbrOfVote] [int] NULL, [StyleViewed] [int] NULL, [StyleLanguage] varchar NOT NULL, [StyleCreatedOn] [datetime] NOT NULL, [StyleCreatedBy] [uniqueidentifier] NOT NULL, [StyleModifiedOn] [datetime] NOT NULL, [StyleModifiedBy] [uniqueidentifier] NOT NULL, [StyleNameCi] varchar NOT NULL, [StylePictureId] [uniqueidentifier] NULL, CONSTRAINT [PK_bd_Style_1] PRIMARY KEY CLUSTERED ( [StyleId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
CREATE TABLE [dbo].[bd_Serie]( [SerieId] [int] IDENTITY(1,1) NOT NULL, [SeriePublisherId] [int] NOT NULL, [SerieBDCollectionId] [int] NULL, [SerieStyleId] [int] NULL, [SerieName] nvarchar NOT NULL, [SerieNameCI] nvarchar NOT NULL, [SerieNameFormated] nvarchar NOT NULL, [SerieNameCIFormated] nvarchar NOT NULL, [SerieResume] nvarchar NULL, [SerieStatus] [int] NOT NULL, [SerieApproved] [bit] NOT NULL, [SerieScoreTotal] [int] NULL, [SerieScoreNbrOfVote] [int] NULL, [SerieViewed] [int] NULL, [SerieLanguage] varchar NOT NULL, [SerieCreatedOn] [datetime] NOT NULL, [SerieCreatedBy] [uniqueidentifier] NOT NULL, [SerieModifiedOn] [datetime] NOT NULL, [SerieModifiedBy] [uniqueidentifier] NOT NULL, [SerieHistoryScoreTotal] [int] NULL, [SerieHistoryScoreNbrVotes] [int] NULL, [SerieDrawingScoreTotal] [int] NULL, [SerieDrawingScoreNbrVotes] [int] NULL, [SeriePictureId] [uniqueidentifier] NULL, [SerieDrawingScore] [int] NULL, [SerieHistoryScore] [int] NULL, [SerieScore] [int] NULL, CONSTRAINT [PK_bd_Serie_1] PRIMARY KEY CLUSTERED ( [SerieId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
ALTER TABLE [dbo].[bd_Serie] WITH NOCHECK ADD CONSTRAINT [FK_bd_Serie_bd_Style] FOREIGN KEY([SerieStyleId]) REFERENCES [dbo].[bd_Style] ([StyleId]) GO
ALTER TABLE [dbo].[bd_Serie] CHECK CONSTRAINT [FK_bd_Serie_bd_Style] GO
Joined: 04-Jan-2007
Joined: 08-Oct-2008