February 23, 2012 at 6:48 am
you cannot do what you've asked for, but your query does not need to have two sub-queries:
CREATE VIEW vw_slowview
AS
select quick.Instrument_Id,
MAX(quick.TimeDateUpdated) AS TimeDateUpdatedSlow
from
(SELECT Instrument_Id, TimeDateUpdated FROM dbo.tblFutures_Options
UNION ALL
SELECT Instrument_Id, TimeDateUpdated FROM dbo.tblFutures_Options_Identifiers
UNION ALL
SELECT Instrument_Id, TimeDateUpdated FROM dbo.tblFutures_Options_Additional_Info
) quick
GROUP BY quick.Instrument_Id
you can also try:
CREATE VIEW vw_slowview2
AS
select quick.Instrument_Id,
MAX(quick.TimeDateUpdated) AS TimeDateUpdatedSlow
from
(SELECT Instrument_Id, max(TimeDateUpdated) TimeDateUpdated FROM dbo.tblFutures_Options GROUP BY Instrument_Id
UNION ALL
SELECT Instrument_Id, max(TimeDateUpdated) TimeDateUpdated FROM dbo.tblFutures_Options_Identifiers GROUP BY Instrument_Id
UNION ALL
SELECT Instrument_Id, max(TimeDateUpdated) TimeDateUpdated FROM dbo.tblFutures_Options_Additional_Info GROUP BY Instrument_Id
) quick
GROUP BY quick.Instrument_Id
February 23, 2012 at 7:03 am
Thanks Eugene, that's a bit tidier.
I am talking to the client about removing the DISTINCT requirement. This will allow the fast view over the 10-minute timescale, but triples the time taken over a longer timescale of 2 days, as well as tripling the number of rows returned.
http://90.212.51.111 domain
February 23, 2012 at 7:19 am
If you want the best performance possible I can advise you to do the following:
Create a separate table to hold InstrumentId and LastUpdateTime
Create light-weight triggers (after insert, update) on all relevant tables (ones you used in union statement) to upsert records into the table which will hold latest update time per instrument.
The above solution should not slow down inserts and updates too much and your select queries will fly 🙂
February 23, 2012 at 7:53 am
You could also move the WHERE clause into the view definition (assuming the 10 minute interval is a fixed value).
If the time interval is a variable, I would consider using a inline-table valued function.
CREATE VIEW vw_slowview_nowfastagain
AS
select slow.Instrument_Id,
MAX(slow.TimeDateUpdated) AS TimeDateUpdatedSlow
from
(
select
quick.Instrument_Id,
quick.TimeDateUpdated
from
(
SELECT Instrument_Id, TimeDateUpdated
FROM dbo.tblFutures_Options
WHERE TimeDateUpdated >= DATEADD(mi, -10, getdate())
UNION ALL
SELECT Instrument_Id, TimeDateUpdated
FROM dbo.tblFutures_Options_Identifiers
WHERE TimeDateUpdated >= DATEADD(mi, -10, getdate())
UNION ALL
SELECT Instrument_Id, TimeDateUpdated
FROM dbo.tblFutures_Options_Additional_Info
WHERE TimeDateUpdated >= DATEADD(mi, -10, getdate())
) quick
) slow
GROUP BY slow.Instrument_Id
February 23, 2012 at 10:42 am
LutzM (2/23/2012)
You could also move the WHERE clause into the view definition (assuming the 10 minute interval is a fixed value).If the time interval is a variable, I would consider using a inline-table valued function.
Unfortunately the 10-minute interval is only fixed 99% of the time. Once a day the interval is 24 hours or thereabouts.
An inline table function might work, and you can have them within views. But I don't believe you can pass the input variable into them in this way.
i.e. select * from myview where time=x
the table function myfunction is inside myview
and it is expecting the input as myfunction(x) I don't think it will pass the variable through.
No worries, I think I'll tell the user they'll have allow a change to their client-side code.
http://90.212.51.111 domain
February 23, 2012 at 11:14 am
I've been talking about replacing the view with an itvF.
And yes, you can call the itvF with a parameter that is used inside the function.
February 23, 2012 at 11:28 am
LutzM (2/23/2012)
I've been talking about replacing the view with an itvF.And yes, you can call the itvF with a parameter that is used inside the function.
I understand, and I'll have to tell my user this.
Many thanks.
http://90.212.51.111 domain
Viewing 7 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply