0

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.

  • 4
    800K rows multiplied by 9 function calls per row, that's about 7M function calls. No wonder it's slow. For the rest, I agree with @Paparazzi. Where is the table definition, where is the search, where is the pagination? You can do pagination in SQL Server all right but you have to give us some clue about what you want. If the sorting and pages are to be decided upon the results of the function calls, then I don't see any easy way to make this fast. – ypercubeᵀᴹ Jun 2 '16 at 9:55
  • 1
    I have voted this for reopening despite that we still don't know the actual query, i.e. neither the actual functions and the possibly complicated Selects in there nor what the pagination is supposed to do. – ypercubeᵀᴹ Jun 3 '16 at 8:26
  • Could you use just the item table to find the correct page of keys into a temp table, then call the functions for just that page's rows? – Michael Green Jun 5 '16 at 10:10
  • @MichaelGreen but the search was not just on the Item table, it was on the return value of function calls too. – Ngoc Jun 7 '16 at 12:40
  • You still haven't told what the pagination/ordering is based on. Is it on columns of the tables or on the results of the function calls? – ypercubeᵀᴹ Jun 7 '16 at 13:08
1

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

| improve this answer | |
  • unfortunately, the function calls involve SELECT on other tables. – Ngoc Jun 3 '16 at 6:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.