星期二 2007年04月24日 a strang derby optimize
There is table MyTable, it has a primary key generatedId defined as:generatedId integer not null generated always as identityHere are two sql queries:
1. select min(generatedId),max(generatedId),max(generatedId) - min(generatedId) from MyTable
2. select minId,maxId, maxId - minId from
(select min(generatedId) minId from MyTable) minT ,
(select max(generatedId) maxId from MyTable) maxT
They will return the same result, but could you guess which one has the better performance?
It's the second one. When the table has 1296607 row, the times for the first sql is 43.8s.
But second sql will finished in 8s.
I try select min(generatedId),max(generatedId) from MyTable then. It needs nearly 43s too. And then select count(*) from MyTable finished in less then 40s.
So what does this mean?
I guess derby optimize select min(generatedId) minId from MyTable and select max(generatedId) minId from MyTable but not optimize sql queries like select min(generatedId),max(generatedId) from MyTable.
Technorati Tags: javadb, derby
( 2007年04月24日, 04:02:01 下午 CST ) [Listen] Permalink 评论 [1]
Share and enjoy:
I'm amazed it's taking that long in the first place. If the table is indexed then MIN() and MAX() should run in constant time.
发表于 空っぽ 在 2008年02月08日, 06:45 上午 CST #