SQL Server Performance

SQL-Query on SQL-Server2005: 35 seconds is too long - optimization possible?

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by TimB83, Apr 28, 2008.

  1. TimB83 New Member

    Hello,
    my name is Tim and it is my first post on sql-server-performance.com
    I have a problem with the duration of a sql-query, but I don't know how I can optimize this.
    Here are the database-tables:
    Table1 (2.600.000 lines):
    A_CODE (PS,int, NOT NULL)
    B_CODE (PS,int, NOT NULL)
    C_CODE (PS,int, NOT NULL)
    D_CODE (PS,int, NOT NULL)
    E_CODE (int, NOT NULL)
    F_CODE (PS,int, NOT NULL)
    VAL (numeric(17,6), NULL)

    Table2 (2.713 lines) =>okay

    Table3 (32 lines)=>okay


    Here is my SQL-Query:
    SELECT DISTINCT table1.B_CODE FROM Table1 as table1 WHERE table1.VAL=83 AND table1.B_CODE IN(
    3,34,65,96,127,159,188,218,249,276,307,332,363,389,420,438, 450,459,469,483,497,506,516,520,527,530,534,537,543) AND table1.C_CODE IN ((
    SELECT table2.C_CODE FROM Table2 as table2 WHERE table2.OTHER_CODE IN(
    SELECT table3.OTHER_CODE FROM Table3 as table3 WHERE table3.OTHER_IDEN='DAY')
    AND table2.START>='20070101' AND table2.END<='20071231'))

    The result is: 1 value / duration: 35seconds


    So my problem is the duration, it is very very long and I think this could be optimized - but how could I do this?
    Doyou have some tipps and tricks to make this sql-query a little bitfaster? Table2 and Table 3 are optimized and correct - I can't changethem. But table 1 is very big and therefore I think I should optimizethis table or this SQL-Query.

    Thank you very much.
    Best regards


    Tim
  2. madhuottapalam New Member



    Basically, the performance of select statement is more or less depends upon the indexes available in the table. post back the tables schema and the indexes available. You may need to create covering indexes
    Madhu
  3. jagblue New Member

    Hi
    Try your Query Like this
    SELECT DISTINCT table1.B_CODE
    FROM Table1 as table1
    INNER JOIN Table2 as table2 ON table1.C_CODE = table2.C_CODE
    INNER JOIN Table3 as table3 ON table2.OTHER_CODE = table3.OTHER_CODE
    WHERE table1.VAL=83
    AND table1.B_CODE IN(3,34,65,96,127,159,188,218,249,276,307,332,363,389,420,438, 450,459,469,483,497,506,516,520,527,530,534,537,543)
    AND table2.START>='20070101'
    AND table2.END<='20071231'
    AND table3.OTHER_IDEN='DAY'
    Also look for Composit Indxes on Table1 for Column(Val,B_CODE,C_CODE) In this order

    Thahks
  4. TimB83 New Member

    Hello,

    thank you very very very much for your tipps.
    I have checked them but they are also so long like 35seconds or more... :-(
    Here are the scripts for the tables:

    CREATE TABLE [dbo].[TABLE1](
    [CODE_A] [int] NOT NULL,
    [CODE_B] [int] NOT NULL,
    [CODE_C] [int] NOT NULL,
    [CODE_D] [int] NOT NULL,
    [CODE_E] [int] NOT NULL,
    [CODE_F] [int] NOT NULL,
    [VAL] [numeric](17, 6) NULL,
    CONSTRAINT [PK_TABLE1] PRIMARY KEY CLUSTERED
    (
    [CODE_A] ASC,
    [CODE_B] ASC,
    [CODE_C] ASC,
    [CODE_D] ASC,
    [CODE_F] ASC
    )WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]




    The other 2 tables are given, I can't change them or the architecture of them - it is only possible to change the first table.
    Here are the other scripts:


    CREATE TABLE [dbo].[TABLE2](
    [CODE_C] [int] NOT NULL,
    [field2] [varchar](20) NOT NULL,
    [field3] [varchar](40) NULL,
    [OTHER_CODE] [int] NOT NULL,
    [START] [int] NOT NULL,
    [END] [int] NOT NULL,
    [field6] [varchar](5) NULL,
    CONSTRAINT [XPKTABLE2] PRIMARY KEY CLUSTERED
    (
    [CODE_C] ASC
    )WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    GO
    ALTER TABLE [dbo].[TABLE2] WITH CHECK ADD CONSTRAINT [FK_0029] FOREIGN KEY([OTHER_CODE])
    REFERENCES [dbo].[TABLE3] ([OTHER_CODE])
    GO
    ALTER TABLE [dbo].[TABLE2] CHECK CONSTRAINT [FK_0029]



    CREATE TABLE [dbo].[TABLE3](
    [OTHER_CODE] [int] NOT NULL,
    [OTHER_IDEN] [varchar](20) NOT NULL,
    [field3] [varchar](40) NULL,
    [field4] [int] NOT NULL,
    [field5] [int] NOT NULL,
    [field6] [int] NOT NULL,
    [field7] [varchar](40) NULL,
    [field8] [varchar](40) NULL,
    [field9] [varchar](20) NULL,
    [field10] [varchar](20) NULL,
    [field11] [int] NULL,
    CONSTRAINT [XPKTABLE3] PRIMARY KEY CLUSTERED
    (
    [OTHER_CODE] ASC
    )WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]



    Hope you can help me?
    Best regards,
    Andy
  5. Luis Martin Moderator

    Andy,
    Did you try DTA?
  6. madhuottapalam New Member

    Covering index on Table1 which includes B_CODE,C_CODE, Val should improve the performance. Create the index and update the statistics then try
    Madhu
  7. virgilash New Member

    You should try:


    SELECT DISTINCT table1.B_CODE
    FROM Table1 as table1
    INNER JOIN Table2 ON table1.c_code=table2.c_code
    INNER JOIN Table3 ON table2.Other_CODE=table3.Other_CodeWHERE table1.VAL=83 AND
    table1.B_CODE IN (3,34,65,96,127,159,188,218,249,276,307,332,363,389,420,438, 450,459,469,483,497,506,516,520,527,530,534,537,543) AND
    table2.START>='20070101' AND table2.END<='20071231')) ANDtable3.OTHER_IDEN='DAY'
    Required indexes:table1 index : b_code & c_code & val
    table2 index : c_code & start & end
    table3: none



  8. Aston New Member

    Hi All,
    I have question which is better way of following performance wise
    1) Filter rows in Where clause using IN(
    3,34,65,96,127,159,188,218,249,276,307,332,363,389,420,438, 450,459,469,483,497,506,516,520,527,530,534,537,543)
    Or
    2) Make table variable and have all values in it and then make a join with table variable.
    Thanks !!!!

Share This Page