[firebird-support] Speed of comparing one field to another

[Maya Opperman [e-mail] [firebird-support]]

Hi,

I have a large table, and I'm only extracting a few records using:

Select t.ID
From my_table t
Where t.Invoiced < t.Received

Invoice and received are of type decimal(18,5)

IBExpert is reporting these as unindexed reads.

Is there an index I could create to speed up the loading?

Thanks in advance
Maya

[Dimitry Sibiryakov [e-mail] [firebird-support]]

Post by Maya Opperman ***@omniaccounts.co.za [firebird-support]
Is there an index I could create to speed up the loading?

No.
--
WBR, SD.


------------------------------------

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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-***@yahoogroups.com
firebird-support-***@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-***@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[Louis Kleiman [e-mail] [firebird-support]]

You could create a denormalized field that contains the difference of those
values (updated by a trigger, perhaps), and you can put an index on that
new field. Then change the query to find rows where [new difference field]
< 0.

Make sense?

Post by Dimitry Sibiryakov ***@ibphoenix.com [firebird-support]

Post by Maya Opperman ***@omniaccounts.co.za [firebird-support]
Is there an index I could create to speed up the loading?

No.
--
WBR, SD.
------------------------------------
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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

[Maya Opperman [e-mail] [firebird-support]]

You could create a denormalized field that contains the difference of those values (updated by a trigger, perhaps), and you can put
an index on that new field. Then change the query to find rows where [new difference field] < 0.
Make sense?

Thank you, looks like I will need to go that route

['Walter R. Ojeda Valiente' [e-mail] [firebird-support]]

Yes, you can, using an expression index

CREATE INDEX IDX_MYINDEX ON MY_TABLE COMPUTED BY (INVOICED - RECEIVED);

and your SELECT would be:

SELECT
*
FROM
MY_TABLE
WHERE
INVOICED - RECEIVED < 0

That way you will not need to create an additional column neither to do an
UPDATE neither to create a trigger.

Greetings.

Walter.

Post by Maya Opperman ***@omniaccounts.co.za [firebird-support]
Hi,
Select t.ID
From my_table t
Where t.Invoiced < t.Received
Invoice and received are of type decimal(18,5)
IBExpert is reporting these as unindexed reads.
Is there an index I could create to speed up the loading?
Thanks in advance
Maya