Hi,
We have a BaseX database of 3.2 MB size with 146,036,090 nodes on 451,124
documents
Executing a simple query that returns only 4 elements is taking 6 seconds to
run.
Here is the execution plan:
Optimized Query:
*:entity
Query:
entity
Result:
- Hit(s): 4 Items
- Updated: 0 Items
- Printed: 549 b
- Read Locking: deterioroTest
- Write Locking: (none)
Timing:
- Parsing: 0.22 ms
- Compiling: 1.13 ms
- Evaluating: 4785.05 ms
- Printing: 28.17 ms
- Total Time: 4814.58 ms
Query plan:
<QueryPlan compiled="true" updating="false">
<CachedPath type="element()+" size="4">
<IterStep axis="child" test="*:entity" type="element()*"/>
</CachedPath>
</QueryPlan>
And this is path list obtained from BaseX GUI
doc(): 451124x
entity: 4x
name: 4x, leaf
text(): 4x, 4 distinct strings, leaf
active: 4x, leaf
text(): 4x, string, leaf
entityid: 4x, leaf
text(): 4x, 4 distinct integers [1, 90086898], leaf
dateadd: 3x, leaf
text(): 3x, 3 distinct integers [1.536184706046E12, 1.538624904652E12],
leaf
description: 1x, leaf
text(): 1x, string, leaf
user: 3x
password: 3x, leaf
text(): 3x, 3 distinct strings, leaf
role: 3x, leaf
text(): 3x, 3 distinct integers [1, 3], leaf
name: 3x, leaf
text(): 3x, 3 distinct strings, leaf
active: 3x, leaf
text(): 3x, string, leaf
entityid: 3x, leaf
text(): 3x, 2 distinct integers [1, 90086898], leaf
userid: 3x, leaf
text(): 3x, 3 distinct integers [1, 1152438998], leaf
email: 3x, leaf
text(): 3x, 3 distinct strings, leaf
dateadd: 2x, leaf
text(): 2x, 2 distinct integers [1.537475569294E12, 1.537993241561E12],
leaf
project: 35x
dateadd: 35x, leaf
text(): 35x, 35 distinct integers [1.536184761155E12, 1.543940735205E12],
leaf
name: 35x, leaf
text(): 35x, 28 distinct strings, leaf
entityid: 35x, leaf
text(): 35x, 4 distinct integers [1, 90086898], leaf
projectid: 35x, leaf
text(): 35x, 29 distinct integers [1, 20181030], leaf
userUpId: 35x, leaf
text(): 35x, 2 distinct integers [1, 123], leaf
priority: 1x, leaf
text(): 1x, string, leaf
collection: 2x
AA: 2x, leaf
text(): 2x, double [0.021, 0.021], leaf
BB: 2x, leaf
text(): 2x, double [0.1268, 0.1268], leaf
CC: 2x, leaf
text(): 2x, double [0.2257, 0.2257], leaf
A: 2x, leaf
text(): 2x, double [0.0388, 0.0388], leaf
B: 2x, leaf
text(): 2x, double [0.1416, 0.1416], leaf
garantianoadmisible: 2x, leaf
text(): 2x, double [0.6, 0.6], leaf
singarantia: 2x, leaf
text(): 2x, 2 distinct doubles [0.65, 0.75], leaf
entityid: 2x, leaf
text(): 2x, 2 distinct integers [2, 90086898], leaf
colateralfinanciero: 2x, leaf
text(): 2x, double [0.12, 0.12], leaf
info_asset: 181x
entitytid: 181x, leaf
text(): 181x, 4 distinct integers [1, 90086898], leaf
total: 33x, leaf
text(): 33x, 12 distinct integers [13856, 14381], leaf
working: 33x, leaf
text(): 33x, 2 distinct strings, leaf
projectid: 181x, leaf
text(): 181x, 29 distinct integers [1, 20181030], leaf
status: 181x, leaf
text(): 181x, 3 distinct strings, leaf
asset: 450811x
assetid: 450811x, leaf
text(): 450811x, integers [42521, 296380], leaf
lastdatepay: 450811x, leaf
text(): 450811x, strings, leaf
ammount: 450811x, leaf
text(): 450811x, string, leaf
term: 450811x, leaf
text(): 450811x, 93 distinct integers [1, 180], leaf
balance: 450811x, leaf
text(): 450811x, doubles [1, 120901239], leaf
rate: 450811x, leaf
text(): 450811x, 69 distinct doubles [0.3346062573, 1.6], leaf
payment: 450811x, leaf
text(): 450811x, doubles [11760, 7043536], leaf
pendingpayments: 450811x, leaf
text(): 450811x, doubles [0, 146.41], leaf
pagare: 450811x, leaf
text(): 450811x, integers [42521, 296380], leaf
calificacion: 450811x, leaf
text(): 450811x, 5 distinct strings, leaf
plazo: 450811x, leaf
text(): 450811x, 93 distinct integers [1, 180], leaf
linea: 450811x, leaf
text(): 450811x, 12 distinct strings, leaf
destino: 450811x, leaf
text(): 450811x, 51 distinct strings, leaf
tasacolocacion: 450811x, leaf
text(): 450811x, 69 distinct doubles [0.3346062573, 1.6], leaf
capitalinicial: 450811x, leaf
text(): 450811x, integers [17347, 150000000], leaf
saldocapital: 450811x, leaf
text(): 450811x, doubles [1, 120901239], leaf
anualidad: 450811x, leaf
text(): 450811x, integers [11760, 7043536], leaf
cedulasociado: 450811x, leaf
text(): 450811x, strings, leaf
codempresa: 464689x, leaf
text(): 450811x, integers [0, 173767], leaf
garantia: 450811x, leaf
text(): 450811x, 7 distinct integers [1, 11], leaf
nombre_de_la_garantia: 450811x, leaf
text(): 450811x, 7 distinct strings, leaf
diasmora: 450811x, leaf
text(): 450811x, integers [0, 1280], leaf
f_ultimop: 450811x, leaf
text(): 450811x, strings, leaf
nrocuotasfaltantesporcancelar: 450811x, leaf
text(): 450811x, doubles [0, 146.41], leaf
edad: 450811x, leaf
text(): 450808x, 75 distinct doubles [18, 118], leaf
Cobertura: 155142x, leaf
text(): 155142x, 2 distinct integers [0, 1], leaf
entityid: 450811x, leaf
text(): 450811x, 4 distinct integers [1, 90086898], leaf
projectid: 450811x, leaf
text(): 450811x, 26 distinct integers [1, 201809], leaf
diasmoracalculado: 450811x, leaf
text(): 450811x, integers [-40, 2742], leaf
simulation: 450811x
payment: 7939701x
number: 7939701x, leaf
text(): 7939701x, integers [1, 655], leaf
principal: 7939701x, leaf
text(): 7939701x, doubles [-2334, 7019755.662381106], leaf
interest: 7939701x, leaf
text(): 7939701x, doubles [4.048068592674E-5, 1209057.2709579417],
leaf
newbalance: 7939701x, leaf
text(): 7939701x, doubles [0, 1.1950670127095795E8], leaf
total: 7939701x, leaf
text(): 7939701x, doubles [0.0028131304069364, 7043536], leaf
npv: 7939701x, leaf
text(): 7939701x, doubles [0.00265466933451965, 7019849.776618443],
leaf
punishment: 502215x, leaf
text(): 502215x, doubles [0, 3082100.44417925], leaf
punishment: 450811x
policyid: 450811x, leaf
text(): 450811x, 20 distinct strings, leaf
name: 450811x, leaf
text(): 450811x, 18 distinct strings, leaf
priority: 450811x, leaf
text(): 450811x, 19 distinct integers [0, 65], leaf
rate: 450811x, leaf
text(): 450811x, 8 distinct doubles [0, 100], leaf
formula: 450811x, leaf
text(): 72339x, strings, leaf
totalnpv: 450811x, leaf
text(): 450811x, doubles [1, 1.2090123899999978E8], leaf
totalpunishment: 450811x, leaf
text(): 450811x, doubles [0, 9.421980520009069E7], leaf
punish: 450811x, leaf
text(): 450811x, doubles [-61.90886675214506, 3.097031737637267E7], leaf
f_cargoprestamo: 295669x, leaf
text(): 295669x, strings, leaf
ESTADO: 70121x, leaf
text(): 70121x, strings, leaf
Rango_de_Mora: 295669x, leaf
text(): 295669x, 8 distinct strings, leaf
intcorriente: 295669x, leaf
text(): 295669x, doubles [-93720, 4698722], leaf
intcorrientenocontabilizado: 295669x, leaf
text(): 295669x, integers [0, 4698106], leaf
intmora: 295669x, leaf
text(): 295669x, doubles [0, 84202], leaf
intmoranocontabilizado: 295669x, leaf
text(): 295669x, doubles [0, 2857125], leaf
formapago: 295669x, leaf
text(): 295669x, 2 distinct strings, leaf
valorgarantia: 295669x, leaf
text(): 295669x, integers [0, 419847500], leaf
nrocuotascanceladas: 295669x, leaf
text(): 295669x, doubles [0, 179.69], leaf
salario: 295669x, leaf
text(): 295669x, integers [0, 1152688304], leaf
CATEGORIA: 268010x, leaf
text(): 268010x, 3 distinct strings, leaf
FECHA_DE_NACIMIENTO: 295669x, leaf
text(): 295667x, strings, leaf
Estado: 184022x, leaf
text(): 184022x, 2 distinct strings, leaf
_: 55836x, leaf
policy: 88x
policyid: 88x, leaf
text(): 88x, 22 distinct strings, leaf
rate: 88x, leaf
text(): 88x, 8 distinct integers [0, 100], leaf
name: 88x, leaf
text(): 88x, 20 distinct strings, leaf
formula: 88x, leaf
text(): 88x, strings, leaf
entityid: 88x, leaf
text(): 88x, 4 distinct integers [1, 90086898], leaf
priority: 88x, leaf
text(): 88x, 21 distinct integers [10, 67], leaf
Is there anything we could do to reduce the response time? This query is used
in que UI of the application and the user is nagging about having to wait 6
seconds to open a Combo box.
Thanks in advance for all the help,
William David Velásquez
Creativo de Software
Creativos Digitales S.A.S.
Calle 30A # 83 - 53 Local 1033
Tel: 322 1730 - 311 709 8421
Medellín, Colombia
¿Necesita Integrar Sistemas? Conozca nuestra solución de Integración no
Invasiva: http://creativosdigitales.co <http://creativosdigitales.co/>
¿Preguntas sobre Facturación Electrónica? Visite:
http://facturasyrespuestas.com <http://facturasyrespuestas.com/>
Síganos en Twitter <https://twitter.com/FacYRespuestas> @FacyRespuestas
