Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    9

    Unanswered: Optimizing database

    I've been asked to assist with the optimization of a new project that has been built with Silverlight/C# and SQL Server. As somebody who's experience lies mainly in the Java/MySQL realm I'm hoping somebody here can help with some suggestions.

    The first problem lies with a table used to store notifications from an ecommerce application. There can be hundred of thousands of records posted to this table every day 24 x 7. Hence, over time performance starts to suffer and there's no window to purge old data from the table. Are there any special SQL-Server faciltiies to handle this problem? Or would it be better to have a separate physical table for each day of the month? (e.g. use 'table1' for all entries posted on the 1st, 'table2' for asll data frorm the 2nd etc)

    Another problem area with this project is where it takes in real time pricing feeds and has to store the incoming data. There are enormous volume spikes with this activity, and the underlying database can't handle the volume and this ends up being a huge bottleneck. One solution would be to introduce a messaging tier between the incoming price and the database to make the process asynchronous, but are there any specific SQl Server capabilities available to address this type of problem?


    TIA

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Do not make multiple tables - you're asking for trouble there!

    Have you got any indexes on the date? Are you returning all rows in the table rather than a smaller date range?

    You may wish to consider Partitioned Tables and Indexes as well.
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by davoutuk View Post
    One solution would be to introduce a messaging tier between the incoming price and the database to make the process asynchronous, but are there any specific SQl Server capabilities available to address this type of problem?
    If you are on SQL 2008, you might want to have a look at the service broker in 2008 for messaging functionality.

    Different tables for every day would be a horrible idea, but perhaps a table for today's data and a table that holds all of the history would not be such a bad idea and a partitioned view to tie them together.

    Is the legacy data relevant if this is all "real-time" data? Is there a business reason for keeping it or is onlly the current data relevant. Do you need the detail level of previous day's data or only rolled up reports on that data?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Are you talking about performance related to INSERT statements, or INSERT and UPDATE? But if you're talking about SELECTs as well, - partitioning is the way to go. Be carefull though, make sure to thoroughly analyze your SELECTs to ensure that partitioned indexes fully reflect every SELECT issued, and fully utilize the partitioned index.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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