SQL Server Performance

Querying Large Table

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Ranjit72, Feb 18, 2008.

  1. Ranjit72 New Member

    Hello All,
    I have a large table with 5 million records and I have to query based on the certain criteria...the query is taking like 15-20 mins to return results,can anyone suggest any tips to improve the performance.
    Details.
    SQL 2000
    TableA: No.of records 50000000+
    There is a primary key/index based on 4 cols.
    Query Criteria Col is a varchar.
    Query:
    Select Col1,Col2,Col4,Coln from TableA where Col4='xyz'.
    Query Execution plan shows most of the cost on table scan.
    I cannot change much on these table as they are the vendor tables and data is pumped from their ERP system every night.
    I can create views and can create my own View Indexes.
    Any pointers or suggestions.
    Thanks a bunch.
    Ranjit
  2. Luis Martin Moderator

    What about a non cluster index on Col4?.
  3. susanthab New Member

    A precise answer is not possible for this since full table structure and details of the indexes are not given.
    But I’m thinking of some suggestions you may have to try it out.
    1. Consider having a covering index which covers all your output columns. However if you have more output columns this will not give better improvement.
    1. Create a non-clustered index on col4 as suggested above.
    1. Indexed view would be a better choice for this scenario.
    You need to try all above suggestions and compare them before continuing any implementation.
  4. Ranjit72 New Member

    Are there any caveats in using Indexed views? I havent used it before.
    Table Structure:
    [SourceID] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    [ApplicationDatabaseID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    [TimeStringID] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    [DeviceID] [varchar] (21) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    [TimeStampID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    [StatsTypeID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    [ApplicationDatabaseTitle] [varchar] (92) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
    [Field1] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
    [Field10] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
    [Field2] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
    [Field3] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
    [Field4] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
    [Field5] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
    [Field6] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
    [Field7] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
    [Field8] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
    [Field9] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
    [StatsDpm] [varchar] (23) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,

    [RowUpdateDateTime] [datetime] NULL
    Most of my query will be having criteria using field4 and field2
    Select * from Table1 where field4='xyz'
    Thank you
    ranjit
  5. Luis Martin Moderator

    Did you try what I suggest in test database?
  6. Ranjit72 New Member

    Martin,
    As I said it is not our database. As explained in the earlier post they will not create anything for us on the table level(Indexes ect).
    We can request them to create a view if we give them the .sql script and the if the DBA agrees they will create the view and give us select access to it.
    I also posted the table schema. If that helps.
    Thanks again.
    --Ranjit
  7. Luis Martin Moderator

    I'm sorry for miss that.
    I my place, the application belong to the provider, but the data (database) belong to the client. So, we can't touch a single line from appl but we can with database.
  8. Ranjit72 New Member

    Thank you all,
    As I said this is a vendor database and we have only select rights, we dont have any create rights. On the other hand we can ask them to create views, but we have to give them the SQL script they will run with their DBO rights.
    I will try to create indexed views.
    Thanks Again
    --Ranjit
  9. satya Moderator

    Have you checked execution plan for this query on the system?
    The best way is to identify why such slow performance is caused.
  10. Ranjit72 New Member

    I have not gone that far, to look at the execution plan the query should be completed right? Please correct me if I am wrong.
    Before my query gets completed either my process is killed nor the transaction log is filled up.
    Any suggestions?
    Thanks
    Ranjit

Share This Page