Simple query hangs forever on large table

Question

I'm trying to crosswalk some code values from another developer's code using the business objects frontend (I know, it's sub-optimal, but they haven't given me back-end access).

What I need to do is just pull a record from the relevant table to compare code values to display values. I'm guessing the problem has something to do with the table containing millions of records. Even when I narrow my query to one value, try only records from today, and set Max rows retrieved to 1, it's hanging forever.

The code it generated for my query is:

SELECT
  CLINICAL_EVENT.EVENT_CD,
  CV_EVENT.DISPLAY
FROM
  CLINICAL_EVENT,
  CODE_VALUE  CV_EVENT
WHERE
  ( CLINICAL_EVENT.EVENT_CD=CV_EVENT.CODE_VALUE  )
  AND  
  (
   CLINICAL_EVENT.EVENT_CD  =  338743225
   AND
   CLINICAL_EVENT.EVENT_END_DT_TM
  >  '16-02-2017 00:00:00'
  )

The code values and corresponding display values are effectively both coming from table `CODE_VALUE`. The only thing you're gaining from the join is duplication of those results according to the number of times the code value appears on the `CLINICAL_EVENT` rows satisfying the date criterion. Why don't you just select the wanted record directly from `CODE_VALUE`, and eliminate table `CLINICAL_EVENT` from the query altogether? — John Bollinger, Feb 16 '17 at 22:20
The join itself could be reducing data as well. so perhaps there are records in one table not in the other that need to be excluded from the results; thus the reason the join is there. (though could be handled though an exists which may be faster) — xQbert, Feb 16 '17 at 22:34
In the BO universe are those two objects actually physical tables or are they derived tables which come with their own overhead? — xQbert, Feb 16 '17 at 22:39
@JohnBollinger Removing the `CLINICAL_EVENT` table as you suggested did the trick. If you want to add that as the answer I'll make it the accepted one :-) — Rominus, Feb 17 '17 at 16:10

score 0 · Answer 1 · answered Feb 16 '17 at 22:14

0

Can you by chance avoid the cross join in your query by using a join syntax instead of the , notation? perhaps the engine is optimizing to avoid the cross join, perhaps not.

SELECT
  CLINICAL_EVENT.EVENT_CD,
  CV_EVENT.DISPLAY
FROM
  CLINICAL_EVENT
INNER JOIN CODE_VALUE  CV_EVENT
 on  CLINICAL_EVENT.EVENT_CD=CV_EVENT.CODE_VALUE
WHERE CLINICAL_EVENT.EVENT_CD  =  338743225 
  AND CLINICAL_EVENT.EVENT_END_DT_TM  >  '16-02-2017 00:00:00'

Additionally what data type is EVENT_END_DT_TM perhaps implicitly casting your '16-02-2017 00:00:00' to a date or datetime would aid performance.

answered Feb 16 '17 at 22:14

xQbert

34,733
2
41
62

It would be surprising if the database does not treat the OP's query exactly the same way as your suggested alternative. In any event, do not overlook that the OP does not have direct access to the DB. The query he presents is software-generated, and it seems unlikely that he has sufficient control over what is sent to the DB to implement your suggestion. – John Bollinger Feb 16 '17 at 22:29
In BO, you can always go to the SQL view of the report, uncheck the auto generate SQL and modify the SQL (provided you have the permissions to do so) – xQbert Feb 16 '17 at 22:40

score 0 · Answer 2 · answered Feb 17 '17 at 16:47

Expanding a bit on my comment:

The code values and corresponding display values you want to examine are effectively both coming from table CODE_VALUE. The only thing you're gaining from the join is duplication of those results according to the number of times the code value appears on the CLINICAL_EVENT rows satisfying the date criterion (in a sense that encompasses suppressing all appearances if there are no matching rows).

You seem to want simply to compare the code value and corresponding description, rather than to evaluate how many times that code appears. In that case, you are incurring a lot of unneeded work -- and possibly even some unwanted work -- by joining CODE_VALUE to CLINICAL_EVENT. Instead, just select the wanted row(s) directly from CODE_VALUE alone.

Simple query hangs forever on large table

2 Answers2