|
Group: Forum Members
Last Active: Mon 6 Dec 2021
Posts: 235,
Visits: 750
|
I upgraded a client to the latest version of Kartris last week and started to get problems with Admin/_Default.aspx taking ages to load (the admin page). I have tracked this down to a long execution time for queries made against vKartrisProductsVersions. In this case the calls were being made from [_spKartrisDB_GetTaskList].
It's not an issue with the queries, they themselves are fine. What I have found is that if you request all columns from the view it returns very quickly, if you request just V_ID then it hangs up. You also get a delay when trying a Count(*). The problem is the execution plan that SQL Server 2008R2 was producing. I have resolved this with appending OPTION (FORCE ORDER) to the two calls to vKartrisProductsVersions from inside [_spKartrisDB_GetTaskList].
Hopefully this helps anyone else who has the same problem.
We are always willing to help out the community or pitch in to help you fix a problem. However, if you want a complete solution made such as a code module or new feature added you have two options. Either
1) Reach out to the Kartris internal development team at http://www.kartris.com/Contact.aspx.
2) Contact one of the Kartris approved partners at http://www.kartris.com/t-Worldwide-Developers.aspx.
Have fun and good luck coding.
|
|
Group: Administrators
Last Active: Fri 15 Sep 2023
Posts: 806,
Visits: 2,737
|
This is interesting - we did see slow load times on that page because of the stats and have a config setting to turn those off for busy sites. Is it possible you can post the whole code of the modified sproc just to be sure we get it exactly right?
--
If my post solves your issue, can you 'Mark as Answer' so it's easier for other users to find in future.
If you would like to be informed of new features, new releases, developments and occasional special bonuses, please sign up to our mailing list: http://bit.ly/19sKMZb
|
|
Group: Forum Members
Last Active: Mon 6 Dec 2021
Posts: 235,
Visits: 750
|
I have also attached it as a zip.
You will see that I have just added OPTION (FORCE ORDER) to two lines. this forces the execution plan to follow the same order that the view is written in.
/****** Object: StoredProcedure [dbo].[_spKartrisDB_GetTaskList] Script Date: 19/05/2016 16:27:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[_spKartrisDB_GetTaskList]
(
@NoOrdersToInvoice as int OUTPUT,
@NoOrdersNeedPayment as int OUTPUT,
@NoOrdersToDispatch as int OUTPUT,
@NoStockWarnings as int OUTPUT,
@NoOutOfStock as int OUTPUT,
@NoReviewsWaiting as int OUTPUT,
@NoAffiliatesWaiting as int OUTPUT,
@NoCustomersWaitingRefunds as int OUTPUT,
@NoCustomersInArrears as int OUTPUT
)
AS
BEGIN
SELECT @NoOrdersToInvoice = Count(O_ID) FROM dbo.tblKartrisOrders WHERE O_Invoiced = 'False' AND O_Paid = 'False' AND O_Sent = 'True' AND O_Cancelled = 'False';
SELECT @NoOrdersNeedPayment = Count(O_ID) FROM dbo.tblKartrisOrders WHERE O_Paid = 'False' AND O_Invoiced = 'True' AND O_Sent = 'True' AND O_Cancelled = 'False';
SELECT @NoOrdersToDispatch = Count(O_ID) FROM dbo.tblKartrisOrders WHERE O_Sent = 'True' AND O_Paid = 'True' AND O_Shipped = 'False' AND O_Cancelled = 'False';
SELECT @NoStockWarnings = Count(DISTINCT V_ID) FROM dbo.vKartrisProductsVersions WHERE V_QuantityWarnLevel >= V_Quantity AND V_QuantityWarnLevel <> 0 OPTION (FORCE ORDER);
SELECT @NoOutOfStock = Count(DISTINCT V_ID) FROM dbo.vKartrisProductsVersions WHERE V_Quantity <= 0 AND V_QuantityWarnLevel <> 0 OPTION (FORCE ORDER);
SELECT @NoReviewsWaiting = Count(REV_ID) FROM dbo.tblKartrisReviews WHERE REV_Live = 'a';
SELECT @NoAffiliatesWaiting = Count(U_ID) FROM dbo.tblKartrisUsers WHERE U_IsAffiliate = 'True' AND U_AffiliateCommission = 0;
SELECT @NoCustomersWaitingRefunds = Count(U_ID) FROM dbo.tblKartrisUsers WHERE U_CustomerBalance > 0;
SELECT @NoCustomersInArrears = Count(U_ID) FROM dbo.tblKartrisUsers WHERE U_CustomerBalance < 0;
END
GO
We are always willing to help out the community or pitch in to help you fix a problem. However, if you want a complete solution made such as a code module or new feature added you have two options. Either
1) Reach out to the Kartris internal development team at http://www.kartris.com/Contact.aspx.
2) Contact one of the Kartris approved partners at http://www.kartris.com/t-Worldwide-Developers.aspx.
Have fun and good luck coding.
SQLQuery1.zip
(1 view,
728 bytes)
|