SELECT taking 24 seconds to complete

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

General SQL Server Forums

New to SQL Server Programming

SELECT taking 24 seconds to complete

Author

Topic

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2013-03-12 : 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?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2013-03-12 : 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
Yak Posting Veteran

50 Posts

Posted - 2013-03-12 : 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?

sqlconfused
Yak Posting Veteran

50 Posts

Posted - 2013-03-13 : 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.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2013-03-15 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources