Index Performance

Hi,

I am trying to improve performance in some statements i run against a 15M rows table.

I have batchs that inserts about 6M rows and some that insert 500K to 1M rows...

Statistically i have a match of 1 to 3.

I am currently disabling all indexes in the destination table and running the insert batch's.

After it finishes the statements i execute the rebuild of all indexes...

This takes up to 4 hours to run... ( 3 of them used just to rebuild the indexes )

My question is: Am i using the best aproach? Or is it better to leave the indexes enabled and do the inserts batch with them?

I Hope i was clear enough

Best Regards,

Yes, that is most often the best approach. It is much faster to add data to a heap, and then to reorganize the data just once. Generally speaking, doing large quantities of inserts would already likely fragment the indexes and need to be rebuilt anyhow.

|||

No, you do NOT want to leave the indexes 'enabled' during the imports.

You may wish to try the following to determine if performance can be improved.

If your situation allows:

switch the database RECOVERY model to 'Simple',

Drop the indexes,

Make the imports in smaller 'batches' (Perhaps 100K rows), Looping until complete

ReBuild the Indexes

switch back to FULL recovery when finished,

and then make a FULL BACKUP.

Be sure that you Build any Clustered Indexex first!

|||

Thats exactly what i am doing...

DB's are in simple mode forever.. because we dont need transactions.

Indexes are not droped but instead are disabled...

I execute the batch (dont use loops because performance is ok - i only use loops for delete's )

I execute Rebuild with ALL on Table_name parameter.

So i guess theres no way around.... I have to wait 3 hours for the indexes to get rebuild all the time... even if i only insert 1 row...

Also the post above speakes on Reorganizing indexes but i guess he meant Rebuilding no? Changing that amount of rows would definitly need a rebuild right?

Regards,

|||

You can also try, if possible, importing to a dummy table, leaving the original table in tact, then later insert into the real table.

In this case, you have 1 hour to import + 1 hour to INSERT = 2 hours

You've just saved 2 hours on waiting for the indexes to be re-enabled.

Just my twist on it,

Adamus

|||

Sorry?

But my batch is already inserting from a temporary table into a final one... So you would do it with the indexes up?

Regards

|||

So you are bulk INSERTING not bulk IMPORTING.

Correct?

Adamus

|||

Yes sorry... it was my english that was right in the first post and wrong in the second when i mentioned import instead of insert

To take out any doubts i am bulk inserting from one temporary table into a final one...

You are true if i was importing anyway

Best Regards,

|||

Are are overwriting or appending to the existing records in the final table?

What I'm getting at is, instead of touching the records for the sake of reports and processing, why not rename the temp table to the name of the final table, if it exists in SQL, and create a new temp table?

If this is possible, add the indexes to the new table.

If you are overwriting:

|

V

Drop Final Table

|

V

Rename Temp Table to Final Table

|

V

Create New Temp Table

|

V

Apply indexes to new final table

Adamus

|||

>>Also the post above speakes on Reorganizing indexes but i guess he meant Rebuilding no? Changing that amount of rows would definitly need a rebuild right?<<

No,if you are just adding rows to a table, you can let SQL Server reorganize the pages of the index instead of doing a full rebuild. It can be done while others are using the table. In 2005, ALTER INDEX...REORGANIZE, in 2000, it is a DBCC.

>>So i guess theres no way around.... I have to wait 3 hours for the indexes to get rebuild all the time... even if i only insert 1 row... <<

Now here is a tricky question. You would be silly to drop all of the indexes and rebuild the table for a single row insert (unless your only job is to do this, and while you wait you can read books on database design. Then it would be a good thing

Seriously, all the answers we gave you were based on a BULK load of the table. Meaning you were the only user at the time, pumping in a very high percentage of rows. Your original question stated:

>>

I am trying to improve performance in some statements i run against a 15M rows table.

I have batchs that inserts about 6M rows and some that insert 500K to 1M rows..<

You are significantly changing the shape of the table if you are adding or even changing keys of .1 of the table. So the fastest thing to do in this case is like to employ one of the techniques mentioned, and use a BULK operation and rebuild indexes.

We haven't even talked about what you are clustering on, or how many indexes, all important things to discuss, if you are only adding (or changing) far fewer rows. Testing is the key. I would suggest you write at least two or three scenarios and test.

If rowcount < N, then just insert directly.

If rowcount >= N and < M, disable these indexes, insert your rows, then add them back,

if rowcount >= M, disable/drop all indexes (including Clustered) and SSIS the rows in.

Especially if you are running short of time for your (presumably) off hours processing to take place

|||

Hi Arnie,

I can see the benefits of switching to "bulk-logged" recovery model, but siwtching to "simple" will break the sequence of transaction log backups.

Switching from the Full or Bulk-Logged Recovery Model

http://msdn2.microsoft.com/en-us/library/ms190203.aspx

AMB

|||

Alejandro,

I didn't indicate, but assumed (and yes, I know that one shouldn't assume) that a full backup would be made before changing the recovery model

I should have been more directly clear on that point.

Your point about the chain of backups is very important. Thanks for adding that to clarify the conversation.

|||

I guess you are using SS 2005. Do a test without disabling the indexes, because SS can decide to use a strategy sometimes

called index-at-a-time, were instead updating each index by each row inserted, SS gather a group of rows, sort them per each

index and merge them. The advantage is that SS will not access same index page more than once.

You can read about this estrategy in the book:

Inside Microsoft SQL Server 2005: The Storage Engine

by Kalen Delaney

Chapter 7 - Index Internals and Management

Table-Level vs. Index-Level Data Modification

Tame Bulky Inserts

By: Kalen Delaney

http://www.sqlmag.com/Articles/Index.cfm?ArticleID=25521&DisplayTab=Article

AMB

|||

Well my table suffers changes in adding and removing records... so this is alot of change happening here... a complete transformation of the final result.

So i think rebuild is definitly a must :/ I didn't thought it would take that amount of time!

Regards

Index on XML to improve speed doesn't work, any suggestion?

I have a xml field in my member table, it stores data like

<field name="n1">v1</field><field name="n2">v2</field> ....

When I retrieve 10 fields from the xml field like

select exfield.value('(field[@.name="n1"]/.)[1]','nvarchar(max)'),exfield.value('(field[@.name="n2"]/.)[1]','nvarchar(max)')..... from members

when returns 2200 records, it takes 37 seconds,

I tried to add different xml index on the xml field, but none of them help.

What do you think if there is an index can help to arthieve that?

thanks

I highly suggest that you read the excellent whitepaper at http://msdn2.microsoft.com/en-us/library/ms345118.aspx. You need to taylor your indexes and queries to your needs and this paper can help you make those decisions.

Regards,

Galex

Index on a varchar column?

In an effort to improve the speed of queries against my main table,
I'll be indexing a column whose data type is varchar(50).

Would I be better off (better performance) if I changed the column's
data type to some numeric type? I would have to update the column's
data to accomodate this, but I would do it if this offers a
performance gain.

-- Bill
Bill wrote:

> In an effort to improve the speed of queries against my main table,
> I'll be indexing a column whose data type is varchar(50).
> Would I be better off (better performance) if I changed the column's
> data type to some numeric type? I would have to update the column's
> data to accomodate this, but I would do it if this offers a
> performance gain.
> -- Bill

If the varchar field is usually using all or most of the 50
characters, *and* it can be changed to an integer, then your
index pages and data pages will certain become smaller, meaning
that more can be in memory at a time, and fewer levels to the
index, etc. Also, the comparison of one integer to another is
faster than varchar-to-varchar.
If the varchar field is most often just a 4-character code,
then the savings will be much less. FInally, consider whether there
is any human-readability value to the varchar content. If not, then
I'd do it...

Joe Weinstein at BEA|||Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<3FDDF197.1040901@.bea.com>...
> Bill wrote:
> > In an effort to improve the speed of queries against my main table,
> > I'll be indexing a column whose data type is varchar(50).
> > Would I be better off (better performance) if I changed the column's
> > data type to some numeric type? I would have to update the column's
> > data to accomodate this, but I would do it if this offers a
> > performance gain.
> > -- Bill
> If the varchar field is usually using all or most of the 50
> characters, *and* it can be changed to an integer, then your
> index pages and data pages will certain become smaller, meaning
> that more can be in memory at a time, and fewer levels to the
> index, etc. Also, the comparison of one integer to another is
> faster than varchar-to-varchar.
> If the varchar field is most often just a 4-character code,
> then the savings will be much less. FInally, consider whether there
> is any human-readability value to the varchar content. If not, then
> I'd do it...
> Joe Weinstein at BEA

Thanks for the help, Joe.

The values in the varchar(50) field are invariably of this format:
abc_1234. Always eight characters in length, alpha alpha alpha
underscore digit digit digit digit.

Maybe if I change the columns data type to char(8), then index?

-- Bill|||If the data is by deinition always eight characters, why use define it
as having a size of 50 OR having a variable size?

With a field width of eight, I don't think that you'll realize any speed
improvements by converting to an integer.

HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||
Bill wrote:

> Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<3FDDF197.1040901@.bea.com>...
>>Bill wrote:
>>
>>
>>>In an effort to improve the speed of queries against my main table,
>>>I'll be indexing a column whose data type is varchar(50).
>>>
>>>Would I be better off (better performance) if I changed the column's
>>>data type to some numeric type? I would have to update the column's
>>>data to accomodate this, but I would do it if this offers a
>>>performance gain.
>>>
>>>-- Bill
>>
>>If the varchar field is usually using all or most of the 50
>>characters, *and* it can be changed to an integer, then your
>>index pages and data pages will certain become smaller, meaning
>>that more can be in memory at a time, and fewer levels to the
>>index, etc. Also, the comparison of one integer to another is
>>faster than varchar-to-varchar.
>> If the varchar field is most often just a 4-character code,
>>then the savings will be much less. FInally, consider whether there
>>is any human-readability value to the varchar content. If not, then
>>I'd do it...
>>
>>Joe Weinstein at BEA
>
> Thanks for the help, Joe.
> The values in the varchar(50) field are invariably of this format:
> abc_1234. Always eight characters in length, alpha alpha alpha
> underscore digit digit digit digit.
> Maybe if I change the columns data type to char(8), then index?

Well, a varchar field doesn't waste 50 chars for an 8-char value,
so the index size will really only drop from 8 bytes to 4 (per entry).
There will be a *little* improvement with an int column, in data volume
and in comparison speed.|||Joe Weinstein <joeNOSPAM@.bea.com> wrote in message news:<3FDDF197.1040901@.bea.com>...
> Bill wrote:
> > In an effort to improve the speed of queries against my main table,
> > I'll be indexing a column whose data type is varchar(50).
> > Would I be better off (better performance) if I changed the column's
> > data type to some numeric type? I would have to update the column's
> > data to accomodate this, but I would do it if this offers a
> > performance gain.
> > -- Bill
> If the varchar field is usually using all or most of the 50
> characters, *and* it can be changed to an integer, then your
> index pages and data pages will certain become smaller, meaning
> that more can be in memory at a time, and fewer levels to the
> index, etc. Also, the comparison of one integer to another is
> faster than varchar-to-varchar.
> If the varchar field is most often just a 4-character code,
> then the savings will be much less. FInally, consider whether there
> is any human-readability value to the varchar content. If not, then
> I'd do it...
> Joe Weinstein at BEA

What Joe said is right.
There are also a couple more things to consider. Sql can always search
numbers faster than text. If all you have in the field are numbers,
then absolutely change it. If you have text in the field. . . If the
data length is closer to 10 or 20 changing it to char might speed
things up. Also if this is the field that is searched most often make
it the clustered index (the order the data is stored on disk). Put
your sql strings in query analyzer and look at the execution plan.
You may find you have table scans on other things that are slowing it
down.
HTH
Pachydermitis|||Neither the use of varying size nor the use of 50 for a data item of
eight characters are erroneous in the sense that they yield incorrect
data; their just, well, inexact. They don't waste space, they just
offend the sensibilities.

The reduction in index size from eight to four should, technically,
allow for faster selects. I just don't think that the speed up will be
significant.

The proof is in the pudding, so try it to see.

HTH

=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!