1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

  2. Anuncie Aqui ! Entre em contato fdantas@4each.com.br

[SQL] SQL Query performance - UI responsiveness concerns

Discussão em 'Outras Linguagens' iniciado por Stack, Setembro 4, 2021.

  1. Stack

    Stack Membro Participativo

    I am running a query on localhost, I am extremely unfamiliar with SQL. I am using a golang library to generate the query statement. This is for an enterprise app so I don't have time to evaluate and code all possible performance cases. I'd prefer good performance for the largest possible queries:

    • upto 6 query parameters eg. BETWEEN 'created' AND 'abandoned', BETWEEN X AND Y, IN (1,2,3.....25), IN ('A', 'B', 'C'....'Z')
    • JOINs/subqueries between a 2-5 tables
    • returning between 50K-5M records (LAT and LNG)

    Currently I am using JOIN to find the lat, lng for a record, and some query parameters. Should I join differently, (left, right)? Should the FROM table be the record or the relation? Subqueries?

    Is this query performance reasonable from a UI perspective? This is on localhost (docker) on a fairly low performance laptop, under WSL (16GB RAM / 6 core CPU [2.2GHz]).

    -- [2547.438ms] [rows:874731]
    SELECT "Longitude","Latitude"
    FROM Wells
    JOIN Well_Reports ON Well_Reports.Well_ID = Wells.Well_ID
    JOIN Lithologies on Lithologies.Well_Report_ID = Well_Reports.Well_Report_ID
    where Lithologies.Colour IN
    (
    'NULL',
    'Gray','White','Dark','Black','Dark Gray','Dark Brown','Dark Red','Dark Blue',
    'Dark Green','Dark Yellow','Bluish Green','Brownish Gray','Brownish Green','Brownish Yellow',
    'Light','Light Gray','Light Red','Greenish Gray','Light Yellow','Light Green','Light Blue',
    'Light Brown','Blue Gray','Greenish Yellow','Greenish Gray'
    );

    • The UI is a heatmap. I haven't really hit performance issues returning 1-million rows.
    • Angular is the framework. Im breaking the HTTP response into 10K record chunks

    My initial impression was 3+ seconds is too long for a UI to start populating data. I was already breaking the response to the UI into chunks, that portion was efficient and async. It never occurred to me to simply break the SQL requests into smaller chunks with LIMIT and OFFSET, so the server can start responding with data immediately (<200ms) even if it takes +5s to completely finish loading.

    I'll write an answer to this effect.

    Thanks and best regards, schmorrison

    Continue reading...

Compartilhe esta Página