BaseX query performance

> 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 > > > > ¿Preguntas sobre Facturación Electrónica? Visite: http://facturasyrespuestas.com > > Síganos en Twitter @FacyRespuestas > > > >