Home » SQL & PL/SQL » SQL & PL/SQL » Testing whether rows exist from multiple tables with one PL/SQL-SQL context switch?
Testing whether rows exist from multiple tables with one PL/SQL-SQL context switch? [message #200969] Thu, 02 November 2006 01:44 Go to next message
karppi
Messages: 2
Registered: November 2006
Junior Member
Hi,

in my PL/SQL I need to avoid PL/SQL-SLQ engine context switching, it seems to be quite a huge performance issue. I have parent table(A) and several children(B,C,D,E,F) to that table, in most cases there are no rows in the child tables. I need to get all the rows from the parent and the child tables and hand them out from my PL/SQL.

Currently my PL/SQL performs a separate query in each table to get the data -this results in several "context switches". I planned to form a query that first tests with one "context switch", in which child tables there really are entries for my query(which is the FK to the parent table) -and only after that I would fetch data from the tables that really contain data for my query. This way I could save several "context switches". My problem is that, I haven't been able to develop such a query.

Any hint on testing whether rows exist from multiple tables with one PL/SQL-SQL context switch?

Re: Testing whether rows exist from multiple tables with one PL/SQL-SQL context switch? [message #201058 is a reply to message #200969] Thu, 02 November 2006 07:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Using outer joins you can return everything in just one query.
It is good to try and minimize the context switches, but don't overestimate them. It is not a "huge performance issue" as you state it.
Re: Testing whether rows exist from multiple tables with one PL/SQL-SQL context switch? [message #201223 is a reply to message #200969] Fri, 03 November 2006 03:32 Go to previous messageGo to next message
karppi
Messages: 2
Registered: November 2006
Junior Member
Thanks for the advice,

I got the idea from several sources stating that "It is worth noting that excessive context switching can affect performance." Of course, it's not so obvious what is "extensive". Well -Oracle manuals seems to state that with 4 or more context switches there would considerable improvement.

I got 50% off from the CPU load by cutting out(hardcoded) the queries to the child tables(compared to doing the queries which would not return any rows). I wonder what is the dominant part in a performance increase of this scale: Is it the decreased number of context switches, leaving out the query that returns no rows or what... (I have a clue that queries with no results might also be "expensive".)

[Updated on: Fri, 03 November 2006 03:43]

Report message to a moderator

Re: Testing whether rows exist from multiple tables with one PL/SQL-SQL context switch? [message #201227 is a reply to message #201223] Fri, 03 November 2006 03:42 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you cut out 90% of the queries, you would expect a decrease in CPU-usage, wouldn't you?
Like I said, it is not necessarily the actual context-switch that costs, it could very well be the number of queries fired.
btw. do you use dynamic sql for the detail-tables?

PL/SQL is made to execute SQL, so don't be too afraid for that. Of course, if you can do it in 1 query, that's the preferred way, but PL/SQL -> SQL isn't so bad.
SQL queries that use PL/SQL functions are (in general) more of a bother.

Maybe you could share some of your code?
Previous Topic: Table name in cursor
Next Topic: table join
Goto Forum:
  


Current Time: Tue Dec 06 06:27:38 CST 2016

Total time taken to generate the page: 0.05672 seconds