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
(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?
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
- Proposed as answer by Allen Li - MSFTMicrosoft contingent staff, Moderator Monday, September 16, 2013 2:14 AM
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
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
>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:
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
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ć
- Edited by Dean Savović Sunday, September 15, 2013 6:17 PM
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);
2. CREATE INDEX IX_Customers_example on dbo.Customers(BirthDate) INCLUDE (FirstName, LastName)
Regards, Dean Savović