Hello,
Our SolidCP has becore really slow. There is a query:
SELECT
Q.QuotaID,
Q.GroupID,
Q.QuotaName,
Q.QuotaDescription,
Q.QuotaTypeID,
QuotaValue = CASE WHEN Q.PerOrganization = 1 AND dbo.GetPackageAllocatedQuota(@PackageID, Q.QuotaID) <> -1 THEN
dbo.GetPackageAllocatedQuota(@PackageID, Q.QuotaID) * @OrgsCount
ELSE
dbo.GetPackageAllocatedQuota(@PackageID, Q.QuotaID)
END,
QuotaValuePerOrganization = dbo.GetPackageAllocatedQuota(@PackageID, Q.QuotaID),
dbo.GetPackageAllocatedQuota(@ParentPackageID, Q.QuotaID) AS ParentQuotaValue,
ISNULL(dbo.CalculateQuotaUsage(@PackageID, Q.QuotaID), 0) AS QuotaUsedValue,
Q.PerOrganization
FROM Quotas AS Q
WHERE Q.HideQuota IS NULL OR Q.HideQuota = 0
ORDER BY Q.QuotaOrde
that takes around 7 seconds to be executed and since it used quote often it's getting really slow. The database server is using SSDs and we have run the profiler but it doesn't give us any more suggestions for optimization. I suspect dbo.GetPackageAllocatedQuota is causing the slowness, but I'm not sure hot to optimize it.
Thanks,
Chris
Hi Chris,
Not looked at this very much, but going by the query analyser, adding these indexes could improve the performance.
CREATE NONCLUSTERED INDEX IDX_Domains_IsSubDomain_IsPreviewDomain_IsDomainPointer_PackageID
ON [dbo].[Domains] ([IsSubDomain],[IsPreviewDomain],[IsDomainPointer])
INCLUDE ([PackageID])
CREATE NONCLUSTERED INDEX IDX_Domains_PackageID_IsDomainPointer
ON [dbo].[Domains] ([PackageID],[IsDomainPointer])
CREATE NONCLUSTERED INDEX IDX_ServiceItemProperties_ServiceItemProperties_PropertyName_PropertyValue
ON [dbo].[ServiceItemProperties] ([PropertyName])
INCLUDE ([ItemID],[PropertyValue])
CREATE NONCLUSTERED INDEX IDX_ServiceItems_ItemTypeID_PackageID
ON [dbo].[ServiceItems] ([ItemTypeID])
INCLUDE ([PackageID])
So you could try adding those and see if that helps.
Cheers,
Sean
Hello,
Thanks for the suggestion. I definitely helped.
Thanks,
Chris
