1

I'm creating a query that one of its joins is made to a table from a different database. The query analyzer is suggesting the creation of an index, however I don't have permission to add objects to the other database.

Then I've tried to create an indexed view pointing to the external database and got this error: Cannot schema bind view 'dbo.vCompanies' because name 'Interfaces.dbo.Companies' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

Here is the code I'm using to create the view:

--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('dbo.vCompanies', 'view') IS NOT NULL
DROP VIEW dbo.vCompanies;
GO
CREATE VIEW dbo.vCompanies
WITH SCHEMABINDING
AS
    SELECT D.company_id, D.plant_id, D.company_name
    FROM Interfaces.dbo.Companies AS D
    GROUP BY D.company_id, D.plant_id, D.company_name
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_vCompanies
    ON dbo.vCompanies(company_id, plant_id);
GO
GO

I suppose this is not supported, if not what else can I do? Replicate the data to my database as another thread on StackOverflow suggested is not an option.


UPDATE

Here's the loooong query :D

SELECT 
    S.idSolicitacao, S.idStatus, ST.statusAr, ST.statusIt, ST.statusPt, ST.statusUs,
    S.numeroDesvio, S.numeroVersao, S.dataSolicitacao, S.dataInicioVigencia,
    S.dataFimVigencia, S.limitePecas, S.idTipoDesvio, T.descricaoAr AS descricaoTipoAr,
    T.descricaoIt AS descricaoTipoIt, T.descricaoPt AS descricaoTipoPt, 
    T.descricaoUs AS descricaoTipoUs, S.idUsuarioSolicitante, U.nome,
    S.idCausa, C.descricao, S.dataEncerramento, A.idArea, A.nome AS nomeArea,
    M.idModelo, M.descricao as descricaoModelo, V.idVersao, V.descricao AS descricaoVersao,
    COALESCE(F.FORNECEDOR, '') AS codFornecedor,
    COALESCE(F.RAZAO_SOCIAL, '') AS descFornecedor,
    DS.codigoDesenhoSubstitutivo AS DesenhoSubs,
    DESEN.Dendis AS DescDesenhoSubs,
    ISNULL((CASE WHEN tiporastreabilidade = 1 THEN inicial ELSE chassiCisMotortransmissao END),'') AS inicioRast,
    ISNULL((CASE WHEN tiporastreabilidade = 1 THEN FINAL ELSE '' END),'') AS fimRast,
    idFormaRastreabilidade,
    ISNULL((SELECT datediff(DD, S.datasolicitacao, getdate()) WHERE S.idstatus < 5),-1) AS DiasPendentes

FROM 
    dbo.DesvioSolicitacao AS S 
        INNER JOIN DesvioPlanta P ON 
            S.idPlanta = P.idPlanta 
            INNER JOIN dbo.DesvioStatus AS ST ON
            S.idStatus = ST.idStatus
            INNER JOIN dbo.DesvioTipo AS T ON
                S.idTipoDesvio = T.idTipoDesvio
                INNER JOIN dbo.DesvioUsuario AS U ON
                    S.idUsuarioSolicitante = U.idUsuario
                    INNER JOIN dbo.DesvioCausa AS C ON
                        S.idCausa = C.idCausa AND S.idPlanta = C.idPlanta
                        /*Area responsável aprovação*/
                        LEFT JOIN DesvioFluxoAprovacao FA ON 
                            S.idSolicitacao = FA.idSolicitacao AND 
                            ISNULL(S.nivelAprovacaoRealizada, 0) + 1 = FA.nivel
                            LEFT JOIN DesvioArea A ON 
                                FA.idArea = A.idArea 
                                /*Modelo*/
                                LEFT JOIN DesvioSolicitacaoModelo SM ON 
                                    S.idSolicitacao = SM.idSolicitacao
                                    LEFT JOIN DesvioModelo M ON 
                                        SM.idModelo = M.idModelo 
                                        /*Versão*/
                                        LEFT JOIN DesvioSolicitacaoVersao SV ON 
                                            S.idSolicitacao = SV.idSolicitacao
                                            LEFT JOIN DesvioVersao V ON 
                                                SV.idVersao = V.idVersao 
                                                /*Fornecedor - Desenho Referência*/
                                                LEFT JOIN DesvioDesenhoPrevisto DP ON 
                                                    S.idSolicitacao = DP.idSolicitacao AND DP.isReferencia = 1 
                                                    OUTER APPLY (SELECT TOP 1 D.FORNECEDOR, D.RAZAO_SOCIAL
                                                                FROM Interfaces.dbo.ELOG_Fornecedor AS D WITH (NOLOCK)
                                                                WHERE DP.codigoFornecedor = D.FORNECEDOR AND
                                                                    D.CODIGO_PLANTA = P.codigo) AS F
                                                    /*DesenhoSubs*/
                                                    LEFT JOIN DesvioDesenhoSubstitutivoProposto DSP ON 
                                                        S.idSolicitacao = DSP.idSolicitacao
                                                        LEFT JOIN DesvioDesenhoSubstitutivo DS ON 
                                                            DS.idSubstituicaoDesenho = DSP.idSubstituicaoDesenho 
                                                            OUTER APPLY (SELECT TOP 1 D.Dendis, D.NDISCOMPLFAB
                                                                        FROM Interfaces.dbo.SSOLQ068 D
                                                                        WHERE
                                                                            DS.codigoDesenhoSubstitutivo = LEFT(D.NDISCOMPLFAB, LEN(D.NDISCOMPLFAB)-1)) AS DESEN

                                                                /*InicioRast / FimRast*/
                                                                LEFT JOIN DesvioSolicitacaoRastreabilidade R ON 
                                                                    S.idSolicitacao = R.idSolicitacao



WHERE S.idPlanta = 1
ORDER BY S.idSolicitacao
  • Can you have someone with permissions create the needed index? I can't think of another way to improve performance unilaterally. – Dan Guzman Jan 29 '16 at 12:30
  • The problem is that this external table is used on an OUTER APPLY and represents 49% of the cost of the entire query, returning 1K rows. – Juliano Nunes Silva Oliveira Jan 29 '16 at 17:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.