sqldba2013
asked on
How to avoid LIKE clause in join - sql server
Hi All,
In the below query, update statment is taking very long time to generate output. Please advise how to rewrite below query for better performance.
I don't want to remove '[' and ']' symbol in query because in the DB data contains '[' and ']' symbols.
In the below query, update statment is taking very long time to generate output. Please advise how to rewrite below query for better performance.
I don't want to remove '[' and ']' symbol in query because in the DB data contains '[' and ']' symbols.
CREATE TABLE #temp
(
[Inquiry Number] NVARCHAR(255),
[CPO Number] VARCHAR(50),
[SO Number] VARCHAR(50)
)
CREATE nonclustered INDEX ix_InqNum ON #temp ([Inquiry Number]) include ([CPO Number])
INSERT INTO #temp
(
[Inquiry Number],[CPO Number]
)
SELECT distinct PRA.inq_num,[PO Number]
FROM [LocalDB].dbo.ps_requests_asr PRA with(nolock)left join [RemoteDB].[DBName].dbo.CPOA with(nolock) ON cast(Inquiries as NVARCHAR(450)) = '[' + PRA.inq_num + ']'
WHERE PRA.inq_num is not null
UPDATE #temp SET [SO Number]= CUST.[SO] FROM #temp INNER JOIN [RemoteDB].[DBName].dbo.CPOA CPON WITH(NOLOCK) ON cast(CPON.Inquiries as NVARCHAR(450)) = '[' + #temp.[Inquiry Number] + ']'
INNER JOIN [RemoteDB].[DBName].dbo.BI_CUSTMON CUST WITH(NOLOCK) ON CUST.[Customer PO] LIKE #temp.[CPO Number] + '%'
select * from #temp
ASKER
I have already created non-clustered index on CPO number of #temp table.
I am facing query slowness issue with UPDATE statement not with INSERT.
Could you please tell me how to avoid like statement in above update statement or how to rewrite above query for better performance.
I have tried with = operator in place of like and I didn't get data as per my requirement for = operator.
Please advise.
I am facing query slowness issue with UPDATE statement not with INSERT.
Could you please tell me how to avoid like statement in above update statement or how to rewrite above query for better performance.
I have tried with = operator in place of like and I didn't get data as per my requirement for = operator.
Please advise.
ASKER
As per your suggestion, I have removed [ and ] from join and I got NULL as a output result.
CREATE TABLE #temp
(
[Inquiry Number] NVARCHAR(255),
[CPO Number] VARCHAR(50),
[SO Number] VARCHAR(50)
)
CREATE nonclustered INDEX ix_InqNum ON #temp ([Inquiry Number]) include ([CPO Number])
INSERT INTO #temp
(
[Inquiry Number],[CPO Number]
)
SELECT distinct '['+PRA.inq_num+']',[PO Number]
FROM [LocalDB].dbo.ps_requests_asr PRA with(nolock)left join [RemoteDB].[DBName].dbo.CPOA with(nolock) ON cast(Inquiries as NVARCHAR(450)) = PRA.inq_num
WHERE PRA.inq_num is not null
UPDATE #temp SET [SO Number]= CUST.[SO] FROM #temp INNER JOIN [RemoteDB].[DBName].dbo.CPOA CPON WITH(NOLOCK) ON cast(CPON.Inquiries as NVARCHAR(450)) = #temp.[Inquiry Number]
INNER JOIN [RemoteDB].[DBName].dbo.BI_CUSTMON CUST WITH(NOLOCK) ON CUST.[Customer PO] LIKE #temp.[CPO Number] + '%'
select * from #temp
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
the above solutions worked for me.
Open in new window
and
Open in new window
And when you are doing changes don't forget to create an index on [CPO Number] of #temp also. Since your join to cpo number is a "starts with" it can also use an index.