Статьи

neo4j / cypher: получение информации о параметрах запроса

Пока я использовал язык запросов neo4j, Майкл говорил мне использовать параметры в моих запросах, но производительность запросов всегда была приемлемой, поэтому я не чувствовал в этом необходимости. Однако недавно я поиграл с набором данных и создал ~ 500 узлов, используя код, подобный следующему:

1
2
3
4
5
6
7
require 'open-uri'
 
open("data/people.cyp", 'w') { |f|
  (1..500).each do |value|
    f.puts("CREATE (p:Person{name: \"#{value}\"})")
  end
}

Это создает файл операторов шифрования, которые выглядят так:

1
2
3
4
5
6
CREATE (:Person{name: "person1"})
CREATE (:Person{name: "person2"})
CREATE (:Person{name: "person3"})
CREATE (:Person{name: "person4"})
CREATE (:Person{name: "person5"})
...

Если мы выполним эти операторы в neo4j-shell или веб-администраторе, мы получим следующий вывод:

1
2
3
4
5
6
7
==> +-------------------+
==> | No data returned. |
==> +-------------------+
==> Nodes created: 500
==> Properties set: 500
==> Labels added: 500
==> 27706 ms

Насколько я понимаю, причина, по которой это занимает много времени, заключается в том, что cypher создает, а затем кэширует план запроса для каждого оператора create, потому что он не может знать, что они фактически совпадают. Так как я довольно часто удалял / перезагружал данные, я хотел немного ускорить цикл обратной связи, так что пришло время применить совет Майкла. Способ работы параметров заключается в том, что вы добавляете заполнители в свои запросы шифров, а затем предоставляете значения, которые должны соответствовать этим заполнителям при выполнении запроса. В этом случае казалось проще всего использовать neography для выполнения моего запроса на работающем сервере neo4j. Если мы хотим создать одного человека с параметризованным именем, мы напишем следующий код:

1
2
3
4
5
require 'neography'
 
params = { :name => "Mark" }
 
Neography::Rest.new.execute_query("CREATE (:Person {name: {name} })", params)

Если мы выполним запрос, чтобы вернуть всех людей, мы увидим, что они были успешно созданы:

1
2
3
4
5
6
7
8
neo4j-sh (0)$ match p:Person return p;
==> +----------------------+
==> | p                    |
==> +----------------------+
==> | Node[1]{name:"Mark"} |
==> +----------------------+
==> 1 row
==> 175 ms

Я хотел передать массив имен, которые я изначально думал, что смогу сделать, но изменив значение в хэше params на массив:

1
2
3
4
5
6
7
8
require 'neography'
 
params = { :name => [] }
(1..500).each do |value|
  params[:name] << "person#{value}"
end
 
Neography::Rest.new.execute_query("CREATE (:Person {name: {name} })", params)

Я выполнил запрос, чтобы вернуть свои 500 человек, но получил только один:

1
2
3
4
5
6
7
8
neo4j-sh (0)$ MATCH p:Person RETURN p
> ;
==> +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
==> | p                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
==> +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
==> | Node[1]{name:["person1","person2","person3","person4","person5","person6","person7","person8","person9","person10","person11","person12","person13","person14","person15","person16","person17","person18","person19","person20","person21","person22","person23","person24","person25","person26","person27","person28","person29","person30","person31","person32","person33","person34","person35","person36","person37","person38","person39","person40","person41","person42","person43","person44","person45","person46","person47","person48","person49","person50","person51","person52","person53","person54","person55","person56","person57","person58","person59","person60","person61","person62","person63","person64","person65","person66","person67","person68","person69","person70","person71","person72","person73","person74","person75","person76","person77","person78","person79","person80","person81","person82","person83","person84","person85","person86","person87","person88","person89","person90","person91","person92","person93","person94","person95","person96","person97","person98","person99","person100","person101","person102","person103","person104","person105","person106","person107","person108","person109","person110","person111","person112","person113","person114","person115","person116","person117","person118","person119","person120","person121","person122","person123","person124","person125","person126","person127","person128","person129","person130","person131","person132","person133","person134","person135","person136","person137","person138","person139","person140","person141","person142","person143","person144","person145","person146","person147","person148","person149","person150","person151","person152","person153","person154","person155","person156","person157","person158","person159","person160","person161","person162","person163","person164","person165","person166","person167","person168","person169","person170","person171","person172","person173","person174","person175","person176","person177","person178","person179","person180","person181","person182","person183","person184","person185","person186","person187","person188","person189","person190","person191","person192","person193","person194","person195","person196","person197","person198","person199","person200","person201","person202","person203","person204","person205","person206","person207","person208","person209","person210","person211","person212","person213","person214","person215","person216","person217","person218","person219","person220","person221","person222","person223","person224","person225","person226","person227","person228","person229","person230","person231","person232","person233","person234","person235","person236","person237","person238","person239","person240","person241","person242","person243","person244","person245","person246","person247","person248","person249","person250","person251","person252","person253","person254","person255","person256","person257","person258","person259","person260","person261","person262","person263","person264","person265","person266","person267","person268","person269","person270","person271","person272","person273","person274","person275","person276","person277","person278","person279","person280","person281","person282","person283","person284","person285","person286","person287","person288","person289","person290","person291","person292","person293","person294","person295","person296","person297","person298","person299","person300","person301","person302","person303","person304","person305","person306","person307","person308","person309","person310","person311","person312","person313","person314","person315","person316","person317","person318","person319","person320","person321","person322","person323","person324","person325","person326","person327","person328","person329","person330","person331","person332","person333","person334","person335","person336","person337","person338","person339","person340","person341","person342","person343","person344","person345","person346","person347","person348","person349","person350","person351","person352","person353","person354","person355","person356","person357","person358","person359","person360","person361","person362","person363","person364","person365","person366","person367","person368","person369","person370","person371","person372","person373","person374","person375","person376","person377","person378","person379","person380","person381","person382","person383","person384","person385","person386","person387","person388","person389","person390","person391","person392","person393","person394","person395","person396","person397","person398","person399","person400","person401","person402","person403","person404","person405","person406","person407","person408","person409","person410","person411","person412","person413","person414","person415","person416","person417","person418","person419","person420","person421","person422","person423","person424","person425","person426","person427","person428","person429","person430","person431","person432","person433","person434","person435","person436","person437","person438","person439","person440","person441","person442","person443","person444","person445","person446","person447","person448","person449","person450","person451","person452","person453","person454","person455","person456","person457","person458","person459","person460","person461","person462","person463","person464","person465","person466","person467","person468","person469","person470","person471","person472","person473","person474","person475","person476","person477","person478","person479","person480","person481","person482","person483","person484","person485","person486","person487","person488","person489","person490","person491","person492","person493","person494","person495","person496","person497","person498","person499","person500"]} |
==> +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
==> 1 row

Я на самом деле использовал параметры таким образом, что только один человек был создан, и его свойство name было установлено в массив из 500 имен! Я настроил код для передачи в массив карт вместо строк, например, так:

1
2
3
4
5
6
7
8
require 'neography'
 
params = { :people => [] }
(1..500).each do |value|
  params[:people] << { :name => "person#{value}"}
end
 
Neography::Rest.new.execute_query("CREATE (:Person {people})", params)

Я запустил свой скрипт, чтобы заполнить базу данных, прежде чем запросить ее снова:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
neo4j-sh (0)$ MATCH p:Person RETURN p;
==> +-----------------------------+
==> | p                           |
==> +-----------------------------+
==> | Node[1]{name:"person1"}     |
==> | Node[2]{name:"person2"}     |
==> | Node[3]{name:"person3"}     |
==> | Node[4]{name:"person4"}     |
==> | Node[5]{name:"person5"}     |
==> | Node[6]{name:"person6"}     |
==> | Node[7]{name:"person7"}     |
==> | Node[8]{name:"person8"}     |
==> | Node[9]{name:"person9"}     |
==> | Node[10]{name:"person10"}   |
==> | Node[11]{name:"person11"}   |
...
==> +-----------------------------+
==> 500 rows
==> 1081 ms

На этот раз я получил ожидаемый результат, поэтому единственное, что нужно было сделать, это быстро проверить, сколько времени потребовалось для ввода параметризованных данных в базу данных:

1
2
3
4
5
$ time bundle exec ruby people.rb
 
real    0m0.993s
user    0m0.733s
sys 0m0.097s

И победителем становится … Майкл, примерно на 26 секунд за пробег.

В этой версии cypher создает план запроса при первом обращении к оператору CREATE, но после этого он может использовать план запроса для последующих вызовов, что приводит к значительному повышению производительности.

Алистер отметил, что подход с использованием параметров запроса весьма удобен при использовании в приложениях, поскольку вам не нужно беспокоиться о конкатенации строк, вместо этого вы можете просто изменить значения в картах.

Основываясь на этом опыте, теперь я могу подтвердить твит Макс Де Марци с прошлой недели:

Объявление о государственной службе для новичков в @ neo4j и Cypher: пожалуйста, используйте параметры в своих запросах http://docs.neo4j.org/chunked/milestone/cypher-parameters.html #MuchFaster