I am updating data from a smaller linked table into a larger table (SQL
server 2K sp4, Win server 2k3 sp1) (data which had been inserted
previously). The update now looks like:
UPDATE tableA
SET colx = (select colx FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol),
coly = (select coly FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol)
WHERE EXISTS (SELECT * FROM linkedtableB as rem
WHERE rem.pkcol = tableA.pkcol)
This seems to run an inordinately long time (in excess of 1hr + for
this one table, and I have to update 40 more tables). My question it,
what would be a more effiecient way to update this data? My reasoning
for updating this data is, while doing the insert earlier, I may have
gathered partial data during an operation, thus requiring an update at
some later point.
Any insight into a better solution would be greatly appriciated.
Daniel
danielp
try this one
UPDATE tableA SET colx =rem.colx , coly =rem.coly FROM linkedtableB rem
JOIN tableA ON
rem.pkcol = tableA.pkcol
"danielp" <danielsmith611@.gmail.com> wrote in message
news:1162310539.241683.96330@.e3g2000cwe.googlegrou ps.com...
>I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>
|||Hi Daniel
Try
UPDATE A
SET colx = B.colx
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"danielp" wrote:
> I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>
|||I missed out a comma!
UPDATE A
SET colx = B.colx,
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi Daniel
> Try
> UPDATE A
> SET colx = B.colx
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "danielp" wrote:
|||Thanks Uri and John!
John Bell wrote:[vbcol=seagreen]
> I missed out a comma!
> UPDATE A
> SET colx = B.colx,
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "John Bell" wrote:
Showing posts with label update. Show all posts
Showing posts with label update. Show all posts
Wednesday, March 28, 2012
More effiect update from linked server
I am updating data from a smaller linked table into a larger table (SQL
server 2K sp4, Win server 2k3 sp1) (data which had been inserted
previously). The update now looks like:
UPDATE tableA
SET colx = (select colx FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol),
coly = (select coly FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol)
WHERE EXISTS (SELECT * FROM linkedtableB as rem
WHERE rem.pkcol = tableA.pkcol)
This seems to run an inordinately long time (in excess of 1hr + for
this one table, and I have to update 40 more tables). My question it,
what would be a more effiecient way to update this data? My reasoning
for updating this data is, while doing the insert earlier, I may have
gathered partial data during an operation, thus requiring an update at
some later point.
Any insight into a better solution would be greatly appriciated.
Danieldanielp
try this one
UPDATE tableA SET colx =rem.colx , coly =rem.coly FROM linkedtableB rem
JOIN tableA ON
rem.pkcol = tableA.pkcol
"danielp" <danielsmith611@.gmail.com> wrote in message
news:1162310539.241683.96330@.e3g2000cwe.googlegroups.com...
>I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>|||Hi Daniel
Try
UPDATE A
SET colx = B.colx
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"danielp" wrote:
> I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>|||I missed out a comma!
UPDATE A
SET colx = B.colx,
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi Daniel
> Try
> UPDATE A
> SET colx = B.colx
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "danielp" wrote:
>|||Thanks Uri and John!
John Bell wrote:[vbcol=seagreen]
> I missed out a comma!
> UPDATE A
> SET colx = B.colx,
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "John Bell" wrote:
>
server 2K sp4, Win server 2k3 sp1) (data which had been inserted
previously). The update now looks like:
UPDATE tableA
SET colx = (select colx FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol),
coly = (select coly FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol)
WHERE EXISTS (SELECT * FROM linkedtableB as rem
WHERE rem.pkcol = tableA.pkcol)
This seems to run an inordinately long time (in excess of 1hr + for
this one table, and I have to update 40 more tables). My question it,
what would be a more effiecient way to update this data? My reasoning
for updating this data is, while doing the insert earlier, I may have
gathered partial data during an operation, thus requiring an update at
some later point.
Any insight into a better solution would be greatly appriciated.
Danieldanielp
try this one
UPDATE tableA SET colx =rem.colx , coly =rem.coly FROM linkedtableB rem
JOIN tableA ON
rem.pkcol = tableA.pkcol
"danielp" <danielsmith611@.gmail.com> wrote in message
news:1162310539.241683.96330@.e3g2000cwe.googlegroups.com...
>I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>|||Hi Daniel
Try
UPDATE A
SET colx = B.colx
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"danielp" wrote:
> I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>|||I missed out a comma!
UPDATE A
SET colx = B.colx,
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"John Bell" wrote:
[vbcol=seagreen]
> Hi Daniel
> Try
> UPDATE A
> SET colx = B.colx
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "danielp" wrote:
>|||Thanks Uri and John!
John Bell wrote:[vbcol=seagreen]
> I missed out a comma!
> UPDATE A
> SET colx = B.colx,
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "John Bell" wrote:
>
More effiect update from linked server
I am updating data from a smaller linked table into a larger table (SQL
server 2K sp4, Win server 2k3 sp1) (data which had been inserted
previously). The update now looks like:
UPDATE tableA
SET colx = (select colx FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol),
coly = (select coly FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol)
WHERE EXISTS (SELECT * FROM linkedtableB as rem
WHERE rem.pkcol = tableA.pkcol)
This seems to run an inordinately long time (in excess of 1hr + for
this one table, and I have to update 40 more tables). My question it,
what would be a more effiecient way to update this data? My reasoning
for updating this data is, while doing the insert earlier, I may have
gathered partial data during an operation, thus requiring an update at
some later point.
Any insight into a better solution would be greatly appriciated.
Danieldanielp
try this one
UPDATE tableA SET colx =rem.colx , coly =rem.coly FROM linkedtableB rem
JOIN tableA ON
rem.pkcol = tableA.pkcol
"danielp" <danielsmith611@.gmail.com> wrote in message
news:1162310539.241683.96330@.e3g2000cwe.googlegroups.com...
>I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>|||Hi Daniel
Try
UPDATE A
SET colx = B.colx
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"danielp" wrote:
> I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>|||I missed out a comma!
UPDATE A
SET colx = B.colx,
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"John Bell" wrote:
> Hi Daniel
> Try
> UPDATE A
> SET colx = B.colx
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "danielp" wrote:
> > I am updating data from a smaller linked table into a larger table (SQL
> > server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> > previously). The update now looks like:
> >
> > UPDATE tableA
> > SET colx = (select colx FROM linkedtableB rem
> > WHERE rem.pkcol = tableA.pkcol),
> > coly = (select coly FROM linkedtableB rem
> > WHERE rem.pkcol = tableA.pkcol)
> > WHERE EXISTS (SELECT * FROM linkedtableB as rem
> > WHERE rem.pkcol = tableA.pkcol)
> >
> > This seems to run an inordinately long time (in excess of 1hr + for
> > this one table, and I have to update 40 more tables). My question it,
> > what would be a more effiecient way to update this data? My reasoning
> > for updating this data is, while doing the insert earlier, I may have
> > gathered partial data during an operation, thus requiring an update at
> > some later point.
> >
> > Any insight into a better solution would be greatly appriciated.
> >
> > Daniel
> >
> >|||Thanks Uri and John!
John Bell wrote:
> I missed out a comma!
> UPDATE A
> SET colx = B.colx,
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "John Bell" wrote:
> > Hi Daniel
> >
> > Try
> >
> > UPDATE A
> > SET colx = B.colx
> > coly = B.coly
> > FROM tableA A
> > JOIN linkedtableB B ON B.pkcol = A.pkcol
> >
> > John
> >
> > "danielp" wrote:
> >
> > > I am updating data from a smaller linked table into a larger table (SQL
> > > server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> > > previously). The update now looks like:
> > >
> > > UPDATE tableA
> > > SET colx = (select colx FROM linkedtableB rem
> > > WHERE rem.pkcol = tableA.pkcol),
> > > coly = (select coly FROM linkedtableB rem
> > > WHERE rem.pkcol = tableA.pkcol)
> > > WHERE EXISTS (SELECT * FROM linkedtableB as rem
> > > WHERE rem.pkcol = tableA.pkcol)
> > >
> > > This seems to run an inordinately long time (in excess of 1hr + for
> > > this one table, and I have to update 40 more tables). My question it,
> > > what would be a more effiecient way to update this data? My reasoning
> > > for updating this data is, while doing the insert earlier, I may have
> > > gathered partial data during an operation, thus requiring an update at
> > > some later point.
> > >
> > > Any insight into a better solution would be greatly appriciated.
> > >
> > > Daniel
> > >
> > >sql
server 2K sp4, Win server 2k3 sp1) (data which had been inserted
previously). The update now looks like:
UPDATE tableA
SET colx = (select colx FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol),
coly = (select coly FROM linkedtableB rem
WHERE rem.pkcol = tableA.pkcol)
WHERE EXISTS (SELECT * FROM linkedtableB as rem
WHERE rem.pkcol = tableA.pkcol)
This seems to run an inordinately long time (in excess of 1hr + for
this one table, and I have to update 40 more tables). My question it,
what would be a more effiecient way to update this data? My reasoning
for updating this data is, while doing the insert earlier, I may have
gathered partial data during an operation, thus requiring an update at
some later point.
Any insight into a better solution would be greatly appriciated.
Danieldanielp
try this one
UPDATE tableA SET colx =rem.colx , coly =rem.coly FROM linkedtableB rem
JOIN tableA ON
rem.pkcol = tableA.pkcol
"danielp" <danielsmith611@.gmail.com> wrote in message
news:1162310539.241683.96330@.e3g2000cwe.googlegroups.com...
>I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>|||Hi Daniel
Try
UPDATE A
SET colx = B.colx
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"danielp" wrote:
> I am updating data from a smaller linked table into a larger table (SQL
> server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> previously). The update now looks like:
> UPDATE tableA
> SET colx = (select colx FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol),
> coly = (select coly FROM linkedtableB rem
> WHERE rem.pkcol = tableA.pkcol)
> WHERE EXISTS (SELECT * FROM linkedtableB as rem
> WHERE rem.pkcol = tableA.pkcol)
> This seems to run an inordinately long time (in excess of 1hr + for
> this one table, and I have to update 40 more tables). My question it,
> what would be a more effiecient way to update this data? My reasoning
> for updating this data is, while doing the insert earlier, I may have
> gathered partial data during an operation, thus requiring an update at
> some later point.
> Any insight into a better solution would be greatly appriciated.
> Daniel
>|||I missed out a comma!
UPDATE A
SET colx = B.colx,
coly = B.coly
FROM tableA A
JOIN linkedtableB B ON B.pkcol = A.pkcol
John
"John Bell" wrote:
> Hi Daniel
> Try
> UPDATE A
> SET colx = B.colx
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "danielp" wrote:
> > I am updating data from a smaller linked table into a larger table (SQL
> > server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> > previously). The update now looks like:
> >
> > UPDATE tableA
> > SET colx = (select colx FROM linkedtableB rem
> > WHERE rem.pkcol = tableA.pkcol),
> > coly = (select coly FROM linkedtableB rem
> > WHERE rem.pkcol = tableA.pkcol)
> > WHERE EXISTS (SELECT * FROM linkedtableB as rem
> > WHERE rem.pkcol = tableA.pkcol)
> >
> > This seems to run an inordinately long time (in excess of 1hr + for
> > this one table, and I have to update 40 more tables). My question it,
> > what would be a more effiecient way to update this data? My reasoning
> > for updating this data is, while doing the insert earlier, I may have
> > gathered partial data during an operation, thus requiring an update at
> > some later point.
> >
> > Any insight into a better solution would be greatly appriciated.
> >
> > Daniel
> >
> >|||Thanks Uri and John!
John Bell wrote:
> I missed out a comma!
> UPDATE A
> SET colx = B.colx,
> coly = B.coly
> FROM tableA A
> JOIN linkedtableB B ON B.pkcol = A.pkcol
> John
> "John Bell" wrote:
> > Hi Daniel
> >
> > Try
> >
> > UPDATE A
> > SET colx = B.colx
> > coly = B.coly
> > FROM tableA A
> > JOIN linkedtableB B ON B.pkcol = A.pkcol
> >
> > John
> >
> > "danielp" wrote:
> >
> > > I am updating data from a smaller linked table into a larger table (SQL
> > > server 2K sp4, Win server 2k3 sp1) (data which had been inserted
> > > previously). The update now looks like:
> > >
> > > UPDATE tableA
> > > SET colx = (select colx FROM linkedtableB rem
> > > WHERE rem.pkcol = tableA.pkcol),
> > > coly = (select coly FROM linkedtableB rem
> > > WHERE rem.pkcol = tableA.pkcol)
> > > WHERE EXISTS (SELECT * FROM linkedtableB as rem
> > > WHERE rem.pkcol = tableA.pkcol)
> > >
> > > This seems to run an inordinately long time (in excess of 1hr + for
> > > this one table, and I have to update 40 more tables). My question it,
> > > what would be a more effiecient way to update this data? My reasoning
> > > for updating this data is, while doing the insert earlier, I may have
> > > gathered partial data during an operation, thus requiring an update at
> > > some later point.
> > >
> > > Any insight into a better solution would be greatly appriciated.
> > >
> > > Daniel
> > >
> > >sql
Friday, March 9, 2012
Monitoring database activities from outside SQL Server
Hi,
I want to know how can I monitor some operations that are executed on a SQL Server database. Operations like: INSERT, DELETE, UPDATE, transactions, and so on (I know that I can do this using triggers, but doing this way I'll have to fill a table with the information from the triggers and stay loading the table data all the time. I want a way to monitor using other resources, in realtime).
My idea is to build a program to monitor these activities (developed in C# to be more exactly). How can I do this?
Thanks
PS: I know that we already have commercial products to do this, but I need to develop one.
WHy not just use a trace and export it to whatever external app you want to review?
I usually create my traces using sp_**trace, then I've set up external apps to call the trace and view the results.
|||The problem is...if I create some triggers and Stored Procedures, my program will have to stay selecting data for long cycles. I'd like to have something automated, so when something occurs, automatically my program understands it.
Must have a way to do this, because I think SQL Profiler wouldn't use SELECTs to get Performance and Counting info.
Monitoring database activities from outside SQL Server
Hi,
I want to know how can I monitor some operations that are executed on a SQL Server database. Operations like: INSERT, DELETE, UPDATE, transactions, and so on (I know that I can do this using triggers, but doing this way I'll have to fill a table with the information from the triggers and stay loading the table data all the time. I want a way to monitor using other resources, in realtime).
My idea is to build a program to monitor these activities (developed in C# to be more exactly). How can I do this?
Thanks
PS: I know that we already have commercial products to do this, but I need to develop one.
WHy not just use a trace and export it to whatever external app you want to review?
I usually create my traces using sp_**trace, then I've set up external apps to call the trace and view the results.
|||The problem is...if I create some triggers and Stored Procedures, my program will have to stay selecting data for long cycles. I'd like to have something automated, so when something occurs, automatically my program understands it.
Must have a way to do this, because I think SQL Profiler wouldn't use SELECTs to get Performance and Counting info.
Wednesday, March 7, 2012
monitor large update
I have a large table 34 million records and I need to update a field.
I am doing this in batches of 1 million records (by joining a temp
table to the primary key).
I am trying to monitor the update using
SELECT COUNT(*)
FROM myTable with(nolock)
where newKey is null
What is weird is the number counts down a few million (one to many
relationship) records then all of the sudden the number is bumped back
up to 34 million records again and the update statement (same
transaction) seems to start over.
Any ideas on what is going on?
Is my with(nolock) method an invalid way to monitor the update?
Thanks for the advice!If you are updating in batches within a loop, then you should have a count
of the current iteration. Perhaps have the SP display some status
information while executing in Query Analyzer. If <print @.loop> doesn't
display until the end of the transaction, then try <select @.loop>. You can
also monitor using SQL Profiler.
To improve the performace of this monster update try the following:
#1 Insert the records with the columns already populated with non-null
values. This way your update does not need to extend the data when updating.
#2 Consider setting recovery model to simple.
#3 Drop all indexes from table before the update and re-create them again
afterward.
#4 Do not include updatable columns in a clustered index.
<daveg.01@.gmail.com> wrote in message
news:1122495806.739965.230420@.z14g2000cwz.googlegroups.com...
>I have a large table 34 million records and I need to update a field.
> I am doing this in batches of 1 million records (by joining a temp
> table to the primary key).
> I am trying to monitor the update using
> SELECT COUNT(*)
> FROM myTable with(nolock)
> where newKey is null
> What is weird is the number counts down a few million (one to many
> relationship) records then all of the sudden the number is bumped back
> up to 34 million records again and the update statement (same
> transaction) seems to start over.
> Any ideas on what is going on?
> Is my with(nolock) method an invalid way to monitor the update?
> Thanks for the advice!
>|||Hi
You may want to create an indexed view
Some examples from Steve Kass
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
<daveg.01@.gmail.com> wrote in message
news:1122495806.739965.230420@.z14g2000cwz.googlegroups.com...
>I have a large table 34 million records and I need to update a field.
> I am doing this in batches of 1 million records (by joining a temp
> table to the primary key).
> I am trying to monitor the update using
> SELECT COUNT(*)
> FROM myTable with(nolock)
> where newKey is null
> What is weird is the number counts down a few million (one to many
> relationship) records then all of the sudden the number is bumped back
> up to 34 million records again and the update statement (same
> transaction) seems to start over.
> Any ideas on what is going on?
> Is my with(nolock) method an invalid way to monitor the update?
> Thanks for the advice!
>|||Could you expain that a bit? Will this allow me to monitor a large
update that might take 2 or 3 hours?
Does the clustered index on the view get updated while the update on
the base table is going on?
I am doing this in batches of 1 million records (by joining a temp
table to the primary key).
I am trying to monitor the update using
SELECT COUNT(*)
FROM myTable with(nolock)
where newKey is null
What is weird is the number counts down a few million (one to many
relationship) records then all of the sudden the number is bumped back
up to 34 million records again and the update statement (same
transaction) seems to start over.
Any ideas on what is going on?
Is my with(nolock) method an invalid way to monitor the update?
Thanks for the advice!If you are updating in batches within a loop, then you should have a count
of the current iteration. Perhaps have the SP display some status
information while executing in Query Analyzer. If <print @.loop> doesn't
display until the end of the transaction, then try <select @.loop>. You can
also monitor using SQL Profiler.
To improve the performace of this monster update try the following:
#1 Insert the records with the columns already populated with non-null
values. This way your update does not need to extend the data when updating.
#2 Consider setting recovery model to simple.
#3 Drop all indexes from table before the update and re-create them again
afterward.
#4 Do not include updatable columns in a clustered index.
<daveg.01@.gmail.com> wrote in message
news:1122495806.739965.230420@.z14g2000cwz.googlegroups.com...
>I have a large table 34 million records and I need to update a field.
> I am doing this in batches of 1 million records (by joining a temp
> table to the primary key).
> I am trying to monitor the update using
> SELECT COUNT(*)
> FROM myTable with(nolock)
> where newKey is null
> What is weird is the number counts down a few million (one to many
> relationship) records then all of the sudden the number is bumped back
> up to 34 million records again and the update statement (same
> transaction) seems to start over.
> Any ideas on what is going on?
> Is my with(nolock) method an invalid way to monitor the update?
> Thanks for the advice!
>|||Hi
You may want to create an indexed view
Some examples from Steve Kass
create table T (
i int,
filler char(1000) default 'abc'
)
go
create view T_count with schemabinding as
select
cast(i as bit) as val,
count_big(*) T_count
from dbo.T group by cast(i as bit)
go
create unique clustered index T_count_uci on T_count(val)
go
insert into T(i)
select OrderID
from Northwind..[Order Details]
go
set statistics io on
select count(*) from T
go
select sum(T_count) from T_count with (noexpand)
go
set statistics io off
-- uses an efficient query plan on the materialized view
go
drop view T_count
drop table T
<daveg.01@.gmail.com> wrote in message
news:1122495806.739965.230420@.z14g2000cwz.googlegroups.com...
>I have a large table 34 million records and I need to update a field.
> I am doing this in batches of 1 million records (by joining a temp
> table to the primary key).
> I am trying to monitor the update using
> SELECT COUNT(*)
> FROM myTable with(nolock)
> where newKey is null
> What is weird is the number counts down a few million (one to many
> relationship) records then all of the sudden the number is bumped back
> up to 34 million records again and the update statement (same
> transaction) seems to start over.
> Any ideas on what is going on?
> Is my with(nolock) method an invalid way to monitor the update?
> Thanks for the advice!
>|||Could you expain that a bit? Will this allow me to monitor a large
update that might take 2 or 3 hours?
Does the clustered index on the view get updated while the update on
the base table is going on?
Subscribe to:
Posts (Atom)