August 22, 2008 at 12:11 pm
I came across a long executing query today, the output gave about 100 rows by 3-4 seconds, which is unacceptable for a products query that will have about 1 000 000 products. So i used SSMS to get the query plan of that query, and tune the problem.
If anyone has ever seen a query that weird (the sum of the % of every operator add up and make about 200 000%...), well let me know!
Maybe it's a glitch, but I don't know why the output is so weird, well, let's say i'll start my optimization by attacking that 180 000% sort operator!
See the attached pictures, since I cannot attach the plan itself... =(
Cheers,
J-F
August 22, 2008 at 1:21 pm
jfbergeron (8/22/2008)
See the attached pictures, since I cannot attach the plan itself... =(
You can zip the .sqlplan file and attach the zip.
Any table variables involved? User-defined functions (scalar or multi-statement table)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 22, 2008 at 1:26 pm
Well here is the plan, and I will post the query too, just thought it was a really cool plan to debug... 😉
/* FULL UPD */
SELECT 'ExtractTagH',
'BatchNbrH',
'UpdateManual',
'UpdateDate',
'IsFull',
'ExtractTagD',
'BatchNbrD',
'TransactionCode',
'ProductID',
'VendorCode',
'MfrCode',
'Catalog',
'CatalogNew',
'VndPrdCode',
'VndPrdCodeNew',
'VndCatalog',
'VndCatalogNew',
'VndCategory',
'GICUPC',
'GICUPCNew',
'UPC',
'UPCNew',
'Zone',
'SPFCategoryID',
'DescEn',
'DescFr',
'PurUOM',
'DistrCost',
'List',
'Col1',
'Col2',
'Col3',
'CashDiscount',
'TaxFlag',
--'SellingUOM',
'UnitQty',
'CartonQty',
'CaseQty',
'PalletQty',
'MinToOrder',
'OrderIncrement',
'Weight',
'POA',
'ReplVendorCode',
'ReplMfrCode',
'ReplGICCatalog',
'ProductStatus',
'ProductStatusSource',
'ProductStatusEffDate',
'PricePercentage',
'MfrVendorID'
UNION ALL
SELECT 'PRD' AS ExtractTagH,
'1' AS BatchNbrH,
'0' AS UpdateManual,
CONVERT(NVARCHAR(500),GETDATE()) AS UpdateDate,
'1' AS IsFull,
NULL AS ExtractTagD,
NULL AS BatchNbrD,
NULL AS TransactionCode,
NULL AS ProductID,
NULL AS VendorCode,
NULL AS MFRCode,
NULL AS Catalog,
CONVERT(NVARCHAR(500),NULL) AS CatalogNew,
NULL AS VndPrdCode,
NULL AS VndPrdCodeNew,
NULL AS VndCatalog,
NULL AS VndCatalogNew,
NULL AS VndCategory,
NULL AS GICUPC,
NULL AS GICUPCNew,
NULL AS UPC,
NULL AS UPCNew,
NULL AS ZONE,
NULL AS SPFCategoryID,
NULL AS DescriptionEnglish,
NULL AS DescriptionFrench,
NULL AS PurUOM,
NULL AS DistrCost,
NULL AS List,
NULL AS Col1,
NULL AS Col2,
NULL AS Col3,
NULL AS CashDiscount,
NULL AS TaxFlag,
--NULL AS SellingUOM,
NULL AS UnitQty,
NULL AS CartonQty,
NULL AS CaseQty,
NULL AS PalletQty,
NULL AS MinToOrder,
NULL AS OrderIncrement,
NULL AS Weight,
NULL AS POA,
NULL AS ReplVendorCode,
NULL AS ReplMfrCode,
NULL AS ReplGICCatalog,
NULL AS ProductStatus,
NULL AS ProductStatusSource,
NULL AS ProductStatusEffDate,
NULL AS PricePercentage,
NULL AS MFRVendorID
UNION ALL
SELECT NULL AS Hdr1,
NULL AS HRD2,
NULL AS HRD3,
NULL AS HRD4,
NULL AS HRD5,
'PRD' AS ExtractTag,
'1' AS 'BatchNbrD',
p.TransactionCode,
CONVERT(NVARCHAR(500),p.ProductID),
p.VendorCode,
p.MfrCode,
p.SPFCatalog,
CONVERT(NVARCHAR(500),p.SPFCatalogNew),
p.VndPrdCode,
CONVERT(NVARCHAR(500),p.VndPrdCodeNew),
p.VndCatalog,
CONVERT(NVARCHAR(500),p.VndCatalogNew),
CONVERT(NVARCHAR(500),p.VndCategory),
p.SPFGICUPC,
CONVERT(NVARCHAR(500),p.SPFGICUPCNew),
p.SPFUPC,
CONVERT(NVARCHAR(500),p.SPFUPCNew),
p.ZONEID,
CONVERT(NVARCHAR(500),p.SPFCategoryID),
p.SPFDescEN,
p.SPFDescFR,
p.SPFPurUOM,
CONVERT(NVARCHAR(500),p.DistrCost),
CONVERT(NVARCHAR(500),p.ListPrice),
CONVERT(NVARCHAR(500),p.Col1),
CONVERT(NVARCHAR(500),p.Col2),
CONVERT(NVARCHAR(500),p.Col3),
CONVERT(NVARCHAR(500),p.SPFCashDisc),
p.SPFTaxFlag,
--p.SPFSellingUOM,
CONVERT(NVARCHAR(500),p.SPFUnitQty),
CONVERT(NVARCHAR(500),p.SPFCartonQty),
CONVERT(NVARCHAR(500),p.SPFCaseQty),
CONVERT(NVARCHAR(500),p.SPFPalletQty),
CONVERT(NVARCHAR(500),p.SPFMinOrder),
CONVERT(NVARCHAR(500),p.SPFOrderIncrement),
p.SPFWeight,
CONVERT(NVARCHAR(500),p.SPFPOA),
p.ReplVendorCode,
p.ReplMfrCode,
p.ReplGICCatalog,
p.SPFStatus,
p.SPFStatusSource,
CONVERT(NVARCHAR(500),p.SPFStatusEffDate),
CONVERT(NVARCHAR(500),p.PricePercentage),
Convert(nvarchar(50),MfrVendorID)
FROM vwGICSPFFullLoad p
WHERE p.DepartmentID = @DepartmentID
Cheers,
J-F
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply