why there is performance difference when retrieving data from view vs underlying select of that view

Question

I am doing query on view with single predicates which gives me the record in 4-7 seconds, but when i try to retrieve the record with same predicate and directly with underlying query from that view it gives me records in less then seconds. I am using MySQL.

I have tried checking the execution plan of both the query and it gives major differences if i have hundreds of thousands of records in tables.

So any clue or idea why performance is better when using query directly?

Following is my view definition

 SELECT    entity_info.source_entity_info_id      AS event_sync_id, 
          entity_info.source_system_id           AS source_system_id, 
          entity_info.target_system_id           AS destination_system_id, 
          event_sync_info.integrationid          AS integration_id, 
          event_sync_info.source_update_time     AS last_updated, 
          entity_info.source_internal_id         AS source_entity_internal_id, 
          entity_info.source_entity_project      AS source_entity_project, 
          entity_info.target_internal_id         AS destination_entity_internal_id, 
          entity_info.destination_entity_project AS destination_entity_project, 
          entity_info.source_entity_type         AS source_entity_type, 
          entity_info.destination_entity_type    AS destination_entity_type, 
          event_sync_info.opshub_update_time     AS opshub_update_time, 
          event_sync_info.entity_info_id         AS entity_info_id, 
          entity_info.global_id                  AS global_id, 
          entity_info.target_entity_info_id      AS target_entity_info_id, 
          entity_info.source_entity_info_id      AS source_entity_info_id, 
          ( 
                 SELECT Count(0) AS count(*) 
                 FROM   ohrv_failed_event_view_count failed_event_view 
                 WHERE  (( 
                                      failed_event_view.integration_id = event_sync_info.integrationid)
                        AND    ( 
                                      failed_event_view.entityinfo = entity_info.source_entity_info_id))) AS no_of_failures
FROM      (ohrv_entity_info entity_info 
LEFT JOIN ohmt_eai_event_sync_info event_sync_info 
ON        (( 
                              entity_info.source_entity_info_id = event_sync_info.entity_info_id)))
WHERE     ( 
                    entity_info.source_entity_info_id IS NOT NULL)

Query examples

select * from view where integration_id=10

Execution plan of this processes 142668 rows for sub query that is there in this view
select QUERY_OF_VIEW and integration_id=10

Execution plan of this looks good and only required rows are getting processed.

score 1 · Answer 1 · answered Dec 27 '18 at 07:18

1

I think the issue is in the following query:

SELECT * FROM view WHERE integration_id = 10;

This forces MySQL to materialize an intermediate table, against which it then has to query again to apply the restriction in the WHERE clause. On the other hand, in the second version:

SELECT (QUERY_OF_VIEW with WHERE integration_id = 10)

MySQL does not have to materialize anything other than the query in the view itself. That is, in your second version MySQL just has to execute the query in the view, without any subsequent subquery.

answered Dec 27 '18 at 07:18

Tim Biegeleisen

502,043
27
286
360

Thanks So what are the alternatives for this , as the view constructed is very complex. I need to have the pagination also when retrieving the result. I tried to use the function and pass parameter to the underlying definition of view, but MySQL is having limitation for function that it can not return table. thread link: https://stackoverflow.com/questions/23421771/how-to-return-table-from-mysql-function. Whatever way i chose for solving this issue need to work with Oracle, MSSQL and HSQL also. – Viral Kondhia Jan 02 '19 at 06:40
If the view doesn't fit your needs, then you should change the view, or maybe create a new one. Views are cheap in most versions of SQL, assuming they are not materialized. – Tim Biegeleisen Jan 02 '19 at 06:55

score 0 · Answer 2 · answered Dec 27 '18 at 07:33

refereeing to this link of documentation you can see,that its depend on if the MERGE algorithm can used it will , but if its not applicable so new temp table must generated to find the relations of data, also you can see this answer that talking about optimization and when to use view and when you should not .

If the MERGE algorithm cannot be used, a temporary table must be used instead. MERGE cannot be used if the view contains any of the following constructs:

Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

DISTINCT

GROUP BY

HAVING

LIMIT

UNION or UNION ALL

Subquery in the select list

Refers only to literal values (in this case, there is no underlying table)

why there is performance difference when retrieving data from view vs underlying select of that view

2 Answers2