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/Update too slow

Hi,

I have a table with 110 nvarchar (255) columns in the database. I receive the data from the server (array) and them loop through it to insert the data into the database. The problem is it takes more than 3 minutes to insert (or update) (in memory) the 310 records it got from the array. I am reusing the Statement (with parameters). How could I improve the performance?

It's really a small quantity of data, the CPU in the device is 520Mhz... it should be alright!

Can aynone help me please?

Thanks

You can read some performance improvement topics here:

SQL Compact Edition Insert Performance

Tuning SQL Compact Edition Insert Performance

I hope these help you get the performance you want.

|||I've recently found a great article about SQL Server CE insert performance:
http://www.pocketpcdn.com/articles/articles.php?&atb.set(a_id)=11003&atb.set(c_id)=74&atb.perform(details)=&

In brief: OLE DB is the fastest method. When you want to stick to managed code try SqlCeResultSet

|||Thanks Guys!

I'll try that!

Cheers
|||I made the changes and the improvement was about 10X... amazing!!

Also the code is much clear now.

I am using this approach (ResultSet) for Updating and Seeking records as well.

I am happy now!

Insert/Update too slow

Hi,

I have a table with 110 nvarchar (255) columns in the database. I receive the data from the server (array) and them loop through it to insert the data into the database. The problem is it takes more than 3 minutes to insert (or update) (in memory) the 310 records it got from the array. I am reusing the Statement (with parameters). How could I improve the performance?

It's really a small quantity of data, the CPU in the device is 520Mhz... it should be alright!

Can aynone help me please?

Thanks

You can read some performance improvement topics here:

SQL Compact Edition Insert Performance

Tuning SQL Compact Edition Insert Performance

I hope these help you get the performance you want.

|||I've recently found a great article about SQL Server CE insert performance:
http://www.pocketpcdn.com/articles/articles.php?&atb.set(a_id)=11003&atb.set(c_id)=74&atb.perform(details)=&

In brief: OLE DB is the fastest method. When you want to stick to managed code try SqlCeResultSet

|||Thanks Guys!

I'll try that!

Cheers
|||I made the changes and the improvement was about 10X... amazing!!

Also the code is much clear now.

I am using this approach (ResultSet) for Updating and Seeking records as well.

I am happy now!

insert slow operation

hi ,
I have large no. of insert operations in my application .
Can I disable indexes somehow & make it fast . Or Can anyone suggest what
all best possiblities for the same .
Thanks
ARR
Aju
You cannot disable indexes. You can only drop them and re-create after
inserting.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>
|||Hi,
Drop the indexes apart from Clustered index and do the insert. You can
recreate the non clusterd index after the insert. This will speed
up the process. If you have some auditing triggers you can disable them
either.
Thanks
Hari
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>
|||For high-volume inserts, consider using a bulk load method such as BULK
INSERT, BCP or DTS. These are much faster than standard Transact-SQL
INSERTs. You can also speed things up by batching multiple INSERT
statements in a single transaction.
Hope this helps.
Dan Guzman
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>

insert slow operation

hi ,
I have large no. of insert operations in my application .
Can I disable indexes somehow & make it fast . Or Can anyone suggest what
all best possiblities for the same .
Thanks
ARRAju
You cannot disable indexes. You can only drop them and re-create after
inserting.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>|||Hi,
Drop the indexes apart from Clustered index and do the insert. You can
recreate the non clusterd index after the insert. This will speed
up the process. If you have some auditing triggers you can disable them
either.
Thanks
Hari
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>|||For high-volume inserts, consider using a bulk load method such as BULK
INSERT, BCP or DTS. These are much faster than standard Transact-SQL
INSERTs. You can also speed things up by batching multiple INSERT
statements in a single transaction.
Hope this helps.
Dan Guzman
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>

insert slow operation

hi ,
I have large no. of insert operations in my application .
Can I disable indexes somehow & make it fast . Or Can anyone suggest what
all best possiblities for the same .
Thanks
ARRAju
You cannot disable indexes. You can only drop them and re-create after
inserting.
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>|||Hi,
Drop the indexes apart from Clustered index and do the insert. You can
recreate the non clusterd index after the insert. This will speed
up the process. If you have some auditing triggers you can disable them
either.
Thanks
Hari
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>|||For high-volume inserts, consider using a bulk load method such as BULK
INSERT, BCP or DTS. These are much faster than standard Transact-SQL
INSERTs. You can also speed things up by batching multiple INSERT
statements in a single transaction.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Aju" <ajuonline@.yahoo.com> wrote in message
news:u0PZcC3OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> hi ,
> I have large no. of insert operations in my application .
> Can I disable indexes somehow & make it fast . Or Can anyone suggest what
> all best possiblities for the same .
>
> Thanks
> ARR
>

Insert running slow

Are inserts really slow in 2005 or am I doing something stupid?
Here's the table:
CREATE TABLE [dbo].[Tickets](
[ticket] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[data] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[added] [datetime] NOT NULL CONSTRAINT [DF_Tickets_added] DEFAULT
(getdate()),
[lastUpdated] [datetime] NOT NULL,
CONSTRAINT [PK_Tickets] PRIMARY KEY CLUSTERED
(
[ticket] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I wrote this stored procedure:
ALTER PROCEDURE [dbo].[CreateTicket]
@.ticket varchar(20) OUTPUT,
@.data varchar(2000)
AS
DECLARE @.key varchar(20)
DECLARE @.len int
DECLARE @.added bit
DECLARE @.cypher varchar(52)
SET NOCOUNT ON;
SET @.added=0
SET @.cypher='abcdefghijklmnopqrstuvwxyzABCDE
FGHIJKLMNOPQRSTUVWXYZ0123456789'
WHILE @.added=0
BEGIN
SELECT @.key='', @.len=20
WHILE @.len>0
BEGIN
-- The following line is the SLOW one!!!
SET @.key = @.key + SUBSTRING(@.cypher, CAST(FLOOR(RAND()*52) AS int)+1,1)
SET @.len = @.len -1
END
IF NOT EXISTS(SELECT 1 FROM Tickets WHERE ticket=@.key)
BEGIN
INSERT INTO Tickets (ticket, data,lastupdated) VALUES(@.key, @.data,GETDATE())
SET @.ticket = @.key
SET @.added = 1
END
END
And then used this to test it's speed:
DECLARE @.ticket varchar(20)
DECLARE @.sec datetime
DECLARE @.cnt int
TRUNCATE TABLE Tickets
SET @.cnt = 0
SET @.sec = DATEADD(second, 1, GETDATE())
WHILE GETDATE()<@.sec
BEGIN
EXEC CreateTicket @.ticket, 'this is a test'
SET @.cnt=@.cnt + 1
END
PRINT @.cnt
When I run this on SQL 2000, I get roughly 3000 records a second. When I
run it against 2005 I get roughly 160 records per second! The statement tha
t
is taking all the time in 2005 is the insert statement!
On 2005 if I comment it out I can execute 8,600ish loops per second. If it
isn't commented out I run 160ish.
On 2000 if I comment it out I can execute 5,900 loops per second, If it
isn't commented out I run 3,000ish.
Is inserting really that expensive or am I missing some knob I forgot to tur
n?Never mind. It appears there's something wrong with the server I was testin
g
on. Testing on another server I got 5,600ish inserts per second. What's
really weird though is the box that's performing slowing is a faster box tha
n
either of the other two with faster disks. Guess it's time to reinstall :)|||Before reinstalling, I would check perfmon and profiler and see what is
actually taking so long. It might be something easy to fix (or it might be
that reinstalling would cause the same performance problems.) A reinstall
might be in order, but unless that is really easy to do for you, it is
probably just something in how something is set up.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Larry Charlton" <LarryCharlton@.discussions.microsoft.com> wrote in message
news:A378F5F2-63BC-4FE0-AD6D-DFBB2952BB06@.microsoft.com...
> Never mind. It appears there's something wrong with the server I was
> testing
> on. Testing on another server I got 5,600ish inserts per second. What's
> really weird though is the box that's performing slowing is a faster box
> than
> either of the other two with faster disks. Guess it's time to reinstall
> :)
>

INSERT query using linked servers is slow.

Good day everyone,
I am setting up a simple data mart on a server running SQL 2005. It gets
updated nightly from another server with SQL 2000. Originally both database
s
were on the same server under 2000. Now that they are on different servers,
some of the INSERT queries seem to be running for an abnormally long time (s
o
long that I end up having to kill them).
All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
from [Local-Tables]". Most only join together 3 or 4 tables, using key
fields. Only a couple of them are running long, the others complete in abou
t
the same time as before. The problem tables are not at all large compared t
o
the ones that work fine, and in some cases even have less activity (new
records).
I am pretty sure the two servers are linked correctly, as the majority of
these SQL commands still work fine. The remote query timeout parameter in
sp_configure has been set to 0, since the default of 600 was causing
problems. Other than that, no changes have been made on either server.
Any suggestions for other things I might check? Thanks in advance for your
help.Couple of things:
Are you using BEGIN TRAN at all? If so, it should be BEGIN DISTRIBUTED TRAN
on linked servers.
Have you checked all the usual rules which apply to large INSERTs? eg
- presumably there is no-one else logged in to the database when you are
doing these large inserts ie no danger of locking.
- are there a lot of indexes on the table you're inserting to; this will
slow things down
- are there any triggers firing? Think about disabling them
- is there any other audit stuff going on, traces etc?
- make sure all table names are fully qualified eg
server01.northwind.dbo.authors (presumably you have to do this anyway)
- if you've got IDENTITY columns, particularly as primary keys on the target
table, I believe these _can_ cause hotspots, although these are supposed to
be a minor concern on modern hardware
- think about breaking up your inserts, say 10,000 rows at a time so you can
keep track of their progress. I've seen techniques for doing this in loops
on the web using either SET ROWCOUNT or TOP
Hope that helps.
Let me know how you get on.
Damien
"Chris F" wrote:

> Good day everyone,
> I am setting up a simple data mart on a server running SQL 2005. It gets
> updated nightly from another server with SQL 2000. Originally both databa
ses
> were on the same server under 2000. Now that they are on different server
s,
> some of the INSERT queries seem to be running for an abnormally long time
(so
> long that I end up having to kill them).
> All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
> from [Local-Tables]". Most only join together 3 or 4 tables, using key
> fields. Only a couple of them are running long, the others complete in ab
out
> the same time as before. The problem tables are not at all large compared
to
> the ones that work fine, and in some cases even have less activity (new
> records).
> I am pretty sure the two servers are linked correctly, as the majority of
> these SQL commands still work fine. The remote query timeout parameter in
> sp_configure has been set to 0, since the default of 600 was causing
> problems. Other than that, no changes have been made on either server.
> Any suggestions for other things I might check? Thanks in advance for you
r
> help.
>|||Chris
How much data do you insert?
Consider script out all indexes (remove them) and run the INSERT ,now that
after inserting re-create all indexes
"Chris F" <ChrisF@.discussions.microsoft.com> wrote in message
news:18089707-C1D6-4296-B937-C17D34DCAE65@.microsoft.com...
> Good day everyone,
> I am setting up a simple data mart on a server running SQL 2005. It gets
> updated nightly from another server with SQL 2000. Originally both
> databases
> were on the same server under 2000. Now that they are on different
> servers,
> some of the INSERT queries seem to be running for an abnormally long time
> (so
> long that I end up having to kill them).
> All of the queries are of the type "INSERT INTO [Remote] SELECT [Fields]
> from [Local-Tables]". Most only join together 3 or 4 tables, using key
> fields. Only a couple of them are running long, the others complete in
> about
> the same time as before. The problem tables are not at all large compared
> to
> the ones that work fine, and in some cases even have less activity (new
> records).
> I am pretty sure the two servers are linked correctly, as the majority of
> these SQL commands still work fine. The remote query timeout parameter in
> sp_configure has been set to 0, since the default of 600 was causing
> problems. Other than that, no changes have been made on either server.
> Any suggestions for other things I might check? Thanks in advance for
> your
> help.
>|||Thanks for your help.
Actually I am not using any form of BEGIN TRAN, since I keep getting a 7391
error in all cases. I am running a stored procedure consisting of several
delete and insert statements. Now that they are through the backlog (from
not having run for several days) all but one of the procedures have
acceptable run times.
Going down your list of checks, there are no other users, the only index is
the PK, no triggers, no audits/traces, no identity columns. I have been
fully qualifying the table on the remote server but not the local one where
the stored proc kicks off, I can try this and see if it helps. Will also
look at breaking up the query (I need to wait until the current run finishes
,
I found out over the wend that it does eventually). The only table we ar
e
still having problems with is the largest in the DB.
"Damien" wrote:
> Couple of things:
> Are you using BEGIN TRAN at all? If so, it should be BEGIN DISTRIBUTED TR
AN
> on linked servers.
> Have you checked all the usual rules which apply to large INSERTs? eg
> - presumably there is no-one else logged in to the database when you are
> doing these large inserts ie no danger of locking.
> - are there a lot of indexes on the table you're inserting to; this will
> slow things down
> - are there any triggers firing? Think about disabling them
> - is there any other audit stuff going on, traces etc?
> - make sure all table names are fully qualified eg
> server01.northwind.dbo.authors (presumably you have to do this anyway)
> - if you've got IDENTITY columns, particularly as primary keys on the targ
et
> table, I believe these _can_ cause hotspots, although these are supposed t
o
> be a minor concern on modern hardware
> - think about breaking up your inserts, say 10,000 rows at a time so you c
an
> keep track of their progress. I've seen techniques for doing this in loop
s
> on the web using either SET ROWCOUNT or TOP
>
> Hope that helps.
> Let me know how you get on.
>
> Damien
> "Chris F" wrote:
>