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

[Justin Cook]

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 time 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 Crystal 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