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

¿Preguntas sobre Facturación Electrónica? Visite: http://facturasyrespuestas.com

Síganos en Twitter @FacyRespuestas