SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: Large Access DB

  1. #1
    SitePoint Evangelist Brandon Luhring's Avatar
    Join Date
    Apr 2002
    Location
    IN, USA
    Posts
    455
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Large Access DB

    My client has given me an MS Access database with 3 MILLION rows in it. It is to do point-to-point mileage based on the first three numbers in the ZIP codes (includes U.S. and Canada codes).

    So, each row contains an Origin ZIP, Destination ZIP, and Mileage.

    My question is about speed. A Query look-up takes too long on my laptop... When this gets moved to their web host, I'm sure speed will improve compared to my laptop, but I'm not sure how much...

    Is 3 million rows too much for Access? Do I need to see if the host will let us convert it to SQL Server (of which I'm not familiar with)?

    Thanks for any input!
    Brandon

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    does your laptop query lookup utilize any indexes on the data?

    3 million rows is not too many for access, and using sql server might cost more

    what database options does your host offer?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Brandon Luhring's Avatar
    Join Date
    Apr 2002
    Location
    IN, USA
    Posts
    455
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not at work, so I'll have to check and make sure that the origin and destination ZIPs are indexed...

    Their host can do file-based databases, for sure. I wanted to make sure that 3 million rows was workable with Access before I asked them what it would take to get SQL Server...

    Thanks for the input. I'll post back on Monday if I find anything out.

    Thanks,
    Brandon

  4. #4
    SitePoint Evangelist Brandon Luhring's Avatar
    Join Date
    Apr 2002
    Location
    IN, USA
    Posts
    455
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow. I set up the Indexing for the two ZIP code columns, and it flies now.

    Thanks for the advice!

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    my pleasure
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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