Home » RDBMS Server » Performance Tuning » how to speedup mentioned SQL query
how to speedup mentioned SQL query [message #252015] Tue, 17 July 2007 12:28 Go to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

hi everyone,
i have a query based on two tables :-
1. A (having 19 lakhs data)
2. B (havind 23 lakhs data)

now my query is

/*  *************************************  */

SELECT COUNT(A_C1) INTO A FROM A
WHERE A_C1 IN

(SELECT DISTINCT B_C1 FROM B WHERE LTRIM(RTRIM(DRGNNO_REF)) = LTRIM(RTRIM(B_C2)))

AND (A_C2,A_C3,A_C4,A_C5) IN (SELECT A_C2,A_C3,A_C4,A_C5 FROM A
HAVING COUNT(A_C2) > 1
AND
COUNT(A_C3) > 1
AND
COUNT(A_C4) > 1
AND
COUNT(A_C5) > 1
AND
A_C5 <> 'NA'
GROUP BY A_C2,A_C3,A_C4,A_C5);

/*  *************************************  */

SO THE EXECUTION OF THAT QUERY IS SOMEWHERE AROUND 2 TO 2:30 MINUTES IN BACK END BUT
IN FRONT END ( FORMS6i) IT WILL TAKE SOMEWHERE AROUND 5 TO 6 MINUTES AND MOST OF THE TIME APPLICATION HANGS AND THEN THE APPLICATION WILL BE RESTARTED.

SO TO INCREASE THE EXECUTION TIME OF THAT QUERY ,PLEASE ALL THE MASTERS MAKES THEIR CONTRIBUTION AND LET ME OUT OF THE PROBLEM ................

[Updated on: Tue, 17 July 2007 12:33]

Report message to a moderator

Re: how to speedup mentioned SQL query [message #252026 is a reply to message #252015] Tue, 17 July 2007 12:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ KEEP OFF YOUR FINGER FROM SHIFT key.
2/ What is a lakh?
3/ If the same query last 2 to 3 times longer in the application then the problem is in the application code not in the query.

Regards
Michel

Re: how to speedup mentioned SQL query [message #252031 is a reply to message #252026] Tue, 17 July 2007 12:48 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
2/ What is a lakh?


100*1000.

[Updated on: Tue, 17 July 2007 12:48]

Report message to a moderator

Re: how to speedup mentioned SQL query [message #252039 is a reply to message #252031] Tue, 17 July 2007 13:05 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

ya that is

100 * 1000 = 1,00,000
Re: how to speedup mentioned SQL query [message #252040 is a reply to message #252026] Tue, 17 July 2007 13:09 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

hi,
now my finger is off from shift key.

what exactly u want to say in 3rd point........


If the same query last 2 to 3 times longer in the application then the problem is in the application code not in the query

please make it clear (what 2 to 3 times u r saying)
Re: how to speedup mentioned SQL query [message #252042 is a reply to message #252031] Tue, 17 July 2007 13:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks but I just wanted to say: don't use words that are not international english (globish).

Regards
Michel
Re: how to speedup mentioned SQL query [message #252045 is a reply to message #252040] Tue, 17 July 2007 13:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what 2 to 3 times u r saying

What is u?
What is r?

Quote:
QUERY IS SOMEWHERE AROUND 2 TO 2:30 MINUTES IN BACK END

Quote:
IN FRONT END ( FORMS6i) IT WILL TAKE SOMEWHERE AROUND 5 TO 6 MINUTES

5 to 6 minutes divided by 2 to 2.5 minutes is about 2 to 3.

Regards
Michel

Re: how to speedup mentioned SQL query [message #252046 is a reply to message #252042] Tue, 17 July 2007 13:17 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

what globish words i am using ?
please reply .....
Re: how to speedup mentioned SQL query [message #252051 is a reply to message #252046] Tue, 17 July 2007 13:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
what globish words i am using ?

You are not you used IM speak.

I already answered: have a look at your application code.

Regards
Michel
Re: how to speedup mentioned SQL query [message #252052 is a reply to message #252051] Tue, 17 July 2007 13:26 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

actually michael , the same code i am using in application also .
There is no change in the code of application also......
the only thing is that by the already mentioned code i am populating 2 lists also.....
Re: how to speedup mentioned SQL query [message #252055 is a reply to message #252052] Tue, 17 July 2007 13:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How is your network?
Maybe the difference in execution time comes from it.

Are you also using Forms on the backend?
If not, you can't say you use the same code.
Quote:
the only thing is that by the already mentioned code i am populating 2 lists also.....

Aha! Comment this code, better comment all the code but the query, and see the execution time.

Regards
Michel

Re: how to speedup mentioned SQL query [message #252056 is a reply to message #252055] Tue, 17 July 2007 14:00 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

hi michel,

Quote:
Aha! Comment this code, better comment all the code but the query, and see the execution time.


what do mean by this quoted line

you are saying to comment the code (which code to comment)
and which to not.....

you are also asking
Quote:
Are you also using Forms on the backend?


can i use forms on backend also if yes than please tell me how can i achieve it......

REGARDS
RAJAT CHAUDHARY
Re: how to speedup mentioned SQL query [message #252059 is a reply to message #252056] Tue, 17 July 2007 14:04 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Quote:
Are you also using Forms on the backend?


I guess his meant was FRONT-END.
Re: how to speedup mentioned SQL query [message #252061 is a reply to message #252056] Tue, 17 July 2007 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What I meant is:
- you say you executed the query in front-end (how? with which tool?) and it lasted 2 to 3 minutes
- you say you used Forms to execute the same query + other code and it lasted 5 to 6 minutes
- I say comment all the code in Forms but the one to execute and fetch the query and time this. How long does it last?

Regards
Michel
Re: how to speedup mentioned SQL query [message #252063 is a reply to message #252061] Tue, 17 July 2007 14:17 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

reply of
1st point :-
Quote:

you say you executed the query in front-end (how? with which tool?) and it lasted 2 to 3 minutes

query in written in pl/sql mode of "when-button-pressed" trigger.
using tool is FORMS6i.

2nd point :-
Quote:
you say you used Forms to execute the same query + other code and it lasted 5 to 6 minutes
.

i am saying the same code gives the result in 5 to 6 minutes and sometimes exceeds this limit.

3rd point :-
Quote:
I say comment all the code in Forms but the one to execute and fetch the query and time this. How long does it last?


so you are saying just to run that code in FORMS6i.


but i want that result to be populated in two lists
and
the same i mentioned above that it took 5 to 6 minutes and sometimes it exceeds that time limit and hangs the application.....

REGARDS
RAJAT
Re: how to speedup mentioned SQL query [message #252066 is a reply to message #252063] Tue, 17 July 2007 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm now completly lost.
When it is executing in 2-3 minutes and when in 5-6 minutes?
In which case?
Under what circonstances?
What is the difference between the two?
If you don't do the same thing in the 2 cases why do you say it is not normal you have 2 different execution times?

Regards
Michel
Re: how to speedup mentioned SQL query [message #252068 is a reply to message #252066] Tue, 17 July 2007 14:36 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

don't be hyper

in 2-3 minutes :- sql*plus 9.0
in 5-6 minutes :- forms 6i

Quote:
If you don't do the same thing in the 2 cases why do you say it is not normal you have 2 different execution times?


thats why i am also shocked after executing the same from both (sql*plus ans forms 6i) timing have a lot of gap........
Re: how to speedup mentioned SQL query [message #252070 is a reply to message #252015] Tue, 17 July 2007 14:44 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
You say you are populating 2 lists.

So are you running the query twice in Forms ?

Re: how to speedup mentioned SQL query [message #252072 is a reply to message #252070] Tue, 17 July 2007 14:47 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

yes actually.....(because i need it......)

actually thats why i give the code ,that after look at the give the suggestion that what should i have to do to speedup the execution.........
Re: how to speedup mentioned SQL query [message #252074 is a reply to message #252070] Tue, 17 July 2007 14:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Marc, good question.

rajat chaudhary, this is why I asked to comment all forms code but the query. So we can see if this is the query or the rest of the code that is at the origin of the time increase.
Mathematically: 5-6 minutes - 2-3 minutes = time of code + time of network. If you execute the query only once, otherwise (2 query executions) time is normal.

Regards
Michel

Re: how to speedup mentioned SQL query [message #252075 is a reply to message #252072] Tue, 17 July 2007 14:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So the problem is not front-end back-end but query execution time.
Now explain us, ,in words, what is the purpose of the query, post execution plan, indexes... and all what is in the forum sticky.

Regards
Michel
Re: how to speedup mentioned SQL query [message #252076 is a reply to message #252074] Tue, 17 July 2007 14:54 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

fine michel,
i will try it and then tomorrow i will tell you what the time difference is there in execution

ok bye
thanks for today's discussion

(tomorrow i will come again)

if u don't mind then could you please give me your mail-id by which we an chat also on other discussions also by the way mine is

Quote:
1986.chaudhary@gmail.com

and
Quote:
rajarchaudhary1986@yahoo.co.in


GOD BLESS YOU


regards
rajat
Re: how to speedup mentioned SQL query [message #252080 is a reply to message #252076] Tue, 17 July 2007 15:07 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a nice night.

Regards
Michel
Previous Topic: slow update on large table
Next Topic: Time Differnce in Execution with / without Hint
Goto Forum:
  


Current Time: Tue Dec 06 08:25:05 CST 2016

Total time taken to generate the page: 0.14081 seconds