#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2004
    Posts
    27
    Rep Power
    0

    Count() speed, generic performance in large table


    I've got a database (single-table, currently) in SQL 05; it has > 100,000,000 rows.

    this simple query:
    Code:
    select COUNT(transaction_id) from muni_data where event_date is null
    takes > 6 minutes to execute. transaction_id is the primary key on the table [bigint]; although there is no index on event_date [the data was imported from a different system and i'm migrating the data in place to SQL-friendly types]

    Anyhow, short question, as I'm new to MSSQL but modestly experienced with MySQL. What's the simplest way to speed this up?

    Thanks!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    Originally Posted by manzellb
    What's the simplest way to speed this up?
    use COUNT(*), and add an index on event_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo