-
Range Join query
I am working with (SAS) proc sql and am having trouble with range join.
Proc sql has problems with subqueries so I am restricted to working with joins only.
Table A has 1000 records consisting of a user_id and an ip_long (long version of an ip address)
Table B has 5 Mill records with 4 variables, ip_from, ip_to, country_code, ISP_name
The ranges are not indexed.
My query to join a user_id and ip_long to it's corresponding countrycode and isp_name takes over 1 hour which is not an acceptable timeframe.
The SAS query optimiser log points out that as an equijoin is not possible the result involves a cartesian product....
Would a similar query running on sqlserver or oracle using a proper sql implimentation be any different?
Any suggestions on how to restructure the query?
Sample query
SELECT a.user_id
, a.ip_long
, b.isp_name
, b.country_code
FROM tablea a
LEFT JOIN tableb b
ON a.ip_long BETWEEN b.ip_from AND b.ip_to
cheers
-
Too basic?
Wrong forum?
Queston not clearly framed?
If the last, what I'm really trying to find out is if range joins in standard sql environments (oracle, sqlserver etc) are always performed by the optimizer using a cartesian product, which in my query above is no doubt the cause of the long query time.
-
Try this
SELECT a.user_id
, a.ip_long
, b.isp_name
, b.country_code
FROM tablea a
LEFT JOIN tableb b
ON a.ip_long =b.ip_long where.......
-
did you try the query I posted?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|