Cross table pattern matching

Question

question

sujafiza asked Nov 23, '10

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-2005 performance-tuning

1

10 |1200

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

WilliamD commented · Nov 23, 2010 at 06:26 AM

@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 ·

Answer 1 · 2010-11-23T05:13:30Z

Håkan Winther answered Nov 23, '10

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

10 |1200

Attachments: 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 commented · Nov 23, 2010 at 05:38 AM

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 ·

sujafiza commented · Nov 23, 2010 at 05:27 AM

But this logic is in a function so I can't try with temp tables.

0 ·

Kev Riley ♦♦ commented · Nov 23, 2010 at 06:45 AM

@sujafiza : huge amounts of data movement in a function??? WilliamD is right - show us what you are really trying to achieve

0 ·

question