SQL Server Forums - SELECT taking 24 seconds to complete

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

Register Now and get your question answered!

Username:	Password:
Save Password
Forgot your Password?

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT taking 24 seconds to complete

New Topic

Reply to Topic

Printer Friendly

Author

Topic

sqlconfused
Starting Member

Canada
17 Posts

Posted - 03/12/2013 : 09:36:15

Hi!

Here is my situation. I have a database we'll call "places". It lists towns with an associated ID field.

-- PLACES --
Name: "toronto", ID: 1
Name: "farm house", ID: 2
Name: "school", ID: 3

This table lists places that members can visit and they can upload photos if they've been there. I am trying to run a query to display locations that my members do NOT have a gallery for. The idea is that they can see what places they might want to visit, but avoid the ones they've already been to. This would be an option in the search feature I already have.

A second table lists the photos of each location.
-- PHOTOTABLE --
Fields: SID, created, dir, numpic, creator

3dk2 - March 12, 2012 - 2 - 20 - Jeff
3kdf0 - May 1, 2011 - 133 - 33 - Brian
3dfks - December 12, 2012 - 214 - 33 - Dave

The SID is a random number to define the unique galleries, created is the date of the gallery being made, dir is the location as found in the PLACES table (PLACES:ID) and creator is the person that made the gallery.

Example: Jeff visited location number 2, which corresponds to the PLACES column ID ("farm house").

My query attempt is this:

select * from places where ID NOT in (select dir from PHOTOTABLE where creator = USERNAME)

and username is dynamically placed in the query string.

The query returns all locations in PLACES table minus any where the ID field matches the DIR in the phototable with your name as the creator. In other words if you have a photogallery, don't show the location.

The query is taking 24 ridiculous seconds to return all the results. This is not efficient and I know a better solution muxt exist.

Help?

Edited by - sqlconfused on 03/12/2013 09:50:16

RickD
Slow But Sure Yak Herding Master

United Kingdom
3560 Posts

Posted - 03/12/2013 : 10:04:19

First thing you might want are some indexes.

A Clustered Index on ID in the Places table and a Clustered Index on SID in Phototable would be a start and may speed things up no end.

After that, a simple left join query will help you.


DECLARE @Username VARCHAR(100)

SET @Username = 'Jeff'

SELECT
	  PLACES.ID
	, PLACES.Name
FROM
	PLACES
LEFT OUTER JOIN
	PHOTOTABLE
ON
	PLACES.ID = PHOTOTABLE.dir
AND
	PHOTOTABLE.creator = @Username
WHERE
	PHOTOTABLE.dir IS NULL

sqlconfused
Starting Member

Canada
17 Posts

Posted - 03/12/2013 : 11:12:12

Thank you for this. Two questions though.

1) What is a clustered index? I routinely call the PLACES table throughout my site and it sounds like this might be of some benefit. I'm not sure how one implements it though (I use MS SQL 2012). [I've googled it but am unsure of the benefits and drawbacks]

2) What does the Where IS Null do? why would I want to check that the directory is null?

Edited by - sqlconfused on 03/12/2013 11:16:37

sqlconfused
Starting Member

Canada
17 Posts

Posted - 03/13/2013 : 10:37:14

Figured it out... if it is null then there is no gallery and thus it SHOULD be included in the returned results.

Edited by - sqlconfused on 03/13/2013 10:40:23

RickD
Slow But Sure Yak Herding Master

United Kingdom
3560 Posts

Posted - 03/15/2013 : 07:50:41

Sorry, I did type out a reply to you, but then my computer crashed and I forgot about it. Glad you worked it out. As for the clustered indexes, do a search and read a few of the pages, you will get the idea.

Topic

New Topic

Reply to Topic

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.06 seconds.