t-sql full table scan problem

Question

Post reply

t-sql full table scan problem

wendy elizabeth

SSCoach

Points: 16915
More actions
September 14, 2013 at 3:36 pm

#277134

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 cany ou tell me what I can do to make this sql run faster?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

siggemannen SSCrazy Points: 2436 More actions · Answer 1

Looks like your query won't compile since you join on non-existing v-table inside the LEFT JOIN?

Can you create an INDEXED VIEW inside the procedure that indexes the zip /state-columns?

That might help, otherwise hard to make query much faster without indexes...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Please post table definition, index definitions and execution plan as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

wendy elizabeth SSCoach Points: 16915 More actions · Answer 3

wendy elizabeth

SSCoach

Points: 16915

September 15, 2013 at 9:46 pm

#1650313

How do you create an indexed view?

psingla Hall of Fame Points: 3840 More actions · Answer 4

A view with clustered index is indexed view.

There are lot of limitations/conditions apply on creating the index view.see BOL for detail

Pramod
SQL Server DBA | MCSE SQL Server 2012/2014
in.linkedin.com/in/pramodsingla/
http://pramodsingla.wordpress.com/

siggemannen SSCrazy Points: 2436 More actions · Answer 5

wendy elizabeth (9/15/2013)
How do you create an indexed view?

Something like:

IF OBJECT_ID('INDEXED_CUSTOMER', N'V') IS NOT NULL

DROP VIEW INDEXED_CUSTOMER;

GO

CREATE VIEW dbo.INDEXED_CUSTOMER

WITH SCHEMABINDING

AS

SELECTcust_id, cust_no, zipcode, city, province -- etc

FROMdbo.customer

GO

CREATE UNIQUE CLUSTERED INDEX pk_i_customer ON INDEXED_CUSTOMER (cust_id)

GO

CREATE UNIQUE INDEX s1_i_customer ON INDEXED_CUSTOMER (zipcode, cust_id) INCLUDE (city, province)

CREATE UNIQUE INDEX s2_i_customer ON INDEXED_CUSTOMER (province, cust_id) INCLUDE (city, zipcode)

GO

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

wendy elizabeth (9/14/2013)
Thus cany ou tell me what I can do to make this sql run faster?

what made you to think that this query is behaving bad ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

wendy elizabeth (9/15/2013)
How do you create an indexed view?

Why are you thinking about creating an indexed view?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass