?
Solved

dynamic sql - improving performance

Posted on 2011-04-29
29
Medium Priority
?
531 Views
Last Modified: 2012-05-11
Hi

I need to break a complex query into small segments of tables and execute them as dynamic statments. For example a query like this

select col1,col2 --select columns table
from table1.table2 --table name table
where table1.col = 'static cond' --stat cond table
and table2.col = 'static cond'
and table1.col = table2.col --join conditions table
and col1 in  (select col1 from table1,table2  --inner query
                    where table1.col = 'static cond' --stat cond table
                         and table2.col = 'static cond'
                         and table1.col = table2.col --join conditions table,coun(*) cnt
)
and cnt > 1 - condition table
group by col1,col2 -- group by table

want to break them up as tables and execute them so that if any condition or joins or tables are changed it is changed outside the query and the query will take those changes dynamically and construct query and execute them.
0
Comment
Question by:mahjag
  • 14
  • 14
29 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35492994
What are you wanting to do?

The way I read this now: If someone adds col3 to table1, you want code to dynamically alter all the SQL that refers to table1 so col3 is now added?
0
 

Author Comment

by:mahjag
ID: 35493128
yes the dynamic statment should handle any of the changes to table names, join conditions and any static conditions that are added to the table and still work on decent performance.
I dont know what to do with inner query - meaning how to break that up in a table
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35493155
I'm afraid what you want to do is likely impossible.  For just the reason you mention.

What is to say that and new column added to a table will need to be part of any join in a query.  I don't know of any tool on the market that will dynamically change queries when base tables change.

Even in the common reporting tools a human manually configures the table joins.  They aren't automatic.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mahjag
ID: 35493265
The tables that I mention will be changed by configuration team and not developers, idea is when a report needs more columns it should require a code change - it requires to re-execute the dynamic query to get the columns once human adds the new columns and conditions and if in case a new table then the joins as well.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35493313
I'm still not really following the exact requirements/question here.

Can I assume this is closely related to your previous question:
http://www.experts-exchange.com/Database/Oracle/Q_26907250.html

I don't think you can do this automatically.  The configuration/design team will need to tell you:  the new columnA in tableB  does or does not need to join to another table and which table(s).
0
 

Author Comment

by:mahjag
ID: 35493402
If tables are populated with right cols - why would the dynamic query work?

Yes this is closely related to my previous question
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35493554
>>If tables are populated with right cols - why would the dynamic query work?

I don't understand.

Based on your previous question and you have a table storing the SQL for a report let's run through this:

You have the SQL in your original post stored in the tables.

The configuration team adds col4 to table2.

What do you want to happen?
0
 

Author Comment

by:mahjag
ID: 35493635
the select columns need to include col4 and if any static conditon that need to go with col4 should also be added then it is the question of how the query is constructed dynamicall to provide results for col4 - I am sorry if I am confusing you - all Iam saying is how to design dynamic query that can take new columns and conditions
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35493652
How will you determine what 'reports' need col4 added to them?
How will you determine if col4 needs to be in any joins?
0
 

Author Comment

by:mahjag
ID: 35493782
by default all reports using the query will get col4 - query would not know if col4 has any static condtions - if any added then it will execute
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35493833
We are on two separate pages here.

I think I see where I'm confused.  You aren't asking how to automatically update the stored SQL when a change happens.  You are wanting the report to execute whatever SQL you have stored and you will be manually updating the stored SQL to reflect the change.

If that is the case then how is this not a duplicate of the other question?

Sample data and expected results would help a lot.  That way we cannot confuse each other.

0
 

Author Comment

by:mahjag
ID: 35494046
OK - let us start with this - I will provide examples - first I am looking for answer on how to execute dyanamic query without impacting performance - I have several tables as I mentioend above on select query that I want to combine them as dynamic query

1. first how do I do explain plan on them - I use toad highlight of query and click on explain plan produce some invalid expression - but query looks ok to me as it compiles.
2. after explain plan how do I tune them -

These are not same as my post earlier - I am looking to an answer on how to design dynamic query and also looking for best practices on them to use so that it would not be a big performance hit -

does that sound good for a start?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35494072
>>first I am looking for answer on how to execute dyanamic query without impacting performance

execute immediate with bind variables.

1: capture the generated string then generate the plan.
2: same as any other query.
0
 

Author Comment

by:mahjag
ID: 35494250
Thanks for the quick response -

if that is the case why sql that uses dynamic query takes more time compared to the actual string that generates the query- if I take the sql from the generated string and run then I see it is much faster than executing from dynamic query  - could you explain why?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35494311
How are you doing the testing?

The database blocks might be cached so the second test will appear to run faster.

When testing, if possible flush the cache between tests.  This will provide worst possible case for testing:  No blocks stored in the buffer cache.

If that isn't really possible, always run the first 'test' twice and use the last run:

for example:
run test1
run test2
run test1

Compare the values of the last two tests.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35494316
Are you sure it's the execution of the generated SQL and not building the SQL causing the slow down?
0
 

Author Comment

by:mahjag
ID: 35494370
I am only taking the sql apart and executing it separately and not looking at building the sql - how to monitor if that is the problem?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35494443
You say 'dynamic' is slow.  You must be executing it somehow.

Show me the slow 'dynamic' code versus the fast not dynamic code.
0
 

Author Comment

by:mahjag
ID: 35494467
all I do is this

v_string := 'select col1,col2 --select columns table
from table1.table2 --table name table
where table1.col = 'static cond' --stat cond table
and table2.col = 'static cond'
and table1.col = table2.col --join conditions table
and col1 in  (select col1 from table1,table2  --inner query
                    where table1.col = 'static cond' --stat cond table
                         and table2.col = 'static cond'
                         and table1.col = table2.col --join conditions table,coun(*) cnt
)
and cnt > 1 - condition table
group by col1,col2 -- group by table
';
execute immediate (v_string);

I execute above and got about 2 min and if I cut the sql inside v_string and execute it is very fast less than 1 min
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1200 total points
ID: 35494554
I believe Toad starts displaying results that are returned as they are being returned.

Are you sure the static query is finishing completely?

Can you try from sqlplus?

execute the script below in sqlplus and post the results.
set serveroutput on
declare 
	junk number;
	v_string varchar2(4000);
begin

v_string := 'select count(1)
from table1.table2
where table1.col = 'static cond
and table2.col = 'static cond'
and table1.col = table2.col 
and col1 in  (select col1 from table1,table2  
                    where table1.col = 'static cond' 
                         and table2.col = 'static cond'
                         and table1.col = table2.col 
)
and cnt > 1
group by col1,col2 
';


dbms_output.put_line('1:' || to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');
execute immediate v_string into junk;
dbms_output.put_line('2:' || to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');

select count(1) into junk
from table1.table2
where table1.col = 'static cond
and table2.col = 'static cond'
and table1.col = table2.col 
and col1 in  (select col1 from table1,table2  
                    where table1.col = 'static cond' 
                         and table2.col = 'static cond'
                         and table1.col = table2.col 
)
and cnt > 1
group by col1,col2 
;

dbms_output.put_line('3:' || to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');
execute immediate v_string into junk;
dbms_output.put_line('4:' || to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');

end;
/

Open in new window

0
 
LVL 2

Expert Comment

by:abbas_najafizadeh
ID: 35496927
if your static condition values is dynamic, then simply save your static values in a single row table and use it in your Query like a sub-select:
where table1.col = (select MystaticCol1 from MySingleRowTable)
and table2.col = (select MystaticCol2 from MySingleRowTable)
..... and so on

... and if you need a dynamic condition (with no static values in it)
like this :  
            ..... and table1.col = table2.col --join conditions table
then pay attention this is a dynamic sql condition...
in this case, solution is :
  Add new fields like newcol1 and newcol2, then make your code like this:
            .... and table1.newcol1 = table2.newcol2 --join conditions table
and before execution of your query fill your new columns with your needed columns to be in your dynamic condition, for example if you need condition as table1.col5 = table2.col6
your UPDATE statement before execute query is:

update
table1 set newcol1=col5;

update
table2 set newcol2=col6;

  .... and table1.newcol1 = table2.newcol2 --join conditions table



thanks,
be successful




0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35498646
abbas_najafizadeh,

I believe this has all already been covered.  We are now down specifically to performance between static and dynamic SQL.

Please read:  http:#a35494250
0
 

Author Comment

by:mahjag
ID: 35504954
have a question from abbas_najafizadeh described in his way of handling static condition - I was planning to have a table that will hold all static condition - but how I can handle a situation where a new static condition being added, if I have a table called HOLD_STATIC_VALUE and have 2 columns - STATIC_ID and VALUE1..10 - let us say at the start I have 10 static conditon and if 11is added I need to AND that conditon in my dynamic query to execute it correctly - should have a cursor around that as well?
0
 

Author Comment

by:mahjag
ID: 35515581
I am not getting better traction here for my posts - I increased point value to 300 -  before I work on performce I have few design considerations

1. I want to create a table called Rules and wanted to assign a id and then have rule type as table_names etc,,
Rule id      Rule name   Rule type       Value
1              MyTestcase  Table             TableA, TableB,TableC - or I can say table name here and add the 3 values of tables in a seprate table
1              MyTestcase    StaticCond    col1 > 0 and col2 <col3 and so on - may be create a table for this?
1              MyTestcase    Join_cond          col1 = col2 col3= col4

my unique constraint on this table will be rule_id,rule_name and rule_type is this good for a start? any suggestions on further breaking up? Any new rows to this table will be added by non technincal users so I have to make this table very simple (may be I can add form on this later on) that is why I am asking for suggestions to simplify - would appreciate any inputs

I then wanted to construct dynamic sql from above and test performance - which is why I wanted to keep ths open until I can get to that part
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35515907
I understand what you are trying to do and as long as the design works for you, then use it.  There is no right or wrong way to do it.

From your previous questions the general consensus was not to store sql in a table.  You are wanting to do it anyway:  Any design that works is as and other.

If you are just focusing on the 'design' then I maintain this is a duplicate of your previous question.  I questioned the duplicate in http:#a35493313

Then in http:#a35494250 is where you said the 'dynamic' was slower than the 'static'.

These are really two questions and I've lost track of what the real meaning of this questions is.
0
 

Author Comment

by:mahjag
ID: 35516481
First I had a design before I posted this question - and ran into some performance issues - I felt that the design was not solid and that is why I am seeing these lag in times - so I do want to revisit the design to make it more robust so I dont encounter performance issue- does this justify to go back to design - if not I will close this question right now - let me know
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35516511
From what I see and you have described, I don't feel the design alone is causing the performance problems.

I don't believe there is a huge performance difference between static and dynamic SQL.

This is what I was getting at in http:#a35494554.  I would like to see the output of that simple example.
0
 

Author Comment

by:mahjag
ID: 35708456
Hi Slightwv

I ran the dynamic sql for performance and I did not see anything different for now - first when I started this question I did have few issues but that got resolved when I changed my design to add new tables and in the sql I was missing a join statement which I added later - dont know if that fixed the issue - I am having more design questions which you said you would not address in this section - I will open a separate one for that - for now no performance issues
0
 

Author Closing Comment

by:mahjag
ID: 35708470
there was no need for this question as the issue with performace got resolved by adding more clarity to the design and added some missing join statements - it was not a p
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Fluent Interface Design Pattern You can use the Fluent Interface (http://en.wikipedia.org/wiki/Fluent_interface) design pattern to make your PHP code easier to read and maintain.  "Fluent Interface" is an object-oriented design pattern that r…
Introduction This question got me thinking... (http://www.experts-exchange.com/questions/28707487/GLOBALS.html) Why shouldn't we use Globals? This is a simple question without a simple answer.  How do you explain these concepts to a programmer w…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month14 days, 9 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question