Home » SQL & PL/SQL » SQL & PL/SQL » sql Query Taking To much time to be execute (merged 4)
icon11.gif  sql Query Taking To much time to be execute (merged 4) [message #419034] Wed, 19 August 2009 11:19 Go to next message
hemss007
Messages: 21
Registered: June 2008
Location: LONDON
Junior Member
Hello There!
I am Thanks to orafaq , to having such big oracle pool.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Can I get some solution to Improve my this query.
Or how can i reduce my query time if i am using functions in it ?

++++++++++++++++++++++++++++++++++++++++++++++++
Running with Functions : ITs Take 160 seconds
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select SELECT
ACC.first_name as "First Name",

ACC.last_name as "Last Name",

ACC.company as "Company Name",

ACC.email_address as "Email",

ACC.address1 as "Address 1",

ACC.address2 as "Address 2",

ACC.address3 as "Address 3",

ACC.city as "City",

ACC.postcode as "Postcode",

COUNTRY."Name" as "Country" ,

ACC.telephone as "Telephone",

ACC.extension as "Extension",

ACC.mobile as "Mobile" ,

ACC.job_title as "Job Title" ,

ACC.date_created as "Registered",

accountbenchmarkqas_concat(ACC.id) as "Benchmark Q and A s",

accountresdownloads_concat(ACC.id) as "Resource Downloads" ,

accountfiledownloads_concat(ACC.id) as "File Downloads",

accountvideosstarted_concat(ACC.id) as "Videos Started" ,

accountvideosfinished_concat(ACC.id) as "Videos Finished",

accounttags_concat(ACC.id) as "Tags",

CASE WHEN ACC.Opt_In = 1 THEN 'Opted In' ELSE 'Opted out' END as "Opt In/Out"

from account ACC

join country on country.id= acc.country_id

where upper(ACC.company) not like '%xxxxxxxx%'

and upper(ACC.company) not like '%xxxxxxxx%'

and upper(ACC.email_address) not like '%xxxxxxxx%'

and upper(ACC.email_address) not like '%xxxxxxxx%'

order by ACC.date_created




Its take around 160 second to finish in ORACLE SQL DEVELOPER AND 4 Minute to generate report in Microsoft sql reporting service.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Running without Functions : ITs Take only 4 seconds
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
select --ACC.id,
ACC.first_name as "First Name",
ACC.last_name as "Last Name",
ACC.company as "Company Name",
ACC.email_address as "Email",
ACC.address1 as "Address 1",
ACC.address2 as "Address 2",
ACC.address3 as "Address 3",
ACC.city as "City",
ACC.postcode as "Postcode",
COUNTRY."Name" as "Country" ,
ACC.telephone as "Telephone",
ACC.extension as "Extension",
ACC.mobile as "Mobile" ,
ACC.job_title as "Job Title" ,
ACC.date_created as "Registered" ,
CASE WHEN ACC.Opt_In = 1 THEN 'Opted In' ELSE 'Opted out' END as "Opt In/Out"
from account ACC
join country on country.id= acc.country_id
where
upper(ACC.company) not like '%xxxxxxxx%'

and upper(ACC.company) not like '%xxxxxxxx%'

and upper(ACC.email_address) not like '%xxxxxxxx%'

and upper(ACC.email_address) not like '%xxxxxxxx%'

order by ACC.date_created

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Running only Functions : ITs Take only 100 seconds
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select

accountbenchmarkqas_concat(ACC.id) as "Benchmark Q and A s",
accountresdownloads_concat(ACC.id) as "Resource Downloads" ,
accountfiledownloads_concat(ACC.id) as "File Downloads",
accountvideosstarted_concat(ACC.id) as "Videos Started" ,
accountvideosfinished_concat(ACC.id) as "Videos Finished",
accounttags_concat(ACC.id) as "Tags"
from account ACC

where
upper(ACC.company) not like '%VOLUME%'

and upper(ACC.company) not like '%ORACLE%'

and upper(ACC.email_address) not like '%@VOLUME%'

and upper(ACC.email_address) not like '%@ORACLE%'

order by ACC.date_created


Can I get some solution to Improve my this query.
Or how can i reduce my query time if i am using functions in it ?

Thanks very much

Hemesh.





Sad Sad
Re: sql Query Taking To much time to be execute [message #419039 is a reply to message #419034] Wed, 19 August 2009 11:28 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the functions are taking all the time then we'd really have to know what they actually do to be able to help you.

So post the code of the functions, and when you do can you please use code tags - see the orafaq forum guide if you're not sure how.

While you're at it you should read the sticky at the of the Performance Tuning forum.
Re: sql Query Taking To much time to be execute (merged 4) [message #419041 is a reply to message #419034] Wed, 19 August 2009 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of reposting 4 times the same post you should take time to read read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: sql Query Taking To much time to be execute [message #419162 is a reply to message #419039] Thu, 20 August 2009 03:35 Go to previous message
hemss007
Messages: 21
Registered: June 2008
Location: LONDON
Junior Member
Thanks for answer,
Here is My all 6 Functions code.
CREATE OR REPLACE FUNCTION "accountbenchmarkqas_concat"
     (ac_id  INTEGER)
RETURN CLOB
IS
  out_value  CLOB;
BEGIN
  SELECT Commajoin(CURSOR (SELECT   'Q:'
                                    ||abha.question
                                    ||'  A:'
                                    ||abha.answer AS "Benchmark Questions"
                           FROM     ACCOUNT acc
                                    JOIN accountactivityhistory aah
                                      ON acc.id = aah.account_id
                                         AND aah.TYPE = 1
                                    JOIN accountbenchmarkhistory abh
                                      ON abh.id = aah.type_id
                                    JOIN accountbenchmarkhistoryanswer abha
                                      ON abh.id = abha.account_benchmark_history_id
                                         AND acc.id = ac_id
                           ORDER BY abha.question),Chr(13))
  INTO   out_value
  FROM   dual;
  
  RETURN Nvl(out_value,' ');
END accountbenchmarkqas_concat;
/

CREATE OR REPLACE FUNCTION "accountfiledownloads_concat"
     (ac_id  INTEGER)
RETURN CLOB
IS
  out_value  CLOB;
BEGIN
  SELECT Commajoin(CURSOR (SELECT   aah.date_created
                                    ||' : '
                                    ||v.asset AS "File Downloads"
                           FROM     ACCOUNT acc
                                    JOIN accountactivityhistory aah
                                      ON acc.id = aah.account_id
                                         AND acc.id = ac_id
                                         AND aah.TYPE = 9
                                    JOIN cmspageasset v
                                      ON v.id = aah.type_id
                           ORDER BY aah.date_created),','
                                                      ||Chr(13))
  INTO   out_value
  FROM   dual;
  
  RETURN Nvl(out_value,' ');
END accountfiledownloads_concat;
/

CREATE OR REPLACE FUNCTION "accountresdownloads_concat"
     (ac_id  INTEGER)
RETURN CLOB
IS
  out_value  CLOB;
BEGIN
  SELECT Commajoin(CURSOR (SELECT   aah.date_created
                                    ||' : '
                                    ||v.title AS "Resource Downloads"
                           FROM     ACCOUNT acc
                                    JOIN accountactivityhistory aah
                                      ON acc.id = aah.account_id
                                         AND acc.id = ac_id
                                         AND aah.TYPE = 5
                                    JOIN "RESOURCE" v
                                      ON v.id = aah.type_id
                           ORDER BY aah.date_created),','
                                                      ||Chr(13))
  INTO   out_value
  FROM   dual;
  
  RETURN Nvl(out_value,' ');
END accountresdownloads_concat;
/

CREATE OR REPLACE FUNCTION "accounttags_concat"
     (ac_id  INTEGER)
RETURN CLOB
IS
  out_value  CLOB;
BEGIN
  SELECT Commajoin(CURSOR (SELECT DISTINCT t.NAME AS tag_name
                           FROM   tag t
                                  LEFT JOIN blogposttagjoin bt
                                    ON bt.tag_id = t.id
                                  LEFT JOIN cmspagetagjoin ct
                                    ON ct.tag_id = t.id
                                  LEFT JOIN eventtagjoin et
                                    ON et.tag_id = t.id
                                  LEFT JOIN resourcetagjoin rt
                                    ON rt.tag_id = t.id
                                  LEFT JOIN videotagjoin vt
                                    ON vt.tag_id = t.id
                                  JOIN accountactivityhistory aah
                                    ON (aah.TYPE = 2
                                        AND aah.type_id = vt.video_id)
                                        OR (aah.TYPE = 4
                                            AND aah.type_id = bt.blog_post_id)
                                        OR (aah.TYPE = 5
                                            AND aah.type_id = rt.resource_id)
                                        OR (aah.TYPE = 6
                                            AND aah.type_id = ct.cms_page_id)
                                        OR (aah.TYPE = 13
                                            AND aah.type_id = et.event_id)
                                  JOIN ACCOUNT acc
                                    ON acc.id = aah.account_id
                                       AND acc.id = ac_id),','
                                                           ||Chr(13))
  INTO   out_value
  FROM   dual;
  
  RETURN Nvl(out_value,' ');
END accounttags_concat;
/

CREATE OR REPLACE FUNCTION "accountvideosfinished_concat"
     (ac_id  INTEGER)
RETURN CLOB
IS
  out_value  CLOB;
BEGIN
  SELECT Commajoin(CURSOR (SELECT   aah.date_created
                                    ||' : '
                                    ||v.title AS "Videos Finished"
                           FROM     ACCOUNT acc
                                    JOIN accountactivityhistory aah
                                      ON acc.id = aah.account_id
                                         AND acc.id = ac_id
                                         AND aah.TYPE = 3
                                    JOIN video v
                                      ON v.id = aah.type_id
                           ORDER BY aah.date_created),','
                                                      ||Chr(13))
  INTO   out_value
  FROM   dual;
  
  RETURN Nvl(out_value,' ');
END accountvideosfinished_concat;
/

CREATE OR REPLACE FUNCTION "accountvideosstarted_concat"
     (ac_id  INTEGER)
RETURN CLOB
IS
  out_value  CLOB;
BEGIN
  SELECT Commajoin(CURSOR (SELECT   aah.date_created
                                    ||' : '
                                    ||v.title AS "Videos Started"
                           FROM     ACCOUNT acc
                                    JOIN accountactivityhistory aah
                                      ON acc.id = aah.account_id
                                         AND acc.id = ac_id
                                         AND aah.TYPE = 2
                                    JOIN video v
                                      ON v.id = aah.type_id
                           ORDER BY aah.date_created),','
                                                      ||Chr(13))
  INTO   out_value
  FROM   dual;
  
  RETURN Nvl(out_value,' ');
END accountvideosstarted_concat;
/ 
Previous Topic: Empty table takes time to show count
Next Topic: Merge on Table with UNUSABLE unique index
Goto Forum:
  


Current Time: Sat Dec 03 20:08:12 CST 2016

Total time taken to generate the page: 0.14045 seconds