Reputation: 3520

Best way to implement sort, search & pagination with Redis for maximum performance

I have large data approx 1,00,000 for employee. I have stored this data to one Redis key called "employess". Now there is one screen where I would like to perform search on some field & sort on each column along with pagination.

So for that I have created following code that works fine. but it takes time around 1.2 seconds to 2 seconds average.

I would like to reduce it to 200 milliseconds ( requirement )

Can somebody guide me how can I achieve that performance or what I am doing wrong in following code.

I am working with C# code & ServiceStack.Redis client. I am free to use any other Redis client if requires.

public class Employee
    {
        public int EmployeeId { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public DateTime DOB { get; set; }
        public char Gender { get; set; }
        public string Street { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Zip { get; set; }
        public string Department { get; set; }
        public string Occupation { get; set; }
        public decimal Salary { get; set; }
    }

// Methods that handles sort,search,paging & getting data form Redis.

  private GeneralResponse<IEnumerable<Employee>> GetEmp(SearchParam filter, int initialPage, int pageSize, out int totalRecords, out int recordFilterd,
           int sortColumn, string sortDirection)
        {
            var response = new GeneralResponse<IEnumerable<Employee>>();
            totalRecords = 0;
            recordFilterd = 0;

            try
            {
                var data = Enumerable.Empty<Employee>().AsQueryable();
                try
                {
                    using (var redisClient = new RedisClient(Common.redisUrl, Common.redisPort))
                    {


                        var rdata = redisClient.Get<IEnumerable<Employee>>("employess");
                        data = rdata.AsQueryable();
                        ViewBag.source = "redis";
                    }
                }
                catch (Exception e)
                {
                    data = Common.EmployeesList.AsQueryable();
                    ViewBag.source = "Database";
                }


                totalRecords = data.Count();
                //filter 
                if (!string.IsNullOrWhiteSpace(filter.FirstName))
                {
                    data = data.Where(x =>
                        x.FirstName.ToLower().Contains(filter.FirstName.Trim().ToLower())
                    );

                }
                if (!string.IsNullOrWhiteSpace(filter.LastName))
                {
                    data = data.Where(x =>
                        x.LastName.ToLower().Contains(filter.LastName.Trim().ToLower())
                    );
                }
                if (!string.IsNullOrWhiteSpace(filter.Department))
                {
                    data = data.Where(x =>
                        x.Department.ToLower() == filter.Department.Trim().ToLower()
                    );
                }
                if (filter.FromDob != null && filter.FromDob != default(DateTime))
                {
                    data = data.Where(x => x.DOB >= filter.FromDob);
                }
                if (filter.ToDob != null && filter.ToDob != default(DateTime))
                {
                    filter.ToDob = filter.ToDob.AddHours(23).AddMinutes(59);
                    data = data.Where(x => x.DOB <= filter.ToDob);

                }
                recordFilterd = data.Count();

                //sort 
                var ascending = sortDirection == "asc";
                switch (sortColumn)
                {
                    case 0:
                        data = data.OrderBy(p => p.EmployeeId, ascending);
                        break;
                    case 1:
                        data = data.OrderBy(p => p.LastName, ascending);
                        break;
                    case 2:
                        data = data.OrderBy(p => p.FirstName, ascending);
                        break;
                    case 3:
                        data = data.OrderBy(p => p.DOB, ascending);
                        break;
                    case 4:
                        data = data.OrderBy(p => p.Gender, ascending);
                        break;
                    case 5:
                        data = data.OrderBy(p => p.Street, ascending);
                        break;
                    case 6:
                        data = data.OrderBy(p => p.City, ascending);
                        break;
                    case 7:
                        data = data.OrderBy(p => p.State, ascending);
                        break;
                    case 8:
                        data = data.OrderBy(p => p.Zip, ascending);
                        break;
                    case 9:
                        data = data.OrderBy(p => p.Department, ascending);
                        break;
                    case 10:
                        data = data.OrderBy(p => p.Occupation, ascending);
                        break;
                    case 11:
                        data = data.OrderBy(p => p.Occupation, ascending);
                        break;
                    default:
                        data = data.OrderBy(p => p.Salary, ascending);
                        break;
                }

                data = data
                    .Skip(initialPage * pageSize)
                    .Take(pageSize);

                var result = data.ToList();
                response.Data = result;

            }
            catch (Exception e)
            {
                response.Error = true;
                response.Exception = e;
            }
            return response;
        }

Any help or guidance will be really appreciated. following is the reference screen where I wanted to achieve the speed.

Upvotes: 3

Answers (3)

Rolland Csatári

Reputation: 11

Hello if you add for each indexed document an integer int_id you can execute the following query : '@int_id:[1 10]'. This query select all documents between 1-10.

more infos: https://oss.redislabs.com/redisearch/Query_Syntax.html

Upvotes: 1

Austin Andrews

Reputation: 50

As was mentioned above in comments and in your answer, redis may not be the best option for this problem, and AWS CloudSearch could be the solution.

When solving a similar issue, I found that my biggest bottleneck was using 'query.Where(x => x.String.Contains(filterText))`.

Adding a full text index onto text-searched columns (first/last name for your solution) allowed us to go to SQL directly with satisfactory performance (100-200ms for a ~1 million row table). https://www.mssqltips.com/sqlservertutorial/9136/sql-server-full-text-indexes/

Upvotes: 1

Kaushik Thanki

Reputation: 3520

After deep research, I finally found that Redis is not good option for performing these operation. Rather than this we can go for AWS CloudSearch which has full capabilities for Sort,Search & Pagination.

Upvotes: 1

Best way to implement sort, search &amp; pagination with Redis for maximum performance

Answers (3)

Related Questions

Best way to implement sort, search & pagination with Redis for maximum performance