Results 1 to 2 of 2

Thread: Too slow I think...

  1. #1

    Thread Starter
    Frenzied Member StrangerInBeijing's Avatar
    Join Date
    Mar 2005
    Location
    Not in Beijing
    Posts
    1,666

    Too slow I think...

    I got an articles table, containing articles posted most days.
    I want to get a list of the the last "x" days with at least "y" articles.

    For instance, give me the last 30 dates that do have at least 10 articles posted on that day.

    This sproc is quite slow:
    Code:
    CREATE PROCEDURE sp_GET_NewsDaysToShow
    @days 		INT,
    @minArticles 	INT
    AS
    SET ROWCOUNT @days
    SELECT COUNT(*) AS Articles, PublishDate
    FROM 
    tblArticles 
    GROUP BY PUblishDate
    HAVING COUNT(*) >= @minArticles
    ORDER BY PublishDate DESC
    any ideas?
    Install and Configure Eclipse For both Java and PHP development
    Accessible Ajax/jQuery Forms Degrade gracefully with JavaScript Disabled

  2. #2
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    Re: Too slow I think...

    This query will read PublishDate from ALL records in the table and the group and count them. You probably understand that this is slow.

    The only thing I can think of to make it faster if you want to be able to run this query real-time is to create an index on the PublishDate column.

    Another way, if you don't need 100% real time data, is to create a new table with a date and count column. Populate this table with data from your query on a scheduled interval.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width