{"action":"created","issue":{"url":"https://api.github.com/repos/openlink/virtuoso-opensource/issues/769","repository_url":"https://api.github.com/repos/openlink/virtuoso-opensource","labels_url":"https://api.github.com/repos/openlink/virtuoso-opensource/issues/769/labels{/name}","comments_url":"https://api.github.com/repos/openlink/virtuoso-opensource/issues/769/comments","events_url":"https://api.github.com/repos/openlink/virtuoso-opensource/issues/769/events","html_url":"https://github.com/openlink/virtuoso-opensource/issues/769","id":350711590,"node_id":"MDU6SXNzdWUzNTA3MTE1OTA=","number":769,"title":"Nested Query performance ","user":{"login":"aishahasmoh","id":42137324,"node_id":"MDQ6VXNlcjQyMTM3MzI0","avatar_url":"https://avatars2.githubusercontent.com/u/42137324?v=4","gravatar_id":"","url":"https://api.github.com/users/aishahasmoh","html_url":"https://github.com/aishahasmoh","followers_url":"https://api.github.com/users/aishahasmoh/followers","following_url":"https://api.github.com/users/aishahasmoh/following{/other_user}","gists_url":"https://api.github.com/users/aishahasmoh/gists{/gist_id}","starred_url":"https://api.github.com/users/aishahasmoh/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/aishahasmoh/subscriptions","organizations_url":"https://api.github.com/users/aishahasmoh/orgs","repos_url":"https://api.github.com/users/aishahasmoh/repos","events_url":"https://api.github.com/users/aishahasmoh/events{/privacy}","received_events_url":"https://api.github.com/users/aishahasmoh/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":5,"created_at":"2018-08-15T07:25:30Z","updated_at":"2018-09-02T07:49:21Z","closed_at":null,"author_association":"NONE","body":"# Nested query performance on Virtuoso\r\n\r\n## Introduction\r\nI am using `isql-vt` to query my local OpenLink Virtuoso server. \r\n\r\n### Twittter graph query\r\n\r\nI am trying to run a query on a Twitter graph to find the tweets of tweeters who have only 250-300 tweets. Specifically I am trying to find the (content, tweeter, hashtags, mentions, and URIs) in the tweets of the tweeters who have 250-300 tweets. I ran into a performance issue that I will report here.\r\n\r\nAs twitter graph is not very well known, I replicated the same issue on the DBpedia dataset. I let _places of birth_ replace _tweeters_ and _people born in a place_ replace the _tweets of the tweeter_.\r\n\r\n### DBpedia query\r\n\r\nIn this query, I am trying to find the (name, place of birth, optionally date of birth and date of death) for every instance that has a date of birth. The only condition that I have is I only want people who were born in places such that only _250 to 300_ people in DBpedia where born in that place. \r\n\r\nI am using a nested query to do it. \r\n\r\n- The inner query finds all the _places of birth_ such that the number of people who were born in that place are 250-300. \r\n- The outer query finds (name, place of birth, optionally date of birth and date of death) of all instances of all people in dbpedia. \r\n- The join implemented by the nested query filters these people to include only those whose place of birth is one of the places returned by the inner query. \r\n\r\nThe implementation of the queries is included at the end of this report. \r\n\r\nThe problem is the huge degradation of performance once I use the nested query.\r\n- The inner query returns 137 Rows in 3.865 sec.\r\n- The outer query returns 3,976,703 Rows in 253.890 sec = 4.2315 min\r\n- The nested query returns 79640 Rows in 10623.9 sec = 177.1 min = 2.95 hour\r\n\r\nMy questions are: \r\n1. Can you explain what causes this degradation of performance when using the nested queries?\r\n2. Is there a way to improve Virtuoso performance on nested queries?\r\n3. Is there a way to rewrite this query in SPARQL to be faster?\r\n\r\n## Implementation of queries in sparql\r\n\r\n### Inner query\r\n```sparql\r\nPREFIX  :     <http://dbpedia.org/resource/>\r\nPREFIX  dbo:  <http://dbpedia.org/ontology/>\r\nPREFIX  dbp:  <http://dbpedia.org/property/>\r\n\r\nSELECT ?place\r\nWHERE\r\n{\r\n  GRAPH <http://dbpedia.org/>\r\n  {  \r\n    ?person  dbo:birthPlace  ?place \r\n  } \r\n}\r\nGROUP BY ?place \r\nHAVING ((count(?person) < 300) && (count(?person) > 250)) \r\nORDER BY ?place\r\n```\r\n\r\n### Outer query\r\n```sparql\r\nPREFIX  :     <http://dbpedia.org/resource/>\r\nPREFIX  dbo:  <http://dbpedia.org/ontology/>\r\nPREFIX  dbp:  <http://dbpedia.org/property/>\r\nPREFIX  foaf: <http://xmlns.com/foaf/0.1/>\r\n\r\nSELECT ?name, ?place , ?birth, ?death \r\nWHERE\r\n{ \r\n  GRAPH <http://dbpedia.org/>\r\n  { \r\n    ?person dbo:birthPlace  ?place ;\r\n             foaf:name       ?name .\r\n    OPTIONAL \r\n    {\r\n      ?person dbo:birthDate   ?birth ;\r\n              dbo:deathDate   ?death     \r\n    }        \r\n  }\r\n}\r\nORDER BY ?place\r\n```\r\n\r\n### Nested query\r\n```sparql\r\nPREFIX  :     <http://dbpedia.org/resource/>\r\nPREFIX  dbo:  <http://dbpedia.org/ontology/>\r\nPREFIX  dbp:  <http://dbpedia.org/property/>\r\nPREFIX  foaf: <http://xmlns.com/foaf/0.1/>\r\n\r\nSELECT ?name, ?place ,?birth, ?death \r\nWHERE\r\n{ \r\n  GRAPH <http://dbpedia.org/>\r\n  {  \r\n    ?person  dbo:birthPlace  ?place ;\r\n             foaf:name       ?name .\r\n    OPTIONAL \r\n    {\r\n      ?person dbo:birthDate   ?birth ;\r\n              dbo:deathDate   ?death\r\n    }\r\n    {\r\n      SELECT ?place\r\n      WHERE\r\n      {\r\n        GRAPH <http://dbpedia.org/>\r\n        {  \r\n          ?person  dbo:birthPlace  ?place \r\n        }\r\n      } \r\n      GROUP BY ?place \r\n      HAVING ((count(?person) < 300) && (count(?person) > 250)) \r\n      ORDER BY ?place\r\n    }       \r\n  }\r\n}\r\nORDER BY ?place\r\n```\r\n\r\n"},"comment":{"url":"https://api.github.com/repos/openlink/virtuoso-opensource/issues/comments/417911798","html_url":"https://github.com/openlink/virtuoso-opensource/issues/769#issuecomment-417911798","issue_url":"https://api.github.com/repos/openlink/virtuoso-opensource/issues/769","id":417911798,"node_id":"MDEyOklzc3VlQ29tbWVudDQxNzkxMTc5OA==","user":{"login":"aishahasmoh","id":42137324,"node_id":"MDQ6VXNlcjQyMTM3MzI0","avatar_url":"https://avatars2.githubusercontent.com/u/42137324?v=4","gravatar_id":"","url":"https://api.github.com/users/aishahasmoh","html_url":"https://github.com/aishahasmoh","followers_url":"https://api.github.com/users/aishahasmoh/followers","following_url":"https://api.github.com/users/aishahasmoh/following{/other_user}","gists_url":"https://api.github.com/users/aishahasmoh/gists{/gist_id}","starred_url":"https://api.github.com/users/aishahasmoh/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/aishahasmoh/subscriptions","organizations_url":"https://api.github.com/users/aishahasmoh/orgs","repos_url":"https://api.github.com/users/aishahasmoh/repos","events_url":"https://api.github.com/users/aishahasmoh/events{/privacy}","received_events_url":"https://api.github.com/users/aishahasmoh/received_events","type":"User","site_admin":false},"created_at":"2018-09-02T07:49:21Z","updated_at":"2018-09-02T07:49:21Z","author_association":"NONE","body":"Thank you for your response. \r\nI have been trying to work on it.\r\n\r\nFirst, I shut down virtuoso and checked the memory that I have and it is\r\n```\r\nMemTotal:       264109248 kB\r\nMemFree:        187115264 kB\r\n```\r\nSo, I do have around 180GB of RAM available for virtuoso.\r\n\r\nSecond, Yes my Twitter graph is in the same file on the same instance. \r\n\r\nThird, \r\nI tried to update to version 7 stable but I faced some errors that I couldn't resolve while trying to update to version 7. I followed the instructions as in the git repo for a 64 bit linux machine. I could build it but when I tried to compile using ```make``` I got the following error:\r\n```\r\nmake[2]: Entering directory `/ssd/amohamed/virtuoso-opensource/binsrc/VirtuosoClient.Net'\r\nmcs /unsafe /target:library /out:OpenLink.Data.Virtuoso.dll /sdk:2 /optimize+ /d:MONO /d:ADONET2 ./ArrayOfLongPacked.cs ./AssemblyInfo.cs ./BlobHandle.cs ./BufferType.cs ./CLI.cs ./ConnectionOptions.cs ./ConnectionPool.cs ./ConnectionStringParser.cs ./DataType.cs ./DateTimeMarshaler.cs ./Diagnostics.cs ./DTC.cs ./Era.cs ./ExplicitString.cs ./Future.cs ./FutureList.cs ./IConvertData.cs ./ICreateErrors.cs ./IInnerCommand.cs ./IInnerConnection.cs ./IMarshal.cs ./InnerConnectionBase.cs ./ISession.cs ./IVirtuosoError.cs ./ManagedCommand.cs ./ManagedConnection.cs ./ManagedErrors.cs ./Marshaler.cs ./MD5.cs ./MemoryHandle.cs ./Platform.cs ./RPC.cs ./Service.cs ./SqlXml.cs ./SqlExtendedString.cs ./SqlRdfBox.cs ./TcpConnection.cs ./TcpSession.cs ./Unix.cs ./VirtDbType.cs ./VirtuosoCommandBuilder.cs ./VirtuosoCommand.cs ./VirtuosoConnection.cs ./VirtuosoDataAdapter.cs ./VirtuosoDataReader.cs ./VirtuosoDateTime.cs ./VirtuosoDateTimeOffset.cs ./VirtuosoErrorCollection.cs ./VirtuosoError.cs ./VirtuosoException.cs ./VirtuosoInfoMessage.cs ./VirtuosoParameterCollection.cs ./VirtuosoParameter.cs ./VirtuosoPermissionAttribute.cs ./VirtuosoPermission.cs ./VirtuosoTimeSpan.cs ./VirtuosoTransaction.cs ./VirtuosoWarning.cs ./Win32.cs ./VirtuosoClientFactory.cs ./VirtuosoCodeAccessPermission.cs ./VirtuosoConnectionStringBuilder.cs /r:System.Data /r:System.EnterpriseServices /r:System.Drawing /resource:./OpenLink.Data.VirtuosoClient.VirtuosoConnection.bmp /resource:./OpenLink.Data.VirtuosoClient.VirtuosoCommand.bmp /resource:./OpenLink.Data.VirtuosoClient.VirtuosoDataAdapter.bmp\r\nwarning CS8001: SDK path could not be resolved\r\n./AssemblyInfo.cs(66,12): warning CS1699: Use compiler option `keyfile' or appropriate project settings instead of `AssemblyKeyFile' attribute\r\nerror CS0006: Metadata file `System.Data' could not be found\r\nerror CS0006: Metadata file `System.EnterpriseServices' could not be found\r\nCompilation failed: 2 error(s), 2 warnings\r\nmake[2]: *** [OpenLink.Data.Virtuoso.dll] Error 1\r\nmake[2]: Leaving directory `/ssd/amohamed/virtuoso-opensource/binsrc/VirtuosoClient.Net'\r\nmake[1]: *** [all-recursive] Error 1\r\nmake[1]: Leaving directory `/ssd/amohamed/virtuoso-opensource/binsrc'\r\nmake: *** [all-recursive] Error 1\r\n```\r\nI don't know what are the ``` `System.Data' ``` and the ```System.EnterpriseServices``` ans why they are not found.\r\n\r\nHere is my machine info as revealed by ```lscpu``` command:\r\n```\r\nArchitecture:          x86_64\r\nCPU op-mode(s):        32-bit, 64-bit\r\nByte Order:            Little Endian\r\nCPU(s):                48\r\nOn-line CPU(s) list:   0-47\r\nThread(s) per core:    2\r\nCore(s) per socket:    12\r\nSocket(s):             2\r\n```\r\nI found that someone had the same issue before but it was not resolved. I found the other issue here:\r\nhttps://github.com/openlink/virtuoso-opensource/issues/623\r\n\r\nCan you please help me resolve this error?\r\nThank you "}}
{"actor":{"display_login":"aishahasmoh"}}