Best way to load Access table with 10 million records ?

[edmscan]

I want to load some test data into a table to test .. the performance of an Access database and run a query against it.

So .. yes, 10 million records. The records will be quite small .. but lots of them.



Well .. after some testing. I decided that it is insane to run a SQL statement .. to load the data. I did it the old fashioned way. I made a huge txt file with 11 million+ lines in it. That look a few minutes.
Then .. imported the file in Access into my table.

One hell of a lot faster ... but the jury is still out to see how Access will perform in querying the data. That is for another thread maybe.

But looking for normalization / optimization suggestions. My relationships are below .. mainly cause I don't know how to delete it.

[ItsMe]

What does it mean to "load data"?

It seams as though you are duplicating data. Did not look too close but it is hard to take your questions seriously.

Access is a tool to manage data. Perhaps you need to import data from an outside source. Correct me if I am wrong.

[edmscan]

Maybe my terminology is bad .. I just want to end up with a table with the required number of records .. with "Some sort of Test Data". The number of records is important .. for performance testing and the data has to be valid due to the foreign keys.

[edmscan]

I have 11,204,496 rows .. and now just time to optimize things. Access is pretty efficient .. could be a lot worse.

Here is a sample of what the records look like .. along with my relationships above.

If anyone has any thoughts about query / normalization improvements .. please let me know.

The Transmissions table has 11+ million rows, the KevinGroup (aka Group above) about 450 and Kevin (aka Radio above) about 6400. This is data from a communications system.

I really only run one query on the data .. below.

SELECT TOP 45 Kevin.User, Transmissions.Type, Transmissions.Group, KevinGroup.Name, Transmissions.Radio, Count(Transmissions.Group) AS CountOfGroup1
FROM Kevin RIGHT JOIN (Transmissions LEFT JOIN KevinGroup ON Transmissions.Group = KevinGroup.Group) ON Kevin.Radio = Transmissions.Radio
GROUP BY Kevin.User, Transmissions.Type, Transmissions.Group, KevinGroup.Name, Transmissions.Radio
ORDER BY Count(Transmissions.Group) DESC;

[hapm]

Perhaps it will get a little quicker when you set up a combined index over Transmissions.Group and Transmissions.Radio. Let me know if it helps.

[edmscan]

Thanks .. I will try it. Always looking for ways to make things faster.