Re: [firebird-support] ORDER BY too slow

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

Hiyes, slowdown is because of sort as engine must sort the table first and then 
retrieve 10 records.But you can try to speed this up by:SELECT  FIRST 10 SKIP 0 
 msg.OBJ_GUID AS "MSG_GUID", msg.PRTY, msg.TTL,  pst.OBJ_GUID AS "PST_GUID", 
pst.MSTB_DTSFROM  (Select * from MSGS m order by m.PRTY) msg  JOIN MSG_PSTS pst 
ON msg.OBJ_GUID = pst.MSG_GUID  JOIN MSG_USRS meu ON msg.OBJ_GUID = 
meu.MSG_GUID  JOIN USRS usr ON msg.USR_GUID = usr.OBJ_GUIDWHERE  meu.USR_GUID = 
'12A61B0FAE3046B6AEDEEDF6B4FE0E78'ORDER BY  msg.PRTY, pst.MSTB  Please include 
results: timing and fetching stats. I am interested self in the results. I 
suppose that this should be faster then your oryginal query.Regards,Karol 
Bieniaszewski
null

[firebird-support] ORDER BY too slow

[firebirdbest...@yahoo.com [firebird-support]]

I'm building a major product using Lazarus 1.8.4, FPC 3.0.4, IBX 2.3.3 and 
Firebird RDBMS 3x.  I have a query that runs in the range of 1 to 3 
milliseconds without the ORDER BY clause.  But once I add the ORDER BY clause, 
the query takes > 1.5 seconds.  Be advised that there is an index on the 
MSGS.PRTY field and an index on the MSG_PSTS.MSTB field.  The PLAN refuses to 
use either index.  Here is the query SQL:
 

 [code=pascal]
 commit;
 SELECT
   FIRST 10 SKIP 0
   msg.OBJ_GUID AS "MSG_GUID", msg.PRTY, msg.TTL,
   pst.OBJ_GUID AS "PST_GUID", pst.MSTB_DTS
 FROM
   MSGS msg
   JOIN MSG_PSTS pst ON msg.OBJ_GUID = pst.MSG_GUID
   JOIN MSG_USRS meu ON msg.OBJ_GUID = meu.MSG_GUID
   JOIN USRS usr ON msg.USR_GUID = usr.OBJ_GUID
 WHERE
   meu.USR_GUID = '12A61B0FAE3046B6AEDEEDF6B4FE0E78'
 ORDER BY
   msg.PRTY, pst.MSTB  
 [/code]
 

 I almost wish I could create an index on multiple tables somehow.  How can I 
speed this up?
 

 Here is the PLAN:
 

 [code=pascal]
 

 Select Expression
 -> First N Records
 -> Skip N Records
 -> Sort (record length: 444, key length: 20)
 -> Nested Loop Join (inner)
 -> Filter
 -> Table "MSG_USRS" as "MEU" Access By ID
 -> Bitmap
 -> Index "FK_MSG_USRS_1" Range Scan (full 
match)
 -> Filter
 -> Table "MSGS" as "MSG" Access By ID
 -> Bitmap
 -> Index "PK_MSGS" Unique Scan
 -> Filter
 -> Table "USRS" as "USR" Access By ID
 -> Bitmap
 -> Index "PK_USRS" Unique Scan
 -> Filter
 -> Table "MSG_PSTS" as "PST" Access By ID
 -> Bitmap
 -> Index "FK_MSG_PSTS_0" Range Scan (full 
match)
 [/code]
 

 The "Sort" appearing here is the problem.  I need it to go away and get 
Firebird to use the indexes provided.
 

 
 Thanks for any help you can provide.