none
t-sql peformance sql problem

    Question

  • I am having a performnace problem is a sql server 2008 sql. The stored procedure does a full table scan when one of the @state or @zip paramteres are used. The dba says he will not put an index on the state and zip columns since this is the only sql that uses state and zip cocde. The sql look like the following:

    select cs.person_id,cs.cust_id,cs.customer_name,cs.address1, cs.address2, cs.city, cs.state,cs.zip,z.product_id,z.product_name,z.product_description
    from customer_table cs
    left join view1  v1 on v1.cust_id = cs.cust_id
    left join
    (select product_id,product_name,product_description from product_table p on p.iventory_id = v.iventory_id
    where @prod_quantity <= p.supply_on_hand) z on z.person_id = c.person_id
    where @zip = Cs.zip or @state=cs.state

    Thus can you tell me what I can do to make this sql run faster?

    Saturday, September 14, 2013 9:05 PM

All replies

  • In order to suggest you something useful please provide an execution plan of the query. How much rows does it return? How much data does (select product_id,product_name,product_description from product_table p on p.iventory_id = v.iventory_id
    where @prod_quantity <= p.supply_on_hand) 

    this select return? I think you do need to put  the indexes on zip and state columns.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Sunday, September 15, 2013 11:51 AM
    Answerer
  • Check your where clause these are majorly different with respect to the execution plan created:

    where @prod_quantity <= p.supply_on_hand) z on z.person_id = c.person_id
     where @zip = Cs.zip or @state=cs.state

    And

    where  p.supply_on_hand >= @prod_quantity) z on z.person_id = c.person_id
     where Cs.zip = @zip  or cs.state = @state

    Put the column on the left side of any comparison. 

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Sunday, September 15, 2013 12:50 PM
  • >The dba says he will not put an index on the state and zip columns since this is the only sql that uses state and zip cocde

    Difficult to optimize without indexing. Optimization bible:

    http://www.sqlusa.com/articles/query-optimization/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Sunday, September 15, 2013 5:22 PM
    Moderator
  • Hi Wendy,

    You query has two errors. Derived table in second left join has ON statement on table that is in FROM clause. Second error is, derived table Z is joined on person_id with c.person_id and person_id does not exist in Z derived table. Also, table with alias C (C.person_id) does not exist.

    Index on zip and state will be useful if data is selective enough. You have many columns in SELECT clause and you would either have to cover those with index or include or the query is selective enough that Key Lookup is not expensive.

    For more help please post DDL, execution plan details and statistics io and time.


    Regards, Dean Savović


    Sunday, September 15, 2013 6:17 PM
  • are you saying that I should put the parameter on the left side?
    Monday, September 16, 2013 3:08 AM
  • Your statement,"many columns in SELECT clause and you would either have to cover those with index or include'? can you tell me what the include menas you are referring to? Can you show me some sql so I know what you are referring to?
    Monday, September 16, 2013 3:31 AM
  • Can you tell me how to place an explain plan on this web service? The explain is a graphical representation.
    Monday, September 16, 2013 1:14 PM
  • For start post screenshot of execution plan. If needed we will ask you for operator details.

    Regards, Dean Savović

    Tuesday, September 17, 2013 6:41 AM
  • INCLUDE is the option that can be used when creating non-clustered indexes.

    For example, if you have table Customers, and columns FirstName, LastName and BirthDate and query looks like this:

    select
    FirstName, LastName
    from dbo.Customers
    where BirthDate = '20010901 00:00:00.000'

    You can cover this query in two ways:

    1. CREATE INDEX IX_Customers_example on dbo.Customer(BirthDate, FirstName, LastName);

    or

    2. CREATE INDEX IX_Customers_example on dbo.Customers(BirthDate) INCLUDE (FirstName, LastName)


    Regards, Dean Savović

    Tuesday, September 17, 2013 6:44 AM