January 28, 2010 at 8:17 am
Hello,
I am having issues with a query, here it is:
USE MatchingDB
SELECT CS.MatchType, CP.SiteID, CP.SubSiteDisplayName, CP.CompetitorPartNumber,
CP.Manufacturer,CP.CategoryTree,MS.SourceID, MS.MatchDescription, MS.MatchCategory,
MS.MatchTrademark,MS.ExcludeKeywords,MS.WarningKeywords,MS.MatchCapacity1 as FuzzyMatchCapacity,
MT.categoryArea
FROM CatalogueMgmtDb.dbo.PMCompetitorProducts AS CP
INNER JOIN dbo.CompetitorMatchingSource AS MS
ON MS.SiteId = CP.SiteId and MS.CompetitorPartNumber = CP.CompetitorPartNumber
INNER JOIN dbo.CompetitorSites CS ON CP.SiteID = CS.SiteId
INNER JOIN dbo.CompetitorCategoryMatchingTypes MT on MT.categoryType = MS.MatchCategory
INNER JOIN dbo.CurrentCompetitorCategoryArea CA
ON MT.categoryArea = CA.CurrentCategoryArea
I we run the first part of the query (excluding the last join), it runs within a minute. Add the last join and the query NEVER completes. The table CurrentCompetitorCategoryArea has 1 record and 1 column with a value of 'Misc' (varchar(10)) The matching field in CompetitorCategoryMatchingTypes is of the same type(varchar(10)). If we replace the join with "WHERE MT.CategoryArea = 'Misc' , the query completes in under a minute.
The table structures are as follows:
CREATE TABLE [dbo].[PMCompetitorProducts(2010-01-28 13:08:25)](
[ID] [int] IDENTITY(1,1) NOT NULL,
[SiteID] [int] NOT NULL,
[SubSiteDisplayName] [varchar](100) NOT NULL,
[Category] [varchar](255) NOT NULL,
[CompetitorPartNumber] [varchar](150) NULL,
[ProductName] [varchar](255) NOT NULL,
[Manufacturer] [varchar](255) NOT NULL,
[Mfpn] [varchar](46) NULL,
[Price] [smallmoney] NULL,
[AvailString] [varchar](150) NULL,
[Stock] [int] NULL,
[UpdateDateTime] [smalldatetime] NOT NULL,
[ExtSpec] [varchar](max) NULL,
[OrigManufacturer] [varchar](255) NULL,
[CategoryTree] [varchar](2000) NOT NULL,
[ImageURL] [varchar](2084) NULL,
There is a synonym in place (PMCompetitorProducts), as this table is dropped and recreated dynamically each night.
CREATE TABLE [dbo].[CompetitorMatchingSource](
[SourceID] [int] IDENTITY(1,1) NOT NULL,
[SubSiteDisplayName] [varchar](100) NOT NULL,
[CompetitorPartNumber] [varchar](150) NOT NULL,
[MatchDescription] [varchar](1500) NOT NULL,
[MatchRange] [varchar](255) NOT NULL,
[MatchCategory] [varchar](255) NOT NULL,
[MatchColour] [varchar](20) NOT NULL,
[MatchModel] [varchar](255) NOT NULL,
[MatchCapacity1] [varchar](20) NOT NULL,
[MatchCapacity2] [varchar](20) NOT NULL,
[MatchCapacity3] [varchar](20) NOT NULL,
[MatchCapacity4] [varchar](20) NOT NULL,
[MatchCapacity5] [varchar](20) NOT NULL,
[MatchCapacity6] [varchar](20) NOT NULL,
[MatchTrademark] [varchar](100) NOT NULL,
[KnownModel] [bit] NULL,
[WarningKeywords] [varchar](1028) NOT NULL,
[ExcludeKeywords] [varchar](1028) NOT NULL,
CREATE TABLE [dbo].[CompetitorSites](
[SiteID] [int] IDENTITY(1,1) NOT NULL,
[SiteName] [varchar](100) NOT NULL,
[CategoryLevel] [int] NOT NULL,
[AlertHours] [int] NOT NULL,
[ConcurrencyRowVersion] [timestamp] NOT NULL,
[MatchType] [varchar](1) NULL,
[LastMatchDateTime] [datetime] NULL,
CONSTRAINT [PK_CompetitorSites] PRIMARY KEY CLUSTERED
CONSTRAINT [IX_CompetitorSites] UNIQUE NONCLUSTERED
There is a synonym in place for this table
CREATE TABLE [dbo].[CompetitorCategoryMatchingTypes](
[CategoryType] [varchar](255) NOT NULL,
[HardExcludeFlag] [bit] NULL,
[categoryArea] [varchar](10) NOT NULL,
[CapacityRegex1] [varchar](10) NULL,
[CapacityRegex2] [varchar](10) NULL,
[CapacityRegex3] [varchar](10) NULL,
[CapacityRegex4] [varchar](10) NULL,
[CapacityRegex5] [varchar](10) NULL,
[CapacityRegex6] [varchar](10) NULL,
[CapacityTollerance1] [float] NULL,
[CapacityTollerance2] [float] NULL,
[CapacityTollerance3] [float] NULL,
[CapacityTollerance4] [float] NULL,
[CapacityTollerance5] [float] NULL,
[CapacityTollerance6] [float] NULL,
[maxSuggestions] [int] NOT NULL,
CONSTRAINT [PK_CompetitorCategoryMatchingTypes_Category] PRIMARY KEY CLUSTERED
[CategoryType] ASC
CHECK ADD CONSTRAINT [FK_CompetitorCategoryMatchingTypes_CategoryAreaConfig] FOREIGN KEY([categoryArea])
REFERENCES [dbo].[CategoryAreaConfig] ([categoryArea])
CHECK CONSTRAINT [FK_CompetitorCategoryMatchingTypes_CategoryAreaConfig]
I apologise for the amount of information but this has been an issue now for longer than it should.
Let me know if you have any questions
Thanks
Andy
January 28, 2010 at 9:43 am
Couple of things to try.
Composite index on alias MT table.
Derived table
inner join ( tableMT inner join table CA) as MTCA on MT.categoryType = MS.MatchCategory
January 28, 2010 at 2:59 pm
create table #distCats
(
CurrentCategoryArea varchar(200)
)
insert into #distCats
select distinct CurrentCategoryArea
from CurrentCompetitorCategoryArea
--then later in your join rather than having
INNER JOIN dbo.CurrentCompetitorCategoryArea CA
ON MT.categoryArea = CA.CurrentCategoryArea
--change it to
INNER JOIN #distCats CA
ON MT.categoryArea = CA.CurrentCategoryArea
--see if that helps
January 29, 2010 at 5:20 am
Thanks guys, I will give theses a try:-D
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply