many years ago I coded a stored procedure containing the following SQL (for a client's backend website) and it has always worked fine for them right up until a few days ago when it started to fail with a timeout message. When I run the SP directly in SSMS it just doesn't terminate, I left it running for 90 minutes and it neither timed out nor completed. In the past it would have run fine in just a few seconds.
During the query the CPU maxes out, but RAM usage stays reasonable.
We have moved to a faster server with more RAM and the latest version of SQPEXPRESS (10.50.4000.0) but this has not fixed the problem. (BTW i'm aware of the single-core limitation of SQLEXPRESS, but that's what we are having to use)
The database tables involved are pretty large, but have only been very slowly increasing in size over the years and the code has always worked pretty quickly right up until recently.
Unfortunately the project was so long ago that I have very little idea about what is going on in the code even though I created it myself!
My hunch is that the query involves an exponential increase in CPU cycles as the table sizes increase, and that we've now hit a critical point in size.
My knowledge of SQL Server is patchy, especially after several years of non-involvement. Would I be right in thinking that this can be tackled by adding indexes? If so how should I go about it?
Or perhaps the script needs to be recoded using a different approach?
Any help greatly appreciated!
select DISTINCT Pprin.product_id as printerID, dbo.deduceMFRfromSubCatName(subcat.category) as MFRdeduced, RTRIM(ISNULL(ECAT.category,'NOCAT')) + '///' + RTRIM(ISNULL(SubCat.category, 'NOSUBCAT')) + '///' AS catPath, ISNULL(PO.forceName,Pprin.model_name) as approvedPrinterName, max(M.dateCreated) as newestProductCreated, max(M.lastUpdated) as mostRecentProductEdit, max(M.lastIceCatFetch) as latestProductSheetFetch, isnull(PO.dateForced, '1-Dec-2012') as nameApprovalDate from xtra_icecat_printers1_rel_carts2 R inner join xtra_icecat_allproducts Pcart on R.prodID2 = Pcart.product_id inner join xtra_itemmaster I on dbo.dataSKU(I.SKUlite) = Pcart.SKUlite inner join xtra_SKUmaster M on dbo.dataSKU(M.SKUlite) = Pcart.SKUlite inner join xtra_icecat_allproducts Pprin on R.prodid1 = Pprin.product_id inner join xtra_icecat_productdata Dprin on R.prodid1 = Dprin.product_id left join Xtra_IceCat_Cats CA on CA.catID=Pprin.category_id left join Xtra_IceCat_PrinterNameOverride PO on R.prodid1 = PO.printerID left join category ECAT on ECAT.CatID=I.CatID left join subcategory Subcat on Subcat.SubcatID=I.subCatID where copyOfPrinterID=Dprin.product_id and isnull(PO.forceName,'OFF')<>'OFF' group by Pprin.product_id, forceName, ECAT.category, subcat.category, Pprin.model_name, PO.dateForced order by Pprin.product_id
dbo.dataSKU function (from comments):
ALTER FUNCTION [dbo].[dataSKU] (@SKUlite as varchar(25)) RETURNS varchar(25) AS BEGIN IF @SKUlite LIKE '%COMP' SET @SKUlite = LEFT(@SKUlite, LEN(@SKUlite)-4) RETURN @SKUlite END;