Once you get past the initial step of converting to stored procedures, you should notice a nice increase. By using a stored procedure, the query optimizer will now have a cached execution plan to use upon execution, instead of writing one each time your CF code sends the request; however, I would seriously look into your indexes and find out what column should be used for your clustered index, and what other columns you can create non-clustered indexes on. It may be very possible that SQL server has to do a full table scan for each query, and if you are joining tables, and doing full table scans for each table, it gets ugly fast.
Also, if you do some reading up on the benefits of indexes, do not get too carried away. The more indexes you have, you will loose performance on the other end of the application (inserting / updating). So depending on what type of data entry you are dealing with, this has to be taken into consideration as well.
On 9/7/05, Justin Cook <[EMAIL PROTECTED]> wrote:
Thanks guys.....i'll have to look those up.
James Blaylock <[EMAIL PROTECTED] > wrote:The Sams Teach Yourself SQL in 10 minutes has a very
good section on stored procedures.
James
--- Tom Woestman wrote:
> Justin,
>
> I believe you can do most if not all the math
> operations in SQL that you can
> do in CF. Multiple queries are definately
> supported. Looping over the
> queries is also supported. I have used a book
> called "The Guru's guide to
> SQL Server, Stored Procedures, XML, and HTML" but I
> did not find the book to
> be all that good at helping to develop stored
> procedures. Does anyone have
> any good suggestions for a book that would be
> helpful in learning to code
> stored procedures?
>
> Tom
>
> _____
>
> From: Justin Cook [mailto: [EMAIL PROTECTED]]
> Sent: Wednesday, Sept ember 07, 2005 7:57 AM
> To: [email protected]
> Subject: RE: Using CF and MS-SQL for large number of
> calculations
>
>
> Tom,
>
> I'm not really good with stored procedures, so let
> me ask you this, can you
> do multiple queries, loop through those queries and
> then do somewhat complex
> mathematical equations to calculate various items
> using SPs?
>
> Tom Woestman wrote:
>
> Justin,
>
> I have found that moving complex operations to SQL
> server can up the
> performance by more than 100 times. There is a lot
> of network overhead
> time/processing required when doing thousands of
> queries from CF to perform
> calculations etc when compared to using one or more
> stored procedures to
> perform the same operations.
>
> For the operations you indicated I agree with Justin
> that it would be best
> to move those to the SQL layer if possible.
>
> Tom
>
> _____
>
> From: Justin Cook [mailto: [EMAIL PROTECTED]]
> Sent: Wednesday, September 07, 2005 7:19 AM
> To: [email protected]
> Subject: RE: Using CF and MS-SQL for large number of
> calculations
>
>
> Thanks for the biblically sounding advice (eg "thy")
>
> . So you think
> this is something that has to be done on the SQL
> server itself? Or are you
> saying that perhaps the database isn't put together
> in a way that is
> condusive to this sort of mathematical calculation?
>
> Justin
>
> Daniel Elmore wrote:> within that facility. Each tim e spending is found in
>
> The solution is to know thy database server better.
> Indexes, computed
> columns and denormalization. Study those, no quick
> fix here.
>
>
>
> Daniel
>
>
>
>
>
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]] On Behalf
> Of Justin Cook
> Sent: Wednesday, September 07, 2005 8:51 AM
> To: CFUG List
> Subject: Using CF and MS-SQL for large number of
> calculations
>
>
>
> Ok, I have a problem so hopefully someone out there
> can share wisdom or
> knowledge. The problem is this...
>
>
>
> We have a client who has a series of clients with
> numerous facilities. We
> have to look through each of these clients and look
> for years with a
> non-zero amount in annual totals of spending. Then
> we have to use each of
> those years, and run queries on each facility to see
> if there is spending
> a facility within a
> particular year, we calculate consulting fees,
> contingency fees, and
> inflation in that instance for that facility and
> then sum each instance of
> spending within this facility and year. So for
> example: facility Building 4
> for the year 2006might have spending(including the
> consulting, contingency
> and inflation) in the amounts of 20000 and 1000 for
> a total of 21000.
>
>
>
> It's really quite hard to describe in a brief manner
> and my example above is
> just to give you an idea of what I am doing,
> nonetheless the problem I am
> facing is that to run the query's and the CF code to
> update even a subset of
> these records takes about 45-60 seconds. This is far
> too long for our
> client. When I try to create a script that will run
> as a scheduled task on
> our Cry stal Tech server, I get a "query timed out"
> error. Does anyone have
> any suggestions? I can give more details if you
> think you might be able to
> help.
>
>
>
> Thanks,
>
> Justin
>
>
>
>
______________________________________________________
Click here to donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redcross-donate3/
----------------------------------------------------------
To post, send email to [email protected]
To unsubscribe:
http://www.dfwcfug.org/form_MemberUnsubscribe.cfm
To subscribe:
http://www.dfwcfug.org/form_MemberRegistration.cfm
