Sql-server – Speed up query that have joined tables and function calls

performancequery-performancesql server

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.

Best Answer

If you mark the functions as WITH SCHEMABINDING, then this may help - particularly if the function doesn't access any tables.