Hi All, I've moved a demo application to AWS Ec2 from another hosted setup. Since doing so I've found that a large portion of my sparql queries have gone from returning in 0.5 seconds to 7-8 seconds. The datasets themselves are quite small ~ 120k triples total in quad store.
An example of one of the queries and explain plan is below. PREFIX INT: <http://localhost:8890/schemas/OE/> PREFIX HR: <http://localhost:8890/schemas/HR/> SELECT (bif:concat((bif:subseq(str(?order_date), 0, 4))," ","Q", str(bif:quarter(?order_date)))) as ?x_axis, (sum(?cost_price*xsd:decimal(str(?quantity)))/ sum(?list_price*xsd:decimal(str(?quantity)))*100) as ?cost, (sum(?unit_price*xsd:decimal(str(?quantity)) - ?cost_price*xsd:decimal(str(?quantity))) / sum(?list_price*xsd:decimal(str(?quantity)))*100) as ?profit, (sum(?unit_price*xsd:decimal(str(?quantity))*?commission_pct) / sum(?list_price*xsd:decimal(str(?quantity)))*100)as ?commission, (sum(?list_price*xsd:decimal(str(?quantity)) - (?unit_price*xsd:decimal(str(?quantity))))/ sum(?list_price*xsd:decimal(str(?quantity)))*100) as ?discount FROM <http://demo.clearbluewater.com.au/wsf/datasets/8/> FROM <http://demo.clearbluewater.com.au/wsf/datasets/9/> WHERE { ?order_uri a INT:ORDERS . ?order_uri INT:order_date ?order_date . ?order_uri INT:orders_has_sales_rep ?sales_rep_uri . ?sales_rep_uri INT:sales_rep_id ?sales_rep_id . ?employee_uri a HR:EMPLOYEES . ?employee_uri HR:employee_id ?employee_id . ?employee_uri HR:commission_pct ?commission_pct . filter (?employee_id = ?sales_rep_id) ?order_uri INT:order_of_order_items ?order_items_uri . ?order_items_uri INT:quantity ?quantity . ?order_items_uri INT:unit_price ?unit_price . ?order_items_uri INT:order_items_has_product_information ?product_uri . ?product_uri INT:cost_price ?cost_price . ?product_uri INT:list_price ?list_price . } group by (bif:concat((bif:subseq(str(?order_date), 0, 4))," ","Q", str(bif:quarter(?order_date)))) order by 1 explain plan - { Precode: 0: $24 "schemas/HR/EMPLOYEES" := Call __i2idn (<constant ( http://localhost:8890/schemas/HR/EMPLOYEES)>) 5: $25 "schemas/HR/commission_pct" := Call __i2idn (<constant ( http://localhost:8890/schemas/HR/commission_pct)>) 10: $26 "schemas/HR/employee_id" := Call __i2idn (<constant ( http://localhost:8890/schemas/HR/employee_id)>) 15: $27 "schemas/OE/sales_rep_id" := Call __i2idn (<constant ( http://localhost:8890/schemas/OE/sales_rep_id)>) 20: $28 "schemas/OE/orders_has_sales_rep" := Call __i2idn (<constant ( http://localhost:8890/schemas/OE/orders_has_sales_rep)>) 25: $29 "schemas/OE/ORDERS" := Call __i2idn (<constant ( http://localhost:8890/schemas/OE/ORDERS)>) 30: $30 "schemas/OE/order_of_order_items" := Call __i2idn (<constant ( http://localhost:8890/schemas/OE/order_of_order_items)>) 35: $31 "schemas/OE/order_date" := Call __i2idn (<constant ( http://localhost:8890/schemas/OE/order_date)>) 40: $32 "schemas/OE/order_items_has_product_information" := Call __i2idn (<constant (http://localhost:8890/schemas/OE/order_items_has_product_information)>) 45: $33 "schemas/OE/unit_price" := Call __i2idn (<constant ( http://localhost:8890/schemas/OE/unit_price)>) 50: $34 "schemas/OE/quantity" := Call __i2idn (<constant ( http://localhost:8890/schemas/OE/quantity)>) 55: $35 "schemas/OE/list_price" := Call __i2idn (<constant ( http://localhost:8890/schemas/OE/list_price)>) 60: $36 "schemas/OE/cost_price" := Call __i2idn (<constant ( http://localhost:8890/schemas/OE/cost_price)>) 65: BReturn 0 Subquery 37 { Fork 181 { Fork 154 { END Node After test: 0: if (<constant (0)> 1(=) <constant (1)>) then 5 else 4 unkn 5 4: BReturn 1 5: BReturn 0 in iterates $45 "in_iter" over (<constant (#i1100006)>, <constant (#i1083254)>) from DB.DBA.RDF_QUAD by RDF_QUAD_POGS 0.78 rows Key RDF_QUAD_POGS ASC ($49 "s-7-1-t4.S") <col=551 P = <constant (#i1)>> , <col=552 O = $24 "schemas/HR/EMPLOYEES"> , <col=549 G = $45 "in_iter"> 0 Local Test 0: if (<constant (0)> 1(=) <constant (1)>) then 5 else 4 unkn 5 4: BReturn 1 5: BReturn 0 from DB.DBA.RDF_QUAD by RDF_QUAD_POGS 0.43 rows Key RDF_QUAD_POGS ASC ($55 "s-7-1-t6.S", $54 "s-7-1-t6.O", $53 "s-7-1-t6.G") inlined <col=551 P = $25 "schemas/HR/commission_pct"> row specs: <col=550 S = $49 "s-7-1-t4.S"> Local Test 0: $58 "callret" := Call one_of_these ($53 "s-7-1-t6.G", <constant (#i1100006)>, <constant (#i1083254)>) 5: if (<constant (0)> 2(<) $58 "callret") then 9 else 10 unkn 10 9: BReturn 1 10: BReturn 0 from DB.DBA.RDF_QUAD by RDF_QUAD_POGS 0.43 rows Key RDF_QUAD_POGS ASC ($61 "s-7-1-t5.O", $60 "s-7-1-t5.G") inlined <col=551 P = $26 "schemas/HR/employee_id"> row specs: <col=550 S = $49 "s-7-1-t4.S"> Local Test 0: $64 "callret" := Call one_of_these ($60 "s-7-1-t5.G", <constant (#i1100006)>, <constant (#i1083254)>) 5: if (<constant (0)> 2(<) $64 "callret") then 9 else 10 unkn 10 9: BReturn 1 10: BReturn 0 in iterates $66 "in_iter" over (<constant (#i1100006)>, <constant (#i1083254)>) from DB.DBA.RDF_QUAD by RDF_QUAD_POGS 0.8 rows Key RDF_QUAD_POGS ASC ($70 "s-7-1-t3.S") <col=551 P = $27 "schemas/OE/sales_rep_id"> , <col=552 O = $61 "s-7-1-t5.O"> , <col=549 G = $66 "in_iter"> in iterates $74 "in_iter" over (<constant (#i1100006)>, <constant (#i1083254)>) from DB.DBA.RDF_QUAD by RDF_QUAD_POGS 0.8 rows Key RDF_QUAD_POGS ASC ($78 "s-7-1-t2.S") <col=551 P = $28 "schemas/OE/orders_has_sales_rep"> , <col=552 O = $70 "s-7-1-t3.S"> , <col=549 G = $74 "in_iter"> 0 in iterates $82 "in_iter" over (<constant (#i1100006)>, <constant (#i1083254)>) from DB.DBA.RDF_QUAD by RDF_QUAD 0.48 rows Key RDF_QUAD ASC ($86 "s-7-1-t0.S") inlined <col=551 P = <constant (#i1)>> , <col=550 S = $78 "s-7-1-t2.S"> , <col=552 O = $29 "schemas/OE/ORDERS"> , <col=549 G = $82 "in_iter"> 0 from DB.DBA.RDF_QUAD by RDF_QUAD_POGS 0.43 rows Key RDF_QUAD_POGS ASC ($92 "s-7-1-t1.S", $91 "s-7-1-t1.O", $90 "s-7-1-t1.G") inlined <col=551 P = $31 "schemas/OE/order_date"> row specs: <col=550 S = $86 "s-7-1-t0.S"> Local Test 0: $95 "callret" := Call one_of_these ($90 "s-7-1-t1.G", <constant (#i1100006)>, <constant (#i1083254)>) 5: if (<constant (0)> 2(<) $95 "callret") then 9 else 10 unkn 10 9: BReturn 1 10: BReturn 0 from DB.DBA.RDF_QUAD by RDF_QUAD_POGS 0.43 rows Key RDF_QUAD_POGS ASC ($98 "s-7-1-t7.O", $97 "s-7-1-t7.G") inlined <col=551 P = $30 "schemas/OE/order_of_order_items"> 0 Local Test 0: $101 "callret" := Call one_of_these ($97 "s-7-1-t7.G", <constant (#i1100006)>, <constant (#i1083254)>) 5: if (<constant (0)> 2(<) $101 "callret") then 9 else 10 unkn 10 9: BReturn 1 10: BReturn 0 Since it is not just one query could it be a system wide issue. Eg Stats need to be updated? Memory doesn't appear to be a problem. Cheers Mark
