February 18, 2015 at 1:56 am
We Have 2 Tables :
1- Table Prod
2- Table Group
>>>>> every Prod May be on one or more Groups
We have a view that is base of a page with search on fields.
In this view we have one Record for every Prod.
and we had to collect all Group Id in string Format in a field. that user want search on a group , we show all products on that group.
then we have this function for create one field of groups for this view :
-----------------------------------------------------------------------------------
Create FUNCTION F1
(
@ProdId INT
)
RETURNS VARCHAR(50)
AS
BEGIN
-- Declare the return variable here
DECLARE @strList VARCHAR(50) =''
SELECT @strList = COALESCE(@strList + '|', '') + Convert(varchar(5) ,dbo.Group.id)
FROM Group
INNER JOIN GroupProduct ON Group.Id = GroupProd.GroupId
WHERE GroupProd.ProdId =@ProdId
Set @strList=@strList+'|'
RETURN @strList
END
-----------------------------------------------------------
Result Prod1 - ... - .... - |54|76|1| - .... > That means Prod1 is on 3 groups : 54 , 76 , 1
and we search in this format : where GFiled like '%|76|%'
-----------------------------------------------------------
Problems :
1- Create this Fields has big cost .
2- For search we dont use any index because '%%'
is this any idea instead this ?
Thank you
February 18, 2015 at 8:31 am
A suggestion would be not to have the concatenated groups on the view and build only those needed.
If you have your view with each group on a single row, you can make your queries SARGable.
An additional option would be to use FOR XML PATH('') to concatenate[/url] the groups without using a scalar function.
If you post DDL and sample data, I could show you how to do it.
February 21, 2015 at 2:32 am
This is script .
But , We produce a XML Field with all Group ID for a product.
But search in a XML filed is very Slow.
(such this format : ProdGroups.exist(''/main/TBL_..[@id= "2"]'') = 1)
----------------------------------------------------------------
CREATE TABLE [dbo].[TBL_Group](
[Id] [int] NULL,
[Title] [varchar](100) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TBL_GroupProd](
[id] [int] NULL,
[ProdID] [int] NULL,
[GroupId] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TBL_Product](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [varchar](100) NULL,
[IsActive] [bit] NULL
) ON [PRIMARY]
GO
GO
INSERT [dbo].[TBL_Group] ([Id], [Title]) VALUES (1, N'G1')
INSERT [dbo].[TBL_Group] ([Id], [Title]) VALUES (2, N'G2')
INSERT [dbo].[TBL_Group] ([Id], [Title]) VALUES (3, N'G3')
INSERT [dbo].[TBL_Group] ([Id], [Title]) VALUES (4, N'G4')
INSERT [dbo].[TBL_Group] ([Id], [Title]) VALUES (5, N'G5')
INSERT [dbo].[TBL_Group] ([Id], [Title]) VALUES (6, N'G6')
INSERT [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (1, 1, 3)
INSERT [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (2, 1, 4)
INSERT [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (3, 2, 2)
INSERT [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (4, 3, 5)
INSERT [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (5, 4, 1)
INSERT [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (6, 4, 5)
INSERT [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (7, 5, 4)
INSERT [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (8, 5, 5)
INSERT [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (9, 5, 6)
SET IDENTITY_INSERT [dbo].[TBL_Product] ON
INSERT [dbo].[TBL_Product] ([ID], [ProductName], [IsActive]) VALUES (1, N'P1', 1)
INSERT [dbo].[TBL_Product] ([ID], [ProductName], [IsActive]) VALUES (2, N'P2', 1)
INSERT [dbo].[TBL_Product] ([ID], [ProductName], [IsActive]) VALUES (3, N'P3', 1)
INSERT [dbo].[TBL_Product] ([ID], [ProductName], [IsActive]) VALUES (4, N'P4', 1)
INSERT [dbo].[TBL_Product] ([ID], [ProductName], [IsActive]) VALUES (5, N'P5', 1)
SET IDENTITY_INSERT [dbo].[TBL_Product] OFF
February 21, 2015 at 3:17 am
Quick suggestion (if I got the question right 😉 )
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_Group') IS NOT NULL DROP TABLE dbo.TBL_Group;
CREATE TABLE [dbo].[TBL_Group](
[Id] [int] NULL,
[Title] [varchar](100) NULL
);
IF OBJECT_ID(N'dbo.TBL_GroupProd') IS NOT NULL DROP TABLE dbo.TBL_GroupProd;
CREATE TABLE [dbo].[TBL_GroupProd](
[id] [int] NULL,
[ProdID] [int] NULL,
[GroupId] [int] NULL
);
IF OBJECT_ID(N'dbo.TBL_Product') IS NOT NULL DROP TABLE dbo.TBL_Product;
CREATE TABLE [dbo].[TBL_Product](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [varchar](100) NULL,
[IsActive] [bit] NULL
);
INSERT INTO [dbo].[TBL_Group] ([Id], [Title]) VALUES (1, N'G1')
INSERT INTO [dbo].[TBL_Group] ([Id], [Title]) VALUES (2, N'G2')
INSERT INTO [dbo].[TBL_Group] ([Id], [Title]) VALUES (3, N'G3')
INSERT INTO [dbo].[TBL_Group] ([Id], [Title]) VALUES (4, N'G4')
INSERT INTO [dbo].[TBL_Group] ([Id], [Title]) VALUES (5, N'G5')
INSERT INTO [dbo].[TBL_Group] ([Id], [Title]) VALUES (6, N'G6')
INSERT INTO [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (1, 1, 3)
INSERT INTO [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (2, 1, 4)
INSERT INTO [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (3, 2, 2)
INSERT INTO [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (4, 3, 5)
INSERT INTO [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (5, 4, 1)
INSERT INTO [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (6, 4, 5)
INSERT INTO [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (7, 5, 4)
INSERT INTO [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (8, 5, 5)
INSERT INTO [dbo].[TBL_GroupProd] ([id], [ProdID], [GroupId]) VALUES (9, 5, 6)
SET IDENTITY_INSERT [dbo].[TBL_Product] ON
INSERT INTO [dbo].[TBL_Product] ([ID], [ProductName], [IsActive]) VALUES (1, N'P1', 1)
INSERT INTO [dbo].[TBL_Product] ([ID], [ProductName], [IsActive]) VALUES (2, N'P2', 1)
INSERT INTO [dbo].[TBL_Product] ([ID], [ProductName], [IsActive]) VALUES (3, N'P3', 1)
INSERT INTO [dbo].[TBL_Product] ([ID], [ProductName], [IsActive]) VALUES (4, N'P4', 1)
INSERT INTO [dbo].[TBL_Product] ([ID], [ProductName], [IsActive]) VALUES (5, N'P5', 1)
SET IDENTITY_INSERT [dbo].[TBL_Product] OFF
DECLARE @SEARCH_PROD VARCHAR(100) = 'P3';
SELECT
GP.GroupId AS GROUP_ID
,PR.ID
,PR.ProductName
,PR.IsActive
FROM dbo.TBL_Product PR
INNER JOIN dbo.TBL_GroupProd GP
ON PR.ID = GP.ProdID
WHERE GP.GroupId IN
(
SELECT
GP.GroupId
FROM dbo.TBL_Product PR
INNER JOIN dbo.TBL_GroupProd GP
ON PR.ID = GP.ProdID
WHERE PR.ProductName = @SEARCH_PROD
);
DECLARE @SEARCH_GROUP VARCHAR(100) = 'G5';
SELECT
GP.GroupId AS GROUP_ID
,PR.ID
,PR.ProductName
,PR.IsActive
FROM dbo.TBL_Product PR
INNER JOIN dbo.TBL_GroupProd GP
ON PR.ID = GP.ProdID
WHERE GP.GroupId IN
(
SELECT
GP.GroupId
FROM dbo.TBL_Group GR
INNER JOIN dbo.TBL_GroupProd GP
ON GR.Id = GP.GroupId
WHERE GR.Title = @SEARCH_GROUP
);
Results
GROUP_ID ID ProductName IsActive
----------- ----------- -------------- --------
5 5 P5 1
5 4 P4 1
5 3 P3 1
GROUP_ID ID ProductName IsActive
----------- ----------- -------------- --------
5 3 P3 1
5 4 P4 1
5 5 P5 1
February 21, 2015 at 3:48 am
Thank you . I Should Test it with another fields. in real environment.
February 24, 2015 at 1:58 am
--(-- After create tables ..)
--No !!
--We have a view That is base.
-----------------------------------------
Create View VW1
As
select Id,ProductName,IsActive,[dbo].[fn_Str_GroupIDs](TBL_Product.Id) As GroupId,
CONVERT(XML,
(SELECT TBL_Group.title, dbo.TBL_Group.id
FROM TBL_Group INNER JOIN
TBL_GroupProd ON TBL_Group.Id = TBL_GroupProd.GroupId
WHERE TBL_GroupProd.ProdId = TBL_Product.Id FOR XML AUTO, ROOT('main'))) AS Group
from dbo.TBL_Product
-----------------------------------------
Create FUNCTION [dbo].[fn_Str_GroupIDs]
(
@ProdId INT
)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @strList VARCHAR(50) =''
SELECT @strList = COALESCE(@strList + '|', '') + Convert(varchar(5) ,G.id)
FROM TBL_Group G
INNER JOIN TBL_GroupProd GP ON G.Id = GP.GroupId
WHERE GP.ProdId =@ProdId
Set @strList=@strList+'|'
RETURN @strList
END
---------------------------------------------
--And This is Our Select that use for get data from view . (That is slow because of Function and xml)
select * from dbo.VW1 where ProductName = 'P4' And GroupId Like '%|5|%'
-- In old methos we use search on xml that is very slow to.
SET DATEFORMAT DMY
February 24, 2015 at 11:09 pm
any suggestion?
February 25, 2015 at 5:42 am
Why you need view and function?
What's wrong with a single query
select Id,ProductName,IsActive,
CONVERT(XML,
(SELECT TBL_Group.title, dbo.TBL_Group.id
FROM TBL_Group INNER JOIN
TBL_GroupProd ON TBL_Group.Id = TBL_GroupProd.GroupId
WHERE TBL_GroupProd.ProdId = TBL_Product.Id FOR XML AUTO, ROOT('main'))) AS [Group]
from dbo.TBL_Product
where ProductName = 'P4'
and exists (select null
from TBL_GroupProd
where TBL_GroupProd.ProdId = TBL_Product.Id
and TBL_GroupProd.GroupId = 5)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply