Too much response time in a Sql Server procedure

Question

Too much response time in a Sql Server procedure

Navigation

#1 by (1 votes)
#2 by (-1 votes)

0

I have the following code in a procedure:

DECLARE @Temp_Entidades TABLE(Id int);
with Report(Id) as 
  (
  --primer select
  select distinct e.Id 
  from  EntidadesPersonas as ep 
        right join Entidades as e on ep.EntidadId=e.Id 
  where (ep.PersonaId in (select Id from @Temp_Prom) or ep.PersonaId is null) 
    and e.EstadoId <> 62 
    and e.Principal <> 1 
and e.UniversidadId = @p_EmpresaId )
insert into @Temp_Entidades select Id from Report;

--Segundo select
select distinct p.Nombres
from Personas as p
  inner join Estados as e on p.EstadoId=e.Id
  inner join EntidadesPersonas as ep on p.Id=ep.PersonaId
where ep.EntidadId in (select Id from @Temp_Entidades)
  and p.EstadoId<>69

I happen to get a total of 145,000 data in @Temp_Entidades ( and I'm using distinct , I need it ) and I also have a total of approximately 15,500 data in the Personas table; in the second select the response time of my procedure extends too much.

Any tips or suggestions on how I can optimize this query?

sql sql-server stored-procedure

asked by Vitmar Aliaga 21.10.2016 в 21:06

source

2 answers

Protect routes with passport laravel and Dingo Api Cordova plugin media error when running multiple audios

score 1 · Answer 1

While we wait for you to add more pertinent and important details to your question, I propose some concrete suggestions.

Solve problems due to the use of a variable of type table with many records

It happens that I get a total of 145,000 data in @Temp_Entidades

Using @Temp_Entidades with so much data in your second query can definitely cause you performance problems, for at least 2 potential reasons:

Your second query would probably find a better execution plan if @Temp_Entidades had an index for the column Id , but does not have it.

One way to define an index in the case of a variable of type table , as it is @Temp_Entidades , is to define it implicitly by means of a primary key:

DECLARE @Temp_Entidades TABLE(Id int primary key);

Another very serious problem is that when a variable of type table is used in a query, by default SQL Server does not have statistics regarding that table. Rather, SQL Server simply assumes that the table (in this case @Temp_Entidades ) only has one record (you can verify this by examining your current execution plan). This, in turn, can lead you to choose a completely wrong execution plan for the query, since the table actually has 145,000 records, not just one.

For SQL Server to access statistics for @Temp_Entidades , you have 2 options:

Use a temporary table instead of a variable of type table :

create table #Temp_Entidades (Id int primary key);

Or add the suggestion option (recompile) to the query:

select distinct p.Nombres
from Personas as p
  inner join Estados as e on p.EstadoId=e.Id
  inner join EntidadesPersonas as ep on p.Id=ep.PersonaId
where ep.EntidadId in (select Id from @Temp_Entidades)
  and p.EstadoId<>69
option (recompile) -- agregar aquí

Review indexes

Of course, you may have to check if you have the right indexes in the other tables. It is difficult to give you correct recommendations without 100% clear the relationship between the tables and the number of records in each. But, from what I can deduce, it makes sense to define the following indices:

Estados (Id)
Personas (Id)
EntidadesPersonas (EntidadId)
EntidadesPersonas (PersonaId)
@Temp_Entidades (Id)

Formulate the query differently

Personally, I would formulate the query a bit differently, for example, using exists instead of in . But in practice, I doubt that something changes, since, in general, SQL Server has the intelligence necessary to make those kinds of modifications automatically if you see it beneficial.

But just in case, here is the query as I would formulate it in this case:

select distinct p.Nombres
  from Personas p
  join Estados e
    on e.Id = p.EstadoId
  join EntidadesPersonas ep
    on ep.PersonaId = p.Id
   and exists(select null
                from @Temp_Entidades te
               where te.Id = ep.EntidadId)
 where p.EstadoId <> 69
option (recompile)

Delete intermediary results between queries

Again, it is obvious that important details are missing in your question. For example, where does @Temp_Prom come from?

But, from what I can see, I get the impression that a single SQL query would have been enough to get the desired final results. But you have divided it into at least 3 smaller queries using variables of type table to load the temporary results of each query and pass it to the next one. Why? Was it about trying to improve performance?

In my opinion, it would be worth examining if you can not improve performance by combining the different queries. As long as you have the right indexes defined, I suspect that is the best option. But to help you with this, you need to add the missing details to your question.

score -1 · Answer 2

-1

Create indexes in the corresponding column (s), either from the Personas , Estados and / or EntidadesPersonas . Create unique indexes

answered by 21.10.2016 в 23:27