Roy,

<< My rate for that is £1000.01 / hour plus expenses >>

What's the .01 for ?  Besides, you should charge £999.99/hour, it will look
cheaper.

--
Tim Young
Elevate Software
www.elevatesoft.com

Charles


Grovels in admiration. I think you have both the purpose correct and the solution. However, I'll wait for Dieter to confirm

The result set from your "horrible" query whilst it takes a bit longer than Dieter's is far more believable.


Roy Lambert [Team Elevate]

Tim

>What's the .01 for ? Besides, you should charge £999.99/hour, it will look
>cheaper.

That's because I'm going to insist on being paid in cash for the first hour and I need the change to go to the loo.

Roy Lambert

Dieter,

In addition to adding the indexes, add a JOINOPTIMIZECOSTS clause to the
query, and that should speed things up a bit:

SELECT      t1.Num Num1,
     t2.Num Num2,
     COUNT(*) cnt
FROM Test t1
INNER JOIN     Test t2 ON t2.dt  = t1.dt AND t2.Num > t1.Num
GROUP BY   t1.Num,
     t2.Num
HAVING      COUNT(*) > 1
ORDER BY   cnt DESC,
     t1.Num,
     t2.Num
JOINOPTIMIZECOSTS

This will cause EDB to do a row scan on the > condition, using the =
condition as input.

JOINOPTIMIZECOSTS will become the standard behavior in 2.04 and above.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


As near as I can tell knocked about 4 seconds of on my machine

Roy Lambert

Charles

Thanks for your help. Yes I have imported the spreadsheet twice. I will try it with a lower number of rows in the next days for checking it by hand.

Dieter Nagy







Charles Tyson wrote:

On brief inspection it looks like every date has 12 numbers, but they
are always the same set of 6 numbers repeated twice.  I wonder if that
is an accident (spreadsheet data imported twice?).  From the
distribution of numbers I guess this may be a lotto database.

From Dieter's description I assume he wants to to take every 2-value
combination (6 numbers => 5+4+3+2+1 = 15 combinations per day) and see
how many times that combo appears on any other day.

If I were doing it, I would probably do this simpler query and pass the
results to my report writer to get the count:

SELECT distinct t1.Num Num1, t2.Num Num2, dt
FROM Test t1
INNER JOIN Test t2 ON t2.dt = t1.dt AND t2.Num > t1.Num
order by num1, num2, dt

(Given the test table this finds 22,080 rows)

It may be possible to do the count in SQL--the following horrible
statement seems to work.  It uses the above query as a subquery to
supply data to the main SELECT FROM clause.  Warning, it will appear to
lock up EDBManager when you "prepare" it, but that is "normal"--give it
40 to 60 seconds.

select t3.num1, t3.num2, count(t3.dt)
from
(
SELECT distinct t1.Num Num1, t2.Num Num2, dt
FROM Test t1
INNER JOIN Test t2 ON t2.dt = t1.dt AND t2.Num > t1.Num
) t3
group by num1, num2

(finds 1,176 rows, which is exactly the number of combinations for 49
items taken 2 at a time--so every combination was used at least once)

P.S. I wouldn't trust any numbers produced by my queries without
checking them by hand!

Roy

many thanks for your help!

Yes, Charles has the  solution. I will try it in the next days with a lower number of rows.
Once again many thanks for your help!

Dieter Nagy




Roy Lambert wrote:

Charles


Grovels in admiration. I think you have both the purpose correct and the solution. However, I'll wait for Dieter to confirm

The result set from your "horrible" query whilst it takes a bit longer than Dieter's is far more believable.


Roy Lambert [Team Elevate]

Tim,

Thanks for your hint. The  new time for the first query is now 19 sec.(31 before).

Thanks.

Dieter Nagy





"Tim Young [Elevate Software]" wrote:

Dieter,

In addition to adding the indexes, add a JOINOPTIMIZECOSTS clause to the
query, and that should speed things up a bit:

SELECT      t1.Num Num1,
     t2.Num Num2,
     COUNT(*) cnt
FROM Test t1
INNER JOIN     Test t2 ON t2.dt  = t1.dt AND t2.Num > t1.Num
GROUP BY   t1.Num,
     t2.Num
HAVING      COUNT(*) > 1
ORDER BY   cnt DESC,
     t1.Num,
     t2.Num
JOINOPTIMIZECOSTS

This will cause EDB to do a row scan on the > condition, using the =
condition as input.

JOINOPTIMIZECOSTS will become the standard behavior in 2.04 and above.

--
Tim Young
Elevate Software
www.elevatesoft.com

Dieter

Sorry if I appeared to be rude...   I just could not understand the problem
(unlike Charles <bg>).

If these queries are being run frequently for pairs, triples, quads etc and
need to run fast I would change the table structure.

Pairs Table
Date,Num1,Num2

Triples Table
Date,Num1,Num2,Num3

etc


To populate pairs table from test table

insert into pairs select distinct dt,num,t2.num from test
join test t2 on test.dt=t2.dt and test.num<t2.num


To query pairs table

select num1,num2,count(*) as cnt
from pairs
group by num1,num2

John

John

It's no problem for me and i know that my english is not the best

Many thanks for your tip!


Dieter Nagy


"John Hay" wrote:

Dieter

Sorry if I appeared to be rude...   I just could not understand the problem
(unlike Charles <bg>).

If these queries are being run frequently for pairs, triples, quads etc and
need to run fast I would change the table structure.

Pairs Table
Date,Num1,Num2

Triples Table
Date,Num1,Num2,Num3

etc


To populate pairs table from test table

insert into pairs select distinct dt,num,t2.num from test
join test t2 on test.dt=t2.dt and test.num<t2.num


To query pairs table

select num1,num2,count(*) as cnt
from pairs
group by num1,num2

John