extremely slow for exporting data to Excel from Access

[stonexu1984]

my access database contains over 3million rows and the size will be increasing. it's used to store the history price of stocks. The problem,as I mentioned, is the speed for exporting. it takes

1: 30secs for 2300 dates * 1 stock
2: 1 min for 250 dates * 25 stocks
3: 5.5 mins for 7200 dates * 5 stocks
4: 5 mins for 1300 dates * 25 stocks
5: 30 mins for 300 dates * 100 stocks


The vba code runs a dates loop first, so for each date, SQL gets the price for all the stocks for that day, and then the 2nd loop puts the price into the corresponding cell, then go to next day.

I've also tried this way, run stock loop first then date loop, put in 1 cell by 1 cell, but this is much slower.

The reason i insist on doing this day by day is: the date is downloaded form Bloomberg and only contains price in trading days. however, stocks, currencys and futures don't have same trading days.

It seems it takes more time to deal with large scale matrix. and the query gets 1 day * 100 stocks is slower than query gets 100 days * 1 stock.

I am not sure if it's the access problem, since if so i would switch to SQLserver + VB.net, maybe that could be faster?


MY PC: E8400 2.99GHz 1.98G ram

 

[boblarson]

What code are you using to do this? You talk about a couple of "Loops" but are you really looping through and doing this?

 

[stonexu1984]

VBA code, yes. loop days and stocks

What code are you using to do this? You talk about a couple of "Loops" but are you really looping through and doing this?

 

[gemma-the-husky]

i think what bob means is that you probably shouldn't need to use coding loops

you may well be able to achieve what you went directly with a query, which will work far more quickly, and probably more reliably

 

[stonexu1984]

but... yes.. i am using query to extract specific data from Access and then put into excel spreadsheet. 1 day 1 query

i think what bob means is that you probably shouldn't need to use coding loops

you may well be able to achieve what you went directly with a query, which will work far more quickly, and probably more reliably

 

[gemma-the-husky]

but you still shouldnt need code

you just design a query with the fields you want, and in the criteria put the date(s) you want, and the stock(s) you want Set the date explicitly first - just #26/3/09# to get the result you want, but then use a technique to set the date from a form, or from a variable, so its reuseable

 

[rommelgenlight]

what is your real problem, exporting or looping? gemma is right. you may want to use query. study normalization of database for improvement of your tables and query.