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

[Daniel Elmore]

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