Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Private
  • Views: 209
  • Last Modified:

Execute SQL Task Extremely Slow

I have a execute sql task in ssis and it performs poorly.  At any given time it's inserting between 500 - 50K records.  Its taking over 2 minutes each time.  Is there a way to improve the performance?  I'm using an ADO.Net connection.  Any suggestions and help is greatly appreciated.
0
LeVette Alexander
Asked:
LeVette Alexander
  • 4
  • 3
  • 2
  • +2
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
I'll go out on a limb here and state that it would greatly help if you could copy-paste the T-SQL that it's executing into this question.  And if it's just a SP/function name, copy-paste that SP/function.
0
 
Scott PletcherSenior DBACommented:
Be sure to do all the INSERTs as part of a single transaction rather than using a separate transaction for each INSERT; all separate trans would be massive overhead.
0
 
James ElliottManaging DirectorCommented:
How many records in your table and is it indexed in any way?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Lack of information in this question.
Where are the data coming from?
0
 
LeVette AlexanderDatabase AdministratorAuthor Commented:
code is simple - its a stored procedure that resembles the below

INSERT INTO TableA
 (ColumnA, ColumnB, ColumnC)

SELECT ColumnA, ColumnB, ColumnC From Table

The table has 500 million records and the table is indexed.  The data comes from other tables and is inserted into a large table.  There is one clustered index and 8 non clustered indexes with the fill factor set to 50 for each index.

My assumption was that I could quickly insert records into the table using ssis execute sql task.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
TableA is being recreated each time the package run or already exists and you are only appending the records?
The indexes has been created in TableA or in the source table?
0
 
LeVette AlexanderDatabase AdministratorAuthor Commented:
The table (TableA) already exists (500 million records).  I'm appending records.  The indexes are on TableA
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. That justifies the time that is taking.
Without an archive solution or a purge process, the only way I can see by now is to partitionate the TableA.
0
 
LeVette AlexanderDatabase AdministratorAuthor Commented:
Thanks Victor. I guess I"m just stuck until we partition the table.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Couple of random thoughts, as 500 million rows is a lot, even with three columns and indexed.

Are there existing rows in the target table?  If so, you might be better served partitioning that table, then doing your insert into that partition, which will speed things up.

Is it an option to drop all indexes in the target table, do your INSERT, then re-create those indexes?
This would eliminate the overhead of also having to INSERT into the index(es) as you're inserting data.
Might want to throw this into a loop so that it inserts only 50k / 100k / whateverk rows at a time, just in case there is a limiting buffer somewhere.   A side benefit of doing this is if with every loop you write the results into a 'log' table, then when the job is executing on a scheduled basis (i.e. not using the designer) you can view current progress by looking at the table.
Is doing this in a data pump an option?  SSIS data pumps can easily 'stream' this data to avoid any buffers.
In your SSIS package are you running this multi-threaded, and can you max out the number of threads on the processor?
Can whatever process that populates the source table be re-written to directly populate the target table, eliminating an 'intermediary hop' of the source table?
0
 
LeVette AlexanderDatabase AdministratorAuthor Commented:
Jim, there is only one table.  I'm not using SSIS in the traditional  manner.  I'm simply inserting records into one table.  I think Batching might be the answer w/o SSIS.

Thanks for your insight.
0
 
Scott PletcherSenior DBACommented:
>> There is one clustered index and 8 non clustered indexes with the fill factor set to 50 for each index. <<

With only 3 or 4 columns in the table, that's an extremely high number of indexes.  Verify that you have the best clustering key and that a fill factor that low is really warranted, esp. on the clustered index.  You're more than doubling the size of that table when (if) you rebuild it with a fill factor that low.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now