| Query Based on Two Views [message #142991] |
Tue, 18 October 2005 12:22  |
pauld
Messages: 4
Registered: October 2005
|
Junior Member |
|
|
Hello ppl, I need your help.
I got this query which is based on two views.
View_1: x1, x2, x3, x4, day, xy
View_2: y1, y2, y3, y4, day, xy
By running a SELECT query on each view takes approx 2 minutes, yes there are lots of records.
When I combine the query most of the time it times out. I think there is something wrong with my query.
View_1 = View_2 on col xy and day, however I have to get the records from View_2 for the following day, i.e if day = 3 from View_1 and the record from View_2 should be coming from view_2
at present my query is like this
SELECT a.x1, a.x2, a.x3, a.x4, a.day, a.xy, b.y1, b.y2
FROM view_1 a, view_2 b
WHERE b.y2= 100
AND a.xy = b.xy
AND b.y1 = ( SELECT b.y1
FROM view_1 a, view_2 b
WHERE b.day = a.day + 1)
ORDER BY a.x1
This query gets called in a coldfusion page.
P.S I can also ask the DBA to make me a new view based on the same columns as the query. Would that help with speed?
|
|
|
|
| Re: Query Based on Two Views [message #142998 is a reply to message #142991] |
Tue, 18 October 2005 13:37  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
1. Turning a query into a view will not make it faster. A view is just a stored query text.
2. Are view1 and view2 on the same table?
3. Can you post sample data for each view and the desired output of your query to help understand the question?
|
|
|
|
| Re: Query Based on Two Views [message #143001 is a reply to message #142991] |
Tue, 18 October 2005 13:53 |
pauld
Messages: 4
Registered: October 2005
|
Junior Member |
|
|
Hi Smartin,
There is a common column 'xy' which has to have same value in both the views. i.e
a.xy = b.xy
and
the next important thing is that there is a column in View_2 'y1' which comes from the following day but for same 'xy'
i.e for View_1 if xy = 2213 and day =3
then from View_2 xy = 2213 and day = 4
For Example
View_1
x1 x2 x3 x4 Day XY
2 3 23 32 3 2132
3 3 2 3 4 2132
4 3 43 32 5 2132
View_2
y1 y2 y3 y4 Day XY
4 100 13 32 3 2132
5 100 4 3 4 2132
7 100 43 32 5 2132
5 100 23 12 6 2132
So Desired Output
a.X1 a.X2 a.X3 a.X4 a.Day a.XY b.y1 b.y2
2 3 23 32 3 2132 5 100
3 3 2 3 4 2132 7 100
4 3 43 32 5 2132 5 100
Let me know if you need further clarification
thank you
|
|
|
|
| Re: Query Based on Two Views [message #143005 is a reply to message #142991] |
Tue, 18 October 2005 14:05 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Can you then just do something like:
SELECT a.x1, a.x2, a.x3, a.x4, a.day, a.xy, b.y1, b.y2
FROM view_1 a, view_2 b
WHERE b.y2= 100
AND a.xy = b.xy
and a.day = b.day + 1;
|
|
|
|
|
|
|
|
| Re: Query Based on Two Views [message #143272 is a reply to message #142991] |
Wed, 19 October 2005 13:47 |
pauld
Messages: 4
Registered: October 2005
|
Junior Member |
|
|
|
I have tried the query and it seems to time out. It seems to take more than 5 mins and after that it times out. Is there a way I can improve the situation if the query itself is not the problem.
|
|
|
|
| Re: Query Based on Two Views [message #143274 is a reply to message #142991] |
Wed, 19 October 2005 13:51 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
The query is most likely the problem, but the difficulty here is that you are querying views not tables directly. And we have no idea about the underlying tables, or indexes, or statistics, or explain plans, or much else to go on. You should probably proceed by working on the underlying tables, forgetting the views exist.
Moving this thread to performance tuning forum.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
