Re: Query performance
From: Joe Celko (jcelko212_at_earthlink.net)
Date: 09/19/04
- Next message: Nigel Rivett: "RE: List of bad practices"
- Previous message: EricT: "RE: Very bad performance"
- In reply to: Ramnadh: "Query performance"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 19 Sep 2004 10:32:13 -0700
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. It is very hard to debug code you cannot see.
Next, let's clean up the query. I know that this is just an example so
meaningless names like "TableA" should not bother me; however, incorrect
names like "id" should not be used even in pseudo-code. It shows people
that you have no data dictionary, no data model and do not enforce
ISO-11179 standards. A basic principle of naming a data element is that
an identifier belongs to a particular set of entities -- there is no
such magical creature as a magic, universal, all-purpose "id" in a
correct data model.
Is this what you meant?
CREATE TABLE TableA
(message_id INTEGER NOT NULL PRIMARY KEY,
...);
CREATE TABLE TableB
(message_id INTEGER NOT NULL
REFERENCES TableA(message_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
...);
Look at all the guesses we have to make because you are too lazy to post
even the simplest DDL. Now a final guess about the query.
SELECT TableA.message_id
FROM TableA, TableB
WHERE TableA.message_id = TableB.message_id
AND TableB.resource_id = 12;
This is about as simple as you can get, so I would guess that the bottle
neck is in the SQL engine, not the SQL.
As a guess, the Clustered Index on TableA might run faster if it were
matched by a Clustered Index on TableB. Of course, if a message_id is
declared as NVARCHAR(255) that could take up some time. If there are
other indexes, the optimizer could be fooled into a bad choice, but that
does not happen often when you have a PK-FK relationship declared. Is
the message_id in TableB NULL-able or not?
--CELKO--
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
- Next message: Nigel Rivett: "RE: List of bad practices"
- Previous message: EricT: "RE: Very bad performance"
- In reply to: Ramnadh: "Query performance"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
