I recently joined a company where the leadership announced plans to go public on NASDAQ within the next two years.
However, on my very first day, I discovered that their client-end tech stack is primarily based on C# WinForms.
I told myself, 'Alright, this is an opportunity to learn a new language.' I also noticed that the application lacks pagination functionality and relies on 'SELECT TOP 500' queries in SQL Server to manage data volume.
Faced with my first task to develop a page for checking historical data, I decided to introduce pagination to enhance the page's functionality.

Before writing the client code, I first reviewed the backend code. I noticed that although an abstract DAO class was defined with a queryPagination method, it was implemented using MySQL-related classes, like this:

@Configuration
public class PrimaryDaoConfig {

//...

@Bean
    @NoTrace
    public AbstractSqlAccess abstractDAO(){
        JdbcTemplate jdbcTemplate = primaryJdbcTemplate();
        NamedParameterJdbcTemplate namedParameterJdbcTemplate  = primaryNamedParameterJdbcTemplate();
        AbstractSqlAccess abstractDAO = new AbstractSqlAccess(){};
        abstractDAO.setJdbcTemplate(jdbcTemplate);
        abstractDAO.setNamedParameterJdbcTemplate(namedParameterJdbcTemplate);

        PaginationResolver paginationResolver = new MySQLPaginationResolver();
        paginationResolver.setJdbcTemplate(jdbcTemplate);
        paginationResolver.setNamedParameterJdbcTemplate(namedParameterJdbcTemplate);
        abstractDAO.setPaginationResolver(paginationResolver);
        return abstractDAO;
    }
}

Thus, I realized the need to refactor this abstract DAO class and create a new class related to SQL Server to implement the pagination method.

To return paginated data to the client-end from the backend, first, I need to count all the items. However, I found that it took over 3 seconds to count about 8000 rows in the database. Therefore, to figure out why it was so slow, I reviewed the SQL they had written. They used 5 tables to create a view, and then this view was also joined with another 3 tables. I haven’t checked the indexes yet, but I am planning to do so next week.

Alright, with such low performance, it's no wonder that no one has implemented pagination functionality in this system. It seems I won’t be able to implement it either until I solve this query performance issue.

==== Updated 2024/03/11 ====
Alright, I'm coming to update this blog and provide an ending.
First, I checked the table's index. They used a 50-character UUID as a primary key for every table, although some tables have other fields as indexes. That was very shocking to me.
Second, I wanted to check the resource occupation and the execution plan of SQL Server when running the query SQL, but my mentor refused to give me the authority to access the database server and told me, 'We just need to write APIs; we don't need to think about other things.'

So... I think I have to find a new job as soon as possible.