Re: Sorting by performance difference - int vs. varchar
From: David Gugick (davidg-nospam_at_imceda.com)
Date: 01/12/05
- Next message: Joe Hatem: "using SQLserver, ADO, Delphi: performance issue"
- Previous message: Vinod Kumar: "Re: which statement fire the trigger ?"
- In reply to: Anders: "Re: Sorting by performance difference - int vs. varchar"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 12 Jan 2005 00:44:15 -0500
If they are really integer values, but were stored in varchar by
mistake, then sure you can convert them. But if they are something like
a street number like the "100" in "100 Center Street", I might keep it
as a varchar.
Can you tell us a little more about your tables and the columns in
question?
-- David Gugick Imceda Software www.imceda.com Anders wrote: > Hi Gert-Jan, thanks for that elaborate answer. I can tell from your > directions > the best solution is to convert my varchar tables containing degits > into int. > > Thanks again, I am repeatedly surprised by the prompt and high-quality > feedback these newsgroups supply. > > cheers, > Anders > http://thedotnet.com > > "Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message > news:41E464A9.A6D540A@toomuchspamalready.nl... >> Sorting the int column will be faster or much faster, depending on >> the collation of the varchar column. >> >> The most important performance difference is that for all but the >> binary collations, sorting a char or varchar column requires a >> conversion to see if two values are equal according to the used >> collation. This is not needed for sorting ints, since all different >> int values are by definition not equal to each other. >> >> Even if you use binary collation, then sorting the varchar(4) column >> will probably still be slower, because the column width is not fixed, >> and this has to be taken into consideration when sorting. >> >> Last factor is how much data needs to be handled. An int requires 4 >> bytes per row. A varchar(4) requires 6 bytes per row, and the row >> overhead will be a few bytes (I think it was 5) more. On average, >> this will yield a bigger row size compared with int, which means >> more memory or I/O is needed. >> >> HTH, >> Gert-Jan >> >> Anders wrote: >>> >>> Hi, do any of you know what difference there is in performance >>> between sorting rows by a int column versus a varchar(4) column ? >>> >>> Thanks for any reply, >>> Anders
- Next message: Joe Hatem: "using SQLserver, ADO, Delphi: performance issue"
- Previous message: Vinod Kumar: "Re: which statement fire the trigger ?"
- In reply to: Anders: "Re: Sorting by performance difference - int vs. varchar"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
