2 similar tables, one takes too long to append data to

[Jpapageorgiou]

Hi.

I have being playing with ms access but I really don't know much about it or databases in general.
I have created a very simple database to gather twitter following/followers data for research purposes.

One table (table01) has a field for the "boss" user (=the user who I gather data for), another field for "client" (=bosses followers or friends)
Both fields are numeric and contain the users id's.
In order to distinguish if the link is follower or friend there is a third field, called type which can be either 1 (=follower) or 2 (=friend).
So the data would look like this:

boss - client - type
12345, 67890, 1
12345, 54321, 2
...
If user with user id 12345 had a follower (type 1) with user id 67890 and a friend with user id 54321

In order to avoid getting duplicate rows I also added a unique identifier which is of the form boss_id-user_id-type.

So the above row looks like this:
12345-67890-1, 12345, 67890, 1
12345-54321-2, 12345, 54321, 2
...

That works just fine.
For several reasons I also needed data of the form source - target.
So I also made another table (table02) of this form.
67890, 12345
12345, 54321
...

In table 2 you don't need the "type" field since the position of the user id shows the type of relationship.
Still, you need a unique identifier in order to avoid duplicates, so I added on with the form: source_id-client_id

So table02 lookes like this
67890-12345, 67890, 12345
12345-54321, 12345, 54321
...

Both tables also have a date/time stamp for each line.

As you can see, table01, having also a type field is bigger than table02.

The problem is when I try to append data, exactly the same data in both tables.
Appending data to table01 is ok, while appending data to table02 (which is smaller, having one less field) takes a really long time, maybe 10 times as long as appending data to table01.

To make sure that no query's are causing the problem I have tried first creating temporary tables with the data to be appended, no duplicates, nothing that would cause the database to make extra calculations and used a simple update query with no filters to append data.
Still I get the same result, table02 takes a very long time to
finish while table01 finishes in no time.

Has anyone got any idea why this is happening?


*I am sorry for the long post but I couldn't describe the problem in less words.

 

[MarkK]

I don't know why your data operation is slow. My observation is that you don't need two tables for this problem. Consider a table structured like . . .

tHierarchical
HierarchyID (Primary Key)
ParentID (Foreign Key to HierarchyID in this table--If this row has a parent)
Data

Now you can write two queries, if you want, parents . . .

SELECT *
FROM tHierarchy
WHERE ParentID = 0

. . . and children . . .

SELECT *
FROM tHierarchy
WHERE ParentID <> 0

. . . and everything is way simpler. If you still have your save problem after you implement this, let me know.

 

[Jpapageorgiou]

Thank you for your answer.
I know it is not essential to have botth tables, but there are several reasons why I would like to have both.

Mainly, adding data to this database is a small percentage of the work so it is better to have to wait then than to wait for query calculations every time.

But it is also a matter of strong curiosity.
I can't imagine why a smaller table would take longer to update than a bigger one.

Anyway I will try your solution and see
Thank you again for your answer