Robert Rawlins - Think Blue wrote: > Ian, > > Just out of interest, how much data are we talking about? Roughly? DB size, > tables, rows etc. Ok, the 10 second description, otherwise we could get into a hours long dissertation. This is a very old and much cobbled together system.
A main table that gets approcamately 2.5 million, and growing, new records a year. We have been collecting this data since 1973 and it currently has 46,594,827 rows. This data provides transaction information on the usage of products stored in a table of tens of thousands of pesticide products. Each of these products reference a table of thousands of chemicals of which each product is composed. When this data is exported from our internal production database to the external report database, these three related tables and some other minor tables are flattened into a singe table containing all the information about each transaction. Other then this main table there are minor related tables that provide looks up for some of the main fields in these tables, the counties in California (58), Range, Township and section numbers, years, sites|commodities (i.e. stuff grown on farms) and such. > > Can you go into a little more depth about you current use case, so, a user > comes to the site, completed an HTML form about the data they want from you, > you then process that data and package it into an email for them, and you're > looking to automate that process a little more? Is that correct? > Well not automate it more, but remove systematic failures and limitations of the current system. Such that it is easy for a user to build too complex or large of a query so that the process ties up the system and causes a log jam until it is cleared. And that users can not currently select data across multiple years, a common request. The main use case is for a user to come to the system and want to know how much and|or what kind of pesticides have been applied to a particular place, food commodity andIor time. > Rob > > -----Original Message----- > From: Ian Skinner [mailto:[email protected]] > Sent: 23 April 2009 16:25 > To: cf-talk > Subject: Re: YIKES! I must let internet users write SQL queries for our > database! > > > Robert Rawlins - Think Blue wrote: > >> Hey Ian, >> >> My first thoughts on this would be to ensure that YOU keep total control >> over the actually SQL that is being run, if you're working with large sets >> of data (more than a few GB) than the performance problems which arise >> > from > >> poorly written SQL could likely cause you all kinds of beef ;-) >> > > For sure, the current HTML form based interface that does this still > allows users to build queries that kill the system, which is one of the > main drivers for todays meeting. To discuss what and how the system > could be improved and how much effort such a project might take. > > The web services idea is a good one, I'll definitely put it on the list > for discussion. > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| 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:321867 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
