Query optimization: 4 tables added, now 20 x's longer! 1

[glgcag]

This query requires a lot of tables because I'm giving the user a LOT of flexibility with optional parameters. Everything is working fine except I added four more tables/parameters to the end of the query (Producer, ExecProducer, Director, Contact) and it has caused a huge jump in the time to run the query (20x)! I have joined the tables on primary keys that are indexed- there are about 20k-40k records in each of the tables, so it's not huge. Any suggestions for optimizing the query?

SELECT AG.AGProjectKey, AG.ProjectTitle, AG.ProdOfficeAddr1, AG.ProdOfficeAddr2, AG.ProdOfficeAddr3, 
AG.ProdOfficeCity, AG.ProdOfficeState, AG.ProdOfficeZip, AG.ProdOfficeFax, AG.ProdOfficePhone, 
AG.ClosedProject, AG.ClosedDate, AG.MusicSupervisor, AG.Location, AG.ProdStartDate, 
AG.WrapShootDate, AG.SpottingDate, AG.RecordingDate, AG.MixingDate, AG.DeliveryDate, 
AG.ReleaseDate, AG.FinalDub, AG.FilmTVBudget, AG.Network,AG.MusicBudget, AG.Synopsis, 
AG.ProjectStatus, AG.NonClientFinalOut, C.ClientNumber AS [ClientKey], C.FirstName AS CLFirstName, 
C.LastName AS CLLastName, 
A.AgentKey, A.AgentName, PCo.ProductionCompanyDesc, 
PCo1.ProductionCompanyDesc AS CoProdCompany, PT.ProjectTypeKey, PT.ProjectTypeDesc, 
P2.ProjectType2Key, P2.ProjectType2Desc, PSrc.Source, G.GenreDesc, PS.ProductionStatusDesc
 
FROM tAGProjects AG 
LEFT JOIN tProjectClients PC ON AG.AGProjectKey = PC.AGProjectKey 
LEFT JOIN tClients C ON PC.ClientKey = C.ClientNumber 
LEFT JOIN tProductionCompanies PCo ON AG.ProductionCompanyKey = PCo.ProductionCompanyKey 
LEFT JOIN tProductionCompanies PCo1 ON AG.CoProductionCompanyKey = PCo1.ProductionCompanyKey 
LEFT JOIN tProjectTypes PT ON AG.ProjectTypeKey = PT.ProjectTypeKey 
LEFT JOIN tProjectType2s P2 ON AG.Type2Key = P2.ProjectType2Key 
LEFT JOIN tGenre G ON AG.GenreKey = G.GenreKey 
LEFT JOIN tProductionStatus PS ON AG.ProductionStatusKey = PS.ProductionStatusKey 
LEFT JOIN tProjectAgents PA ON AG.AGProjectKey = PA.AGProjectKey JOIN tAgents A ON PA.AgentKey = A.AgentKey 
LEFT JOIN tProjectSources PSrc ON AG.AGProjectKey = PSrc.AGProjectKey 
LEFT JOIN tProjectProducers PP ON AG.AGProjectKey = PP.AGProjectKey
--These four tables cause the query to take 20 x's longer!
LEFT JOIN tProjectExecProducers PEx ON AG.AGProjectKey = PEx.AGProjectKey
LEFT JOIN tProjectDirectors PDr ON AG.AGProjectKey = PDr.AGProjectKey
LEFT JOIN tProjectContacts PCn ON AG.AGProjectKey = PCn.AGProjectKey
LEFT JOIN tProjectCast PCa ON AG.AGProjectKey = PCa.AGProjectKey

WHERE AG.ClosedProject = 0 AND A.AgentKey = 2
ORDER BY AG.ProjectTitle ASC

 

[SQLSister]

first make sure that there is an index on the AGProjectKey file don all these tables. Creating a foriegn key does not create an index in the foreign key table like creating a primary key does.

Second what shows in your execution plan under the old query vice the new one? That may point you to where the difficulty is.

Third, if you are creating joins you don't need for user flexibility, you should stop this practice immediately. Use the if statment instead to go the statement that has the joins you need for a particular instance. The rule is never do more work than is needed for the results you want. If the results often do not need these tables the sp should be structured to only use them in the cases where they are necessary. Sometimes procedural code is much more efficient that doing unnecessary joins. Then at least the slower code will only run the few times it is necessary.

"NOTHING is more important in a database than integrity." ESquared

 

[glgcag]

SQLSister,

Thank you for your response! I was already toying with the idea of writing an IF statement that would include joins to the tables as they were needed based on whether or not a parameter was passed in. Essentially, like this:

IF @Prod Is Not Null
BEGIN
   sSQL = sSQL + 'LEFT JOIN tProjectProducers PP ON AG.AGProjectKey = PP.AGProjectKey '
END

In my actual stored procedure I'm creating the SQL using a string variable anyway, then use sp_executesql with parameters, so this will work fine. Should I make it a habit to only include the joins that I need and dynamically build the FROM statement as well?

Lastly, you are right about the index. The FK "AGProjectKey" is not indexed. I created a non-clustered, non-unique index for AGProjectKey in all four tables and it cut the query execution time in HALF! Still too long though, so I think I'm going to try it by dynamically building the FROM statement and see if that doesn't speed it up.

 

[SQLSister]

No I did not mean dymanically build the table. Do not use dynamic sql if it can be avoided as it is very insecure. I mean write two or more actual sql statments depending on the useage

IF @test1 is null
begin
select field1, field2 from table1
end
else
begin
select field1, field2 from table1 t1
join table2 t2 on t1.idfield = t2.idField
where t2.field3 = @test1
end

read this link to understand why you should not use dynamic SQl if it can be avoided.

http://www.sommarskog.se/dynamic_sql.html

"NOTHING is more important in a database than integrity." ESquared

 

[glgcag]

SQLSister,

Actually, sommarskog's site is where I went to get info on building my dynamic sql and I am implementing sommarskog's suggestions. I'm using sp_executesql with parameters.

I've looked at every one of sommerskog's suggestions and really, I have no other option as the requirements for this particular sp are any number of combinations for 17 separate parameters!

(By the way, that link is AWESOME! It has really helped me a lot!)

 

[SQLSister]

In that case make sure you pay close attention to the suggestion for the debug parameter! And make lots of test cases to test this, because there is plenty of room for error here.

"NOTHING is more important in a database than integrity." ESquared

 

[glgcag]

OK, I will review the debug parameter info. Again, thanks for all the help SQLSister!