以下是引用hjlali在2021-8-20 19:06:21的发言:
谢谢你,你这个方法看起来更高阶一点。目前来看,我原来的语句语法是没有问题的,按照2楼的办法,分两段执行也就几秒钟就可以出来结果。
经过比较,主要是用了alltrim函数拖慢了速度,如果能有一条命令快速出结果的办法当然更好了。
对比测试一下,30W条记录
程序代码:
** 测试数据
CREATE CURSOR ys1 (lxfs C(10))
FOR i=1 TO 300000
INSERT INTO ys1 VALUES (PADL(i,7,"0"))
ENDFOR
CREATE CURSOR fs1 (lxfs C(10))
APPEND FROM DBF("ys1")
SELECT ys1
GO TOP
REPLACE lxfs WITH "ys1_000001"
GO BOTTOM
REPLACE lxfs WITH "ys1_100000"
t = SECONDS()
SELECT ALLTRIM(lxfs) AS MY_KEY from fs1 DIST NOCO INTO TABLE TEMP_KEY
SELECT * FROM ys1 where ALLTRIM(lxfs) NOT in(SELECT MY_KEY FROM TEMP_KEY) NOWAIT
?SECONDS()-t && 5.5s
** 去掉ALLTRIM
t = SECONDS()
SELECT lxfs AS MY_KEY from fs1 DIST NOCO INTO TABLE TEMP_KEY
SELECT * FROM ys1 where lxfs NOT in(SELECT MY_KEY FROM TEMP_KEY) NOWAIT
?SECONDS()-t && 5.0s
t = SECONDS()
SELECT fs1
INDEX on lxfs TAG tag_fs1
SELECT ys1
SET RELATION TO lxfs INTO "fs1"
COPY TO tmp FOR !FOUND("fs1")
SET RELATION TO
?SECONDS()-t && 3.5s
SELECT * FROM tmp NOWAIT