Tim,

I have some serious performance problems with grouping and plain equi-joins
on medium sized tables.

I'm attaching a zip with a script to create the database and a set of
queries (only 8 kb). Here's a couple of examples:

a) Grouping

/*
  Q1: Find the weighted filter ratio on Customers Phone_Number
  A1: 0.000003
*/

select
 Phone_Number,
 cast(count(Phone_Number) as float) count_Phone_Number,
 cast(count(*) as float) count_all
into memory\temp
from Customers
group by Phone_Number;

select
 (sum(count_Phone_Number*count_Phone_Number))/
 (sum(count_Phone_Number)*sum(count_all)) A1
from memory\temp

Execution time: 141 seconds

2) Equi-join

/*
  Q9: Find the rowcount in the join Shipments->Order_Details
  A9: 1,080,000

  Detail join ratio: 2.0 ( 1080000(A9)/540000(A8) )
  Master join ratio: 0.9 ( 1080000(A9)/1200000(A6) )
*/

select count(*) A9
from Shipments S,Order_Details OD
where OD.Shipment_ID = S.Shipment_ID

Execution time: 505 seconds

The queries were executed in DBSYS using a localhost C/S connection.

Ole

Hi Ole,

Yes, this is a strange behavior. Group by takes 213 secs for me (500000 recs),
however, just selecting the phone numbers takes only 14 secs. I believe
that 14 secs should also be high estimate for groupping if the field
is indexed (it is), because you need to traverse same number of records,
but copy fewer records. BTW, this doesn't work with 4.19 at all, I guess
there was a bug in it ;(.

This is, also, funny: this query wouldn't run in 4.19, reporting
something's missing in group by:
select
  cast(count(*) as float) count_all
from Customers

4.22 runs it, but it takes 4 secs while simple count(*) is
instantaneous. How can it possibly take more time? It's a definite
glitch in sql optimization.


Regards,
Michael

Ole Willy Tuv wrote:
> Tim,
>
> I have some serious performance problems with grouping and plain equi-joins
> on medium sized tables.
>
> I'm attaching a zip with a script to create the database and a set of
> queries (only 8 kb). Here's a couple of examples:
>
> a) Grouping
>
> /*
>    Q1: Find the weighted filter ratio on Customers Phone_Number
>    A1: 0.000003
> */
>
> select
>   Phone_Number,
>   cast(count(Phone_Number) as float) count_Phone_Number,
>   cast(count(*) as float) count_all
> into memory\temp
> from Customers
> group by Phone_Number;
>
> select
>   (sum(count_Phone_Number*count_Phone_Number))/
>   (sum(count_Phone_Number)*sum(count_all)) A1
> from memory\temp
>
> Execution time: 141 seconds
>
> 2) Equi-join
>
> /*
>    Q9: Find the rowcount in the join Shipments->Order_Details
>    A9: 1,080,000
>
>    Detail join ratio: 2.0 ( 1080000(A9)/540000(A8) )
>    Master join ratio: 0.9 ( 1080000(A9)/1200000(A6) )
> */
>
> select count(*) A9
> from Shipments S,Order_Details OD
> where OD.Shipment_ID = S.Shipment_ID
>
> Execution time: 505 seconds
>
> The queries were executed in DBSYS using a localhost C/S connection.
>
> Ole
>
>

Hi Michael,

Thanks for the feedback.

Did you also run the second query (AppendixB_Q9.sql). If so, what execution
time did you get on your system ?

Regards
Ole

> Did you also run the second query (AppendixB_Q9.sql). If so, what execution
> time did you get on your system ?
No, I wasn't patient enough to wait for it to complete yesterday
Today it took 375 sec. I tried replacing where with join, but it
didn't help any (487 sec). This is very strange providing
select * from Shipments S takes only 5 secs and the field
Shipment_ID is indexed.

I also had problems running your sql_tuning_testdata.sql script. I had to
run it in portions, cause some inserts there crashed the server a couple
of times (first I tried with 4.19 and then when I figured it won't work
I switched to 4.22 and still crashed it but was able to finish). I didn't
make note about error messages (I suspect AV in dbsrvr, dropped connection
and then dbsys was not able to reconnect - which was the error I saw on
screen).


Michael

P.S. The PC is AMD Athlon 64 3000, 2.2 Ghz, 512 Mb, XP/SP2.

Michael,

<< Today it took 375 sec. I tried replacing where with join, but it didn't
help any (487 sec). This is very strange providing select * from Shipments S
takes only 5 secs and the field Shipment_ID is indexed. >>

Yes, the join is dead slow.

<< I also had problems running your sql_tuning_testdata.sql script. I had to
run it in portions, cause some inserts there crashed the server a couple of
times (first I tried with 4.19 and then when I figured it won't work I
switched to 4.22 and still crashed it but was able to finish). >>

The database script (sql_tuning_testdata.sql) runs fine without any errors
here on 4.22 b6. It's pretty slow though, 1418 seconds here, which is
probably caused by slow joined queries in the insert statements.

Ole

Ole,

<< I have some serious performance problems with grouping and plain
equi-joins on medium sized tables. >>

I will take a look at this this weekend (I'm out of town right now for
personal family reasons so the situation isn't particularly conducive to
testing something like this).

--
Tim Young
Elevate Software
www.elevatesoft.com

> The database script (sql_tuning_testdata.sql) runs fine without any errors
> here on 4.22 b6.
I was running 4.22 b1. I guess I should download the latest build...


Michael

Tim,

<< I will take a look at this this weekend (I'm out of town right now for
personal family reasons so the situation isn't particularly conducive to
testing something like this). >>

No need to rush it. Thanks for looking into it.

Ole

Hi Ole,

Actually, the reason I tried your test is because I was curious
how did you managed to create large tables from only 8 kb of
sql source. I expected it to be a carthesian join and so it was.
Do you know of any other method?

Carthesian joins are very tricky business, and I think every database
server developer should always keep it in mind, because it is
so very easy to put a server down with just one line expression
For example:
select count(*) from
  d d1, d d2, d d3, d d4, d d5, d d6, d d7, d d8
(d table contains 10 records, so the result is 10 power 7).

This actually *takes time* on MSSQL2000. A few secs. 10 tables
take some more time - which means it is actually trying to
iterate this thing (why?)!
7 tables take DBISAM 45 sec! 8 tables will probably take a few
minutes. 10 tables - I doubt it will handle 10 power 10 records

However, MSSQL is smart!, so
select * from
  d d1, d d2, d d3, d d4, d d5, d d6, d d7, d d8
takes NO TIME. I wouldn't try running this on dbisam .

There could be some legitimate tasks when you need to use those...

I played around a bit with MSSQL 2000 and was able to get it
busy for a *very* long time doing nothing . I think one
can crash the server joining 10 or more tables...

I have to admit, that this has amazed me quite a bit - somehow
I used to think, that those guys who code optimizers actually
realize that they're working with "functional" language and
so they must try to understand the request rather then merely
execute it. It'd seem pretty logical to me, if they'd prohibit
you from demanding a result sets larger then certain limits, especially
if it requires some sorting (after all they can predict execution time).

So finally , the question I wanted to ask you was: do you know
of any database that is actually smart enough to "understand"
how to execute something like this?

select
  (d1.a * 10000000 + d2.a * 1000000 + d3.a * 100000 + d4.a * 10000 +
  d5.a * 1000 + d6.a * 100 + d7.a * 10 + d8.a) as a
from
  d d1, d d2, d d3, d d4, d d5, d d6, d d7, d d8
order by a

MSSQL doesn't fail, but it takes very long time.

This is by far not the most complex thing you can write with SQL.
I wonder how they handle more complex things?


Michael



Ole Willy Tuv wrote:
> Tim,
>
> I have some serious performance problems with grouping and plain equi-joins
> on medium sized tables.
>
> I'm attaching a zip with a script to create the database and a set of
> queries (only 8 kb). Here's a couple of examples:
>
> a) Grouping
>
> /*
>    Q1: Find the weighted filter ratio on Customers Phone_Number
>    A1: 0.000003
> */
>
> select
>   Phone_Number,
>   cast(count(Phone_Number) as float) count_Phone_Number,
>   cast(count(*) as float) count_all
> into memory\temp
> from Customers
> group by Phone_Number;
>
> select
>   (sum(count_Phone_Number*count_Phone_Number))/
>   (sum(count_Phone_Number)*sum(count_all)) A1
> from memory\temp
>
> Execution time: 141 seconds
>
> 2) Equi-join
>
> /*
>    Q9: Find the rowcount in the join Shipments->Order_Details
>    A9: 1,080,000
>
>    Detail join ratio: 2.0 ( 1080000(A9)/540000(A8) )
>    Master join ratio: 0.9 ( 1080000(A9)/1200000(A6) )
> */
>
> select count(*) A9
> from Shipments S,Order_Details OD
> where OD.Shipment_ID = S.Shipment_ID
>
> Execution time: 505 seconds
>
> The queries were executed in DBSYS using a localhost C/S connection.
>
> Ole
>
>

Michael,

<< Actually, the reason I tried your test is because I was curious how did
you managed to create large tables from only 8 kb of sql source. I expected
it to be a carthesian join and so it was. Do you know of any other method?
>>

No, sorry I don't. I didn't develop the script myself. The script was a
companion to a text book on SQL tuning, and I revised the SQL Server version
to a few of the database engines I have here.

<< So finally , the question I wanted to ask you was: do you know of any
database that is actually smart enough to "understand" how to execute
something like this? >>

No, I haven't played with cartesian joins, other than the sql_tuning
database script.

Ole