question

Hi All I have project that basically requires me to “find the best match” solution. Any advice/guidance on what the best solution would be (in terms of performance) will be appreciated (CLR/SET Based /Cursor (arrrh) etc). Clients have a set of attributes and each attribute has a “value”, this value can be 0 (not essential), 1 (preferred) and 2 (mandatory). Each supplier has the same set of attributes, but their value is a simple true/false property. If ClientB has all attribute set to “2”, then only suppliers that have and value of “1” should be select, if more than one supplier is returned then a sort on `supplier.rate` would be required. With the data supplied it should return SupplierA and SupplierC. As a result of this, SupplierA and B have now been made excluded from the next search for ClientA. As you may see this client only has preferred attribute therefore suppliers with “1” and “0” can be returned… As this is preferable then ideally `supplierattribute.value` should be “1” if supplier match. I have attached the schema and some sample data to match the scenario above (I think ;-)) Thanks in advance…. Sorry it is so long... SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Client]( [clientId] [int] IDENTITY(1,1) NOT NULL, [code] [nchar](10) NOT NULL, CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED ( [clientId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[Attribute] Script Date: 10/19/2010 10:41:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Attribute]( [attributeId] [int] IDENTITY(1,1) NOT NULL, [Ref] [nchar](10) NULL, [Desc] [nchar](50) NULL, [Type] [varchar](2) NULL, CONSTRAINT [PK_Attribute] PRIMARY KEY CLUSTERED ( [attributeId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[Supplier] Script Date: 10/19/2010 10:41:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Supplier]( [supplierId] [int] IDENTITY(1,1) NOT NULL, [code] [nchar](10) NOT NULL, [rate] [decimal](18, 2) NULL, CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED ( [supplierId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[SupplierAttribute] Script Date: 10/19/2010 10:41:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[SupplierAttribute]( [supplierattributeId] [int] IDENTITY(1,1) NOT NULL, [attributeId] [int] NOT NULL, [supplierId] [int] NOT NULL, [value] [tinyint] NULL, CONSTRAINT [PK_SupplierAttribute] PRIMARY KEY CLUSTERED ( [supplierattributeId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[ClientAttribute] Script Date: 10/19/2010 10:41:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[ClientAttribute]( [clientattributeId] [int] IDENTITY(1,1) NOT NULL, [attributeId] [int] NOT NULL, [clientId] [int] NOT NULL, [value] [tinyint] NULL, CONSTRAINT [PK_ClientAttribute] PRIMARY KEY CLUSTERED ( [clientattributeId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: ForeignKey [FK_ClientAttribute_Attribute] Script Date: 10/19/2010 10:41:06 ******/ ALTER TABLE [dbo].[ClientAttribute] WITH CHECK ADD CONSTRAINT [FK_ClientAttribute_Attribute] FOREIGN KEY([attributeId]) REFERENCES [dbo].[Attribute] ([attributeId]) GO ALTER TABLE [dbo].[ClientAttribute] CHECK CONSTRAINT [FK_ClientAttribute_Attribute] GO /****** Object: ForeignKey [FK_ClientAttribute_Client] Script Date: 10/19/2010 10:41:06 ******/ ALTER TABLE [dbo].[ClientAttribute] WITH CHECK ADD CONSTRAINT [FK_ClientAttribute_Client] FOREIGN KEY([clientId]) REFERENCES [dbo].[Client] ([clientId]) GO ALTER TABLE [dbo].[ClientAttribute] CHECK CONSTRAINT [FK_ClientAttribute_Client] GO /****** Object: ForeignKey [FK_SupplierAttribute_Attribute] Script Date: 10/19/2010 10:41:06 ******/ ALTER TABLE [dbo].[SupplierAttribute] WITH CHECK ADD CONSTRAINT [FK_SupplierAttribute_Attribute] FOREIGN KEY([attributeId]) REFERENCES [dbo].[Attribute] ([attributeId]) GO ALTER TABLE [dbo].[SupplierAttribute] CHECK CONSTRAINT [FK_SupplierAttribute_Attribute] GO /****** Object: ForeignKey [FK_SupplierAttribute_Supplier] Script Date: 10/19/2010 10:41:06 ******/ ALTER TABLE [dbo].[SupplierAttribute] WITH CHECK ADD CONSTRAINT [FK_SupplierAttribute_Supplier] FOREIGN KEY([supplierId]) REFERENCES [dbo].[Supplier] ([supplierId]) GO ALTER TABLE [dbo].[SupplierAttribute] CHECK CONSTRAINT [FK_SupplierAttribute_Supplier] GO --Clear down DELETE FROM SupplierAttribute DELETE FROM ClientAttribute DELETE FROM Supplier DELETE FROM Client DELETE FROM Attribute --insert default attributes INSERT INTO Attribute(Ref, [Desc], [Type] ) VALUES ('CheckC1', 'Check 1', 'C') INSERT INTO Attribute(Ref, [Desc], [Type] ) VALUES ('CheckC2', 'Check 2', 'C') INSERT INTO Attribute(Ref, [Desc], [Type] ) VALUES ('CheckC3', 'Check 3', 'C') INSERT INTO Attribute(Ref, [Desc], [Type] ) VALUES ('CheckC4', 'Check 4', 'C') --insert client data INSERT INTO Client (code) VALUES ('Client A') --insert client data INSERT INTO Client (code) VALUES ('Client B') --insert client A attributes INSERT INTO ClientAttribute (attributeId, clientId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC1'), (SELECT clientId FROM Client WHERE code = 'Client A'), 1 ); INSERT INTO ClientAttribute (attributeId, clientId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC2'), (SELECT clientId FROM Client WHERE code = 'Client A'), 1 ); INSERT INTO ClientAttribute (attributeId, clientId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC3'), (SELECT clientId FROM Client WHERE code = 'Client A'), 1 ); INSERT INTO ClientAttribute (attributeId, clientId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC4'), (SELECT clientId FROM Client WHERE code = 'Client A'), 1 ); --insert client B attributes INSERT INTO ClientAttribute (attributeId, clientId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC1'), (SELECT clientId FROM Client WHERE code = 'Client B'), 2 ); INSERT INTO ClientAttribute (attributeId, clientId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC2'), (SELECT clientId FROM Client WHERE code = 'Client B'), 2 ); INSERT INTO ClientAttribute (attributeId, clientId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC3'), (SELECT clientId FROM Client WHERE code = 'Client B'), 2 ); INSERT INTO ClientAttribute (attributeId, clientId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC4'), (SELECT clientId FROM Client WHERE code = 'Client b'), 2 ); --insert supplier data INSERT INTO Supplier (code, rate) VALUES ('Supplier A', 0.24) INSERT INTO Supplier (code, rate) VALUES ('Supplier B', 0.30) INSERT INTO Supplier (code, rate) VALUES ('Supplier C', 0.28) INSERT INTO Supplier (code, rate) VALUES ('Supplier D', 0.24) INSERT INTO Supplier (code, rate) VALUES ('Supplier E', 0.24) --Build SupplierA INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC1'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier A'), 1 ); INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC2'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier A'), 1 ); INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC3'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier A'), 1 ); INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC4'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier A'), 1 ); --Build SupplierB INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC1'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier B'), 1 ); INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC2'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier B'), 0 ); INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC3'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier B'), 0 ); INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC4'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier B'), 0 ); --Build SupplierC INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC1'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier C'), 1 ); INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC2'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier C'), 1 ); INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC3'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier C'), 1 ); INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC4'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier C'), 1 ); --Build SupplierD INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC1'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier D'), 0 ); INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC2'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier D'), 1 ); INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC3'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier D'), 1 ); INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC4'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier D'), 0 ); --Build SupplierE INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC1'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier E'), 1 ); INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC2'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier E'), 0 ); INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC3'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier E'), 1 ); INSERT INTO SupplierAttribute(attributeId, supplierId, [value]) VALUES ((SELECT attributeId FROM Attribute WHERE Ref = 'CheckC4'), (SELECT SupplierId FROM Supplier WHERE code = 'Supplier E'), 0 );