Hi Ian, That all makes a great deal of sense. I really feel you're onto the right track with the database tuning method, that'll be a great start, whilst this is a fairly heavy database, in this day and age it also isn't really THAT heavy and these slow running queries, crashes and batch processes really isn't something you should have to worry about, I'm sure they're all quite solvable.
>From a front end perspective I think that defining an API for data retrieval is really a key for you, if you can create a common interface for the data collection which allows people to specify parameters for the data, you can then build a front end app, be it HTML, FLEX, AIR or otherwise which allows users to access the data without you and the other developers having to interpret their bespoke requirements and build queries for them. This is really just building a reporting tool for them. Then, should you wish to open a web service up which would allow more experienced people/corporations to access the data then that becomes very easy because all you're doing is publishing a version of the same API you use for your application. This way you're able to meet everyone's requirements. And with this strictly documented and defined API there is none of the confusion about misinterpreting their requirements, if they get the wrong data it's their fault for specifying the incorrect parameters. Because DB tuning is all quite specific to the data you want to retrieve (and insert) I would first start assessing and designing the API, once you know what and how the data is going to be retrieved (I mean you know what the queries look like) then you can tune the database to meet that requirement. Furthermore once you're in that position you can also start thinking about query caching, views and things like that to further extend the performance of the system. Rob -----Original Message----- From: Ian Skinner [mailto:h...@ilsweb.com] Sent: 23 April 2009 17:35 To: cf-talk Subject: Re: YIKES! I must let internet users write SQL queries for our database! Database tuning is an important line item on my discussion list for this meeting. To expand on some of your points. First the system currently limits users to only selecting data from on year at a time as a stop-gap measure to try and limit the operating cost of the query. The trouble is that this is both too limiting and not limiting enough. First it is still quite possible for a user to build a query that will jam up the system while at the same time it limits users who want specific enough information to be reasonable, but want it for multiple years. It forces them to submit the same request over and over only changing the year. Speaking of these users, they are truly a mixed bunch. They range from the professional who supposedly knows what they want to the soccer parent who wants to know *everything* that has been used withing a 100 mile radius of their home/work/school. Unfortunately we have to cater to both as best as we can. A big driver for making this as self-help as possible was the realization that we where commonly getting these requests from both sides of a litigation case in civil court. But because of how each side might have asked for the information combined with how different developers might have understood the request and the data we could be providing radically different results to each side that is effect provided some sort of de-facto 'interpretation' of the data. A position we really do not want to be in. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:321882 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
