How to model this query

[tupac_rd]

Hi Gurus,

How to model this query in FM. Should I directly use the SQL in the Import View or is there a better way

SELECT
        TB1.*

FROM SCHEMA.TABLE1 TB1

WHERE COL3 = 'A'

AND (COL5 = 'I' OR COL6 IN
(SELECT COL6 FROM 
(SELECT *
FROM SCHEMA.TABLE1 TB1
WHERE
COL5 = 'U'

GROUP BY
COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16,COL17,COL18,COL19,COL20,COL21,COL22,COL23,COL24,COL25,COL26,COL27,COL28,COL29,COL30,COL31,COL32,COL33,COL34,COL35) HISTORY

GROUP BY COL6
HAVING COUNT(COL6) > 1))

Thanks

[blom0344]

I would try to rewrite it to a more efficient statement. There is , for instance, a group by on all minus 1 of all the table columns. Makes no sense at all, but could have serious performance implications.
What are you trying to achieve with this?

[Alp]

Not discussion the query efficiency ...
How about creating a DB view or materialized view saving troubles in FM?

- Alp

[blom0344]

Not discussion the query efficiency ...
How about creating a DB view or materialized view saving troubles in FM?

- Alp

The suggestion to rewrite the query itself would be the first step.  :D A materialized view would be doubtful in terms of winning performance. Creating database objects spreads maintenance over both database and model. I would test 2 scenario's; one with SQL subject and one with imported view. I doubt there i much diff in performance 

[tupac_rd]

Thanks for the replies.

Actually, the SQL was given to me by a Report designer to include it in FM. The SQL is not including the qst 5 columns in Group by clause, since he wants the data to be grouped only from Col 6 onwards.... I will try to get more info from him...

[blom0344]

Perhaps it would be prudent to check up on  'group by'  versus 'order by'
Group by is added to SQL when you have aggregates, order by determines the sorting of data.
I think it is not best practice to add manual group by / order by  in the model (unless you have nested contructions)
The order of data can be influenced from the frontend quite easily. In other words do not inject the model with this type of customized SQL unless you have no other option.
Personally, I think your report writer should take some SQL training  :-\

[Alp]

A few questions:

1. What is size of SCHEMA.TABLE1?
2. How often it gets updated?
3. What is expected size of the result set from the SQL Query?
4. What filtering is expected on the result set from the query?
5. Any expectations on reports response time?

- Alp