Hello!

Currently running version 2.6 Final (april 15th 2009) in adapter mode.

We have been using LLBGEN for a while now and so far haven't really had any problems with it, but recently as our customer base has grown and our queries are bringing back more data (not massive amounts, just more than they used it) unfortunately for some reason our worker process memory is sky rocketing when we run certain queries, so much so it's taking the website down after a while.

Once particular function is as follows;

  public static EntityCollection GetAllOrdersWhenShowPrintStationIsFalse(Guid portalId, ConfigManager configuration)
        {
            IRelationPredicateBucket bucket = new RelationPredicateBucket();
            bucket.Relations.Add(OrderDetailEntity.Relations.OrderEntityUsingOrderId);
            bucket.Relations.Add(OrderEntity.Relations.UserEntityUsingUserId);
            bucket.Relations.Add(OrderDetailEntity.Relations.ProductEntityUsingProductId);
            bucket.Relations.Add(OrderDetailEntity.Relations.OrderDetailStatusEntityUsingOrderDetailStatusId);
            bucket.Relations.Add(OrderDetailEntity.Relations.DocumentEntityUsingDocumentId, JoinHint.Left);

            bucket.PredicateExpression.Add(OrderFields.PortalId == portalId);
            bucket.PredicateExpression.Add(OrderDetailStatusFields.ShowPrintStation == false);
            bucket.PredicateExpression.Add(OrderFields.PendingConfirmation == false);
            EntityCollection orders = new EntityCollection(new OrderDetailEntityFactory());
            PrefetchPath2 prefetch = new PrefetchPath2((int)EntityType.OrderDetailEntity);
            prefetch.Add(OrderDetailEntity.PrefetchPathDocument);
            prefetch.Add(OrderDetailEntity.PrefetchPathOrder).SubPath.Add(OrderEntity.PrefetchPathUser);
            prefetch.Add(OrderDetailEntity.PrefetchPathOrderDetailStatus);
            prefetch.Add(OrderDetailEntity.PrefetchPathProduct);

            using (DataAccessAdapter a = new DataAccessAdapter())
            {
                a.FetchEntityCollection(orders, bucket, prefetch);
            }

            return orders;
        }

When the " a.FetchEntityCollection(orders, bucket, prefetch);" runs it takes over a minute to complete and the worker process memory jumps from 40,000K to 553,000K!! The particular query above only brings back 300 rows!

With debugging on the following prints to the output window

Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [dbo].[OrderDetail].[OrderDetailId], [dbo].[OrderDetail].[ShortOrderDetailId], [dbo].[OrderDetail].[OrderId], [dbo].[OrderDetail].[DocumentId], [dbo].[OrderDetail].[OrderDetailStatusId], [dbo].[OrderDetail].[JobticketId], [dbo].[OrderDetail].[Jdf], [dbo].[OrderDetail].[Price], [dbo].[OrderDetail].[ProductXml], [dbo].[OrderDetail].[PrintstationId], [dbo].[OrderDetail].[PaperCopies], [dbo].[OrderDetail].[ProductID] AS [ProductId], [dbo].[OrderDetail].[Cancellled] FROM ((((( [dbo].[Order] INNER JOIN [dbo].[OrderDetail] ON [dbo].[Order].[OrderId]=[dbo].[OrderDetail].[OrderId]) INNER JOIN [dbo].[User] ON [dbo].[User].[UserId]=[dbo].[Order].[UserId]) INNER JOIN [dbo].[Product] ON [dbo].[Product].[ProductId]=[dbo].[OrderDetail].[ProductID]) INNER JOIN [dbo].[OrderDetailStatus] ON [dbo].[OrderDetailStatus].[OrderDetailStatusId]=[dbo].[OrderDetail].[OrderDetailStatusId]) LEFT JOIN [dbo].[Document] ON [dbo].[Document].[DocumentId]=[dbo].[OrderDetail].[DocumentId]) WHERE ( ( [dbo].[Order].[PortalId] = @PortalId1 AND [dbo].[OrderDetailStatus].[ShowPrintStation] = @ShowPrintStation2 AND [dbo].[Order].[PendingConfirmation] = @PendingConfirmation3)) Parameter: @PortalId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2dd8645a-b0ff-4e95-ac13-eae2a26dd2d4. Parameter: @ShowPrintStation2 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False. Parameter: @PendingConfirmation3 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.

Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging. The thread 0x1a1c has exited with code 0 (0x0). The thread 0x12c4 has exited with code 0 (0x0). Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSubQuery Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [dbo].[OrderDetail].[DocumentId] FROM ((((( [dbo].[Order] INNER JOIN [dbo].[OrderDetail] ON [dbo].[Order].[OrderId]=[dbo].[OrderDetail].[OrderId]) INNER JOIN [dbo].[User] ON [dbo].[User].[UserId]=[dbo].[Order].[UserId]) INNER JOIN [dbo].[Product] ON [dbo].[Product].[ProductId]=[dbo].[OrderDetail].[ProductID]) INNER JOIN [dbo].[OrderDetailStatus] ON [dbo].[OrderDetailStatus].[OrderDetailStatusId]=[dbo].[OrderDetail].[OrderDetailStatusId]) LEFT JOIN [dbo].[Document] ON [dbo].[Document].[DocumentId]=[dbo].[OrderDetail].[DocumentId]) WHERE ( ( ( [dbo].[Order].[PortalId] = @PortalId1 AND [dbo].[OrderDetailStatus].[ShowPrintStation] = @ShowPrintStation2 AND [dbo].[Order].[PendingConfirmation] = @PendingConfirmation3))) Parameter: @PortalId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2dd8645a-b0ff-4e95-ac13-eae2a26dd2d4. Parameter: @ShowPrintStation2 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False. Parameter: @PendingConfirmation3 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.

Method Exit: CreateSelectDQ Method Exit: CreateSubQuery Generated Sql query: Query: SELECT [dbo].[Document].[DocumentId], [dbo].[Document].[LibraryId], [dbo].[Document].[FileName], [dbo].[Document].[Description], [dbo].[Document].[OriginalExtension], [dbo].[Document].[FinalExtension], [dbo].[Document].[StatusId], [dbo].[Document].[CreatedDate], [dbo].[Document].[UpdateDate], [dbo].[Document].[MissingFonts], [dbo].[Document].[NumberOfPages], [dbo].[Document].[FileTypeId], [dbo].[Document].[LastOrderedDate], [dbo].[Document].[FileSize], [dbo].[Document].[ErrorMessage], [dbo].[Document].[UploadedBy], [dbo].[Document].[ProductXml], [dbo].[Document].[ProductId] FROM [dbo].[Document] WHERE ( [dbo].[Document].[DocumentId] IN (SELECT [dbo].[OrderDetail].[DocumentId] FROM ((((( [dbo].[Order] INNER JOIN [dbo].[OrderDetail] ON [dbo].[Order].[OrderId]=[dbo].[OrderDetail].[OrderId]) INNER JOIN [dbo].[User] ON [dbo].[User].[UserId]=[dbo].[Order].[UserId]) INNER JOIN [dbo].[Product] ON [dbo].[Product].[ProductId]=[dbo].[OrderDetail].[ProductID]) INNER JOIN [dbo].[OrderDetailStatus] ON [dbo].[OrderDetailStatus].[OrderDetailStatusId]=[dbo].[OrderDetail].[OrderDetailStatusId]) LEFT JOIN [dbo].[Document] ON [dbo].[Document].[DocumentId]=[dbo].[OrderDetail].[DocumentId]) WHERE ( ( ( [dbo].[Order].[PortalId] = @PortalId1 AND [dbo].[OrderDetailStatus].[ShowPrintStation] = @ShowPrintStation2 AND [dbo].[Order].[PendingConfirmation] = @PendingConfirmation3))))) Parameter: @PortalId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2dd8645a-b0ff-4e95-ac13-eae2a26dd2d4. Parameter: @ShowPrintStation2 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False. Parameter: @PendingConfirmation3 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.

Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging. Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSubQuery Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [dbo].[OrderDetail].[OrderId] FROM ((((( [dbo].[Order] INNER JOIN [dbo].[OrderDetail] ON [dbo].[Order].[OrderId]=[dbo].[OrderDetail].[OrderId]) INNER JOIN [dbo].[User] ON [dbo].[User].[UserId]=[dbo].[Order].[UserId]) INNER JOIN [dbo].[Product] ON [dbo].[Product].[ProductId]=[dbo].[OrderDetail].[ProductID]) INNER JOIN [dbo].[OrderDetailStatus] ON [dbo].[OrderDetailStatus].[OrderDetailStatusId]=[dbo].[OrderDetail].[OrderDetailStatusId]) LEFT JOIN [dbo].[Document] ON [dbo].[Document].[DocumentId]=[dbo].[OrderDetail].[DocumentId]) WHERE ( ( ( [dbo].[Order].[PortalId] = @PortalId1 AND [dbo].[OrderDetailStatus].[ShowPrintStation] = @ShowPrintStation2 AND [dbo].[Order].[PendingConfirmation] = @PendingConfirmation3))) Parameter: @PortalId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2dd8645a-b0ff-4e95-ac13-eae2a26dd2d4. Parameter: @ShowPrintStation2 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False. Parameter: @PendingConfirmation3 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.

Method Exit: CreateSelectDQ Method Exit: CreateSubQuery Generated Sql query: Query: SELECT [dbo].[Order].[OrderId], [dbo].[Order].[OrderDate], [dbo].[Order].[ShortOrderId], [dbo].[Order].[UserId], [dbo].[Order].[AccountCode], [dbo].[Order].[ApprovalRequired], [dbo].[Order].[Approved], [dbo].[Order].[ApprovedBy], [dbo].[Order].[ApprovedDate], [dbo].[Order].[PortalId], [dbo].[Order].[AwaitingQuote], [dbo].[Order].[Price], [dbo].[Order].[OrderComplete], [dbo].[Order].[CompletedDate], [dbo].[Order].[PendingConfirmation] FROM [dbo].[Order] WHERE ( [dbo].[Order].[OrderId] IN (SELECT [dbo].[OrderDetail].[OrderId] FROM ((((( [dbo].[Order] INNER JOIN [dbo].[OrderDetail] ON [dbo].[Order].[OrderId]=[dbo].[OrderDetail].[OrderId]) INNER JOIN [dbo].[User] ON [dbo].[User].[UserId]=[dbo].[Order].[UserId]) INNER JOIN [dbo].[Product] ON [dbo].[Product].[ProductId]=[dbo].[OrderDetail].[ProductID]) INNER JOIN [dbo].[OrderDetailStatus] ON [dbo].[OrderDetailStatus].[OrderDetailStatusId]=[dbo].[OrderDetail].[OrderDetailStatusId]) LEFT JOIN [dbo].[Document] ON [dbo].[Document].[DocumentId]=[dbo].[OrderDetail].[DocumentId]) WHERE ( ( ( [dbo].[Order].[PortalId] = @PortalId1 AND [dbo].[OrderDetailStatus].[ShowPrintStation] = @ShowPrintStation2 AND [dbo].[Order].[PendingConfirmation] = @PendingConfirmation3))))) Parameter: @PortalId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2dd8645a-b0ff-4e95-ac13-eae2a26dd2d4. Parameter: @ShowPrintStation2 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False. Parameter: @PendingConfirmation3 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.

Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging. Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [dbo].[User].[UserId], [dbo].[User].[ApplicationName], [dbo].[User].[UserName], [dbo].[User].[DeliveryXml], [dbo].[User].[DeliveryTemplateId] FROM [dbo].[User] WHERE ( [dbo].[User].[UserId] IN (@UserId1, @UserId2, @UserId3, @UserId4, @UserId5, @UserId6, @UserId7, @UserId8, @UserId9, @UserId10, @UserId11, @UserId12, @UserId13, @UserId14, @UserId15, @UserId16)) Parameter: @UserId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2c6f48a0-f123-4efa-8863-6b89de1d6051. Parameter: @UserId2 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 489654f7-7562-468a-857a-43690470ee53. Parameter: @UserId3 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: e27c85be-acfb-4017-ae39-34ff76246905. Parameter: @UserId4 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: fd891d6a-e596-497e-a3f2-ad4f0365d3ce. Parameter: @UserId5 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 6c85ce7f-cbf2-4500-a1be-b3bb021a5cda. Parameter: @UserId6 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: a8159fbb-e07b-4ad7-8cf2-46671bd182c2. Parameter: @UserId7 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3c0085a2-0863-411c-ae6a-61d49b13f7dd. Parameter: @UserId8 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 9076130c-d96e-4382-a834-3febbf7712b4. Parameter: @UserId9 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 7667dfec-b2e1-4217-977b-6dc7325fc05f. Parameter: @UserId10 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 60ee1cb4-5eb7-4f38-8833-54da4e09a60c. Parameter: @UserId11 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 95f9d5e7-aebb-42ca-bb88-15fd661b4012. Parameter: @UserId12 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0af3c501-cba3-4754-b114-bf0be25f4bcf. Parameter: @UserId13 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 04dd43de-ebef-438e-9296-526fc9655e20. Parameter: @UserId14 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: d95f26b0-4574-4982-a903-bf89b6de017b. Parameter: @UserId15 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 42571b74-2e5e-4909-8625-99c11c15de9c. Parameter: @UserId16 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 412a9ee8-6211-408d-923b-b8bc4bed2177.

Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging. Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [dbo].[OrderDetailStatus].[OrderDetailStatusId], [dbo].[OrderDetailStatus].[PortalId], [dbo].[OrderDetailStatus].[StatusName], [dbo].[OrderDetailStatus].[ShowUser], [dbo].[OrderDetailStatus].[EndUserDisplay], [dbo].[OrderDetailStatus].[EmailUser], [dbo].[OrderDetailStatus].[AllowDelete], [dbo].[OrderDetailStatus].[ShowPrintStation], [dbo].[OrderDetailStatus].[InitialStatus], [dbo].[OrderDetailStatus].[AwaitingPaperOriginals], [dbo].[OrderDetailStatus].[QueryOutstanding], [dbo].[OrderDetailStatus].[InProduction], [dbo].[OrderDetailStatus].[Printed], [dbo].[OrderDetailStatus].[Deleted], [dbo].[OrderDetailStatus].[Archived], [dbo].[OrderDetailStatus].[Complete], [dbo].[OrderDetailStatus].[Downloaded], [dbo].[OrderDetailStatus].[SystemStatus], [dbo].[OrderDetailStatus].[StatusDeleted], [dbo].[OrderDetailStatus].[EmailContent], [dbo].[OrderDetailStatus].[EmailSubject] FROM [dbo].[OrderDetailStatus] WHERE ( [dbo].[OrderDetailStatus].[OrderDetailStatusId] = @OrderDetailStatusId1) Parameter: @OrderDetailStatusId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 6bb642bc-1123-4ae6-9b38-36a63dde7db1.

Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging. Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT [dbo].[Product].[ProductId], [dbo].[Product].[Image], [dbo].[Product].[Name], [dbo].[Product].[Description], [dbo].[Product].[ProductXml], [dbo].[Product].[PortalId], [dbo].[Product].[Deleted], [dbo].[Product].[TemplateProduct] FROM [dbo].[Product] WHERE ( [dbo].[Product].[ProductId] IN (@ProductId1, @ProductId2, @ProductId3, @ProductId4, @ProductId5, @ProductId6, @ProductId7, @ProductId ) Parameter: @ProductId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0cc40a71-91d6-4b85-b224-9a46f1f42ad1. Parameter: @ProductId2 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: eca18dc9-c331-4dc9-a4a5-a1cde8938079. Parameter: @ProductId3 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 6971c7be-7c74-4ff2-9db2-670cc008efa6. Parameter: @ProductId4 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3fc5937f-1ac8-4f5a-9be2-29d775a74894. Parameter: @ProductId5 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 28945c27-3962-4b26-a10c-00beed39f233. Parameter: @ProductId6 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2a613b68-5e8a-4368-b9b5-eb24955bf17c. Parameter: @ProductId7 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1b7c7698-aeca-41af-85ff-f583b27ffc67. Parameter: @ProductId8 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8ce657aa-9b2d-45da-9df8-5b2595f527d8.

Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging.

The Memory usage can go up to 1.7GB if a few people are using the site and it takes it down which is obviously causing us major issues! The only way we can revive it is by killing the worker process as the memory doesn't come back down.

I am assuming this is something I am doing wrong unless people would be screaming left right and center about this if it were a bug and I can't see anyone doing that. Please please can you point me in the direction of the best way of fixing this!

Thanks

Bex

Ok. The main query returns 300 rows approx. What about the prefetched rows? How many rows per node are you fetching?

Hi!

I'm not sure exactly how many rows each prefetch is bringing back but I have run each query above individually direct in SQL Server and the first one;

Query: SELECT [dbo].[OrderDetail].[OrderDetailId], [dbo].[OrderDetail].[ShortOrderDetailId], [dbo].[OrderDetail].[OrderId], [dbo].[OrderDetail].[DocumentId], [dbo].[OrderDetail].[OrderDetailStatusId], [dbo].[OrderDetail].[JobticketId], [dbo].[OrderDetail].[Jdf], [dbo].[OrderDetail].[Price], [dbo].[OrderDetail].[ProductXml], [dbo].[OrderDetail].[PrintstationId], [dbo].[OrderDetail].[PaperCopies], [dbo].[OrderDetail].[ProductID] AS [ProductId], [dbo].[OrderDetail].[Cancellled] FROM ((((( [dbo].[Order] INNER JOIN [dbo].[OrderDetail] ON [dbo].[Order].[OrderId]=[dbo].[OrderDetail].[OrderId]) INNER JOIN [dbo].[User] ON [dbo].[User].[UserId]=[dbo].[Order].[UserId]) INNER JOIN [dbo].[Product] ON [dbo].[Product].[ProductId]=[dbo].[OrderDetail].[ProductID]) INNER JOIN [dbo].[OrderDetailStatus] ON [dbo].[OrderDetailStatus].[OrderDetailStatusId]=[dbo].[OrderDetail].[OrderDetailStatusId]) LEFT JOIN [dbo].[Document] ON [dbo].[Document].[DocumentId]=[dbo].[OrderDetail].[DocumentId]) WHERE ( ( [dbo].[Order].[PortalId] = @PortalId1 AND [dbo].[OrderDetailStatus].[ShowPrintStation] = @ShowPrintStation2 AND [dbo].[Order].[PendingConfirmation] = @PendingConfirmation3)) Parameter: @PortalId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2dd8645a-b0ff-4e95-ac13-eae2a26dd2d4. Parameter: @ShowPrintStation2 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False. Parameter: @PendingConfirmation3 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.

Brang back 359 rows and took 1min 11 secs to run

The second

SELECT [dbo].[OrderDetail].[DocumentId] FROM ((((( [dbo].[Order] INNER JOIN [dbo].[OrderDetail] ON [dbo].[Order].[OrderId]=[dbo].[OrderDetail].[OrderId]) INNER JOIN [dbo].[User] ON [dbo].[User].[UserId]=[dbo].[Order].[UserId]) INNER JOIN [dbo].[Product] ON [dbo].[Product].[ProductId]=[dbo].[OrderDetail].[ProductID]) INNER JOIN [dbo].[OrderDetailStatus] ON [dbo].[OrderDetailStatus].[OrderDetailStatusId]=[dbo].[OrderDetail].[OrderDetailStatusId]) LEFT JOIN [dbo].[Document] ON [dbo].[Document].[DocumentId]=[dbo].[OrderDetail].[DocumentId]) WHERE ( ( ( [dbo].[Order].[PortalId] = @PortalId1 AND [dbo].[OrderDetailStatus].[ShowPrintStation] = @ShowPrintStation2 AND [dbo].[Order].[PendingConfirmation] = @PendingConfirmation3))) Parameter: @PortalId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2dd8645a-b0ff-4e95-ac13-eae2a26dd2d4. Parameter: @ShowPrintStation2 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False. Parameter: @PendingConfirmation3 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.

Also 359 rows and took less than a second to run

The third query

Query: SELECT [dbo].[Document].[DocumentId], [dbo].[Document].[LibraryId], [dbo].[Document].[FileName], [dbo].[Document].[Description], [dbo].[Document].[OriginalExtension], [dbo].[Document].[FinalExtension], [dbo].[Document].[StatusId], [dbo].[Document].[CreatedDate], [dbo].[Document].[UpdateDate], [dbo].[Document].[MissingFonts], [dbo].[Document].[NumberOfPages], [dbo].[Document].[FileTypeId], [dbo].[Document].[LastOrderedDate], [dbo].[Document].[FileSize], [dbo].[Document].[ErrorMessage], [dbo].[Document].[UploadedBy], [dbo].[Document].[ProductXml], [dbo].[Document].[ProductId] FROM [dbo].[Document] WHERE ( [dbo].[Document].[DocumentId] IN (SELECT [dbo].[OrderDetail].[DocumentId] FROM ((((( [dbo].[Order] INNER JOIN [dbo].[OrderDetail] ON [dbo].[Order].[OrderId]=[dbo].[OrderDetail].[OrderId]) INNER JOIN [dbo].[User] ON [dbo].[User].[UserId]=[dbo].[Order].[UserId]) INNER JOIN [dbo].[Product] ON [dbo].[Product].[ProductId]=[dbo].[OrderDetail].[ProductID]) INNER JOIN [dbo].[OrderDetailStatus] ON [dbo].[OrderDetailStatus].[OrderDetailStatusId]=[dbo].[OrderDetail].[OrderDetailStatusId]) LEFT JOIN [dbo].[Document] ON [dbo].[Document].[DocumentId]=[dbo].[OrderDetail].[DocumentId]) WHERE ( ( ( [dbo].[Order].[PortalId] = @PortalId1 AND [dbo].[OrderDetailStatus].[ShowPrintStation] = @ShowPrintStation2 AND [dbo].[Order].[PendingConfirmation] = @PendingConfirmation3))))) Parameter: @PortalId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2dd8645a-b0ff-4e95-ac13-eae2a26dd2d4. Parameter: @ShowPrintStation2 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False. Parameter: @PendingConfirmation3 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.

rerturns 346 rows and took 56 seconds

The 4th again took less than a second and brang back 359 rows

Query: SELECT [dbo].[OrderDetail].[OrderId] FROM ((((( [dbo].[Order] INNER JOIN [dbo].[OrderDetail] ON [dbo].[Order].[OrderId]=[dbo].[OrderDetail].[OrderId]) INNER JOIN [dbo].[User] ON [dbo].[User].[UserId]=[dbo].[Order].[UserId]) INNER JOIN [dbo].[Product] ON [dbo].[Product].[ProductId]=[dbo].[OrderDetail].[ProductID]) INNER JOIN [dbo].[OrderDetailStatus] ON [dbo].[OrderDetailStatus].[OrderDetailStatusId]=[dbo].[OrderDetail].[OrderDetailStatusId]) LEFT JOIN [dbo].[Document] ON [dbo].[Document].[DocumentId]=[dbo].[OrderDetail].[DocumentId]) WHERE ( ( ( [dbo].[Order].[PortalId] = @PortalId1 AND [dbo].[OrderDetailStatus].[ShowPrintStation] = @ShowPrintStation2 AND [dbo].[Order].[PendingConfirmation] = @PendingConfirmation3))) Parameter: @PortalId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2dd8645a-b0ff-4e95-ac13-eae2a26dd2d4. Parameter: @ShowPrintStation2 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False. Parameter: @PendingConfirmation3 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.]

The 5th less than a second and 357 rows

Query: SELECT [dbo].[Order].[OrderId], [dbo].[Order].[OrderDate], [dbo].[Order].[ShortOrderId], [dbo].[Order].[UserId], [dbo].[Order].[AccountCode], [dbo].[Order].[ApprovalRequired], [dbo].[Order].[Approved], [dbo].[Order].[ApprovedBy], [dbo].[Order].[ApprovedDate], [dbo].[Order].[PortalId], [dbo].[Order].[AwaitingQuote], [dbo].[Order].[Price], [dbo].[Order].[OrderComplete], [dbo].[Order].[CompletedDate], [dbo].[Order].[PendingConfirmation] FROM [dbo].[Order] WHERE ( [dbo].[Order].[OrderId] IN (SELECT [dbo].[OrderDetail].[OrderId] FROM ((((( [dbo].[Order] INNER JOIN [dbo].[OrderDetail] ON [dbo].[Order].[OrderId]=[dbo].[OrderDetail].[OrderId]) INNER JOIN [dbo].[User] ON [dbo].[User].[UserId]=[dbo].[Order].[UserId]) INNER JOIN [dbo].[Product] ON [dbo].[Product].[ProductId]=[dbo].[OrderDetail].[ProductID]) INNER JOIN [dbo].[OrderDetailStatus] ON [dbo].[OrderDetailStatus].[OrderDetailStatusId]=[dbo].[OrderDetail].[OrderDetailStatusId]) LEFT JOIN [dbo].[Document] ON [dbo].[Document].[DocumentId]=[dbo].[OrderDetail].[DocumentId]) WHERE ( ( ( [dbo].[Order].[PortalId] = @PortalId1 AND [dbo].[OrderDetailStatus].[ShowPrintStation] = @ShowPrintStation2 AND [dbo].[Order].[PendingConfirmation] = @PendingConfirmation3))))) Parameter: @PortalId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2dd8645a-b0ff-4e95-ac13-eae2a26dd2d4. Parameter: @ShowPrintStation2 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False. Parameter: @PendingConfirmation3 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: False.

the 6th 16 rows and less than a second

Query: SELECT [dbo].[User].[UserId], [dbo].[User].[ApplicationName], [dbo].[User].[UserName], [dbo].[User].[DeliveryXml], [dbo].[User].[DeliveryTemplateId] FROM [dbo].[User] WHERE ( [dbo].[User].[UserId] IN (@UserId1, @UserId2, @UserId3, @UserId4, @UserId5, @UserId6, @UserId7, @UserId8, @UserId9, @UserId10, @UserId11, @UserId12, @UserId13, @UserId14, @UserId15, @UserId16)) Parameter: @UserId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2c6f48a0-f123-4efa-8863-6b89de1d6051. Parameter: @UserId2 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 489654f7-7562-468a-857a-43690470ee53. Parameter: @UserId3 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: e27c85be-acfb-4017-ae39-34ff76246905. Parameter: @UserId4 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: fd891d6a-e596-497e-a3f2-ad4f0365d3ce. Parameter: @UserId5 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 6c85ce7f-cbf2-4500-a1be-b3bb021a5cda. Parameter: @UserId6 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: a8159fbb-e07b-4ad7-8cf2-46671bd182c2. Parameter: @UserId7 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3c0085a2-0863-411c-ae6a-61d49b13f7dd. Parameter: @UserId8 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 9076130c-d96e-4382-a834-3febbf7712b4. Parameter: @UserId9 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 7667dfec-b2e1-4217-977b-6dc7325fc05f. Parameter: @UserId10 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 60ee1cb4-5eb7-4f38-8833-54da4e09a60c. Parameter: @UserId11 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 95f9d5e7-aebb-42ca-bb88-15fd661b4012. Parameter: @UserId12 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0af3c501-cba3-4754-b114-bf0be25f4bcf. Parameter: @UserId13 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 04dd43de-ebef-438e-9296-526fc9655e20. Parameter: @UserId14 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: d95f26b0-4574-4982-a903-bf89b6de017b. Parameter: @UserId15 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 42571b74-2e5e-4909-8625-99c11c15de9c. Parameter: @UserId16 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 412a9ee8-6211-408d-923b-b8bc4bed2177.

the 7th 1 row and less than a second

Query: SELECT [dbo].[OrderDetailStatus].[OrderDetailStatusId], [dbo].[OrderDetailStatus].[PortalId], [dbo].[OrderDetailStatus].[StatusName], [dbo].[OrderDetailStatus].[ShowUser], [dbo].[OrderDetailStatus].[EndUserDisplay], [dbo].[OrderDetailStatus].[EmailUser], [dbo].[OrderDetailStatus].[AllowDelete], [dbo].[OrderDetailStatus].[ShowPrintStation], [dbo].[OrderDetailStatus].[InitialStatus], [dbo].[OrderDetailStatus].[AwaitingPaperOriginals], [dbo].[OrderDetailStatus].[QueryOutstanding], [dbo].[OrderDetailStatus].[InProduction], [dbo].[OrderDetailStatus].[Printed], [dbo].[OrderDetailStatus].[Deleted], [dbo].[OrderDetailStatus].[Archived], [dbo].[OrderDetailStatus].[Complete], [dbo].[OrderDetailStatus].[Downloaded], [dbo].[OrderDetailStatus].[SystemStatus], [dbo].[OrderDetailStatus].[StatusDeleted], [dbo].[OrderDetailStatus].[EmailContent], [dbo].[OrderDetailStatus].[EmailSubject] FROM [dbo].[OrderDetailStatus] WHERE ( [dbo].[OrderDetailStatus].[OrderDetailStatusId] = @OrderDetailStatusId1) Parameter: @OrderDetailStatusId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 6bb642bc-1123-4ae6-9b38-36a63dde7db1.

The last one 8 rows and took less than a second

SELECT [dbo].[Product].[ProductId], [dbo].[Product].[Image], [dbo].[Product].[Name], [dbo].[Product].[Description], [dbo].[Product].[ProductXml], [dbo].[Product].[PortalId], [dbo].[Product].[Deleted], [dbo].[Product].[TemplateProduct] FROM [dbo].[Product] WHERE ( [dbo].[Product].[ProductId] IN (@ProductId1, @ProductId2, @ProductId3, @ProductId4, @ProductId5, @ProductId6, @ProductId7, @ProductId) Parameter: @ProductId1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0cc40a71-91d6-4b85-b224-9a46f1f42ad1. Parameter: @ProductId2 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: eca18dc9-c331-4dc9-a4a5-a1cde8938079. Parameter: @ProductId3 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 6971c7be-7c74-4ff2-9db2-670cc008efa6. Parameter: @ProductId4 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 3fc5937f-1ac8-4f5a-9be2-29d775a74894. Parameter: @ProductId5 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 28945c27-3962-4b26-a10c-00beed39f233. Parameter: @ProductId6 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2a613b68-5e8a-4368-b9b5-eb24955bf17c. Parameter: @ProductId7 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1b7c7698-aeca-41af-85ff-f583b27ffc67. Parameter: @ProductId8 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 8ce657aa-9b2d-45da-9df8-5b2595f527d8.

Does this give enough information?

Thanks

The first query seems to be the problem. If that orderdetails table contains (n)text/image/varchar(max)/xml/varbinary(max) fields, you should exclude them using excludefields feature and see if it helps. the query suggests there is at least a 'ProductXml' field, which might contain a lot of data. (i.e., if the main query already gives the memory overhaul, you have to look into excluding fields in the query).

Also you should run the query without the prefetch path and see how big memory consumption is.

Hi Walaa

Thanks for the information. I have excluded all the xml columns that I can and it's sped it up hugely, and the memory usuage is virtually gone!

Definitely something to keep in mind for next time.. I really didn't expect the xml columns to cause so much hassle.

Thanks Bex

BexMed wrote:

Hi Walaa

Thanks for the information. I have excluded all the xml columns that I can and it's sped it up hugely, and the memory usuage is virtually gone!

Definitely something to keep in mind for next time.. I really didn't expect the xml columns to cause so much hassle.

Thanks Bex

It's often not something you'd consider a problem, because 'xml' is seen as a datatype for small pieces of data (i.e. not MB's of data), but if you for example save word-documents as xml or other huge XML documents inside the DB, it will immediately run into big problems, because all that data is pulled from the DB. Sqlserver allows 2GB of XML per field per row, so potentially it can become a huge problem . (same goes for image/ntext/varchar(max)/varbinary(max) fields obviously).