Need some advice to improve performance on query

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 2008 Forums

Transact-SQL (2008)

Need some advice to improve performance on query

Author

Topic

spsubaseball
Starting Member

17 Posts

Posted - 2012-06-11 : 09:31:54

The following query is killing me with the time its taking to return data. Does anyone have any ideas to speed the performance?

SELECT m.[property_id], m.[Address], m.New_OpenBid_Yes_No,
m.deed_book, m.page_no, m.Open_Bid_Last_Updated, m.modifiedby, m.[City], m.[Zip],
m.County, m.[OwnerLName], m.[OwnerFName], m.DoNotBuy, m.LotSize, m.Mortgage_Amount, m.Mtg_Type, m.Yr_Built,
m.Attorney, m.Tax_Value, m.[Stories], m.[HalfBathrooms], m.[Bathrooms], m.[SquareFt], m.[Bedrooms], m.[Subdivision],
f.auction_cap,f.SET_Max, f.Open_Bid, i.imagepaththumb
FROM [AUC_Property] m
RIGHT JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY property_id_fk ORDER BY property_id_fk) AS Rn,* FROM AUC_Image) i
ON property_id = property_id_fk
AND Rn=1
JOIN [Fin_Property] f
ON f.fin_Property_Id = m.Property_Id
where Active_Month='Y' and m.fall_off != 'Y' and m.Attorney not like '%McCalla%' and m.Attorney not like '%Rubin%' and m.Attorney not like '%Swertfeger%' and m.Attorney not like '%McCurdy%' and m.Attorney not like '%Johnson%' and m.Attorney not like '%Pender%'
ORDER BY m.New_OpenBid_Yes_No desc, f.set_max asc, m.DoNotBuy asc, f.Open_Bid desc, m.[Address] ASC

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-11 : 11:07:30

These conditions are killing you:

and m.Attorney not like '%McCalla%' and m.Attorney not like '%Rubin%' and m.Attorney not like '%Swertfeger%' and m.Attorney not like '%McCurdy%' and m.Attorney not like '%Johnson%' and m.Attorney not like '%Pender%'

The ORDER BY is not helping either. If this is being used for a report, let the report do the ORDER BY.

I'm also not clear what the RIGHT JOIN is for, looks like you're trying to get an image file?

It's probably better to post your table structure with sample data and expected results. It may be you only need an index or two, or you might have to redesign something if this is an important query that needs to run constantly.

spsubaseball
Starting Member

17 Posts

Posted - 2012-06-11 : 11:12:48

The right join is just to check to see if a picture exists. I'm trying to show all other attorney's that have a picture but are not those 5 or 6 attorneys to display on a asp.net gridview.

I'm just trying to check if theirs a picture involved for that property.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-11 : 11:30:45

This might perform better:

SELECT m.[property_id], m.[Address], m.New_OpenBid_Yes_No, m.deed_book, 
m.page_no, m.Open_Bid_Last_Updated, m.modifiedby, m.[City], m.[Zip], m.County, 
m.[OwnerLName], m.[OwnerFName], m.DoNotBuy, m.LotSize, m.Mortgage_Amount, m.Mtg_Type, 
m.Yr_Built, m.Attorney, m.Tax_Value, m.[Stories], m.[HalfBathrooms], m.[Bathrooms], 
m.[SquareFt], m.[Bedrooms], m.[Subdivision], f.auction_cap,f.SET_Max, f.Open_Bid,
(SELECT TOP 1 imagepaththumb FROM AUC_Image WHERE m.property_id = property_id_fk) imagepaththumb
FROM [AUC_Property] m
INNER JOIN [Fin_Property] f ON f.fin_Property_Id = m.Property_Id
WHERE Active_Month='Y' AND m.fall_off <> 'Y' 
AND m.Attorney NOT IN ('McCalla Full Name','Rubin Full Name','Swertfeger Full Name',
'McCurdy Full Name','Johnson Full Name','Pender Full Name')

The bolded part may run faster than using the RIGHT JOIN if there's an index on imagethumbpath. In any event, the original RIGHT JOIN was probably going to table scan because of SELECT *.

The red part is the critical one: you have to use the full value stored in the Attorney column. If you use LIKE with a leading wildcard character it cannot use any indexes and you'll end up with table scans. I left out the ORDER BY, let the gridview do the sorting.

spsubaseball
Starting Member

17 Posts

Posted - 2012-06-11 : 12:31:33

Thanks for the advice rob, the query with selecting top 1 doesn't work. Each property can have more than one picture so I'm just trying to pull back just one picture path in the query. So is there an easier way for me to do that then the way I'm doing it?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-11 : 12:42:30

why do you not have a Persons table with a list of people including lawyers and filter out based on some other criteria like PersonID?

[AUC_Property] should never have free text lawyers names
<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-06-11 : 12:55:49

quote:
the query with selecting top 1 doesn't work

What do you mean by "doesn't work"? I've tested it successfully with some sample data I cooked up.

I also agree with Yosiaz regarding the attorneys, it should be a separate table with an ID column that links to AUC_Property.

spsubaseball
Starting Member

17 Posts

Posted - 2012-06-11 : 14:14:21

I'm trying to return properties that have pictures not all properties.

SELECT m.[property_id], m.[Address], m.New_OpenBid_Yes_No, m.deed_book,
m.page_no, m.Open_Bid_Last_Updated, m.modifiedby, m.[City], m.[Zip], m.County,
m.[OwnerLName], m.[OwnerFName], m.DoNotBuy, m.LotSize, m.Mortgage_Amount, m.Mtg_Type,
m.Yr_Built, m.Attorney, m.Tax_Value, m.[Stories], m.[HalfBathrooms], m.[Bathrooms],
m.[SquareFt], m.[Bedrooms], m.[Subdivision], f.auction_cap,f.SET_Max, f.Open_Bid,
(SELECT TOP 1 imagepaththumb FROM AUC_Image WHERE m.property_id = property_id_fk) imagepaththumb
FROM [AUC_Property] m
INNER JOIN [Fin_Property] f ON f.fin_Property_Id = m.Property_Id
WHERE Active_Month='Y' AND m.fall_off <> 'Y'
AND m.Attorney NOT IN ('S. H. McCalla','Rubin Lublin Suarez Serrano, LLC','L. J. Swertfeger','McCurdy & Candler, L.L.C.','Johnson & Freedman, LLC','Pendergast & Associates, P.C.')

This returns 7,500 records when it should be 350.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-11 : 14:22:12

[code]
SELECT *
FROM (
SELECT m.[property_id],
m.[Address],
m.New_OpenBid_Yes_No,
m.deed_book,
m.page_no,
m.Open_Bid_Last_Updated,
m.modifiedby,
m.[City],
m.[Zip],
m.County,
m.[OwnerLName],
m.[OwnerFName],
m.DoNotBuy,
m.LotSize,
m.Mortgage_Amount,
m.Mtg_Type,
m.Yr_Built,
m.Attorney,
m.Tax_Value,
m.[Stories],
m.[HalfBathrooms],
m.[Bathrooms],
m.[SquareFt],
m.[Bedrooms],
m.[Subdivision],
f.auction_cap,
f.SET_Max,
f.Open_Bid,
i.imagepaththumb,
ROW_NUMBER() OVER (PARTITION BY property_id_fk ORDER BY property_id_fk) AS Rn
FROM [AUC_Property] m
inner JOIN dbo.AUC_Image i
ON property_id = property_id_fk
JOIN [Fin_Property] f
ON f.fin_Property_Id = m.Property_Id
where Active_Month='Y'
and m.fall_off != 'Y'
and m.Attorney not like '%McCalla%'
and m.Attorney not like '%Rubin%'
and m.Attorney not like '%Swertfeger%'
and m.Attorney not like '%McCurdy%'
and m.Attorney not like '%Johnson%'
and m.Attorney not like '%Pender%'
)

a

where Rn = 1
ORDER BY a.New_OpenBid_Yes_No desc, a.set_max asc, a.DoNotBuy asc, a.Open_Bid desc, a.[Address] ASC
[/code]

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

spsubaseball
Starting Member

17 Posts

Posted - 2012-06-11 : 15:51:26

Thank you yosiasz, this guiding me in a better direction. One last question, if I'm trying to get a master of every property with or without a picture using that same setup of query is still taking awhile.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-11 : 16:26:56

do you have index on
1. AUC_Property table column Attorney
2. AUC_Property table column property
3. AUC_Image table column property_id_fk
4. Fin_Property table column fin_Property_Id
5. Active_Month
6. fall_off
Other than that as others have previously commented your database table design approach is causing performance issues. Do you understand that? Answer that.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Sachin.Nand

2937 Posts

Posted - 2012-06-11 : 16:38:16

@spsubaseball

Do you really need such a big order by ?

After Monday and Tuesday even the calendar says W T F ....

Sachin.Nand

2937 Posts

Posted - 2012-06-11 : 16:40:51

quote:
Originally posted by yosiasz

do you have index on
1. AUC_Property table column Attorney ....

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

An index on Attorney column is not going to help if you have predicates like this.


 m.Attorney not like '%McCalla%' 
   and m.Attorney not like '%Rubin%'

After Monday and Tuesday even the calendar says W T F ....

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-11 : 17:06:43

depends. it will help if the index is full text index. Either way, this points directly to the issue of a poorly designed db. Better would be to have a Persons table with PersonType lookup then use filter on indexes foreign key instead of the LIKE predicate

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by yosiasz

do you have index on
1. AUC_Property table column Attorney ....

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

An index on Attorney column is not going to help if you have predicates like this.

m.Attorney not like '%McCalla%'
and m.Attorney not like '%Rubin%'

After Monday and Tuesday even the calendar says W T F ....

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Sachin.Nand

2937 Posts

Posted - 2012-06-11 : 17:29:03

@spsubaseball

How is the performance when you remove the whole NOT IN clause ?Also please post the execution plan for query with and without NOT IN's

After Monday and Tuesday even the calendar says W T F ....

spsubaseball
Starting Member

17 Posts

Posted - 2012-06-12 : 07:43:21

The performance with the NOT in query works fine now. It's when I remove the NOT in is when I have a twenty seconds to return a query when its only 9,000 records out of 40,000 total records.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources