Exporting SQL query trouble shooting

Question

I have written a query, and able to run it successfully. When I try to export it, its exporting speed is very slow i.e 50 rows per 10-15 mins and I managed to export it around 50K data in 10 hours. Kindly help me if there is any problem with this query. I'm using sql developer

Thank you

My desired result is column-wise :

|GLUSR_USR_CUSTTYPE_NAME|GLUSR_USR_ID|S_COUNT|MAX_DATE as Last Meeting|Min_DATE as first Meeting|Total Meeting in Last 90 Days|Total Meeting in last 180 days| active in last 30 Days| active in last 60 Days| active in last 90 Days|

sql:

Select GLUSR_USR_ID,
    GLUSR_USR_CUSTTYPE_NAME,
    ( select  Count(1)                   from STS_DSR_SALES@mainr,STS_Company@mainr where STS_DSR_SALES.FK_STS_COMPANY_ID=CompanyID and GLUSR_USR_ID=STS_FK_GLUSR_ID)S_COUNT,
    (Select MAX(STS_DSR_SALES_DATE)      from STS_DSR_SALES@mainr,STS_Company@mainr where STS_DSR_SALES.FK_STS_COMPANY_ID=COMPANYID)MAX_DATE,
    (Select min(STS_DSR_SALES_DATE)      from STS_DSR_SALES@mainr,STS_Company@mainr where STS_DSR_SALES.FK_STS_COMPANY_ID=COMPANYID)Min_DATE,
    (Select count (1) STS_DSR_SALES_DATE from STS_DSR_SALES@mainr,STS_COMPANY@mainr where STS_DSR_SALES.FK_STS_COMPANY_ID= COMPANYID and GLUSR_USR_ID=STS_FK_GLUSR_ID and trunc(STS_DSR_SALES_DATE)>SYSDATE-90)last_90,
    (Select count (1) STS_DSR_SALES_DATE from STS_DSR_SALES@mainr,STS_COMPANY@mainr where STS_DSR_SALES.FK_STS_COMPANY_ID= COMPANYID and GLUSR_USR_ID=STS_FK_GLUSR_ID and trunc(STS_DSR_SALES_DATE)>SYSDATE-180)last_180,
    (Select Count (1) STS_DSR_SALES_DATE from STS_DSR_SALES@mainr,GLUSR_CLCKSTRM_4HOTLEAD_ARCH@mainr where GLUSR_USR_ID=FK_GLUSR_USR_ID and STS_DSR_SALES_DATE=Report_DATE and TRUNC(REPORT_DATE)>SYSDATE-30)ACTIVE_30,
    (Select Count (1) STS_DSR_SALES_DATE from STS_DSR_SALES@mainr,GLUSR_CLCKSTRM_4HOTLEAD_ARCH@mainr where GLUSR_USR_ID=FK_GLUSR_USR_ID and STS_DSR_SALES_DATE=Report_DATE and TRUNC(REPORT_DATE)>SYSDATE-60)ACTIVE_60,
    (Select Count (1) STS_DSR_SALES_DATE from STS_DSR_SALES@mainr,GLUSR_CLCKSTRM_4HOTLEAD_ARCH@mainr where GLUSR_USR_ID=FK_GLUSR_USR_ID and STS_DSR_SALES_DATE=Report_DATE and TRUNC(REPORT_DATE)>SYSDATE-90)ACTIVE_90
 From
    (
        select GLUSR_USR_ID, fk_sts_company_id,GLUSR_USR_CUSTTYPE_NAME 
        from STS_DSR_SALES@mainr,GLUSR_USR@mainr,STS_COMPANY@MAINR         
        where STS_FK_GLUSR_ID=GLUSR_USR_ID
        and fk_sts_company_id=CompanyID
        and    trunc(sts_dsr_sales_date) between '01-oCT-19' and '31-oCT-19'
    )

Those expected results are unreadable and you have tagged 2 completely differe RDBMS. Only tag what you're actually using. — Thom A, Dec 22 '19 at 15:33
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) — Thom A, Dec 22 '19 at 15:33
Unfortunately there's not nearly information specific information here for anybody to offer anything except a guess. Database tuning is **all about the details**. Please read [this answer on asking questions about Oracle query optimization](https://stackoverflow.com/a/34975420/146325), then edit your question to provide more information. — APC, Dec 22 '19 at 21:49
You are using an ancient join syntax (old comma-separated joins instead of explicite inner joins). Moreover, we don't know which columns belong to which tables. Please qualify each column with its table. (e.g. is it `sts_dsr_sales.companyid` or `glusr_usr.companyid` or `sts_company.companyid`?) Then, please explain your tables. What do they contain, which are their keys? — Thorsten Kettner, Dec 22 '19 at 22:04

score 0 · Accepted Answer · answered Dec 22 '19 at 15:54

It's pretty difficult to get the whole picture, because you're referring to columns without table names / aliases and relying that those exact names only appear in one of the tables, and also you're selecting from a select clause.

Your query seems to have a lot of repeating parts with min/max or different date ranges. I'm quite sure those can be combined into a smaller number of queries using case statement, something like

sum(case when TRUNC(REPORT_DATE)>SYSDATE-30 then 1 end),
sum(case when TRUNC(REPORT_DATE)>SYSDATE-60 then 1 end),
sum(case when TRUNC(REPORT_DATE)>SYSDATE-90 then 1 end),

So you'll get rid of some of the queries.

Don't remember anymore if this was a bad thing (or only in SQL Server) and it's been over a decade since I last worked with Oracle, so things might have also changed :)

But you might want to test replacing this:

trunc(sts_dsr_sales_date) between '01-oCT-19' and '31-oCT-19'

with something like this so you'll get rid of the function:

sts_dsr_sales_date >= '01-OCT-19' and sts_dsr_sales_date < '1-NOV-19'

never rely on implicit data type conversions. And why, 20 years after Y2k, are we still using 2-digit years. Should be 'sts_dsr_sales_date >= to_date('01-OCT-2019','dd-MON-yyyy') and sts_dsr_sales_date < to_date('01-NOV-2019','dd-MON-yyyy'); — EdStevens, Dec 22 '19 at 18:52
I agree with EdStevens, but you should use date literals anyway: `sts_dsr_sales_date >= date '2019-10-01' and sts_dsr_sales_date < date '2019-11-01'`. — Thorsten Kettner, Dec 22 '19 at 22:07
Exactly, the syntax for date literals in Oracle SQL is (for example) `date '2019-11-01'` as [the documentation](https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#BABGIGCJ) has made clear for years (link is to 10g docs to show it's not new - this was actually added back in 9i). It always surprises me how often even experienced developers get it wrong. — William Robertson, Dec 23 '19 at 12:41

score 0 · Answer 2 · edited Dec 23 '19 at 03:27

You're accessing the tables too many times for getting the answer/results. Try framing the inline query with a WITH() clause and building up the data pipeline incrementally one piece at a time.

With Q1 as ( 
inline_query on 2 tables STS_DSR_SALES and STS_Company which defines the overall scope of your query/ask 
),
Q2 as (
select ... <All/90/180 day aggregates defined via sum(case statements)>
from Q1 inner join <other_table T3 GLUSR_USR>
where ... (keys)
),
Q3 as (
select ... <All/30/60/90 day aggregates defined via sum(case statements)>
from Q1 inner join <other_table T4 GLUSR_CLCKSTRM_4HOTLEAD_ARCH>
where ... (keys)
)
--final result
select * from (
select * from Q2 inner join Q3 on ...
)
pivot ( 
-- convert rows to cols ... get your KPIs to line up as columns 
);

Exporting SQL query trouble shooting

2 Answers2