What specifically would I look for in the Query Analyzer. I'm not sure what I should be looking for that would tell me why it takes longer to execute as a stored procedure than if I sent the query from ColdFusion normally.
Thanks for helping.. I'm a Stored Procedure newbie. ^_^ Cody ----- Original Message ----- From: "Billy Cravens" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, January 17, 2002 12:52 PM Subject: Re: Stored Procedure Too Slow > 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 > > > > > > > ______________________________________________________________________ Why Share? Dedicated Win 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=coldfusionc 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
