April 27, 2022 at 5:41 pm
Hello,
Is it correct that MS SQL automatically retrieves data to optimize the "SELECT" statements?
Example: If I have a table with 1 million records and I run SELECT for the first 1000 records, then for the next 1000 records and so on....
Is it right that MS SQL recognizes the pattern and preprocesses the next 1000 records even before I ask for it ? kind of "guessing" what will probably be my next step....
Thx
April 27, 2022 at 8:24 pm
Maybe it will. SQL can pre-fetch data. It depends on the specifics of the situation, including your edition of SQL Server.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 28, 2022 at 12:35 pm
Here is the Microsoft documentation on the subject: https://docs.microsoft.com/en-us/sql/relational-databases/reading-pages?view=sql-server-ver15
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 5, 2022 at 5:54 pm
It sounds like you're doing an equivalent to SELECT * FROM MyTable ORDER BY PK OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY, followed by SELECT * FROM MyTable ORDER BY PK OFFSET 1000 ROWS FETCH NEXT 1000 ROWS ONLY later, and you're asking if you do this a few times, if SQL Server will pre-load the next 1000 rows after that, in case you ask for it. Does that sound right? To the best of my understanding, it doesn't deliberately load the data to make that specific use case happen. It will load into the buffer the data it needs for the 1000 rows you asked for, but because it has to load entire pages, it might pick up extra records on the pages it needs that aren't in the 1000 you just asked for. When doing read-ahead reads, it can read entire extents (8-page blocks), which might contain plenty of records you didn't ask for. In my example, assuming MyTable had a clustered index on [PK], the extra rows read would be "next" in line, so it could in effect be preloading some of the next rows you might ask for. However, it didn't do it because it's anticipating your need, they just came along for the ride with the data it knows it needs right now.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply