[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