Home » RDBMS Server » Performance Tuning » Query Based on Two Views
Query Based on Two Views [message #142991] Tue, 18 October 2005 12:22 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #143006 is a reply to message #143001] Tue, 18 October 2005 14:06 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
How about using some code tags so we can read your output?
Re: Query Based on Two Views [message #143020 is a reply to message #142991] Tue, 18 October 2005 15:00 Go to previous messageGo to next message
pauld
Messages: 4
Registered: October 2005
Junior Member
thanks guy
I shall try it out, have network problems now.
Re: Query Based on Two Views [message #143272 is a reply to message #142991] Wed, 19 October 2005 13:47 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Indexes Difference
Next Topic: How to treat binds as literals
Goto Forum:
  


Current Time: Fri Mar 29 03:46:31 CDT 2024