[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
http://mattwarden.com


This email proudly and graciously contributes to entropy.



More information about the thelist mailing list