I have a Bootstrap Table that use server side pagination to display results from a query like this:
SELECT Item.a, Item.b, Item.c, Info1.Name, Info1.Name, Info2.Name, Info3.Name, Info4.Name, Info5.Name, Info6.Name, Info7.Name, Info8.Name, Info9.Name, dbo.CalculateSomeText1(Item.Id) AS Text1 dbo.CalculateSomeText2(Item.Id) AS Text2 dbo.CalculateSomeText3(Item.Id) AS Text3 dbo.CalculateSomeText4(Item.Id) AS Text4 dbo.CalculateSomeText5(Item.Id) AS Text5 dbo.CalculateSomeText6(Item.Id) AS Text6 dbo.CalculateSomeText7(Item.Id) AS Text7 dbo.CalculateSomeText8(Item.Id) AS Text8 dbo.CalculateSomeText9(Item.Id) AS Text9 FROM Item LEFT JOIN Info1 ON Item.Id1 = Info1.Id LEFT JOIN Info2 ON Item.Id2 = Info2.Id LEFT JOIN Info3 ON Item.Id3 = Info3.Id LEFT JOIN Info4 ON Item.Id4 = Info4.Id LEFT JOIN Info5 ON Item.Id5 = Info5.Id LEFT JOIN Info6 ON Item.Id6 = Info6.Id LEFT JOIN Info7 ON Item.Id7 = Info7.Id LEFT JOIN Info8 ON Item.Id8 = Info8.Id LEFT JOIN Info9 ON Item.Id9 = Info9.Id
The table offers a 'master search' that allow user to enter some text and then retrieve records that have that text in any column. (And then there's a filter for each column).
Anyway, the table was fairly large (800K rows) and the raw query above took about 4 minutes.
Is there any trick I could put in here to speed up the thing? If not, do I have to do the search again everytimes user click [next page]? (I'm afraid caching the whole resultset may kill my application)
I figured that searching on function returns requires the function to be executed for each rows which make it worse. But I guess it could not be helped, the function may not be deterministic.
Info1,.. Info9 were just examples. The point is the table Item has a lot of 1-Many references.
The main table is Item, the query above is the raw that retrieve the data, I planned on doing the pagination in application code rather than in SQL (it doesn't support SKIP anyway). If you have any suggestion on paging with SQL, please, I'm all ears.
The main table definition is
Item( Id int PK, i1 int, i2 int, .. i9 int, s1 nvarchar(50), s2 nvarchar(50), s3 nvarchar(50), ... s20 nvarchar(50), Id1 int FK -> Info1, Id2 int FK -> Info2, ... Id9 int FK -> Info9, )
The other Info table is fairly simple. They're just for reference. The point is, the main table is large with many text fields, none of them has index (except the keys). And the query is supposed to get all of the field, I was trying to simplify the query for easier understanding.
Most of the function calls involve
SELECT on other tables. These functions belong to the legacy system that I have no power over. Too long, too complicated to touch anyway.
What I try to achieve is a way to speed up this query. A general idea is fine. I hope it's clear enough.
I'm trying out an indexed view for this, once I get it going I'll show you guys the result if it actually helps at all.