| Subject | Re: [firebird-support] Another ordering question? |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2015-09-09T16:34:52Z |
Try
with tmp1(MYKEY1) as
(SELECT MIN(MYKEY1) FROM MYTAB),
tmp2(MYKEY1, MYKEY2) as
(SELECT t.MYKEY1, MIN(mt.MYKEY2)
FROM tmp1 t
JOIN MYTAB mt ON t.MYKEY1 = mt.MYKEY1
GROUP BY 1)
SELECT mt2.<several columns> FROM tmp2 t2
JOIN MYTAB mt2 ON t2.MYKEY1 = mt2.MYKEY1 AND t2.MYKEY2 = mt2.MYKEY2
and see if that gets the same or a different disk read (I don't know, I'm just curious).
Set
2015-09-09 14:00 GMT+02:00 Tim Ward tdw@... [firebird-support] <firebird-support@yahoogroups.com>:
SELECT FIRST 1 <several columns> FROM MYTAB ORDER BY MYKEY1, MYKEY2;
PLAN (MYTAB ORDER PK_MYTAB)
Current memory = 3437420
Delta memory = 152
Max memory = 3586952
Elapsed time= 0.04 sec
Buffers = 150
Reads = 232
Writes 0
Fetches = 132082
There may be several thousand records in the table, but the primary key
is defined as (MYKEY1, MYKEY2) and the plan says it's using it, so why
the 232 disk reads? - one might have guessed 2 would be enough, one for
the index and one for the record. (The key fields are both 32 bit
integers. Yes, I do have plans to replace them by a generator-generated
key, but I can't see why that would make a difference to this particular
performance issue.)
This table is being used as a FIFO queue. Would the most likely
explanation be lack of garbage collection, so it's having to scan lots
of deleted records before finding the first real one, or are there other
possibilities? I must say that
Oldest transaction 390433636
Oldest active 390433637
Oldest snapshot 390433637
Next transaction 391092651
doesn't look too encouraging, so I'd better investigate that.
--
Tim Ward
------------------------------------
Posted by: Tim Ward <tdw@...>
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-digest@yahoogroups.com
firebird-support-fullfeatured@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/