• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 60
  • Last Modified:

ssis 2008 slower on inserting records?

I have several ssis jobs which I use the Scripting task.  In the scripting task I have vb.net that use to insert into a sql 2005 db and now we insert into a 2008 Database.  Now the process takes two to three times as long.  from 1 hour to 3 hours.  is there a better way to insert the data via the scripting task that 2008 uses better than 2005. here is an example, thanks.


i loop thru this until all records in file are inserted (possibly up to 500,000 records.)

I just talked to a developer next to me & he only inserts one record at a time & it's still way slower than it was on 2005.


    strsql.Append("insert into [dbo].[tblDaily] (DATE,STORE_ID,MDN,ESN,PLAN_ID,IB_SMS_COUNT,FREE_IB_SMS_COUNT,IB_SMS_REV,INCL_IB_SMS_COUNT,OB_SMS_COUNT,FREE_OB_SMS_COUNT,OB_SMS_REV,INCL_OB_SMS_COUNT,PEAK_MINS,FREE_PEAK_MINS,PEAK_REV,INCL_PEAK_MINS,OFF_PEAK_MINS,FREE_OFF_PEAK_MINS,OFF_PEAK_REV,INCL_OFF_PEAK_MINS,DA_COUNT,DA_REV,DATA_RINGTONE_COUNT,DATA_RINGTONE_REV,DATA_WALLPAPER_COUNT,DATA_WALLPAPER_REV,DATA_PICTURE_MAIL_COUNT,DATA_PICTURE_MAIL_REV,DATA_GAMES_COUNT,DATA_GAMES_REV,DATA_BROWSING_COUNT,DATA_BROWSING_REV,MONTHLY_SERVICE_FEES,WAIVED_MONTHLY_SERVICE_FEES,SPT_PEAK_EXP,SPT_OFF_PEAK_EXP,SPT_LD_EXP,SPT_INSMS_EXP,SPT_OUTSMS_EXP,SPT_MRC_EXP,TEL_MRC_EXP,TEL_PEAK_EXP,TEL_OFF_PEAK_EXP,TEL_INSMS_EXP,TEL_OUTSMS_EXP,QUAL_MRC_EXP,QUAL_IVR_EXP,[TOTAL REVENUE],[TOTAL EST COST],PROFIT,MARGIN,ActivNo,DOWNLOAD_KB,DATA_BROWSING_UNITS,[File Name], QR_INBOUND_SMS_COUNT, QR_INBOUND_SMS_CHARGES, QR_OUTBOUND_SMS_COUNT, QR_OUTBOUND_SMS_CHARGES, QR_PEAK_MINS, QR_PEAK_CHARGES, QR_OFF_PEAK_MINS, QR_OFF_PEAK_CHARGES, QR_DIR_ASSIST_COUNT, QR_DIR_ASSIST_CHARGES, QR_FREE_PEAK_MINS, QR_FREE_OFF_PEAK_MINS, QR_FREE_INBOUND_SMS_COUNT, QR_FREE_OUTBOUND_SMS_COUNT, QR_PICTURE_MAIL_COUNT, QR_PICTURE_MAIL_CHARGES ) ")
           

 While (index < 50 And (Not sr1.EndOfStream))

lineData = sr1.ReadLine().Split(Convert.ToChar(9))

               
      strsql.Append("select '" + DBNull.Value + "','" + lineData(1).ToString() + "','" + lineData(2).ToString() + "','" + lineData(3).ToString() + "','" +
lineData(4).ToString() + "'," + ibsmscount.ToString() + "," + freeibsmscount.ToString() + "," + ibsmsrev.ToString() + "," + inclibsmscount.ToString + ","
+ obsmscount.ToString() + "," + freeobsmscount.ToString() + "," + obsmsrev.ToString() + "," + inclobsmscount.ToString + "," + peakmins.ToString() + "," +
freepeakmins.ToString() + "," + peakrev.ToString() + "," + inclpeakmins.ToString() + "," + offpeakmins.ToString() + "," + freeoffpeakmins.ToString() + ","
+ offpeakrev.ToString() + "," + incloffpeakmins.ToString() + "," + dacount.ToString() + "," + darev.ToString() + "," + dataringtonecount.ToString() + ","
+ dataringtonerev.ToString() + "," + datawallpapercount.ToString() + "," + datawallpaperrev.ToString() + "," + datapicmailcount.ToString() + "," +
datapicmailrev.ToString() + "," + datagamescount.ToString() + "," + datagamesrev.ToString() + "," + databrowsingcount.ToString() + "," + databrowsingrev.ToString()
 + "," + monthlyservicefees.ToString() + "," + waivedmonthlyservicefees.ToString() + "," + sptpeakexp.ToString() + "," + sptoffpeakexp.ToString() + "," +
sptldexp.ToString() + "," + sptinsmsexp.ToString() + "," + sptoutsmsexp.ToString() + "," + sptmrcexp.ToString() + "," + telmrcexp.ToString() + "," +
telpeakexp.ToString() + "," + teloffpeakexp.ToString() + "," + telinsmsexp.ToString() + "," + teloutsmsexp.ToString() + "," + qualmrcexp.ToString() + "," +
qualivrexp.ToString() + "," + totalrevenue.ToString() + "," + totalestcost.ToString() + "," + profit.ToString() + "," + margin.ToString() + "," +
ActiveNo.ToString + "," + Download_KB.ToString + "," + DataBrowsingUnits.ToString + ",'" + filename + "'," + strExtra16Cols + " union all ")

               

index = index + 1


End While




                indexlastdot = strsql.ToString().LastIndexOf("union all")
                strsqlfinal.Append(strsql.ToString().Substring(0, indexlastdot - 1))
                sqlcmd.CommandText = strsqlfinal.ToString()
                sqlcmd.Connection = sqlcon
                sqlcmd.ExecuteNonQuery()

'  Loop back thru and do the next batch of records until done...
0
purdyra1
Asked:
purdyra1
1 Solution
 
Jason Yousef, MSSr. BI DeveloperCommented:
Hi,
there're many reasons your insert might be slow.

check these tips:
http://arnosoftwaredev.blogspot.com/2011/10/tips-for-lightning-fast-insert.html

Also

- If the SQL server is local, consider using SQL Server destination instead of OLE DB Destination.

-Consider indexes fragmentation and performance when inserting high volume of data.

-Use “Fast Load” when using OLEDB, it uses BULK INSERT and would be faster.

-Optimize the packet size in your connection managers to 32K (32767) and that’s the fastest option.


http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_6615-SSIS-Performance-considerations.html
0
 
nishant joshiTechnology Development ConsultantCommented:
I want to know that why you are using vb.net code.
as this task can be achieved using ssis-Dataflow task,

Regards,
Nishant
0
 
Jason Yousef, MSSr. BI DeveloperCommented:
You might also revise your vb.net code to use bulk copy, check that link

http://www.dotnetcurry.com/ShowArticle.aspx?ID=323
0
 
purdyra1Author Commented:
the reason i am using vb.net is there is a boatload of custom stuff we do to each field prior to being loaded.  i am sure it could be done thru the gui but it  just makes it easier doing vb.net since we are .net developers.
0
 
ValentinoVBI ConsultantCommented:
I think you should consider the following: create a Data Flow Task (DFT) with your file as source (using the Flat File Source is it's a CSV file), followed by a Script Component with your custom data manipulation (code can be copied from your current script) and at the end an OLE DB Destination.

It may require some experimenting if you're new to the DFT but I ensure you won't regret it.  As huslayer mentioned, use "fast load" on the OLE DB destination.  You'll see your data flying through the flow!

PS: I've got a developer background ;)
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now