Slow search question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Slow search question

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2002-08-17 : 15:21:19

My indexes seem to be in order. I have index on orderid, propadd and
county right now. I am considering writing this in dynamic sql. Does anybody have a better way of writing this.

(
@bring in all input vars
@ordernum,
....
)

IF @ordernum IS NULL
BEGIN
SET @ordernum ='%'
END

IF @propadd IS NULL
BEGIN
SET @propadd ='%'
END

IF @county IS NULL
BEGIN
SET @county ='%'
END

IF @city IS NULL OR @city=' '
BEGIN
SET @city ='%'
END

IF @state IS NULL OR @state=' '
BEGIN
SET @state ='%'
END

IF @zipcode IS NULL
BEGIN
SET @zipcode ='%'
END

--********************************************************************
--SEARCH COMBINATION
--*******************************************************************

INSERT INTO @orders (orderid, ordernum, propadd,city,state,zipcode,partyname)
SELECT TOP 50
a.orderid,
a.ordernum,
a.propertyaddress,
a.city,
a.state,
a.zipcode,
b.partyname
FROM dbo.tblorders a
LEFT JOIN dbo.tblparty b ON (a.orderid=b.orderid)
WHERE b.primarycust=1
AND a.ordernum LIKE @ordernum + '%'
AND (a.propertyaddress LIKE @propadd + '%')
AND (a.county LIKE @county + '%')
AND (a.city LIKE @city + '%')
AND (a.state LIKE @state + '%')
AND (a.zipcode LIKE + @zipcode + '%')

The search is very vast when I search propadd, ordernum or combination but county, city and anything below are very slow. County is indexed. My question is if I use dynamic SQL and trim the where clause to only include selections is this best practice.

Thanks in advance.

slow down to move faster...

r937
Posting Yak Master

112 Posts

Posted - 2002-08-18 : 10:07:37

dynamic sql is best practice when the situation calls for it

as far as the optimizer knows, you could have a LIKE on any of those 6 columns, so the execution plan probably reflects that

note: IANAOE (i am not an optimizer expert)

the knock against dynamic sql is that it has to be parsed and bound at run time, but if some of the WHERE conditions are missing, the execution plan is going to reflect that and therefore be, um, optimized

give it a try

rudy
http://rudy.ca/

rudy
http://rudy.ca/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources