question

sujafiza avatar image
sujafiza asked

Cross table pattern matching

Hi All, I have two tables @t1(t1nodekey varchar(25)) and @t2(t2nodekey varchar(25)) having a list of keywords. My requirement is to insert the keywords from @t1 if that has a pattern match in @t2 and vice versa. May be it looks something like below: 

insert @t3
select distinct t1nodekey from @t1 
where exists (select t2nodekey from @t2 where t1nodekey like  t2nodekey+'%')
UNION
select distinct t2nodekey from @t2 
where exists (select t1nodekey from @t1 where t2nodekey like  t1nodekey+'%')
But because of huge data in these tables it is causing bad performance. Can anybody suggest any better solution to accomplish this. I'm using SQLserver 2005. Thanks in advance, Su
sql-server-2005performance-tuning
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
@sujafiza - you mention that this is a function and therefore has to stay as table variables. Can you show us what it is you are doing that means you *have* to use a function. I am sure we can come up with something to help you here.
4 Likes 4 ·

1 Answer

·
Håkan Winther avatar image
Håkan Winther answered
One thing you should remeber is that SQL server doesn't create statistics for table variables and it asumes there is only one record in each table variable. If you have a lot of records then SQL server will use an inefficient execution plan. You can try to convert them to temporary tables instead, otherwise you need to use OPTION (RECOMPILE) You should also consider using UNION ALL instead of UNION. Why? Because Union will try to remove duplicate values from the combined result. If you don't expect to have duplicates then UNION ALL will save you a lot of resources. I have seen too many cases where UNION is used where UNION ALL should have been used. Personally I think that the behavior of UNION ALL should have been implemented with UNION and the behavior of UNION should have been implemented with something like UNION DISTINCT, because that would be more logical.
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image Håkan Winther commented ·
If you really want to increase the performance, try to stay far, far away from functions that aren't inline! Search on the Internet and you will find lots of information about the performance impact of UDFs like this one http://allinthehead.com/retro/247/user-defined-functions-considered-harmful This is about SQL 2000, but nothing has changed since introduced and will not even change in Denali. :(
1 Like 1 ·
sujafiza avatar image sujafiza commented ·
But this logic is in a function so I can't try with temp tables.
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
@sujafiza : huge amounts of data movement in a function??? WilliamD is right - show us what you are really trying to achieve
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.