abnormal message when performing restore on 2005

Hi!
I have performed a full, differential and translog backup of my database.
Now when I try to restore the full backup it I get this message
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "userdb" has not been backed up. Use
BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not
want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I then used this command
restore database userdb from disk='X:\backups\userfulldev.bak' with replace
and it restored the information correctly...
But im a bit curious exactly what I did wrong. Usually I never have to use
the "with replace" option when I restore my databases...
your thoughts?
/henrikThat is a new "error" message designed to keep you from erasing potentially
useful data. It applies when the following conditions are al true
You are restoring over an existing database
The database is in full recovery mode
There are active log segments (segments that have not been backed up).
The assumption is that the database contains useful transactions that must
be preserved.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Henrik Nordgren" <HenrikNordgren@.discussions.microsoft.com> wrote in
message news:84A018FB-0A32-4991-BC94-BD875EAB553B@.microsoft.com...
> Hi!
> I have performed a full, differential and translog backup of my database.
> Now when I try to restore the full backup it I get this message
> Msg 3159, Level 16, State 1, Line 1
> The tail of the log for the database "userdb" has not been backed up. Use
> BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do
> not
> want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
> statement to just overwrite the contents of the log.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> I then used this command
> restore database userdb from disk='X:\backups\userfulldev.bak' with
> replace
> and it restored the information correctly...
> But im a bit curious exactly what I did wrong. Usually I never have to use
> the "with replace" option when I restore my databases...
> your thoughts?
> /henrik
>|||On Feb 14, 1:47 pm, Henrik Nordgren
<HenrikNordg...@.discussions.microsoft.com> wrote:
> Hi!
> I have performed a full, differential and translog backup of my database.
> Now when I try to restore the full backup it I get this message
> Msg 3159, Level 16, State 1, Line 1
> The tail of the log for the database "userdb" has not been backed up. Use
> BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not
> want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
> statement to just overwrite the contents of the log.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I then used this command
> restore database userdb from disk='X:\backups\userfulldev.bak' with replace
> and it restored the information correctly...
> But im a bit curious exactly what I did wrong. Usually I never have to use
> the "with replace" option when I restore my databases...
> your thoughts?
> /henrik
This is telling you that something modified your database after your
last log backup was done. There are "new" transactions in the log
that you will lose if you proceed with the restore...

abnormal message when performing restore on 2005

Hi!
I have performed a full, differential and translog backup of my database.
Now when I try to restore the full backup it I get this message
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "userdb" has not been backed up. Use
BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not
want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I then used this command
restore database userdb from disk='X:\backups\userfulldev.bak' with replace
and it restored the information correctly...
But im a bit curious exactly what I did wrong. Usually I never have to use
the "with replace" option when I restore my databases...
your thoughts?
/henrik
That is a new "error" message designed to keep you from erasing potentially
useful data. It applies when the following conditions are al true
You are restoring over an existing database
The database is in full recovery mode
There are active log segments (segments that have not been backed up).
The assumption is that the database contains useful transactions that must
be preserved.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Henrik Nordgren" <HenrikNordgren@.discussions.microsoft.com> wrote in
message news:84A018FB-0A32-4991-BC94-BD875EAB553B@.microsoft.com...
> Hi!
> I have performed a full, differential and translog backup of my database.
> Now when I try to restore the full backup it I get this message
> Msg 3159, Level 16, State 1, Line 1
> The tail of the log for the database "userdb" has not been backed up. Use
> BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do
> not
> want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
> statement to just overwrite the contents of the log.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> I then used this command
> restore database userdb from disk='X:\backups\userfulldev.bak' with
> replace
> and it restored the information correctly...
> But im a bit curious exactly what I did wrong. Usually I never have to use
> the "with replace" option when I restore my databases...
> your thoughts?
> /henrik
>
|||On Feb 14, 1:47 pm, Henrik Nordgren
<HenrikNordg...@.discussions.microsoft.com> wrote:
> Hi!
> I have performed a full, differential and translog backup of my database.
> Now when I try to restore the full backup it I get this message
> Msg 3159, Level 16, State 1, Line 1
> The tail of the log for the database "userdb" has not been backed up. Use
> BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not
> want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
> statement to just overwrite the contents of the log.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I then used this command
> restore database userdb from disk='X:\backups\userfulldev.bak' with replace
> and it restored the information correctly...
> But im a bit curious exactly what I did wrong. Usually I never have to use
> the "with replace" option when I restore my databases...
> your thoughts?
> /henrik
This is telling you that something modified your database after your
last log backup was done. There are "new" transactions in the log
that you will lose if you proceed with the restore...
sql

abnormal message when performing restore on 2005

Hi!
I have performed a full, differential and translog backup of my database.
Now when I try to restore the full backup it I get this message
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "userdb" has not been backed up. Use
BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not
want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
statement to just overwrite the contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I then used this command
restore database userdb from disk='X:\backups\userfulldev.bak' with replace
and it restored the information correctly...
But im a bit curious exactly what I did wrong. Usually I never have to use
the "with replace" option when I restore my databases...
your thoughts?
/henrikThat is a new "error" message designed to keep you from erasing potentially
useful data. It applies when the following conditions are al true
You are restoring over an existing database
The database is in full recovery mode
There are active log segments (segments that have not been backed up).
The assumption is that the database contains useful transactions that must
be preserved.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Henrik Nordgren" <HenrikNordgren@.discussions.microsoft.com> wrote in
message news:84A018FB-0A32-4991-BC94-BD875EAB553B@.microsoft.com...
> Hi!
> I have performed a full, differential and translog backup of my database.
> Now when I try to restore the full backup it I get this message
> Msg 3159, Level 16, State 1, Line 1
> The tail of the log for the database "userdb" has not been backed up. Use
> BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do
> not
> want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
> statement to just overwrite the contents of the log.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
> I then used this command
> restore database userdb from disk='X:\backups\userfulldev.bak' with
> replace
> and it restored the information correctly...
> But im a bit curious exactly what I did wrong. Usually I never have to use
> the "with replace" option when I restore my databases...
> your thoughts?
> /henrik
>|||On Feb 14, 1:47 pm, Henrik Nordgren
<HenrikNordg...@.discussions.microsoft.com> wrote:
> Hi!
> I have performed a full, differential and translog backup of my database.
> Now when I try to restore the full backup it I get this message
> Msg 3159, Level 16, State 1, Line 1
> The tail of the log for the database "userdb" has not been backed up. Use
> BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do no
t
> want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE
> statement to just overwrite the contents of the log.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I then used this command
> restore database userdb from disk='X:\backups\userfulldev.bak' with replac
e
> and it restored the information correctly...
> But im a bit curious exactly what I did wrong. Usually I never have to use
> the "with replace" option when I restore my databases...
> your thoughts?
> /henrik
This is telling you that something modified your database after your
last log backup was done. There are "new" transactions in the log
that you will lose if you proceed with the restore...

A tale of 2 activities

I’m trying to calculate the difference between turn times. However, the employees performing each activity aren’t the same, and I want to make sure that the employee performing the starting activity gets credit for the turn time. Below is a copy of my MDX, the problem like I say is that I don’t know how to specifically assign the credit for the turn time. I’ve also attached a copy of the SQL version of what I’m trying to do in MDX.

Non-working MDX code

SELECT

NON EMPTY{

{([Measures].[Activity Turn Time]//This is an int and in seconds

,[Activity Code].[Activity Code].&[TD])}//TD is the ending activity

-

{([Measures].[Activity Turn Time]//This is an int and in seconds

,[Activity Code].[Activity Code].&[AP])}//AP is the starting activity

} ON COLUMNS

,{([Employee].[Emp Full Name].ALLMEMBERS)}ON ROWS

FROM[Employee Scorecard]

Working SQL code

SELECT AP.Emp_Full_Name

,SUM(TD.Activity_Turn_Time - AP.Activity_Turn_Time) as TD_to_AP

FROM TD

JOIN AP ON

TD.Application_ID = AP.Application_ID

GROUP BY AP.Emp_Full_Name

How about trying a calculated measure, like:

With Member [Measures].[TD_to_AP] as

([Measures].[Activity Turn Time]//This is an int and in seconds

,[Activity Code].[Activity Code].&[TD])//TD is the ending activity

-

([Measures].[Activity Turn Time]//This is an int and in seconds

,[Activity Code].[Activity Code].&)//AP is the starting activity

SELECT

NON EMPTY{

[Measures].[TD_to_AP]

} ON COLUMNS

, [Employee].[Emp Full Name].[Emp Full Name].MEMBERS ON ROWS

FROM [Employee Scorecard]

|||

I guess I’m not being clear. My MDX code executes and returns results. The problem is that sometimes the person performing the AP and the TD activity are different people and I want to specifically assign the results to the person who does the ending activity (TD).

|||

Your original MDX query might not execute exactly as you expect - note that:

{([Measures].[Activity Turn Time]//This is an int and in seconds

,[Activity Code].[Activity Code].&[TD])}//TD is the ending activity

-

{([Measures].[Activity Turn Time]//This is an int and in seconds

,[Activity Code].[Activity Code].&)}//AP is the starting activity

}

is equivalent to this first set alone because, with the '-' operator, you're trying to remove a non-existent tuple from the first set:

{([Measures].[Activity Turn Time]//This is an int and in seconds

,[Activity Code].[Activity Code].&[TD])}//TD is the ending activity

Anyway, to replicate the SQL query logic, there should be something like an [Application] dimension with an [Application_ID] attribute:

Code Snippet

With Member [Measures].[TD_to_AP] as
Sum(NonEmpty([Application].[Application_ID].[Application_ID],
{([Activity Code].[Activity Code].&[AP], [Measures].[Activity Turn Time])}),
([Measures].[Activity Turn Time]//This is an int and in seconds,

,[Employee].[Emp Full Name].[All] // since ending employee may be different
,[Activity Code].[Activity Code].&[TD])//TD is the ending activity
-
([Measures].[Activity Turn Time]//This is an int and in seconds
,[Activity Code].[Activity Code].&[AP])//AP is the starting activity
)

SELECT
NON EMPTY{
[Measures].[TD_to_AP]
} ON COLUMNS
, [Employee].[Emp Full Name].[Emp Full Name].MEMBERS ON ROWS
FROM [Employee Scorecard]

|||

This is really close to what I need. However, for some reason when I do this the TD numbers are way higher than expected.

Code Snippet

With Member [Measures].[TD_to_AP] as

SUM(

NonEmpty([Fact Application].[Fact Application].[All]

,{([Activity Code].[Activity Code].&[TD]

,[Special Activity First].[Special Activity First Name].&[First Activity]

,[Measures].[Activity Turn Time])}

)

,([Measures].[Activity Turn Time]//This is an int and in seconds,

,[Employee].[Emp Full Name].[All] // since ending employee may be different

,[Special Activity First].[Special Activity First Name].&[First Activity]

,[Activity Code].[Activity Code].&[TD])//TD is the ending activity

-

([Measures].[Activity Turn Time]//This is an int and in seconds

,[Special Activity First].[Special Activity First Name].&[First Activity]

,[Activity Code].[Activity Code].&[AP])//AP is the starting activity

)

SELECT

NON EMPTY{[Measures].[TD_to_AP]} ON COLUMNS

,{[Employee].[Emp Full Name].[Emp Full Name].ALLMEMBERS}ON ROWS

FROM [Employee Scorecard

]

|||

The 1st parameter of NonEmpty() should be the set of individual members, not the [All] member, ie. instead of:

NonEmpty([Fact Application].[Fact Application].[All] // this member aggregates all application records

it should be something like:

NonEmpty([Fact Application].[Fact Application].[Fact Application] // the set of individual application records to filter

A quicker way of performing this XML query?

Hi all

I have the following query that makes up part of a table-value-function i've written in SQL 2K5.

XML Query

SELECT CONVERT(XML, objectdata).value('(/xmlData/IsTrue)[1]', 'bit') as IsTrue
from myTable
where idfield = [@.FunctionTable].ID

Until adding this query, the function ran in under 60 seconds. Adding this query has added an extra 120 seconds to the function execution time.

This query is called around 200 times in the function as part of an update:

UPDATE @.FunctionTable set ...

....

, FieldValue = (SELECT CONVERT(XML, objectdata).value('(/xmlData/IsTrue)[1]', 'bit') as IsTrue
from myTable
where idfield = [@.FunctionTable].ID)

Is there a more performant way to do the same XML lookup?

Many Thanks

Are there any XML indexes created?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Probabaly not, would this index be placed upon the source field?
|||

You can create an index on the field where you do the XQuery.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

A quicker way of performing this XML query?

Hi all

I have the following query that makes up part of a table-value-function i've written in SQL 2K5.

XML Query

SELECT CONVERT(XML, objectdata).value('(/xmlData/IsTrue)[1]', 'bit') as IsTrue
from myTable
where idfield = [@.FunctionTable].ID

Until adding this query, the function ran in under 60 seconds. Adding this query has added an extra 120 seconds to the function execution time.

This query is called around 200 times in the function as part of an update:

UPDATE @.FunctionTable set ...

....

, FieldValue = (SELECT CONVERT(XML, objectdata).value('(/xmlData/IsTrue)[1]', 'bit') as IsTrue
from myTable
where idfield = [@.FunctionTable].ID)

Is there a more performant way to do the same XML lookup?

Many Thanks

Are there any XML indexes created?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||Probabaly not, would this index be placed upon the source field?
|||

You can create an index on the field where you do the XQuery.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

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)

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, thanksYou 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...
> 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
> >
> >
>|||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...
> > 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
> > >
> > >
> >
> >
>|||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...
> > 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...
> > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>|||"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)

A MergeJoin using two fields doesnt work !

Hi,

I have an issue. When I am performing a Merge Join using two fields of type
DT_WSTR on both sides of the Join, the Dataviewer after the Join only shows the left side 2 key
fields having values populated but both fields from the right hand side as being NULL. The Dataviewers I put in before the join show both sides of the join fields being populated ( 2 sides x 2 fields ). If I remove one of the fields from the join it shows both fields from the left side and the right side of the join being populated . I am trying to knock out duplicate values from the insert using 'genuine' Nulls from the right hand side so I need both parts of the 'Key'. I have checked the adnvanced tab that ignores Case etc but has had no effect.

I am using the technique from here : http://www.sqlis.com/311.aspx

Is this an SSIS bug ? It is driving me up the wall.

Help would be much appreciated please.

Thanks
Jon

SORTED !!

Sorry for this wasted post but this may be usefull for someone else in the future.

I changed the package to perform a lookup instead of a merge join and that failed as well. When I looked at the DB one of the two fields I was looking up to was defined as an NCHAR(15) and so always had whitespace at the end. I changed the datatype to be an NVARCHAR(15), reloaded the contents of the field and Voila, all was well.

Thanks anyway.

Jon