Query

Hi
Can someone tell me what is wrong with this query? It says it's ok but just
runs and doesn't time out.
qfm_GetEventSearchResult 'WHERE (EntryDate BETWEEN "2005/Jan/26" AND
"2005/Feb/09")
AND (Assignee.Name BETWEEN "Beattie Steve" AND "Beattie Steve")
AND (OnBehalf.Name BETWEEN "Beattie Steve" AND "Beattie Steve")', '500'Plz post the code of the sp qfm_GetEventSearchResult.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Jaco" <Jaco@.discussions.microsoft.com> wrote in message
news:05265C8E-2C69-4E69-8FFC-CF604D3296C9@.microsoft.com...
> Hi
> Can someone tell me what is wrong with this query? It says it's ok but
> just
> runs and doesn't time out.
> qfm_GetEventSearchResult 'WHERE (EntryDate BETWEEN "2005/Jan/26" AND
> "2005/Feb/09")
> AND (Assignee.Name BETWEEN "Beattie Steve" AND "Beattie Steve")
> AND (OnBehalf.Name BETWEEN "Beattie Steve" AND "Beattie Steve")', '500'
>|||sorry -
CREATE PROCEDURE qfm_GetEventSearchResult
@.param nvarchar(4000)= NULL,
@.maxRecords nvarchar(10) = NULL
AS
Declare @.SQL nvarchar(4000)
SET @.param = REPLACE(@.param,CHAR(34),CHAR(39))
set @.SQL = ' Select Distinct Top ' + @.maxRecords
set @.SQL = @.SQL + ' Event.EventID as Event,'
set @.SQL = @.SQL + ' EventProject.ProjectName as Proj,'
set @.SQL = @.SQL + ' Event.EntryDate AS Date,'
set @.SQL = @.SQL + ' dbo.fn_ShortName(1,EventType.Name) as Type,'
set @.SQL = @.SQL + ' Substring (EventStatus.Name,1,3) AS Status,'
--set @.SQL = @.SQL + '
dbo.fn_ModelOrService(Event.EventType,Model.Model,service.Service) as
Descrip,'
--join on express event to grab service or model name
set @.SQL = @.SQL + '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,'
set @.SQL = @.SQL + ' dbo.GeographyName(Event.GeographyID, 1, CHAR(47)) AS
Geography'
set @.SQL = @.SQL + ' FROM Event'
set @.SQL = @.SQL + ' LEFT JOIN qfmWorkTypeLookup as WorkType ON
WorkType.WorkTypeID = Event.WorkTypeID'
set @.SQL = @.SQL + ' LEFT JOIN qfmServiceGroup_ServiceLookup as service on
service.ServiceID = Event.ModelORServiceID'
set @.SQL = @.SQL + ' LEFT JOIN qfmLinksLookup Links ON Links.EventNumber =
Event.EventID' --AG: Fix for Defect 7993
set @.SQL = @.SQL + ' LEFT JOIN qfmAgentOperativeLookup Operative ON
Operative.EventID = Event.EventID' --Rhavy: Fix for Defect 2151
set @.SQL = @.SQL + ' LEFT JOIN Geography ON Geography.GeographyID =
Event.GeographyID'
set @.SQL = @.SQL + ' LEFT JOIN qfmSiteByGeogIDLookup AS G1 ON
Event.GeographyID = G1.GeographyID'
set @.SQL = @.SQL + ' LEFT JOIN qfmGeographyRegionLookup as GeographyRegion ON
GeographyRegion.regionID = G1.regionID' --Fix for defect 7242
set @.SQL = @.SQL + ' LEFT JOIN qfmGeographyAreaLookup as GeographyArea ON
GeographyArea.regionID = GeographyRegion.AreaID'
set @.SQL = @.SQL + ' LEFT JOIN qfmSectorLookup as Region ON Region.regionID =
GeographyArea.SectorID' --Fix for defect 7145
set @.SQL = @.SQL + ' LEFT JOIN qfmGeographySiteTypeLookup SiteType ON
SiteType.GeographyID = Geography.GeographyID' --Fix for Defect 3808
set @.SQL = @.SQL + ' INNER JOIN EventType ON Event.EventType =
EventType.EventTypeID'
set @.SQL = @.SQL + ' LEFT JOIN qfmPriorityListLookup Priority ON
Event.PriorityID = Priority.PriorityID'
set @.SQL = @.SQL + ' LEFT JOIN ContractorType'
set @.SQL = @.SQL + ' RIGHT JOIN Agent ON ContractorType.ContractorTypeID =
Agent.ContractorTypeID ON Event.AgentID = Agent.AgentID'
set @.SQL = @.SQL + ' LEFT JOIN qfmEventStatusLookup EventStatus ON
Event.EventStatusNo = EventStatus.EventStatusID'
set @.SQL = @.SQL + ' LEFT JOIN ClientSatisfaction ON
Event.ClientSatisfactionID = ClientSatisfaction.ClientSatisfactionID'
set @.SQL = @.SQL + ' LEFT JOIN qfmCostCodeLookup CostCode on Event.CostCodeID
= CostCode.CostCodeID'
set @.SQL = @.SQL + ' LEFT JOIN Inventory ON Inventory.InventoryID =
Event.InventoryID' --DD4968
set @.SQL = @.SQL + ' LEFT JOIN qfmEquipmentGroupLookup EquipmentGroup ON
Inventory.EquipmentGroupID = EquipmentGroup.EquipmentGroupID'
set @.SQL = @.SQL + ' LEFT JOIN qfmModelLookup Model ON Inventory.ModelID =
Model.ModelID'
set @.SQL = @.SQL + ' LEFT JOIN qfmDepartmentsLookup Departments ON
Inventory.DepartmentID = Departments.DepartmentsID'
set @.SQL = @.SQL + ' LEFT JOIN qfmDivisionLookup Division on
Division.DivisionID = Inventory.DivisionID'
set @.SQL = @.SQL + ' LEFT JOIN qfmCoverCodeLookup CoverCode ON
Event.CoverCodeID = CoverCode.CoverCodeID'
set @.SQL = @.SQL + ' LEFT JOIN qfmEventProjectlookup Project ON Event.EventID
= Project.EventID'
set @.SQL = @.SQL + ' LEFT JOIN qfmEventProjectListLookup EventProject ON
EventProject.EventsProjectId = Project.EventsProjectId'
set @.SQL = @.SQL + ' LEFT JOIN qfmSiteLocationLookup AS G2 ON
Event.GeographyID = G2.GeographyID'
set @.SQL = @.SQL + ' LEFT JOIN qfmSiteByUnitLookup AS G3 ON Event.GeographyID
= G3.GeographyID'
set @.SQL = @.SQL + ' LEFT JOIN qfmSiteByPartitionLookup G4 ON
Event.GeographyID = G4.GeographyID'
set @.SQL = @.SQL + ' LEFT JOIN qfmAllocationLookup Allocation ON
Event.CostAllocationID = Allocation.AllocationID'
set @.SQL = @.SQL + ' LEFT JOIN qfmPersonnelLookup Personnel ON
Event.ReportedBy = Personnel.Name'
set @.SQL = @.SQL + ' LEFT JOIN qfmOnBehalfLookup OnBehalf ON Event.OnBehalfID
= OnBehalf.PersonnelID'
set @.SQL = @.SQL + ' LEFT JOIN qfmAssigneeLookup Assignee ON Event.AssigneeID
= Assignee.PersonnelID'
set @.SQL = @.SQL + ' LEFT JOIN qfmContractLookup Contract ON Event.ContractID
= Contract.ContractID'
set @.SQL = @.SQL + ' LEFT JOIN qfmIRInvoiceDetailLookup IRInvoiceDetail ON
IRInvoiceDetail.EventID = Event.EventID'
set @.SQL = @.SQL + ' LEFT JOIN PurchaseOrderCost ON PurchaseOrderCost.EventID
= Event.EventID'
set @.SQL = @.SQL + ' LEFT JOIN qfmexpresseventlookup ExpEvent ON
ExpEvent.EventID = Event.EventID'
set @.SQL = @.SQL + ' ' + @.param + ' ORDER BY Event.EventID DESC'
Exec sp_executesql @.SQL
"Roji. P. Thomas" wrote:

> Plz post the code of the sp qfm_GetEventSearchResult.
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "Jaco" <Jaco@.discussions.microsoft.com> wrote in message
> news:05265C8E-2C69-4E69-8FFC-CF604D3296C9@.microsoft.com...
>
>|||On Wed, 9 Feb 2005 03:07:02 -0800, Jaco wrote:

>sorry -
>CREATE PROCEDURE qfm_GetEventSearchResult
>@.param nvarchar(4000)= NULL,
>@.maxRecords nvarchar(10) = NULL
>AS
(snip)
Hi Jaco,
Are you really sure that this is the only way to address your needs?
Usually, passing a where clause to a stored procedure is a token of a weak
design. Besides, it is incrediblyu dangerous - your system is wide open to
SQL injection attacks (see http://www.sommarskog.se/dynamic_sql.html for
an excellent explenations of these and other dangers of dynamic SQL).
If I didn't miscount, you are joining a total of THIRTY-SEVEN tables in
this join. And only one of them is joined using inner join, all others are
joined with a left or right outer join. Surely, you didn't _expect_ great
performance from this query?
Some tips that might (or might not - you'll have to try) help you speed
this up:
* Eliminate tables you don't need. You use a dynamically passed where
clause - do you join in some extra tables, just so they can be used in the
where clause if needed? If so, then eliminate them - you're just making
the DB engine do needless extra work.
* Remove the distinct. It's quite possible that you need it because you
join in to many tables; remove the unneeded tables, and the distinct
becomes redundant as well.
* Check the user-defined functions you are using. Could they be a
bottleneck? Remember that SQL Server will call them for each row in the
result set. If 80 duplicate rows get removed because of the DISTINCT, each
of the functions has already been called 80 times. If possible, remove
them or put the function's logic inline (so that SQL Server has more
optimization options).
* If you use a join only to test for existance or non-existance of a row
in a table, change it to [NOT] EXISTS (subquery).
* Change LEFT and RIGHT joins to INNER joins wherever possible.
* Change the way you call this proc. Instead of
qfm_GetEventSearchResult 'WHERE (EntryDate BETWEEN "2005/Jan/26" AND
"2005/Feb/09")
AND (Assignee.Name BETWEEN "Beattie Steve" AND "Beattie Steve")
AND (OnBehalf.Name BETWEEN "Beattie Steve" AND "Beattie Steve")', '500'
use
qfm_GetEventSearchResult 'WHERE (EntryDate BETWEEN "20050126" AND
"20050209")
AND (Assignee.Name = "Beattie Steve")
AND (OnBehalf.Name = "Beattie Steve")', '500'
(That is - use an unambiguous date format to prevent misconversions and
use equality instead of BETWEEN, since the upper and lower limit of the
between range are equal anyway)
* Check the execution plan. Which tables are being scanned? Which are
accessed using an index? Which are accessed very often? Maybe adding some
indexes will gain you some performance.
* Manage expectations :-) If you feed SQL Server a humongous query that
joins this amount of tables and calls several functions to boot, you can't
expect great performance. I'm afraid that the compilation of an execution
plan will already take quite some time - just consider the enormous number
of strategies the optimizer will have to choose from!
* And the most important advice: reconsider if you really need this
procedure to operate as it now does.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks.
"Hugo Kornelis" wrote:

> On Wed, 9 Feb 2005 03:07:02 -0800, Jaco wrote:
>
> (snip)
> Hi Jaco,
> Are you really sure that this is the only way to address your needs?
> Usually, passing a where clause to a stored procedure is a token of a weak
> design. Besides, it is incrediblyu dangerous - your system is wide open to
> SQL injection attacks (see http://www.sommarskog.se/dynamic_sql.html for
> an excellent explenations of these and other dangers of dynamic SQL).
> If I didn't miscount, you are joining a total of THIRTY-SEVEN tables in
> this join. And only one of them is joined using inner join, all others are
> joined with a left or right outer join. Surely, you didn't _expect_ great
> performance from this query?
> Some tips that might (or might not - you'll have to try) help you speed
> this up:
> * Eliminate tables you don't need. You use a dynamically passed where
> clause - do you join in some extra tables, just so they can be used in the
> where clause if needed? If so, then eliminate them - you're just making
> the DB engine do needless extra work.
> * Remove the distinct. It's quite possible that you need it because you
> join in to many tables; remove the unneeded tables, and the distinct
> becomes redundant as well.
> * Check the user-defined functions you are using. Could they be a
> bottleneck? Remember that SQL Server will call them for each row in the
> result set. If 80 duplicate rows get removed because of the DISTINCT, each
> of the functions has already been called 80 times. If possible, remove
> them or put the function's logic inline (so that SQL Server has more
> optimization options).
> * If you use a join only to test for existance or non-existance of a row
> in a table, change it to [NOT] EXISTS (subquery).
> * Change LEFT and RIGHT joins to INNER joins wherever possible.
> * Change the way you call this proc. Instead of
> qfm_GetEventSearchResult 'WHERE (EntryDate BETWEEN "2005/Jan/26" AND
> "2005/Feb/09")
> AND (Assignee.Name BETWEEN "Beattie Steve" AND "Beattie Steve")
> AND (OnBehalf.Name BETWEEN "Beattie Steve" AND "Beattie Steve")', '500'
> use
> qfm_GetEventSearchResult 'WHERE (EntryDate BETWEEN "20050126" AND
> "20050209")
> AND (Assignee.Name = "Beattie Steve")
> AND (OnBehalf.Name = "Beattie Steve")', '500'
> (That is - use an unambiguous date format to prevent misconversions and
> use equality instead of BETWEEN, since the upper and lower limit of the
> between range are equal anyway)
> * Check the execution plan. Which tables are being scanned? Which are
> accessed using an index? Which are accessed very often? Maybe adding some
> indexes will gain you some performance.
> * Manage expectations :-) If you feed SQL Server a humongous query that
> joins this amount of tables and calls several functions to boot, you can't
> expect great performance. I'm afraid that the compilation of an execution
> plan will already take quite some time - just consider the enormous number
> of strategies the optimizer will have to choose from!
> * And the most important advice: reconsider if you really need this
> procedure to operate as it now does.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>sql