RE: SQL advice please
I would always put as much of the more complicated SQL on the server as possible. Its designed to handle it. I very rarely have more than simple SELECTs in my CF. Also if you have a good DBA he'll optimise the queries much better than the application developer would in CF. Just my view of course :) Regards Stew ~~ 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: SQL advice please
Can someone explain, in general if possible when the best time to use SQL Server views/storedProdedures/triggers VS. CF Queries? Use Stored Procedures wherever you can. They're usually quicker than plain CF queries, sometimes enormously so, and help to modularise your code, which is always good. Triggers don't do much for you that SP's can't, but they're simpler. Personally I'm not a big user of views. Nick ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** ~~ 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: SQL advice please
Any time you run the same SQL over and over and over again! Itis much faster than if you have CF retrieve the query everytime. - Original Message - From: "S R" [EMAIL PROTECTED] To: "CF-Talk" [EMAIL PROTECTED] Sent: Friday, November 17, 2000 11:35 AM Subject: SQL advice please Can someone explain, in general if possible when the best time to use SQL Server views/storedProdedures/triggers VS. CF Queries? Thanks Sal _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. Share information about yourself, create your own public profile at http://profiles.msn.com. ~~ 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 ~~ 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: SQL advice please
Yup use SP's wherever poss. I tend not to use views either. I build temp tables in the SP or just incorporate it into the SQL statement using various joins -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: DeVoil, Nick [mailto:[EMAIL PROTECTED]] Sent: 17 November 2000 17:43 To: CF-Talk Subject: RE: SQL advice please Can someone explain, in general if possible when the best time to use SQL Server views/storedProdedures/triggers VS. CF Queries? Use Stored Procedures wherever you can. They're usually quicker than plain CF queries, sometimes enormously so, and help to modularise your code, which is always good. Triggers don't do much for you that SP's can't, but they're simpler. Personally I'm not a big user of views. Nick ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** ~~ 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 ~~ 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: SQL advice please
From the SQL server books online. Stew "The benefits of using stored procedures in SQL Server rather than SQL programs stored locally on client computers are: They allow modular programming. You can create the procedure once, store it in the database, and call it any number of times in your program. Stored procedures can be created by a person who specializes in database programming, and they can be modified independently of the program source code. They allow faster execution. If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are created, and an in-memory version of the procedure can be used after the procedure is executed the first time. Transact-SQL statements repeatedly sent from the client each time they run are compiled and optimized every time they are executed by SQL Server. They can reduce network traffic. An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network. They can be used as a security mechanism. Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly." ~~ 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: SQL advice please
Don't rule out views guys, they provide great management, security and performance. SQL 2000 has added indexed and portioned views. -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Friday, November 17, 2000 12:47 PM To: CF-Talk Subject: RE: SQL advice please Yup use SP's wherever poss. I tend not to use views either. I build temp tables in the SP or just incorporate it into the SQL statement using various joins -- Andrew Ewings Project Manager Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.co.uk/ Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. -Original Message- From: DeVoil, Nick [mailto:[EMAIL PROTECTED]] Sent: 17 November 2000 17:43 To: CF-Talk Subject: RE: SQL advice please Can someone explain, in general if possible when the best time to use SQL Server views/storedProdedures/triggers VS. CF Queries? Use Stored Procedures wherever you can. They're usually quicker than plain CF queries, sometimes enormously so, and help to modularise your code, which is always good. Triggers don't do much for you that SP's can't, but they're simpler. Personally I'm not a big user of views. Nick ** Information in this email is confidential and may be privileged. It is intended for the addressee only. If you have received it in error, please notify the sender immediately and delete it from your system. You should not otherwise copy it, retransmit it or use or disclose its contents to anyone. Thank you for your co-operation. ** ~~ 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 ~~ 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 ~~ 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