RE: Using CF and MS-SQL for large number of calculations

[Justin Cook]

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 <[EMAIL PROTECTED]>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: list@dfwcfug.org
> 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 <[EMAIL PROTECTED]>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: list@dfwcfug.org
> 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 <[EMAIL PROTECTED]>wrote:
>
> 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
> within that facility. Each tim e spending is found in
> 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 list@dfwcfug.org
To unsubscribe:
http://www.dfwcfug.org/form_MemberUnsubscribe.cfm
To subscribe:
http://www.dfwcfug.org/form_MemberRegistration.cfm