28 Jan
2019
28 Jan
'19
6:48 p.m.
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, leafuser: 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], leafproject: 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, leafcollection: 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], leafinfo_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, leafasset: 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], leafbalance: 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, leafpolicy: 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], leafIs 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