SQL View Optimization

Question

I am trying to build a view that does basically 2 things, whether a record in table 1 is in table 2 and whether a link to another table is still there. it worked on a subset of data, but when i tried to run the full query it timed out in the view designer.

The view worked fine until I added in the check to see whether the link to another table was present.

Initially it joined table A to Table B and filtered out where A.ID wasnt present in the ID column in table B

I was then told that if the link between the person and the address table (stored in table C) was removed then we would have no way of knowing other than to get a full extract of that table again and see which links are no longer present. I am trying to use that check to determine whether to display some data in particular columns

I am using the following structure close to 60 times to choose whether to show information in a column:

Column1 = case when exists (select LinkID from LinkTable C
          where cast(C.LinkAddressID as varchar) = A.AddressID
            and cast(C.LinkID as varchar) = A.ID)
          then Column1
          else NULL
          end

There is about 1.6m records in Table A just over 4m records in the Link table.

is there a better way to write this query / view that would be more optimized?

Please let me know if more information is needed

When defining varchar datatypes you should always specify the size. Without more details here it is hard to know what is going on but it seems like possibly a left join to LinkTable would be better. You need to provide a lot more information here before we can really help. A small piece of a query is not enough to help with performance. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Jul 18 '16 at 13:32

score 0 · Answer 1 · answered Jul 18 '16 at 13:41

Select C.LinkID
   From A
   Left Join C On C.LinkAddressID = A.AddressID And C.LinkID = A.ID

This will give you C.LinkID if a match exists on the two conditions and NULL if both criteria are not satisfied.

Having indexes / keys such as primary key on A.ID and foreign key relationships based on what is in the join clause will provide very good performance.

score 0 · Answer 2 · answered Jul 18 '16 at 15:18

As Joe suggested, if for all 60 columns you use the same AddressId and Id fields to match two tables, I believe so you can use something as following query

SELECT
    Column1 = CASE WHEN C.LinkID IS NULL THEN NULL ELSE A.Column1 END,
    ....
FROM A
Left Join LinkTable C
    ON C.LinkAddressID = A.AddressID AND C.LinkID = A.ID

Casting data types will definitely disable the advantage from index. So keep away data type cast if possible on joins and in WHERE clauses

SQL View Optimization

2 Answers2