Here's 2 methods that might show a mild improvement - especially in
readability and setting up the script, but any speed improvement will
depend very much on your implementation and Indices (as Simon mentioned).
I'd imagine a covering index would do the best here, but it will be
expensive in space terms if this DB grows very large. (It will basically
double the DB size).
The last one shows the option that would work the very fastest with such
an index, but it is a real pig's meal in code terms.
CREATE TABLE T (M1 INT, M2 INT, M3 INT, M4 INT, M5 INT, M6 INT, M7 INT,
M8 INT, M9 INT, M10 INT);
INSERT INTO T (M1,M2,M3,M4,M5,M6,M7,M8,M9,M10) VALUES
(1, 7,11, 15,0,0,0,0,0,0)
,(3,11,22,100,0,0,0,0,0,0)
,(2,150,0, 0,0,0,0,0,0,0)
,(7,15, 0, 0,0,0,0,0,0,0)
;
WITH P(VSet) AS (
VALUES (0),(1),(2),(7),(15),(150)
)
SELECT *
FROM T
WHERE (M1 IN (SELECT VSet FROM P))
AND (M2 IN (SELECT VSet FROM P) OR M2 IS NULL)
AND (M3 IN (SELECT VSet FROM P) OR M3 IS NULL)
AND (M4 IN (SELECT VSet FROM P) OR M4 IS NULL)
AND (M5 IN (SELECT VSet FROM P) OR M5 IS NULL)
AND (M6 IN (SELECT VSet FROM P) OR M6 IS NULL)
AND (M7 IN (SELECT VSet FROM P) OR M7 IS NULL)
AND (M8 IN (SELECT VSet FROM P) OR M8 IS NULL)
AND (M9 IN (SELECT VSet FROM P) OR M9 IS NULL)
AND (M10 IN (SELECT VSet FROM P) OR M10 IS NULL)
;
-- M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | M9 | M10
-- ------------ | ----- | --- | --- | --- | --- | --- | --- | --- | ---
-- 2 | 150 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
-- 7 | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
WITH P(VSet) AS (
VALUES ('0,1,2,7,15,150')
)
SELECT T.*
FROM T, P
WHERE (instr(VSet,M1))
AND (instr(VSet,M2) > 0 OR M2 IS NULL)
AND (instr(VSet,M3) > 0 OR M3 IS NULL)
AND (instr(VSet,M4) > 0 OR M4 IS NULL)
AND (instr(VSet,M5) > 0 OR M5 IS NULL)
AND (instr(VSet,M6) > 0 OR M6 IS NULL)
AND (instr(VSet,M7) > 0 OR M7 IS NULL)
AND (instr(VSet,M8) > 0 OR M8 IS NULL)
AND (instr(VSet,M9) > 0 OR M9 IS NULL)
AND (instr(VSet,M10) > 0 OR M10 IS NULL)
;
-- M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | M9 | M10
-- ------------ | ----- | --- | --- | --- | --- | --- | --- | --- | ---
-- 2 | 150 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
-- 7 | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
SELECT T.*
FROM T
WHERE (M1 IS NULL OR M1=0 OR M1=1 OR M1=2 OR M1=7 OR M1=15 OR M1=150)
AND (M2 IS NULL OR M2=0 OR M2=1 OR M2=2 OR M2=7 OR M2=15 OR M2=150)
AND (M3 IS NULL OR M3=0 OR M3=1 OR M3=2 OR M3=7 OR M3=15 OR M3=150)
AND (M4 IS NULL OR M4=0 OR M4=1 OR M4=2 OR M4=7 OR M4=15 OR M4=150)
AND (M5 IS NULL OR M5=0 OR M5=1 OR M5=2 OR M5=7 OR M5=15 OR M5=150)
AND (M6 IS NULL OR M6=0 OR M6=1 OR M6=2 OR M6=7 OR M6=15 OR M6=150)
AND (M7 IS NULL OR M7=0 OR M7=1 OR M7=2 OR M7=7 OR M7=15 OR M7=150)
AND (M8 IS NULL OR M8=0 OR M8=1 OR M8=2 OR M8=7 OR M8=15 OR M8=150)
AND (M9 IS NULL OR M9=0 OR M9=1 OR M9=2 OR M9=7 OR M9=15 OR M9=150)
AND (M10 IS NULL OR M10=0 OR M10=1 OR M10=2 OR M10=7 OR M10=15 OR
M10=150)
;
-- M1 | M2 | M3 | M4 | M5 | M6 | M7 | M8 | M9 | M10
-- ------------ | ----- | --- | --- | --- | --- | --- | --- | --- | ---
-- 2 | 150 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
-- 7 | 15 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
On 2016/08/12 6:01 PM, Chris Depetris wrote:
We have a table that has 10 fields that are used as in query as a key. The
fields in question are M1-M10.
M1-M10 will contain an integer of 0-5000 (may be higher). Also the values in
fields M1-M10 are always unique within the record and are usually ascending
(we can mandate this if it will help). An exception to this is the value of
0 or NULL which means the field is not used and if a field is 0 all higher
fields will be 0 ex: M4 = 0 then M5-10 will also be 0 (sometimes this can be
null, but we can eliminate the nulls) and of course M1 will never be 0.
There is no guarantee that 2 or more records will not have the same values
for fields M1-M10. So an example of fields from those records would be.
1,7,11,15,0,0, 0, 0, 0, 0
3,11,22,100,0,0,0,0,0,0,0
2,150,0,0,0,0,0,0,0
7,15,0,0,0,0,0,0,0,0
We now need to query these to find all records that have only
values from within a specific set. The current query we use which does work
is similar to the following.
Select * FROM TABLE where ((M1 IN (0,
1,2,7,15,150)) AND (M2 IN (0, 1,2,7,15,150) Or M2 IS NULL )
AND (M2 IN (0, 1,2,7,15,150) Or M2 IS NULL) AND (M3 IN (0, 1,2,7,15,150) Or
M3 IS NULL)
AND (M4 IN (0, 1,2,7,15,150) Or M4 IS NULL) AND (M5 IN (0, 1,2,7,15,150) Or
M5 IS NULL)
AND (M6 IN (0, 1,2,7,15,150) Or M6 IS NULL) AND (M7 IN (0, 1,2,7,15,150) Or
M7 IS NULL)
AND (M8 IN (0, 1,2,7,15,150) Or M8 IS NULL) AND (M9 IN (0, 1,2,7,15,150) Or
M9 IS NULL)
AND (M10 IN (0, 1,2,7,15,150) Or M10 IS NULL))
This query works and has reasonable performance right now for
us, but I feel like there should be a more efficient way to do this.
Thanks
Chris
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
