Index on a varchar column?

[Bill]

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

[Joe Weinstein]

Re: Index on a varchar column?



Bill wrote:
[color=blue]
> 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[/color]

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

[Bill]

Re: Index on a varchar column?

Joe Weinstein <joeNOSPAM@bea. com> wrote in message news:<3FDDF197. 1040901@bea.com >...[color=blue]
> Bill wrote:
>[color=green]
> > 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[/color]
>
> 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[/color]

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

[Stephen Hendricks]

Re: Index on a varchar column?

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!

[Joe Weinstein]

Re: Index on a varchar column?



Bill wrote:
[color=blue]
> Joe Weinstein <joeNOSPAM@bea. com> wrote in message news:<3FDDF197. 1040901@bea.com >...
>[color=green]
>>Bill wrote:
>>
>>[color=darkred]
>>>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
>>>performanc e gain.
>>>
>>>-- Bill[/color]
>>
>>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[/color]
>
>
> 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?[/color]

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.

[Pachydermitis]

Re: Index on a varchar column?

Joe Weinstein <joeNOSPAM@bea. com> wrote in message news:<3FDDF197. 1040901@bea.com >...[color=blue]
> Bill wrote:
>[color=green]
> > 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[/color]
>
> 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[/color]

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

[Stephen Hendricks]

Re: Index on a varchar column?

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!