在TSQL中有交叉连接的替代方法吗

I have two tables Table1 and Table2 where i need to find all the rows which satisfies the condition Fuzzy_Qgram(Approximate matching optimization) between Table1.LastName and Table2.LastName

我有两个表表表表1和表2，其中我需要查找表1之间满足条件Fuzzy_Qgram(近似匹配优化)的所有行。LastName和Table2.LastName

Here is my Query :

我的问题是:

Select * from Table1 
Cross Join
Table2
Where Fuzzy_Qgram(Table1.LastName,Table2.LastName)> =1.5

In this query, Each row in Table1 is multipled with each row in Table2 and the rows which satisfy Fuzzy_Qgram(Table1.LastName,Table2.LastName)> =1.5 will be returned.

在这个查询中，Table1中的每一行都与Table2中的每一行和满足Fuzzy_Qgram(Table1. lastname,Table2. lastname)> =1.5的行进行了多处理。

The Execution of query is very slow as i have more than 300,000 rows in Table1 and 3000 rows in Table2

查询的执行非常缓慢，因为表1中有30多万行，表2中有3000行

Is there any alternative to Cross Join for such queries for better and faster optimization?

对于这样的查询，是否有交叉连接的替代方案以获得更好、更快的优化?

2 个解决方案

#1

Does the order of parameters to the function make a difference? I mean, when you run the following query, are you guaranteed to get the same results?

参数对函数的顺序有影响吗?我的意思是，当你运行下面的查询时，你能保证得到相同的结果吗?

Select Fuzzy_Qgram('Washington','Obama')
Select Fuzzy_Qgram('Obama','Washington')

If so, then there shouldn't be any reason to test it both ways, so you could change your query to:

如果是这样，那么就没有任何理由对它进行双向测试，因此您可以将查询更改为:

Select * 
from   Table1 
       Inner join Table2
         On Table.LastName <= Table2.LastName
Where Fuzzy_Qgram(Table1.LastName,Table2.LastName)> =1.5

Of course, I have no idea what Fuzzy_Qgram does, so you will want to test this thoroughly. If this is correct, it will probably reduce your execution time by approximately 1/2.

当然，我不知道Fuzzy_Qgram是做什么的，所以您需要对它进行彻底的测试。如果这是正确的，它可能会将执行时间减少大约1/2。

#2

To cut the long story short.

长话短说。

SELECT * 
FROM 
 Table1 t1
 JOIN Table2 t2
  ON 1=1
 CROSS APPLY (
  SELECT 
    fuzzy_qgram = t1.someColumnOf_t1 + t2.someColumnOf_t2
 ) ext 
WHERE ext.fuzzy_qgram >= 1.5

Of course, you have to replace t1.someColumnOf_t1 + t2.someColumnOf_t2 with your fuzzy_qgram logic. Use this and there is a chance you will see significant speedup.

当然，你必须替换t1。someColumnOf_t1 + t2。有模糊的qgram逻辑的someColumnOf_t2。使用这个，你就有可能看到显著的加速。

#1