> In <s4c761f5.005@WPO.CSO.NIU.EDU>, on 01/22/98
> at 03:13 PM, Angie Byrd <J50AKB1@WPO.CSO.NIU.EDU> said:
>
> >Administrative Information Services puts the data from the
> >main database into a Microsoft SQL server database. We then
> >connect via ODBC through Access to the SQL server. We
> >maintain the Access database locally to increase response
> >time.
>
> >Dave Benham wrote the Cold Fusion program. I believe the cfm to database
> >communication was not that difficult, but Dave
> >will have to respond specifically to that one. The larger
> >issue was manipulating the flat file information from the
> >main database into more of a relational database.
>
> Do you use Access to do the above manipulation or is this handled by
> SQL server (or Cold Fusion)? This seems to be a common hurdle.
> At what point and with which tools should one manipulate the data.
> Rarely is administrative data stored the way you need it.
>
> Sean
>
We use all three tools actually. The data on the SQL server is not
organized the way it would need to be to allow some detailed and complex
queries. So we built a fairly customized database in Access from the SQL
server. But we couldn't do large searches on the Access DB because Access
DBs are VERY SLOW, so we still needed to use SQL server to help speed
things up. The average query time to the SQL server(over the network) is
less than 5 ms. The average search on a local Access DB took well over 100
ms. Big difference, especially when you are doing hundreds if not
thousands of queries for every search.
So its a trade off. The slower MS Access DB we use could do more detailed
searches because we could tailor the structure of the DB to our needs. At
the same time, the Access DB was slow. The SQL server is much faster, but
the data wasn't formatted the way we needed it. So we needed to combine
both. When we needed complex searches done, we used information from an
Access DB. When speed was needed we went to the SQL server.
Cold Fusion, besides from allowing us to connect to the datasources via
ODBC, provided some control logic that allowed us to break the search up
across two different databases and CF also helped in providing the
formatted output.
Giving Cold Fusion access to the two different databases through ODBC
wasn't too difficult. There were several problems getting everything to
work on NT after it was developed and tested on a win95 machine, but they
were dealt with.
Like you said, the data wasn't stored the way we needed it, but that is
due mostly to the fact the data wasn't organized for us, it was organized
to be used in a different manner. But with some clever logic(?) :) and the
Cold Fusion program, we were able to get it to work.
-dave
**************************************************************************
* David Benham *
* dbenham@niu.edu *
* http://mp.cs.niu.edu/~z935418 *
**************************************************************************