StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>First, why <code>DBCC DROPCLEANBUFFERS;</code> ? This is a hard cold reset of the buffer pool. Unless you want to measure and tune your hard drives IO performance, nobody cares about the performance of a cold cache. This is <strong>not</strong> how your system will work. Caching pages in the buffer pool is <em>the</em> most critical performance aspect in databases, and you take that out. Its like showing up in a Ferrari without the engine and asking why is so slow. For performance measurements you should do exactly the <strong>opposite</strong>: run he query 4-5 times to warm up the cache, then measure.</p> <p>Second, what is your table structure? Is the table <code>Account.Customer</code> table cluster index order by <code>Record_id</code>? If no, you will never get the performance you want, no matter how you express your T-SQL.</p> <p>And last but not least, what system do you have? Does it have enough RAM to cache the entire database in memory? If no, buy more RAM. Are there other processes that compete for memory, like IIS/Asp? If yes, kick them out to their own server, you should n<a href="https://stackoverflow.com/questions/1401834/when-can-i-host-iis-and-sql-server-on-the-same-machine/1429038#1429038">ever ever run the database on the same host as the web server</a> if performance is important.</p> <p>For an alternative fast paging consider keyset driven solutions:</p> <pre><code>/* moving up */ SELECT top(@Page_Size) * FROM Account.Customer WHERE Record_Id > @lastPageRecordId ORDER BY Record_Id; /* moving down */ SELECT top(@Page_Size) * FROM Account.Customer WHERE Record_Id < @firstPageRecordId ORDER BY Record_Id DESC; </code></pre> <p>A keyset driven solution can seek straight to the last position and then range scans the next/previous page, using the clustered index key position. The paging logic (state) must remember the last and first keys on the page being displayed in order to continue from there, instead of remembering the page number.</p> <p>Rowcount based solutions (as well as LIMIT in MySQL) are less efficient than keyset based ones because they always have to count the records to position themselves, instead of seeking straight to the position as keysets can.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload