Hi,
I am using the MS sql DTS package to import some log data
from openenterprise server to the MS Sql server.
I am trying use the timestamps values of the MS SQL job
which ran successfully last time.
I was able to create an DTS pachage which imports the last
24 hours log data from a table of a database engine
name "polyhedra" which is used in a SCADA system to a MS
SQL sever table.
I made a schedule which imports the log data for the last
24 hours. My query statement look like as follows:
Select ID, name, timestamp, realvalue, delete
from realsamples
where timestamp > now()-hours(24)
But instead of hours(24), I need to use the timestamp when
my job last ran successfully. Because incase the any of
the server fails to run for more than 24 hours I will not
get the data older than last 24 hours.
Please help me to solve the problem.
Thank you in advance.You can get information about jobs (including the times when the job started
and if it completed succesfully) using the sp_help_jobhistory stored
procedure. See Books Online for the full syntax and details.
Jacco Schalkwijk
SQL Server MVP
"M Sikder" <anonymous@.discussions.microsoft.com> wrote in message
news:01a101c3bc3d$ed71f000$a301280a@.phx.gbl...
> Hi,
> I am using the MS sql DTS package to import some log data
> from openenterprise server to the MS Sql server.
> I am trying use the timestamps values of the MS SQL job
> which ran successfully last time.
> I was able to create an DTS pachage which imports the last
> 24 hours log data from a table of a database engine
> name "polyhedra" which is used in a SCADA system to a MS
> SQL sever table.
> I made a schedule which imports the log data for the last
> 24 hours. My query statement look like as follows:
> Select ID, name, timestamp, realvalue, delete
> from realsamples
> where timestamp > now()-hours(24)
> But instead of hours(24), I need to use the timestamp when
> my job last ran successfully. Because incase the any of
> the server fails to run for more than 24 hours I will not
> get the data older than last 24 hours.
> Please help me to solve the problem.
> Thank you in advance.
Showing posts with label job. Show all posts
Showing posts with label job. Show all posts
Wednesday, March 28, 2012
Having Indexed tbl will speed up DTS job ?
Hi ,
I have a DTS job that populates the data from server A to server B.
howver, i did not create the tbl with indexes.
from what i know , creating an indexed tbl will enable a faster retrieval
of data but in the case where my DTS job needs to populates the data across,
will it actually speed my my DTS job performance taking into acct that now
SQL will need to create the indexes for each indexed field ?
appreciate any advise
thksHi
From:
http://msdn.microsoft.com/library/d...asp?frame=true
"The performance benefits of indexes, however, do come with a cost. Tables
with indexes require more storage space in the database. Also, commands that
insert, update, or delete data can take longer and require more processing
time to maintain the indexes. When you design and create indexes, you should
ensure that the performance benefits outweigh the extra cost in storage spac
e
and processing resources."
If you are indexing the source table and are selectivly retrieving the
records, then there may be benefits, if you have indexed the destination
table it may slow the insertion of the records down, but subsequent retrieva
l
of information from that table can be improved.
A technique that is sometimes used, it to drop the indexes on your
destination table before inserting your records and then re-applying them
afterwards. This can sometime be quicker than doing the inserts with the
index present, and it will also mean that the indexes are less fragmented
(because they have been re-built).
John
"maxzsim" wrote:
> Hi ,
> I have a DTS job that populates the data from server A to server B.
> howver, i did not create the tbl with indexes.
> from what i know , creating an indexed tbl will enable a faster retrieva
l
> of data but in the case where my DTS job needs to populates the data acros
s,
> will it actually speed my my DTS job performance taking into acct that now
> SQL will need to create the indexes for each indexed field ?
> appreciate any advise
> thkssql
I have a DTS job that populates the data from server A to server B.
howver, i did not create the tbl with indexes.
from what i know , creating an indexed tbl will enable a faster retrieval
of data but in the case where my DTS job needs to populates the data across,
will it actually speed my my DTS job performance taking into acct that now
SQL will need to create the indexes for each indexed field ?
appreciate any advise
thksHi
From:
http://msdn.microsoft.com/library/d...asp?frame=true
"The performance benefits of indexes, however, do come with a cost. Tables
with indexes require more storage space in the database. Also, commands that
insert, update, or delete data can take longer and require more processing
time to maintain the indexes. When you design and create indexes, you should
ensure that the performance benefits outweigh the extra cost in storage spac
e
and processing resources."
If you are indexing the source table and are selectivly retrieving the
records, then there may be benefits, if you have indexed the destination
table it may slow the insertion of the records down, but subsequent retrieva
l
of information from that table can be improved.
A technique that is sometimes used, it to drop the indexes on your
destination table before inserting your records and then re-applying them
afterwards. This can sometime be quicker than doing the inserts with the
index present, and it will also mean that the indexes are less fragmented
(because they have been re-built).
John
"maxzsim" wrote:
> Hi ,
> I have a DTS job that populates the data from server A to server B.
> howver, i did not create the tbl with indexes.
> from what i know , creating an indexed tbl will enable a faster retrieva
l
> of data but in the case where my DTS job needs to populates the data acros
s,
> will it actually speed my my DTS job performance taking into acct that now
> SQL will need to create the indexes for each indexed field ?
> appreciate any advise
> thkssql
Having Indexed tbl will speed up DTS job ?
Hi ,
I have a DTS job that populates the data from server A to server B.
howver, i did not create the tbl with indexes.
from what i know , creating an indexed tbl will enable a faster retrieval
of data but in the case where my DTS job needs to populates the data across,
will it actually speed my my DTS job performance taking into acct that now
SQL will need to create the indexes for each indexed field ?
appreciate any advise
thks
Hi
From:
http://msdn.microsoft.com/library/de...asp?frame=true
"The performance benefits of indexes, however, do come with a cost. Tables
with indexes require more storage space in the database. Also, commands that
insert, update, or delete data can take longer and require more processing
time to maintain the indexes. When you design and create indexes, you should
ensure that the performance benefits outweigh the extra cost in storage space
and processing resources."
If you are indexing the source table and are selectivly retrieving the
records, then there may be benefits, if you have indexed the destination
table it may slow the insertion of the records down, but subsequent retrieval
of information from that table can be improved.
A technique that is sometimes used, it to drop the indexes on your
destination table before inserting your records and then re-applying them
afterwards. This can sometime be quicker than doing the inserts with the
index present, and it will also mean that the indexes are less fragmented
(because they have been re-built).
John
"maxzsim" wrote:
> Hi ,
> I have a DTS job that populates the data from server A to server B.
> howver, i did not create the tbl with indexes.
> from what i know , creating an indexed tbl will enable a faster retrieval
> of data but in the case where my DTS job needs to populates the data across,
> will it actually speed my my DTS job performance taking into acct that now
> SQL will need to create the indexes for each indexed field ?
> appreciate any advise
> thks
I have a DTS job that populates the data from server A to server B.
howver, i did not create the tbl with indexes.
from what i know , creating an indexed tbl will enable a faster retrieval
of data but in the case where my DTS job needs to populates the data across,
will it actually speed my my DTS job performance taking into acct that now
SQL will need to create the indexes for each indexed field ?
appreciate any advise
thks
Hi
From:
http://msdn.microsoft.com/library/de...asp?frame=true
"The performance benefits of indexes, however, do come with a cost. Tables
with indexes require more storage space in the database. Also, commands that
insert, update, or delete data can take longer and require more processing
time to maintain the indexes. When you design and create indexes, you should
ensure that the performance benefits outweigh the extra cost in storage space
and processing resources."
If you are indexing the source table and are selectivly retrieving the
records, then there may be benefits, if you have indexed the destination
table it may slow the insertion of the records down, but subsequent retrieval
of information from that table can be improved.
A technique that is sometimes used, it to drop the indexes on your
destination table before inserting your records and then re-applying them
afterwards. This can sometime be quicker than doing the inserts with the
index present, and it will also mean that the indexes are less fragmented
(because they have been re-built).
John
"maxzsim" wrote:
> Hi ,
> I have a DTS job that populates the data from server A to server B.
> howver, i did not create the tbl with indexes.
> from what i know , creating an indexed tbl will enable a faster retrieval
> of data but in the case where my DTS job needs to populates the data across,
> will it actually speed my my DTS job performance taking into acct that now
> SQL will need to create the indexes for each indexed field ?
> appreciate any advise
> thks
Having Indexed tbl will speed up DTS job ?
Hi ,
I have a DTS job that populates the data from server A to server B.
howver, i did not create the tbl with indexes.
from what i know , creating an indexed tbl will enable a faster retrieval
of data but in the case where my DTS job needs to populates the data across,
will it actually speed my my DTS job performance taking into acct that now
SQL will need to create the indexes for each indexed field ?
appreciate any advise
thksHi
From:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_30s5.asp?frame=true
"The performance benefits of indexes, however, do come with a cost. Tables
with indexes require more storage space in the database. Also, commands that
insert, update, or delete data can take longer and require more processing
time to maintain the indexes. When you design and create indexes, you should
ensure that the performance benefits outweigh the extra cost in storage space
and processing resources."
If you are indexing the source table and are selectivly retrieving the
records, then there may be benefits, if you have indexed the destination
table it may slow the insertion of the records down, but subsequent retrieval
of information from that table can be improved.
A technique that is sometimes used, it to drop the indexes on your
destination table before inserting your records and then re-applying them
afterwards. This can sometime be quicker than doing the inserts with the
index present, and it will also mean that the indexes are less fragmented
(because they have been re-built).
John
"maxzsim" wrote:
> Hi ,
> I have a DTS job that populates the data from server A to server B.
> howver, i did not create the tbl with indexes.
> from what i know , creating an indexed tbl will enable a faster retrieval
> of data but in the case where my DTS job needs to populates the data across,
> will it actually speed my my DTS job performance taking into acct that now
> SQL will need to create the indexes for each indexed field ?
> appreciate any advise
> thks
I have a DTS job that populates the data from server A to server B.
howver, i did not create the tbl with indexes.
from what i know , creating an indexed tbl will enable a faster retrieval
of data but in the case where my DTS job needs to populates the data across,
will it actually speed my my DTS job performance taking into acct that now
SQL will need to create the indexes for each indexed field ?
appreciate any advise
thksHi
From:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_05_30s5.asp?frame=true
"The performance benefits of indexes, however, do come with a cost. Tables
with indexes require more storage space in the database. Also, commands that
insert, update, or delete data can take longer and require more processing
time to maintain the indexes. When you design and create indexes, you should
ensure that the performance benefits outweigh the extra cost in storage space
and processing resources."
If you are indexing the source table and are selectivly retrieving the
records, then there may be benefits, if you have indexed the destination
table it may slow the insertion of the records down, but subsequent retrieval
of information from that table can be improved.
A technique that is sometimes used, it to drop the indexes on your
destination table before inserting your records and then re-applying them
afterwards. This can sometime be quicker than doing the inserts with the
index present, and it will also mean that the indexes are less fragmented
(because they have been re-built).
John
"maxzsim" wrote:
> Hi ,
> I have a DTS job that populates the data from server A to server B.
> howver, i did not create the tbl with indexes.
> from what i know , creating an indexed tbl will enable a faster retrieval
> of data but in the case where my DTS job needs to populates the data across,
> will it actually speed my my DTS job performance taking into acct that now
> SQL will need to create the indexes for each indexed field ?
> appreciate any advise
> thks
Subscribe to:
Posts (Atom)