When you are in Query Analyzer make sure that the "Show Execution Plan" is selected (under the Query menu). Once this is selected place both your query and an execute of your stored procedure in the window and execute them together.
You will see a new tab labeled "Execution Plan". Clicking this tab will show you how SQL Server retrieved the data. You are looking to see if both the query and the SP retrieve the data the same way. Also, each part of the query is broken out and you can see what part takes the most time. Hope this helps, if you have any more questions feel free to ask. ______________________________________________________ Bill Grover Supervisor MIS Phone: 301.424.3300 x3324 EU Services, Inc. FAX: 301.424.3696 649 North Horners Lane E-Mail: [EMAIL PROTECTED] Rockville, MD 20850-1299 WWW: http://www.euservices.com ______________________________________________________ > -----Original Message----- > From: Cody [mailto:[EMAIL PROTECTED]] > Sent: Thursday, January 17, 2002 2:28 PM > To: CF-Talk > Subject: Re: Stored Procedure Too Slow > > > 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 > > > > > > > > > > > > ______________________________________________________________________ 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
