Re: Using SQL profiler with cfqueryparam
I should have seen the solution eariler, or followed Nick's advice. The easy way to track long winded queries is the set the SQL profiler filter to include 'declare%' in the TextData field. The original query will be displayed, under the sp_prepexec, and then executed. So this query will execute exactly like further calls via the sp_exec statement (or dynamic SQL statement). Thanks Nick. --bj For the filter, I'm limiting my search to reads greater or equal to 5000. For the data columns, I'm selecting everything. For the events, I'm selecting Security Audit: Audit login, logout; Sessions: ExistingConnection; Stored Procedures: All; TSQL: SQL:BatchCompleted. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:247630 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Using SQL profiler with cfqueryparam
This is not an SP per se, it is just how SQL handles these dynamic SQL strings. Upon further research this is a SP. I pulled the following from Ray Camden's blog. So this: cfquery name=getUser datasource=#dsn# SELECTname, Email, userId FROM users WHERE username = cfqueryparam cfsqltype=CF_SQL_CHAR value=joe AND pin = cfqueryparam cfsqltype=CF_SQL_INTEGER value= /cfquery Translates into this: cfquery name=getUser datasource=#dsn# DECLARE @param1 varchar(50) DECLARE @param2 int = set @param1 = 'joe' set @param2 = SELECTname, Email, userId FROM users WHERE username = @param1 AND pin = @param2 /cfquery So once the declares are called, it's assigned a dynamic handler in SQL (for illustrative purposes handle number 8). Then calls to this query is handled by exec sp_execute statement. Now here's where I'm a bit confused. So in this example would ColdFusion call: exec sp_execute 8, 'joe', '' Or would Coldfusion call the about cfquery declaration SQL server is dynamically attributing the exec sp_execute statement? Or is something else happening. I'll do some more research on this post the results. But if anyone else knows, please feel free to point me in the right direction. --bj ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246885 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Using SQL profiler with cfqueryparam
As noted, it is not an true SP in the way you have a pre-compiled set of SQL (it is not stored within the DB itself)- it is straight forward dynamic SQL. What version of SQL are you running? When you run a trace (not on a production server) you get a choice of what columns/params you want log...they can be as deep or as limited as possiblelook at the Trace Settings/Options and see what you are actually logging, -Original Message- From: Billy Jamme [mailto:[EMAIL PROTECTED] Sent: 18 July 2006 16:01 To: CF-Talk Subject: Re: Using SQL profiler with cfqueryparam This is not an SP per se, it is just how SQL handles these dynamic SQL strings. Upon further research this is a SP. I pulled the following from Ray Camden's blog. So this: cfquery name=getUser datasource=#dsn# SELECTname, Email, userId FROM users WHERE username = cfqueryparam cfsqltype=CF_SQL_CHAR value=joe AND pin = cfqueryparam cfsqltype=CF_SQL_INTEGER value= /cfquery Translates into this: cfquery name=getUser datasource=#dsn# DECLARE @param1 varchar(50) DECLARE @param2 int = set @param1 = 'joe' set @param2 = SELECTname, Email, userId FROM users WHERE username = @param1 AND pin = @param2 /cfquery So once the declares are called, it's assigned a dynamic handler in SQL (for illustrative purposes handle number 8). Then calls to this query is handled by exec sp_execute statement. Now here's where I'm a bit confused. So in this example would ColdFusion call: exec sp_execute 8, 'joe', '' Or would Coldfusion call the about cfquery declaration SQL server is dynamically attributing the exec sp_execute statement? Or is something else happening. I'll do some more research on this post the results. But if anyone else knows, please feel free to point me in the right direction. --bj ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246888 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Using SQL profiler with cfqueryparam
This is not an SP per se, it is just how SQL handles these dynamic SQL strings. Upon further research this is a SP. sp_prepare() and sp_execute() are system stored procedures which respectively prepare and execute the SQL. The SQL code from your cfquery does not become a stored procedure, it's just a batch of SQL. When you use cfqueryparam, SQL server uses this execution model (aka prepared statements). I haven't checked this, but presumably if you're seeing the call to sp_execute(), you should be seeing the sp_prepare() as well, which will let you make the link between SQL code and parameter values. Nick ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246910 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Using SQL profiler with cfqueryparam
I'm running SQL Server 2000. The SP is dynamically compiled, and is stored in memory on the DB. The dynamic SP is accessed by handlers instead of actually calling the SP from disk (aka The true way). But tomato, tomato. (:) that doesn't type well does it?) For the filter, I'm limiting my search to reads greater or equal to 5000. For the data columns, I'm selecting everything. For the events, I'm selecting Security Audit: Audit login, logout; Sessions: ExistingConnection; Stored Procedures: All; TSQL: SQL:BatchCompleted. Unfortunately, I'm still unable to trace back to the original query. I'll run ethereal to sniff what's going to the DB back to CF. It'll give me an idea of what app is keeping track of the handler ID. I'll post results in a few hours. As noted, it is not an true SP in the way you have a pre-compiled set of SQL (it is not stored within the DB itself)- it is straight forward dynamic SQL. What version of SQL are you running? When you run a trace (not on a production server) you get a choice of what columns/params you want log...they can be as deep or as limited as possiblelook at the Trace Settings/Options and see what you are actually logging, ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246914 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Using SQL profiler with cfqueryparam
Hello All, I'm trying to track some slow running queries with MS SQL profiler. If I use cfqueryparam Coldfusion will automatically convert the query to a stored procedure. For example: cfquery name=test datasource=MSSQL SELECT id, user FROM userTable WHERE id = #form.id# AND user = #form.user# /cfquery In MS SQL profiler you will actually see this query in the TextData field; returning: SELECT id, user FROM userTable WHERE id = #form.id# AND user = #form.user# However, if you add cfqueryparams to variables in the query, you'll see: exec sp_execute 27, '12', '941' Where 27 is the stored procedure handler, '12' is the form.id variable and '941' is the form.user variable. My question is, where does ColdFusion store the handler information? In other words, how would I track exec sp_execute 27, '12', '941' to the actual query being called. Thanks in advance, Jim ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246808 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Using SQL profiler with cfqueryparam
This is not an SP per se, it is just how SQL handles these dynamic SQL strings. This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Billy Jamme [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Mon Jul 17 20:44:40 2006 Subject: Using SQL profiler with cfqueryparam Hello All, I'm trying to track some slow running queries with MS SQL profiler. If I use cfqueryparam Coldfusion will automatically convert the query to a stored procedure. For example: cfquery name=test datasource=MSSQL SELECT id, user FROM userTable WHERE id = #form.id# AND user = #form.user# /cfquery In MS SQL profiler you will actually see this query in the TextData field; returning: SELECT id, user FROM userTable WHERE id = #form.id# AND user = #form.user# However, if you add cfqueryparams to variables in the query, you'll see: exec sp_execute 27, '12', '941' Where 27 is the stored procedure handler, '12' is the form.id variable and '941' is the form.user variable. My question is, where does ColdFusion store the handler information? In other words, how would I track exec sp_execute 27, '12', '941' to the actual query being called. Thanks in advance, Jim ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246815 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Using SQL profiler with cfqueryparam
Thanks. But how do you track by this dynamic SQL string to the orignal parent query? This is not an SP per se, it is just how SQL handles these dynamic SQL strings. -Original Message- From: Billy Jamme [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Mon Jul 17 20:44:40 2006 Subject: Using SQL profiler with cfqueryparam Hello All, I'm trying to track some slow running queries with MS SQL profiler. If I use cfqueryparam Coldfusion will automatically convert the query to a stored procedure. For example: cfquery name=test datasource=MSSQL SELECT id, user FROM userTable WHERE id = #form.id# AND user = #form.user# /cfquery In MS SQL profiler you will actually see this query in the TextData field; returning: SELECT id, user FROM userTable WHERE id = #form.id# AND user = #form.user# However, if you add cfqueryparams to variables in the query, you'll see: exec sp_execute 27, '12', '941' Where 27 is the stored procedure handler, '12' is the form.id variable and '941' is the form.user variable. My question is, where does ColdFusion store the handler information? In other words, how would I track exec sp_execute 27, '12', '941' to the actual query being called. Thanks in advance, Jim ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246821 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Using SQL profiler with cfqueryparam
What filters are you using? What us your trace file setup? This e-mail is from Reed Exhibitions (Oriel House, 26 The Quadrant, Richmond, Surrey, TW9 1DL, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Billy Jamme [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Mon Jul 17 21:46:36 2006 Subject: Re: Using SQL profiler with cfqueryparam Thanks. But how do you track by this dynamic SQL string to the orignal parent query? This is not an SP per se, it is just how SQL handles these dynamic SQL strings. -Original Message- From: Billy Jamme [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com Sent: Mon Jul 17 20:44:40 2006 Subject: Using SQL profiler with cfqueryparam Hello All, I'm trying to track some slow running queries with MS SQL profiler. If I use cfqueryparam Coldfusion will automatically convert the query to a stored procedure. For example: cfquery name=test datasource=MSSQL SELECT id, user FROM userTable WHERE id = #form.id# AND user = #form.user# /cfquery In MS SQL profiler you will actually see this query in the TextData field; returning: SELECT id, user FROM userTable WHERE id = #form.id# AND user = #form.user# However, if you add cfqueryparams to variables in the query, you'll see: exec sp_execute 27, '12', '941' Where 27 is the stored procedure handler, '12' is the form.id variable and '941' is the form.user variable. My question is, where does ColdFusion store the handler information? In other words, how would I track exec sp_execute 27, '12', '941' to the actual query being called. Thanks in advance, Jim ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246825 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Using SQL profiler with cfqueryparam
Just one filter. Reads is greater than or equal to 1. What do you mean by trace file setup? I'm just running Profiler from the default tools in the MS SQL install. I connect to the DB analyze the queries in real-time. Can I track the sp_exec handler in SQL? What filters are you using? What us your trace file setup? Subject: Re: Using SQL profiler with cfqueryparam Thanks. But how do you track by this dynamic SQL string to the orignal parent query? stored ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/cf_lists/message.cfm/forumid:4/messageid:246831 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
