Re: Updating the SQL key value

Jerry Stuckle wrote:
Toby A Inkster wrote:

Firstly, I said char, not varchar. char columns are fixed width storage
and consequently a lot faster to search than varchar columns. On a
32-bit processor, char(4) is particularly fast as it corresponds to the
processor's word length.

True, but unless it's a binary column the search must be
case-insensitive.

I normally use PostgreSQL, in which all strings are handles
case-sensitively unless you specify otherwise.

Finally, even if none of the above are true, an int column will almost
always be word aligned in memory. A char field - even if it's char(4),
is not always word aligned.

Perhaps not word-aligned when stored as row data, but certainly
row-aligned in the index. And you'd nearly always want to index a primary
key column.

Firstly, in this example, the licence code is an arbitrary string
chosen by the creator of the data, so it needs to change no more often
than an integer key would.

So why not just use an auto-number field. It seems you're unnecessarily
creating an unnecessary bottleneck.

If you use a piece of useful data as a primary key, instead of a
meaningless number, this in many cases reduces the need to perform
joins between tables, resulting in simplifying queries and speeding things
up. As an example, consider extracting a list of article titles plus the
author's login name from our articles and users tables:

My schema:

CREATE TABLE articles (
article_id integer PRIMARY KEY,
title varchar NOT NULL,
body varchar NOT NULL,
author char(16) REFERENCES users
);
CREATE TABLE users (
login char(16) PRIMARY KEY,
email varchar NOT NULL
);
-- Insert lots of data here.
SELECT title, author FROM articles;

Yours:

CREATE TABLE articles (
article_id integer PRIMARY KEY,
title varchar NOT NULL,
body varchar NOT NULL,
author integer REFERENCES users
);
CREATE TABLE users (
user_id integer PRIMARY KEY,
login char(16) NOT NULL,
email varchar NOT NULL,
UNIQUE (login)
);
-- Insert lots of data here.
SELECT a.title, u.login AS author
FROM articles a
INNER JOIN users u ON a.author=u.user_id;

The second schema has to query two tables and perform an inner join,
which is slower than performing a single query to a single table.

Sure. But you don't have to use referential integrity. And no,
databases do not just point to another table, as I indicated. Look at
the data in your tables. You will find the char fields.

You will find the char fields if you use SQL. I'm talking about the binary
data that the RDBMS actually keeps in memory while it's running. They use
all kinds of tricks for speed boosts, including using pointers for primary
keys instead of using the real data.

Sure. And in this case transactions cause their own problems. You need
at least 4 calls to the database:

START TRANSACTION
SELECT MAX(colId)+1 FROM mytable
INSERT INTO mytable ...
COMMIT

Significantly slower than a single call which returns an auto-numbered
column. And worse yet, it will tie up the table for additional changes
until the COMMIT. Very bad for concurrency.

I'm sure autonumbering results in something fairly similar happening
deep down in the RDBMS anyway.

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact
Geek of ~ HTML/SQL/Perl/PHP/Python*/Apache/Linux

* = I'm getting there!
.

Relevant Pages

  • Re: Updating a form based on a query using the "total" feature
    ... FROM a INNER JOIN b ON ... ... Now, if you also have a GROUP BY clause, you probably have, that is worse, ... query, use that temp table to update other stuff. ... >I have set up an ACCESS database of journal articles. ...
    (microsoft.public.access.forms)
  • Re: Complex search on a relationship
    ... The first links the keywords to ... FROM KeysAndArticles AS KeysAndArticles_1 INNER JOIN Articles ON ...
    (microsoft.public.access.queries)
  • Help with Parameter Query
    ... I have a database that has articles that are classified by topic. ... I wrote a query that will return all of the articles that are related to 2 ... INNER JOIN Article ON x.articleid = Article.ArticleID) ... atp.topicid FROM articletopic atp WHERE atp.topicid ...
    (microsoft.public.access.queries)