help on multiple column updates - backtracking data for 30 days

i need to back track data should there be changes for the last 30 days.
help!
thank you!misheL wrote:

> i need to back track data should there be changes for the last 30
> days.
Are you seriously expecting many posters to respond and take the time
to answer your question considering the amount of time you have put
into formulating your question?
Kind regards,
Stijn Verrept.|||On Tue, 13 Dec 2005 17:30:02 -0800, misheL wrote:

>i need to back track data should there be changes for the last 30 days.
>help!
>thank you!
Hi misheL,
Please provide better specifications. See www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help on database choice?

Hi there,
I am currently writing an application for NNTP, the application requires
very FAST inserts, updates etc.
I started to write it with mySQL and I got good results, I was wondering if
anyone can confirm speed comparisions (and other comparisons) using MSDE
rather than mySQL.
As far as i know my app which runs on the desktop requires no special
licenses to use MSDE but mySQL does.
The application with only have 1 concurrent user and is being developed with
c# Visual Studio.net
Any help or information anybody can offer would be gratefully appreciated
Thanks in advance
Ian
Oh I forgot to mention, there is only 1 concurrent user but that user has
approximately 10 connections to the database. Maybe this will have an effect
on performance,.
THanks again
Ian
"Ian" <ianinmalaga@.hotmail.com> wrote in message
news:eLL8qdnZEHA.3228@.TK2MSFTNGP12.phx.gbl...
> Hi there,
> I am currently writing an application for NNTP, the application requires
> very FAST inserts, updates etc.
> I started to write it with mySQL and I got good results, I was wondering
if
> anyone can confirm speed comparisions (and other comparisons) using MSDE
> rather than mySQL.
> As far as i know my app which runs on the desktop requires no special
> licenses to use MSDE but mySQL does.
> The application with only have 1 concurrent user and is being developed
with
> c# Visual Studio.net
> Any help or information anybody can offer would be gratefully appreciated
> Thanks in advance
> Ian
>
sql

Help needed with an update trigger

Hi All,
I need to write a trigger to catch all updates made to a table and insert
the old and new values into a new table.
The problem is that the table being "Audited" has a lot of fields in (more
than 120) and a trigger that was written for it takes too long to execute.
Splitting the table up into smaller tables not an option right now
unfortunatly.
Is it possible to write an update trigger that can be fired and is clever
enough to only validate updated fields and still be as fast as possible.
ThanksJason
Well , check out IF UPDATE() command within a truigger that tells you what
column is updated as well as
COLUMNS_UPDATED() command
This is a short script written by Anith Sen
DECLARE @.ColID INT
DECLARE @.Cols VARCHAR(8000)
SET @.Cols = SPACE(0)
SET @.ColID = 1
WHILE @.ColID <= (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'w_works')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
POWER(2, (@.ColID - 1) % 8) =
POWER(2, (@.ColID - 1) % 8)
SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
SET @.ColID = @.ColID + 1
END
PRINT 'Updated columns are :' + @.Cols
On other hand you can update only these columns that was updated by using
the below technique
Before you give it to the production test it carefully
UPDATE YourTable SET col=I.col,.........
FROM insertded I INNER JOIN YourTable T
ON T.PK=I.PK AND
(
T.col<>I.col OR T.col1<>I.col1 OR......... )
"Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to write a trigger to catch all updates made to a table and insert
> the old and new values into a new table.
> The problem is that the table being "Audited" has a lot of fields in (more
> than 120) and a trigger that was written for it takes too long to execute.
> Splitting the table up into smaller tables not an option right now
> unfortunatly.
> Is it possible to write an update trigger that can be fired and is clever
> enough to only validate updated fields and still be as fast as possible.
> Thanks
>|||Thanks Uri, I'll give it a go.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqZUDw9mFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Jason
> Well , check out IF UPDATE() command within a truigger that tells you
> what column is updated as well as
> COLUMNS_UPDATED() command
> This is a short script written by Anith Sen
> DECLARE @.ColID INT
> DECLARE @.Cols VARCHAR(8000)
> SET @.Cols = SPACE(0)
> SET @.ColID = 1
> WHILE @.ColID <= (SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'w_works')
> BEGIN
> IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
> POWER(2, (@.ColID - 1) % 8) =
> POWER(2, (@.ColID - 1) % 8)
> SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
> SET @.ColID = @.ColID + 1
> END
> PRINT 'Updated columns are :' + @.Cols
>
> On other hand you can update only these columns that was updated by
> using the below technique
> Before you give it to the production test it carefully
>
> UPDATE YourTable SET col=I.col,.........
> FROM insertded I INNER JOIN YourTable T
> ON T.PK=I.PK AND
> (
> T.col<>I.col OR T.col1<>I.col1 OR......... )
>
>
> "Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
> news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
>|||On Mon, 8 Aug 2005 15:08:14 +1000, Jason Fischer wrote:

>Hi All,
>I need to write a trigger to catch all updates made to a table and insert
>the old and new values into a new table.
>The problem is that the table being "Audited" has a lot of fields in (more
>than 120) and a trigger that was written for it takes too long to execute.
>Splitting the table up into smaller tables not an option right now
>unfortunatly.
>Is it possible to write an update trigger that can be fired and is clever
>enough to only validate updated fields and still be as fast as possible.
>Thanks
>
Hi Jason,
The number of column won't usually affect performance as much as you
describe here. It seems as if your trigger is not doing things in the
fastest possible way.
Could you please post (a simplified version of) the table's DDL (as
CREATE TABLE statement), some sample data (as INSERT statements), the
expected outpuit and the current trigger code. No need to supply the
full 120 columns - trim it down to three or four or so to show the
patterns in your data and in your trigger.
See www.aspfaq.com/5006 for more details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help needed with an update trigger

Hi All,
I need to write a trigger to catch all updates made to a table and insert
the old and new values into a new table.
The problem is that the table being "Audited" has a lot of fields in (more
than 120) and a trigger that was written for it takes too long to execute.
Splitting the table up into smaller tables not an option right now
unfortunatly.
Is it possible to write an update trigger that can be fired and is clever
enough to only validate updated fields and still be as fast as possible.
ThanksJason
Well , check out IF UPDATE() command within a truigger that tells you what
column is updated as well as
COLUMNS_UPDATED() command
This is a short script written by Anith Sen
DECLARE @.ColID INT
DECLARE @.Cols VARCHAR(8000)
SET @.Cols = SPACE(0)
SET @.ColID = 1
WHILE @.ColID <= (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'w_works')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
POWER(2, (@.ColID - 1) % 8) = POWER(2, (@.ColID - 1) % 8)
SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
SET @.ColID = @.ColID + 1
END
PRINT 'Updated columns are :' + @.Cols
On other hand you can update only these columns that was updated by using
the below technique
Before you give it to the production test it carefully
UPDATE YourTable SET col=I.col,.........
FROM insertded I INNER JOIN YourTable T
ON T.PK=I.PK AND
(
T.col<>I.col OR T.col1<>I.col1 OR......... )
"Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to write a trigger to catch all updates made to a table and insert
> the old and new values into a new table.
> The problem is that the table being "Audited" has a lot of fields in (more
> than 120) and a trigger that was written for it takes too long to execute.
> Splitting the table up into smaller tables not an option right now
> unfortunatly.
> Is it possible to write an update trigger that can be fired and is clever
> enough to only validate updated fields and still be as fast as possible.
> Thanks
>|||Thanks Uri, I'll give it a go.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqZUDw9mFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Jason
> Well , check out IF UPDATE() command within a truigger that tells you
> what column is updated as well as
> COLUMNS_UPDATED() command
> This is a short script written by Anith Sen
> DECLARE @.ColID INT
> DECLARE @.Cols VARCHAR(8000)
> SET @.Cols = SPACE(0)
> SET @.ColID = 1
> WHILE @.ColID <= (SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'w_works')
> BEGIN
> IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
> POWER(2, (@.ColID - 1) % 8) => POWER(2, (@.ColID - 1) % 8)
> SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
> SET @.ColID = @.ColID + 1
> END
> PRINT 'Updated columns are :' + @.Cols
>
> On other hand you can update only these columns that was updated by
> using the below technique
> Before you give it to the production test it carefully
>
> UPDATE YourTable SET col=I.col,.........
> FROM insertded I INNER JOIN YourTable T
> ON T.PK=I.PK AND
> (
> T.col<>I.col OR T.col1<>I.col1 OR......... )
>
>
> "Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
> news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
>> Hi All,
>> I need to write a trigger to catch all updates made to a table and insert
>> the old and new values into a new table.
>> The problem is that the table being "Audited" has a lot of fields in
>> (more than 120) and a trigger that was written for it takes too long to
>> execute.
>> Splitting the table up into smaller tables not an option right now
>> unfortunatly.
>> Is it possible to write an update trigger that can be fired and is clever
>> enough to only validate updated fields and still be as fast as possible.
>> Thanks
>|||On Mon, 8 Aug 2005 15:08:14 +1000, Jason Fischer wrote:
>Hi All,
>I need to write a trigger to catch all updates made to a table and insert
>the old and new values into a new table.
>The problem is that the table being "Audited" has a lot of fields in (more
>than 120) and a trigger that was written for it takes too long to execute.
>Splitting the table up into smaller tables not an option right now
>unfortunatly.
>Is it possible to write an update trigger that can be fired and is clever
>enough to only validate updated fields and still be as fast as possible.
>Thanks
>
Hi Jason,
The number of column won't usually affect performance as much as you
describe here. It seems as if your trigger is not doing things in the
fastest possible way.
Could you please post (a simplified version of) the table's DDL (as
CREATE TABLE statement), some sample data (as INSERT statements), the
expected outpuit and the current trigger code. No need to supply the
full 120 columns - trim it down to three or four or so to show the
patterns in your data and in your trigger.
See www.aspfaq.com/5006 for more details.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help needed with an update trigger

Hi All,
I need to write a trigger to catch all updates made to a table and insert
the old and new values into a new table.
The problem is that the table being "Audited" has a lot of fields in (more
than 120) and a trigger that was written for it takes too long to execute.
Splitting the table up into smaller tables not an option right now
unfortunatly.
Is it possible to write an update trigger that can be fired and is clever
enough to only validate updated fields and still be as fast as possible.
Thanks
Jason
Well , check out IF UPDATE() command within a truigger that tells you what
column is updated as well as
COLUMNS_UPDATED() command
This is a short script written by Anith Sen
DECLARE @.ColID INT
DECLARE @.Cols VARCHAR(8000)
SET @.Cols = SPACE(0)
SET @.ColID = 1
WHILE @.ColID <= (SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'w_works')
BEGIN
IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
POWER(2, (@.ColID - 1) % 8) =
POWER(2, (@.ColID - 1) % 8)
SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
SET @.ColID = @.ColID + 1
END
PRINT 'Updated columns are :' + @.Cols
On other hand you can update only these columns that was updated by using
the below technique
Before you give it to the production test it carefully
UPDATE YourTable SET col=I.col,.........
FROM insertded I INNER JOIN YourTable T
ON T.PK=I.PK AND
(
T.col<>I.col OR T.col1<>I.col1 OR......... )
"Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need to write a trigger to catch all updates made to a table and insert
> the old and new values into a new table.
> The problem is that the table being "Audited" has a lot of fields in (more
> than 120) and a trigger that was written for it takes too long to execute.
> Splitting the table up into smaller tables not an option right now
> unfortunatly.
> Is it possible to write an update trigger that can be fired and is clever
> enough to only validate updated fields and still be as fast as possible.
> Thanks
>
|||Thanks Uri, I'll give it a go.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eqZUDw9mFHA.2156@.TK2MSFTNGP14.phx.gbl...
> Jason
> Well , check out IF UPDATE() command within a truigger that tells you
> what column is updated as well as
> COLUMNS_UPDATED() command
> This is a short script written by Anith Sen
> DECLARE @.ColID INT
> DECLARE @.Cols VARCHAR(8000)
> SET @.Cols = SPACE(0)
> SET @.ColID = 1
> WHILE @.ColID <= (SELECT COUNT(*)
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE TABLE_NAME = 'w_works')
> BEGIN
> IF (SUBSTRING(COLUMNS_UPDATED(),(@.ColID - 1) / 8 + 1, 1)) &
> POWER(2, (@.ColID - 1) % 8) =
> POWER(2, (@.ColID - 1) % 8)
> SET @.Cols = @.Cols + CAST(@.ColID AS VARCHAR) + ','
> SET @.ColID = @.ColID + 1
> END
> PRINT 'Updated columns are :' + @.Cols
>
> On other hand you can update only these columns that was updated by
> using the below technique
> Before you give it to the production test it carefully
>
> UPDATE YourTable SET col=I.col,.........
> FROM insertded I INNER JOIN YourTable T
> ON T.PK=I.PK AND
> (
> T.col<>I.col OR T.col1<>I.col1 OR......... )
>
>
> "Jason Fischer" <jason.fischer@.micropay.com.au> wrote in message
> news:Oi0v9c9mFHA.2892@.TK2MSFTNGP10.phx.gbl...
>
|||On Mon, 8 Aug 2005 15:08:14 +1000, Jason Fischer wrote:

>Hi All,
>I need to write a trigger to catch all updates made to a table and insert
>the old and new values into a new table.
>The problem is that the table being "Audited" has a lot of fields in (more
>than 120) and a trigger that was written for it takes too long to execute.
>Splitting the table up into smaller tables not an option right now
>unfortunatly.
>Is it possible to write an update trigger that can be fired and is clever
>enough to only validate updated fields and still be as fast as possible.
>Thanks
>
Hi Jason,
The number of column won't usually affect performance as much as you
describe here. It seems as if your trigger is not doing things in the
fastest possible way.
Could you please post (a simplified version of) the table's DDL (as
CREATE TABLE statement), some sample data (as INSERT statements), the
expected outpuit and the current trigger code. No need to supply the
full 120 columns - trim it down to three or four or so to show the
patterns in your data and in your trigger.
See www.aspfaq.com/5006 for more details.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)