RE: question of performance

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: GreySky (GreySky_at_discussions.microsoft.com)
Date: 11/03/04 

Date: Wed, 3 Nov 2004 12:23:04 -0800

Integer values can fit into registers on a chip. Strings are always pointers
to locations in memory, and operations must be performed against string data
brought to the chip's cache, other hardware or software cache, or bussed in
from way out of town.

I've always wondered why *anyone* would recommend using strings as primary
keys. I realize they can make primary keys more "readable" if built up like
a code, and I realize vendors like Siebel issue text-based primary keys in
100-block increments to keep their primary keys unique among all tables. I
also realize the official stance from some of the mvp's on this board is to
use text-based keys (*sigh*).

But think about it. If you're comparing millions of keys to build a
Cartesian Product Table (i.e., build your query), doesn't it make more sense
to use something that can literally sit on the chip in a register and be
compared in one CMP operation (one op to move the base integer to a register,
one op to move the other integer to a register, one op to tell the chip to
compare the two, done), rather than having to point to memory for the data to
begin the comparison?

Another thing -- (long) integers are 4 bytes, period. I don't think I've
seen character-based primary keys this short. Not only that, these guys are
likely unicode, so each character is two bytes of not compressed (and if so,
perhaps requires translation before comparisons can be undertaken). Smaller
indexes = faster scans = quicker results.

Use Long Integers / Autonumbers for primary keys. Use indexes. Watch your
queries scream. And if you want, look at www.intel.com and read about the
Intel architecture.

David Atkins, MCP

"Nemo" wrote:

> Hello Folks,
> about the performance(speed) of a query, does it make difference if I define
> my field type as string or integer. Thanks.
>
>
>

Relevant Pages

  • Re: question of performance
    ... I tend to use integers as my primary keys. ... then use a string to store the value. ... If you're comparing millions of keys to build a ... > compared in one CMP operation (one op to move the base integer to a register, ...
    (microsoft.public.access.queries)
  • Re: To store a huge table during start-up of a J2EE application
    ... where I will create two strings. ... if your primary keys and index are setup ... a database query should not run for more than tens of ... dont see the value of losing the ...
    (comp.lang.java.programmer)
  • Re: Upgrading VC 6.0 to VC 2003
    ... I rewrote the piece of code doing an array of strings to use a map instead. ... I had another dll that wouldn't register and your suggestion about looking ... Sherry ... >> Is there a hresult error code? ...
    (microsoft.public.dotnet.faqs)
  • Re: Does Codds view of a relational database differ from that ofDate&Darwin?[M.Gittens]
    ... > Marshall Spight wrote: ... > you to define certains sets of strings described by regular expression ... > as strings, and is user-extensible. ... > Another small thing is updating primary keys. ...
    (comp.databases.theory)
  • Re: Guru Design Question: Multiple Primary Keys
    ... > I have a question about Primary Keys and uniquely identifying records. ... > I have a Cash Register Table, Cash Register Product Table and a Cash ... > Now, when I want to relate a Register Product to a Register Group, because ...
    (microsoft.public.sqlserver.programming)