Query performance - where to start tuning the server??
-
Hi All,
So I have a query that is part of a web application... its purpose is to perform a search for products. The performance for this query is not great, taking up to 15-20 seconds to return results. Query time fluctuates at different times of day... but is never very good.
I want to help with making this query perform better, but I am no DBA... I have reached out to the dev so we can go over this together, but I need to be careful and not just put this off on the devs to change their code. I want to make sure that the server / database is tuned appropriately as well.
I have a copy of the query, and am looking at the execution plan, live query statistics, etc. I have no idea what to take from it... and from a sense, where to begin. Overall, the SQL Server's performance is ok... certainly no disk or CPU, memory pressure, etc.
If I posted the execution plan XML or something here, any up for providing some advice?
-
The picture is unreadable, even zoomed. It will be easier to help you if you give us the execution plan as a .sqlplan file (right click on the diagram in Management Studio and click "save execution plan as..." There is additional information in the plan file that you can't see in a picture as well.
Also beneficial would be to know the indexes that already exist on these tables.
-
Thanks for the reply. Sorry - I didn't intend for the image to be very reviewable... It was just a section to show the general scope / size of the query 🙂
I have attached a sqlplan file. I don't know at the moment about what indexes exist... I need to gather that info;
Thanks!
-
Offhand I see there are 3 expensive sorts on the WebImage table, all with the same WHERE clause and ORDER BY, would be much more efficient to just hit the table once in the FROM clause, e.g. something like:
CROSS APPLY (SELECT TOP 1 WIImageURL, WILargeImageURL, WIPDFImageURL FROM [WebImage] WHERE wiitno = wci_itno ORDER BY wiseq) urlsI'm also noticing a large number of SELECT clause subqueries hitting the same couple of tables IW, YW, with just slightly different criteria. I'm thinking those could be written better as a subquery in the FROM clause also. At the very least, it doesn't make sense to run each subquery twice, once to test EXISTS, then again to retrieve a value, use ISNULL instead.
I notice what looks like a large table scan on WWW_CatalogRetail is happening, check what indexes are on that table and see if there is one that matches the join columns.
After correcting those 3 code issues, then you can see if there is other things to tune from the server perspective.
-
Thanks so much for the recommendations. I see the following indexes on WWW_CatalogRetail;
ALTER TABLE [dbo].[WWW_CatalogRetail] ADD CONSTRAINT [PK_WWW_CatalogRetail] PRIMARY KEY CLUSTERED
(
[WCRType] ASC,
[WCRCatalog] ASC,
[WCRCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [Type,Catalog] ON [dbo].[WWW_CatalogRetail]
(
[WCRType] ASC,
[WCRCatalog] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [WCRDesc] ON [dbo].[WWW_CatalogRetail]
(
[WCRDescription] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
I will review with the devs and see where it goes...
Thanks! -
I would start by changing all those SELECT TOP 1. You're reading the tables multiple times when you can read them just once.
Your WHERE clause is not SARGable so you're reading the whole table (or index) to look for the appropriate values.
This is an example based on your query:
SELECT min(WCI_ITNO) AS 'NASKU'
,wi.WIImageURL AS 'IMAGE'
,wi.WILargeImageURL AS 'WILargeImageURL'
,min(WCI_DESC) AS 'ITEM_DESC'
,min(WCI_IUM) AS 'UNIT'
,min(WCI_XFNO) AS 'ITEM_NUMBER'
,min(WCI_ITYP) AS 'WCI_ITYP'
,wi.WIPDFImageURL AS 'WIPDFImageURL'
,min(WCI_DSC2) AS 'CASE'
,min(WCIBrand) AS 'BRAND'
FROM WWW_CatalogItem
INNER JOIN IG2011 ON IGITNO = WCI_ITNO
AND IGCTYP = WCIType
AND IGCODE = WCICatalog
LEFT JOIN [WWW_CatalogRetail] ON WCRType = WCIType
AND [WCRCatalog] = WCICatalog
AND [WCRCode] = WCIRetail
AND WCIRetail <> 'ZZZ'
/*LEFT JOIN [WebImage] ON wiitno = wci_itno*/--Apparently this is not used.
LEFT JOIN [NA ASI]..t_products Promo ON WciProdId = Promo.ProdId
INNER JOIN TS2011 ON IGCTID = TSCTID
AND TSCTYP = IGCTYP
AND TSCODE = IGCODE
AND TSCNT != 0
LEFT JOIN ItemInformation ON IIITNO = WCI_ITNO
AND IIRID = 'CAT'
AND IITDSC <> ''
AND IITDSC IS NOT NULL
OUTER APPLY(SELECT TOP 1 WIImageURL, WILargeImageURL, WIPDFImageURL
FROM [WebImage]
WHERE wiitno = wci_itno
ORDER BY wiseq) wi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply