What does the execution plan in Query Analyzer tell you? ----- Original Message ----- From: "Cody" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, January 17, 2002 11:18 AM Subject: Stored Procedure Too Slow
> 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
