Sproc - 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 2000 Forums

Transact-SQL (2000)

Sproc

Author

Topic

jacowess
Starting Member

1 Post

Posted - 2005-04-22 : 10:47:39

Hi

I have a sproc with a lot of joins (need all the joins as the query can have multiple searches) The problem is that when searching a large record set the query times out.
Do anyone have any pointers?

Select Distinct Top 999 Event.EventID as Event,

EventProject.ProjectName as Proj,
Event.EntryDate AS Date,
dbo.fn_ShortName(1,EventType.Name) as Type,
Substring (EventStatus.Name,1,3) AS Status,
dbo.fn_ModelOrService(Event.EventType,
case when ExpEvent.eventid is null then Model.Model else ExpEvent.Model end,
case when ExpEvent.eventid is null then service.Service else ExpEvent.service end)
as Descrip, Geography.site + '/' + Geography.location + '/' + Geography.unit + '/' + Geography.partition AS Geography

FROM Event

LEFT JOIN qfmWorkTypeLookup as WorkType ON WorkType.WorkTypeID = Event.WorkTypeID
LEFT JOIN qfmServiceGroup_ServiceLookup as service on service.ServiceID = Event.ModelORServiceID
LEFT JOIN qfmAgentOperativeLookup Operative ON Operative.AgentID = Event.AgentID
LEFT JOIN Geography ON Geography.GeographyID = Event.GeographyID
LEFT JOIN qfmSiteByGeogIDLookup AS G1 ON Event.GeographyID = G1.GeographyID
LEFT JOIN qfmGeographyRegionLookup as GeographyRegion ON GeographyRegion.regionID = G1.regionID
LEFT JOIN qfmGeographyAreaLookup as GeographyArea ON GeographyArea.regionID = GeographyRegion.AreaID
LEFT JOIN qfmSectorLookup as Region ON Region.regionID = GeographyArea.SectorID
LEFT JOIN qfmGeographySiteTypeLookup SiteType ON SiteType.GeographyID = Geography.GeographyID
INNER JOIN EventType ON Event.EventType = EventType.EventTypeID
LEFT JOIN qfmPriorityListLookup Priority ON Event.PriorityID = Priority.PriorityID
LEFT JOIN ContractorType
RIGHT JOIN Agent ON ContractorType.ContractorTypeID = Agent.ContractorTypeID ON Event.AgentID = Agent.AgentID
LEFT JOIN qfmEventStatusLookup EventStatus ON Event.EventStatusNo = EventStatus.EventStatusID
LEFT JOIN ClientSatisfaction ON Event.ClientSatisfactionID = ClientSatisfaction.ClientSatisfactionID
LEFT JOIN qfmCostCodeLookup CostCode on Event.CostCodeID = CostCode.CostCodeID
LEFT JOIN Inventory ON Inventory.InventoryID = Event.InventoryID
LEFT JOIN qfmEquipmentGroupLookup EquipmentGroup ON Inventory.EquipmentGroupID = EquipmentGroup.EquipmentGroupID
LEFT JOIN qfmModelLookup Model ON Inventory.ModelID = Model.ModelID
LEFT JOIN qfmDepartmentsLookup Departments ON Inventory.DepartmentID = Departments.DepartmentsID
LEFT JOIN qfmDivisionLookup Division on Division.DivisionID = Inventory.DivisionID
LEFT JOIN qfmCoverCodeLookup CoverCode ON Event.CoverCodeID = CoverCode.CoverCodeID
LEFT JOIN qfmEventProjectlookup Project ON Event.EventID = Project.EventID
LEFT JOIN qfmEventProjectListLookup EventProject ON EventProject.EventsProjectId = Project.EventsProjectId
LEFT JOIN qfmSiteLocationLookup AS G2 ON Event.GeographyID = G2.GeographyID
LEFT JOIN qfmSiteByUnitLookup AS G3 ON Event.GeographyID = G3.GeographyID
LEFT JOIN qfmSiteByPartitionLookup G4 ON Event.GeographyID = G4.GeographyID
LEFT JOIN qfmAllocationLookup Allocation ON Event.CostAllocationID = Allocation.AllocationID
LEFT JOIN qfmPersonnelLookup Personnel ON Event.ReportedBy = Personnel.Name
LEFT JOIN qfmOnBehalfLookup OnBehalf ON Event.OnBehalfID = OnBehalf.PersonnelID
LEFT JOIN qfmAssigneeLookup Assignee ON Event.AssigneeID = Assignee.PersonnelID
LEFT JOIN qfmContractLookup Contract ON Event.ContractID = Contract.ContractID
LEFT JOIN qfmIRInvoiceDetailLookup IRInvoiceDetail ON IRInvoiceDetail.EventID = Event.EventID
LEFT JOIN PurchaseOrderCost ON PurchaseOrderCost.EventID = Event.EventID
LEFT JOIN qfmexpresseventlookup ExpEvent ON ExpEvent.EventID = Event.EventID
WHERE (EntryDate BETWEEN '2005/Jan/06' AND '2005/Apr/20') AND (Assignee.Name BETWEEN '.DONSON RAY' AND '.DONSON RAY') ORDER BY Event.EventID DESC

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-22 : 14:54:00

>>need all the joins as the query can have multiple searches

Do you mean you have a lot of input parameters? In other words you have a lot of possible things to search by?
If any given instance of the SP only uses a small portion of these tables, you should probably consider sp_executesql. There are several disadvantages of dynamic sql but one big advantage is being able to simplify a select statement when there are a whole bunch of optional search parameters.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources