CODEWITHSUNDEEP
sqliteperformance
Lokked
Lokked

Reputation: 63

SQLite - Help to Make Complicated Query More Efficient

The data is Financial data, structured in buckets, where one bucket (Rollup) can contain other buckets of data. As Example structure:

Rollup1 | Dept1
Rollup1 | Rollup2 | Dept2
Rollup1 | Rollup2 | Dept3
Rollup1 | Rollup3 | Dept4
Rollup1 | Rollup3 | Rollup4 | Dept5
Rollup1 | Rollup3 | Rollup4 | Dept6

There are 8 Columns of this, with Rollups and Depts scattered throughout (but the leaves are always single Depts). Approx 10k rows.

The goal of the Query Result is to show a single column with ALL Rollups, with variable logic to present certain Rollups normally, and modifying all other Rollups.

For example, if my variable contained "Dept4", my result would be:

Rollup1
Rollup3
NA - Rollup2
NA - Rollup4

In the real scenario, there are 3 variables which determine the display of the Rollup column.

Here is what I have, which functions as it should, however, the performance is VERY bad. 1 Query takes up to 5 seconds, which I would like to improve.

SELECT DISTINCT CASE
   WHEN "2" NOT IN
      (
         SELECT "2"
         FROM "Finance New"
         WHERE (@VAR3 = 'All' OR @VAR3 IN ("2","3","4","5","6","7","8","9"))
         AND (@VAR4 = 'All' OR "10" = @VAR4)
         AND (@VAR5 = 'All' OR "11" = @VAR5)
         )
   THEN
      'Z N/A - ' || "2"
   ELSE
      "2"
   END AS COL2
FROM "Finance New"
WHERE "5" <> 'All Applicable' AND "1" <> '9999'
AND "2" LIKE '9%'

UNION

SELECT DISTINCT CASE
   WHEN "3" NOT IN
      (
         SELECT "3"
         FROM "Finance New"
         WHERE (@VAR3 = 'All' OR @VAR3 IN ("2","3","4","5","6","7","8","9"))
         AND (@VAR4 = 'All' OR "10" = @VAR4)
         AND (@VAR5 = 'All' OR "11" = @VAR5)
         )
   THEN
      'Z N/A - ' || "3"
   ELSE
      "3"
   END AS COL2
FROM "Finance New"
WHERE "5" <> 'All Applicable' AND "1" <> '9999'
AND "3" LIKE '9%'
UNION

Etc, for each of the columns in the Rollup/Dept Tree report.

The inner select in each union query appends to the text based on the variable criteria. Sorting is done automatically. The last line before UNION (AND "3" LIKE "9%") is to actually grab the Rollup. Rollups all start with 9.

Input parameters are labeled like @VARx.

I'm wondering if there is a more efficient way of performing this, assuming I cannot create a temp table and cannot change the structure of the data.

Thank you!

Upvotes: 0

Views: 103

Answers (1)

CL.
CL.

Reputation: 180270

  1. All these ORs prevent the use of indexes. If at all possible, remove those @VAR = 'All' comparisons (or the other one) and create the SQL string dynamically, depending on the actual VAR3/4/5 values.
  2. The LIKE prevents the use of indexes (because LIKE (or GLOB) would require TEXT affinity on the indexed column). Replace this with normal comparisons, i.e., replace "col" LIKE '9%' with "col" >= '9' AND "col" < ':'.
  3. The UNION already removes duplicates; drop the DISTINCTs.
  4. Without indexes, all these queries do full table scans. Create the following (covering) indexes:
    CREATE INDEX i_10_11_all on "Finance New"("10","11", "2","3","4","5","6","7","8","9");
    CREATE INDEX i_11_10_all on "Finance New"("11","10", "2","3","4","5","6","7","8","9");
    CREATE INDEX i_2_1_5 on "Finance New"("2", "1","5");
    CREATE INDEX i_3_1_5 on "Finance New"("3", "1","5");
    -- and so on for 4..9

Upvotes: 1

Related Questions