[thelist] SQL SELECT speed-up question

Matt Warden mwarden at gmail.com
Thu Dec 6 16:06:05 CST 2007

On 12/6/07, Jay Turley <jayturley at gmail.com> wrote:
> I have tried using temporary tables, derived tables, UNIONs, and a bunch of
> other things that didn't work; nothing will speed it up more than a few
> seconds. I am not an expert in SQL, so I feel there must be something I am
> missing about how to approach this. Here is sample code using derived tables
> and IN:

Do you have a formulation of the query that is most logical and
ignores potential performance issues? It will be easier for us to
start from there.

> There is
> no primary key defined on the table, which may very well be the problem.
> This is because two of the columns are nullable, and so I can't define a
> primary key over all 4 columns. Business logic guarantees each row to be
> unique, but I think that without the PK, I am ending up running table scans.

Yes, if there is no PK then there probably isn't an index. However, if
these are lookup tables, unless you have a large number of values, it
is not going to make much of a difference to add an index.

In other words, that will probably help some, but my gut says it is
not going to make you happy.

Matt Warden
Cincinnati, OH, USA

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list