Data won´t load from Access Database

[josn0w]

When trying to load data (11 Million Lines) from an access database the process "CefSharp.BrowserSubprocess.exe" stays for more than 30 minutes by 25% CPU-Load.

Afterwards the data didn´t even load properly into an diagram.

Any ideas?

[Anonymous]

Hello @josn0w

Can you tell us the hardware configuration for the computer using Power BI desktop because it sound like and memory limits.

And by the way, I quite agree with @Greg_Deckler about the number of record and the database...

Sebastien

[josn0w]

@Anonymous

Hardware:

Processor: i7-26420 @2.80 GHz

RAM: 8 GB

64Bit

@Greg_Deckler

It was some demo data and yes there are 11.000.000 lines...

[Anonymous]

Hello,

I think there's an issue with you database design and optimization.

I've got a different configuration VM (64 bits, 4 Go RAM) and try to use it for direct querying a 6.000.000 rows tables from Sql Server.

I was facing that CefSharp.BrowserSubprocess.exe hang when I tried to access my table.

I optimize it with a ColumnStore Index and everything was quite fine.

I know you cannot use this king of index with Access, but I think you should try to optimize your tables with indexes and aggregate tables.

Let me know if it help you.

Sébastien

[josn0w]

@Anonymous

So just adding indices will solve it?

[josn0w]

In Access adding an auto-increment for 11000000 lines fails, because there is not enough space 😞

[itchyeyeballs]

MS Access is alot better than a lot of people give it credit for but as the other guys have pointed out, its not designed for that many records, are you able to run any queries with Access itself?

If switching to one of the fee alternatives is not an option  (Mysql, SQLserver express etc) I would look at exportng the data to a csv and importing from there.

As an aside, you can usually get around the lack of space issue in Access if you create a new table/column rather than try to update existing. In your case you could try creating a new empty table with auto-increment primary key (and other keys if appropriate) then use an append query to add your data. Using the compact database tool also helps if you have a lot of data.

[Anonymous]

Adding indexes for aggregation should improve performance, but you could have another trouble.

Dou you have field with Money datatype? If yes, I think this could be problem. I was facing the same behavior with a small table (600.000 rows) having fields with Money Datatype.

When I tried to use them in a chart, the cefSharp.... hang !!!

So I change their datatype from Money to Numeric(13,2) or Numeric (13,4), and no more performance issue !

Good Luck !

Sebastien

[Greg_Deckler]

So, does the data load into the model or no?

Sorry, side note, 11 million rows in an Access database...seriously?