SQL Server Performance

Optimized queries

Discussion in 'SQL Server 2008 T-SQL Performance Tuning' started by MarcoPolo, Dec 11, 2010.

  1. MarcoPolo New Member

    I have this tables :
    create table CUSTOMER_ORDER
    (Orderid INT IDENTITY(1,1) NOT NULL,
    Orderdate DATETIME NOT NULL,
    Mode VARCHAR(50) NOT NULL,
    State SMALLINT NOT NULL,
    Warehouse_id SMALLINT NOT NULL,
    CONSTRAINT PK_CUSTOMER_ORDER PRIMARY KEY (Orderid)
    );

    create table PRODUCT
    (Product_id INT IDENTITY(1,1) NOT NULL,
    Ean VARCHAR(50) NOT NULL,
    Product_store SMALLINT NOT NULL,
    CONSTRAINT PK_PRODUCT PRIMARY KEY (Product_id)
    );

    create table ITEM
    (OrderId INT NOT NULL,
    itemId SMALLINT NOT NULL,
    productid INT NOT NULL,
    CONSTRAINT PK_ITEM PRIMARY KEY (OrderId,itemid),
    CONSTRAINT FK_PRODUCT_C FOREIGN KEY (productid) REFERENCES PRODUCT(Product_id),
    CONSTRAINT FK_CUSTOMER_ORDER FOREIGN KEY (OrderId) REFERENCES CUSTOMER_ORDER(Orderid)
    );


    create table WAREHOUSE
    (Product_id INT NOT NULL,
    Warehouse_id SMALLINT NOT NULL,
    Location VARCHAR(50) NOT NULL,
    CONSTRAINT PK_WAREHOUSE PRIMARY KEY (Product_id, Warehouse_id),
    CONSTRAINT FK_PRODUCT_W FOREIGN KEY (Product_id) REFERENCES PRODUCT(Product_id)
    );
    And I want to retrieve by "Mode" all "CUSTOMER_ORDER" for Warehouse_id 2
    and for the Orderdate '05/06/1999'
    and with the State (1, 2 or 3)
    and which contains the Ean '1234567890123'
    My answer :
    SELECT Mode, COUNT(*)
    FROM CUSTOMER_ORDER C, ITEM I, PRODUCT P
    where
    C.Orderid = I.OrderId
    AND I.productid = P.Product_id
    AND Warehouse_id = 2
    AND Orderdate = '05/06/1999'
    AND (State = 1 OR State = 2 OR State = 3)
    AND Ean = '01234567890123'
    GROUP BY Mode;
    Is it possible to optimized this query ?
  2. Luis Martin Moderator

    Welcome to the forums!!.
    Did you check execution plan?.
  3. MarcoPolo New Member

    I'm check the execution plan but I'm a beginner with the db. I must have a query which is the best.
  4. FrankKalis Moderator

    [quote user="MarcoPolo"]
    I have this tables :
    create table CUSTOMER_ORDER
    (Orderid INT IDENTITY(1,1) NOT NULL,
    Orderdate DATETIME NOT NULL,
    Mode VARCHAR(50) NOT NULL,
    State SMALLINT NOT NULL,
    Warehouse_id SMALLINT NOT NULL,
    CONSTRAINT PK_CUSTOMER_ORDER PRIMARY KEY (Orderid)
    );

    create table PRODUCT
    (Product_id INT IDENTITY(1,1) NOT NULL,
    Ean VARCHAR(50) NOT NULL,
    Product_store SMALLINT NOT NULL,
    CONSTRAINT PK_PRODUCT PRIMARY KEY (Product_id)
    );

    create table ITEM
    (OrderId INT NOT NULL,
    itemId SMALLINT NOT NULL,
    productid INT NOT NULL,
    CONSTRAINT PK_ITEM PRIMARY KEY (OrderId,itemid),
    CONSTRAINT FK_PRODUCT_C FOREIGN KEY (productid) REFERENCES PRODUCT(Product_id),
    CONSTRAINT FK_CUSTOMER_ORDER FOREIGN KEY (OrderId) REFERENCES CUSTOMER_ORDER(Orderid)
    );


    create table WAREHOUSE
    (Product_id INT NOT NULL,
    Warehouse_id SMALLINT NOT NULL,
    Location VARCHAR(50) NOT NULL,
    CONSTRAINT PK_WAREHOUSE PRIMARY KEY (Product_id, Warehouse_id),
    CONSTRAINT FK_PRODUCT_W FOREIGN KEY (Product_id) REFERENCES PRODUCT(Product_id)
    );
    And I want to retrieve by "Mode" all "CUSTOMER_ORDER" for Warehouse_id 2
    and for the Orderdate '05/06/1999'
    and with the State (1, 2 or 3)
    and which contains the Ean '1234567890123'
    My answer :
    SELECT Mode, COUNT(*)
    FROM CUSTOMER_ORDER C, ITEM I, PRODUCT P
    where
    C.Orderid = I.OrderId
    AND I.productid = P.Product_id
    AND Warehouse_id = 2
    AND Orderdate = '05/06/1999'
    AND (State = 1 OR State = 2 OR State = 3)
    AND Ean = '01234567890123'
    GROUP BY Mode;
    Is it possible to optimized this query ?
    [/quote]
    From your question I wouldn't have guessed that you want the COUNT(*). I thought you would want to retrieve all data for the given filters. Anyway, the query itself is okay, I would probably use the explicit JONI syntax, but in case of all INNER JOINs it doesn't really matter that much. One thing to look at however is that you don't have any index in place to support that query. So, it's gonna scan through the tables.
    Also, I can't help, but I think this is a homework question [;)]

Share This Page