INSERTED table performance

I have a table with one UPDATE trigger. When I execute a one row update
command
to the table, Graphical Query Plan reports very slow select from INSERTED
table (900ms).
However, if I check the same command using Profiler, everything goes quickly
(duration 0 ms). Why is that? Which one should I trust, profiler or query
plan?I trust Profiler more than the Graphical Query Plan. I have seen some quite
strange costs and percentages in the Graphical Query Plan, specially when
objects are involved that don't exist at the beginning of the query, like
the inserted and deleted tables, temporary tables and table variables
--
Jacco Schalkwijk
SQL Server MVP
"Pexi" <pekkadotheimonen@.plenwaredotnospamdotcom> wrote in message
news:emPWAZ4rDHA.2444@.TK2MSFTNGP12.phx.gbl...
> I have a table with one UPDATE trigger. When I execute a one row update
> command
> to the table, Graphical Query Plan reports very slow select from INSERTED
> table (900ms).
> However, if I check the same command using Profiler, everything goes
quickly
> (duration 0 ms). Why is that? Which one should I trust, profiler or query
> plan?
>|||Pexi,
Something that surprise me when I first found out. Inserted and deleted do
not exist, but are virtual tables that are populated each time you query
them by scanning the transaction log to extract the before and after images.
This is why the suggestion is to fill temp tables #inserted and #deleted if
you need to make repeated use of these tables.
Regarding the difference in the timings, the best way to measure is to
create a test. Do a loop calling your UPDATE repeatedly and logging the
milliseconds in a table.
SET @.BeginTime = GetDate()
EXEC YourTestStatement
INSERT INTO TrackingTable Values(@.BeginTime, GetDate())
Afterward you can analyze the results (and publish an article).
Russell Fields
http://www.sqlpass.org/
2004 PASS Community Summit - Orlando
- The largest user-event dedicated to SQL Server!
"Pexi" <pekkadotheimonen@.plenwaredotnospamdotcom> wrote in message
news:emPWAZ4rDHA.2444@.TK2MSFTNGP12.phx.gbl...
> I have a table with one UPDATE trigger. When I execute a one row update
> command
> to the table, Graphical Query Plan reports very slow select from INSERTED
> table (900ms).
> However, if I check the same command using Profiler, everything goes
quickly
> (duration 0 ms). Why is that? Which one should I trust, profiler or query
> plan?
>|||Thanks for the replies! You kind of confirm my thinking:
never trust the query plan - it just tells fairy tales sometimes :)
pexi
"Pexi" <pekkadotheimonen@.plenwaredotnospamdotcom> wrote in message
news:emPWAZ4rDHA.2444@.TK2MSFTNGP12.phx.gbl...
> I have a table with one UPDATE trigger. When I execute a one row update
> command
> to the table, Graphical Query Plan reports very slow select from INSERTED
> table (900ms).
> However, if I check the same command using Profiler, everything goes
quickly
> (duration 0 ms). Why is that? Which one should I trust, profiler or query
> plan?
>

Insert syntax error

I'm not quite sure if this is the right forum. I am trying to execute a insert statement. Using an ADO connection with Jet 4 (MS Access and VBA).

"INSERT INTO tblLocking (Table, Record, User) VALUES ('tblDiscrepancy','70','mnewheiser')"

I am getting a syntax error on the above statement. All fields that i am inserting to (Table, Record and User) are all text types, and the table does exist.

There is a fourth column in the table (LockID) which is a Access [Auto-Number] type(why i'm not sure on the forum) do i need to declare this in the statement.

Any help would be much appreciated as its is beginning to drive me mad.

CheersOk, so it appears the solutions wasn't as complicated as i thought it would be.

Although 'Table' and 'User' where valid columns in the table, they are also reserved words (or something like that). So in the statement they needs [ and ] around them (i.e. [Table])

Don't have to slit my wrists now. :)

Insert Statement

When I execute the INSERT statement on SQL Server 2000, the table on which I
run this statement is not affected. What could be the problem? Could anyone
please tell me what's the maximum size of a table in SQL Server 2000.
Thanking you in advance.Choi
Please read this article in the BOL
"Estimating the Size of a Table"
"Choi" <anonymous@.discussions.microsoft.com> wrote in message
news:CFD1E2F3-E9DE-4E87-9FAB-68FC64912C40@.microsoft.com...
> When I execute the INSERT statement on SQL Server 2000, the table on which
I run this statement is not affected. What could be the problem? Could
anyone please tell me what's the maximum size of a table in SQL Server 2000.
> Thanking you in advance.|||Hi,
Check whether the implicit transaction is set to "on".
How to verify,
1. In Query Analyzer, go to tools -- Options
2. Go to connection Properties tab
3. Check whether SET IMPLICIT_TRANSACTIONS is selected.
If selected remove that option and re-run the insert statement.
Other possibility for this can be ,
BEGIN TRAN
insert statement
ROLLBACK TRAN
Thanks
Hari
MCDBA
"Choi" <anonymous@.discussions.microsoft.com> wrote in message
news:CFD1E2F3-E9DE-4E87-9FAB-68FC64912C40@.microsoft.com...
> When I execute the INSERT statement on SQL Server 2000, the table on which
I run this statement is not affected. What could be the problem? Could
anyone please tell me what's the maximum size of a table in SQL Server 2000.
> Thanking you in advance.

Insert Statement

When I execute the INSERT statement on SQL Server 2000, the table on which I run this statement is not affected. What could be the problem? Could anyone please tell me what's the maximum size of a table in SQL Server 2000
Thanking you in advance.Choi
Please read this article in the BOL
"Estimating the Size of a Table"
"Choi" <anonymous@.discussions.microsoft.com> wrote in message
news:CFD1E2F3-E9DE-4E87-9FAB-68FC64912C40@.microsoft.com...
> When I execute the INSERT statement on SQL Server 2000, the table on which
I run this statement is not affected. What could be the problem? Could
anyone please tell me what's the maximum size of a table in SQL Server 2000.
> Thanking you in advance.|||Hi,
Check whether the implicit transaction is set to "on".
How to verify,
1. In Query Analyzer, go to tools -- Options
2. Go to connection Properties tab
3. Check whether SET IMPLICIT_TRANSACTIONS is selected.
If selected remove that option and re-run the insert statement.
Other possibility for this can be ,
BEGIN TRAN
insert statement
ROLLBACK TRAN
Thanks
Hari
MCDBA
"Choi" <anonymous@.discussions.microsoft.com> wrote in message
news:CFD1E2F3-E9DE-4E87-9FAB-68FC64912C40@.microsoft.com...
> When I execute the INSERT statement on SQL Server 2000, the table on which
I run this statement is not affected. What could be the problem? Could
anyone please tell me what's the maximum size of a table in SQL Server 2000.
> Thanking you in advance.