Click here to Skip to main content
15,904,934 members
Please Sign up or sign in to vote.
1.44/5 (2 votes)
See more: , +
I work on SQL server 2012 I face issue I can't display Part Number with Mask related for family without using like because it is more slower running
when data is big
so can i do result below without using like


SQL
   CREATE TABLE [dbo].[Partspc](
    [PortionKey] [nvarchar](255) NULL,
    [GroupID] [float] NULL,
    [familyid] [float] NULL
) ON [PRIMARY]

INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'T496', 1, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'X', 2, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'B', 2, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'754', 3, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'755', 3, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'K', 4, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'M', 4, 7524090)
INSERT [dbo].[Partspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'blank', 4, 7524090)


CREATE TABLE [dbo].[Masksspc](
    [PortionKey] [nvarchar](255) NULL,
    [GroupID] [float] NULL,
    [familyid] [float] NULL
) ON [PRIMARY]


INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'T496', 1, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'X', 2, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'B', 2, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'754', 3, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'755', 3, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES (N'_', 4, 7524090)
INSERT [dbo].[Masksspc] ([PortionKey], [GroupID], [familyid]) VALUES ('blank', 4, 7524090)

DECLARE @GetFinalResultParts as table
 (    [familyid] [int] not NULL INDEX IXkfamily NONCLUSTERED,
     [GroupID] [int] not NULL INDEX IXkgroup NONCLUSTERED,
     [PartNumber] [nvarchar](200) NOT NULL INDEX IXkpart NONCLUSTERED
     )





 insert into @GetFinalResultParts
 SELECT distinct r.familyid,r.GroupID,IIF(r.PortionKey='blank','',r.PortionKey)
 FROM    dbo.Partspc r with(nolock)


 ;WITH cte AS (
         SELECT  t1.familyid,t1.GroupID,cast((t1.PartNumber) as nvarchar(200)) PartNumber
         FROM    @GetFinalResultParts t1
         WHERE   t1.GroupID = 1
         UNION ALL
         SELECT  t.familyid,t.GroupID,cast((s.PartNumber+ t.PartNumber) as nvarchar(200)) PartNumber

         FROM    @GetFinalResultParts t INNER JOIN
                 cte s ON t.GroupID = s.GroupID + 1
 )
 SELECT  familyid,PartNumber,GroupID
 into  getfinaldatapc
 from    cte
 where GroupID =(select max(GroupID) from dbo.Partspc with(nolock))
 group by familyid,PartNumber,GroupID


DECLARE @GetFinalMasks as table
 (    [familyid] [int] not NULL INDEX IXkfamily NONCLUSTERED,
     [GroupID] [int] not NULL INDEX IXkgroup NONCLUSTERED,
     [MaskNumber] [nvarchar](200) NOT NULL INDEX IXkmask NONCLUSTERED)





 insert into @GetFinalMasks
 SELECT distinct m.familyid,m.GroupID,IIF(m.PortionKey='blank','',m.PortionKey)
 FROM    dbo.Masksspc m with(nolock)


 ;WITH cte AS (
         SELECT  t1.familyid,t1.GroupID,cast((t1.MaskNumber) as nvarchar(200)) MaskNumber
         FROM    @GetFinalMasks t1
         WHERE   t1.GroupID = 1
         UNION ALL
         SELECT  t.familyid,t.GroupID,cast((s.MaskNumber+t.MaskNumber) as nvarchar(200)) MaskNumber

         FROM    @GetFinalMasks t INNER JOIN
                 cte s ON t.GroupID = s.GroupID + 1
 )
 SELECT  familyid,MaskNumber
 into  getfinaldatapcmask
 from    cte
 where GroupID =(select max(GroupID) from dbo.Masksspc with(nolock))
 group by familyid,MaskNumber

  SELECT * FROM getfinaldatapc
 select * from getfinaldatapcmask


What I have tried:

SQL
SELECT * FROM getfinaldatapc g  
  JOIN getfinaldatapcmask gm 
  ON g.familyid=gm.familyid AND (g.PartNumber=gm.MaskNumber OR
  (g.PartNumber LIKE gm.MaskNumber+'%' AND LEN(g.PartNumber)=LEN(gm.MaskNumber)))
Posted
Updated 12-Sep-22 0:55am
Comments
CHill60 11-Jun-21 8:47am    
Have you analyzed your query to make sure it's that last bit that is the issue?
Try putting appropriate indexes on getfinaldatapc and getfinaldatapcmask
ahmed_sa 11-Jun-21 11:19am    
are there are any solution without using like
ahmed_sa 11-Jun-21 12:17pm    
can i generate unique id for table getfinaldatapc by checksum or what every
and generate unique id for table getfinaldatapcmask also by checksum
then join between two tables by id generated
if this possible can you help me applying
[no name] 11-Jun-21 13:51pm    
Apparently, there is no guarantee that SQL will short-circuit an OR.

https://stackoverflow.com/questions/11219791/or-operator-short-circuit-in-sql-server

1 solution

Instead of using:
SELECT * FROM getfinaldatapc g  
  JOIN getfinaldatapcmask gm 
  ON g.familyid=gm.familyid AND (g.PartNumber=gm.MaskNumber OR
  (g.PartNumber LIKE gm.MaskNumber+'%' AND LEN(g.PartNumber)=LEN(gm.MaskNumber)))


try with
SELECT * FROM getfinaldatapc g  
  JOIN getfinaldatapcmask gm 
  ON g.familyid=gm.familyid AND (g.PartNumber=gm.MaskNumber OR
  (  
    LEFT(g.PartNumber,LEN(gm.MaskNumber))=gm.MaskNumber )
  )
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900