Handling a table with ~4.72 million records

[ghost]

I'm dealing with a table size I don't believe Serenity was meant to handle. Consequently, the table takes rather long to load (and that's only partial loading). Add in a few other necessary joins, and the connection will time out before any records are returned.

Is there any way to only load the records for a given page and not any page beyond?

Or are there any other ways to relieve the stress on the system?

[hubeyb]

Which database do you use?
I did not understand your issue clearly, bu I explain what I guess about your stuation.

Serenity grids uses server side paging. So you can select a part of data, max 2500 default.
and if there is no slowness at the server side, and you can get data with sql tool also you can get a part of data with serenity. But if there is database slowness issue like indexing or lock issue, you must resolve at databse side.

if you use SQL database, and the table has live usage, I reccomend that create a view with nolock for time out. You can get view data with Serenity Row decleration.

I hope this helps you.

[ghost]

Hey @hubeyb

Thank you for the response. I'm using a SQL Server database.

Can you elaborate more about creating a specific view? And how to get the view data in the row declaration?

My issue is that the query I need to do takes too long, so the server times out before I get any records back. I'm trying to get around that. Either by doing something different with Serenity or writing a query that returns fewer results.

[edwardch]

Serenity doesn't really care how big or small the table is and as @hubeyb said, it uses server side paging.
I see you had another issue regarding joins, so maybe a good idea would be to run SQL Server Profiler (available in Management Studio) to see what query hits the database when you try to load the grid.

Copy this query and paste it into a new query window, and run it manually. You might be able to optimise it here, and then add in those changes to your ReallyBigTableRow.cs file.

If you're working with large tables it's usually a good idea to use indexes, and make sure your primary/foreign keys are set up correctly.

Pro tip: when you're in your query editor press Ctrl+M to get an execution plan for your query - this can suggest indexes to add. The suggested indexes can be overkill so just check them carefully before adding them.

[ghost]

Hey @edwardch

Thanks for the added insight. I'm rather new to SQL Server Management Studio and its profiler, so I don't quite know how to interpret the information. Overall, the query I ran took 4:13, which is obviously unacceptable.

For the profile while the query ran, I saw a whole lot of batching, so I'm not sure where the database is taking the hit exactly.

Alternatively, the query works just fine when several filters are enabled, which is likely how it'll be used 95% of the time. As per #4400 I'm looking to remove the option of accessing the page when no filters are used. However, I'm also struggling with that.

Ideally, resolving the long query time is what I'd prefer though.

As for the indices, the developer before me looks to have already made the necessary indices across the database. If you're able to walk me through how to interpret the various tools provided by SQL Server Management Studio, that'd be much appreciated!

[ga5tan]

If it's slow directly in SQL studio, Serenity aint gonna make it faster.
As guys before said, you have to optimize. Which is also not exactly straightforward and requires some expertise (not as easy as instructions to change proxy in your browser)
You have to focus on the things, where execute plan takes longest. If you trim it down to minimum and it's still slow, you will have to NOT allow empty filter (I have such thing as well)
Guys also suggested you can make NOLOCK view, and generate entity out of it as well...
did you try if view is faster?

[brunobola]

Depending on the SQL Edition, you can enable partitioning to increase performance.
And for sure some indexes will make it faster if you have joins and searchable columns in your tables.

You can also use other ways to have the data paging with serenity by just using other components rather then slickgrid (Serenity grid).

[edwardch]

I'm curious, and here is something else that might help...

In the default list handler, two queries are called:

SELECT
    TOP (100)
    T0.A, T0.B, T0.C 
FROM ReallyBigTable T0
-- SOME JOINS
-- SOME CONDITIONS

Then

SELECT
    COUNT(*) 
FROM ReallyBigTable T0
-- SOME JOINS
-- SOME CONDITIONS

The first query is the one that actually returns data to your grid, and this is the one with the limit on it (using "TOP 100") so it will only query a small portion of the table.

The second query is just showing a record count, but there is no limit (it is "COUNT(*)") so it will probably be quite slow if you have a big table.

Can you try update your list handler in your ReallyBigTableRepository.cs file like this:

private class MyListHandler : ListRequestHandler<MyRow>
{
	protected override void ApplyFilters(SqlQuery query)
	{
		base.ApplyFilters(query);

		// Setting CountRecords to false stops the count(*) query from running
		query.CountRecords = false;
	}
}

Your grid will display the text "No records" at the bottom and you might want to change that later.

Let me know if this helps, at all?

[ghost]

Thanks guys for all the help! I realize this isn't exactly a Serenity issue anymore, so I appreciate everyone's willingness to help still!

After talking with my manager and the users, I think the way I'm going to tackle this problem is with a view that only returns records less than a year old.

I also really appreciate the education in databases. I definitely learned a lot from this thread.

[edwardch]

Before you try the view, did you try the CountRecords flag?

Then for your one year ago date filter you can do this in the list handler as well...

private class MyListHandler : ListRequestHandler<MyRow>
{
	protected override void ApplyFilters(SqlQuery query)
	{
		base.ApplyFilters(query);

		// Only get records within the last year
		query.Where(fld.YourDateField > DateTime.Now.AddYears(-1));
		
		// Setting CountRecords to false stops the count(*) query from running
		query.CountRecords = false;
	}
}

I'm interested to know if this helps (speed) and how much!

[ghost]

@edwardch Whoops, forgot to say that I did try setting the CountRecords flag to false and it didn't make a noticeable difference.

Haha, there is seriously a lot I don't know about Serenity, because I thought I was going to have to make a view. No joke Edward, you've been a huge driving force behind my progress in this project, so I seriously thank you.

Trying the date filter now...

[ghost]

@edwardch Similar to when I tried out the date where clause in SQL Server Manager, it takes roughly ~20 seconds to return all records within the past year. Not ideal, but it is actually returning them now!

[minhhungit]

I see that timeout is from count number of record query even we created index, not paging query.
So my suggestion is we should create a feature like infinitive loading, it will load new data when we scroll down, with that we won't care about record counter.

Btw, we have a quickfix to increase query timeout value but still waiting approval from @volkanceylan

[ghost]

@minhhungit I believe I saw something regarding your quickfix when doing research on my issue. Hopefully he approves it.

[ali-h2010]

How to show a loading spinner upon searching?