Vryjdfkk

The scenario

I am having issues with a SELECT query on a view named V3, which takes ages (I had to cancel in SSMS, it timeouts in the application). V3 combines two other views, V1 and V2, to get a one-row result in the end (WHERE clause with idx value).

V3 is defined the following way:

SELECT Live_DataIdx, DTLive1, DTLive2, DTLive3, DTLive4, DTDelivery, DTData2, DTLive5, DTData3, DTData4, Desc, Counter, DataFormat, DataType, Live_QueueIdx, Live_QueueGUID

FROM V1



UNION



SELECT History_DataIdx, DTLive1, DTLive2, DTLive3, DTLive4, DTDelivery, DTData2, DTLive5, DTData3, DTData4, Desc, Counter, DataFormat, DataType, live_QueueIdx, Live_QueueGUID

FROM V2

V1 definition:

SELECT DISTINCT TOP (100) PERCENT dbo.Live_Data.Live_DataIdx,dbo.Live_Queue.DTLive1,dbo.Live_Queue.DTLive2, dbo.Live_Queue.DTLive3,dbo.Live_Queue.DTLive4, dbo.Live_Data.DTData1 AS DTDelivery,dbo.Live_Data.DTData2, dbo.Live_Queue.DTLive5,dbo.Live_Data.DTData3,dbo.Live_Data.DTData4, dbo.MsgType.Desc, dbo.Live_Data.Counter, dbo.Live_Queue.DataFormat, dbo.Live_Data.DataType, dbo.Live_Queue.Live_QueueIdx, dbo.Live_Queue.Live_QueueGUID

FROM dbo.Data 

INNER JOIN dbo.Live_Data ON dbo.Data.DataGUID = dbo.Live_Data.DataGUID 

INNER JOIN dbo.MsgType ON dbo.Live_Data.MsgTypeGUID = dbo.MsgType.MsgTypeGUID 

LEFT OUTER JOIN dbo.Live_Queue ON dbo.Live_Data.Live_QueueGUID = dbo.Live_Queue.Live_QueueGUID

ORDER BY dbo.Live_Data.Live_DataIdx DESC

V2 is declared as follows:

SELECT DISTINCT TOP (100) PERCENT dbo.History_Data.History_DataIdx, dbo.History_Queue.DTLive1, dbo.History_Queue.DTLive2, dbo.History_Queue.DTLive3, 

                  dbo.History_Queue.DTLive4, dbo.History_Data.DTData1 AS DTDelivery, dbo.History_Data.DTData2, dbo.History_Queue.DTLive5, 

                  dbo.History_Data.DTData3, dbo.History_Data.DTData4, dbo.MsgType.Desc, dbo.History_Data.Counter, dbo.History_Queue.DataFormat, dbo.History_Data.DataType, 

                  dbo.History_Queue.History_QueueIdx AS live_QueueIdx, dbo.History_Queue.Live_QueueGUID

FROM dbo.Data 

INNER JOIN dbo.History_Data ON dbo.Data.DataGUID = dbo.History_Data.DataGUID 

INNER JOIN dbo.MsgType ON dbo.History_Data.MsgTypeGUID = dbo.MsgType.MsgTypeGUID 

LEFT OUTER JOIN dbo.History_Queue ON dbo.History_Data.Live_QueueGUID = dbo.History_Queue.Live_QueueGUID

ORDER BY dbo.History_Data.History_DataIdx DESC

I am calling view V3 via Linq2SQL (but I did also try in SSMS) with the following query:

SELECT [Desc],Counter,DataType,DataFormat,DTLive1,DTData3,DTData4,

       DTDelivery,DTData2,DTLive2,DTLive3,DTLive5,DTLive4

FROM V3

WHERE Live_QueueIdx = 4325324

My request

Thus, I am asking for hints about creating some indices (if recommended: how should those be structed) or reconsiderations about the query. I don't think this use-case is of anything unique, so there has to be plans outside for this kind of a query.

Considerations

These views are just overwhelming to start digging into the solution of the right index composition. I did already try some nonclustered indices, but I am not sure about it, because the query just don't stop (waiting for more than 10 minutes), my feeling is that there's some other problem. The NCIs also didn't have any effect. Many different factors need to be considered:

• Multiple columns are requested. Which column set might be responsible for the delay? Only one, many, all but one?


• Multiple joins do exist


• Where clause is used on an idx value

The problem lies within V2 that contains the history data (~1.5 million entries in each history table), V1 can be called in an instance. But because V3 is an UNION of both, V3 suffers from the slowmotion effect of V2. The idea behind the V3 is to combine live and history data to get one specific entry with the selected idx value (here: 4325324) in the end.

The V1 and V2 views are nearly equal, the only difference is that V2 is built with the history tables (History_Data and History_Queue) instead of the live tables (Live_Data and Live_Queue), while both have the same structure.

Any of the tables above has a primary key on a uniqueidentifier value (see listing below), thus automatically a clustered index on it:

• 
  Data: DataGuid
  


• 
  Live_Data: Live_DataGUID
  


• 
  MsgType: MsgTypeGUID
  


• 
  Live_Queue: Live_QueueGUID
  


• 
  History_Data: Live_DataGUID
  


• 
  History_Queue: Live_QueueGUID
  

The select query runs like endlessly, I stopped after a period of more than 10 minutes.

Execution plan

The execution plan can be regarded at the following PasteThePlan URI: Estimated Execution Plan

Note: I removed the nonclustered indices. I tried to add to start from the beginning again to get an idea how to begin systematically on this kind of slowmotion issues.

Any help is kindly appreciated, not only indexwise, but conceptually, too. Thank you.