Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Oracle Client
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.....
-------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
Edited 3-Oct-12 20:15pm
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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:
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>
  Permalink  
v2
Comments
@AmitGajjar at 5-Oct-12 22:10pm
   
correct 5+

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 350
1 Gihan Liyanage 332
2 OriginalGriff 236
3 ClimerChinna 222
4 vikinghunter 168
0 Sergey Alexandrovich Kryukov 8,298
1 OriginalGriff 7,112
2 CPallini 2,598
3 Richard MacCutchan 2,005
4 Abhinav S 1,788


Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 5 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100