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 6 22394
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 <jo*******@bea.com> wrote in message news:<3F**************@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 <jo*******@bea.com> wrote in message news:<3F**************@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 <jo*******@bea.com> wrote in message news:<3F**************@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! This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: blue |
last post by:
Is there a preferred way to order a varchar column numerically (for those
that are numeric), then alphanumerically for all others?
I've tried:
ORDER BY CASE WHEN IsNumeric(<column_name>) = 1...
|
by: Alexandre H. Guerra |
last post by:
I needed to log all statements executed during a period of time
and now i need ordering the long varchar column in the statements monitor table
(STMT_TEXT)
Is there any flag to set to release...
|
by: Laurent |
last post by:
DB2 8.1
-------
db2 => create table test (COL1 VARCHAR(10))
db2 => insert into test values ('A')
db2 => insert into test values ('A ')
db2 => insert into test values ('B')
db2 => insert into...
|
by: John Sidney-Woollett |
last post by:
Is it possible to alter a table to resize a varchar column? The PG docs
indicate lots of uses for "alter table T alter column C..." but not one
that allows the changing of the type (or same type,...
|
by: Hunter Hillegas |
last post by:
I have two varchars on my table...
I tried to run the following:
VMGEngine=# update user_account set editor_status = 'true' and
administrator_status = 'true' where rec_num = 20;
ERROR: ...
|
by: jim_geissman |
last post by:
Can I create an index on a variation of a column that isn't actually in
the table?
I have a ParcelNumber column, with values like
123 AB-670
12345ABC 000-00-040
12-345-67
AP34567890
|
by: RGow |
last post by:
Hi,
I'd like to know whether there is a way to alter the data type of a
VARCHAR FOR BIT DATA column to be simply a VARCHAR column? I executed
an ALTER TABLE statement, similar to,
ALTER TABLE...
|
by: Krishna Ladwa |
last post by:
In Sql Server 2000 Version, I found that no Notification message box appears when converting text column to varchar but the data gets truncated to the given size for the varchar. Whereas it appears...
|
by: pkrulz |
last post by:
Hi
I have a varchar column containing a list of numbers separated by commas.
eg ColName Type Data
NumberList Varchar(50) 15,1,14,13
...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
| |