| Urgent help needed in tuning the query [message #267594] |
Thu, 13 September 2007 16:41  |
grk28
Messages: 38
Registered: June 2006
|
Member |
|
|
Hi,
I have a query which has set of select queries with union all and i am trying to use this query to insert into one table with order by one of the column in all the select statements.
Here is the example
insert into tall
select
c1,
c2,c3
from t1
union all
select
c4,c5,c6
from t2
union all
select
c7,c8,c9
from t3
order by 1
I have 500,000 records which has to be inserted.This insert is almost taking an hour to do that.Can some one explain me what could be the reason for this ?
even if i try to run the query with order by clause it is taking lot of time but if i comment the order by clause it is executing in seconds.it means order by clause is casuing the issue but how can i overcome that because i cant remove the order by clause in order to generate the sequence according to date.
Please help me out in how can i improve the performance of the query .
Thanks
R
|
|
|
|
|
|
|
|
| Re: Urgent help needed in tuning the query [message #267621 is a reply to message #267601] |
Thu, 13 September 2007 22:48  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Why is necessary to sort the rows before they are inserted? Tables are just unordered heaps; even if you do insert them in a special order, it cannot deterministically affect any future queries.
Anyway, let's say you do have a magical reason for sorting before the insert. How long does the SELECT take without the insert. You said it takes a long time - how long is that? 20 minutes? If so, tuning the SELECT will still leave 40 minutes of inserting.
ie. Where is all the time spent? SELECTing or INSERTing?
The reason it returns quickly without the ORDER BY is because it is returning the first matching rows of the first part of the UNION ALL. At the time it returns the first row, it hasn't found the entire result-set yet. When you add an ORDER BY, it needs to retrieve the entire result set before the first row can be returned.
Ross Leishman
|
|
|
|
| Re: Urgent help needed in tuning the query [message #267651 is a reply to message #267621] |
Fri, 14 September 2007 02:03 |
grk28
Messages: 38
Registered: June 2006
|
Member |
|
|
Ross,
Thanks for your response.
The reason for sorting it before insertion is i need to generate the sequence(unique id) based on the date on which the record has been inserted into the database.here is the example
c1 c2
a 09/10/2007
b 10/09/2006
c 09/09/1999
I need to insert records by generating a sequence which is as follows
id c1 c2
1 a 09/09/1999
2 b 10/09/2006
3 c 09/10/2007
so in order to get this i am sorting the result set first and then associating a sequence with it.
Let me know if i can achieve this in a better manner.
Thanks for all the help.
|
|
|
|
|
|
|
|
| Re: Urgent help needed in tuning the query [message #267659 is a reply to message #267653] |
Fri, 14 September 2007 02:21 |
grk28
Messages: 38
Registered: June 2006
|
Member |
|
|
Hi anacedent,
Thanks for your response.
Let me put it in this way.
I have total two queries.
one query has following output.
c1 c2
a 09/10/2007
b 10/10/2007
c 10/10/2006
other query has follwing output
c1 c2
e 09/11/2006
d 10/10/2008
f 10/05/2005
so if i use a sequence with a union all here directly ,09/10/2007 will have a sequence less than 09/11/2006 which shouldnt be the case.
In order to avoid this i am sorting these results with date column so that they form a correct order then assigning sequence to them.
Hope it isn't too confusing.
thanks
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
