We do use stored procs for all interaction with the database. We are a "no inline queries allowed" shop. :) We also add indexes where possible and beneficial. Some of reports still run for ten minutes though.
~Brad -----Original Message----- From: Teddy Payne [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 2:36 PM To: CF-Talk Subject: Re: reports reports reports Brad, Hundreds of thousands is not that bad if you use stord procedures and a good indexing scheme. Teddy On 10/3/06, Brad Wood <[EMAIL PROTECTED]> wrote: > > Thanks for the links. > > These reports are rendered hundreds of times a day for all our > companies. Even more so at month end. People (executives) here like > their reports! > Mostly customer volumes, turn times, client profitability kind of stuff. > > > As far as the dataset... Anywhere from hundreds of thousands to millions > of records are being looked at for each report. The actual amount of > data returned is usually aggregated in some way, but is usually in > hundreds to thousands of records returned. > > ~Brad > > -----Original Message----- > From: Teddy Payne [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 03, 2006 2:12 PM > To: CF-Talk > Subject: Re: reports reports reports > > Brad, > No worries. > > How often are these reports going to be rendered? What size of a data > set > will they be calculated upon? > > As for Flex reporting, here is an example site: > http://demo.quietlyscheming.com/ChartSampler/app.html > > The code for the report demos are downloadable from: > http://www.quietlyscheming.com/blog/2006/08/30/all-flex-samples-on-quiet > lyscheming-updated-and-now-downloadable/ > > Teddy > > On 10/3/06, Brad Wood <[EMAIL PROTECTED]> wrote: > > > > Teddy, thanks for the insight. I will try to address a couple of the > > questions you posed... > > > > I guess the main reason why I am suggesting CF would be the logical > > solutions for our reporting is simply because all of our other > > interfaces we have right now are CF. All operations functionality > > (including the current reporting module) is part of a "CF front-end / > MS > > SQL back-end" web app. Our current set of programmers are CF > > programmers, our front-end expertise is CF, and our current servers > are > > Linux (I.E. .Net is limited in my mind since it is married to its > > platform). All logins, authentication, tracking, and Contact manager > > data (which the reports link directly to) are all part of the CF web > > app. So in my mind, it just seems logical that to change technologies > > would be a shift in gears for our company. It would also cause a rift > > in our technologies where integrating links directly to order details > > and contact manager would be across systems. Also, we would have to > > re-build our security model and a new authentication system for a > second > > web app written in a different language. It just sounds like so much > > work! :) > > > > You mention that there is network overhead for CF to connect to the > SQL > > servers to run the report. That makes perfect sense, but wouldn't you > > have just as much overhead for a SQL reporting server to also connect > to > > the database? Maybe I don't understand how the SQL server connects to > > the database, but it is still a separate server connecting across the > > network to your database to run a select or a prepared statement, > right? > > > > I am especially interested in hearing from anyone who has used any of > > the Flex charting stuff. I am curious as to how hard it is to create > > them. > > > > Job security is not a concern here. I'm just trying to keep my > company > > from chasing after every pretty "technology" butterfly it sees and > > ending up with 15 different technologies which are all poorly > integrated > > or thought through. (But, yes, I may be a little selfish in wanting > > things to stay in CF) :) > > > > Some of my main concerns with our reporting mechanism are having the > > flexibility we currently have with the HTML/CF interface we are > using-- > > especially if we move to a Flex solution. All of our reports have > > dynamic criteria the user can select as they run the report. The > > interface needs to be able to create form fields of every type, exact > > validation against those fields, and then capture their values and > plug > > them in to the query that is run to populate the report. I don't know > > if that can be does easily with action script even. > > > > Anyway, enough rambling. I KNOW there are some people out there with > > opinions/experience in this area. Nobody would shut up about the cfif > > recordcount, and now I can't get anyone to talk! lol > > > > ~Brad > > > > > > -----Original Message----- > > From: Teddy Payne [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, October 03, 2006 12:47 PM > > To: CF-Talk > > Subject: Re: reports reports reports > > > > Brad, > > The question here is why do you feel that ColdFusion needs to do the > > reporting? Is the data set ideal for CF? > > > > In the server farm that I work with, we have dozens upon dozens of > > database > > and multiple data warehouses. > > > > We use the report builder for SQL Server 2000 and it is quick and gets > > the > > reports to those who need it. The SQL Server reports also have an > > option to > > deploy the calculated report to an HTML page, which is a static result > > of > > the finished compilation. > > > > Now, don't get me wrong here. I have used CF for 8 years and use it > for > > the > > majority of my solutions, but it is not meant to report everything. > > > > You have to think about CF connecting to the servers you want to run > > reports > > on as additional network overhead. > > > > You can create static reports in CF as well and can offer lots of > pretty > > graphing options. You can go as far as building a Flex Charting > > solution as > > well for even better results. I would recommend Flex Charts over CF > > Reports > > for more options and flexibility. > > > > Now, who is going to build these reports? Are they asking you to > create > > the > > reports or the DBAs? Is job security a concern? > > > > Just be objective here and figure out what is the best option instead > of > > asking if CF should get the oppurtunity. From what I have seen on the > > calculations for some of the larger reports, the SQL report builder is > > quicker on the recompilation of a report. CF is quicker when > rendering > > aggregated data sets that do not need to recompile the data sets. > > > > Teddy > > > > On 10/3/06, Brad Wood <[EMAIL PROTECTED]> wrote: > > > > > > I'm looking for opinions advice here. My DBA is going nuts over the > > SQL > > > Server 2005 reporting services. He wants to replace our current > > custom > > > built (CF) reporting mechanism with it. I think ColdFusion report > > > builder would be a better option since we are, after all, a > ColdFusion > > > shop, not .NET. And not to mention all of our production servers > run > > > Linux. > > > > > > > > > > > > Nice features our company seems to want often in reports is the > > ability > > > to sort and filter data, add/remove/re-arrange columns from the > > report. > > > Keep stats on when the report was last run, by whom, and how long it > > > took. Change the server the report runs off of on the fly. > > > > > > > > > > > > The problem I have is that I know nothing about the SQL Server 2005 > > > reporting service, OR the ColdFusion report builder other than they > > are > > > all free. > > > > > > A couple of the guys on the database team are already playing around > > > with the SQL version and making a collection of cool reports to use > in > > > their argument for why we should use it. Before all the > pointy-haired > > > bosses get sold on that I want to make sure ColdFusion report > builder > > > gets its fair say. > > > > > > > > > > > > Can anyone with experience with one or the other comment on the > > > following: > > > > > > > > > > > > Which one is faster? > > > > > > Which one is better? > > > > > > Which one has more features when it comes to manipulating the data > > after > > > running the report? > > > > > > Which one will get my morning coffee and bagel for me? > > > > > > Which one has the easiest learning curve? > > > > > > > > > > > > I anxiously await advice. > > > > > > > > > > > > Thanks! > > > > > > > > > > > > ~Brad > > > > > > > > > > > > > > > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255246 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
