Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Posts
    30

    Unanswered: String comparison and Collation

    I have two tables: T1 and T2 with same fields.
    Now, I need to do the following query many times:
    select *
    from T1 full outer join T2
    on T1.f1 = T2.f1 and
    T1.f2 = T2.f2 and
    T1.f3 = T2.f3

    Since f1, f2 and f3 are nvarchar fields. The speed
    is pretty slow. I am trying to use binary collation,
    hoping to improve the performance. But the
    performance turns even worse. I am using something like

    select *
    from T1 full outer join T2
    on T1.f1 = T2.f1 collate Latin1_General_BIN and
    T1.f2 = T2.f2 collate Latin1_General_BIN and
    T1.f3 = T2.f3 collate Latin1_General_BIN

    It made me puzzled. Anybody have suggestion?

    Thanks.

  2. #2
    Join Date
    Dec 2002
    Posts
    63
    DId you check for differences in the two query plans?

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I had to deal with collation when I was converting from a vendor structure to internal and found out that the vendor was using CS (case-sensitive as the only difference). I had to forget about indexes, none ever kicked in.

    How big are nvarchars? You can try to convert fields to nchar and drop experimenting with collation. Instead, once converted to nchar, you can create covered indexes on fields in question, providing the total size is under 900 characters.

  4. #4
    Join Date
    Apr 2003
    Posts
    30

    Thanks.

    Unfortunately, the length of two fields are defined as 1024. And i don't think the performance will be better if another layer of conversion is conducted. I am not sure whether the query plan will show how the comparison is done. I will double check tomorrow.

Posting Permissions

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