March 26, 2009 at 12:11 pm
I have a situation where I have to join two tables on a key column and a timestamp column.
The issue is that I have to join in situations where the timestamp on first table is within
timestamp + 20 seconds of the second table. See the join below
My first table (tbl1 below) has about 25million records and second table (tbl2 below) has 5 million.
Multi joins are possible.
The time it is taking to do the join written below is over 5 hours.
My questions is - can this be made to run faster - some how.
I have created some sample data below - 100 recs in each table - this runs pretty quick but
the volume I have is taking too long to run.
drop table tbl1, tbl2
create table tbl1 (act1 bigint, t1_ts smalldatetime, datacol1 varchar(25) )
insert into tbl1 values (3052793594, '2009-01-01 00:08:31.000','somedata')
insert into tbl1 values (9194967838, '2009-01-01 00:21:07.000','somedata')
insert into tbl1 values (7174633752, '2009-01-01 00:21:58.000','somedata')
insert into tbl1 values (9105853335, '2009-01-01 00:25:08.000','somedata')
insert into tbl1 values (8129261391, '2009-01-01 00:25:17.000','somedata')
insert into tbl1 values (7024593331, '2009-01-01 00:25:46.000','somedata')
insert into tbl1 values (9105853335, '2009-01-01 00:27:26.000','somedata')
insert into tbl1 values (2394582114, '2009-01-01 00:29:22.000','somedata')
insert into tbl1 values (4072760227, '2009-01-01 00:45:54.000','somedata')
insert into tbl1 values (8166335594, '2009-01-01 00:45:54.000','somedata')
insert into tbl1 values (9105928229, '2009-01-01 00:47:48.000','somedata')
insert into tbl1 values (2546908776, '2009-01-01 00:50:18.000','somedata')
insert into tbl1 values (9108933604, '2009-01-01 01:04:51.000','somedata')
insert into tbl1 values (9049645195, '2009-01-01 01:06:02.000','somedata')
insert into tbl1 values (9049645195, '2009-01-01 01:06:29.000','somedata')
insert into tbl1 values (2399470828, '2009-01-01 01:20:04.000','somedata')
insert into tbl1 values (3605312237, '2009-01-01 01:20:39.000','somedata')
insert into tbl1 values (4345094937, '2009-01-01 01:20:58.000','somedata')
insert into tbl1 values (8636551864, '2009-01-01 01:32:34.000','somedata')
insert into tbl1 values (9106255770, '2009-01-01 01:36:42.000','somedata')
insert into tbl1 values (7023620996, '2009-01-01 01:41:36.000','somedata')
insert into tbl1 values (7024319493, '2009-01-01 01:48:13.000','somedata')
insert into tbl1 values (9106545352, '2009-01-01 01:53:03.000','somedata')
insert into tbl1 values (8503522392, '2009-01-01 01:57:22.000','somedata')
insert into tbl1 values (4699643656, '2009-01-01 02:09:49.000','somedata')
insert into tbl1 values (8509266004, '2009-01-01 02:21:38.000','somedata')
insert into tbl1 values (2525991165, '2009-01-01 02:23:14.000','somedata')
insert into tbl1 values (2525420222, '2009-01-01 02:28:59.000','somedata')
insert into tbl1 values (9038872503, '2009-01-01 02:38:45.000','somedata')
insert into tbl1 values (7025727667, '2009-01-01 02:45:22.000','somedata')
insert into tbl1 values (9038877802, '2009-01-01 02:47:44.000','somedata')
insert into tbl1 values (5733647072, '2009-01-01 02:51:33.000','somedata')
insert into tbl1 values (7027349721, '2009-01-01 03:01:08.000','somedata')
insert into tbl1 values (7027429306, '2009-01-01 03:01:22.000','somedata')
insert into tbl1 values (8283973190, '2009-01-01 03:17:57.000','somedata')
insert into tbl1 values (8505096571, '2009-01-01 04:03:16.000','somedata')
insert into tbl1 values (7856543649, '2009-01-01 04:11:42.000','somedata')
insert into tbl1 values (3523263192, '2009-01-01 04:22:54.000','somedata')
insert into tbl1 values (9104886948, '2009-01-01 04:39:54.000','somedata')
insert into tbl1 values (7027167038, '2009-01-01 04:46:32.000','somedata')
insert into tbl1 values (8504597290, '2009-01-01 04:57:50.000','somedata')
insert into tbl1 values (7028762153, '2009-01-01 05:00:27.000','somedata')
insert into tbl1 values (5733016488, '2009-01-01 05:02:27.000','somedata')
insert into tbl1 values (2399366727, '2009-01-01 05:03:10.000','somedata')
insert into tbl1 values (7027367524, '2009-01-01 05:12:55.000','somedata')
insert into tbl1 values (9104243821, '2009-01-01 05:15:32.000','somedata')
insert into tbl1 values (9108753540, '2009-01-01 05:22:52.000','somedata')
insert into tbl1 values (3527956293, '2009-01-01 05:33:36.000','somedata')
insert into tbl1 values (8504593176, '2009-01-01 05:34:40.000','somedata')
insert into tbl1 values (4345897234, '2009-01-01 05:35:55.000','somedata')
insert into tbl1 values (4345897234, '2009-01-01 05:37:21.000','somedata')
insert into tbl1 values (4345897234, '2009-01-01 05:37:54.000','somedata')
insert into tbl1 values (4345897234, '2009-01-01 05:39:00.000','somedata')
insert into tbl1 values (4345897234, '2009-01-01 05:39:00.000','somedata')
insert into tbl1 values (4345897234, '2009-01-01 05:39:27.000','somedata')
insert into tbl1 values (5745867399, '2009-01-01 05:48:56.000','somedata')
insert into tbl1 values (9195423498, '2009-01-01 05:58:51.000','somedata')
insert into tbl1 values (4192951565, '2009-01-01 06:06:31.000','somedata')
insert into tbl1 values (7175824502, '2009-01-01 06:19:02.000','somedata')
insert into tbl1 values (3522889399, '2009-01-01 06:22:53.000','somedata')
insert into tbl1 values (7025475954, '2009-01-01 06:26:30.000','somedata')
insert into tbl1 values (4349773956, '2009-01-01 06:35:31.000','somedata')
insert into tbl1 values (9049648884, '2009-01-01 06:35:59.000','somedata')
insert into tbl1 values (9374922974, '2009-01-01 06:36:29.000','somedata')
insert into tbl1 values (2524563924, '2009-01-01 06:40:25.000','somedata')
insert into tbl1 values (7172412634, '2009-01-01 06:42:00.000','somedata')
insert into tbl1 values (2398106793, '2009-01-01 06:42:52.000','somedata')
insert into tbl1 values (4238540907, '2009-01-01 06:43:02.000','somedata')
insert into tbl1 values (4346076151, '2009-01-01 06:43:04.000','somedata')
insert into tbl1 values (7173340816, '2009-01-01 06:45:24.000','somedata')
insert into tbl1 values (3522450214, '2009-01-01 06:50:47.000','somedata')
insert into tbl1 values (7172619516, '2009-01-01 06:50:49.000','somedata')
insert into tbl1 values (4195221744, '2009-01-01 06:52:03.000','somedata')
insert into tbl1 values (8503850216, '2009-01-01 06:52:08.000','somedata')
insert into tbl1 values (4195221744, '2009-01-01 06:53:22.000','somedata')
insert into tbl1 values (2522167891, '2009-01-01 06:54:31.000','somedata')
insert into tbl1 values (8634945597, '2009-01-01 06:54:42.000','somedata')
insert into tbl1 values (8283975499, '2009-01-01 06:56:52.000','somedata')
insert into tbl1 values (3525682977, '2009-01-01 06:56:52.000','somedata')
insert into tbl1 values (6207430125, '2009-01-01 06:58:55.000','somedata')
insert into tbl1 values (2527464904, '2009-01-01 07:00:52.000','somedata')
insert into tbl1 values (9376381823, '2009-01-01 07:03:51.000','somedata')
insert into tbl1 values (3366673794, '2009-01-01 07:07:06.000','somedata')
insert into tbl1 values (8508750044, '2009-01-01 07:10:58.000','somedata')
insert into tbl1 values (2524696579, '2009-01-01 07:12:53.000','somedata')
insert into tbl1 values (4078460109, '2009-01-01 07:16:04.000','somedata')
insert into tbl1 values (3368388714, '2009-01-01 07:16:06.000','somedata')
insert into tbl1 values (8502645978, '2009-01-01 07:18:02.000','somedata')
insert into tbl1 values (6604386862, '2009-01-01 07:18:29.000','somedata')
insert into tbl1 values (2525675056, '2009-01-01 07:19:36.000','somedata')
insert into tbl1 values (9108924863, '2009-01-01 07:20:28.000','somedata')
insert into tbl1 values (3527500836, '2009-01-01 07:21:31.000','somedata')
insert into tbl1 values (7577537788, '2009-01-01 07:22:38.000','somedata')
insert into tbl1 values (2392836119, '2009-01-01 07:24:32.000','somedata')
insert into tbl1 values (9103235418, '2009-01-01 07:24:45.000','somedata')
insert into tbl1 values (8166505296, '2009-01-01 07:26:07.000','somedata')
insert into tbl1 values (3522598367, '2009-01-01 07:27:38.000','somedata')
insert into tbl1 values (3524554700, '2009-01-01 07:30:41.000','somedata')
insert into tbl1 values (8282946168, '2009-01-01 07:32:06.000','somedata')
insert into tbl1 values (3362190454, '2009-01-01 07:32:57.000','somedata')
create table tbl2 (act2 bigint, t2_ts smalldatetime, datacol2 varchar(25) )
insert into tbl2 values (3052793594, '2009-01-01 00:06:33.000', 'somedata')
insert into tbl2 values (9194967838, '2009-01-01 00:20:24.000', 'somedata')
insert into tbl2 values (7174633752, '2009-01-01 00:20:48.000', 'somedata')
insert into tbl2 values (9105853335, '2009-01-01 00:24:45.000', 'somedata')
insert into tbl2 values (8129261391, '2009-01-01 00:24:06.000', 'somedata')
insert into tbl2 values (7024593331, '2009-01-01 00:24:21.000', 'somedata')
insert into tbl2 values (9105853335, '2009-01-01 00:26:18.000', 'somedata')
insert into tbl2 values (2394582114, '2009-01-01 00:27:24.000', 'somedata')
insert into tbl2 values (4072760227, '2009-01-01 00:43:57.000', 'somedata')
insert into tbl2 values (8166335594, '2009-01-01 00:44:30.000', 'somedata')
insert into tbl2 values (9105928229, '2009-01-01 00:47:06.000', 'somedata')
insert into tbl2 values (2546908776, '2009-01-01 00:48:33.000', 'somedata')
insert into tbl2 values (9108933604, '2009-01-01 01:04:09.000', 'somedata')
insert into tbl2 values (9049645195, '2009-01-01 01:04:57.000', 'somedata')
insert into tbl2 values (9049645195, '2009-01-01 01:05:24.000', 'somedata')
insert into tbl2 values (2399470828, '2009-01-01 01:18:06.000', 'somedata')
insert into tbl2 values (3605312237, '2009-01-01 01:20:03.000', 'somedata')
insert into tbl2 values (4345094937, '2009-01-01 01:20:03.000', 'somedata')
insert into tbl2 values (8636551864, '2009-01-01 01:30:36.000', 'somedata')
insert into tbl2 values (9106255770, '2009-01-01 01:35:36.000', 'somedata')
insert into tbl2 values (7023620996, '2009-01-01 01:40:12.000', 'somedata')
insert into tbl2 values (7024319493, '2009-01-01 01:46:48.000', 'somedata')
insert into tbl2 values (9106545352, '2009-01-01 01:52:21.000', 'somedata')
insert into tbl2 values (8503522392, '2009-01-01 01:56:39.000', 'somedata')
insert into tbl2 values (4699643656, '2009-01-01 02:08:39.000', 'somedata')
insert into tbl2 values (8509266004, '2009-01-01 02:20:24.000', 'somedata')
insert into tbl2 values (2525991165, '2009-01-01 02:22:03.000', 'somedata')
insert into tbl2 values (2525420222, '2009-01-01 02:28:18.000', 'somedata')
insert into tbl2 values (9038872503, '2009-01-01 02:37:36.000', 'somedata')
insert into tbl2 values (7025727667, '2009-01-01 02:43:57.000', 'somedata')
insert into tbl2 values (9038877802, '2009-01-01 02:46:33.000', 'somedata')
insert into tbl2 values (5733647072, '2009-01-01 02:50:09.000', 'somedata')
insert into tbl2 values (7027349721, '2009-01-01 02:59:45.000', 'somedata')
insert into tbl2 values (7027429306, '2009-01-01 02:59:57.000', 'somedata')
insert into tbl2 values (8283973190, '2009-01-01 03:16:30.000', 'somedata')
insert into tbl2 values (8505096571, '2009-01-01 04:02:09.000', 'somedata')
insert into tbl2 values (7856543649, '2009-01-01 04:11:39.000', 'somedata')
insert into tbl2 values (3523263192, '2009-01-01 04:21:48.000', 'somedata')
insert into tbl2 values (9104886948, '2009-01-01 04:39:12.000', 'somedata')
insert into tbl2 values (7027167038, '2009-01-01 04:45:09.000', 'somedata')
insert into tbl2 values (8504597290, '2009-01-01 04:55:51.000', 'somedata')
insert into tbl2 values (7028762153, '2009-01-01 04:59:03.000', 'somedata')
insert into tbl2 values (5733016488, '2009-01-01 05:01:18.000', 'somedata')
insert into tbl2 values (2399366727, '2009-01-01 05:01:12.000', 'somedata')
insert into tbl2 values (7027367524, '2009-01-01 05:11:30.000', 'somedata')
insert into tbl2 values (9104243821, '2009-01-01 05:14:48.000', 'somedata')
insert into tbl2 values (9108753540, '2009-01-01 05:20:54.000', 'somedata')
insert into tbl2 values (3527956293, '2009-01-01 05:32:27.000', 'somedata')
insert into tbl2 values (8504593176, '2009-01-01 05:32:42.000', 'somedata')
insert into tbl2 values (4345897234, '2009-01-01 05:34:45.000', 'somedata')
insert into tbl2 values (4345897234, '2009-01-01 05:36:45.000', 'somedata')
insert into tbl2 values (4345897234, '2009-01-01 05:36:12.000', 'somedata')
insert into tbl2 values (4345897234, '2009-01-01 05:37:18.000', 'somedata')
insert into tbl2 values (4345897234, '2009-01-01 05:37:51.000', 'somedata')
insert into tbl2 values (4345897234, '2009-01-01 05:39:09.000', 'somedata')
insert into tbl2 values (5745867399, '2009-01-01 05:47:45.000', 'somedata')
insert into tbl2 values (9195423498, '2009-01-01 05:58:09.000', 'somedata')
insert into tbl2 values (4192951565, '2009-01-01 06:04:48.000', 'somedata')
insert into tbl2 values (7175824502, '2009-01-01 06:17:54.000', 'somedata')
insert into tbl2 values (3522889399, '2009-01-01 06:21:45.000', 'somedata')
insert into tbl2 values (7025475954, '2009-01-01 06:25:06.000', 'somedata')
insert into tbl2 values (4349773956, '2009-01-01 06:34:21.000', 'somedata')
insert into tbl2 values (9049648884, '2009-01-01 06:34:54.000', 'somedata')
insert into tbl2 values (9374922974, '2009-01-01 06:34:48.000', 'somedata')
insert into tbl2 values (2524563924, '2009-01-01 06:39:42.000', 'somedata')
insert into tbl2 values (7172412634, '2009-01-01 06:40:51.000', 'somedata')
insert into tbl2 values (2398106793, '2009-01-01 06:41:42.000', 'somedata')
insert into tbl2 values (4238540907, '2009-01-01 06:41:54.000', 'somedata')
insert into tbl2 values (4346076151, '2009-01-01 06:41:54.000', 'somedata')
insert into tbl2 values (7173340816, '2009-01-01 06:44:15.000', 'somedata')
insert into tbl2 values (3522450214, '2009-01-01 06:49:39.000', 'somedata')
insert into tbl2 values (7172619516, '2009-01-01 06:49:39.000', 'somedata')
insert into tbl2 values (4195221744, '2009-01-01 06:50:21.000', 'somedata')
insert into tbl2 values (8503850216, '2009-01-01 06:50:51.000', 'somedata')
insert into tbl2 values (4195221744, '2009-01-01 06:51:39.000', 'somedata')
insert into tbl2 values (2522167891, '2009-01-01 06:53:24.000', 'somedata')
insert into tbl2 values (8634945597, '2009-01-01 06:52:45.000', 'somedata')
insert into tbl2 values (8283975499, '2009-01-01 06:55:21.000', 'somedata')
insert into tbl2 values (3525682977, '2009-01-01 06:55:45.000', 'somedata')
insert into tbl2 values (6207430125, '2009-01-01 06:57:48.000', 'somedata')
insert into tbl2 values (2527464904, '2009-01-01 07:00:09.000', 'somedata')
insert into tbl2 values (9376381823, '2009-01-01 07:02:42.000', 'somedata')
insert into tbl2 values (3366673794, '2009-01-01 07:06:24.000', 'somedata')
insert into tbl2 values (8508750044, '2009-01-01 07:09:42.000', 'somedata')
insert into tbl2 values (2524696579, '2009-01-01 07:12:12.000', 'somedata')
insert into tbl2 values (4078460109, '2009-01-01 07:14:06.000', 'somedata')
insert into tbl2 values (3368388714, '2009-01-01 07:15:24.000', 'somedata')
insert into tbl2 values (8502645978, '2009-01-01 07:16:03.000', 'somedata')
insert into tbl2 values (6604386862, '2009-01-01 07:17:06.000', 'somedata')
insert into tbl2 values (2525675056, '2009-01-01 07:18:54.000', 'somedata')
insert into tbl2 values (9108924863, '2009-01-01 07:19:45.000', 'somedata')
insert into tbl2 values (3527500836, '2009-01-01 07:20:24.000', 'somedata')
insert into tbl2 values (7577537788, '2009-01-01 07:21:30.000', 'somedata')
insert into tbl2 values (2392836119, '2009-01-01 07:22:33.000', 'somedata')
insert into tbl2 values (9103235418, '2009-01-01 07:23:36.000', 'somedata')
insert into tbl2 values (8166505296, '2009-01-01 07:25:57.000', 'somedata')
insert into tbl2 values (3522598367, '2009-01-01 07:26:30.000', 'somedata')
insert into tbl2 values (3524554700, '2009-01-01 07:29:39.000', 'somedata')
insert into tbl2 values (8282946168, '2009-01-01 07:30:36.000', 'somedata')
insert into tbl2 values (3362190454, '2009-01-01 07:32:15.000', 'somedata')
create CLUSTERED index aidx on tbl1(act1, t1_ts)
create CLUSTERED index aidx on tbl2(act2, T2_ts)
SELECT distinct *
FROM tbl1 A
LEFT JOIN tbl2 B
ON a.act1 = b.act2
AND A.T1_ts BETWEEN B.T2_ts AND DATEADD(S, 120, B.T2_TS)
March 26, 2009 at 12:19 pm
The execution plan I end up with on this, from the test data, is pretty efficient for what it has to do.
My first question is, do you actually query all 25-million rows at a time?
My second is, does it actually need "distinct" in there? If so, why? No human being is going to be looking at 25-million+ rows of data, and that's what distinct is usually for. Distinct is an expensive operator and can slow queries down quite a bit, so it's worth asking.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 26, 2009 at 12:20 pm
Another question. The written spec at the top of your question says 20 seconds, but the query at the bottom has 120 seconds. Which is it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 26, 2009 at 12:24 pm
Distinct probably can be removed
It is 2 minutes (120 seconds)
20 was a typo.
March 26, 2009 at 12:46 pm
That leaves are you running it on all 25-million rows regularly?
Also, what's the execution plan look like?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 26, 2009 at 12:58 pm
Ignoring your query for the moment, what is the expected results from the query based on the sample data. In other words, what are we trying to accomplish with the join of the data. I understand the the join on a range, it can result in duplicate data from the first table.
March 26, 2009 at 1:03 pm
That is very correct.
The aim here is to find the record from second table which is equal or upto 2 minutes after the timestamp on first table.
If I get one tbl1 record joined to more than one tbl2 records - my next few steps will be to find the one which is nearest and ignore the rest.
March 27, 2009 at 7:22 am
You should be able to reduce that to one step, instead of two.
For example:
SELECT *,
(select min(t2_ts)
from tbl2
where act2 = a.act1
and t2_ts between a.t1_ts and dateadd(second, 120, a.t1_ts)) as T2_ts
FROM tbl1 A;
That'll find the appropriate table2 value for each table1 value. (If I have the relationship correctly defined. Easy enough to reverse the tables if you need to do so.) It is also, based on tests on my machine, faster than the original query, even with the distinct operator removed.
Try variations on that, see if it'll do what you need. Does it help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 30, 2009 at 9:24 am
Thanks for the solution.
But I gave a simpified answer to the question earlier. The actual requirement is this
From all the records that are within 2 minutes
Try and pick the one which is nearest but at least 30 seconds away
If that does not give any - then farthest
example
2:10:10 2:10:35 2:10:40 2:11:45 2:11:50
If the rec on left matches to 4 on right.
Then first condition will give us last two and the min of the two is what I want.
If it was only two records matching
2:10:10 2:10:35 2:10:40
Nothing more than 30 sec - so I pick 2:10:40
March 30, 2009 at 9:48 am
That was not correct example
2009-01-01 05:38:00 Table 1
2009-01-01 05:36:00 Table 2
2009-01-01 05:37:00 Tbl2
2009-01-01 05:38:00 Tbl2
The Tbl1 timestamp is after and within 2 minutes of tbl2
Now I need to pick the nearest which is at least 30 sec away
So last one is ruled out because it is not at least 30 sec away.
1st and second are 30 sec away - and the nearest is second one.
if there were none that are 30 sec away then we pick the farthest of the ones that are less than 30 sec away
March 31, 2009 at 7:07 am
Try this:
SELECT *,
isnull(
(select min(t2_ts)
from tbl2
where act2 = a.act1
and t2_ts between dateadd(second, 30, a.t1_ts) and dateadd(second, 120, a.t1_ts)),
(select max(t2_ts)
from tbl2
where act2 = a.act1
and t2_ts between a.t1_ts and dateadd(second, 30, a.t1_ts))) as T2_ts
FROM tbl1 A;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 31, 2009 at 10:43 am
Thanks - I understand the approach.
Hope it will make it better as far as the run time is concerned.
March 31, 2009 at 1:58 pm
GSquared, correlated subquery is bad enough by itself, but 2 correlated subqueries in a single query - it's just a killer.
_____________
Code for TallyGenerator
March 31, 2009 at 2:04 pm
Sergiy (3/31/2009)
GSquared, correlated subquery is bad enough by itself, but 2 correlated subqueries in a single query - it's just a killer.
Oh, that's nothing. It's also got functions on both sides of the Where clause in the first one.
With the table structure given, I don't think there's a better option on this one. I tested the execution plans on it, and performance, and it's the best of the options I could think of on it.
The other way to do it would be a couple of self-joins on the table, and use of the Min and Max functions on those, and the sub-query Where clauses would become the Join arithmetic. Tried that, and it was much worse than the sub-query version. Took over 1000 times longer on about 100k rows of data.
If you have a better idea, please feel free to present it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply