sql server partition table not performance in big tables?

Question

I have table with 35 millions rows. When I select rows as shown as that was not working and It takes a long time and eventually gives a memory error (my ram is 32GB).

I partitioned the table based on the monthly date and I have 64 table as shown as

But query not working again and It takes a long time and eventually gives a memory error . How can I do solve this problem?

Answer 1

This is basically your query:

select s.*
from student s
where s.id = (select max(si.id)
              from studentIndex si
              where si.family = s.name and si.name_ship = s.name_ship
             )    and 
      try_convert(numeric(38, 12), s.Length ) between 18.485411 and 23.51031 and
      try_convert(numeric(38, 12), s.Weight ) between 21.77079 and 77.13644 and
      s.time > 1594326600;

This is basically a full table scan, so I don't see why it would run out of member. The exception is the correlated subquery. For that, you need an index on studentIndex(family, name_ship) -- and to be sure that the columns are of the same time (otherwise, the index may not be used).

Your length and width comparisons look an awful lot like geographic coordinates. I might also suggest a GIS solution. However, if those are latitudes and longitudes, the area is pretty big, so GIS might not really be that helpful.