Help with optimization for searching a data table for a logical 'AND'

Spacix One

I'm trying to search some data in a table I don't have control over and I need to figure out how to do an logical and between two specified search items. I need to find all rows in a table that has the same value in collumn A and column B is equal to X or Y. The is similar to the following, but it contains more than 600,000 records.

Tablename: data_table

id

record_id

parm_type

value

0

1001

10

1

0

1002

20

2

0

1003

30

3

0

1004

40

4

1

1001

15

5

1

1002

25

6

1

1003

35

7

1

1004

45

8

2

1005

20

9

2

1002

30

10

2

1003

40

11

2

1008

50

12

3

1007

20

13

3

1002

30

14

3

1003

40

15

3

1007

50

16

4

1001

20

17

4

1002

30

18

4

1007

40

19

4

1008

50

I want to get every value with the same record_id, that has a parm_type type of 1002 or 1003 and the value is greater than 20 for type 1002 and less than 40 for 1003. Here is what I tried but it was taking very long and gets execution timeouts

SELECT id,record_id,parm_type,value FROM data_table
WHERE record_id IN
(
SELECT record_id FROM data_table
WHERE parm_type=1002 AND value > 20
)
AND record_id IN
(
SELECT record_id FROM data_table
WHERE parm_type=1003 AND value > 40
);

I know there have to be others ways to accomplish this, but I'm no DBA...

-Spacix All your

N a v a n e e t h

Couple of ideas, 1 - Create a temporary table. Execute each query separately and insert to temporary table. Finally, select the temporary table. Here is it

CREATE TABLE #temp
(
/* your code */
);

insert into #temp
SELECT record_id FROM data_table WHERE parm_type = 1002 AND value > 20

insert into #temp
SELECT record_id FROM data_table WHERE parm_type=1003 AND value > 40

SELECT * FROM #temp

2 - Use UNION[^]

SELECT record_id FROM data_table WHERE parm_type = 1002 AND value > 20
UNION
SELECT record_id FROM data_table WHERE parm_type=1003 AND value > 40

:)

Navaneeth How to use google | Ask smart questions

i j russell

SELECT id,record_id,parm_type,value FROM data_table WHERE (parm_type=1002 AND value > 20) OR (parm_type=1003 AND value > 40);

Spacix One

Maybe I need more clairification (and a better example) Ok so I want to find everything with the same record ID, that has atleast one match for every parm_type and value spesified. What has be suggested so far is a "logical" OR and meaning any of the parm_type and value criteria match. A better example would be one that actually returns data, I apologize for my previous bad example.

SELECT id,record_id,parm_type,value FROM data_table
WHERE record_id IN
(
SELECT record_id FROM data_table
WHERE parm_type=1002 AND value > 20
)
AND record_id IN
(
SELECT record_id FROM data_table
WHERE parm_type=1003 AND value >= 40
);

If I were to run this with the data_table in my previous post I'd expect to be returned would be the following result table:

Result from: data_table

id

record_id

parm_type

value

8

2

1005

20

9

2

1002

30

10

2

1003

40

11

2

1008

50

12

3

1007

20

13

3

1002

30

14

3

1003

40

15

3

1007

50

I hope this could clear things up.

-Spacix All your skynet questions[^] belong to solved

Spacix One

Navaneeth thank you for your reply. I think a UNION statment would merge record_id that would match either of the parm_type requirements. Please correct me if I am wrong!

-Spacix All your skynet questions[^] belong to solved

Spacix One

Thank you for the reply i.j.russell This is acually what I have currently, but this is a logical OR where I'm needing to do a logical AND. I want to a one or more record_id values that contains a "parm_type=1002 AND value > 20" and a "parm_type=1004 AND value > 40." This makes it a bit harder to search :(

-Spacix All your skynet questions[^] belong to solved

David Skelly

I'm a bit unclear what you're looking for here. What do you mean by "every value with the same record_id"? The solutions posted by the others will give you a list of record_ids that have entries in the table for either (type = 1002 and value > 20) OR (type = 1003 and value > 40). The solution that you posted would give you a list of record_ids that have entries in the table for both (type = 1002 and value > 20) AND (type = 1003 and value > 40). These are not the same thing, so which one are you looking for? In terms of your original question, this is probably no help since you do not have control over the table, but one obvious question is: is there an index over parm_type? Without an index over this column, the DBMS is going to end up doing a full table scan which will take a long time on a big table (i.e. it will simply look at every row in the table in turn to see if it matches the query).

Spacix One

David Skelly wrote:

The solution that you posted would give you a list of record_ids that have entries in the table for both (type = 1002 and value > 20) AND (type = 1003 and value > 40).

Is exactly what I'm trying todo, but need a faster method. I don't really have control over the table, but I can force an index on that column since I can run SQL against the whole table. Maybe it'll speed up if I toss an alter table to add an index to the result_id... Yes, yes that just might work! David Skelly you're an evil genius.

-Spacix All your skynet questions[^] belong to solved

Spacix One

I added an index to the record_id indicator with an alter table and now my original query returns in 0.58 seconds (db time) instead of ~400 seconds (db time) Guess this just goes shows me even more how crappy the self proclaimed coder/dba that designed this was, not that I didn't already know he was full of himself by looking at his code ;)

-Spacix All your skynet questions[^] belong to solved