Performance enhancement of oracle client query

Question

0.00/5 (No votes)

See more:

Hi,
I am new to oracle client and have assigned a task to enhance the performance of the following query as it is taking too much time to fetch the records on SSRS reporting side. Can anybody help on this.

Thanks in advance.....

SQL

-------BEGIN 2D
SELECT a.id ID, a.name NAME,'222' TYPE, 1 AS ORD, 'Navigation' CATEGORY,
(SELECT Decode (Count(*),1,0,Count(*)) from files_v WHERE sur_id = a.id AND sur_type = '222' AND data_type = 'Navigation') total,
NULL  DOCTYPE
FROM
sur_222 a
left join dataset_222_n b ON b.sur_222_id=a.id AND b.data_type='Navigation' 
WHERE
a.id IN  (:ID)
GROUP BY a.id, a.name
-------END 2D
UNION ALL
------BEGIN 3D
SELECT a.id ID, a.name NAME, '333' TYPE, 1 AS ORD, 'Navigation' CATEGORY,
(SELECT Decode (Count(*),1,0,Count(*)) FROM files_v WHERE sur_id = a.id AND sur_type = '333' AND data_type = 'Navigation') total,
NULL  DOCTYPE  
FROM
sur_333 a
left join dataset_333_n b ON b.sur_333_id=a.id AND b.data_type='Navigation'
WHERE
a.id IN  (:ID)
GROUP BY a.id, a.name
------END 3D

Posted 3-Oct-12 20:09pm

Member 9482652

Updated 3-Oct-12 20:15pm

Kenneth Haugland

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Answer 1 · 2012-10-05T10:49:00

SQL

Start with removing the left joins.
You're not using any fields from the joined tables (b) and as the joins are being of the left kind, means that no rows are sorted away from the first table (a) in the joins.

And, I'm getting the impression that the database isn't normalized, or are the tables actually views?

<edit>I've made some assumptions, try this:

SQL

WITH tab AS (
    SELECT  a.id ID
           ,a.name NAME
           ,'222' TYPE
           ,1 AS ORD
           ,'Navigation' CATEGORY
           ,NULL  DOCTYPE
    FROM    sur_222 a
    WHERE   a.id IN  (:ID)
    -------END 2D
    UNION ALL
    ------BEGIN 3D
    SELECT  a.id ID
           ,a.name NAME
           ,'333' TYPE
           ,1 AS ORD
           ,'Navigation' CATEGORY
           ,NULL  DOCTYPE  
    FROM    sur_333 a
    WHERE   a.id IN  (:ID)
    )
SELECT  ID
       ,NAME
       ,TYPE
       ,ORD
       ,CATEGORY
       ,Decode (Count(*),1,0,Count(*)) total
       ,DOCTYPE
FROM    tab a, files_v v
WHERE   sur_id = a.id
AND     sur_type = TYPE
AND     data_type = 'Navigation'
GROUP BY ID
        ,NAME
        ,TYPE
        ,ORD
        ,CATEGORY
        ,DOCTYPE

</edit>