CODEWITHSUNDEEP
sqlsql-server
POSH Guy
POSH Guy

Reputation: 2048

SQL view is pretty slow. Do you see any problem is the view

We've created an SQL view. However, when I query the view it takes more than 10 minutes to return a single entry. Do you see any problem with the view? How can we troubleshoot a performance issue? Is there a best practice for creating the view

CREATE VIEW [dbo].[vw_mytestview]
AS
    SELECT        
        Table1.SERVICE_LINE_DESCR, Table1.SUB_SERVICE_LINE_CD, 
        Table1.SUB_SERVICE_LINE_DESCR, Table1.SL_COMPANY_ID, 
        Table1.SL_DEPARTMENT_ID, Table1.SL_SUB_AREA, Table1.DELETE_FLAG, 
        Table1.SL_RANK_CD, Table1.SL_RANK_DESCR, 
        Table1.SL_COMM, Table1.EMPL_STATUS, Table1.GBL_ASSIGNEE_IND, 
        Table1.SL_EXCL_INCL_FL, Table1.SL_MC_CTRY_2CHAR, 
        Table1.SL_IND_SEGMENT_CD, Table1.SL_GD_STATUS, Table1.SL_DOMAIN, 
        Table1.SL_MAILING_ADDR, Table1.SL_CODED_MAIL_ADDR, 
        Table1.START_DATE, Table1.HIRE_DT, Table1.DATETIME_STAMP, 
        Table2.samaccountname
    FROM            
        dbo.mytesttable AS Table1 
    INNER JOIN
        dbo.vw_otherone AS Table2 ON Table1.colum1 COLLATE DATABASE_DEFAULT = Table2.colum1 COLLATE DATABASE_DEFAULT
    WHERE        
        (Table2.samaccountname IS NOT NULL) 
        AND (Table1.SL_DISPLAY_NAME IS NOT NULL) 
        AND (Table1.SL_ACCOUNT_TYPE IS NOT NULL) 
        AND (Table1.SL_FIRST_NAME IS NOT NULL) 
        AND (Table1.SL_LAST_NAME IS NOT NULL) 

GO

Upvotes: 0

Views: 57

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

It is almost impossible to say, because your view references another view.

However, this condition is highly suspicious:

ON Table1.GPN COLLATE DATABASE_DEFAULT = Table2.GPN COLLATE DATABASE_DEFAULT

Changing the collation can prevent the use of indexes. Two columns used as JOIN keys should already have compatible collations. If not, fix your data model!

And, depending on how many of your fields are "empty", you might be able to filter more efficiently. However, that would only be true if the WHERE conditions remove a significant number of rows.

Upvotes: 3

Himanshu
Himanshu

Reputation: 3970

Can use an index on your key columns (present in where condition) else the view doesnt have any problem

Upvotes: 0

Related Questions