Re: sliced find in Linq

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Marc Gravell wrote:
2) use an INSERT INTO #temptable <normal select> query where you
insert a TOP (pagesize * (pagenumber+1) into the SELECT, so you
only insert the data you need at most, not the big resultset
A small note here is that this gets expensive when the user clicks on
the "last page" (page 11234 of 11234) button; I know that you know
this - I'm just saying...

Yes, that's a problem.
However, a developer of software which offers thousands of pages of
result to a request should ask him/herself this: will the user traverse
tens of thousands or even millions of rows manually? No, in almost all
cases, the user won't. So offering thousands of pages is likely not
going to help much. ;)

BUt I agree, it's worth mentioning and can bring down a server
unfortunately. However, as SqlServer is the only RDBMS system without
proper keywords in the SELECT statement for skip/take/paging, I think
it's more a problem of the vendor of the db system. E.g. picking oracle
or even mysql won't give you this problem ;) (ok, also not linq to sql
;)

4) optional: drop the temptable.
In a stored-procedure, yes - but isn't that mandatory in a flat
command text approach?

Temptables are dropped at the connection closing statement. This might
be right after the command, but it can also be that there will be other
commands over the same connection. This means that the temptable used
will occupy space in the tempdb catalog's data file. So dropping it
actively after the usage of the fetch could help performance :)

perhaps with an in-memory table type
Generally, yes; but for larger volumns sometimes temp-tables are
preferable. Depends on the scenario, so very hard to write a generic
handler...

yes, if you want truly the best performance for every scenario, you're
not going to cut it with temptables. They're the 'best' choice for a
generic scenario though, but not ideal...

FB


--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
.

Relevant Pages