1

I have some select statements that fetch data from multiple tables and usually they are ran many times to update lists on the application for users to see data. Since I'm pretty much new to this whole story about views, I was reading this article how it said views are supposed to replace long queries and are just virtual tables to access data faster and I was wondering how and if should i use views instead of these long queries.

Here are some of my queries

IF @Action = 'SELECTALL'
BEGIN
    SELECT hr.IdColaborador,  hr.MotivoCriacao, hr.IdReq, Nome, hcb.Email, hr.RefInterna, hi.Descricao AS InfoLog, hs.Descricao AS STATUS, hd.DataAbertura AS DataCriacao FROM hRequisicao AS hr    
    JOIN hColaborador AS hcb ON hcb.IdColaborador = hr.IdColaborador
    JOIN hStatus AS hs ON hs.IdStatus = hr.IdStatus
    JOIN hInfoLogistica hi ON hi.IdInfoLogistica = hr.IdInfoLogistica               
    JOIN hDataLog hd ON hd.IdReq = hr.IdReq
    WHERE hr.IdStatus != 6
END
IF @Action = 'SELECTALL_USERID'
BEGIN
    SELECT hr.IdColaborador, hr.IdReq, Nome, hcb.Email, hr.RefInterna, hs.Descricao AS STATUS, hl.DataAbertura AS DataCriacao, hi.Descricao AS InfoLog  FROM hRequisicao AS hr  
    JOIN hColaborador AS hcb ON hcb.IdColaborador = hr.IdColaborador
    JOIN hStatus AS hs ON hs.IdStatus = hr.IdStatus     
    JOIN hDataLog AS hl ON hl.IdReq = hr.IdReq
    JOIN hInfoLogistica AS hi ON hi.IdInfoLogistica = hr.IdInfoLogistica
    WHERE hr.IdColaborador = @IdColaborador AND hr.IdStatus != 6
END 
IF @Action = 'SELECT'
BEGIN
    SELECT IdReq, hr.MotivoCriacao, hr.IdCodeRequest, hr.IdColaborador, Nome, hcb.Email, Projecto, Desenho, Indice, CadenciaMensal, NumCOMDEV, RefCliente, RefInterna, QTDLancamentoFormas, CapacidadeReal, Peso,
           TipoCaixa, QTDPecasCaixa, UnidadeProducao, CelulaProducao, NumKanbansProducao, QTDComp, TipoMetodologia, QTDMetProd, hi.Descricao AS InfoLog FROM hRequisicao AS hr
    JOIN hCodeRequest AS hcr ON hr.IdCodeRequest = hcr.IdCodeRequest
    JOIN hCaixa AS hc ON  hr.IdCaixa = hc.IdCaixa
    JOIN hComponentes AS hcp ON hr.IdComp = hcp.IdComp
    JOIN hMetodologiaProducao AS hmp ON hr.IdMetProd = hmp.IdMetProd
    JOIN hColaborador AS hcb ON hcb.IdColaborador = hr.IdColaborador
    JOIN hInfoLogistica AS hi ON hi.IdInfoLogistica = hr.IdInfoLogistica
    WHERE IdReq = @IdReq
END

I never used views and I really would like to try to apply them for once to get used to them and see how they work but I'm not confident yet when and how should i use them and if they are like just a place holder to hide the long code

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Jackal
  • 3,359
  • 4
  • 33
  • 78
  • 2
    They can provide a significant performance increase. I'll leave it to this famous answer, to provide a detailed explanation: https://stackoverflow.com/questions/439056/is-a-view-faster-than-a-simple-query At the end of the day, putting a query into a view and comparing performance takes a matter of minutes. I'd suggest just trying it out. – dylanthelion Jan 05 '19 at 00:27
  • Thanks i think i will try out this indexed view and see how it works – Jackal Jan 05 '19 at 00:33
  • 2
    Indexed Views may not be available in all versions of SQL Server, it used to be only in Enterprise Edition (and developer ed, for dev use only). That so called "famous" answer relies entirely on the existence of this feature and basically assume it is available You might want to also read: http://www.sqlservercentral.com/articles/editions/88074/ which shows you can get this feature to work in lower editions. – Paul Maxwell Jan 05 '19 at 01:22

0 Answers0