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, September 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 <[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: [email protected] > Subject: RE: Using CF and MS-SQL for large number of > calculations > > > Thanks for the biblically sounding advice (eg "thy") > <http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/09.gif> > . 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 > > > > ______________________________________________________ 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
