Indexes and Performance - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Indexes and Performance

Author

Topic

mufasa
Yak Posting Veteran

62 Posts

Posted - 2004-08-17 : 14:15:24

Hi

I have a SP that truncates and inserts data into a table.

The original table it is reading from has over 7 million records.

My result returns a little over 2 million records.

The table has one clustered index, using five columns.

My sort cost is over 38% and wanted to try and reduce it.

One idea I had was to drop the index from the table and recreate it after the insert.

Time to run the SP ranges between 6-10 minutes.

below is the code that I am using

truncate table DetailSales
insert into DetailSales
SELECT TOP 100 PERCENT dbo.Rep_Calendar.Period AS Period,
dbo.DYDFIL.dyd_category AS CAT,
dbo.DYDFIL.dyd_class AS CLASS,
dbo.DYDFIL.dyd_style AS STYLE,
dbo.DYDFIL.dyd_supplier AS Supplier,
dbo.T_COLOR.color_code,
dbo.DYDFIL.dyd_size_code AS SizeCode,
dbo.DYDFIL.dyd_season AS Season,
dbo.Rep_Calendar.[Month],
dbo.Rep_Calendar.Week,
dbo.T_COLOR.color_desc AS COLOR,
dbo.DYDFIL.dyd_size_desc AS SizeDesc,
SUM(dbo.DYDFIL.dyd_qty) AS QTY,
SUM(dbo.DYDFIL.dyd_retail) AS Retail,
SUM(dbo.DYDFIL.dyd_sold_price) AS Sold,
SUM(dbo.DYDFIL.dyd_cost) AS Cost,
SUM(CASE WHEN ABS(dbo.DYDFIL.dyd_retail) > ABS(dbo.DYDFIL.dyd_sold_price) THEN dbo.DYDFIL.dyd_qty ELSE 0 END) AS MkDnQty

FROM dbo.DYDFIL INNER JOIN
dbo.Rep_Calendar ON dbo.DYDFIL.dyd_date = dbo.Rep_Calendar.CivilDate INNER JOIN
dbo.T_COLOR ON dbo.DYDFIL.dyd_color = dbo.T_COLOR.color_code INNER JOIN
dbo.[This Year Last Year1] ON dbo.Rep_Calendar.[Year] >= dbo.[This Year Last Year1].LY

GROUP BY
dbo.Rep_Calendar.Period,
dbo.DYDFIL.dyd_category,
dbo.DYDFIL.dyd_class,
dbo.DYDFIL.dyd_style,
dbo.DYDFIL.dyd_supplier,
dbo.T_COLOR.color_code,
dbo.DYDFIL.dyd_size_code,
dbo.DYDFIL.dyd_season,
dbo.Rep_Calendar.[Month],
dbo.Rep_Calendar.Week,
dbo.T_COLOR.color_desc,
dbo.DYDFIL.dyd_size_desc

Thanks for any suggestions

Mufasa

jharwood
Starting Member

41 Posts

Posted - 2004-08-17 : 15:17:12

From BOL....
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain.

dbcc dbreindex or dbcc indexdefrag may help after truncating.

mufasa
Yak Posting Veteran

62 Posts

Posted - 2004-08-17 : 15:34:16

I truncate the table because I need the indexes that exist in the table format.

I was told that when you try to insert 2 million plus records into a table with indexes, it will take a while to sort the records before it inserts them.

I wanted to know if it would run quicker if I were to remove the indexes before the insert, then recreate them after the insert?

Maybe there would be no difference, but I need to start stream lining my jobs as they are starting to interfere (locks) with other jobs.

My job window (time when no other people or jobs are running) is getting shorter

Thanks

Mufasa

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-17 : 15:35:58

quote:
Originally posted by mufasa

I wanted to know if it would run quicker if I were to remove the indexes before the insert, then recreate them after the insert?

Yes. Often times, it is faster to do the load if you first drop the indexes, do the load, then recreate the indexes.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources