Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Unanswered: Running SQL query in HUGE database

    hi All, I am currently dealing with a number of tables each with over 40,000 records. I have these tables in Access and i'm planning to do some SQL query on them.

    The problem is that due to then large table size, the query is running extremely slow and I'm not sure if using Access SQL is the most viable option.

    I have something like:
    there are three tables, T1 , T2 and T3.

    T1: T2 T3

    ID Value ID Value ID Value
    1 100 2 5 3 1
    2 200 3 5 4 1
    3 300 4 5
    4 400

    My job is to add up all the corresponding values in the three tables and come out with something like this :

    Results
    ID Value
    1 100
    2 205
    3 306
    4 406

    So you see the three tables have different number of records and If a record in T1 is not found in T2 or T3, I still want to keep the original value in T1. And for each table there are some 10,000 records!!!

    Any advice on how to go about doing this? Some other alternatives I cuold think of is to copy the three tables to Excel and use formula, but in reality I have large number of such files so doing it manually is very time consuming.

    Thanks!

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Running SQL query in HUGE database

    Your query would be:
    PHP Code:
    select idsum(value)
    from
    select idvalue from t1
      union all
      select id
    value from t2
      union all
      select id
    value from t3
    )
    group by id
    order by id

    Whether this is too much data for Access to handle, I don't know. It is certainly a pretty small amount of data for a DBMS such as SQL Server or Oracle.

  3. #3
    Join Date
    Mar 2004
    Posts
    2
    thanks I just tried it and it works very well.

    however I forgot to add a point that T2 and T3 might contain records that do not exist in T1 (say Id=5)
    but I ONLY want records that exist in T1.

    What should I do?
    Thanks!

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In that case, perhaps an outer join is more appropriate?

    select t1.id, t1.value+coalesce(t2.value,0)+coalesce(t3.value,0)
    from t1
    left outer join t2 on t2.id = t1.id
    left outer join t3 on t3.id = t1.id;

Posting Permissions

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