Manage big Dataset queries

Hey guys.

I have two big datasets (more than 80.000 items). Im trying to perfome some queries and it works when the dataset is like 20.000 items but it crashes when its bigger. The message in the java console says: Uncaught (in promise) Error: WDE0028: Operation time limit exceeded.

Here is part of the code:

import wixData from 'wix-data';

    $w("#table1").columns = [
        { "id": "Diagnóstico", "dataPath": "Diagnóstico", "label": "Diagnóstico", "type": "string" },
        { "id": "Porcentaje", "dataPath": "Porcentaje", "label": "Porcentaje", "type": "string" }
    ];

   // Establecer la estructura de columnas en la tabla
        const columns = [
            { "id": "Ciudad", "dataPath": "Diagnóstico", "label": "Ciudad", "type": "string" },
            { "id": "Porcentaje", "dataPath": "Porcentaje", "label": "Porcentaje", "type": "string" }
        ];
        $w("#tablaCiudad").columns = columns;

        const columnsProfesion = [
            { "id": "Profesion", "dataPath": "Diagnóstico", "label": "Profesión", "type": "string" },
            { "id": "Porcentaje", "dataPath": "Porcentaje", "label": "Porcentaje", "type": "string" }
        ];
        $w("#profesionTable").columns = columnsProfesion;

           // Establecer la estructura de columnas en la tabla
    const columnsEncuesta = [
        { "id": "Item", "dataPath": "Item", "label": "Item", "type": "string" },
        { "id": "Porcentaje", "dataPath": "Porcentaje", "label": "Porcentaje", "type": "string" }
    ];
    $w('#encuestaTable').columns = columnsEncuesta;
    

async function countRecords() {
    let startDate = $w('#datePicker1').value;
    let endDate = $w('#datePicker2').value;

    let yearValue = startDate.getFullYear();
    let monthValue = startDate.getMonth();
    let dayValue = startDate.getDate();
    let startDateSrch = new Date(yearValue, monthValue, dayValue, 0, 0, 0);

    yearValue = endDate.getFullYear();
    monthValue = endDate.getMonth();
    dayValue = endDate.getDate();
    let endDateSrch = new Date(yearValue, monthValue, dayValue, 23, 59, 59);

    const MAX_ITEMS_PER_QUERY = 1000;

    async function fetchAllRecords(query) {
        let records = [];
        let skip = 0;
        let hasMoreRecords = true;

        while (hasMoreRecords) {
            let response = await query
                .limit(MAX_ITEMS_PER_QUERY)
                .skip(skip)
                .find();

            records = records.concat(response.items);

            if (response.items.length < MAX_ITEMS_PER_QUERY) {
                hasMoreRecords = false;
            } else {
                skip += MAX_ITEMS_PER_QUERY;
            }
        }

        return records;
    }

    let hcCollection = await fetchAllRecords(
        wixData.query("BACKUPHC")
        .between("fechaConsulta", startDateSrch, endDateSrch)
        .eq("atendido", "ATENDIDO")
        .hasSome("codEmpresa", "PARTICULAR")
    );

    let numeroIds = hcCollection.map(item => item.numeroId);
    let uniqueNumeroIds = [...new Set(numeroIds)];

    console.log("LOS QUE SON", uniqueNumeroIds);

    // GÉNERO
    $w('#generoBtn').onClick(async (event) => {
        console.log("si");

        let formCollection = await fetchAllRecords(
            wixData.query("FORMULARIOBK").hasSome("documentoIdentidad", uniqueNumeroIds)
        );

        let matchingForms = formCollection.filter(form => uniqueNumeroIds.includes(form.documentoIdentidad));
        let generosCoincidentes = matchingForms.map(form => `${form.documentoIdentidad} - ${form._createdDate.toLocaleDateString()}`);

        let formMaleCount = formCollection.filter(form => form.genero === "MASCULINO").length;
        let formFemaleCount = formCollection.filter(form => form.genero === "FEMENINO").length;

        let numFormRecords = formCollection.length;
        $w("#numFormRecords").text = numFormRecords.toString();

        let numHCRecords = hcCollection.length;
        $w("#numHCRecords").text = numHCRecords.toString();

        let numMaleRecords = formMaleCount;
        $w("#numMaleRecords").text = numMaleRecords.toString();

        let numFemaleRecords = formFemaleCount;
        $w("#numFemaleRecords").text = numFemaleRecords.toString();

        let numMatchingRecords = matchingForms.length;
        $w("#numMatchingRecords").text = numMatchingRecords.toString();

        let malePercentage = (numMaleRecords / numMatchingRecords) * 100;
        $w("#porcentajeMasculino").text = malePercentage.toFixed(2).toString() + "%";

        let femalePercentage = (numFemaleRecords / numMatchingRecords) * 100;
        $w("#porcentajeFemenino").text = femalePercentage.toFixed(2).toString() + "%";
    });

Help!

Why do you need to load all the items ? Usually, this is not something that a page would include.
You can display either one page at a time or load more when the user requests or let the user define a filter to load up to 100 items.

Optimize your code: Review your code for any inefficiencies that might be causing the performance problem. Look for areas where you can optimize loops, reduce redundant calculations, or minimize unnecessary data fetching. MyAARPMedicare benefits Limit the number of items fetched: In your code, you’re using MAX_ITEMS_PER_QUERY to limit the number of items fetched per query. You could try reducing this number further to see if it helps improve the performance. However, be cautious not to set it too low, as it may lead to incomplete or inaccurate results. Use pagination: Instead of fetching all the records at once, consider implementing pagination. Fetch a subset of records at a time and load more as needed. This approach can help distribute the workload and prevent timeout issues. Split the workload: If possible, split the workload across multiple queries or operations. For example, you could divide the dataset into smaller chunks based on certain criteria (e.g., date ranges, alphabetical ranges, etc.) and process each chunk separately. Utilize backend processing: If the operations you’re performing can be offloaded to backend code, consider using Wix Backend functions. Backend code typically has higher resource limits, which can handle more extensive processing tasks. Consider database optimization: Depending on the nature of your queries, you might want to evaluate the structure of your database and consider adding indexes to improve query performance. This step might require assistance from a database expert or the Wix support team. Contact Wix support: If you’ve exhausted all the above options and are still facing performance issues, it’s best to reach out to Wix support directly. They can provide more specific guidance and assistance based on your Wix setup and configuration.

I understand why the post-opener needs the whole data to be loaded first, for example in the usecase, when he needs to fill-up a Dropdown with options from database. In this case, he will need to scan whole data, to prepare his dropdown completely and not just loading a part of data to the dropdown.