| Subject | Re: Select question |
|---|---|
| Author | Adam |
| Post date | 2004-12-08T12:52:02Z |
Rob,
I wouldn't imagine so, modern CPUs are pretty quick at adding numbers
together. Your biggest performance bottlenecks tend to be hard disk
speed and bus speed for these sorts of things.
What you may want to consider though is having some sort of "needed"
table, that can be automatically manipulated using triggers on the
events of the other tables. For example, increasing the shelf
quantity could reduce the needed quantity of an item. If you only
have 100 items, it probably isn't worth your while, but if you have
thousands of items that are frequently bought and sold, and you need
to check the needed amount frequently, that is another option for you.
All you are actually doing is moving the addition out of the select
and into the insert / update / delete times. It will depend on your
data shape as to the best solution. If the query is quick enough for
the select, I wouldn't bother with moving it to a trigger filled
table.
Adam
--- In firebird-support@yahoogroups.com, "Robert martin" <rob@c...>
wrote:
I wouldn't imagine so, modern CPUs are pretty quick at adding numbers
together. Your biggest performance bottlenecks tend to be hard disk
speed and bus speed for these sorts of things.
What you may want to consider though is having some sort of "needed"
table, that can be automatically manipulated using triggers on the
events of the other tables. For example, increasing the shelf
quantity could reduce the needed quantity of an item. If you only
have 100 items, it probably isn't worth your while, but if you have
thousands of items that are frequently bought and sold, and you need
to check the needed amount frequently, that is another option for you.
All you are actually doing is moving the addition out of the select
and into the insert / update / delete times. It will depend on your
data shape as to the best solution. If the query is quick enough for
the select, I wouldn't bother with moving it to a trigger filled
table.
Adam
--- In firebird-support@yahoogroups.com, "Robert martin" <rob@c...>
wrote:
> Thanks Thomasgreat!
>
> That works like a charm !
> I did not know that you could do Case statements in SQL, it's
>performance hit in doing this sort of thing?
> Just out of interest. Does anybody know if there much of a
>untested as
> Rob Martin
> Software Engineer
>
> phone +64 03 377 0495
> fax +64 03 377 0496
> web www.chreos.com
> Wild Software Ltd
> ----- Original Message -----
> From: Thomas Clarke
> To: firebird-support@yahoogroups.com
> Sent: Wednesday, December 08, 2004 4:18 PM
> Subject: RE: [firebird-support] Select question
>
>
> Rob,
>
> If my sql is right then something like this should work. It is
> it is late at night here so don't take my word for it.sl.CustOrdQty) >= 0
>
> select
> case
> when sl.maxStock - (sl.ShelfQty + sl.SupOrdrQty -
> then sl.maxStock - (sl.ShelfQty + sl.SupOrdrQty - sl.CustOrdQty)
> else 0
> end AS Needed
> ...
>
> Thomas Clarke.
>
> -----Original Message-----
> From: Robert martin [mailto:rob@c...]
> Sent: Tuesday, December 07, 2004 11:09 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Select question
>
>
>
> Hi
>
> [Non-text portions of this message have been removed]