Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Posts
    45

    Unanswered: Varchar In Key causes slower select speeds

    I used to use a char 10 for a key field in one of my tables.
    Speed on this select procedure is being watched closely. When I changed the key to a varchar 10 the speed slowed from .01 seconds to .1 with no other variables changing. Is there any documentation that explains this?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I though the impact is less substantial...plus you could have a lot of other things going on...it's all about the resources...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Location
    Arizona
    Posts
    3

    Re: Varchar In Key causes slower select speeds

    Originally posted by Will trever
    I used to use a char 10 for a key field in one of my tables.
    Speed on this select procedure is being watched closely. When I changed the key to a varchar 10 the speed slowed from .01 seconds to .1 with no other variables changing. Is there any documentation that explains this?
    What does your join look like?

  4. #4
    Join Date
    Dec 2002
    Posts
    45

    Looks like this

    CREATE PROCEDURE dbo.Component_Commissions_TB_Ret
    (
    @VendorCode char(3),
    @Des char(3),
    @Org char(3),
    @BookingDate datetime,
    @DepartureDate datetime,
    @ComponentType_1 int,
    @ComponentValue_1 varchar(50),
    @ComponentType_2 int,
    @ComponentValue_2 varchar(50),
    @ComponentType_3 int,
    @ComponentValue_3 varchar(50),
    @ComponentType_4 int,
    @ComponentValue_4 varchar(50),
    @ComponentType_5 int,
    @ComponentValue_5 varchar(50),
    @ComponentType_6 int,
    @ComponentValue_6 varchar(50),
    @ComponentType_7 int,
    @ComponentValue_7 varchar(50)
    )
    AS

    BEGIN
    set nocount on
    Select ax.commission as Component_Commission,ax.cap as Component_Cap from
    (
    select a.guid,a.commission,a.totalcomponents, a.cap, a.seq from Component_Commissions a
    where
    a.guid in
    (
    select a.guid from Component_Commissions a where a.VendorCode=@VendorCode and a.org = @Org and a.des=@Des
    )
    or a.org in
    (
    select a.guid from Component_Commissions a, City_Groups b
    where a.VendorCode=b.VendorCode and a.org = b.groupname and a.VendorCode=@VendorCode and b.citycode = @Org and a.des = @Des and b.Application_Type = 'C'
    )
    and a.des = @Des
    or a.guid in
    (
    select a.guid from Component_Commissions a, City_Groups b
    where a.VendorCode = b.VendorCode and a.VendorCode=@VendorCode and a.des = b.groupname and b.citycode = @Des and a.org = @Org and b.Application_Type = 'C'
    )
    and a.org = @Org

    or a.guid in
    (
    select a.guid from Component_Commissions a, City_Groups b
    where a.VendorCode = b.VendorCode
    and a.VendorCode = @VendorCode
    and a.des = b.groupname
    and a.org = b.groupname
    and b.citycode = @Des
    and b.citycode = @Org
    and b.Application_Type = 'C'
    )
    ) ax INNER JOIN
    (
    SELECT distinct(a.guid)
    FROM dbo.Component_Commissions a INNER JOIN
    dbo.Selected_Component_Values b ON a.Guid = b.Guid
    and a.VendorCode = @VendorCode
    and
    (
    b.componenttype = @ComponentType_1 and (b.ComponentValue in (@ComponentValue_1,'~'))
    or
    b.componenttype = @ComponentType_2 and (b.ComponentValue in (@ComponentValue_2,'~'))
    or
    b.componenttype = @ComponentType_3 and (b.ComponentValue in (@ComponentValue_3,'~'))
    or
    b.componenttype = @ComponentType_4 and (b.ComponentValue in (@ComponentValue_4,'~'))
    or
    b.componenttype = @ComponentType_5 and (b.ComponentValue in (@ComponentValue_5,'~'))
    or
    b.componenttype = @ComponentType_6 and (b.ComponentValue in (@ComponentValue_6,'~'))
    or
    b.componenttype = @ComponentType_7 and (b.ComponentValue in (@ComponentValue_7,'~'))
    )
    and b.guid not in
    (
    SELECT distinct(a.guid)
    FROM dbo.Component_Commissions a INNER JOIN
    dbo.Selected_Component_values b ON a.Guid = b.Guid
    and a.VendorCode = @VendorCode
    and b.componenttype not in
    (
    select b.componenttype
    where
    b.componenttype = @ComponentType_1
    or
    b.componenttype = @ComponentType_2
    or
    b.componenttype = @ComponentType_3
    or
    b.componenttype = @ComponentType_4
    or
    b.componenttype = @ComponentType_5
    or
    b.componenttype = @ComponentType_6
    or
    b.componenttype = @ComponentType_7
    )
    )
    )bx on ax.guid = bx.guid INNER JOIN
    (
    select a.guid,a.seq from Date_Booking_N_Departure a, Component_Commissions b
    where a.guid = b.guid
    and a.seq = b.seq
    and a.Application_Type = 'C'
    and b.VendorCode = @VendorCode
    and BookingHighDate >= @BookingDate
    and BookingLowDate <= @BookingDate
    and DepartureHighDate >= @DepartureDate
    and DepartureLowDate <= @DepartureDate
    ) cx on ax.guid = cx.guid
    and bx.guid = cx.guid
    and ax.seq = cx.seq

    END
    GO

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    CHAR data is fixed length, while VARCHAR is variable. This includes the way the data is actually stored in the database. CHAR data is allocated a specific number of bytes in each record, and unused bytes are filled with spaces (there are some exceptions). VARCHAR data is stored more efficiently, in that only the number of bytes required for the string length are allocatted (I believe there are a few more bytes that indicate the actual length of the data).

    When SQL Server querys CHAR data, it always knows where to find it in the record and how many bytes it has to read in order to get the full string (its "offset"). With VARCHAR data, it first has to figure out how long the data is so that it knows how many bytes to read. This takes additional time.

    VARCHAR data stores more efficiently (except for short strings). CHAR data sorts and searches more efficiently.

    blindman

  6. #6
    Join Date
    Dec 2002
    Posts
    45

    Excellent answer

    Thank you so much blindman, for the excellent insight. Do you know what the overhead is for a varchar. For instance should anything under a char 4 always be a char just because of the extra overhead for a varchar 4?
    We often have wondered where to draw this line.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Unfortunately, the choice depends on many factors:

    What is a typical data length?
    How variable is your data length?
    What is more important, storage space or execution time?
    What proportion of your records will have no data (NULL value)?
    What is it going to cost you, in processing time, if you have to trim trailing spaces off your result set before displaying it?

    VARCHAR(1) is, of course, practically useless.
    I'd be reluctant to use VARCHAR for strings less than five or six characters.
    I'd be reluctant to use CHAR for strings greater than twenty characters.
    If you have a fixed length type of data, such as Social Security Numbers or Employee ID codes, use CHAR.

    These are all rules of thumb, of course, and everything else is grey.

    blindman

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Just found this on the web:

    http://www.databasejournal.com/featu...le.php/1439081

    13. Use char/nchar data type instead of varchar/nvarchar if the column's size will be 4 bytes or less.

    The char data type is a fixed-length data type and varchar data type is variable-length data type.

    So, by using char data type, you can increase the probability of an in-place update instead of delete/insert or deferred update. The in-place update is the most effective method of modification; when it is used, the data is changed on its physical place. When the row's size is changed, the delete/insert modification method can be used. This does result in some performance degradation, though.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •