I have a table named "TEST" dt=Date, Num = Int. 17.664 Rows.

I tried the following in the EDB_Manager:

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


SQL Query (Executed by ElevateDB 2.03 Build 17)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.
================================================================================

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

Source Tables
-------------

Test (t1): 17664 rows
Test (t2): 17664 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows

Joins
-----

The driver table was the Test (t1) table

The Test (t1) table was joined to the Test (t2) table with the inner join
expression:

"t2"."dt" = "t1"."dt" AND "t2"."Num" > "t1"."Num"

The optimizer attempted to re-order the joins to a more optimal order

The joins were already in the most optimal order

The following join condition was applied to the Test (t2) table:

"t2"."Num" > "t1"."Num" AND "t2"."dt" = "t1"."dt"

Row scan (test)

Hint: Create index (test) on column "t2"."Num" for possible better performance

Hint: Create index (test) on column "t2"."dt" for possible better performance


Result set I/O statistics
-------------------------

Total rows visited: 88320

Row buffer manager

Max buffer size: 32760 Buffer size: 47040

Hits: 263784   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 1   Bytes written: 47040

Index Page buffer manager

Max buffer size: 65536 Buffer size: 81920

Hits: 533618   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 289   Bytes written: 1261568

================================================================================
1176 row(s) returned in 361,267 secs  <==========
================================================================================


Hint: Create index (test) on column "t2"."Num" for possible better performance

Hint: Create index (test) on column "t2"."dt" for possible better performance

How can I do that and increase the speed?

TIA

Dieter Nagy

Dieter


Go into EDBManager, select the table, select indexes, select create new index, fill in the blanks.

Roy Lambert

Thanks Roy, I was confuse, because I can't found the "t2". Now it works.

Dieter Nagy







Dieter Nagy wrote:

I have a table named "TEST" dt=Date, Num = Int. 17.664 Rows.

I tried the following in the EDB_Manager:

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


SQL Query (Executed by ElevateDB 2.03 Build 17)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.
================================================================================

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

Source Tables
-------------

Test (t1): 17664 rows
Test (t2): 17664 rows

Result Set
----------

The result set was insensitive and read-only
The result set consisted of zero or more rows

Joins
-----

The driver table was the Test (t1) table

The Test (t1) table was joined to the Test (t2) table with the inner join
expression:

"t2"."dt" = "t1"."dt" AND "t2"."Num" > "t1"."Num"

The optimizer attempted to re-order the joins to a more optimal order

The joins were already in the most optimal order

The following join condition was applied to the Test (t2) table:

"t2"."Num" > "t1"."Num" AND "t2"."dt" = "t1"."dt"

Row scan (test)

Hint: Create index (test) on column "t2"."Num" for possible better performance

Hint: Create index (test) on column "t2"."dt" for possible better performance


Result set I/O statistics
-------------------------

Total rows visited: 88320

Row buffer manager

Max buffer size: 32760 Buffer size: 47040

Hits: 263784   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 1   Bytes written: 47040

Index Page buffer manager

Max buffer size: 65536 Buffer size: 81920

Hits: 533618   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 289   Bytes written: 1261568

================================================================================
1176 row(s) returned in 361,267 secs  <==========
================================================================================


Hint: Create index (test) on column "t2"."Num" for possible better performance

Hint: Create index (test) on column "t2"."dt" for possible better performance

How can I do that and increase the speed?

TIA

Dieter Nagy

Is there another way to inccrease the speed?
Index on dt and num created.




With 1 Join:

Hits: 180112   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 1   Bytes written: 73728

================================================================================
1176 row(s) returned in 37,924 secs
================================================================================


With 2 Joins:

Hits: 530524   Misses: 0   Hit ratio: 1

Reads: 0   Bytes read: 0

Writes: 8   Bytes written: 1318912

================================================================================
14756 row(s) returned in 147,218 secs
================================================================================





With 3 Joins:


Hits: 2570497   Misses: 7   Hit ratio: 1

Reads: 7   Bytes read: 28672

Writes: 344   Bytes written: 5586944

================================================================================
21006 row(s) returned in 386,96 secs
================================================================================


TIA
Dieter Nagy

Dieter


What is the actual point of the query? I can see you're doing a self join but I can't figure out why. It may be there's a different way of achieving the end result that's a lot more efficient.

Roy Lambert [Team Elevate]

Roy

I try to get the numbers who repeats with the same date.
For example: 1 + 2  count = 36
                   ......
                   20 + 34  count = 121  and so on.

Thanks
Dieter




Roy Lambert wrote:

Dieter


What is the actual point of the query? I can see you're doing a self join but I can't figure out why. It may be there's a different way of achieving the end result that's a lot more efficient.

Roy Lambert [Team Elevate]

Dieter


I thought that might be in. In which case (unkess I'm missing something) there's no need for the JOIN. eg

select COUNT(*), _Surname, _Forename FROM Contacts
GROUP BY _Surname, _Forename
HAVING COUNT(*) > 1

If I understand your query correctly try

select COUNT(*), dt , Num FROM Test
GROUP BY dt, Num
HAVING COUNT(*) > 1


Roy Lambert

Roy

Thanks for your answer.
It's not so easy.

The table test:

dt= 2006-01-01 num = 1
dt= 2006-01-01 num = 21
dt= 2006-01-01 num = 15  <===
dt= 2006-01-01 num = 16
dt= 2006-01-01 num = 3    <===
.....
dt= 2007-01-02num = 11
dt= 2007-01-02 num = 3    <===
dt= 2007-01-02 num = 15  <===
dt= 2007-01-02 num = 16
dt= 2007-01-02 num = 22
........
The result should be: 3  15 = 2x
and so on......

Thanks
Dieter










Roy Lambert wrote:

Dieter


I thought that might be in. In which case (unkess I'm missing something) there's no need for the JOIN. eg

select COUNT(*), _Surname, _Forename FROM Contacts
GROUP BY _Surname, _Forename
HAVING COUNT(*) > 1

If I understand your query correctly try

select COUNT(*), dt , Num FROM Test
GROUP BY dt, Num
HAVING COUNT(*) > 1


Roy Lambert

Dieter


Sorry, but that baffles me totally.

I just don't understand what the <== bits are about. If all you're saying is the date doesn't matter just leave it out of the GROUP BY, in fact leave it out of the query entirely.

If that's not it can you post the catalog and table to the binaries ng please so I can have a look.

Roy Lambert [Team Elevate]

Roy

Thanks for your help.
I see I have to change the layout of my tables.

Thanks
Dieter Nagy






Roy Lambert wrote:

Dieter


Sorry, but that baffles me totally.

I just don't understand what the <== bits are about. If all you're saying is the date doesn't matter just leave it out of the GROUP BY, in fact leave it out of the query entirely.

If that's not it can you post the catalog and table to the binaries ng please so I can have a look.

Roy Lambert [Team Elevate]