title: How to optimize a select query in SQL Server?
body: <p>I'm making a website that retrieves data from a table and displays it to the user. To do this I created a view on my Database that joins the main table "Historics" with other tables by id to get some columns names instead of the id. However, when I do "SELECT * FROM VIEW", the query never charges. I've tried to run it in the database, and it takes 16 minutes to retrieve approximately 4 millions of rows.</p>
  
  <p><img src="https://i.imgur.com/C4LTIPk.jpg" alt="time">
  As you can notice I'm not an expert with SQL. I have tried to create Non-clustered indexes in the columns that I am using for the joins, and also I created one index for all of the columns at the same time, but none of this seems to work. (The indexes that I created are on the original table, not in the view)</p>
  
  <p>Here is how I am creating the view. I select the columns that I want to show and then I make left outer joins to get the names of the columns by id.</p>
  
  <pre><code>DROP VIEW BIHistoricoEventos
  GO
  CREATE VIEW BIHistoricoEventos AS
  SELECT        BI.HISTORICO_EVENTOS_CERRADOS.COD_EVE, 
                BI.HISTORICO_EVENTOS_CERRADOS.FEC_NOT, 
                BI.HISTORICO_EVENTOS_CERRADOS.SEMANA, 
                BI.HISTORICO_EVENTOS_CERRADOS.ANO, 
                BI.HISTORICO_EVENTOS_CERRADOS.COD_PRE, 
                BI.HISTORICO_EVENTOS_CERRADOS.COD_SUB, 
                BI.HISTORICO_EVENTOS_CERRADOS.COD_DPTO_O, 
                BI.HISTORICO_EVENTOS_CERRADOS.COD_MUN_O, 
                BI.HISTORICO_EVENTOS_CERRADOS.COD_ASE, 
                BI.HISTORICO_EVENTOS_CERRADOS.AJUSTE, 
                TipoEvento.nom_eve AS 'Nombre evento', Departamentos.Nom_dpto 
                AS 'Departamento', 
                Municipios.Nom_mun AS 'Municipio', 
                Ajustes.nombre AS 'Nombre Ajuste', Aseguradoras.Raz_soc AS 
                'Razon social aseguradora'
  FROM         BI.HISTORICO_EVENTOS_CERRADOS LEFT OUTER JOIN
               Departamentos ON BI.HISTORICO_EVENTOS_CERRADOS.COD_DPTO_O = 
                    Departamentos.Cod_dpto LEFT OUTER JOIN
               Municipios ON CONCAT(BI.HISTORICO_EVENTOS_CERRADOS.COD_DPTO_O, 
                    BI.HISTORICO_EVENTOS_CERRADOS.COD_MUN_O)  = 
                    Municipios.Cod_mun LEFT OUTER JOIN 
               Aseguradoras ON BI.HISTORICO_EVENTOS_CERRADOS.COD_ASE = 
                    Aseguradoras.Cod_ase LEFT OUTER JOIN
               TipoEvento ON BI.HISTORICO_EVENTOS_CERRADOS.COD_EVE = 
                    TipoEvento.cod_eve LEFT OUTER JOIN 
               Ajustes ON  BI.HISTORICO_EVENTOS_CERRADOS.AJUSTE = 
                    SUBSTRING(Ajustes.nombre, 1, 1)
  GO
  </code></pre>
  
  <p>I made indexes on each column present in the joins, but it still takes so long. 
  I also don't know for sure if the indexes should be on the original table or on the view.</p>
  
  <p>I would like to reduce the time that the query takes, I'm looking forward to your replies. Thank you.</p>
  
  <p>EDIT: Here is the execution plan.. But currently I don't know how to optimize this. 
  <img src="https://i.ibb.co/NysYKVQ/execution-plan.png" alt="exec plan">
  Here is the link for the image <a href="https://i.ibb.co/NysYKVQ/execution-plan.png" rel="nofollow noreferrer">https://i.ibb.co/NysYKVQ/execution-plan.png</a></p>