COUNT(*) with WHERE variables too slow with 460 000 records table

  • Is there a way to do COUNT(*) to run faster or rewrite alternative which will count number of records for particular select. It is SELECT with 6 joined tables and 460 000 records.

    Running search without count(*) is 7ms, with count(*) it jumps up to 360ms ... Drastic difference, however I need to know number of records for particular search.

    Thanks

  • i use this when i need a fast result.

    SELECT

    o.name AS [Name],

    i.rowcnt AS [Count]

    FROM sysobjects o

    JOIN sysindexes i ON i.id = o.id AND indid IN(0,1)

    WHERE xtype = 'u'

    order by i.rowcnt desc

    it is dependent on your statistics being up to date.

  • A little more description if you could between the without/with count differences? I'm trying to grok what the difference in the queries would be, count(*) shouldn't cause that much of a difference in the plans.

    If you can, attach the two execution plans as well, the devil might be in the details.

    In general... 360 ms for a half a million row count doesn't seem that outrageous to me. 7 ms to return 460,000 rows seems insanely quick, thus why I'd like to know more specifics.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • tjaybelt (4/21/2011)


    i use this when i need a fast result.

    SELECT

    o.name AS [Name],

    i.rowcnt AS [Count]

    FROM sysobjects o

    JOIN sysindexes i ON i.id = o.id AND indid IN(0,1)

    WHERE xtype = 'u'

    order by i.rowcnt desc

    it is dependent on your statistics being up to date.

    I agree TJ if you're looking for table counts, but he's trying to determine the counts off a joined structure.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • You'll have to provide some more information. A query which counts records shouldn't take longer than the equivalent query that returns them all.

    The best ways to speed it up would be to consider indexes, and perhaps considering outer joins with foreign keys. But if you write your queries here, people will be able to help you more.

    Rob

    Rob Farley
    LobsterPot Solutions & Adelaide SQL Server User Group
    Company: http://www.lobsterpot.com.au
    Blog: http://blogs.lobsterpot.com.au

  • I'm going to guess that you only return the first page of data in 7ms, and that this allows a massive shortcut in processing.

    Rob Farley
    LobsterPot Solutions & Adelaide SQL Server User Group
    Company: http://www.lobsterpot.com.au
    Blog: http://blogs.lobsterpot.com.au

  • Here is the code for COUNT:

    select @pocet=COUNT(i.id)

    from vwShowInzeraty i

    left join vwShowMotorizacia m on i.id_motorizacia = m.id

    inner join vwShowKategorie k on i.id_kategoria = k.id

    where

    (@id is null or i.id = @id)

    and (@id_kategoria is null or k.position.ToString() like @kategoria_text + '%')

    and (@id_znacka is null or i.id_znacka = @id_znacka)

    and (@id_model is null or i.id_model = @id_model)

    and (@id_karoseria is null or i.id_karoseria = @id_karoseria)

    and (@id_palivo is null or i.id_palivo = @id_palivo)

    and (m.vykon between @vykon_od and @vykon_do)

    and (m.vykon_ps between @vykon_ps_od and @vykon_ps_do)

    and (m.objem between @objem_od and @objem_do)

    and (i.rok_vyroby between @rok_vyroby_od and @rok_vyroby_do)

    and (i.najazdene between @najazdene_od and @najazdene_do)

    and (i.cena between @cena_od and @cena_do)

    and (@user is null or i. = @user)

    Without this COUNT select whole stored procedure returns PAGED data within 55ms (20 records found with WHERE conditions).

    With this COUNT select it returns data within 288ms.

    Is it possible? Or this is closest I can get with this kind of table.

  • I'm guessing there's some nasty logic in those views.

    But still, to find twenty rows that satisfy those conditions is going to be a lot quicker than counting how many there are in total.

    Rob Farley
    LobsterPot Solutions & Adelaide SQL Server User Group
    Company: http://www.lobsterpot.com.au
    Blog: http://blogs.lobsterpot.com.au

  • You can also use COUNT(1) instead of COUNT(*) or COUNT(<colname>).

  • Using COUNT(1) instead of COUNT(*) won't change the situation.

    Rob Farley
    LobsterPot Solutions & Adelaide SQL Server User Group
    Company: http://www.lobsterpot.com.au
    Blog: http://blogs.lobsterpot.com.au

  • Hello catch-all query (otherwise known as performance nightmare)

    http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

    Count(1) is the same as count(*), in meaning and performance. Count(<column name>) is not the same in meaning and can only perform the same speed or slower than count(*)

    http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Try in the end of complete query

    OPTION (MERGE JOIN)

    or

    OPTION(MAXDOP 2) --Its depend on the available Processors in the Server

    create temp table for data you want to filter then join them

    Regards,

    Syed Jahanzaib Bin Hassan

    MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog

    http://www.aureus-salah.com

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • Syed Jahanzaib Bin hassan (4/22/2011)


    OPTION (MERGE JOIN)

    or

    OPTION(MAXDOP 2) --Its depend on the available Processors in the Server

    Got some justification for those suggestions?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply