last day (15 days later) » 

Feeds
Feeds
15:59
0
Q: speed up referenced table order by and pagination

Steveassuming I have the following tables CREATE TABLE Item ( ID INT PRIMARY KEY IDENTITY(1, 1), Name VARCHAR(50) NOT NULL ) CREATE INDEX UX_Item_Name ON Item(Name) CREATE TABLE OnSale ( ID INT PRIMARY KEY IDENTITY(1, 1), ItemID INT FOREIGN KEY REFERENCES Item(ID), SomeProperty ...

sql-server sql-server-2016
Martin Smith
Martin Smith
Who is looking at the millionth result? Does this happen in practice?
Steve
Steve
@MartinSmith oh yes. Google bot. And whenever it does that it slows down everyone else.
Martin Smith
Martin Smith
can you use this approach instead of calculating it all from scratch each time? dba.stackexchange.com/a/224754/3690
Steve
Steve
@MartinSmith looks like that method requires a unique ordered id to be the anchor point. However I dont think I have such ID in my setup.
Martin Smith
Martin Smith
For the example table you have given here you would need to change the order by to ORDER BY Item.Name, Item.ID so it is deterministic in the event of ties and then pass both in to get the next 10 rows after that record
Steve
Steve
15:59
@MartinSmith what if OnSale table contains 100 rows with ItemID = 1 at 100 different locations? I dont mind if the order is not deterministic but it has to be able to get pass ItemID = 1 at some point
Martin Smith
Martin Smith
you need to order by enough columns to make the order fully deterministic. So probably adding locationid in for this different example
Steve
Steve
@MartinSmith thanks for the potential solution. I will give it more thought. I am starting to wonder if i should just stop the pagination after 10k since I highly doubt any human being would go thru that many pages.
Michael Green
Michael Green
sqlservercentral.com/articles/paging/69892
Joe Obbish
Joe Obbish
Why is a materialized view not an option?
Steve
Steve
@JoeObbish cuz my site has real time updating. Materialized view is killing insert/delete performance. Tried it once and it did not work out.
@MichaelGreen sorry forgot to mention. Already using the CTE method but performance still sux after 200k. I have decided to drop pages beyond 10k
smoore4
smoore4
15:59
can't you do this instead?? SELECT OnSale.ID, OnSale.ItemID, OnSale.SomeProperty, Item.Name FROM OnSale INNER JOIN Item ON Item.ID = OnSale.ItemID WHERE OnSale.ID IN (SELECT OnSale.ID FROM OnSale WHERE SomeProperty = 'Something') ORDER BY Item.Name OFFSET {x} ROWS FETCH NEXT 10 ROWS ONLY
Joe Obbish
Joe Obbish
@Steve Is it correct to say that a materialized view option without a lot of DML overhead would be acceptable? Although based on the comment that you made it sounds like you're no longer looking for a solution here? Should this question remain around?
Steve
Steve
@JoeObbish wouldnt hurt to hear about potential solutions. And yes if materialized view option without having to sacrifice DML performance is acceptable. I do have like couple tens of thousands of concurrent inserts to the OnSale table per min and quite some delete as well.
@smoore4 that kills the purpose of CTE. You can read more about it by following the link provided by Michael
Joe Obbish
Joe Obbish
@Steve Can you add additional details to the question about the indexed view that you tried? I'm trying to get a better sense of why it didn't work.
Steve
Steve
@JoeObbish sorry I dont have the details anymore. Tried it a while ago and it didnt work out. Basically the indexed view only contain one join to the Item table. And I moved all the search related indexes to the indexed view. However it did not take long till the insert/update/delete lags everything out.

last day (15 days later) »