Re: Is there any way to optimize count(*)

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

From: David Browne (meat_at_hotmail.com)
Date: 07/06/04 

Date: Tue, 6 Jul 2004 08:20:38 -0500

"Casper Hornstrup" <msdn@csite.com> wrote in message
news:%23OFhco1YEHA.3128@TK2MSFTNGP09.phx.gbl...
> I do paging on a table with products. Selecting a page in an resultset is
> fast (about 20ms).
> Selecting the total number of results in the resultset is however very
slow.
> SELECT COUNT(*) FROM Product WHERE ...
> This takes 2 seconds to perform. Can I do something to optimize this?
>

Check the execution plan.

If all of the columns referenced in the WHERE clause are in an index, then
SELECT COUNT(*) WHERE should be pretty cheap. Otherwise it will require a
table scan or an index scan followed by bookmark lookups.

David

Relevant Pages

  • Re: Is there any way to optimize count(*)
    ... If you need some help selecting them, please post DDL, sample data, and your ... full query. ... Selecting a page in an resultset is ...
    (microsoft.public.sqlserver.programming)
  • Re: Paging, Filtering and Sorting
    ... The reason I want to use the paging is because of the number of records. ... only show a chunk at a time in the grid. ... > like to be able to load pages into the grid. ... > entire resultset into the dataset and pick up the next block of results as ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Accessible GridView
    ... selecting, sorting, and paging). ... are there other controls that could provide this ...
    (microsoft.public.dotnet.framework.aspnet.webcontrols)
  • RE: how do you slow down the speed of paging when selecting text
    ... When you cut are you trying to drag the selected text to paste, ... Try to cut the chosen text, then scroll at ... > When selecting text for a cut and paste, and the text exists over several ... the paging goes to quickly to stop where I want it to. ...
    (microsoft.public.word.docmanagement)
  • Re: Modifying ResultSetTableModel not Database
    ... > anyone know how to modify a resultSet or tablemodel so that it changes ... ResultSet interface does not preclude the option of a connected ... Generally speaking you should assume that paging the resultset is actually ...
    (comp.lang.java.gui)