Click here to Skip to main content
15,434,219 members
Home / Discussions / Database
   

Database

 
GeneralRe: Billion rows Pin
jschell17-Mar-18 5:58
Memberjschell17-Mar-18 5:58 
GeneralRe: Billion rows Pin
Mycroft Holmes17-Mar-18 13:18
professionalMycroft Holmes17-Mar-18 13:18 
GeneralRe: Billion rows Pin
jschell24-Mar-18 6:14
Memberjschell24-Mar-18 6:14 
GeneralRe: Billion rows Pin
Mycroft Holmes24-Mar-18 13:08
professionalMycroft Holmes24-Mar-18 13:08 
GeneralRe: Billion rows Pin
jschell31-Mar-18 7:01
Memberjschell31-Mar-18 7:01 
GeneralRe: Billion rows Pin
Mycroft Holmes31-Mar-18 13:40
professionalMycroft Holmes31-Mar-18 13:40 
GeneralRe: Billion rows Pin
Jörgen Andersson31-Mar-18 23:13
professionalJörgen Andersson31-Mar-18 23:13 
GeneralRe: Billion rows Pin
jschell6-Apr-18 10:03
Memberjschell6-Apr-18 10:03 
AnswerRe: Billion rows Pin
Jörgen Andersson11-Mar-18 22:56
professionalJörgen Andersson11-Mar-18 22:56 
GeneralRe: Billion rows Pin
jschell14-Mar-18 15:15
Memberjschell14-Mar-18 15:15 
GeneralRe: Billion rows Pin
Jörgen Andersson14-Mar-18 22:17
professionalJörgen Andersson14-Mar-18 22:17 
Then I believe I have enough info to recommend you to not use pagination the way you do.

SQL
SELECT * FROM tbl LIMIT 2000,10;
is an extraordinary inefficient query.
It's internally functioning like this pseudocode:
SQL
SELECT  BOTTOM 10 *
FROM    (
    SELECT  TOP (2000+10) *
    FROM    T
    )
In short it selects the 2010 first rows to just throw away the first 2000 rows.

If you can store the max(ID) from the previous page I'd recommend trying this instead
SQL
SELECT  id
       ,RefId1
       ,RefId2
       ,...
FROM    T
WHERE   id > @PreviousMaxID
ORDER BY id
LIMIT @PageSize
You obviously need to have an index on the id column for this to be fast.
Wrong is evil and must be defeated. - Jeff Ello

GeneralRe: Billion rows Pin
jschell17-Mar-18 5:52
Memberjschell17-Mar-18 5:52 
GeneralRe: Billion rows Pin
Jörgen Andersson17-Mar-18 7:39
professionalJörgen Andersson17-Mar-18 7:39 
GeneralRe: Billion rows Pin
jschell24-Mar-18 6:18
Memberjschell24-Mar-18 6:18 
GeneralRe: Billion rows Pin
Jörgen Andersson24-Mar-18 8:18
professionalJörgen Andersson24-Mar-18 8:18 
GeneralRe: Billion rows Pin
jschell31-Mar-18 6:56
Memberjschell31-Mar-18 6:56 
GeneralRe: Billion rows Pin
Jörgen Andersson31-Mar-18 23:25
professionalJörgen Andersson31-Mar-18 23:25 
QuestionIdentity behavior in sql server 2008 and 2012 Pin
VK197-Mar-18 10:02
MemberVK197-Mar-18 10:02 
SuggestionRe: Identity behavior in sql server 2008 and 2012 Pin
CHill608-Mar-18 8:04
mveCHill608-Mar-18 8:04 
AnswerRe: Identity behavior in sql server 2008 and 2012 Pin
Richard Deeming8-Mar-18 9:14
mveRichard Deeming8-Mar-18 9:14 
PraiseRe: Identity behavior in sql server 2008 and 2012 Pin
CHill608-Mar-18 9:37
mveCHill608-Mar-18 9:37 
GeneralRe: Identity behavior in sql server 2008 and 2012 Pin
VK199-Mar-18 7:43
MemberVK199-Mar-18 7:43 
AnswerRe: Identity behavior in sql server 2008 and 2012 Pin
jschell10-Mar-18 10:30
Memberjschell10-Mar-18 10:30 
GeneralRe: Identity behavior in sql server 2008 and 2012 Pin
VK1912-Mar-18 2:27
MemberVK1912-Mar-18 2:27 
GeneralRe: Identity behavior in sql server 2008 and 2012 Pin
jschell14-Mar-18 15:16
Memberjschell14-Mar-18 15:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.