Slow DB query
 
Notifications
Clear all

Slow DB query

3 Posts
2 Users
1 Likes
1,686 Views
Posts: 9
Topic starter
(@ov33rs33r)
Active Member
Joined: 8 years ago

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

2 Replies
Posts: 1
 Sean
(@nah-sean)
New Member
Joined: 7 years ago

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

Reply
Posts: 9
Topic starter
(@ov33rs33r)
Active Member
Joined: 8 years ago

Hello,

Thanks for the suggestion. I definitely helped.

Thanks,

Chris

Reply
Share: