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+'%')
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

more ▼

asked Nov 23, 2010 at 04:58 AM in Default

avatar image

72 4 5 8

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

Nov 23, 2010 at 06:26 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Nov 23, 2010 at 05:13 AM

avatar image

Håkan Winther
16.6k 38 46 58

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

Nov 23, 2010 at 05:27 AM sujafiza

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. :(

Nov 23, 2010 at 05:38 AM Håkan Winther

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

Nov 23, 2010 at 06:45 AM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 23, 2010 at 04:58 AM

Seen: 1247 times

Last Updated: Nov 23, 2010 at 06:26 AM

Copyright 2018 Redgate Software. Privacy Policy