Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Dec 2010
    Posts
    4

    Design for Holding 1000 Point FFT Data

    This is most likely a simple question. I'm just having a heck of a time searching on google for the right answer for this.

    I created a simple database in Access a few years ago to hold test data for 1000+ point FFTs. The way that I did it is there a a RESPONSE table that has 3 columns: ID, Frequency, Value. This means that for each response ran, there will be 1000+ rows added to this table. After over a thousand responses have been run, there are now over 1 million rows. I'm a little nervous of the stability of this structure. I would have preferred to make 1000+ columns in the response table for each point to avoid so much redundancy, but access limits the number of columns to 256.

    So here are my questions:
    Is there a more efficient way of holding this data?
    Can an Access database handle this strain? (If not, what else should I use?)

    I appreciate any help anyone can offer and I apologize if this has been answered before.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Nearly all of the "major players" in the database field can handle a database this size. Unfortunately the JET database engine (MDB files) used by MS-Access doesn't qualifty as a "major player", but the Access GUI can be used against any ODBC or OLE-DB compliant database engine.

    My first suggestion would be Microsoft SQL Server. It is a natural fit from the same vendor. It is NOT what you're used to, but it is probably the closest contender of the bunch.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Pat, I gotta tell you, Access (and the Jet engine) can easily handle millions of records.

    15 years ago I ran an Access-based data warehouse that approached 2GB in size, and that was on Jet 2.0
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by Tails86 View Post
    I would have preferred to make 1000+ columns in the response table for each point to avoid so much redundancy, but access limits the number of columns to 256.
    You are better off with the multi-record approach. Denormalizing as you suggest above almost always leads to disaster.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    it all depends on waht you want to achieve, how you need to analyse the data (theres stuff all point storing it if you cannot manipulate it to provide the results you need).

    FWIW Im guessing a single datapoint in a set of 1000 is of little or no relevance, which would suggest to me that stroing them as individual rows isn't neccesary required.

    Jet will happily store upto 2gb of data, heck more recent versions of JET may even be able to store more. however you will hit others intrinsic limits within Access.. the 255 column limit is one, but that limit also affects queries as well.
    Microsoft Access Specifications and Limits | Database Solutions for Microsoft Access | databasedev.co.uk

    so I don't think you are going to be able to do FFT using SQL, its going to have to be a VBA process.

    there is nothing stopping you having the data spread out in either chained records in the same table, or spread across 4 (or more if required) tables using a one to one relationship

    Im guessing budget may be an issue.. you can use SQL server which is available as a limited version of the full product but I doubt the limits will affect you. you cna easily enough upgrade an Access application to SQL server.. however like all machine implemented changes there is a signficant risk that you get all the problems of SQL Server and JET, and don't truly garner much benefit from the process, unless you do a through evaluation of the existing design and get rid of the bottle necks

    if budget is an issue then there are competing products from other server vendors (IBM, Oracle and so on), IIRC virtually all server based products offer a cut down limited version of their retail product, there are a couple of full fledged versions such as Mysql which are avaialble free.

    As to stability it all depends. JET, the underlying storage mechanism in plain vanilla Access gets flaky somewhere between 15..30 concurrent users. if you are below that threshold then I doubt it will have serious problems. however its a big file, and it may well be unwieldy to retrieve that information, there is always a risk trucking that much data up and down the network that gremlins can intervene. because it uses the fileserver approach, rather than client server approach there is more exposure to such corruption.

    depending on your implementation, ie whether you access the data from an Access application or another means. if its an Access application then I think you perhaps should consider splitting the db into a front end (containing the interface.. forms, reports and so on) and the back end contianing the data. if you are accessing the data using a program say VB, delphi, or whatever then there is less need to worry, effectively you have already split your DB

    ultimately it comes down to you
    have you seen any symptoms that suggest to you the db is getting flaky?
    is it you or someone you know who is suggesting that JET isn't up to the job?

    if you are seing probelms then you need to redesign soon, if you aren't, and the db is healthily below the 2gb limit I'd leave it as be.
    there is an adage that if it ain't broke don't fix it. thats not suggesting you shouldn't investigate your options, but unless you have good reasons to make changes I'd leave it well alone.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Jet also has a database bloat issue that can crop up, where you have to periodically run the compact/repair utility. (It has no automatic purging of deleted records, temporary tables/record, intermediate query records and the like) that can, depending upon the situation, lead to issues even though the database size is relatively small in proportion to the 2GB limit.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Dec 2010
    Posts
    4
    Thank you all for the advice.

    I'm using this database as a back end to a VB application. I have been asked to do some updates which will force me to update the database structure and I thought that this would be the best time to make other changes.

    I hear a lot of mixed feelings about Access all over the net (and apparently this message board as well). I got really nervous when I crashed the database during testing the first time around. The database became corrupt. This was due to an error in the coding which I fixed, but I think a database should be able to handle invalid SQL commands without crashing like that. Other than that though, there has been no issues with database access within the last two years besides the retrieval speed slowing down slightly. I have been messing around with Microsoft SQL Server for another project. I could change to that fairly easily, or I could adopt the "if it aint broke, don't fix it" attitude as healdem suggests. I don't have much hard evidence that Access is bad for this setup. Many people just seem to feel that Access is a weak and unstable database. I have noticed the bloat issue as loquin said. After records have been deleted, the file size of the database remains unchanged. I should probably learn how to compact the database on termination of the program.

    As for structure, I need to be able to do things like retrieve the average response then grabbing the 'golden unit' from the ID bounds given. - The golden unit is the response that resembles the average response the closest based on octave weighted frequency values. I guess that the structure that I have will work best for making these calculations. I will probably add a separate FREQUENCIES table and reference these values from the RESPONSE table. Database structures bug me with the amount of redundancy that is required at times :P

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    If you can port it over to SQL Server now, relatively painlessly, then by all means do so.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    At the risk of looking dumb - what's an FFT database and what is it you're storing?

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Its a database that stores "Ffffttts", obviously.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    FFT.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Ah. I thought it stood for "Fuper Fonic Transport".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by blindman View Post
    Ah. I thought it stood for "Fuper Fonic Transport".
    Everybody points at those. You'd have a lot more "points" if there were any people around.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Well - after trying to read that link I realised I'm out of my depth on this one! When I googled for "FFT database" it came back with all sorts of cr@p but none on this. My guess is that, if nobody else stores this stuff in a database, then there might be a reason for not storing it in a database.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Fast Fourier Transform is what your brain does to decompose the continuous contiguous sound signal received through your ears into separate distinct waves. It enables us to hear individual conversations while the TV is on, so it is very important.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •