Hi William,

We have a BaseX database of 3.2 MB size with 146,036,090 nodes on 451,124 documents

Is it 3.2 MB or GB?

Executing a simple query that returns only 4 elements is taking 6 seconds to run.

If you have specific entity documents, you can partition your database and choose dedicated paths:

/entities: entity1.xml entity2.xml ... /assets ...

…and address these documents via the db:open function:

db:open('db', '/entities')/entity

You can as well store these documents in a dedicated 'entity' database:

db:open('entities')/entity

Cheers, Christian

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