SQL Server Performance Forum – Threads Archive

How to accelerate the "insert" rate and reduce log

SQL Server 2005 XEON CPU 3.0G MEMORY 2.0G RAID Tow tables: HIS_HTTP_ONLINE_LOG(PARTITION) FOR HISTORY DATA REL_HTTP_ONLINE_LOG(NOT PARTITIONED) FOR EVERYDAY DATA, AND THEY HAVE THE SAME STRUCTURE CREATE TABLE HIS_HTTP_ONLINE_LOG( ID numeric(20,0) NOT NULL, USERID varchar(32) NOT NULL, USERIP varchar(16) NOT NULL, USERPORT numeric(10, 0) NULL, OBJECTIP varchar(16) NULL, OBJECTPORT numeric(10, 0) NULL, HTTPURL varchar(256) NULL, HTTPHOST varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> NULL, HTTPDNS varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> NULL, VISITIME numeric(10, 0) NULL, STARTIME datetime NOT NULL, ENDTIME datetime NOT NULL )……. SELECT * INTO REL_HTTP_ONLINE_LOG SELECT * FROM HIS_HTTP_ONLINE_LOGWHERE 1=2 There are 5 indexes in HIS_HTTP_ONLINE_LOG , There is not one index in REL_HTTP_ONLINE_LOG There are about 5000,000 records in REL_HTTP_ONLINE_LOG everyday, at night it will move into HIS_HTTP_ONLINE_LOG automatically, The data of everyday in REL_HTTP_ONLINE_LOG will be last 90 days. My operations: 1: set the the recovery model of database "simple"; 2: EXEC SP_DBOPTION DB, ‘select into/bulkcopy’, ‘TRUE' 3Â£ÂºINSERT INTO HIS_HTTP_ONLINE_LOG SELECT * FROM REL_HTTP_ONLINE_LOG 4: TRUNCATE TABLE REL_HTTP_ONLINE_LOG ASK: why the step 3 cost so much time ? (about 1 hour) and how can I reduce the transaction logs in this period ? Could you give me some suggestions ? Thanks!

In step3 you are inserting the data into REL_HTTP_ONLINE_LOG then truncating REL_HTTP_ONLINE_LOG table in step4 why? May you are inserting the data into HIS_HTTP_ONLINE_LOG from REL_HTTP_ONLINE_LOG then trucating the REL_HTTP_ONLINE_LOG tabel… To reduce the step3 time: Make sure you have enough allocated space in your db… Mohammed U.

Ad and order by claus to your SELECT * FROM HIS_HTTP_ONLINE_LOGWHERE 1=2 so the inserted data matches the clustering index of your destination table !
This just to avoid pagesplit overhaed

quote:Originally posted by MohammedU In step3 you are inserting the data into REL_HTTP_ONLINE_LOG then truncating REL_HTTP_ONLINE_LOG table in step4 why? May you are inserting the data into HIS_HTTP_ONLINE_LOG from REL_HTTP_ONLINE_LOG then trucating the REL_HTTP_ONLINE_LOG tabel… To reduce the step3 time: Make sure you have enough allocated space in your db… Mohammed U.

REL_HTTP_ONLINE_LOG is for realtime and it works at daytime,
its data will be moved into HIS_HTTP_ONLINE_LOG at midnight I have enough space in my db Now I’m seeking for how to accelerate the "insert " operation Could you tell me how to ?

Correct me if i am mistaken (a) Your History table will have last 90 days history
(b) Your Online table will have 5 million records daily
(c) you are pumping this 5 million rows to Historytable daily at midnight
(d) my question is does this history table contain approx 90 * 5 million rows ?
(f) actually you want a solution to pump this live data from Online table to History table in a faster method ? I think what u can do is use BCP or Bulk Insert. This link is bit confusing, anyhow try in that way. http://www.sql-server-performance.com/bcp.asp Madhu

is:

3Â£ÂºINSERT INTO REL_HTTP_ONLINE_LOG
SELECT * FROM HIS_HTTP_ONLINE_LOGWHERE 1=2[/qoute]
actually:
INSERT INTO REL_HTTP_ONLINE_LOG
SELECT *
FROM HIS_HTTP_ONLINE_LOG
WHERE 1=2 If so, what is the purpose of such command? It will not insert any row.

You sure that is the code? INSERT INTO REL_HTTP_ONLINE_LOG
SELECT * FROM HIS_HTTP_ONLINE_LOGWHERE 1=2
is guaranteed to return no results. Common way to create an empty recordset/dataset in an app. Also can be used to create an empty table if used in a SELECT INTO. Hank

You can use Madhu recomendation to speed up but your will not be FAST BCP, because my guess is you have the indexes on History table… without indexes querying such a big table is pain… To reduce the time and log size you can load the data every hour instead whole day data as your table has datetime column in it and make sure you the index on datetime coulumn…
Mohammed U.

"INSERT INTO REL_HTTP_ONLINE_LOG
SELECT * FROM HIS_HTTP_ONLINE_LOGWHERE 1=2"
is just in order to build a table not for other reason .
madhuottapalam:
Thanks for your help, it seems you have known my question
(d) my question is does this history table contain approx 90 * 5 million rows ?
Yes,the history table will contain approx 90 * 5 million rows . (f) actually you want a solution to pump this live data from Online table to History table in a faster method ?
Yes but I don’t know the following:
"I think what u can do is use BCP or Bulk Insert. This link is bit confusing, anyhow try in that way." why can "bcp and bulk insert " adjust for my question ?
Today I will try it and then give you a answer.

Sorry,
I wrote the step 3 wrong I have corrected it now

Read about BCP and Bulk Insert first in BOL and internet. such bulk data transfer can be done in a better way using BCP or Bulk Insert. Madhu

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by shuai_sh SQL Server 2005 XEON CPU 3.0G MEMORY 2.0G RAID Tow tables: HIS_HTTP_ONLINE_LOG(PARTITION) FOR HISTORY DATA <hr height="1" noshade id="quote"></blockquote id="quote"> Good. Partitioned by range of id column I guess. <blockquote id="quote">quote:<hr height="1" noshade id="quote"> REL_HTTP_ONLINE_LOG(NOT PARTITIONED) FOR EVERYDAY DATA, AND THEY HAVE THE SAME STRUCTURE CREATE TABLE HIS_HTTP_ONLINE_LOG( ID numeric(20,0) NOT NULL, USERID varchar(32) NOT NULL, USERIP varchar(16) NOT NULL, USERPORT numeric(10, 0) NULL, OBJECTIP varchar(16) NULL, OBJECTPORT numeric(10, 0) NULL, HTTPURL varchar(256) NULL, HTTPHOST varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> NULL, HTTPDNS varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> NULL, VISITIME numeric(10, 0) NULL, STARTIME datetime NOT NULL, ENDTIME datetime NOT NULL )……. SELECT * INTO REL_HTTP_ONLINE_LOG SELECT * FROM HIS_HTTP_ONLINE_LOGWHERE 1=2 There are 5 indexes in HIS_HTTP_ONLINE_LOG , There is not one index in REL_HTTP_ONLINE_LOG <hr height="1" noshade id="quote"></blockquote id="quote"> So REL_HTTP_ONLINE_LOG is heap table (without clustered index). I would make one and make sure clustered index is used in the insert query. It is important to insert data in the order of destination table clustered index which should be on id column. <blockquote id="quote">quote:<hr height="1" noshade id="quote"> There are about 5000,000 records in REL_HTTP_ONLINE_LOG everyday, at night it will move into HIS_HTTP_ONLINE_LOG automatically, The data of everyday in REL_HTTP_ONLINE_LOG will be last 90 days. My operations: 1: set the the recovery model of database "simple"; <hr height="1" noshade id="quote"></blockquote id="quote"> Simple recovery model doesn’t help here unless you split insert into batches of 10,000 rows or something similar. If you want to reduce log growth here you need to insert data in smaller batches. I hope you don’t shrink your db and you don’t autoexpand it. The slowness may also come from expansion of transaction log or data files.

Since you have SQL2005 and you are familiar with partitioning, most optimal would be that you to allign the partitions of both tables, so you can just move the actual data by moving the partition. Check outhttp://www.sqlskills.com/resources/Whitepapers/Partitioning in SQL Server 2005 Beta II.htm for more info on partitioning and especialy for this technique ! [8D]

Yes, I read it once and forgot. This is great new feature of sql server 2005.

No doubt, if i had to work on this… would have used 1. Table Partitioning.
2. BCP with batch size at leat 10,000 or more (depending on row size & DISK IO)
3. Put database in simple recovery, if possible. if still insert are slow:
1. Ask for faster Disk to get better Disk IO
2. Having a RAID is best suitable as DB is in Simple Recovery too.. (just for avoiding disk crash and having no TL backups) Deepak Kumar
MVP, MCDBA – SQL Server Disclaimer: This post is provided as is with no rights & warranty for accuracy, for the sake of knowledge sharing only.

If you don’t want to do major modificatins why don’t you copy hourly data in regular hours to history using BCP/DTS instead of whole day one time at night…
Mohammed U.

I have tested by using BCP and I find it will also bring large log
where bcp the data in table test results:
"BCP REL_HTTP_ONLINE_LOG(5 million records ,no index) OUT ……" will bring out 835M datafile
and it took 60s
"BCP the datafile in HIS_HTTP_ONLINE_LOG(6 indexes)……" will bring out log 13.8G MB
and it took 5000s
it is slower than my way
{
1: disable all HIS_HTTP_ONLINE_LOG’s indexes
2:insert into HIS_HTTP_ONLINE_LOG
select *
frin REL_HTTP_ONLINE_LOG
3: enable all HIS_HTTP_ONLINE_LOG’s indexes
the period was less than 30 minutes
}
Now I’m testing alzdba’s suggestion
and later I will give you a result,
I think it will handle my question.

Hello my friends ,I tested from three ways SQL Server 2005
before testing I will clear the log
1Â£Âº
a) disable all indexes
b) insert into HIS_HTTP_ONLINE_LOG
select * from REL_HTTP_ONLINE_LOG
3) enable all indexes
The time of step b) increased along with the increase data of HIS_HTTP_ONLINE_LOG
5million 00:03:45.017
10million 00:10:04.563
15million 00:15:05.233
20million 00:19:47.173
25million 00:24:02.970
30million 00:28:16.343
2Â£Âº
Use BCP
exporting datafile is very quick ,about 60 seconds,
however inporting datafile is very slowly (-bÂ£Â½10000)
Time:01:06:00
Log: 13.8GB
3Â£Âº
HIS_HTTP_ONLINE_LOG uses partition(STARTIME) Â£Â¬and it will bring a partition daily,build 6 indexes INSERT INTO HIS_HTTP_ONLINE_LOG
SELECT *
FROM REL_HTTP_ONLINE_LOG (do not disable any indexes) because STARTIME=getdate()
The time of this step is steady ,about 20 minutes
Log:15GB

]]>