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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 553
  • Last Modified:

Issue With Date Index In SQL Server 2005

We have some ETL loads running to load data into a SQL Server table. This table has an index defined on a date columns

For reporting purpose we are using this table (in join with 13 other tables) to generate a report.

When ever some data is loaded into the table through ETL the indexes for that particular date are getting messed up and the query is just long running for that particular dates.

Once the indexes are rebuilt the query returns results in 7 seconds. Kindly suggest some permanent solution for this issue
0
jsrams
Asked:
jsrams
3 Solutions
 
chapmandewCommented:
you could drop the index before the ETL and add it again afterwards.
0
 
BrandonGalderisiCommented:
Do you have a db maintenance plan that is reindexing your tables?  Any type of large data load (or purge) will not only fragment indexes, but will also mess with your statistics causing queries to run slow.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if this ETL is loading lots of data, it might be considered to drop the index before and recreate it after the load.
0
 
jsramsAuthor Commented:
The data that is loaded through the ETL process is not more thatn 20000 records per day. Still is it preferred to rebuilt the indexes once the data load is done.
0
 
chapmandewCommented:
>>Still is it preferred to rebuilt the indexes once the data load is done.

If they are fragmented.
0

Featured Post

Industry Leaders: 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!

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