I had always presumed that a Stored Procedure ran faster than a SQL query...
the problem is that when I actually run speed tests, it's about the same
speed...
I have an app which works out if a job is chargable by the amount of work
already done on existing jobs for the client (i.e. if a client has 20 hours
of pre-paid work, then anything over that is "chargable") - but it's meant
to be dynanic, so we can't store the "remaining" hours in a field or cache
the query as new jobs are constantly added and work continually done
The SQL looks like this;
Select Distinct r.RequestId, r.Title, r.DateEntered,
r.RapidResponse, p.Name, p.ProjectID, r.maintenance,
Sum(t.estimatedHours) as TotalHours, p.DaysMaintenance
>From Request as r, Project as P, Task as T
Group By r.AlltasksAssigned, t.requestId, r.projectId, P.Name,
r.Title, r.RequestId, p.ProjectID, r.maintenance,
p.DaysMaintenance, r.RapidResponse, r.DateEntered,
r.price, r.Maintenance, r.AuthorisedBy
Having r.ProjectId=P.ProjectId
AND r.RequestID=T.RequestID
AND (r.Rapidresponse='Yes'
OR not r.Maintenance='Error')
and r.AuthorisedBy Is Null
and r.Price is NULL
AND r.AlltasksAssigned is not null
And NOT R.RequestId In (Select distinct t.RequestId
From TaskEffort as te, task as t
where Te.taskid=t.task)
order by r.DateEntered
Currently, the SQL query takes about 2 seconds (varies between 1900ms and
2100ms) and the Stored Procedure (which is the same, apart from having
"Create Procedure ... As" in front) runs at about 2150ms average
I'm currently calling the SP with <cfstoredproc>
There are indexes in place where I can, but it's just damn confusing why the
SP runs slower than the SQL...
Just so you know, CF4.0.1, SQL Server 7 SP2, NT4 SP6a - I know they
shouldn't all be on the same machine, but what can you do with limited
budgets?
Any help would be greatly appreciated...
Philip Arnold
Director
Certified ColdFusion Developer
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
