Replacing nested views with a single view to speed up performance

Question

I have created a couple of views to return data of interest from a database, with the second view returning data from the first view rather than the base tables.

The first view (variantsToScore) pulls the relevant data together from across several tables. The second view (variants_to_score_by_transcript) then filters this returned data.

CREATE VIEW variantsToScore AS
SELECT v.variantID, v.chromosome, v.position, v.referenceBase, v.alternateBase, v.[HGVS_c], v.[HGVS_p], s.geneID, s.transcriptID, a.[1000g2014oct_all], a.[1000g2014oct_afr], a.[1000g2014oct_eur], a.[1000g2014oct_amr], a.[1000g2014oct_eas], s.scorer, s.scoreDate, s.score, s.checked, s.checker, t.reportTranscript
FROM dbo.variant v, dbo.varScore s, dbo.transcript_relevance t, dbo.varAnnotation a
WHERE v.variantID = s.variantID
AND s.geneID = t.geneID
AND s.transcriptID = t.transcriptID
AND v.concatField = a.concatField
AND v.variantID = a.variantID
AND s.geneID = a.geneID
AND s.transcriptID = a.transcriptID



Create View variants_to_score_by_transcript as
Select *
From variantsToScore
Where (reportTranscript = 'Y')

Union
Select *
From variantsToScore
Where variantID not in (

Select variantID
From variantsToScore
Where reportTranscript = 'Y');

Returning the first view is pretty quick - even though it returns several thousand rows. However, returning the second view (the filtered data) is quite a slow process.

I'm not very confident in the design of this second view and hope there is a better way to go about filtering the results than I have created. Also, in reading around I have come across multiple articles and posts which are not altogether positive about nesting views on principle.

I am not sure whether the poor performance is due to the design of the second view, or the nesting of the views, or maybe a combination of the two. Perhaps my design should cut out the nesting of the views altogether and try to return and filter the results all in one view? Will it be more useful to redesign the second view to improve performance than worrying about combining the views into one?

Any thoughts and reccomendations would be welcome. Cheers.

EDIT:

To try and clear up what I want to achieve with the second view I have included an expected table of results to be returned by the first view, and a desired table of results to be returned by the filtering of the second view.

Expected results before filtering: -

variantID | transcriptID | reportTranscript
    a     |       x      |         Y
    a     |       y      |         N
    a     |       z      |         N
    b     |       x      |         Y
    b     |       y      |         Y
    b     |       z      |         Y
    c     |       x      |         N
    c     |       y      |         
    c     |       z      |         N

Desired results after filtering: -

variantID | transcriptID | reportTranscript
    a     |       x      |         Y
    b     |       x      |         Y
    b     |       y      |         Y
    b     |       z      |         Y
    c     |       x      |         N
    c     |       y      |         
    c     |       z      |         N

Answer 1

Based on sample data/expected result, something like this should work:

Create View variants_to_score_by_transcript
as
   With Reports as (
     select *,MAX(ReportTranscript) OVER (PARTITION BY variantID) as RepTran
     from variantsToScore 
   )
   select * from Reports
   where RepTran <> 'Y' or ReportTranscript = 'Y'

That is - we include rows either if they have ReportTranscript equal to Y or no other rows for the same variantID have a ReportTranscript of Y.

What you appear to be trying to do with your second view is to sort the rows produced by the first view¹. But this doesn't make sense - views (and tables) have no inherent ordering - the only way to guarantee the order of a set of results is to use an ORDER BY clause on the outermost query.

So, we could write your second view as a stored procedure:

Create Procedure variants_to_score_by_transcript
as
Select *
From variantsToScore
order by case when reportTranscript='Y' THEN 0 ELSE 1 END

---

For your first view, I'd at least suggest a stylistic change since you're still using pre-ANSI joins. ANSI joins were standardized in 1992 - so it's probably time to get used to them:

CREATE VIEW variantsToScore AS
SELECT v.variantID, v.chromosome, v.position, v.referenceBase, v.alternateBase,
       v.[HGVS_c], v.[HGVS_p], s.geneID, s.transcriptID, a.[1000g2014oct_all],
       a.[1000g2014oct_afr], a.[1000g2014oct_eur], a.[1000g2014oct_amr],
       a.[1000g2014oct_eas], s.scorer, s.scoreDate, s.score, s.checked, s.checker,
       t.reportTranscript
FROM dbo.variant v
       INNER JOIN
     dbo.varScore s
       ON
         v.variantID = s.variantID
       INNER JOIN
     dbo.transcript_relevance t
       ON
         s.geneID = t.geneID and
         s.transactionID = t.transactionID
      INNER JOIN
     dbo.varAnnotation a
      ON
         v.concatField = a.concatField and
         v.variantID = a.variantID and
         s.geneID = a.geneID and
         s.transcriptID = a.transcriptID

But this shouldn't have any performance impacts.

---

¹If you were actually also trying to eliminate duplicate rows, I'd suggest strongly that you identify what's causing duplicate rows to be produced (this is usually a logic issue), or, if they still need to be eliminated, use the DISTINCT option in the SELECT clause, rather than using UNION for this.