I have a table with more than 100 thousand records and I must get only 15 records (Not the last 15)
This query takes 00:01:45 (1 minute 45 seconds) to show me the information.
select a.Silo, (select top 1 level from silos b where
b.silo=a.silo order by b.insertdate desc ) as Nivel,
(select top 1 PERCENTAGE from silos c where c.silo=a.silo order
by c.insertdate desc ) as Porcentaje,
(select top 1 InsertDate from silos d where d.SILO = a.SILO
order by d.InsertDate desc) as Fecha
from silos a where substring(a.silo,1,4)='Silo'
group by a.silo
order by CONVERT(INT,SUBSTRING(silo,5,LEN(Silo)))
It is in the information that it displays
Is there a way to improve the query so that it runs faster?
The table only has those fields.
It is not necessary to perform sub-queries to achieve the result. You can easily achieve the same result by first getting the max date
InsertDate
of eachSilo
, and then doing ajoin
with your fact table.Something like:
It will return the result reasonably quickly as long as an index exists like this:
This is the option I usually use to get the first value of each group. I give each element a row number by partitioning by group and then just select row number 1.
I think it would be important for you to consider splitting your column
silo
so that you don't store composite values and all columns have atomic values. That will help you improve performance in the long run. You can keep the columnsilo
as a computed column.If it's only 15 records and the top 1 for each record, it's easier and faster to get the queries individually. For example:
With the following result:
The query is rudimentary and primitive, yes. But it returns the desired result and at a lower cost. Can it be improved? Yes, but the relational design of the tables needs to be improved:
substring(a.silo,1,4)='Silo'
you nullify the index, because you have to apply the substring function to the whole table.If the idea is to fetch 15 records, one for each silo, as long as they are not the last records, you can use the function
max
:With the following results:
In this way you get the most recent date and therefore the first records.
This is the fiddle in SQL SERVER.