Random results in SQL queries without repeating (Scroll Infinity)

Asked

Viewed 546 times

2

I’ve had this problem for a long time and I can’t fix it.

I have a page with infinite scroll that returns some rows from the database, but the results most often repeat. I’m using the following query whenever the scroll reaches the bottom of the page:

SELECT TOP 10 * FROM table_name ORDER BY NEWID()

To remedy the situation I include a mask, which does not return the results already displayed on the page, for example:

SELECT TOP 10 * FROM table_name WHERE id NOT IN ($resultados_ja_exibidos) ORDER BY NEWID()

However there comes a time when the above query gets very slow due to the large number of ids that need to be checked before returning the results.

How do I optimize this function?

php sql-server optimizing rolling

  • I popularized an array with the results, for example, you show per page 10 results, I popularized an array with 100 results and of those 100, 10 would appear and so on, when the requests are over I would search for 100 more in the database and repeat the process of popular the array, I believe you would ease the access to the database and could make the comparison with the array itself (an array with history).

  • I also tried this. I populated an array with all the results, then passed it to javascript. I was riding every 10, but the only obstacle is that I depend 90% on customer performance. You can store in php and then access this array ?

  • Yes you can place an array in a Session

  • @Diegohenrique If at some point your query becomes slow, the problem is not in the query, it is very likely in the table design. Is the ID field indexed? It is or can be marked with unique ? That other fields pass in the portion WHERE of your consultation?

  • Yes the field is marked as UNIQUE, I make a INNER JOIN and has a few more parameters that I refer to such as: Status, expiration date...

  • 1

    The results repeat because every time requirement of 10 in 10 rows randomly. I use SQL SERVER 2008. There is no function LIMIT.

  • I usually sort the queries by id,save the last id and with it I make the pagination. Using id to sort I believe that solves your problem.flw

Show 2 more comments

1 answer

1

The select below handle of lines 1 to 20. Then just switch to the offset values of your paging to work:

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER ( ORDER BY uma_coluna_qualquer ) AS linha, *
    FROM      table_name 
) AS tabela_numerada
WHERE linha >= 1 AND linha < 20
ORDER BY linha

Best not to use NEWID(), because it does not guarantee the order. Use any field of the table.

Browser other questions tagged php sql-server optimizing rolling

You are not signed in. Login or sign up in order to post.