# Analysing large registry databases and "insufficient memory"

**URL:** <https://discourse.datamethods.org/t/analysing-large-registry-databases-and-insufficient-memory/2502>\
**Category:** systems\
**Created:** [October 7, 2019, 2:03pm UTC](https://discourse.datamethods.org/t/analysing-large-registry-databases-and-insufficient-memory/2502 "2019-10-07T14:03:08Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![pau13rown](https://discourse.datamethods.org/user_avatar/discourse.datamethods.org/pau13rown/32/1000_2.png) [@pau13rown](https://discourse.datamethods.org/u/pau13rown)\
**Post date:** [October 7, 2019, 2:03pm UTC](https://discourse.datamethods.org/t/analysing-large-registry-databases-and-insufficient-memory/2502/1 "2019-10-07T14:03:08Z")

</div>

the task: analysing patient registry data (millions of patients)

the problem: the data are so large that the stats software complains of “insufficient” memory when attempting to fit ‘complex’ models (i’ll leave out the detail)

the solution: buy more RAM, but i’m up to 64gb. Anything else?

some background info:  
-there are plenty of tips online re making code more efficient, limiting CPU time, data storage etc.

-this guy complains of the same problem: [“PROC PHREG : avec fragilité (effet aléatoire) + processus de comptage”](https://www.developpez.net/forums/d1490127/logiciels/solutions-d-entreprise/business-intelligence/sas/sas-stat/proc-phreg-fragilite-effet-aleatoire-p-processus-comptage/) [translation: ultimately they suggest buying more RAM]

-a potential solution?: [Biom J, Analyzing Large Datasets with Bootstrap Penalization](https://www.ncbi.nlm.nih.gov/pmc/articles/PMC5577005/) [they claim to make their code available, but as usual, the link doesnt work: [broken link](https://github.com/ruiqwy/bootpenal.git)]

-maybe the best/simplest option: [Analyzing Big Data in Psychology: A Split/Analyze/Meta-Analyze Approach (SAM)](https://www.frontiersin.org/articles/10.3389/fpsyg.2016.00738/full)

has anyone implemented this before? i guess this problem is something statisticians encounter, unless we limit ourselevs to the most crude model? although it is not raised when discussing issues re registry data: [Analysis, Interpretation, and Reporting of Registry Data To Evaluate Outcomes](https://www.ncbi.nlm.nih.gov/books/NBK208602/). i cannot see any references to the SAM approach in the medical literature, and i would have to let my computer run for weeks

edit: i would have to monitor progress and flag those sub-samples where the model failed to converge, a further complication, especially if it takes weeks to run

cheers

---

<div class="post-metadata">

**Author:** ![Doug\_Dame](https://discourse.datamethods.org/user_avatar/discourse.datamethods.org/doug_dame/32/806_2.png) [@Doug\_Dame](https://discourse.datamethods.org/u/Doug_Dame)\
**Post date:** [October 7, 2019, 2:57pm UTC](https://discourse.datamethods.org/t/analysing-large-registry-databases-and-insufficient-memory/2502/2 "2019-10-07T14:57:18Z")

</div>

You don’t say what software you’re using, but I think there are six general strategies for dealing with Big Data problems, which I define as “more data and/or model than your normal computing platform can handle, without funky/painful workarounds.”

In approximate order of ease:

1. reduce dataset width
2. reduce dataset height
3. chunk in pieces
4. expand hardware capacity
5. simplify computations
6. find more efficient software (absolute last resort, for me)

These are not mutually exclusive, of course.

Some more specific ideas:

1. reduce dataset width

2. reduce dataset height

3. chunk in pieces

4. expand hardware capacity

5. simplify computations

Hope this gives you some more ideas.

---

<div class="post-metadata">

**Author:** ![f2harrell](https://discourse.datamethods.org/user_avatar/discourse.datamethods.org/f2harrell/32/165_2.png) [@f2harrell](https://discourse.datamethods.org/u/f2harrell)\
**Post date:** [October 7, 2019, 4:29pm UTC](https://discourse.datamethods.org/t/analysing-large-registry-databases-and-insufficient-memory/2502/3 "2019-10-07T16:29:00Z")

</div>

This would be better for [http://stackoverflow.com](http://stackoverflow.com) with carefully chosen tags such as r, sas, etc.

---

<div class="post-metadata">

**Author:** ![timdisher](https://discourse.datamethods.org/letter_avatar_proxy/v4/letter/t/3d9bf3/32.png) [@timdisher](https://discourse.datamethods.org/u/timdisher)\
**Post date:** [October 7, 2019, 7:16pm UTC](https://discourse.datamethods.org/t/analysing-large-registry-databases-and-insufficient-memory/2502/4 "2019-10-07T19:16:54Z")

</div>

> [@pau13rown](#):
>
> -a potential solution?: [Biom J, Analyzing Large Datasets with Bootstrap Penalization](https://www.ncbi.nlm.nih.gov/pmc/articles/PMC5577005/) [they claim to make their code available, but as usual, the link doesnt work: [broken link](https://github.com/ruiqwy/bootpenal.git)]

Working link: [GitHub - guhjy/bootpenal: R package: Bootstrap Penalization for variable selection with big data](https://github.com/guhjy/bootpenal)

Although if you are having this issue in SAS you’re probably better off just trying to translate rather than use R which is probably even worse.

---

<div class="post-metadata">

**Author:** ![pau13rown](https://discourse.datamethods.org/user_avatar/discourse.datamethods.org/pau13rown/32/1000_2.png) [@pau13rown](https://discourse.datamethods.org/u/pau13rown)\
**Post date:** [October 8, 2019, 5:12am UTC](https://discourse.datamethods.org/t/analysing-large-registry-databases-and-insufficient-memory/2502/5 "2019-10-08T05:12:20Z")

</div>

thanks for the link! yeah, id definitely translate to sas, would help me to understand the approach at the same time

---

<div class="post-metadata">

**Author:** ![pau13rown](https://discourse.datamethods.org/user_avatar/discourse.datamethods.org/pau13rown/32/1000_2.png) [@pau13rown](https://discourse.datamethods.org/u/pau13rown)\
**Post date:** [October 9, 2019, 5:39am UTC](https://discourse.datamethods.org/t/analysing-large-registry-databases-and-insufficient-memory/2502/6 "2019-10-09T05:39:41Z")

</div>

actually, it’s less a Q about computer power (despite the title of the post), because i’m resigned to that limitation, and more a Q of stats ie the meta-analysis approach, or boostrapping… I’m just surprised others haven’t been confronted with the issue (there is never any comment in the papers i’m reading re how it was handled). The worry then is that the limited computer power is dictating the analysis ie we are favouring crude models, and that seems to be the case in the literature

---

<div class="post-metadata">

**Author:** ![timdisher](https://discourse.datamethods.org/letter_avatar_proxy/v4/letter/t/3d9bf3/32.png) [@timdisher](https://discourse.datamethods.org/u/timdisher)\
**Post date:** [October 9, 2019, 1:31pm UTC](https://discourse.datamethods.org/t/analysing-large-registry-databases-and-insufficient-memory/2502/7 "2019-10-09T13:31:58Z")

</div>

My intuition is that ML folks should have some insight here since they are actually fitting complex models to huge datasets on a semi-regular basis.

---

<div class="post-metadata">

**Author:** ![pau13rown](https://discourse.datamethods.org/user_avatar/discourse.datamethods.org/pau13rown/32/1000_2.png) [@pau13rown](https://discourse.datamethods.org/u/pau13rown)\
**Post date:** [October 9, 2019, 1:53pm UTC](https://discourse.datamethods.org/t/analysing-large-registry-databases-and-insufficient-memory/2502/8 "2019-10-09T13:53:18Z")

</div>

i saw some posts on medium from the ML folks but it’s all technical, incidental detail and no substance, pandas and who-knows-what eg [https://medium.com/analytics-vidhya/split-apply-combine-strategy-for-data-mining-4fd6e2a0cc99](https://medium.com/analytics-vidhya/split-apply-combine-strategy-for-data-mining-4fd6e2a0cc99)

---

<div class="post-metadata">

**Author:** ![mattebarclay](https://discourse.datamethods.org/user_avatar/discourse.datamethods.org/mattebarclay/32/1099_2.png) [@mattebarclay](https://discourse.datamethods.org/u/mattebarclay)\
**Post date:** [October 9, 2019, 5:25pm UTC](https://discourse.datamethods.org/t/analysing-large-registry-databases-and-insufficient-memory/2502/9 "2019-10-09T17:25:57Z")

</div>

Have you seen:

Rhodes, KM, Turner, RM, Payne, RA, White, IR.  
Computationally efficient methods for fitting mixed models to electronic health records data.  
Statistics in Medicine . 2018; 37: 4557– 4570.  
[https://doi.org/10.1002/sim.7944](https://doi.org/10.1002/sim.7944)

?

Might be what you’re looking for.

---

<div class="post-metadata">

**Author:** ![tamas.ferenci](https://discourse.datamethods.org/letter_avatar_proxy/v4/letter/t/e47774/32.png) [@tamas.ferenci](https://discourse.datamethods.org/u/tamas.ferenci)\
**Post date:** [October 9, 2019, 10:12pm UTC](https://discourse.datamethods.org/t/analysing-large-registry-databases-and-insufficient-memory/2502/10 "2019-10-09T22:12:58Z")

</div>

My first idea would be the “expand hardware capacity” solution, but with a cloud approach (so that you don’t have to buy something which would be impossible and/or uneconomical for a few such calculations).

E.g. with Google Compute Engine, [you can get](https://cloud.google.com/compute/all-pricing#larger_ultramem) almost 1 TB of memory for $6.3/hour.

An R package [is available](https://cran.r-project.org/web/packages/googleComputeEngineR/index.html) with which you can set up the whole environment in a minute, and use the familiar RStudio through web to carry out the calculations.

And, for GCE, you can get $300 and 1 year free by registering.

(Why this would be my first attempt? If it succeeds, you have to make no compromise, as with the other solutions.)

---

<div class="post-metadata">

**Author:** ![pau13rown](https://discourse.datamethods.org/user_avatar/discourse.datamethods.org/pau13rown/32/1000_2.png) [@pau13rown](https://discourse.datamethods.org/u/pau13rown)\
**Post date:** [October 10, 2019, 6:30am UTC](https://discourse.datamethods.org/t/analysing-large-registry-databases-and-insufficient-memory/2502/11 "2019-10-10T06:30:38Z")

</div>

looking forward to reading this, thanks!

---

<div class="post-metadata">

**Author:** ![jroon](https://discourse.datamethods.org/letter_avatar_proxy/v4/letter/j/fbc32d/32.png) [@jroon](https://discourse.datamethods.org/u/jroon)\
**Post date:** [October 10, 2019, 9:48am UTC](https://discourse.datamethods.org/t/analysing-large-registry-databases-and-insufficient-memory/2502/12 "2019-10-10T09:48:14Z")

</div>

Re the cloud approach, Google etc.

Is that an option if you are using individual patient data? Thankfully I don’t have this big data problem int he first place but if I did there is no way I could upload our data to a service like that for data protection reasons.

---

<div class="post-metadata">

**Author:** ![tamas.ferenci](https://discourse.datamethods.org/letter_avatar_proxy/v4/letter/t/e47774/32.png) [@tamas.ferenci](https://discourse.datamethods.org/u/tamas.ferenci)\
**Post date:** [October 10, 2019, 10:21am UTC](https://discourse.datamethods.org/t/analysing-large-registry-databases-and-insufficient-memory/2502/13 "2019-10-10T10:21:44Z")

</div>

Good question. I can’t give you a legal answer, but practically, you could create the virtual machine instance, upload the data, carry out the calculations, download the results, and then _delete the whole instance_. (In contrast to having a permanent instance which you use from time to time.) This is the best way to surely get rid of any data in the cloud, as the instance itself is eliminated.

Now, while I think it’ll indeed delete every trace of the data, whether you can have a legal guarantee for this is something beyond my knowledge… (A quick search gives pages [like](https://cloud.google.com/security/overview/) this or [this](https://medium.com/google-cloud/handling-sensitive-data-on-the-google-cloud-platform-12dac6a6b044), you’d perhaps use this a starting point to find this out.)