May 19, 2011 at 3:33 am
Hi All,
we have requirement Taht we need to insert 4,000,000 in destination table,Destination Table is heap ,no index.
Destination table has following Structure and Source table has also same
CREATE TABLE [dbo].[Desination]
(
[Col1] [nchar] (24) NOT NULL,
[Col2] [int] NOT NULL,
[Col3] [int] NOT NULL,
[Col4] [tinyint] NOT NULL,
[col5] [tinyint] NULL
) ON [PRIMARY]
GO
While i write statement
like
INSERT INTO dbo.Desination
( Col1, Col2, Col3, Col4, col5 )
SELECT Col1 ,
Col2 ,
Col3 ,
Col4 ,
col5 FROM dbo.SourceTable with(nolock)
it consumes 15 seconds average.
Both table are same Database.
can i make it more faster within 5 seconds,i desperately needs it.can i make use of Bulk insert?
Please not from source table col2,col3,col4,col5 will be same value for Destination table. while col1 will have Sessionid for that user,so all 4,000,000 row will have same value in col1.mdf file is on SSD drive while ldf file on SAS.
Thanks in advance.
May 19, 2011 at 4:36 am
This sort of thing is mainly dependent on I/O throughput, although there may be an opportunity to make it minimally logged with some changes (NOLOCK will definitely prevent this as well as being dangerous and inconsistent)
I assume you're on SQL 2008 due to where you've posed your question?
What recovery model is the database using?
May 19, 2011 at 5:05 am
my database has simple Recovery model.
May 19, 2011 at 5:15 am
In which case, changing your WITH(NOLOCK) hint to WITH(TABLOCK) may enable the operation to be minimally logged.
Obviously this depends on whether you can afford for the operation to lock the source table for the duration of the insert.
Otherwise, you would likely need to scale your IO up - you could look into Bulk Insert, but the overhead of streaming out/in to the same SQL Server could easily outweigh the benefits (SSIS is probably the most accesible tool to test this).
You said that the mdf file is on SSD and ndf on SAS. Is the source on one and the destination on another? You'll likely be bottlenecked by the slowest of the two. Where are Transaction Logs?
You should also set statistics IO and Time on and use them to determine whether a change improves/degrades performance rather than using elapsed time as I assume other activity is taking place on the server that competes for resources...
May 19, 2011 at 5:19 am
Why do you need that many rows to be inserted from 1 table to another table in that short amount of time ? What are you going to being using the destination table for ? Once the data has been migrated to the new table are you going to be creating any CI's or NCI's or are you going to keep it as a heap ?
What sort of hardware are you running on ? Is your system using local disks or SAN ? As mentioned in previous post your IO is going to be your hold up. Your SSD's are you using any Raid ?
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
May 19, 2011 at 6:18 am
I'd say that most of this is happening in memory which means that the limitations of hardware are the factors here.
Do I think you can get under 5 secs - unlikely to be honest.
Points.
select into is much quicker than insert into.
SSDs don't always go faster.
Generally everything happens in memory before disk so unless your data sets are large disks don't come into it.
I just ran a few random tests and I'd say 5 secs was good for what you're doing.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 19, 2011 at 6:24 am
colin.Leversuch-Roberts (5/19/2011)
I'd say that most of this is happening in memory which means that the limitations of hardware are the factors here.Do I think you can get under 5 secs - unlikely to be honest.
Points.
select into is much quicker than insert into.
SSDs don't always go faster.
Generally everything happens in memory before disk so unless your data sets are large disks don't come into it.
I just ran a few random tests and I'd say 5 secs was good for what you're doing.
It takes him 15 and wants to cut it under 5. But I agree, it's more an hardware issue now and there's little room for optimization. Also I don't really see why it needs to be that much faster. Can you clarify that part?
May 19, 2011 at 6:27 am
Hi HowardW,
there was typo ,actually we have ldf on SAS, we don't have any ndf.i created ndf on SAS
drive and Test i got 11 seconds,that most of time i get with nolock hint or tablock hint.
i read from SAS drive and write on SSD drive or writing SAS to SSD drive it takes 11 seconds.
May 19, 2011 at 6:27 am
colin.Leversuch-Roberts (5/19/2011)
select into is much quicker than insert into.
Not necessarily true - in SQL 2008 minimal logging applies to more insert statements than just SELECT...INTO, especially into heaps:
Also with Trace Flag 610, some Clustered Index Inserts are also minimally logged (to the OP, this trace flag is not relevent to heaps):
colin.Leversuch-Roberts (5/19/2011)
Generally everything happens in memory before disk so unless your data sets are large disks don't come into it.
Hmm, depends on what frequency the inserts occur, whether all pages are cached in memory and transaction logging most definitely happens on disk.
May 19, 2011 at 6:30 am
Gaurang-Patel (5/19/2011)
Hi HowardW,there was typo ,actually we have ldf on SAS, we don't have any ndf.i created ndf on SAS
drive and Test i got 11 seconds,that most of time i get with nolock hint or tablock hint.
i read from SAS drive and write on SSD drive or writing SAS to SSD drive it takes 11 seconds.
Sorry, it should be INSERT INTO TABLE WITH(TABLOCK) rather than making the select hold a tablock
May 19, 2011 at 6:33 am
colin.Leversuch-Roberts,
i know that select into is much quicker than insert into.and i had doubt that
it is unlikely that i am going to get all records inserted in 5 sec(appending table)
while it performs Best Select into finishes in 2 sec and insert into finishes in 9 sec,
but we have structure we can not use select into ,we have to use insert into.
Thanks.
May 19, 2011 at 6:35 am
Don't even know if this is possible but...
select into + partitioning? Should be under 5 sec.
May 19, 2011 at 6:48 am
Gaurang-Patel (5/19/2011)
colin.Leversuch-Roberts,i know that select into is much quicker than insert into.and i had doubt that
it is unlikely that i am going to get all records inserted in 5 sec(appending table)
while it performs Best Select into finishes in 2 sec and insert into finishes in 9 sec,
but we have structure we can not use select into ,we have to use insert into.
Thanks.
If SELECT INTO takes 2 secs (you really need to have an objective measure over several runs rather than referring to the fastest possible time) then the TABLOCK hint on the insert table should be very similar.
May 19, 2011 at 7:04 am
colin.Leversuch-Roberts (5/19/2011)
I'd say that most of this is happening in memory which means that the limitations of hardware are the factors here.Do I think you can get under 5 secs - unlikely to be honest.
Points.
select into is much quicker than insert into.
SSDs don't always go faster.
Generally everything happens in memory before disk so unless your data sets are large disks don't come into it.
I just ran a few random tests and I'd say 5 secs was good for what you're doing.
HowardW (5/19/2011)
Sorry, it should be INSERT INTO TABLE WITH(TABLOCK) rather than making the select hold a tablock
Yes,i agree with you and and i know Select into is faster most of the cases then insert into
but Structure is such kind that we can not use Select into.
i agree that everything happens in memory first,i forgot that point almost,thanks for remembering me.
let me share my result of while select into performs it's best and insert into performs it's best.in this result i also include tablock as suggested by HowardW fortunately it helps most of time but not everytime but it is good.
Select into: 2 sec.
insert into: 9 sec (without Tablock).
insert into: 3 sec (with Tablock).
Thanks All.
May 19, 2011 at 7:08 am
Ninja's_RGR'us (5/19/2011)
Don't even know if this is possible but...select into + partitioning? Should be under 5 sec.
yes, i will try it after some time, i will like to see result of it as well.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply