Queries about Indexing

• Jobs90312

  Mr or Mrs. 500

  Points: 533

  More actions

  September 16, 2022 at 11:43 am

  #4088474

  Hello everyone,

  I am trying to clear my concepts about Indexing in SQL.

  I have a SSIS package. Basic purpose of this package is to fetch XML files data into SQL tables.

  SSIS Steps:

  1. Drop tables if exists.
  2. Create tables.
  3. Altering tables and adding keys (primary, unique and foreign keys constraints).
  4. Temporary nocheck constraints (to add data from files to tables).
  5. Fetch data from multiple XML files into SQL tables created above (using for each loop, XML source and Ole db desination etc).
  6. Running update queries to modify some columns in all the tables.

   

  I have noticed as the rows increases the update query timing also increases even though I have defined that it only applies to those rows which are new.

  How do I improve this timing? Do I need to create more Indexes as compared to the ones already created when creating keys in step 3?

  Also when should one create indexes on tables? Before adding the data or after?

  And what if data updates in the tables? Do we need to re-create indexes as well?

  Thanks.

• Site Owners

  SSC Guru

  Points: 80330

  More actions

  September 17, 2022 at 12:10 pm

  #4088825

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.

• andreas.kreuzberg

  SSCertifiable

  Points: 6191

  More actions

  September 20, 2022 at 12:25 pm

  #4089764

  Hi,

  we don't know how many rows you've got, but it is better, if you create an index after you put all the data in the table.

  If your application will change many data, it maybe helpfull, to do some maintenance on the index. But it depends on your index, on your workload, nobody can tell you what to do, without more information.

  There are good article, how someone discribes, that is sometimes faster, to rebuild an index, than to reorg an index.

  And take a look at the index maintenance from Ola Hallengreen.

  Kind regards,

  Andreas