How to Tune Data Inserts

Question

How to Tune Data Inserts

Mr.SQL DBA

SSC-Addicted

Points: 496
More actions
August 27, 2010 at 1:48 am

#140395

Hi SQL Experts,
In Performance tuning we can only tune the database only for the data retrieval ie for 'select' queries by creating indexes; query type etc....
How can we tune the performance for data insertions into a particular table ie 'insert' statements.
Thanking you in Advance,
Jags.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

insert solely depends on the selectivity of the select command how much data is being matched with the keys( specially clus key) of the destination table.

Post you r insert along with table and index definition. we can help you there

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Additionally , you can also use BATCH wise insertion

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Mr.SQL DBA SSC-Addicted Points: 496 More actions · Answer 3

Thank you for your reply.

What exactly my question is....

Scenario 1:-

if we are selecting some 10000 records in a praticular table with out an index it take some 10 to 20 seconds. like "select * from Table name".

To imporve the performance of this query we make an index to solve this performance issue to bring the time to 1 or 2 seconds.

like that..................

Scenario 2:-

if we are inserting some 10000 records in a praticular table with out an index or even with an index it take some 10 to 20 seconds.

What can i do now to bring the insertion time and improve the performance. is there any other options other than changing the structure of the table.

Thanking you in Advance

Jags

WayneS SSC Guru Points: 95460 More actions · Answer 4

You've actually got two things going on:

1. The selection of the records to insert, and

2. The actual insertion of records into the table.

As everyone else has already covered, the use of indexes on the selection part will speed things up.

However, the physical insertion will actually be slower with more indexes - every index has to be updated, perhaps splitting the leaf nodes, etc. On the table being inserted into, generally the less indexes, the better. This is why you will sometimes see, for a batch insert process, to first drop the existing indexes, add the data, and then put the indexes back at the end of the process.

Obviously, there needs to be a balance.

I wouldn't worry about the insertion issue - SQL handles this pretty fast, and only on large tables with lots of data insertion/update/delete activity is this going to be an issue. So, focus on optimizing the selects that are gathering the data to be inserted.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Mr.SQL DBA SSC-Addicted Points: 496 More actions · Answer 5

Mr.SQL DBA

SSC-Addicted

Points: 496

August 27, 2010 at 11:15 pm

#1213386

Thank you for the answer.

Jags

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

WayneS (8/27/2010)
I wouldn't worry about the insertion issue - SQL handles this pretty fast, and only on large tables with lots of data insertion/update/delete activity is this going to be an issue. So, focus on optimizing the selects that are gathering the data to be inserted.

I may be taking this out of context and I apologize if I did...

I'd worry very much about the insertion issue as it's a typical hotspot for application time-outs.

I've recently run into a problem where a developer had a very slow SELECT and made an index the made it lightning quick. As soon as he promoted the index to production, the web site started reporting massive amounts of time-outs and failures and, obviously, it was because of the new index. The first column of the index had a cardinality of only 2 and had to do an extent split every time a new row was inserted. Correctly rearranging the columns in the index worked well for the troublesome SELECT and kept INSERTs from timing out. The table really wasn't that big... it starts out as a "new" table every morning and grows throughout the day... typically, the table never grows to more than a quarter million rows during the day so it's not a "big" table.

Similar things can happen with the clustered index if it's in an order that's different than the insert order of new rows.

Bottom line is that tuning of INSERTs is just as important as tuning SELECTs, IMHO.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

homebrew01 SSC Guru Points: 55548 More actions · Answer 7

We've got a few instances of large inserts occuring during production hours that cause blocking. I'm looking at breaking them up into smaller insert batches and/or dropping & recreating the indexes.

Mr.SQL DBA SSC-Addicted Points: 496 More actions · Answer 8

Jeff,

You are absolutely correct. Tuning of Inserts is as Important as Tuning the Selects.

so i want any options or any types or any methods or any scenarios to Tune the Insert Queries other than changing the structure of the table or else dropping the existing indexes(as if it is practically not possible in production servers)

Thanking you,

Jags

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 9

Mr.SQL DBA (8/30/2010)
Jeff,
You are absolutely correct. Tuning of Inserts is as Important as Tuning the Selects.
so i want any options or any types or any methods or any scenarios to Tune the Insert Queries other than changing the structure of the table or else dropping the existing indexes(as if it is practically not possible in production servers)
Thanking you,
Jags

I gave you one scenario. I imagine there are thousands more including running into blocking as someone else said. You probably can't avoid all of the possible problems you could run into. About the only thing you can do is to evaluate each circumstance and do the "It Depends" analysis to figure out what is best for that cirsumstance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ramji29 Hall of Fame Points: 3832 More actions · Answer 10

Yeah there was one more thought on this....

it will be better if the data selected (from other table or tables for inserting into my table) shud be ordered in the order of cluster indexed column of the destination to avoid more of page splits .

Rick.Cornell.SME SSCrazy Eights Points: 8597 More actions · Answer 11

Rick.Cornell.SME

SSCrazy Eights

Points: 8597

August 31, 2010 at 12:32 pm

#1214543

Ordering always helps too...

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 12

Twinsoft SME (8/31/2010)
Ordering always helps too...

Not always

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Ramji29 Hall of Fame Points: 3832 More actions · Answer 13

Bhuvnesh (9/1/2010)
Twinsoft SME (8/31/2010)
Ordering always helps too...
Not always

May be not always but if ordering is done properly then ... in almost all the cases....

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 14

Ramji29 (9/1/2010)
Bhuvnesh (9/1/2010)
Twinsoft SME (8/31/2010)
Ordering always helps too...
Not always
May be not always but if ordering is done properly then ... in almost all the cases....

Mostly Sql optimizer do a SORT operation (if required data is not in order ) according to the Clus index of destination table

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)