Sql Query optimization - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Sql Query optimization

Author

Topic

ultimate_senthil
Starting Member

13 Posts

Posted - 2009-07-16 : 16:19:14

DEAR All

i have used the below query to retrive the records from table with multiple table joins
but the execution time for this query takes 48 seconds, after removing the where condition it takes less than one second. can any one say the best solution to reduce the execution time.

------------------------------------

SELECT DISTINCT PS.PictureId FROM DAMS_Tbl_PictureSearch PS
LEFT OUTER JOIN DAMS_Tbl_Pictures P on PS.PictureId = P.PictureId
LEFT OUTER JOIN DAMS_Tbl_PublisherDetails PD ON P.PublisherId = PD.PublisherId
LEFT OUTER JOIN DAMS_Tbl_Magazine M ON P.MagazineId = M.MagazineId
LEFT OUTER JOIN DAMS_Tbl_PictureContributors PCO ON P.PictureId = PCO.PictureId
LEFT OUTER JOIN DAMS_Tbl_Contributors CON ON PCO.ContributorId = CON.ContributorId
LEFT OUTER JOIN DAMS_Tbl_PictureCategory PC ON PC.PictureId = P.PictureId
LEFT OUTER JOIN DAMS_Tbl_Category C ON PC.CategoryId = C.CategoryId
LEFT OUTER JOIN DAMS_Tbl_PictureSubCategory PSC ON PSC.PictureId = P.PictureId
LEFT OUTER JOIN DAMS_Tbl_SubCategory SC ON SC.SubCategoryId = PSC.SubCategoryId

WHERE ((P.PictureId NOT IN (SELECT PR.PictureId FROM DAMS_Tbl_PictureRestrictTerr PR WHERE PR.TerritoryId = 92) AND (P.MagazineId NOT IN (SELECT MR.MagazineId FROM DAMS_Tbl_MagazineRestrictTerr MR WHERE MR.TerritoryId = 92) OR
P.MagazineId IS NULL)AND (PCO.ContributorId NOT IN (SELECT PC.ContributorId FROM DAMS_Tbl_PictureContributors PC join DAMS_Tbl_ContributorRestrictTerr CRT on PC.ContributorId = CRT.ContributorId WHERE CRT.TerritoryId = 92)OR PCO.ContributorId IS NULL))
AND (P.MagazineId NOT IN (SELECT DISTINCT ExclusiveMagazine FROM DAMS_Tbl_ContractDetails WHERE ContractType = 'Exclusive' AND ExclusiveType = 'Magazine' AND UserId <> 556 AND PublicationCountry = 92 AND ContractStartDate <= GETDATE() AND ContractExpireDate >= GETDATE()) OR P.MagazineId IS NULL)
AND (PCO.ContributorId NOT IN (SELECT DISTINCT ExclusivePhotographer FROM DAMS_Tbl_ContractDetails WHERE ContractType = 'Exclusive' AND ExclusiveType = 'Photographer' AND UserId <> 556 AND PublicationCountry = 92 AND ContractStartDate <= GETDATE() AND ContractExpireDate >= GETDATE()) OR PCO.ContributorId IS NULL))

senthilkumar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-16 : 16:39:57

Why all the LEFT JOINs, when they are not used?

SELECT DISTINCT	PS.PictureId
FROM		DAMS_Tbl_PictureSearch AS PS 
INNER JOIN	DAMS_Tbl_Pictures AS P on P.PictureId = PS.PictureId
			AND NOT EXISTS (SELECT * FROM DAMS_Tbl_PictureRestrictTerr AS PR WHERE PR.TerritoryId = 92 AND PR.PictureId = P.PictureId)
			AND NOT EXISTS (SELECT * FROM DAMS_Tbl_MagazineRestrictTerr AS MR WHERE MR.TerritoryId = 92 AND MR.MagazineId = P.MagazineId)
			AND NOT EXISTS (SELECT * FROM DAMS_Tbl_ContractDetails WHERE ContractType = 'Exclusive' AND ExclusiveType = 'Magazine' AND UserId <> 556 AND PublicationCountry = 92 AND ContractStartDate <= GETDATE() AND ContractExpireDate >= GETDATE() AND ExclusiveMagazine = P.MagazineId) 
INNER JOIN	DAMS_Tbl_PictureContributors AS PCO ON PCO.PictureId = P.PictureId
			AND NOT EXISTS (SELECT * FROM DAMS_Tbl_PictureContributors AS PC INNER JOIN DAMS_Tbl_ContributorRestrictTerr AS CRT on PC.ContributorId = CRT.ContributorId WHERE CRT.TerritoryId = 92 AND PC.ContributorId = PCO.ContributorId)
			AND NOT EXISTS (SELECT * FROM DAMS_Tbl_ContractDetails WHERE ContractType = 'Exclusive' AND ExclusiveType = 'Photographer' AND UserId <> 556 AND PublicationCountry = 92 AND ContractStartDate <= GETDATE() AND ContractExpireDate >= GETDATE() AND ExclusivePhotographer = PCO.ContributorId)

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-16 : 16:43:28

First step is to make your post more readable:

SELECT DISTINCT ps.pictureid 
FROM   dams_tbl_picturesearch ps 
       LEFT OUTER JOIN dams_tbl_pictures p 
         ON ps.pictureid = p.pictureid 
       LEFT OUTER JOIN dams_tbl_publisherdetails pd 
         ON p.publisherid = pd.publisherid 
       LEFT OUTER JOIN dams_tbl_magazine m 
         ON p.magazineid = m.magazineid 
       LEFT OUTER JOIN dams_tbl_picturecontributors pco 
         ON p.pictureid = pco.pictureid 
       LEFT OUTER JOIN dams_tbl_contributors con 
         ON pco.contributorid = con.contributorid 
       LEFT OUTER JOIN dams_tbl_picturecategory pc 
         ON pc.pictureid = p.pictureid 
       LEFT OUTER JOIN dams_tbl_category c 
         ON pc.categoryid = c.categoryid 
       LEFT OUTER JOIN dams_tbl_picturesubcategory psc 
         ON psc.pictureid = p.pictureid 
       LEFT OUTER JOIN dams_tbl_subcategory sc 
         ON sc.subcategoryid = psc.subcategoryid 
WHERE  ((p.pictureid NOT IN (SELECT pr.pictureid 
                             FROM   dams_tbl_picturerestrictterr pr 
                             WHERE  pr.territoryid = 92) 
         AND (p.magazineid NOT IN (SELECT mr.magazineid 
                                   FROM   dams_tbl_magazinerestrictterr mr 
                                   WHERE  mr.territoryid = 92) 
               OR p.magazineid IS NULL) 
         AND (pco.contributorid NOT IN (SELECT pc.contributorid 
                                        FROM   dams_tbl_picturecontributors pc 
                                               JOIN dams_tbl_contributorrestrictterr crt 
                                                 ON pc.contributorid = crt.contributorid 
                                        WHERE  crt.territoryid = 92) 
               OR pco.contributorid IS NULL)) 
        AND (p.magazineid NOT IN (SELECT DISTINCT exclusivemagazine 
                                  FROM   dams_tbl_contractdetails 
                                  WHERE  contracttype = 'Exclusive' 
                                         AND exclusivetype = 'Magazine' 
                                         AND userid <> 556 
                                         AND publicationcountry = 92 
                                         AND contractstartdate <= Getdate() 
                                         AND contractexpiredate >= Getdate()) 
              OR p.magazineid IS NULL) 
        AND (pco.contributorid NOT IN (SELECT DISTINCT exclusivephotographer 
                                       FROM   dams_tbl_contractdetails 
                                       WHERE  contracttype = 'Exclusive' 
                                              AND exclusivetype = 'Photographer' 
                                              AND userid <> 556 
                                              AND publicationcountry = 92 
                                              AND contractstartdate <= Getdate() 
                                              AND contractexpiredate >= Getdate()) 
              OR pco.contributorid IS NULL))

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-16 : 16:44:23

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-16 : 16:45:04

Peso is online - I can go to bed

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-16 : 16:47:17

How many records do your 48-second query return?

How many records do this query return

SELECT DISTINCT	PictureId
FROM		DAMS_Tbl_PictureSearch

N 56°04'39.26"
E 12°55'05.63"

ultimate_senthil
Starting Member

13 Posts

Posted - 2009-07-16 : 17:16:38

quote:
Originally posted by Peso

How many records do your 48-second query return?

How many records do this query return
SELECT DISTINCT PictureId
FROM DAMS_Tbl_PictureSearch

N 56°04'39.26"
E 12°55'05.63"

it returns 50038 records

senthilkumar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-16 : 17:39:32

Both your 48-second query, and the query posted 07/16/2009 : 16:47:17 ?

N 56°04'39.26"
E 12°55'05.63"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-16 : 17:51:07

Maybe it is the total of both queries?

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-16 : 18:01:36

I would guess so since all joins are outer joins and the only select is from base table, and even DISTINCTed.

N 56°04'39.26"
E 12°55'05.63"

ultimate_senthil
Starting Member

13 Posts

Posted - 2009-07-16 : 18:27:52

Ya!. both the query return 50038 records.

i have tried your query it takes just 1 second, when i include the where condition it takes 3 seconds. kindly review the below

SELECT DISTINCT PS.PictureId FROM DAMS_Tbl_PictureSearch AS PS
inner JOIN DAMS_Tbl_Pictures AS P on P.PictureId = PS.PictureId

AND NOT EXISTS (SELECT * FROM DAMS_Tbl_PictureRestrictTerr AS PR WHERE PR.TerritoryId = 92 AND PR.PictureId = P.PictureId)
AND NOT EXISTS (SELECT * FROM DAMS_Tbl_MagazineRestrictTerr AS MR WHERE MR.TerritoryId = 92 AND MR.MagazineId = P.MagazineId)
AND NOT EXISTS (SELECT * FROM DAMS_Tbl_ContractDetails WHERE ContractType = 'Exclusive' AND ExclusiveType = 'Magazine' AND UserId <> 556 AND
PublicationCountry = 92 AND ContractStartDate <= GETDATE() AND ContractExpireDate >= GETDATE() AND ExclusiveMagazine = P.MagazineId)
left outer join DAMS_Tbl_PictureContributors AS PCO ON PCO.PictureId = P.PictureId
AND NOT EXISTS (SELECT * FROM DAMS_Tbl_PictureContributors AS PC INNER JOIN DAMS_Tbl_ContributorRestrictTerr AS CRT on PC.ContributorId = CRT.ContributorId WHERE CRT.TerritoryId = 92 AND PC.ContributorId = PCO.ContributorId)
AND NOT EXISTS (SELECT * FROM DAMS_Tbl_ContractDetails WHERE ContractType = 'Exclusive' AND ExclusiveType = 'Photographer' AND UserId <> 556 AND PublicationCountry = 92 AND ContractStartDate <= GETDATE() AND ContractExpireDate >= GETDATE() AND ExclusivePhotographer = PCO.ContributorId)

WHERE(PS.Keywords LIKE 'woman' OR PS.Keywords LIKE 'woman %' OR PS.Keywords LIKE '% woman %' OR PS.Keywords LIKE '% woman' OR PS.Keywords LIKE '% woman,%' OR PS.Keywords LIKE '% woman.%' OR PS.Keywords LIKE '% woman;%' OR PS.Keywords LIKE '% woman!%' OR PS.Keywords LIKE '% woman?%')

senthilkumar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-16 : 18:50:01

It means your original query is flawed and doesn't filter at all!

If your original query takes 48 seconds and returns 50038 records
and this query

SELECT DISTINCT	PictureId
FROM		DAMS_Tbl_PictureSearch

runs in under a second and also returns 50038 records, it means all your LEFT OUTER JOINs and WHERE are contraproductive.

How long time, and how many records are returned, with the suggestion posted 07/16/2009 : 16:39:57 ?

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources