Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Working with large amount of data... (97 SR2)

    Hi Everyone... On a completely different topic than my last post... I'm currently starting work on an application where I'm dealing with a main data table imported from a text file that is 178MB's (a fixed width report) ... I got it down to 90MB's by rewriting the file as delimited and removing headings and summaries... and I finally got it to import into Access... However running queries on the data, trying to join with other tables, etc... just takes FOREVER... or bombs out completely... lol...

    Generally I work with about 200,000 to 400,000 records at a time... This sucker is over 1,200,000 records.... Does anyone have any advice on how to work with this amount of data?... Should I be using SQL Server or a more powerful DBMS for this?... Or are there tricks to dealing with this in Access itself?

    Thanks in advance (again)! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with large amount of data... (97 SR2)

    Without knowing the data structure of the large table or the types of queries you're running against it, it's a bit tough to say, so I'll speak in generalities.

    (1) Judicious use of indexes may speed the queries, though will increase the size of the Access database.
    (2) Splitting the large data table into multiple smaller tables may help out. It may violate some basic relational design theory, but, often performance counts more than theory.
    (3) Examine the way you apply criteria in your queries to squeeze out some performance gains. For example, if you're applying criteria to a numeric field with IN(3, 25, 36) you should put the value that occurs most often in the first position; same principle applies to immediate if (IIF) statements.

    Or after those considerations you may just elect to upsize to SQL Server, Oracle, DB2 or MySQL. Best of luck!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with large amount of data... (97 SR2)

    Thanks Shane!

    I think I'm going to bring my laptop to work tomorrow and try this thing in SQL Server... I don't have a lot of experience with SQL Server but I think it's worth giving it a shot... I learned on DB2 at school but don't have that available here at work...

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Working with large amount of data... (97 SR2)

    Your laptop may not give you much better performance than Jet (Access) if it doesn't have lots of RAM. One of the tricks in working with SQL Server is to have enough memory to get the entire table into memory - queries really run fast then. The basics of all the large database engines are pretty much the same (i.e. SQL of similar varieties), but the admin interfaces vary considerably. I actually like the SQL Server interface the best of the lot, but that's a personal preference. Let us know how you get on.
    Wendell

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Working with large amount of data... (97 SR2)

    That many records is probably straining Access' abilities. However, regardless of whether you use Access, SQL Server, or something else, having proper indexes on the table is of utmost importance. Does the table have a Primary Key? If not, add one, even if it is just an autonumber. Also, make sure you compact the database after initially adding all these new records.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Working with large amount of data... (97 SR2)

    Another option you might try is staging data to temporary tables. I have a few databases that import several million records where the performance is absolutely terrible. To get around this, when the data is loaded, I run a routine that processes and stages the data such that users queries only work on "processed" data. This added time when loading data but the users are very happy as the processes they need are quite fast. Reports that the users needed at first took between one and four minutes to open based on their machines, however, now they open in seconds.
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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