August 27, 2010 at 1:48 am
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.
August 27, 2010 at 2:17 am
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;-)
August 27, 2010 at 2:21 am
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;-)
August 27, 2010 at 7:59 am
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
August 27, 2010 at 8:53 am
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
August 27, 2010 at 11:15 pm
Thank you for the answer.
Jags
August 28, 2010 at 9:45 am
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
August 30, 2010 at 6:02 am
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.
August 30, 2010 at 11:14 am
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
August 30, 2010 at 7:16 pm
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
August 31, 2010 at 5:36 am
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 .
August 31, 2010 at 12:32 pm
Ordering always helps too...
September 1, 2010 at 10:14 pm
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;-)
September 1, 2010 at 11:24 pm
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....
September 1, 2010 at 11:29 pm
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;-)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply