A more efficient query

I am trying to add and subtract a few fields in a table to determine return
on investment
Currently i'm performing this as follows:
SELECT
(ColumnCost1 + ColumnCost2) as Cost,
(ColumnRevenue1 + ColumnRevenue2) as Revenue,
((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2)) as
ReturnOnInvestment
FROM
TableName
Is there a more efficient way of doing this, i am calling the same
calcutions twice so it seems there must be.
I tried setting variables to the costs and revenues, but multiple results
are being returned so this proved difficult.
Any help would be appreciated, thanks
You could try a derived table, but I seriously doubt you'll see any
performance increase:
SELECT
Cost, Revenue
(Revenue - Cost) as ReturnOnInvestment
FROM
(SELECT
(ColumnCost1 + ColumnCost2) as Cost,
(ColumnRevenue1 + ColumnRevenue2) as Revenue
FROM TableName) x(Cost, Revenue)
"GrantMagic" <grant@.magicalia.com> wrote in message
news:OM9DXmckEHA.3340@.TK2MSFTNGP14.phx.gbl...
> I am trying to add and subtract a few fields in a table to determine
return
> on investment
> Currently i'm performing this as follows:
> SELECT
> (ColumnCost1 + ColumnCost2) as Cost,
> (ColumnRevenue1 + ColumnRevenue2) as Revenue,
> ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2))
as
> ReturnOnInvestment
> FROM
> TableName
> Is there a more efficient way of doing this, i am calling the same
> calcutions twice so it seems there must be.
> I tried setting variables to the costs and revenues, but multiple results
> are being returned so this proved difficult.
>
> Any help would be appreciated, thanks
>
|||Yeah, i tested the two methods against each other and there is no difference
between the two
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%238hGNqckEHA.3988@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> You could try a derived table, but I seriously doubt you'll see any
> performance increase:
>
> SELECT
> Cost, Revenue
> (Revenue - Cost) as ReturnOnInvestment
> FROM
> (SELECT
> (ColumnCost1 + ColumnCost2) as Cost,
> (ColumnRevenue1 + ColumnRevenue2) as Revenue
> FROM TableName) x(Cost, Revenue)
>
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:OM9DXmckEHA.3340@.TK2MSFTNGP14.phx.gbl...
> return
ColumnCost2))[vbcol=seagreen]
> as
results
>
|||You could try creating computed columns and index them. That might be quite
a bit faster...
ALTER TABLE TableName
ADD Cost AS (ColumnCost1 + ColumnCost2)
ALTER TABLE TableName
ADD Revenue AS (ColumnRevenue1 + ColumnRevenue2)
ALTER TABLE TableName
ADD ReturnOnInvestment AS
((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2))
CREATE INDEX IX_Cost_Revenue ON TableName (Cost, Revenue,
ReturnOnInvestment)
-- You should probably try to make this into a covering index, with the
rest of the columns in your real query
"GrantMagic" <grant@.magicalia.com> wrote in message
news:uhlLd6ckEHA.2848@.TK2MSFTNGP15.phx.gbl...
> Yeah, i tested the two methods against each other and there is no
difference
> between the two
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%238hGNqckEHA.3988@.TK2MSFTNGP14.phx.gbl...
> ColumnCost2))
> results
>
|||Thanks, i will give that a try.
Would i need to drop those columns after my query, or only create them once?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eBh6oPdkEHA.3356@.TK2MSFTNGP15.phx.gbl...
> You could try creating computed columns and index them. That might be
quite
> a bit faster...
> ALTER TABLE TableName
> ADD Cost AS (ColumnCost1 + ColumnCost2)
> ALTER TABLE TableName
> ADD Revenue AS (ColumnRevenue1 + ColumnRevenue2)
> ALTER TABLE TableName
> ADD ReturnOnInvestment AS
> ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2))
> CREATE INDEX IX_Cost_Revenue ON TableName (Cost, Revenue,
> ReturnOnInvestment)
> -- You should probably try to make this into a covering index, with the
> rest of the columns in your real query
> "GrantMagic" <grant@.magicalia.com> wrote in message
> news:uhlLd6ckEHA.2848@.TK2MSFTNGP15.phx.gbl...
> difference
>
|||"GrantMagic" <grant@.magicalia.com> wrote in message
news:uD9r5idkEHA.556@.tk2msftngp13.phx.gbl...
> Thanks, i will give that a try.
> Would i need to drop those columns after my query, or only create them
once?
Only once, they'll be columns in your table after that, just like any
other column (except you won't be able to update them; they'll be
automatically computed when you insert or update the other columns)
|||GrantMagic,
These calculations are so basic and highly optimized for any CPU, that
there will be no way to create any significant performance gain by
rewriting the statement. The current cost of the calculation part is
simply too low (in comparison with I/O, network speed, logical reads,
etc.)
Gert-Jan
GrantMagic wrote:
> I am trying to add and subtract a few fields in a table to determine return
> on investment
> Currently i'm performing this as follows:
> SELECT
> (ColumnCost1 + ColumnCost2) as Cost,
> (ColumnRevenue1 + ColumnRevenue2) as Revenue,
> ((ColumnRevenue1 + ColumnRevenue2) - (ColumnCost1 + ColumnCost2)) as
> ReturnOnInvestment
> FROM
> TableName
> Is there a more efficient way of doing this, i am calling the same
> calcutions twice so it seems there must be.
> I tried setting variables to the costs and revenues, but multiple results
> are being returned so this proved difficult.
> Any help would be appreciated, thanks
(Please reply only to the newsgroup)