#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    2
    Rep Power
    0

    Question Optimize multiple counts?


    Hi, i am playing around with a simple 'community' project where the users can post msgs in a msgboard. I was wondering how I can speed things up here: Because I've made a list displaying "xx new messages since last login" in each thread of the forum, and to do that i am counting all the msgs in the forum since last the user logged in. Example as follows:

    select count(*) from msgs where thread=xx between user.lastlogin and getDate()

    This is naturally super-slow, especially when doing 10-20 counts each time the page loads, so I would appreciate if I could get some tips to speed things up a bit :-) Anyone?
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    2
    Rep Power
    0

    Indexing might help


    -Try to index the tread and date field in your msgs table.

    -Can the date be later then the current date? Why use the BETWEEN expression and not just:

    msgs.date >= user.lastlogin

    Not sure whether this will help you, but it's worth trying. Good luck!
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    2
    Rep Power
    0

    thanks.


    Thanks, I'll try that. How exactly does indexing work in this case? Do you know some examples i could check out?
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    2
    Rep Power
    0

    Indexing


    Indexing your table won't effect the format of your SQL query. Use the "MSSQL enterprise manager" to index the fields (right click on the tables choose All tasks -> Manage indexes...). Your SQL queries can be the same as before. The SQL server will automaticly use other datastructures to improve the performance of the SELECT query.

    For more information about index types, consult some online documentation.

IMN logo majestic logo threadwatch logo seochat tools logo