February 23, 2012 at 4:39 am
I have a simple view:
CREATE VIEW vw_quickview
AS
select
quick.Instrument_Id,
quick.TimeDateUpdated
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
I run:
select * from vw_quickview where TimeDateUpdated >= DATEADD(mi, -10, getdate())
This completes fast because, although there are millions of rows in all three tables, they each have an index on TimeDateUpdated.
The query returns no rows.
Now I want to return only distinct values of Instrument_Id in the view. But because I need to continue to filter on TimeDateUpdated when I select from the view. So I've expanded the view definition to:
CREATE VIEW vw_slowview
AS
selectslow.Instrument_Id,
MAX(slow.TimeDateUpdated) AS TimeDateUpdatedSlow
from
(select
quick.Instrument_Id,
quick.TimeDateUpdated
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
) slow
GROUP BY slow.Instrument_Id
Now when I run
select * from vw_slowview where TimeDateUpdatedSlow >= DATEADD(mi, -10, getdate())
it takes over 15 seconds, because it has to do table scans.
Is there a better way to do this?
e.g. can I get SQL Server to generate a query plan to "push" the WHERE clause critera inside the GROUP BY, where it will find there are no rows, allowing the query to complete fast.
The client wants me to improve the view performance (i.e. without them having to change the sql called nor use a stored procedure).
Thanks
http://90.212.51.111 domain
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply