| Author |
Topic |
|
chih
Posting Yak Master
154 Posts |
 Posted - 2008-12-10 : 20:53:45
|
| Hi All,For exampleinsert tran_logselect top 500000 * from tranIt always causes timeout when we insert a large number of rows.Is there any way to speed up the insert performance?Thank you in advance |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-10 : 21:02:36
|
| Drop indexes.Use Export/Import Wizard.Reapply index once it is done. |
 |
|
|
chih
Posting Yak Master
154 Posts |
Posted - 2008-12-10 : 21:07:22
|
I cannot drop index becasue some applications need to select/update this table. quote:
Originally posted by sodeep
Drop indexes.Use Export/Import Wizard.Reapply index once it is done.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-10 : 21:44:31
|
| but you can create index after insert. so will it be a problem for dropping it only during insert? |
|
|
|
chih
Posting Yak Master
154 Posts |
Posted - 2008-12-10 : 22:09:11
|
There is no problem to drop the index but it can cause a table lock when creating a index.Also, if I drop the index, it can affect performance in update/select tablequote:
Originally posted by visakh16
but you can create index after insert. so will it be a problem for dropping it only during insert?
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-10 : 22:33:44
|
quote:
Originally posted by chih
There is no problem to drop the index but it can cause a table lock when creating a index.Also, if I drop the index, it can affect performance in update/select tablequote:
Originally posted by visakh16
but you can create index after insert. so will it be a problem for dropping it only during insert?
does that mean you will simultaneously selecting from table while bulk insert is happening? |
|
|
|
chih
Posting Yak Master
154 Posts |
Posted - 2008-12-10 : 22:38:44
|
yes, that's correct.The table can be updated/selected when inserting.If there is no way to speed up insert performance, will it possible to avoid locking issue when inserting and updating happened at the same time?For select, I can use with (nolock). quote:
Originally posted by visakh16
quote:
Originally posted by chih
There is no problem to drop the index but it can cause a table lock when creating a index.Also, if I drop the index, it can affect performance in update/select tablequote:
Originally posted by visakh16
but you can create index after insert. so will it be a problem for dropping it only during insert?
does that mean you will simultaneously selecting from table while bulk insert is happening?
|
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-11 : 14:32:42
|
quote:
Originally posted by chih
yes, that's correct.The table can be updated/selected when inserting.If there is no way to speed up insert performance, will it possible to avoid locking issue when inserting and updating happened at the same time?For select, I can use with (nolock). quote:
Originally posted by visakh16
quote:
Originally posted by chih
There is no problem to drop the index but it can cause a table lock when creating a index.Also, if I drop the index, it can affect performance in update/select tablequote:
Originally posted by visakh16
but you can create index after insert. so will it be a problem for dropping it only during insert?
does that mean you will simultaneously selecting from table while bulk insert is happening?
If you are using Nolock then you won't have locking/blocking issue. You can use Export/Import wizard. |
|
|
|
chih
Posting Yak Master
154 Posts |
Posted - 2008-12-11 : 16:52:39
|
how about updating? it can cause lock when inserting. That is the reason why i want to know any way to speed up insert peformance.Thank youquote:
Originally posted by sodeepIf you are using Nolock then you won't have locking/blocking issue. You can use Export/Import wizard.
|
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-11 : 18:27:54
|
| Yes if other person are updating same records which is involved in row-level inserting could cause deadlocks. |
|
|
|
thiyagu_rind
Starting Member
46 Posts |
Posted - 2008-12-12 : 04:29:30
|
| Hi, Check is there any INSERT Trigger is on the Table. Just Disable the Triggers while inserting, Once the insertion is done enable the trigger. Check the Constraints for the table. If there is any constraints, remove the constraints while inserting. Which will help you in the perfomance. For Update statement refer www.sqlhunt.blogspot.comRegardsThiyagarajan |
|
|
|
foobar22
Starting Member
1 Post |
Posted - 2012-01-11 : 17:59:30
|
| Go sure that you have chosen an appropriate clustered index on the target table, one that does not lead to constant index node splits; an identity column usually is a good choice. If the clustered index is a business key, add an order by <business-key> clause to your insert-from-select.More tips can be found here: [url]http://arnosoftwaredev.blogspot.com/2011/10/tips-for-lightning-fast-insert.html[/url] |
|
|
|
|
Improve insert performance