[thelist] SQL SELECT speed-up question

Jay Turley jayturley at gmail.com
Thu Dec 6 15:39:57 CST 2007


Hi!

I am trying to speed up some sql we use to select contacts from our internal
contact manager.

And I've been beating my head against the wall for days now.

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:

select
    c.contact_id
    ,c.fname
    ,c.lname
    ,c.do_not_contact
    ,ct.contact_type
    ,c.team_sharing
from
    econtacts c
inner join
    (
    select
        i.contact_id
    from
        einv_interests i
    where
        i.inv_type_id in
(10,61,60,55,56,57,58,59,50,51,52,41,42,44,43,45,46,47,48,49)
    and
        i.inv_range_id in (3,4)
    ) ci
on
    c.contact_id = ci.contact_id
inner join
    econtact_type ct
on
    c.contact_type_id = ct.contact_type_id

The derived table "ci" in the query above may have as many as 40 integers in
the sets.

The table structure for econtacts is pretty much as shown above in the
select portion. The einv_interests table structure is as follows:

CREATE TABLE [einv_interests] (
    [contact_id] [int] NOT NULL ,
    [inv_type_id] [int] NOT NULL ,
    [inv_range_id] [int] NULL ,
    [inv_cat_id] [int] NULL ,
     FOREIGN KEY
    (
        [contact_id]
    ) REFERENCES [econtacts] (
        [contact_id]
    ),
     FOREIGN KEY
    (
        [inv_cat_id]
    ) REFERENCES [einv_cat] (
        [inv_cat_id]
    ),
     FOREIGN KEY
    (
        [inv_range_id]
    ) REFERENCES [einv_range] (
        [inv_range_id]
    ),
     FOREIGN KEY
    (
        [inv_type_id]
    ) REFERENCES [einv_type] (
        [inv_type_id]
    )
) ON [PRIMARY]
GO

where einv_cat, einv_range, and einv_type are simple lookup tables. 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.
:(

Any help figuring out how to speed this us would be greatly appreciated.

Thanks a ton!



More information about the thelist mailing list