Has anyone tested simple cached queries with CFQUERY against the
performance of sp_executesql and SQL statement strings? Functionally
it's exactly the same as cfquery in that variables are replaced with
values... however, the DB does the work and the CF server simply
issues directives and receives
Andy Ousterhout wrote:
> I thought that even with simple queries that Stored Procs where much faster?
That is not my experience. YMMV
Jochem
~|
Logware: a new and convenient web-based time tracking application. Start
tracking a
Caching with CFQUERY? To be honest I rarely use CFQUERY now apart from QoQ
so I am a tad rusty on its ins and outs ;-)
-Original Message-
From: Andy Ousterhout [mailto:[EMAIL PROTECTED]
Sent: 21 January 2005 13:51
To: CF-Talk
Subject: RE: Stored Proc Question
Good point. But you
Good point. But you can't use query caching without placing in application
or session scope and still use cfqueryparam
-Original Message-
From: Robertson-Ravo, Neil (RX)
In some cases yes but in reality if you are using query caching with cfquery
it can be just as fast. For simple select
faster such as use of memory etc. It is not all about how fast you get
results back.
-Original Message-
From: Andy Ousterhout [mailto:[EMAIL PROTECTED]
Sent: 21 January 2005 00:13
To: CF-Talk
Subject: RE: Stored Proc Question
I thought that even with simple queries that Stored Procs where
I thought that even with simple queries that Stored Procs where much faster?
I tried this on a couple of other SELECTS and got noticable improvement. By
the way, I always use CFQUERRPARAM.
Andy
-Original Message-
From: Jochem van Dieten
Andy Ousterhout wrote:
> Here is the proc that I a
Andy Ousterhout wrote:
> Here is the proc that I am using. You suggest either 2 separate queries or
> 2 procs?
> WHERE ((@InvoiceNumber IS NOT NULL) AND (tabInvoices.InvoiceNumber
> [EMAIL PROTECTED]))
> OR ((@PeachtreeKEY IS NOT NULL) AND (@PeachtreeInvoice IS
> NOT
Here is the proc that I am using. You suggest either 2 separate queries or
2 procs?
create proc spreadInvoice
@InvoiceNumber int, /*Provide either Invoice Number or both
*/
@PeachTreeInvoice char(21), /* Peachtree Invoice Number and */
@PeachtreeKey cha
Andy Ousterhout wrote:
> Lets say that I need to look up an invoice by 2 different mechanisms:
> Internal reference/Key
> 2 strings
>
> Will the execution plan for a stored proc for the Key be different enough from
> one for the strings to justify creating 2 stored procs? Right now I've g
Lets say that I need to look up an invoice by 2 different mechanisms:
Internal reference/Key
2 strings
Will the execution plan for a stored proc for the Key be different enough from
one for the strings to justify creating 2 stored procs? Right now I've got a
single stored proc that does b
Thanks for everyones input!
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfu
I guess I could try this
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 05, 2003 10:07 AM
To: CF-Talk
Subject: Re: Stored Proc Question Help Please
Eric Creese wrote:
> This is a very simple question.
>
> I need to do this in SQL Se
Eric Creese wrote:
> This is a very simple question.
>
> I need to do this in SQL Server from a job not a CF page.
>
> I have a table where I store customer IDs. I want to pull the individual IDs and
> loop each ID out of the table and run it against another query and write that output
> of th
Thanks I will see what I can do with this. I appreciate you time and your help.
-Original Message-
From: John Stanley [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 05, 2003 9:53 AM
To: CF-Talk
Subject: RE: Stored Proc Question Help Please
The syntax I have attached actually uses two
lto:[EMAIL PROTECTED]
Sent: Thursday, June 05, 2003 7:29 AM
To: CF-Talk
Subject: Stored Proc Question Help Please
This is a very simple question.
I need to do this in SQL Server from a job not a CF page.
I have a table where I store customer IDs. I want to pull the individual IDs and loop
each
What's the relationship between the two tables?
Ade
-Original Message-
From: Eric Creese [mailto:[EMAIL PROTECTED]
Sent: 05 June 2003 15:29
To: CF-Talk
Subject: Stored Proc Question Help Please
This is a very simple question.
I need to do this in SQL Server from a job not a CF
Although a trigger would be better. Much more efficient in this case.
WG
-Original Message-
From: Haggerty, Mike [mailto:[EMAIL PROTECTED]
Sent: 05 June 2003 15:48
To: CF-Talk
Subject: RE: Stored Proc Question Help Please
You need to write a cursor, it sounds like. Take a look in the
or
select * from #temp_origin_records
union all
select * from #temp_dest_records order by company_id, summar
y_id desc
drop table #temp_origin_records
drop table #temp_dest_records
-Original Message-
From: Eric Creese [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 05, 2003 10:29 AM
To:
You need to write a cursor, it sounds like. Take a look in the books online
to get a sense of how it works.
M
-Original Message-
From: Eric Creese [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 05, 2003 10:29 AM
To: CF-Talk
Subject: Stored Proc Question Help Please
This is a very
This is a very simple question.
I need to do this in SQL Server from a job not a CF page.
I have a table where I store customer IDs. I want to pull the individual IDs and loop
each ID out of the table and run it against another query and write that output of the
query to a tmpTable. How do I do
Hi again,
OK, in our current application I,m turning all our queries to stored procs,
and its starting to feel like maybe I've bitten off too much, as my
knowledge of t-sql is one week, I've ordered a book by Garth Wells, 'T-SQL
programming with Stored Procedures', waiting for it to arrive.
OK s
especially useful if you need to refer to the rowcount in more than
one place.
My $.25 worth...
Shawn Grover
-Original Message-
From: Andy Ewings [mailto:[EMAIL PROTECTED]]
Sent: Monday, August 13, 2001 10:56 AM
To: CF-Talk
Subject: RE: Stored Proc Question
First Query here
IF @@r
Barleycorn [mailto:[EMAIL PROTECTED]]
Sent: 13 August 2001 17:49
To: CF-Talk
Subject: Stored Proc Question
Hello, i'm running a stored proc that contains two queries. if the first
query runs and does not return records, i need to run the second query. Can
anyone tell me the equivilent of recordCou
If your running a SELECT statement you can include COUNT(*) as an extra
column. If you are running another SQL statement which does not return a
recordset (INSERT, DELETE, UPDATE) you can check the @@ROWCOUNT something
like:
IF @@ROWCOUNT = 0
Cheers,
Bill
In a message dated 8/13/01 1
> Hello, i'm running a stored proc that contains two queries.
> if the first query runs and does not return records, i need
> to run the second query. Can anyone tell me the equivilent
> of recordCount in Transact-SQL? Thanks.
You should be able to use @@ROWCOUNT for this.
Dave Watts, CTO, Fi
Hello, i'm running a stored proc that contains two queries. if the first
query runs and does not return records, i need to run the second query. Can
anyone tell me the equivilent of recordCount in Transact-SQL? Thanks.
~~
Structure your ColdFusion
> then you can refer to #insertRow.table1_ID# and it will
> contain the ID that was inserted for you by SQL Server,
> despite no such variable/column being mentioned in the
> , because the SQL Server ODBC driver returns the
yes. that value is created by sql server & passed back to
whatever app ca
I always like to use explicit references if possible-- @@IDENTITY is used
here because it is the only way to do this.
"Faster" in terms of time difference between these two techniques should
not even be a consideration because, for all intents and purposes, it is
negligible, and there are countle
thing?
-Original Message-
From: Adam Phillip Churvis [mailto:[EMAIL PROTECTED]]
Sent: 25 August 2000 14:33
To: [EMAIL PROTECTED]
Subject: RE: SQL Trigger / Stored Proc question
Sorry, my friend, but you are wrong here. Triggers can contain hundreds of
lines of complicated code to enforce
..a trigger cannot contain a select and therefore cannot return
>a resultset or return a parameter
>
>-Original Message-
>From: DeVoil, Nick [mailto:[EMAIL PROTECTED]]
>Sent: 25 August 2000 11:38
>To: '[EMAIL PROTECTED]'
>Subject: RE: SQL Trigger / Stored Proc que
quite righta trigger cannot contain a select and therefore cannot return
a resultset or return a parameter
-Original Message-
From: DeVoil, Nick [mailto:[EMAIL PROTECTED]]
Sent: 25 August 2000 11:38
To: '[EMAIL PROTECTED]'
Subject: RE: SQL Trigger / Stored Proc question
&
Paul,
> triggers do have their place in development toolboxes.
Sure, I never said otherwise. Triggers are great.
I've built systems that rely completely on them.
> > But what is the query that queryName refers to?
>
> the cfquery for the original insert.
>
> > Are you saying that CF is clever
lets nip this myth in the bud
> But what is the query that queryName refers to?
the cfquery for the original insert.
> Are you saying that CF is clever enough to pick up the fact that the
> trigger has fired & grab the value from somewhere?
no, but ODBC & sql server are...
> The SQL S
> > I don't think you can pass a value out from a trigger - once the trigger
>
> sure you can. you can reference its value in queryName.mytable_ID
But what is the query that queryName refers to?
If you say CREATE TRIGGER...AS SELECT mytable_ID...
then what is executed is the CREATE TRIGGER st
> I don't think you can pass a value out from a trigger - once the trigger
sure you can. you can reference its value in queryName.mytable_ID
--
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubsc
> CREATE TRIGGER GetMax_ID ON mytable
> FOR INSERT
> AS
> SELECT mytable_ID FROM INSERTED
>
> How would I reference the result within a stored procedure to insert
> the "mytable_id" in the next query?
Neil
I don't think you can pass a value out from a trigger - once the trigger
is created it ex
Lets say I use the standard insert trigger like so:
CREATE TRIGGER GetMax_ID ON mytable
FOR INSERT
AS
SELECT mytable_ID FROM INSERTED
How would I reference the result within a stored procedure to insert the
"mytable_id" in the next query?
Thanks,
Neil
p.s. Maybe someone knows why CFTree's fol
Hi,
Just wondering if anyone can help with a SQL Server 7 Stored Procedure
question.
I have a stored procedure that returns a group of fields from a table. What
I'd like to do is return a field which is the result of another stored
procedure that does something to one of the fields from the t
38 matches
Mail list logo
