MS SQL Server - INSERT INTO affecting user SELECT queries

Author

INSERT INTO affecting user SELECT queries

2005-04-27, 11:23 am

Hi all

I am using a batch once a day INSERT INTO statement to insert 1
million plus records from an 'import' table to a 'live' table for an
online archive solution.

This all works fine though it takes a while to run, as I have Indexes
defined for many columns. During the Insert, sqlserver grinds to a
halt for any user SELECT querys on the live table.

My question is, how can i improve query response while this import in
going in. I'm considered dropping indexes and recreating after the
INSERT but won't this affect the ability to search?

Any thoughts greatly appreaciated

--Insert the documents
INSERT INTO LiveDOCUMENTS
(
index1,index2,index3
,index4,index5,index
6,index7,index8,inde
x9,index10,index11,i
ndex12,index13,index
14,index15
)

SELECT
index1,index2,index3
,index4,index5,index
6,index7,index8,inde
x9,index10,index11,i
ndex12,index13,index
14,index15
FROM IMPORTDocuments

Jens Süßmeyer

2005-04-27, 1:23 pm

Perhaps you gotta too pessimistic locking on the query, try to soften you
query with one of the optimization hints . By default SQL Server decide what
to use, but perhaps this could improve performance to you:

USE NORTHWIND
Select * from Orders WITH (ROWLOCK)

HTH, Jens Suessmeyer.

---
http://www.sqlserver2005.de
---

"rw" <ron@stringx.com> schrieb im Newsbeitrag
news:2d051ab.0504270758.391c3add@posting.google.com...
> Hi all
>
> I am using a batch once a day INSERT INTO statement to insert 1
> million plus records from an 'import' table to a 'live' table for an
> online archive solution.
>
> This all works fine though it takes a while to run, as I have Indexes
> defined for many columns. During the Insert, sqlserver grinds to a
> halt for any user SELECT querys on the live table.
>
> My question is, how can i improve query response while this import in
> going in. I'm considered dropping indexes and recreating after the
> INSERT but won't this affect the ability to search?
>
> Any thoughts greatly appreaciated
>
> --Insert the documents
> INSERT INTO LiveDOCUMENTS
> (
> index1,index2,index3
,index4,index5,index
6,index7,index8,inde
x9,index10,index11,i
ndex12,index13,index
14,index15
> )
>
> SELECT
> index1,index2,index3
,index4,index5,index
6,index7,index8,inde
x9,index10,index11,i
ndex12,index13,index
14,index15
> FROM IMPORTDocuments

Hugo Kornelis

2005-04-27, 8:23 pm

On 27 Apr 2005 08:58:28 -0700, rw wrote:

(snip)
>My question is, how can i improve query response while this import in
>going in. I'm considered dropping indexes and recreating after the
>INSERT but won't this affect the ability to search?

Hi rw,

Dropping and recreating indexes can speed up the import, but it will
definitely damage performance of your searches.

A better way is to split up the import process in several smaller batches.
Make sure that each batch is in it's own transaction. This will result in
all locks being released between the batches, instead of being held during
the entire import process.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

ron weston

2005-04-28, 7:23 am

Thanks for that information, I shall read up and have an experiment.

*** Sent via Developersdex http://www.developersdex.com ***

ron weston

2005-04-28, 7:23 am

Hi

Thanks for your reply.

Are you suggesting that perhaps the use of an SQL cursor to import the
records say at 100 a time?

I'd be very interested if anyone has something similar to hand that I
can look at

*** Sent via Developersdex http://www.developersdex.com ***

Hugo Kornelis

2005-04-28, 8:23 pm

On Thu, 28 Apr 2005 01:43:28 -0700, ron weston wrote:

>
>Hi
>
>Thanks for your reply.
>
>Are you suggesting that perhaps the use of an SQL cursor to import the
>records say at 100 a time?
>
>I'd be very interested if anyone has something similar to hand that I
>can look at

Hi Ron,

Not exactly a cursor (that term is used for row by row operations), and
100 at a time is too small - but basically: yes.

Best is to use something in the data that will break up the data in
batches of several thousand rows each (Note: test it with various batch
sizes to find the ideal size). Your code would be something like this:

WHILE (....)
BEGIN
BEGIN TRAN
INSERT INTO Dest (column list)
SELECT column list
FROM Source
WHERE expression to define one batch
-- Insert error handling here
DELETE FROM Source
WHERE expression to define one batch (same as above)
-- Insert error handling here
COMMIT TRAN
END

Another way is to use SET ROWCOUNT to limit the number of rows. I like
this less, as you have to include an ORDER BY that might slow things
down. (But if you omit the ORDER BY, you might end up deleting the wrong
rows).

DECLARE @rc int
SET @rc = 1
WHILE @rc > 0
BEGIN
BEGIN TRAN
INSERT INTO Dest (column list)
SELECT column list]
FROM Source
ORDER BY ordering_column
SET @rc = @@ROWCOUNT
-- Insert error handling here
IF @rc > 0
BEGIN
DELETE FROM Dest
WHERE ordering_column <= (SELECT MAX(ordering_column)

FROM Source
WHERE something to limit this to
columns that are inserted)
-- Insert error handling here
END
COMMIT
END

If the source table can be modified during this process, you'll have to
take extra steps. The code above has the risk that a row is added after
the insert and before the delete. That row might be deleted from the
source without ever getting into destination. If this is an issue for
you, let me know. (But don't expect a prompt reply - I'm off for a short
holiday tomorrow).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Sponsored Links