250 million records in a union query

[Access9001]

Is this possible?

For a personal project I have about 250 million records worth of data, and I am inserting them into separate backends (about 10 of them) because each Access backend has a 2 GB limit.

But in my frontend, if I were to do operations on the aggregate union of all these backend tables, would it allow me to process it even though it's got as many as 250 million records in total?

 

[RuralGuy]

It is not the number of records that is the limit but the size of the FE with the internal tables needed to process your request. You may have outgrown Access as a BackEnd but the Free mini SQL from MS could handle it.

 

[Access9001]

What I mean is that I have be1.mdb, be2, be3, etc, with one table each.

I have a frontend, fe.db, where I plan to have a massive union query that pulls from all the backend tables, but the ultimate result should be like 250 million records and somewhere around 17 gb in size. My question is if I need something else for this.

 

[RuralGuy]

As I said, Microsoft SQL Server does not have the limitations and the Express version is free: http://www.microsoft.com/sqlserver/en/us/editions/express.aspx

 

[Access9001]

It only seems to support 10 GB -- my tables add up to like 15 GB, and the SQL Server software isn't installing properly

 

[RuralGuy]

This link seems to imply 524,272 terabytes. http://msdn.microsoft.com/en-us/library/ms143432.aspx

 

[RuralGuy]

Okay, it seems your 10GB limit is correct: http://beyondrelational.com/blogs/sqlnews/archive/2010/04/27/sql-server-2008-r2-express-database-size-limit-increased-to-10gb-download-now.aspx

 

[Galaxiom]

Really the original question was about the limitations of an Access Front End rather than how to store a large back end.

Since the table is not actually in the FE the 2GB file size limit does't really apply. The real issue is the limitation of Access's ability to handle a large recordset.

I would suggest the OP is in the best position to try it and let us know.

 

[Access9001]

I managed to install SQL server.

I basically have six tables here, now properly under 10 GB in size.

How can I combine these into one huge table with proper indexes?

 

[Galaxiom]

What kind of data are you storing? Is there there much repetition in it?

Indexes can also take up a lot of space on a big table especially if the text data is not repetitive.

Is there any way to reduce the size of the content of records by representing repetitious string values with Integers?

 

[Access9001]

They are all unique records, but there is lots of repetition across all three columns

I've got User ID, Badge ID, and BadgeDate
(Number, Number, and Datetime, respectively)

Would I do this:

SELECT * INTO AllBadges
FROM
(
SELECT UserID, BadgeID, WinDate FROM UserBadges1
UNION ALL
SELECT UserID, BadgeID, WinDate FROM UserBadges2
UNION ALL
SELECT UserID, BadgeID, WinDate FROM UserBadges3
UNION ALL
SELECT UserID, BadgeID, WinDate FROM UserBadges4
UNION ALL
SELECT UserID, BadgeID, WinDate FROM UserBadges5
UNION ALL
SELECT UserID, BadgeID, WinDate FROM UserBadges6) as T0

 

[Galaxiom]

No room there for reducing the data then.

Do the tables have to be separated even in SQL Server Express? Or is that just so Access can handle the linked table? I have never worked with such a huge database so i really don't know what are the limits. (Hoping to find out from you.)

However I can say you really should be trying to use techniques that let SQL Server do the work by using passthrough queries and/or queries stored on the server rather than linked tables and Jet/ACE doing the hard yards.

 

[Access9001]

Oh no I imported the tables from Access into SQL Server so they aren't linked (as far as I can tell, anyway) -- now I'm just trying to fuse these 6 tables into 1 now that i don't need to use a bunch of Access backends

 

[Access9001]

What kind of Indexes should I make and how?

(clustered, nonclustered, xml, spatial etc)

 

[Galaxiom]

Run a series of append queries to add the records to one table.

 

[Galaxiom]

The type of index depends on how you are using the data.
http://msdn.microsoft.com/en-us/library/aa933131(v=sql.80).aspx

 

[Access9001]

Yeah I have the one table, but now I am concerned about how to properly index it to optimize speed