Post reply

search table design

  • June 20, 2009 at 10:27 pm

    #135378

    Hi,

    I administer a dating website and we currently have two databases, one that basically stores user info and another that stores messages between users. The user info db is currently a bottleneck since it is constantly being querried by users searching other users in the website. I figured I could divide this database, keeping one database with all the users' information and creating a new database on a separate server that would exclusively be used for searching users' profiles. This new database doesn't have to bee 100% updated, ie, when a user updates his information, it can be updated in the search results after a couple of hours.

    So my questions are, is this a good design to solve the problem? If so, the search database would only need one table with only essential user information, like name, age, location, etc. - how can I update this information in the search database with the minimum cost for performance (I was thinking of simply deleting everything in the table and reinserting all user information every couple of hours, but I think there must be a better way to do this as only a small percentage of the information gets added or updated by the hour)?

    I'm already using full text search on our databses. Is there anything else that can help with performance?

    Also, I've never transfered information between two different sql servers other than using xml, so if you can, please let me know specifically how this should be done (sql statements, etc).

    Andre

  • June 20, 2009 at 10:47 pm

    #1012959

    as as (6/20/2009)

    I administer a dating website and we currently have two databases, one that basically stores user info and another that stores messages between users. The user info db is currently a bottleneck since it is constantly being querried by users searching other users in the website. I figured I could divide this database, keeping one database with all the users' information and creating a new database on a separate server that would exclusively be used for searching users' profiles. This new database doesn't have to bee 100% updated, ie, when a user updates his information, it can be updated in the search results after a couple of hours.

    So my questions are, is this a good design to solve the problem? If so, the search database would only need one table with only essential user information, like name, age, location, etc. - how can I update this information in the search database with the minimum cost for performance (I was thinking of simply deleting everything in the table and reinserting all user information every couple of hours, but I think there must be a better way to do this as only a small percentage of the information gets added or updated by the hour)?

    You might want to use transaction replication in this case. All the incremental records will be moved from primary to search database almost immediately or on a desired interval specified by you. You may want to consider log shipping but this wont be greatest of ideas as users will NOT be allowed to perform search on the search database when the logs are being restored.

    I'm already using full text search on our databses. Is there anything else that can help with performance?

    You need to figure out what all columns are used for searching extensively. See if you've got indexes on them? eg- location column etc. Do you have a maintainance plan that reorganizes indexes no a routine basis (weekly?)

    Also, I've never transfered information between two different sql servers other than using xml, so if you can, please let me know specifically how this should be done (sql statements, etc).

    replication would do that for you. Other than that if you want to manually move data from one server to other ,u'll need to add the second server as a linked server in the primary database. Then you can insert using 4 part notation

    insert into server2.db2.dbo.TargetTable

    select * from SourceDBTable


    Pradeep Singh

  • June 21, 2009 at 12:39 pm

    #1013058

    I'm not so sure that you need a separate server for this. A separate database may be well enough. It's similar to having a regular database and a reporting database.

    Having two servers virtually doubles all costs. Transactional replication is fine and a good way to do things even if you just use a separate database instead of a separate server. As a side bar, you can learn to hate it quickly because it does lock the schema for the objects involved. 😛

    Before going through the cost of an additional server and the licenses involved, I believe I'd try just making a separate database and see how that affects your performance. Of course, index tuning and regular maintenance will be part of the key to performance here.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 22, 2009 at 7:58 am

    #1013297

    Thanks

Viewing 4 posts - 1 through 4 (of 4 total)

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