SQL Server Performance

Tuning

Discussion in 'T-SQL Performance Tuning for Developers' started by sfunds, Sep 19, 2005.

  1. sfunds New Member

    Hi,
    I have SQL SErver 2000
    The database consist of 2 tables Table 1 having details like company name,code etc.
    The second table the important one hase the daily EODquotes. there are about 800 companies
    with data from 2000.
    I see that a query to get data for a particular compay

    Select * from EODQuotes where symbol = 'ACC' takes around 2.47 minutes
    The total data size is around 1000000 rows ACC has around 1500
    I have index on code+tradedate

    How can I improve the speed.

    I was previously storing Data For Each Company in Separate Table(It was easy and fast to get data but writing procedures was very difficult and backup was also difficult)


    What happens when i create a view is it similar to creating table resource wise
    performance wise.

    Thanks
  2. Adriaan New Member

    <s>Sounds like you're relatively new to databases - no problem [<img src='/community/emoticons/emotion-1.gif' alt=':)' />] but does the table have any indexes?</s>AARGH stupid question, your message says so.[xx(]
  3. Adriaan New Member

    Questions should have been: is it a clustered index, with the company code as the first column specified?

    And does the table have a primary key defined? It should be on the two columns that you have in the index, and in that case you should drop the index, and just add create the primary key.
  4. satya Moderator

    Keeping the query in stored procedure will have a good affect than running the query all the times, as the plan will be cached and running PROFILER during this process is a good start to assess.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. sfunds New Member

    REATE TABLE [dbo].[EODQuotes] (
    [SYMBOL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [OPEN] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [HIGH] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LOW] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CLOSE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LAST] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PREVCLOSE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TOTTRDQTY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TOTTRDVAL] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [TIMESTAMP] [datetime] NOT NULL
    ) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[EODQuotes] WITH NOCHECK ADD
    CONSTRAINT [PK_EODQuotes] PRIMARY KEY CLUSTERED
    (
    [SYMBOL],
    [TIMESTAMP]
    ) ON [PRIMARY]
    GO


    This is how the table is created
  6. Adriaan New Member

    Looks like you might benefit from dropping this PK, adding an identity column as the primary key, plus a unique constraint index and/or constraint on SYMBOL + TIMESTAMP, plus an index on SYMBOL alone.

    As usual, test in development environment (with plenty of data) before implementing in production.
  7. dineshasanka Moderator

  8. joechang New Member

    there is no way it should take 2.47min to retrieve 1500 rows from a clustered index, something is seriously wrong,
    it should be around 1sec, and most of that is on the client side.
    i would start troubleshooting as much as possible, and this includes options other than the PK
  9. FrankKalis Moderator

    quote:
    do not create PK on date time column! it will definitely going reduce the performance of the database
    Why? I think this statement is too general.

    You're obviously storing some equity quotations. Why do you store High, Low, Open... as CHAR(10) and not as DECIMAL(x,2) ?
    Why is SYMBOL a VARCHAR(50)? Reuter and/or Bloomberg never use such a wide identifier. I think, a width of 10 should be enough here.
    When you say you need almost 3 minutes to return just 1500 rows I would suspect your problem somewhere else but not in your indexing. Btw, 1500 rows a 30 rows a month are more than 4 years. What financial analysis are you running against such a time frame?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  10. Adriaan New Member

    Doesn't it depend on the number of distinct values for the SYMBOL column, and the number of rows for each? If there's a huge number for a handful of values, and just a small number for most, what happens to performance?
  11. FrankKalis Moderator

    He wrote he stores data for about 800 companies. A query like SELECT * FROM table WHERE SYMBOL = 'ACC' should almost be able to read the data sequentially given the existing clustered index. Like Joe said, it should be in the 1 - 2 seconds time frame, but not that much more.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  12. mmarovic Active Member

    I agree with Frank and Joe. Since your clustered pk is not on identity colum, but on varchar + something else you can expect high fragmentation and bad performance, but I can't imagine it can be so bad just due to fragmentation. It must be something else that is the main reason.

    How did you measure query performance? On production server? Did anything run at the same time?
    Do you have other application run on db server other then MSSQL Server?
    Which column contains company idenitifier? Is it Symbol?
    Have you tried to rebuild pk and then measure performance?
  13. jn4u Member

    Discussing the problem is one solution. I would use the profiler to capture the events during some hours. Something likes this

    http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm

    Then more or less I would look at the server is behaving. Put up a windows “performance” logging.

    http://img376.imageshack.us/img376/7395/screenshot0233gz.jpg

    After that I would have the actual “sql” statements that are slow. If would find out that the problem is this statement.

    “Select * from EODQuotes where symbol = 'ACC'”

    Then I would start to with testing put a clustered index on symbol column. Get all the EODQuotes right order on the disk. I don#%92t tells u to do it just an example. I should also compare the sql statement time with the profiler information. Ask me question as how is the Buffer cache hit ration? % processor time? Processor Quene Length? Etc… Test different solutions and profile the database get right one.
  14. sfunds New Member

    This happend only in my production machine
    No other process is running on that.

    Same database in my other machine returns data in 6 secs when many some other process are also running.
    My production server has better config then my machine which is old.

    only diff is my production server has Active directory installed and It has windows server 2003 running on it

Share This Page