Hopefully someone can help me with this. I have this query that executes in around 90ms or so:
--------------- <cfquery name="treetwig" datasource="#request.site.maindsn#"> SELECT Child.category_id, Child.category_name, (SELECT Count(*) FROM category_tree AS Child2, category_tree AS Parent2 WHERE Child2.StartBranch BETWEEN Parent2.StartBranch AND Parent2.EndBranch AND Child2.category_id = Child.category_id) AS lvl, P.pageid, P.Active FROM category_tree AS Parent, category_tree AS Child, tblPage AS P WHERE (Child.startbranch BETWEEN Parent.startbranch AND Parent.endbranch) AND Parent.category_id=#val(attributes.category_id)# AND Child.page_id = P.pageid AND P.Active = 1 ORDER BY Child.startbranch </cfquery> -------------- But when I turn it into a stored procedure using: -------------- <cfstoredproc procedure="pr_treetwig" datasource="#request.site.maindsn#"> <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@CategoryID" value="#val(attributes.category_id)#"> <cfprocresult name="treetwig" resultset="1"> </cfstoredproc> -------------- .. and the stored procedure looks like: -------------- CREATE PROCEDURE pr_treetwig @CategoryID [int] AS SELECT Child.category_id, Child.category_name, (SELECT Count(*) FROM category_tree AS Child2, category_tree AS Parent2 WHERE Child2.StartBranch BETWEEN Parent2.StartBranch AND Parent2.EndBranch AND Child2.category_id = Child.category_id) AS lvl, P.pageid, P.Active FROM category_tree AS Parent, category_tree AS Child, tblPage AS P WHERE (Child.startbranch BETWEEN Parent.startbranch AND Parent.endbranch) AND Parent.category_id= @CategoryID AND Child.page_id = P.pageid AND P.Active = 1 ORDER BY Child.startbranch GO -------------- It takes over twice as long to execute -- around 240ms. Can anyone tell me why? This has been driving me crazy. ^_^ Cody ______________________________________________________________________ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
