Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: SQL Humor

Re: SQL Humor

From: Hugo Kornelis <hugo_at_pe_NO_rFact.in_SPAM_fo>
Date: Sat, 20 Aug 2005 00:32:28 +0200
Message-ID: <eclcg19u8755p8o054c77j81jqn424mv9k@4ax.com>


On 18 Aug 2005 15:02:56 -0700, Mikito Harakiri wrote:

>Hugo Kornelis wrote:
>> Many companies use a short mnemonic code for their customers. The use of
>> a short mnemonic code for products is not uncommon either. Imagine a
>> customers table with 10,000 customers, a products table with 5,000
>> products and an orders table with a few million rows. Now would you
>> prefer char(6) or varchar(6) for CustomerCode? And char(5) or varchar(5)
>> for ProductCode?
>
>But if lookup table cardinality goes up, then, the storage factor
>char(n) vs varchar(n) goes down! Perhaps, you can convince me that the
>effect of the two trailing bytes is not miniscule, as I previously
>thought, but it just can't be significant. Any benchmark demonstrating
>that the performance degradation is not in single percentage digits is
>welcome.

Hi Mikito,

Below is the script I used to compare performance. I compared three tasks:

  1. Performing lots of single inserts (speed of inserts matters in databases that have to process thousands of new rows per second)
  2. Adding an index (not an everyday task, but there are situations where a process can be sped up by adding an index, doing the process, then dropping the index again)
  3. Generating a typical report that involves some joins (the CASE expressions I used may look silly, but imagine that I am using a payment status instead - also note that I store the results in a temporary table first, to make sure that network speed won't influence the results)

You'll find the full code further below. But first the results:

  1. For the inserts, the elapsed time on my machine was 118156 ms with char, 124406 ms with varchar. A degradation of over 5 %.
  2. Adding the index took 2840 ms with char, 3236 ms with varchar. A performance degradation of almost 14 %.

3a. Generating the report with the index created in step 2 took 224 ms on average for char; 234 ms on average for varchar (I used three consecutive executions for both tests, each time starting with a clean cache). Performance degradation: 4.5 %.
3b. Generating the same report after dropping the supporting index showed an even bigger difference: avg 2281 for char; avg 3250 for varchar. Degradation: over 40 %.

1a. To top it off, I repeated the insertion test, but this time AFTER creating the extra index. For char, the time taken was 227936 ms; for varchar 313110 ms. Performance degradation: 37 %.

So here's your choice: either you use the index, degrade the queries by "only" 4.5% but at the cost of slowing inserts down by 37%, or you discard the index to reverse these figures (inserts slowed down 5%; queries slowed down 40%).

Or you choose char instead of varchar when you know that the length is more or less fixed. :-)

Here's the code I used for this benchmark:

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)
Received on Fri Aug 19 2005 - 17:32:28 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US