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