Is possible to turn the following into a view [message #335263] |
Mon, 21 July 2008 11:53 |
bztom33
Messages: 95 Registered: June 2005
|
Member |
|
|
Hi,
I want to improve the speed of the return query. Is possible to turn this into a view table?
my_foreast_date, crsSensor,prsSensor,arsSensor and userID are input parameters.
select a.date_time, a.value value1, b.value value2, b.flag, c.value value3 from product a, product b, product c
where a.run_date =:my_forecast_date
and a.run_date = b.run_date
and a.run_date = c.run_date
and a.date_time = b.date_time
and a.date_time = c.date_time
and a.fcst_sensor_id =:crsSensor
and b.fcst_sensor_id =:prsSensor
and c.fcst_sensor_id =:arsSensor
and a.user_id = 1
and a.user_id = b.user_id
and c.user_id = :userID
order by a.date_time
Thanks,
|
|
|
|
Re: Is possible to turn the following into a view [message #335299 is a reply to message #335263] |
Mon, 21 July 2008 14:51 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
do you mean something like this:
CREATE OR REPLACE VIEW SomeView
AS
SELECT a.Date_Time,
a.VALUE Value1,
b.VALUE Value2,
b.Flag,
c.VALUE Value3,
a.Run_Date My_Forecast_Date,
a.fcst_Sensor_Id crsSensor,
b.fcst_Sensor_Id prsSensor,
c.fcst_Sensor_Id arsSensor,
c.User_Id = UserId
FROM Product a,
Product b,
Product c
WHERE a.Run_Date = b.Run_Date
AND a.Run_Date = c.Run_Date
AND a.Date_Time = b.Date_Time
AND a.Date_Time = c.Date_Time
AND a.User_Id = 1
AND a.User_Id = b.User_Id
/
SELECT *
FROM SomeView
WHERE My_Forecast_Date = :my_forecast_date
AND crsSensor = :crsSensor
AND prsSensor = :prsSensor
AND arsSensor = :arsSensor
AND UserId = :userID
/
Notice that we simply added the columns your want to query by, to the view select list. Then you can create a view based on the query without the where components, leaving you to submit a query that then uses these new columns.
I leave it to you to test the syntax and validity of this code.
Good luck, Kevin
|
|
|
Re: Is possible to turn the following into a view [message #335796 is a reply to message #335299] |
Wed, 23 July 2008 16:00 |
bztom33
Messages: 95 Registered: June 2005
|
Member |
|
|
Hey Kevin,
Thank you very much for helping me to simplify my query. It's working great. This view actually speed up my query result quite a bit.
Thanks Again.
Tom
CREATE OR REPLACE VIEW SomeView
AS
SELECT a.Date_Time,
a.VALUE Value1,
b.VALUE Value2,
b.Flag,
c.VALUE Value3,
a.Run_Date My_Forecast_Date,
a.fcst_Sensor_Id crsSensor,
b.fcst_Sensor_Id prsSensor,
c.fcst_Sensor_Id arsSensor,
c.User_Id UserId
FROM Product a,
Product b,
Product c
WHERE a.Run_Date = b.Run_Date
AND a.Run_Date = c.Run_Date
AND a.Date_Time = b.Date_Time
AND a.Date_Time = c.Date_Time
AND a.User_Id = 1
AND a.User_Id = b.User_Id
[Updated on: Wed, 23 July 2008 16:01] Report message to a moderator
|
|
|
Re: Is possible to turn the following into a view [message #335797 is a reply to message #335263] |
Wed, 23 July 2008 16:03 |
|
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
I'd take another look at that performance speedup. I am not sure why converting to the view would make it faster. It is essentially the same query. This is not to say that there is no optimizer perculiarity involved. Just do some more testing.
Glad we got one right.
Kevin
|
|
|