Help me speed up this SIMPLE query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help me speed up this SIMPLE query

Author

Topic

mmarian
Starting Member

2 Posts

Posted - 2004-06-18 : 12:17:47

Two tables. The MASTER table has records that are linked to the SERVERLIFE table through "slid" (FK in MASTER, PK of SERVERLIFE, so a SERVERLIFE record can be associated with 0-M MASTER records but each MASTER record can only be associaetd with 1 SERVERLIFE record).

The point of this query is to see which records in SERVERLIFE are not linked to ANY records in MASTER.
The problem is MASTER has 2000 records and SERVERLIFE has about 1500. Because of this one query alone, my asp page takes about 20 seconds to load. Is there any way I can speed this up?

**********
select * from SERVERLIFE where slid NOT IN (select slid from MASTER)
**********

Any help would be very appreciated. Thank you!

Mike

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-18 : 12:28:31

select *
from SERVERLIFE l
LEFT JOIN MASTER r
ON l.slid = r.slid
WHERE r.slid IS NULL

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-18 : 12:28:57

Do you have an index on slid in MASTER table? Indexes aren't automatically created on the foreign keys. They are automatically created on the pks though.

Tara

mmarian
Starting Member

2 Posts

Posted - 2004-06-18 : 12:43:23

Wow that sped it up from 20 seconds to instantly! Thanks so much for your quick response.

Mike

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-18 : 12:44:10

2000 records takes 20 seconds?

Even if it does a scan I don't think should take that long....

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-06-18 : 12:45:58

Did you see if you've got an index on slid in MASTER table? It's a common mistake to not index the foreign keys. Some people assume that they are automatically created when the constraint is created.

Tara

Kristen
Test

22859 Posts

Posted - 2004-06-18 : 14:05:11

To clarify for my pea sized brain please:

So if I have two tables
CUSTOMER
and
STATUS_LOOKUP

and I have the column
CUSTOMER.Status (lets assumed its an INT)
which JOINs to
STATUS_LOOKUP.ID
(which is the PK of STATUS_LOOKUP)
then if I make a Foreign Key I don't need to index STATUS_LOOKUP.ID (because it is a PK), but I should index
CUSTOMER.Status
to boose performance?

Thanks

Kristen

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-18 : 14:17:55

Yes. For the Customer table

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-18 : 14:21:55

quote:
Originally posted by drymchaser

Yes. For the Customer table

It Depends

For example in my solution, it's going to scan the left table and use the index on the right table..it might be an index scan, buth it will still have to scan...

In your exampl as well, since there are no other predicates, one will scan

Brett

8-)

EDIT: Very good Preston...when I first read it was just Yes....

I was gonna reply to that, but the reply must have picked up the edit.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-18 : 15:03:54