Working with large amount of data… (97 SR2)

Topic

New Reply

Hi Everyone… On a completely different topic than my last post… I’m currently starting work on an application where I’m dealing with a main data table imported from a text file that is 178MB’s (a fixed width report) … I got it down to 90MB’s by rewriting the file as delimited and removing headings and summaries… and I finally got it to import into Access… However running queries on the data, trying to join with other tables, etc… just takes FOREVER… or bombs out completely… lol…

Generally I work with about 200,000 to 400,000 records at a time… This sucker is over 1,200,000 records…. Does anyone have any advice on how to work with this amount of data?… Should I be using SQL Server or a more powerful DBMS for this?… Or are there tricks to dealing with this in Access itself?

Thanks in advance (again)!

Reply | Quote

Replies

Without knowing the data structure of the large table or the types of queries you’re running against it, it’s a bit tough to say, so I’ll speak in generalities.

(1) Judicious use of indexes may speed the queries, though will increase the size of the Access database.
(2) Splitting the large data table into multiple smaller tables may help out. It may violate some basic relational design theory, but, often performance counts more than theory.
(3) Examine the way you apply criteria in your queries to squeeze out some performance gains. For example, if you’re applying criteria to a numeric field with IN(3, 25, 36) you should put the value that occurs most often in the first position; same principle applies to immediate if (IIF) statements.

Or after those considerations you may just elect to upsize to SQL Server, Oracle, DB2 or MySQL. Best of luck!

Reply | Quote

Thanks Shane!

I think I’m going to bring my laptop to work tomorrow and try this thing in SQL Server… I don’t have a lot of experience with SQL Server but I think it’s worth giving it a shot… I learned on DB2 at school but don’t have that available here at work…

Reply | Quote

Your laptop may not give you much better performance than Jet (Access) if it doesn’t have lots of RAM. One of the tricks in working with SQL Server is to have enough memory to get the entire table into memory – queries really run fast then. The basics of all the large database engines are pretty much the same (i.e. SQL of similar varieties), but the admin interfaces vary considerably. I actually like the SQL Server interface the best of the lot, but that’s a personal preference. Let us know how you get on.

Reply | Quote

Your laptop may not give you much better performance than Jet (Access) if it doesn’t have lots of RAM. One of the tricks in working with SQL Server is to have enough memory to get the entire table into memory – queries really run fast then. The basics of all the large database engines are pretty much the same (i.e. SQL of similar varieties), but the admin interfaces vary considerably. I actually like the SQL Server interface the best of the lot, but that’s a personal preference. Let us know how you get on.

Reply | Quote

Another option you might try is staging data to temporary tables. I have a few databases that import several million records where the performance is absolutely terrible. To get around this, when the data is loaded, I run a routine that processes and stages the data such that users queries only work on “processed” data. This added time when loading data but the users are very happy as the processes they need are quite fast. Reports that the users needed at first took between one and four minutes to open based on their machines, however, now they open in seconds.

Reply | Quote

Another option you might try is staging data to temporary tables. I have a few databases that import several million records where the performance is absolutely terrible. To get around this, when the data is loaded, I run a routine that processes and stages the data such that users queries only work on “processed” data. This added time when loading data but the users are very happy as the processes they need are quite fast. Reports that the users needed at first took between one and four minutes to open based on their machines, however, now they open in seconds.

Reply | Quote

Thanks Shane!

I think I’m going to bring my laptop to work tomorrow and try this thing in SQL Server… I don’t have a lot of experience with SQL Server but I think it’s worth giving it a shot… I learned on DB2 at school but don’t have that available here at work…

Reply | Quote

Without knowing the data structure of the large table or the types of queries you’re running against it, it’s a bit tough to say, so I’ll speak in generalities.

(1) Judicious use of indexes may speed the queries, though will increase the size of the Access database.
(2) Splitting the large data table into multiple smaller tables may help out. It may violate some basic relational design theory, but, often performance counts more than theory.
(3) Examine the way you apply criteria in your queries to squeeze out some performance gains. For example, if you’re applying criteria to a numeric field with IN(3, 25, 36) you should put the value that occurs most often in the first position; same principle applies to immediate if (IIF) statements.

Or after those considerations you may just elect to upsize to SQL Server, Oracle, DB2 or MySQL. Best of luck!

Reply | Quote

That many records is probably straining Access’ abilities. However, regardless of whether you use Access, SQL Server, or something else, having proper indexes on the table is of utmost importance. Does the table have a Primary Key? If not, add one, even if it is just an autonumber. Also, make sure you compact the database after initially adding all these new records.

Reply | Quote

That many records is probably straining Access’ abilities. However, regardless of whether you use Access, SQL Server, or something else, having proper indexes on the table is of utmost importance. Does the table have a Primary Key? If not, add one, even if it is just an autonumber. Also, make sure you compact the database after initially adding all these new records.

Reply | Quote