Subquery question

[glgcag]

I have a query that gathers all information on our projects. (tProjects). All contacts working on the project are linked to this table from tContacts by project many to many tables. (ie: tProjects linked to tProjectProducers which is linked to tContacts. The same for tProjects -> tProjectDirectors -> tContacts.)

Currently I'm running a query that returns all linked contact fields. Hence, I can have hundreds of records for ONE project which means I have to do the sorting in my report, which is slow.


What if I were to run a sub-query for each contact type, instead of a join, and then loop through the contacts on that project and create a comma separated varchar variable to return in the query as "ProjectProducers", "ProjectDirectors", etc. so that I can just insert the value from the query into a field on the report?

Since there are 10 or so sub-queries that would have to be run, I'm wondering if for each contact type, looping through the few records to create a variable would be efficient.

As you've probably deduced by now, I'm new to T-SQL and SQL Server. I've been an Access programmer for some time now, so I'm not as efficient in SQL Server as I'd like to be and I may be on the wrong path in this approach anyway, so your help is appreciated!

 

[SQLSister]

I'm not sure exactly what you are asking, please post sample data and sample result set.

However, you should never loop through data if you can help it. Looping is one of the worst things you can do performancewise. Forget you ever heard of looping 9or cursors) is my advice.

Questions about posting. See faq183-874

 

[ESquared]

Access reports can have subreports which are linked to the main report on some criteria and which return just the sub-records for the main report's current record. Why not just use that functionality?

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[glgcag]

Here's the SELECT statement, it's pretty ugly. ESquared, I am currently using subreports to separate the joined records, but it's a lot of work, when returning a short list of names is easier and doesn't require all the subreports this will require.

SELECT tProductionCompanies_1.ProductionCompanyDesc AS CoProdCompany, tProductionCompanies.ProductionCompanyDesc, 
tAGProjects.AGProjectKey, tAGProjects.ProjectTitle, tClients.FirstName AS [Client First Name], tClients.LastName AS 
[Client Last Name], tAgents.AgentKey, tAgents.AgentName, [tMailList].[FirstName] + ' ' +  [tMailList].[LastName] AS Producer, 
[tMailList_1].[FirstName] + ' ' + [tMailList_1].[LastName] AS ExecProducer, [tMailList_2].[FirstName] + ' ' + [tMailList_2].[LastName] 
AS Director, [tMailList_3].[FirstName] + ' ' +  [tMailList_3].[LastName] AS Contact, 
[tMailList_4].[FirstName] + ' ' +  [tMailList_4].[LastName] AS [Cast], tProjectSources.Source, tProjectTypes.ProjectTypeDesc, 
tProjectType2s.ProjectType2Desc, tGenre.GenreDesc, tAGProjects.Network, tProductionStatus.ProductionStatusDesc, 
tAGProjects.ClosedProject, tAGProjects.ClosedDate, tAGProjects.MusicSupervisor, tAGProjects.Location, tAGProjects.ProdStartDate, 
tAGProjects.WrapShootDate, tAGProjects.SpottingDate, tAGProjects.RecordingDate, tAGProjects.MixingDate, tAGProjects.DeliveryDate , 
tAGProjects.ReleaseDate, tAGProjects.FinalDub, tAGProjects.FilmTVBudget, tAGProjects.MusicBudget, tAGProjects.Synopsis, 
tAGProjects.ProjectStatus, tAGProjects.NonClientFinalOut, tAGProjects.ProdOfficePhone

FROM (tMailList RIGHT JOIN (tClients RIGHT JOIN ((((((((((((((tAGProjects LEFT JOIN tProductionCompanies ON 
tAGProjects.ProductionCompanyKey = tProductionCompanies.ProductionCompanyKey) LEFT JOIN tProductionCompanies AS tProductionCompanies_1 ON 
tAGProjects.CoProductionCompanyKey = tProductionCompanies_1.ProductionCompanyKey) LEFT JOIN tProjectTypes ON tAGProjects.ProjectTypeKey = 
tProjectTypes.ProjectTypeKey) LEFT JOIN tProjectType2s ON tAGProjects.Type2Key = tProjectType2s.ProjectType2Key) LEFT JOIN 
tGenre ON tAGProjects.GenreKey = tGenre.GenreKey) LEFT JOIN tProductionStatus ON tAGProjects.ProductionStatusKey = 
tProductionStatus.ProductionStatusKey) LEFT JOIN (tProjectAgents LEFT JOIN tAgents ON tProjectAgents.AgentKey = 
tAgents.AgentKey) ON tAGProjects.AGProjectKey = tProjectAgents.AGProjectKey) LEFT JOIN (tProjectCast LEFT JOIN tMailList AS 
tMailList_4 ON tProjectCast.ContactKey = tMailList_4.MailListKey) ON tAGProjects.AGProjectKey = tProjectCast.AGProjectKey) 
LEFT JOIN tProjectClients ON tAGProjects.AGProjectKey = tProjectClients.AGProjectKey) LEFT JOIN (tProjectContacts LEFT JOIN tMailList 
AS tMailList_3 ON tProjectContacts.ContactKey = tMailList_3.MailListKey) ON tAGProjects.AGProjectKey = 
tProjectContacts.AGProjectKey) LEFT JOIN (tProjectDirectors LEFT JOIN tMailList AS tMailList_2 ON tProjectDirectors.ContactKey = 
tMailList_2.MailListKey) ON tAGProjects.AGProjectKey = tProjectDirectors.AGProjectKey) LEFT JOIN 
(tProjectExecProducers LEFT JOIN tMailList AS tMailList_1 ON tProjectExecProducers.ContactKey = tMailList_1.MailListKey) ON 
tAGProjects.AGProjectKey = tProjectExecProducers.AGProjectKey) LEFT JOIN tProjectProducers ON tAGProjects.AGProjectKey = 
tProjectProducers.AGProjectKey) LEFT JOIN tProjectSources ON tAGProjects.AGProjectKey = tProjectSources.AGProjectKey) 
ON tClients.ClientNumber = tProjectClients.ClientKey) ON tMailList.MailListKey = tProjectProducers.ContactKey)

Essentially, I would like to perform a SELECT DISTINCT tAGProjects, with a join to the "one-to-one" tables (tProjectTypes, tProjectType2s, tProductionStatus) and the rest of the tables need to return a list of the items, if that makes sense. (ie: a list of "Agents", "Genres", "ProjectSources", "ProdCos", "Clients", etc.)

I might be trying to do too much with a stored proc, but I thought I'd give it a try. (Ultimately, this will be on a private web portal as well, so this will definitely help there as well.)

I hope this helps!

 

[glgcag]

ESquared,

I tested this using Access reporting as the "sorter" of the records, and for the 500 projects I was attempting to retrieve, the record count was over ten thousand when all the joined records were gathered. Therefore, when the report was attempting to sort through all ten thousand records, it took FOREVER! So, I think there has to be a better way.

 

[SQLSister]

10000 isn;t very many records. Perhaps you need to look at the indexing?

Questions about posting. See faq183-874

 

[glgcag]

The problem wasn't in SQL Server gathering the records, it was in relying on Access to sort and group the data. So what I've done is:

Instead of gathering all records using the above query, I'm gathering the distinct projects, so that there's say 500 records, instead of 10000

But now, I'd like to use those 500 projects to create other recordsets that I can use on my report, that gather all the "Agents", "Directors", etc. for each project. Would maybe creating a stored procedure that returns multiple recordsets based on the initial 500 or so projects be the solution?