SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
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 Previous Topic Topic Next Topic  

sqlconfused
Starting Member

Canada
43 Posts

Posted - 03/12/2013 :  09:36:15  Show Profile  Reply with Quote
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
3608 Posts

Posted - 03/12/2013 :  10:04:19  Show Profile  Reply with Quote
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
Go to Top of Page

sqlconfused
Starting Member

Canada
43 Posts

Posted - 03/12/2013 :  11:12:12  Show Profile  Reply with Quote
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
Go to Top of Page

sqlconfused
Starting Member

Canada
43 Posts

Posted - 03/13/2013 :  10:37:14  Show Profile  Reply with Quote
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
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 03/15/2013 :  07:50:41  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000