SQL Server Performance Forum – Threads Archive

optimizing range-lookup join

Consider the following situation. I’ve got a big table TAB_A (4M) and a small table TAB_B(500)
In table TAB_A there’s a attribute probability (between 0 and 1)
In table TAB_B there’s three columns: value_from, value_to, cum_std_normal_distribution
0 0.002 0.00000001
0.002 0.004 0.00000241
0.004 0.006 0.00006890
etc For every record in TAB_A I need to retrieve the cum_std_normal_distribution belonging to the value of the probability.
So if for record 1 the probability equals 0.005 I need to use the 0.00000241-value SQL looks like: SELECT TAB_A.key, ….., TAB_B.cum_std_normal_distribution
FROM TAB_A, TAB_B
WHERE TAB_A.probability >= TAB_B.value_from
AND TAB_A.probability < TAB_B.value_to The database does tablescans on both tables and although B is very small, for a 4M-table A it takes up 3 hours Does someone know a trick to solve this? I suspect there should be a kind of algorithmic trick to solve this and make it fly (using only 1 table-scan on the 4M-table) Thanks heaps Jaap Jadie

Have you any clustered index on table A , have you reindex table A ,
dbcc dbreindex , and if you dont have any index please create an index on table A , it will sure boost your performance. HTH Regards. hsGoswami
[email protected]

I’ve tried all kinds of indexes on both tables, but it doesn’t seem to use any.
I guessed that due to the <= and > join it couldn’t use an index. I tried probability + key (amongst others), or do you have any other suggestions? Thanks,
Jaap Jadie

why you dont use joins to retrieve data from more then one table , please refer
http://www.sql-server-performance.com/tuning_joins.asp
Regards. hsGoswami
[email protected]

Your where clause actually contains join condition only. If ranges in table B cover all possible probabilites (cover the whole [0, 1] interval) then only index that can help is one containing probability and columns from table a in select list. Even that index will not make it fly.

]]>