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
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?
Not discussion the query efficiency ...
How about creating a DB view or materialized view saving troubles in FM?
- Alp
Quote from: Alp on 14 Jul 2010 10:32:33 PM
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
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...
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 :-\
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