Ok. Is it possible to obtain the query execution time excluding the
compilation time using version 6 of virtuoso?
I think there is an execution time which virtuoso returns on normal query
execution. Can I obtain the query execution time by subtracting compilation
time from the execution time which virtuoso returns on normal query
execution?
On Tue, Nov 26, 2013 at 11:31 AM, Hugh Williams <hwilli...@openlinksw.com>wrote:
> Hi Rose,
>
> Actually the explain() function execution time is the compilation time for
> the query as it does not actually run the query, just compile it ...
>
> http://docs.openlinksw.com/virtuoso/fn_explain.html
>
> Best Regards
> Hugh Williams
> Professional Services
> OpenLink Software, Inc. // http://www.openlinksw.com/
> Weblog -- http://www.openlinksw.com/blogs/
> LinkedIn -- http://www.linkedin.com/company/openlink-software/
> Twitter -- http://twitter.com/OpenLink
> Google+ -- http://plus.google.com/100570109519069333827/
> Facebook -- http://www.facebook.com/OpenLinkSoftware
> Universal Data Access, Integration, and Management Technology Providers
>
> On 26 Nov 2013, at 05:25, Rose Beck <rosebeck...@gmail.com> wrote:
>
> I mean is the time returned by explain() the query execution time
> excluding the compilation time?
>
>
>
> On Tue, Nov 26, 2013 at 10:54 AM, Rose Beck <rosebeck...@gmail.com> wrote:
>
>> Sorry my bad.
>> explain() does return a time. Is the time which is returned by explain
>> the query execution time?
>>
>> SQL> explain('sparql select * where{?a ?b ?c}');
>> REPORT
>> VARCHAR
>>
>> _______________________________________________________________________________
>>
>> {
>> from DB.DBA.RDF_QUAD by RDF_QUAD_POGS 2.1e+08 rows
>> Key RDF_QUAD_POGS ASC ($23 "s_1_2_t0.S", $22 "s_1_2_t0.P", $21
>> "s_1_2_t0.O")
>>
>>
>>
>> After code:
>> 0: $26 "a" := Call __id2in ($23 "s_1_2_t0.S")
>> 5: $27 "b" := Call __id2in ($22 "s_1_2_t0.P")
>> 10: $28 "c" := Call __ro2sq ($21 "s_1_2_t0.O")
>> 15: BReturn 0
>> Select ($26 "a", $27 "b", $28 "c", <$25 "<DB.DBA.RDF_QUAD s_1_2_t0>" spec
>> 5>)
>> }
>>
>> 13 Rows. -- 1 msec.
>>
>> Since the dataset contains a lot of rows therefore I am a bit confused?
>>
>>
>> On Tue, Nov 26, 2013 at 11:00 AM, Hugh Williams <hwilli...@openlinksw.com
>> > wrote:
>>
>>> Hi Rose,
>>>
>>> I don't see any single quotes around the query in the explain function
>>> call ?
>>>
>>> Best Regards
>>> Hugh Williams
>>> Professional Services
>>> OpenLink Software, Inc. // http://www.openlinksw.com/
>>> Weblog -- http://www.openlinksw.com/blogs/
>>> LinkedIn -- http://www.linkedin.com/company/openlink-software/
>>> Twitter -- http://twitter.com/OpenLink
>>> Google+ -- http://plus.google.com/100570109519069333827/
>>> Facebook -- http://www.facebook.com/OpenLinkSoftware
>>> Universal Data Access, Integration, and Management Technology Providers
>>>
>>> On 26 Nov 2013, at 05:11, Rose Beck <rosebeck...@gmail.com> wrote:
>>>
>>> I tried to use explain() with SPARQL but its giving me the following
>>> error. Also does explain return the query execution time(which does not
>>> include the compilation time).
>>>
>>> SQL> explain (sparql select * where{?a ?b ?c});
>>>
>>> *** Error 37000: [Virtuoso Driver][Virtuoso Server]SQ074: Line 1: syntax
>>> error at 'sparql' before 'SELECT'
>>> at line 3 of Top-Level:
>>> explain (sparql select * where{?a ?b ?c})
>>> SQL> sparql explain(select * where{?a ?b ?c});
>>>
>>> *** Error 37000: [Virtuoso Driver][Virtuoso Server]SQ074: Line 1: SP030:
>>> SPARQL compiler, line 1: syntax error at 'explain' before '('
>>> at line 4 of Top-Level:
>>> sparql explain(select * where{?a ?b ?c})
>>> SQL> explain (select * where{?a ?b ?c});
>>>
>>> *** Error 37000: [Virtuoso Driver][Virtuoso Server]SQ074: Line 1: syntax
>>> error at 'select' before '*'
>>> at line 5 of Top-Level:
>>> explain (select * where{?a ?b ?c})
>>>
>>>
>>>
>>> On Tue, Nov 26, 2013 at 10:47 AM, Hugh Williams <
>>> hwilli...@openlinksw.com> wrote:
>>>
>>>> Hi Rose,
>>>>
>>>> You can use the explain() function to obtain a query plan with v6 but
>>>> it will not include the compilation time, see:
>>>>
>>>> http://docs.openlinksw.com/virtuoso/databaseadmsrv.html#perfdiagqueryplans
>>>>
>>>> Best Regards
>>>> Hugh Williams
>>>> Professional Services
>>>> OpenLink Software, Inc. // http://www.openlinksw.com/
>>>> Weblog -- http://www.openlinksw.com/blogs/
>>>> LinkedIn -- http://www.linkedin.com/company/openlink-software/
>>>> Twitter -- http://twitter.com/OpenLink
>>>> Google+ -- http://plus.google.com/100570109519069333827/
>>>> Facebook -- http://www.facebook.com/OpenLinkSoftware
>>>> Universal Data Access, Integration, and Management Technology Providers
>>>>
>>>> On 26 Nov 2013, at 01:55, Rose Beck <rosebeck...@gmail.com> wrote:
>>>>
>>>> Actually I do not have admin privileges on the server which I am
>>>> using, so wont be able to upgrade to version 7. Therefore it will be really
>>>> great if there is a method by which I may do profiling with virtuoso
>>>> version 6.
>>>>
>>>>
>>>>
>>>>
>>>> On Mon, Nov 25, 2013 at 5:33 PM, Rose Beck <rosebeck...@gmail.com>wrote:
>>>>
>>>>> Thanks a lot for helping again.
>>>>>
>>>>> Is there some way out by which I may do profiling with V6?
>>>>>
>>>>>
>>>>> On Mon, Nov 25, 2013 at 5:41 PM, Hugh Williams <
>>>>> hwilli...@openlinksw.com> wrote:
>>>>>
>>>>>> Hi Rose,
>>>>>>
>>>>>> As you are runing v6 the profile function will not work it requires
>>>>>> v7+ ...
>>>>>>
>>>>>> The following shows simpler output with the compilation time being
>>>>>> stated at the end:
>>>>>>
>>>>>> SQL> profile ('sparql select count(*) where {?s ?p ?o}');
>>>>>> result
>>>>>> LONG VARCHAR
>>>>>>
>>>>>> _______________________________________________________________________________
>>>>>>
>>>>>> 287536
>>>>>>
>>>>>> {
>>>>>> time 0.11% fanout 1 input 1 rows
>>>>>> time 0.08% fanout 1 input 1 rows
>>>>>> { fork
>>>>>> time 1e+02% fanout 0 input 1 rows
>>>>>> RDF_QUAD 2.9e+05 rows()
>>>>>>
>>>>>>
>>>>>> After code:
>>>>>> 0: sum callret-0 1 set no set_ctr
>>>>>> 5: BReturn 0
>>>>>> }
>>>>>> time 0.046% fanout 0 input 1 rows
>>>>>> Select (callret-0)
>>>>>> }
>>>>>>
>>>>>>
>>>>>> 4 msec 77% cpu, 1 rnd 287210 seq 0% same seg
>>>>>> 0% same pg
>>>>>> Compilation: 1 msec 0 reads 0% read 0 messages 0% clw
>>>>>>
>>>>>> 4 Rows. -- 132 msec.
>>>>>> SQL>
>>>>>>
>>>>>> So compilation is 1ms and execution is 132msec
>>>>>>
>>>>>> Best Regards
>>>>>> Hugh Williams
>>>>>> Professional Services
>>>>>> OpenLink Software, Inc. //
>>>>>> http://www.openlinksw.com/
>>>>>> Weblog -- http://www.openlinksw.com/blogs/
>>>>>> LinkedIn -- http://www.linkedin.com/company/openlink-software/
>>>>>> Twitter -- http://twitter.com/OpenLink
>>>>>> Google+ -- http://plus.google.com/100570109519069333827/
>>>>>> Facebook -- http://www.facebook.com/OpenLinkSoftware
>>>>>> Universal Data Access, Integration, and Management Technology
>>>>>> Providers
>>>>>>
>>>>>> On 25 Nov 2013, at 11:19, Rose Beck <rosebeck...@gmail.com> wrote:
>>>>>>
>>>>>> Dear Hugh,
>>>>>>
>>>>>> Thanks a lot for the help.
>>>>>> I am using virtuoso version: Version 6.1.8-dev.3127-pthreads as of
>>>>>> Aug 10 2013
>>>>>> Also I am unable to find the compilation and query execution time
>>>>>> from the example result set given below. Can you please help me a bit
>>>>>> with
>>>>>> this.
>>>>>>
>>>>>> {
>>>>>> -- In the below excerpt some lines are omitted. Comments are inline.
>>>>>>
>>>>>>
>>>>>> time 1.3e-06% fanout 1 1 rows in
>>>>>>
>>>>>> s# 98 cluster outer seq start, set no <V $40 set_ctr in>
>>>>>> save ctx:()
>>>>>>
>>>>>> -- the section inside the fork is executed to the end
>>>>>> -- before the section after the fork is started.
>>>>>> s# 161 Fork 42
>>>>>> {
>>>>>> wait time 0.00012% of exec real time, fanout 0
>>>>>>
>>>>>> -- The below introduces the 2 stage DFG which does most of the work.
>>>>>> s# 108 { Cluster location fragment 51 0 unordered
>>>>>> Params: ()
>>>>>> Output: ()
>>>>>>
>>>>>> -- First partitioning step. Each of the 32 slices gets the
>>>>>> -- identical plan to execute, this is a flood for all is this
>>>>>> -- first step does not specify a partitioning key value.
>>>>>>
>>>>>> time 8e-05% fanout 0 0 rows in
>>>>>> s# 115 Stage 1: Params: (<V $112 set_ctr in>)
>>>>>>
>>>>>> -- This is the initial table scan. The operator is executed
>>>>>> -- 32 times, once for each slice of data.
>>>>>>
>>>>>> time 0.33% fanout 3.1e+06 32 rows in
>>>>>> s# 121 from DB.DBA.ct_c by ct_c 1.7e+08 rows
>>>>>> Key ct_c ASC (<V $28 a.k2 in>)
>>>>>> [copies params]
>>>>>> vector param casts: <V $40 set_ctr in>-> <V $112 set_ctr in>
>>>>>>
>>>>>> -- This is the second partitioning step, each of the rows of
>>>>>> -- output of the previous is sent to the slice corresponding to K2.
>>>>>>
>>>>>> time 31% fanout 0.9 2e+08 rows in
>>>>>> s# 131 Stage 2: Params: (<v $134 q_a.k2 S136 in>, <V $138 set_ctr in>)
>>>>>>
>>>>>> -- This is the 2nd index lookup, by a.k2 = b.k1 There are two
>>>>>> -- parameters given in the vector param cast section, the first
>>>>>> -- is a.k2 and the second is the set number. This is constant
>>>>>> -- since the query is run on a single row of input but if the
>>>>>> -- query were run on multiple rows of input this would specify
>>>>>> -- which row of input the a.k2 comes from so that this would be
>>>>>> -- aggregated in the right place. Many aggregates can be produced
>>>>>> -- in a single invocation with multiple rows of input.
>>>>>>
>>>>>> time 68% fanout 0 2e+08 rows in
>>>>>> s# 144 from DB.DBA.ct_c by ct_c unq 1 rows
>>>>>> Key ct_c ASC ()
>>>>>> inlined k1 = <v $134 q_a.k2 S136 in>
>>>>>> vector param casts: <V $28 a.k2 in>-> <v $134 q_a.k2 S136 in>, <r $112
>>>>>> set_ctr via S121>-> <V $138 set_ctr in>
>>>>>>
>>>>>>
>>>>>> -- This is the aggregation.
>>>>>>
>>>>>> After code:
>>>>>> 0: sum <V $43 count i> 1 set no <r $138 set_ctr via S144>
>>>>>> 5: BReturn 0
>>>>>>
>>>>>> }
>>>>>> }
>>>>>>
>>>>>> -- The below returns the aggregate to the client. The combining of
>>>>>> -- aggregates from different slices is implicit in the for operator
>>>>>> -- whenever this ends with a cluster location fragment.
>>>>>>
>>>>>> time 5.4e-07% fanout 0 1 rows in
>>>>>> s# 164 Select (<V $43 count i>)
>>>>>> set no: <V $40 set_ctr in>
>>>>>> }
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Mon, Nov 25, 2013 at 4:42 PM, Rose Beck <rosebeck...@gmail.com>wrote:
>>>>>>
>>>>>>> Dear Hugh,
>>>>>>>
>>>>>>> Thanks a lot for the help. I tried to profile using profile but I am
>>>>>>> getting the following errors:
>>>>>>> SQL> profile('sparql select ?a?b?c where{?a ?b ?c}');
>>>>>>>
>>>>>>> *** Error 42001: [Virtuoso Driver][Virtuoso Server]SR185: Undefined
>>>>>>> procedure DB.DBA.profile.
>>>>>>> at line 9 of Top-Level:
>>>>>>> profile('sparql select ?a?b?c where{?a ?b ?c}')
>>>>>>> SQL> sparql profile('select ?a?b?c where{?a ?b ?c}');
>>>>>>>
>>>>>>> *** Error 37000: [Virtuoso Driver][Virtuoso Server]SQ074: Line 1:
>>>>>>> SP030: SPARQL compiler, line 1: syntax error at 'profile' before '('
>>>>>>> at line 10 of Top-Level:
>>>>>>> sparql profile('select ?a?b?c where{?a ?b ?c}')
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Mon, Nov 25, 2013 at 4:35 PM, Hugh Williams <
>>>>>>> hwilli...@openlinksw.com> wrote:
>>>>>>>
>>>>>>>> Hi Rose,
>>>>>>>>
>>>>>>>> With Virtuoso 7 you can obtain the compilation (query plan) and
>>>>>>>> query execution time of a query using the "profile" function as
>>>>>>>> detailed at:
>>>>>>>>
>>>>>>>>
>>>>>>>> http://docs.openlinksw.com/virtuoso/databaseadmsrv.html#readingqueryprofile
>>>>>>>>
>>>>>>>> You can also enable general query logging and profiling using the
>>>>>>>> "prof_enable" function as detailed at:
>>>>>>>>
>>>>>>>> http://docs.openlinksw.com/virtuoso/databaseadmsrv.html#querylogging
>>>>>>>>
>>>>>>>>
>>>>>>>> Best Regards
>>>>>>>> Hugh Williams
>>>>>>>> Professional Services
>>>>>>>> OpenLink Software, Inc. //
>>>>>>>> http://www.openlinksw.com/
>>>>>>>> Weblog -- http://www.openlinksw.com/blogs/
>>>>>>>> LinkedIn -- http://www.linkedin.com/company/openlink-software/
>>>>>>>> Twitter -- http://twitter.com/OpenLink
>>>>>>>> Google+ -- http://plus.google.com/100570109519069333827/
>>>>>>>> Facebook -- http://www.facebook.com/OpenLinkSoftware
>>>>>>>> Universal Data Access, Integration, and Management Technology
>>>>>>>> Providers
>>>>>>>>
>>>>>>>> On 25 Nov 2013, at 09:15, Rose Beck <rosebeck...@gmail.com> wrote:
>>>>>>>>
>>>>>>>> After executing SPARQL queries in virtuoso using:
>>>>>>>> 1. ./isql 1111 dba dba
>>>>>>>> 2. And then firing the SPARQL query
>>>>>>>>
>>>>>>>> After this virtuoso returns the query execution time. So does the
>>>>>>>> returned time include the plan generation time. If yes, is it possible
>>>>>>>> to
>>>>>>>> find:
>>>>>>>> (plan generation time) and (time to retrieve the results after the
>>>>>>>> plan has already been generated by Virtuoso)?
>>>>>>>>
>>>>>>>> I need to know these query times as I am benchmarking Virtuoso
>>>>>>>> against postgreSQL. With postgresql I am able to separately retrieve
>>>>>>>> the
>>>>>>>> query execution time after the plan has been generated by postgresql
>>>>>>>> using
>>>>>>>> \timing. It would be great if Virtuoso could also give me the time it
>>>>>>>> takes to retrieve the results after the plan has been generated
>>>>>>>> separately
>>>>>>>> as that would help me take a one to one comparison.
>>>>>>>>
>>>>>>>> I'll be really grateful to you for the guidance.
>>>>>>>>
>>>>>>>> ------------------------------------------------------------------------------
>>>>>>>> Shape the Mobile Experience: Free Subscription
>>>>>>>> Software experts and developers: Be at the forefront of tech
>>>>>>>> innovation.
>>>>>>>> Intel(R) Software Adrenaline delivers strategic insight and
>>>>>>>> game-changing
>>>>>>>> conversations that shape the rapidly evolving mobile landscape.
>>>>>>>> Sign up now.
>>>>>>>>
>>>>>>>> http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk_______________________________________________
>>>>>>>> Virtuoso-users mailing list
>>>>>>>> Virtuoso-users@lists.sourceforge.net
>>>>>>>> https://lists.sourceforge.net/lists/listinfo/virtuoso-users
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>> ------------------------------------------------------------------------------
>>>> Shape the Mobile Experience: Free Subscription
>>>> Software experts and developers: Be at the forefront of tech innovation.
>>>> Intel(R) Software Adrenaline delivers strategic insight and
>>>> game-changing
>>>> conversations that shape the rapidly evolving mobile landscape. Sign up
>>>> now.
>>>>
>>>> http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk_______________________________________________
>>>> Virtuoso-users mailing list
>>>> Virtuoso-users@lists.sourceforge.net
>>>> https://lists.sourceforge.net/lists/listinfo/virtuoso-users
>>>>
>>>>
>>>>
>>>
>>>
>>
>
> ------------------------------------------------------------------------------
> Shape the Mobile Experience: Free Subscription
> Software experts and developers: Be at the forefront of tech innovation.
> Intel(R) Software Adrenaline delivers strategic insight and game-changing
> conversations that shape the rapidly evolving mobile landscape. Sign up
> now.
>
> http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk_______________________________________________
> Virtuoso-users mailing list
> Virtuoso-users@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/virtuoso-users
>
>
>
------------------------------------------------------------------------------
Shape the Mobile Experience: Free Subscription
Software experts and developers: Be at the forefront of tech innovation.
Intel(R) Software Adrenaline delivers strategic insight and game-changing
conversations that shape the rapidly evolving mobile landscape. Sign up now.
http://pubads.g.doubleclick.net/gampad/clk?id=63431311&iu=/4140/ostg.clktrk
_______________________________________________
Virtuoso-users mailing list
Virtuoso-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/virtuoso-users