This week's book giveaway is in the Kotlin forum.
We're giving away four copies of Kotlin in Action and have Dmitry Jemerov & Svetlana Isakova on-line!
See this thread for details.
Win a copy of Kotlin in Action this week in the Kotlin forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Slow Execution  RSS feed

 
Prabhash Mishra
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,
        I have the following query which is taking time to execute.I think it is due to GroupBy.Can anybody modify this query and execute query without using GroupBy but the output result should be same.I am not able to do it.



Thanks,
Prabhash
 
Dave Tolls
Rancher
Posts: 2909
35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Just tidying up the SQL so we can actually see what's in it.
 
Dave Tolls
Rancher
Posts: 2909
35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK.
First off, how slow is slow?

What are the requirements?

Second, What does the table look like?
Is each row a line item for the given order_id?
 
Prabhash Mishra
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
   The query is about fetching billing address,shipping address,total order summary(total amt,total tax,total discount) and all the line items associated with that orderid.In sql devloper it is talking 1.5 sec to 2 sec.But clients requirement is .3 msc.

Thanks,
Prabhash
 
Prabhash Mishra
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
apology requirement is .3s.
 
Dave Tolls
Rancher
Posts: 2909
35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, but what do the tables look like?
Is there a master order table (where the id comes from)?
What's the relationship?

I ask, because there looks to be an awful lot of irrelevant data if the rows on this table represent order line items.
 
Prabhash Mishra
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
   I am a java devloper and query is written by some oracle guy.I asked oracle guy to give me view and SCHL_ORDER_DETAILS_V is a view.After so many effort of optimization he was not able to reduce the time.I write this query to fetch required data from view.Since he is not able to optimize query thats why i am optimizing the view query.Oracle guy is absent since one week and its urgent thats why i am optimizing my view query.

Thanks,
Prabhash

 
Dave Tolls
Rancher
Posts: 2909
35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you don't know much about Oracle then it's going to be almost impossible to help.

What does this view represent?

Do you have the DDL that created the view?
 
Prabhash Mishra
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
   DDL for the view is:



Thanks,
Prabhash
 
Dave Tolls
Rancher
Posts: 2909
35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Blimey.

That's a bit of a monster.

I would start with an "explain plan" for your query then and see what it's doing.
That'll give you some start points.

I'm off for the weekend, but there are others who may be able to pick this up (if they're around), but that does look like something that might be too big to deal with here.
 
Prabhash Mishra
Ranch Hand
Posts: 41
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
    Waiting for your solution.

Thanks
Prabhash
 
Dave Tolls
Rancher
Posts: 2909
35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As I suggested, you'll need an explain plan to figure out what Oracle is doing with the query.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!