How to using alternative solution of like ?

Question

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 11-Jun-21 1:30am

ahmed_sa

Updated 12-Sep-22 0:55am

Add a Solution

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

Add a Solution

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

Member 7870345 · Answer 1 · 2022-09-12T00:55:00

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 )
  )

How to using alternative solution of like ?

1 solution

Solution 1

Add your solution here

Preview 0

How to using alternative solution of like ?

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us