Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

staging table and indexes Expand / Collapse
Author
Message
Posted Wednesday, March 24, 2010 4:46 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 13, 2010 2:28 AM
Points: 18, Visits: 77
Hi there,

I'd like to ask for some advice.

I have a Main table that is used for reporting. Every 30 minutes a staging table is updated with the latest data and the data is modified at this stage via an SP. This allows my main table to be fully available during this time

The data is now ready to be imported to the main table for updated reporting. there are about 10000 records and I know this is not alot. However, there is a UNION ALL on another table with over 200,000 records so speed is essential.

I would like to ask what the best way is to keep the indexes working well with no fragmentation. Does it make a difference if I rebuild indexes on my stage table before the data is imported to the main table? or would I have to rebuild indexes on the main table when the data is imported? or will the main table be that effected if I don't worry about rebuilding or defraging the indexes?

Thanks in advance for any help

Post #888806
Posted Wednesday, March 24, 2010 6:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
The indexes will help on main table in reportting so rebuilding can be done after importing
but why do you want to rebuild indexes after very import?
And do you see any performance degrade on reporting side?
pablavo (3/24/2010)
I don't worry about rebuilding or defraging the indexes?

and why you dont want to worry about rebuilding or defraging ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #888859
Posted Wednesday, March 24, 2010 6:40 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 13, 2010 2:28 AM
Points: 18, Visits: 77
Hi Bhuvnesh, Thanks for responding

you wrote:
The indexes will help on main table in reportting so rebuilding can be done after importing
but why do you want to rebuild indexes after very import?


I should have added that the main table will be truncated before every import and the new data added to it. So you suggest that it's a good idea to rebuild after importing the data to the main table. Does your question ask me why I want to rebuild after every import? I don't know if I do, that's why I set up the thread... Since the table is truncated every 30 minutes and new data added, will the indexes have to be rebuilt or would the table be fine since it's truncated?

You wrote:
And do you see any performance degrade on reporting side?


As far as the main table is concerned, that is truncated and has a data import every 30 minutes, I'm unsure if there will be performance degradation. the other table (that UNIONS the Main table) that is updated nightly will have it's indexes rebuilt and that only happens out-of-hours. it's important that the main table is available as much as possible during the day but also effecient.

Do you think I will not need to rebuild after every import or at all?







pablavo (3/24/2010)
--------------------------------------------------------------------------------
I don't worry about rebuilding or defraging the indexes?

and why you dont want to worry about rebuilding or defraging ?
Post #888886
Posted Wednesday, March 24, 2010 7:38 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
pablavo (3/24/2010)
I should have added that the main table will be truncated before every import and the new data added to it.
NOw that makes sense that rebuilding indexes with every import.
So i think you should build indexes with fillfactor = 100 and then import the data. and why Fillfactor = 100 coz it will make sql server to traverse less data pages.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #888963
Posted Thursday, March 25, 2010 2:31 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, March 31, 2014 3:08 PM
Points: 1,906, Visits: 10,941
pablavo (3/24/2010)


I should have added that the main table will be truncated before every import and the new data added to it. So you suggest that it's a good idea to rebuild after importing the data to the main table. Does your question ask me why I want to rebuild after every import? I don't know if I do, that's why I set up the thread... Since the table is truncated every 30 minutes and new data added, will the indexes have to be rebuilt or would the table be fine since it's truncated?



If you're talking about a non-clustered index, I think I would drop the index after truncating the table, then import the new data, then re-create the index. It would speed up the import (possibly considerably, depending on the index), and the index would be rebuilt as efficiently as SQL was able to.

If it is a clustered index, keep the index in place during import, but see if you can order the incoming data in the same order as the clustered index would order it, so you'll be filling pages in sequence.


Rob Schripsema
Accelitec, Inc
Post #890236
Posted Friday, March 26, 2010 12:24 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
Interesting
Rob Schripsema (3/25/2010)
If you're talking about a non-clustered index, I think I would drop the index after truncating the table, then import the new data, then re-create the index.

Can you explain, why Non-clustered index after import ?


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #890398
Posted Friday, March 26, 2010 7:44 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 7:13 AM
Points: 1,634, Visits: 1,964
It's frequently quicker to create a non-clustered index with the data already in the table than to do a bulk insert and reindex.
Post #890628
Posted Friday, March 26, 2010 10:00 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, March 31, 2014 3:08 PM
Points: 1,906, Visits: 10,941
Bhuvnesh (3/26/2010)
Interesting

Can you explain, why Non-clustered index after import ?


As was just said, it's frequently quicker to let SQL index the entire table once the data is in it, building just the index pages, than to make it write a record to a data page and then turn around and have to find and update an index page as part of the insert.

With a clustered index, on the other hand, the data is being written in the same set of pages as the index, so it would be more efficient (and this is an educated guess, I don't have benchmarks to verify this) to have the clustered index in place when inserting data. And if the data was being inserted in the same order it will be physically stored in the clustered index, so much the better.

Maybe others can verify this, or correct my thinking....


Rob Schripsema
Accelitec, Inc
Post #890786
Posted Friday, March 26, 2010 2:32 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 7:13 AM
Points: 1,634, Visits: 1,964
I think that will depend largely on how closely the order of the inserted data matches the order of the clustered index. If it matches then SQL will be writing sequentially so there's no concern about there not being space where it wants to put the new row. If it doesn't match then there's the possibility that there won't be space where it needs to put it and fragmenting will happen. One thing that I don't have time to right now but would be interesting to is seeing as this is all happening in one insert whether or not SQL will order the rows in the order it needs to write them if no sort order is applied.
Post #891102
Posted Monday, March 29, 2010 4:15 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 11, 2011 1:21 PM
Points: 50, Visits: 162
cfradenburg (3/26/2010)
. . . One thing that I don't have time to right now but would be interesting to is seeing as this is all happening in one insert whether or not SQL will order the rows in the order it needs to write them if no sort order is applied.


I believe that what will happen is that the data being imported will tend to be in the "order of entry" rather than SQL Server doing something smart and figuring out what ORDER BY clause should have been provided.

In any case, if you supply the ORDER BY clause and it is not needed, then the worst it should do is occupy your fingers a bit longer as you type; however, if it is needed, it could significantly improve performance during and after the import.


Ralph D. Wilson II
Development DBA

"Give me 6 hours to chop down a tree and I will spend the first 4 sharpening the ax."
A. Lincoln
Post #892286
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse