Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


staging table and indexes


staging table and indexes

Author
Message
pablavo
pablavo
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
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
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2926 Visits: 4076
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;-)
pablavo
pablavo
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
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 ?
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2926 Visits: 4076
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;-)
Rob Schripsema
Rob Schripsema
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2319 Visits: 11042
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
Propack, Inc.
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2926 Visits: 4076
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;-)
chrisfradenburg
chrisfradenburg
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1732 Visits: 2057
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.
Rob Schripsema
Rob Schripsema
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2319 Visits: 11042
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
Propack, Inc.
chrisfradenburg
chrisfradenburg
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1732 Visits: 2057
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.
RalphWilson
RalphWilson
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search