# How to fetch millions of data faster? **URL:** \ **Category:** Cypher\ **Created:** [September 5, 2018, 9:16am UTC](https://community.neo4j.com/t/how-to-fetch-millions-of-data-faster/1064 "2018-09-05T09:16:05Z")\ **Posts on this page:** 11\ **Page:** 2
**Author:** ![erikazosi](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/erikazosi/32/714_2.png) [@erikazosi](https://community.neo4j.com/u/erikazosi)\ **Post date:** [September 7, 2018, 4:47am UTC](https://community.neo4j.com/t/how-to-fetch-millions-of-data-faster/1064/21 "2018-09-07T04:47:43Z")
Yes, it's on my personal computer which is running on 16G heap size and 1G is the page cache size. ---
**Author:** ![erikazosi](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/erikazosi/32/714_2.png) [@erikazosi](https://community.neo4j.com/u/erikazosi)\ **Post date:** [September 7, 2018, 4:53am UTC](https://community.neo4j.com/t/how-to-fetch-millions-of-data-faster/1064/22 "2018-09-07T04:53:30Z")
Thank you for your time. I am planning to use python as the client program. Even though I increased the heap it didn't help to process this query. ---
**Author:** ![michael.hunger](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/michael.hunger/32/27377_2.png) [@michael.hunger](https://community.neo4j.com/u/michael.hunger)\ **Post date:** [September 7, 2018, 8:13pm UTC](https://community.neo4j.com/t/how-to-fetch-millions-of-data-faster/1064/23 "2018-09-07T20:13:35Z")
So I spend two hours working through your query, after all it is a gigantic full graph query that basically touches almost all entities in the graph. I did a bunch of optimizations: - 9G page-cache, 8G heap - store the delta directly in the node (as numeric value) - use a label for tagging active bureaus - load the installment with their delta before expanding - turn the optional match into a pattern comprehension - store the status in the list so you save the 2nd expand to bureaus - and then just filter the list for the status for doing the initial updates: ``` call apoc.periodic.iterate("MATCH (i:Installments_Payment) RETURN i", "SET i.DaysInstallMinusEntry = toInteger(i.DAYS_INSTALMENT)-toInteger(i.DAYS_ENTRY_PAYMENT)" ,{batchSize:10000,iterateList:true,parallel:true,concurrency:12}); // takes 88s call apoc.periodic.iterate("MATCH (bureau:Bureau) WHERE bureau.CREDIT_ACTIVE = 'Active' RETURN bureau", "SET bureau:Active" ,{batchSize:10000,iterateList:true,parallel:true,concurrency:12}); // takes 12s ``` The runtime on my computer is between 5 and 7 minutes, here is the final result: ``` from neo4j.v1 import GraphDatabase import time driver = GraphDatabase.driver("bolt://localhost:7687", auth=("neo4j", "test")) statement = """ MATCH (i:Installments_Payment) WITH i, toInteger(i.DAYS_INSTALMENT)-toInteger(i.DAYS_ENTRY_PAYMENT) AS DaysInstallMinusEntry MATCH (a:Application)-[:HAS_PREVIOUS]->(p:Previous_Application)-[:HAS_PREV_INSTALLMENTS]->(i) WITH a, DaysInstallMinusEntry, count(i) AS TotalInstallments WITH a,DaysInstallMinusEntry,TotalInstallments, [(a)-[:HAS_BUREAU]->(bureau) | bureau:Active] as bureauStatus WITH DaysInstallMinusEntry, TotalInstallments, size([status in bureauStatus WHERE status]) AS TotalActiveBureau, size(bureauStatus) as TotalBureau, a RETURN a.SK_ID_CURR AS ApplicationID, TotalInstallments,DaysInstallMinusEntry,TotalBureau, toFloat(TotalActiveBureau)/toFloat(TotalBureau) AS ActiveBureauByTotalBureauRatio ORDER BY ActiveBureauByTotalBureauRatio DESC """ start = time.time() rows = 0 value = 0 for record in driver.session().run(statement): rows = rows + 1 value = value + record["ActiveBureauByTotalBureauRatio"] print("rows {rows}".format(rows=rows)) print("took {time}".format(time=time.time() - start)) rows 5339018 took 450.4561972618103 -> 7 minutes ``` here are all the details > **[erikazosi-queries.cypher](https://www.dropbox.com/s/dpyk60ziitmy9fn/erikazosi-queries.cypher?dl=0)** > > Shared with Dropbox ---
**Author:** ![gabriel\_toma](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/gabriel_toma/32/722_2.png) [@gabriel\_toma](https://community.neo4j.com/u/gabriel_toma)\ **Post date:** [September 8, 2018, 12:17pm UTC](https://community.neo4j.com/t/how-to-fetch-millions-of-data-faster/1064/24 "2018-09-08T12:17:26Z")
So, the black screen is because the query has actually finished and Browser is trying to retrieve back the resulted stream...which, being too big, crashes the Browser. Hence i've added all results in grouping expression (count, sum, avg, etc) so that the result stream is 1 row. Managed to run the query in 8+ minutes (AMd Threadripper 1900x, 8 cores X 3.9 (i've seen that it's actually running on a single processor which is pumped to 4Ghz ![2018-09-08%2014_26_37-neo4j%40bolt___localhost_7687%20-%20Neo4j%20Browser](https://us1.discourse-cdn.com/flex021/uploads/neo4jcommunity/original/2X/2/2bc712a376884a3faa254c3e58312a52c1172306.png) ) / 32gb ram 3200 CL14, m.2 NVME) ![2018-09-08%2015_31_54-neo4j%40bolt___localhost_7687%20-%20Neo4j%20Browser](https://us1.discourse-cdn.com/flex021/uploads/neo4jcommunity/original/2X/8/86a3629023bf1fe51daa115a6d0120d67f8043d1.png) Trick is to run outside of index scans, meaning: **match(a:Application)** **with collect(a) as aa** **unwind aa as a** _MATCH (a:Application)-[:HAS\_PREVIOUS]...._ I actually wonder how much time does it take to output the entire results. ---
**Author:** ![michael.hunger](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/michael.hunger/32/27377_2.png) [@michael.hunger](https://community.neo4j.com/u/michael.hunger)\ **Post date:** [September 8, 2018, 1:43pm UTC](https://community.neo4j.com/t/how-to-fetch-millions-of-data-faster/1064/25 "2018-09-08T13:43:09Z")
Neo4j browser is not meant to retrieve millions of rows. See my code, it takes 7-8 minutes from python. You just need to stream and write the data somewhere or analyze it further e.g. with pandas. Yes, it is currently a single processor query, this might change in the future with a newer runtime. ---
**Author:** ![michael.hunger](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/michael.hunger/32/27377_2.png) [@michael.hunger](https://community.neo4j.com/u/michael.hunger)\ **Post date:** [September 8, 2018, 1:43pm UTC](https://community.neo4j.com/t/how-to-fetch-millions-of-data-faster/1064/26 "2018-09-08T13:43:50Z")
Your "trick" has no effect here. If you look at the profile you don't save any db-hits. ---
**Author:** ![gabriel\_toma](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/gabriel_toma/32/722_2.png) [@gabriel\_toma](https://community.neo4j.com/u/gabriel_toma)\ **Post date:** [September 8, 2018, 2:09pm UTC](https://community.neo4j.com/t/how-to-fetch-millions-of-data-faster/1064/27 "2018-09-08T14:09:16Z")
Michael, Erika, what kind of operation is this: "with ... [(a)-[:HAS\_BUREAU]-\>(bureau:Bureau) | bureau.CREDIT\_ACTIVE = "Active"] as bureauStatus" ? Haven't seen it before Different approach, worse timings - 11 min MATCH (a:Application) with a CALL apoc.path.subgraphAll(a,{relationshipFilter:'HAS\_PREVIOUS|HAS\_PREV\_INSTALLMENTS|HAS\_BUREAU'}) YIELD nodes, relationships with a,[(a)-[:HAS\_BUREAU]-\>(bureau:Bureau) | bureau.CREDIT\_ACTIVE = "Active" and bureau in filter(n in nodes where n:Bureau)] as bureauStatus, filter(n in nodes where n:Installments\_Payment) as ii WITH size([status in bureauStatus WHERE status]) as TotalActiveBureau,ii,bureauStatus,a unwind ii as i with size(bureauStatus) as TotalBureau, a, toInteger(i.DAYS\_INSTALMENT)-toInteger(i.DAYS\_ENTRY\_PAYMENT) AS DaysInstallMinusEntry,count(i) AS TotalInstallments,TotalActiveBureau RETURN count(a.SK\_ID\_CURR) AS ApplicationID, avg(TotalInstallments), avg(DaysInstallMinusEntry), avg(TotalBureau), avg(toFloat(TotalActiveBureau)/toFloat(TotalBureau)) AS ActiveBureauByTotalBureauRatio ORDER BY ActiveBureauByTotalBureauRatio DESC ---
**Author:** ![michael.hunger](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/michael.hunger/32/27377_2.png) [@michael.hunger](https://community.neo4j.com/u/michael.hunger)\ **Post date:** [September 9, 2018, 12:08am UTC](https://community.neo4j.com/t/how-to-fetch-millions-of-data-faster/1064/28 "2018-09-09T00:08:01Z")
it is a [pattern comprehension](https://neo4j.com/blog/cypher-graphql-neo4j-3-1-preview/), almost like a subquery, can contain patterns with new variables, where clauses and result expressions, results in a list of values ---
**Author:** ![yeshveer.yadav](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/yeshveer.yadav/32/4634_2.png) [@yeshveer.yadav](https://community.neo4j.com/u/yeshveer.yadav)\ **Post date:** [October 10, 2019, 12:59pm UTC](https://community.neo4j.com/t/how-to-fetch-millions-of-data-faster/1064/29 "2019-10-10T12:59:44Z")
HI i need to run simple match query match(n:label)-[:has\_example]-\>(m:label2) with n count(n.property) as c, collect(n.property) as c2 where c\>1000 return m.property, c,c2 order by c desc on 45 million nodes ... and my server is not able to run ,.. how can i make it run via parallel or any other way..?? ---
**Author:** ![benjamin.squire](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/benjamin.squire/32/1496_2.png) [@benjamin.squire](https://community.neo4j.com/u/benjamin.squire)\ **Post date:** [October 17, 2019, 6:01pm UTC](https://community.neo4j.com/t/how-to-fetch-millions-of-data-faster/1064/30 "2019-10-17T18:01:42Z")
Please post a separate Question and then you can link it here. ---
**Author:** ![yeshveer.yadav](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/yeshveer.yadav/32/4634_2.png) [@yeshveer.yadav](https://community.neo4j.com/u/yeshveer.yadav)\ **Post date:** [October 18, 2019, 4:59am UTC](https://community.neo4j.com/t/how-to-fetch-millions-of-data-faster/1064/31 "2019-10-18T04:59:03Z")
> [@yeshveer.yadav](#): > > HI > i need to run simple match query > match(n:label)-[:has\_example]-\>(m:label2) with n count(n.property) as c, collect(n.property) as c2 where c\>1000 > return m.property, c,c2 order by c desc > on 45 million nodes ... > > and my server is not able to run ,.. how can i make it run via parallel or any other way..?? > [@How to fetch data on from data base having around 60 million node of one type?](https://community.neo4j.com/t/how-to-fetch-data-on-from-data-base-having-around-60-million-node-of-one-type/11122): > > HI i need to run simple match query match(n:label)-[:has\_example]-\>(m:label2) with m, count(n.property) as c, collect(n.property) as c2 where c\>100 return m.property, c,c2 order by c desc limit 10 on 60+ million nodes ... and my server is not able to run ,.. how can i make it run via parallel or any other way..?? [Previous page](https://community.neo4j.com/t/how-to-fetch-millions-of-data-faster/1064.md?page=1)