Index Defragmentation and Statistics Updation

Question

Index Defragmentation and Statistics Updation

VastSQL

SSC-Dedicated

Points: 36144
More actions
April 14, 2010 at 5:56 am

#220482

Hi Experts,
We are having a job which loads millions of record from oracle to sql server via linked server. The job is taking more than 16hours to complete now ,which took only 9 hours initially.
Do we need to rebuild or reorganise index after this process each time? what about update statistics?
Please reply
Thanks In advance

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Index fragmentation or statistics not up to date cannot be the cause of such an issue, if the destination table(s) is only populated by the job. Index fragmentation an outdated statistics can degrade read performance, not write performance.

I suggest that you avoid loading huge amounts of data via linked server. Use SSIS packages or other ETL tools instead.

If you have complex business logic implemented in your linked server query, apply it later: first of all, bring data to your destination server, maybe in a staging table in a different database with simple or bulk logged recovery, with no indexes. This will produce a much faster data transfer. Later you can apply all the transformations you need to push data into the destination table.

I did the same thing when importing millions of rows from AS/400 and the import process went down from 40 to 3 minutes.

Hope this helps

Gianluca

-- Gianluca Sartori

VastSQL SSC-Dedicated Points: 36144 More actions · Answer 2

Thanks a lot Gianluca .

The job was created by ETL team here and we are now in a process of fine tuning that.

I am not sure whether they will accept these changes .Can u please tell more on this .

one more doubt will creating index on tables improves performance??

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Creating indexes will slow down writes, but will improve read performance.

What exactly is unclear to you of the above?

-- Gianluca Sartori

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

Ratheesh.K.Nair (4/14/2010)
one more doubt will creating index on tables improves performance??

During INSERT, the more indexes you have on target table the more overhead you are adding to the process a.k.a. performance gets hurt.

During SELECT, having the appropriate number and setup of indexes a.k.a. "good indexing strategy" will increase performance.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

VastSQL SSC-Dedicated Points: 36144 More actions · Answer 5

Thanks Everyone for the information.

Can you please let me know what best can i do to increase the performance?