RE: Performance Question Query
Probably none. Preferred approach would be to use cfstoredproc as you can then return multiple resultsets which you cannot do with a cfquery. -Original Message- From: Brian Peddle [mailto:[EMAIL PROTECTED] Sent: 16 January 2006 16:55 To: CF-Talk Subject: Performance Question Query Anyone know if there any performance difference between running a stored proc like this? Proc_test value1,value2,value3 As opposed to THANKS! ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:229680 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Performance Question
For simple queries there is no performance improvement whatsoever using stored procedures. Micha Schopman From: Steve Brownlee [mailto:[EMAIL PROTECTED] Sent: Thu 4/7/2005 5:26 PM To: CF-Talk Subject: RE: Performance Question One query is (almost) always more efficient than three. You might also want to consider using a stored procedure and calling it with the tag. Even faster that way. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201945 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Performance Question
factory = createObject( "java", "coldfusion.server.ServiceFactory" );
ds = factory.getDataSourceService().getDatasource( "dsn" );
conn = ds.getConnection();
stmt = conn.createStatement();
stmt.execute( "select * from A; select * from B" );
rs = stmt.getResultSet();
while( rs.next() ) {
writeOutput(rs.getString( "tableAcolumn" )& '');
}
if( stmt.getMoreResults() ) {
rs = stmt.getResultSet();
}
while( rs.next() ) {
writeOutput( rs.getString( "tableBcolumn" ) & '' );
}
rs.close();
stmt.close();
conn.close();
> I have a situation that could be implemented in two different ways and
> looking to see which situation would be best:
> I have 3 result sets that i need to obtain...i could query the
> database 3 times to obtain each one or query the database once and
> then filter the result in cf using cfquery...in the long run is the
> cfquery slower at filtering than querying the db 3 times? lets say we
> would be dealing with 300-500 records in total.
~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201943
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Performance Question
Jason Rogoz wrote: >I have a situation that could be implemented in two different ways and looking >to see which situation would be best: >I have 3 result sets that i need to obtain...i could query the database 3 >times to obtain each one or query the database once and then filter the result >in cf using cfquery...in the long run is the cfquery slower at filtering than >querying the db 3 times? lets say we would be dealing with 300-500 records in >total. > > Answer to all performance questions: It depends But you may also be able to create a VIEW on the db, and simply query that. Or you could possibly use temp tables. It also depends on which physical machine is faster, your db server or your cf server (assuming they are on different machines). In general DB servers are usually better at filtering the data than cf would be. __ Pete Freitag work: http://www.cfdev.com/ blog: http://www.petefreitag.com/ shop: http://www.dealazon.com/ Author of the CFMX Developers Cookbook http://www.petefreitag.com/bookshelf/ ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201898 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Performance Question
Jason Rogoz wrote: > I have a situation that could be implemented in two different ways and > looking to see which situation would be best: > I have 3 result sets that i need to obtain...i could query the database 3 > times to obtain each one or query the database once and then filter the > result in cf using cfquery...in the long run is the cfquery slower at > filtering than querying the db 3 times? lets say we would be dealing with > 300-500 records in total. Depends on the queries. Just test it. Jochem ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201884 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Performance Question
why not write both and time the execution? -Original Message- From: Jason Rogoz [mailto:[EMAIL PROTECTED] Sent: 07 April 2005 16:21 To: CF-Talk Subject: Performance Question I have a situation that could be implemented in two different ways and looking to see which situation would be best: I have 3 result sets that i need to obtain...i could query the database 3 times to obtain each one or query the database once and then filter the result in cf using cfquery...in the long run is the cfquery slower at filtering than querying the db 3 times? lets say we would be dealing with 300-500 records in total. ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201882 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Performance Question
One query is (almost) always more efficient than three. You might also want to consider using a stored procedure and calling it with the tag. Even faster that way. -Original Message- From: Jason Rogoz [mailto:[EMAIL PROTECTED] Sent: Thursday, April 07, 2005 11:21 AM To: CF-Talk Subject: Performance Question I have a situation that could be implemented in two different ways and looking to see which situation would be best: I have 3 result sets that i need to obtain...i could query the database 3 times to obtain each one or query the database once and then filter the result in cf using cfquery...in the long run is the cfquery slower at filtering than querying the db 3 times? lets say we would be dealing with 300-500 records in total. ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:201882 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Performance question - CFINCLUDE versus writing a cus
Stored Procedures are a database function. I suggest looking in your database docs. I've done some work with Sybase SQL Anywhere a few years back. If memory serves me, they had something called views, which were akin to stored procedures. Don't hold me to that though. I'm not sure what you mean when you say Sybase IQ. New product? Same Product? I haven't kept up on the Sybase product line. Once you have the stored procedure created, you can reference it through SQL or using the CFSTOREDPROC tag. Carol Bluestein wrote: > We are managing huge SYBASE IQ databases and looking for ways to increase > performance. Your comment on stored procedures (5x quicker) really caught my > attention since our queries are also huge. I check allaire's knowledge base and > my current CF books (incl.Forta's Advanced Cold Fusion 4.0) and can find > nothing extensive. Is this all there is or are there other sources of > information > re: STORED PROCEDURES. Any help with information or pointing me towards > information would be very helpful. > > Thanks, > Carol > > Carol L. Bluestein > Senior Programmer > NYS Office of Real Property > 518-486-6335 > [EMAIL PROTECTED] > > ____Reply Separator > Subject:Re: Performance question - CFINCLUDE versus writing a custom > Author: [EMAIL PROTECTED] > Date: 1/30/01 5:24 PM > > Converting intensive queries to stored procedures is the single most > performance tweak you can do for a CF site. I've seen SP's perform up to 5 > times as fast as a regular > > jon > - Original Message - > From: "Kevin Gilchrist" <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Tuesday, January 30, 2001 4:00 PM > Subject: Re: Performance question - CFINCLUDE versus writing a custom CF > > > Unfortunately stored procedures aren't an option. > > What would that gain me anyway? Is it just more efficient for the queries > > to be executed by the DB (Oracle in this case)? > > > > Going by Philip's answer, I guess doing CFINCLUDE'a are the way to go. > > > > "Steve Bernard" <[EMAIL PROTECTED]> wrote in message > > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > > Are stored procedures not an option? > > > > > > Steve > > > > > > -Original Message- > > > From: Kevin Gilchrist [mailto:[EMAIL PROTECTED]] > > > Sent: Tuesday, January 30, 2001 12:07 PM > > > To: CF-Talk > > > Subject: Performance question - CFINCLUDE versus writing a custom CF > > > > > > > > > Hi all, > > > > > > I'm writing a web app that acts as a dashboard for an MSP, i.e. a GUI > that > > > shows the status of all sorts of applications, network and host > devices. > > I > > > need to write quite a few select queries for each device/host/app type, > so > > > will end up with ~150 different queries. > > > > > > What I'm wondering about is if anyone has any thoughts on whether it's > > > better to access these queries from the GUI templates using > > > > > > - a series of conditional CFINCLUDE's that would include a smallish > query > > > template. > > > > > > - or write a custom CF tag with all the queries inside and pass three > > > parameters that would give me a unique query name. A three deep CFWITCH > > > statement could home in on the relevant query. > > > > > > Is it correct to assume that it's a bigger hit on CF to call on a > > relatively > > > large templates even though it's doesn't have to process much of it (cf_ > > > tag), as opposed to including whole but small templates? > > > I guess it may be better organizationally to implement the queries as a > > > bunch of small files to include? > > > > > > It seems like a case of six in one, half a dozen in the other but I'll > > never > > > know if I don't ask... > > > > > > Thanks, > > > Kevin > > > > > > P.S. I intend to convert it all to fusebox for version 2. > > > > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Performance question - CFINCLUDE versus writing a custom CF
> Converting intensive queries to stored procedures is the single most > performance tweak you can do for a CF site. I've seen SP's perform up to 5 > times as fast as a regular Saying that - if your queries/SPs are cached, then you're going to see dramatic improvements again Philip Arnold Director Certified ColdFusion Developer ASP Multimedia Limited T: +44 (0)20 8680 1133 "Websites for the real world" ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Performance question - CFINCLUDE versus writing a custom CF
Converting intensive queries to stored procedures is the single most performance tweak you can do for a CF site. I've seen SP's perform up to 5 times as fast as a regular jon - Original Message - From: "Kevin Gilchrist" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Tuesday, January 30, 2001 4:00 PM Subject: Re: Performance question - CFINCLUDE versus writing a custom CF > Unfortunately stored procedures aren't an option. > What would that gain me anyway? Is it just more efficient for the queries > to be executed by the DB (Oracle in this case)? > > Going by Philip's answer, I guess doing CFINCLUDE'a are the way to go. > > "Steve Bernard" <[EMAIL PROTECTED]> wrote in message > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > Are stored procedures not an option? > > > > Steve > > > > -Original Message- > > From: Kevin Gilchrist [mailto:[EMAIL PROTECTED]] > > Sent: Tuesday, January 30, 2001 12:07 PM > > To: CF-Talk > > Subject: Performance question - CFINCLUDE versus writing a custom CF > > > > > > Hi all, > > > > I'm writing a web app that acts as a dashboard for an MSP, i.e. a GUI that > > shows the status of all sorts of applications, network and host devices. > I > > need to write quite a few select queries for each device/host/app type, so > > will end up with ~150 different queries. > > > > What I'm wondering about is if anyone has any thoughts on whether it's > > better to access these queries from the GUI templates using > > > > - a series of conditional CFINCLUDE's that would include a smallish query > > template. > > > > - or write a custom CF tag with all the queries inside and pass three > > parameters that would give me a unique query name. A three deep CFWITCH > > statement could home in on the relevant query. > > > > Is it correct to assume that it's a bigger hit on CF to call on a > relatively > > large templates even though it's doesn't have to process much of it (cf_ > > tag), as opposed to including whole but small templates? > > I guess it may be better organizationally to implement the queries as a > > bunch of small files to include? > > > > It seems like a case of six in one, half a dozen in the other but I'll > never > > know if I don't ask... > > > > Thanks, > > Kevin > > > > P.S. I intend to convert it all to fusebox for version 2. > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Performance question - CFINCLUDE versus writing a custom CF
Unfortunately stored procedures aren't an option. What would that gain me anyway? Is it just more efficient for the queries to be executed by the DB (Oracle in this case)? Going by Philip's answer, I guess doing CFINCLUDE'a are the way to go. "Steve Bernard" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Are stored procedures not an option? > > Steve > > -Original Message- > From: Kevin Gilchrist [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, January 30, 2001 12:07 PM > To: CF-Talk > Subject: Performance question - CFINCLUDE versus writing a custom CF > > > Hi all, > > I'm writing a web app that acts as a dashboard for an MSP, i.e. a GUI that > shows the status of all sorts of applications, network and host devices. I > need to write quite a few select queries for each device/host/app type, so > will end up with ~150 different queries. > > What I'm wondering about is if anyone has any thoughts on whether it's > better to access these queries from the GUI templates using > > - a series of conditional CFINCLUDE's that would include a smallish query > template. > > - or write a custom CF tag with all the queries inside and pass three > parameters that would give me a unique query name. A three deep CFWITCH > statement could home in on the relevant query. > > Is it correct to assume that it's a bigger hit on CF to call on a relatively > large templates even though it's doesn't have to process much of it (cf_ > tag), as opposed to including whole but small templates? > I guess it may be better organizationally to implement the queries as a > bunch of small files to include? > > It seems like a case of six in one, half a dozen in the other but I'll never > know if I don't ask... > > Thanks, > Kevin > > P.S. I intend to convert it all to fusebox for version 2. > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Performance question - CFINCLUDE versus writing a custom CF
Are stored procedures not an option? Steve -Original Message- From: Kevin Gilchrist [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 30, 2001 12:07 PM To: CF-Talk Subject: Performance question - CFINCLUDE versus writing a custom CF Hi all, I'm writing a web app that acts as a dashboard for an MSP, i.e. a GUI that shows the status of all sorts of applications, network and host devices. I need to write quite a few select queries for each device/host/app type, so will end up with ~150 different queries. What I'm wondering about is if anyone has any thoughts on whether it's better to access these queries from the GUI templates using - a series of conditional CFINCLUDE's that would include a smallish query template. - or write a custom CF tag with all the queries inside and pass three parameters that would give me a unique query name. A three deep CFWITCH statement could home in on the relevant query. Is it correct to assume that it's a bigger hit on CF to call on a relatively large templates even though it's doesn't have to process much of it (cf_ tag), as opposed to including whole but small templates? I guess it may be better organizationally to implement the queries as a bunch of small files to include? It seems like a case of six in one, half a dozen in the other but I'll never know if I don't ask... Thanks, Kevin P.S. I intend to convert it all to fusebox for version 2. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Performance question - CFINCLUDE versus writing a custom CF
> I'm writing a web app that acts as a dashboard for an MSP, i.e. a GUI that > shows the status of all sorts of applications, network and host > devices. I > need to write quite a few select queries for each device/host/app type, so > will end up with ~150 different queries. > > What I'm wondering about is if anyone has any thoughts on whether it's > better to access these queries from the GUI templates using > > - a series of conditional CFINCLUDE's that would include a smallish query > template. > > - or write a custom CF tag with all the queries inside and pass three > parameters that would give me a unique query name. A three deep CFWITCH > statement could home in on the relevant query. > > Is it correct to assume that it's a bigger hit on CF to call on a > relatively > large templates even though it's doesn't have to process much of it (cf_ > tag), as opposed to including whole but small templates? > I guess it may be better organizationally to implement the queries as a > bunch of small files to include? > > It seems like a case of six in one, half a dozen in the other but > I'll never know if I don't ask... OK, here's a few things to remember about how CF works with templates; Once they're converted to PCode, they are stored in memory (cached) until either CF runs out of cache (and the oldest template gets shunted) or a newer version is uploaded If you turn on Trusted Cache, then it'll NEVER check to see if a newer version is there Speed wise, a CFINCLUDE is faster than CF_ or CFMODULE, the reason is that the CFINCLUDE actually (theoretically) puts the code into memory in one file, and then executes the whole thing (it's not actually how it works, but it's close enough) - CF_ and CFMODULE are different instances of CF (which is why the Variables scope doesn't follow through) - I'm not sure if this is still 100% true of 4.5.2, but it definitely was for 4.0.1 and 4.5.1 Now onto queries - Cache them! If they're not being changed on a regular basis (such as State lists), then they won't change often, so why load them all the time? Erm, can't think of much more, but this should help you a bit Philip Arnold Director Certified ColdFusion Developer ASP Multimedia Limited T: +44 (0)20 8680 1133 "Websites for the real world" ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: [Performance question - CFINCLUDE versus writing a custom CF]
sounds like you should look into a stored proc if you can get away with it. Kevin Gilchrist <[EMAIL PROTECTED]> wrote: Hi all, I'm writing a web app that acts as a dashboard for an MSP, i.e. a GUI that shows the status of all sorts of applications, network and host devices. I need to write quite a few select queries for each device/host/app type, so will end up with ~150 different queries. What I'm wondering about is if anyone has any thoughts on whether it's better to access these queries from the GUI templates using - a series of conditional CFINCLUDE's that would include a smallish query template. - or write a custom CF tag with all the queries inside and pass three parameters that would give me a unique query name. A three deep CFWITCH statement could home in on the relevant query. Is it correct to assume that it's a bigger hit on CF to call on a relatively large templates even though it's doesn't have to process much of it (cf_ tag), as opposed to including whole but small templates? I guess it may be better organizationally to implement the queries as a bunch of small files to include? It seems like a case of six in one, half a dozen in the other but I'll never know if I don't ask... Thanks, Kevin P.S. I intend to convert it all to fusebox for version 2. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Performance Question
cfinclude At 10:29 AM 11/04/00 -0700, you wrote: >Hi Folks, > >I was wondering about something when it comes to performance: > >Which is faster, a custom tag (CF_mytag not CFX_mytag) or using a >CFINCLUDE to include the code that is being called? > >Thanks, > >Doug >-- >Archives: http://www.eGroups.com/list/cf-talk >To Unsubscribe visit >http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or >send a message to [EMAIL PROTECTED] with 'unsubscribe' in >the body. -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
