October 28, 2007 at 4:17 pm
Hi,
I'm executing a nested queries consisting of LEFT OUTER JOIN for:
Duration = 27 sec;
Reads = 1690;
Number of users = 1.
This is extremely slow for a small set of data. I'm afraid that when we have a larger dataset and more concurrent users that query will take forward.
What am I doing wrong for my query to be taking too long?
Here's the query:
SELECT B.Business_Id as Business1_0_,
B.Place_Of_Business_Id as Place2_30_0_,
B.Business_Type_Id as Business3_30_0_,
B.Business_Name as Business4_30_0_,
B.Business_Description as Business5_30_0_,
B.Last_Update_Timestamp as Last6_30_0_
FROM Busines as B
LEFT OUTER JOIN Business_Service as BS
ON B.Business_Id = BS.Business_Id
LEFT OUTER JOIN Business_Service_Category as BSC
ON B.Business_Id = BSC.Business_Id
LEFT OUTER JOIN Business_Sub_Category as BSSC
ON B.Business_Id = BSSC.Business_Id
where B.Business_Id IN (
Select B.Business_Id from Busines as B
JOIN Business_Address as BA ON B.Business_Id = BA.Business_Id
JOIN Address as A ON A.Address_Id = BA.Address_Id
WHERE A.City_Name like '%New York%'
and A.state = 'NY') AND ( FREETEXT(B.Business_Name, 'Designer')
or BSC.Service_Category_Id IN
( Select SC.Service_Category_Id from Service_Category as SC
where FREETEXT(SC.Service_Category_Name, 'Designer') )
or BSSC.Sub_Category_Id IN (
Select SSC.Service_Sub_Category_Id from Service_Sub_Category as SSC
where FREETEXT(SSC.Service_Sub_Category_Name, 'Designer') )
or BS.Service_Id IN (
Select S.Service_Id from Service as S
where FREETEXT(S.Service_Name, 'Designer') ) )
----------------------------------------------------------------------------------------------------------------------------------------
Here's the SQL Query Plan:
|--Nested Loops(Left Semi Join, OUTER REFERENCES:(.[Business_Id], [BS].[Service_Id], [BSC].[Service_Category_Id], [BSSC].[Sub_Category_Id]))
|--Nested Loops(Left Outer Join, WHERE:([PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Sub_Category].[Business_Id] as [BSSC].[Business_Id]))
| |--Nested Loops(Left Outer Join, WHERE:([PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Service_Category].[Business_Id] as [BSC].[Business_Id]))
| | |--Nested Loops(Left Outer Join, WHERE:([PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Service].[Business_Id] as [BS].[Business_Id]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES:(.[Business_Id]))
| | | | |--Sort(DISTINCT ORDER BY:(.[Business_Id] ASC))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES:([BA].[Business_Id]))
| | | | | |--Hash Match(Inner Join, HASH:([A].[Address_Id])=([BA].[Address_Id]), RESIDUAL:([PB].[dbo].[Address].[Address_Id] as [A].[Address_Id]=[PB].[dbo].[Business_Address].[Address_Id] as [BA].[Address_Id]))
| | | | | | |--Clustered Index Scan(OBJECT:([PB].[dbo].[Address].[PK__Address__0519C6AF] AS [A]), WHERE:([PB].[dbo].[Address].[State] as [A].[State]='NY' AND [PB].[dbo].[Address].[City_Name] as [A].[City_Name] like '%JayVille%'))
| | | | | | |--Index Scan(OBJECT:([PB].[dbo].[Business_Address].[ClusteredIDX_Business_Address] AS [BA]))
| | | | | |--Index Seek(OBJECT:([PB].[dbo].[Busines].[UI_ukBusiness] AS ), SEEK:(.[Business_Id]=[PB].[dbo].[Business_Address].[Business_Id] as [BA].[Business_Id]) ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT:([PB].[dbo].[Busines].[PK_Busines] AS ), SEEK:(.[Business_Id]=[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]) ORDERED FORWARD)
| | | |--Clustered Index Scan(OBJECT:([PB].[dbo].[Business_Service].[ClusterIDX_Business_Service] AS [BS]))
| | |--Clustered Index Scan(OBJECT:([PB].[dbo].[Business_Service_Category].[ClusterIDX_Business_Service_Category] AS [BSC]))
| |--Clustered Index Scan(OBJECT:([PB].[dbo].[Business_Sub_Category].[ClusterIDX_Business_Sub_Category] AS [BSSC]))
|--Concatenation
|--Filter(WHERE:([PB].[dbo].[Busines].[Business_Id] as .[Business_Id] = [Full-text Search Engine].))
| |--Remote Scan(OBJECT:(FREETEXT))
|--Nested Loops(Left Semi Join)
| |--Clustered Index Seek(OBJECT:([PB].[dbo].[Service_Category].[PK__Service_Category__15502E78] AS [SC]), SEEK:([SC].[Service_Category_Id]=[PB].[dbo].[Business_Service_Category].[Service_Category_Id] as [BSC].[Service_Category_Id]) ORDERED FORWARD)
| |--Filter(WHERE:([Full-text Search Engine]. = [PB].[dbo].[Business_Service_Category].[Service_Category_Id] as [BSC].[Service_Category_Id]))
| |--Remote Scan(OBJECT:(FREETEXT))
|--Nested Loops(Left Semi Join)
| |--Clustered Index Seek(OBJECT:([PB].[dbo].[Service_Sub_Category].[PK_Service_Sub_Category] AS [SSC]), SEEK:([SSC].[Service_Sub_Category_Id]=[PB].[dbo].[Business_Sub_Category].[Sub_Category_Id] as [BSSC].[Sub_Category_Id]) ORDERED FORWARD)
| |--Filter(WHERE:([Full-text Search Engine]. = [PB].[dbo].[Business_Sub_Category].[Sub_Category_Id] as [BSSC].[Sub_Category_Id]))
| |--Remote Scan(OBJECT:(FREETEXT))
|--Nested Loops(Left Semi Join)
|--Clustered Index Seek(OBJECT:([PB].[dbo].[Service].[PK__Service__117F9D94] AS ), SEEK:(.[Service_Id]=[PB].[dbo].[Business_Service].[Service_Id] as [BS].[Service_Id]) ORDERED FORWARD)
|--Filter(WHERE:([Full-text Search Engine]. = [PB].[dbo].[Business_Service].[Service_Id] as [BS].[Service_Id]))
|--Remote Scan(OBJECT:(FREETEXT))
Please Help....
October 29, 2007 at 12:22 am
I am not exactly sure because I cannot see your data, but it seems to me you need to get rid of all those nested OR clauses and turn them into UNIONs.
Your query would then looks SOMETHING like this:
SELECT B.Business_Id as Business1_0_,
B.Place_Of_Business_Id as Place2_30_0_,
B.Business_Type_Id as Business3_30_0_,
B.Business_Name as Business4_30_0_,
B.Business_Description as Business5_30_0_,
B.Last_Update_Timestamp as Last6_30_0_
FROM Busines as B
LEFT OUTER JOIN Business_Service as BS
ON B.Business_Id = BS.Business_Id
LEFT OUTER JOIN Business_Service_Category as BSC
ON B.Business_Id = BSC.Business_Id
LEFT OUTER JOIN Business_Sub_Category as BSSC
ON B.Business_Id = BSSC.Business_Id
WHERE A.City_Name like '%New York%'
and A.state = 'NY')
AND ( FREETEXT(B.Business_Name, 'Designer')
UNION
SELECT B.Business_Id as Business1_0_,
B.Place_Of_Business_Id as Place2_30_0_,
B.Business_Type_Id as Business3_30_0_,
B.Business_Name as Business4_30_0_,
B.Business_Description as Business5_30_0_,
B.Last_Update_Timestamp as Last6_30_0_
FROM Busines as B
LEFT OUTER JOIN Business_Service as BS
ON B.Business_Id = BS.Business_Id
LEFT OUTER JOIN Business_Service_Category as BSC
ON B.Business_Id = BSC.Business_Id
LEFT OUTER JOIN Business_Sub_Category as BSSC
ON B.Business_Id = BSSC.Business_Id
where BSC.Service_Category_Id IN ( Select SC.Service_Category_Id
from Service_Category as SC
where FREETEXT(SC.Service_Category_Name, 'Designer')
UNION
SELECT B.Business_Id as Business1_0_,
B.Place_Of_Business_Id as Place2_30_0_,
B.Business_Type_Id as Business3_30_0_,
B.Business_Name as Business4_30_0_,
B.Business_Description as Business5_30_0_,
B.Last_Update_Timestamp as Last6_30_0_
FROM Busines as B
LEFT OUTER JOIN Business_Service as BS
ON B.Business_Id = BS.Business_Id
LEFT OUTER JOIN Business_Service_Category as BSC
ON B.Business_Id = BSC.Business_Id
LEFT OUTER JOIN Business_Sub_Category as BSSC
ON B.Business_Id = BSSC.Business_Id
where BSSC.Sub_Category_Id IN (Select SSC.Service_Sub_Category_Id
from Service_Sub_Category as SSC
where FREETEXT(SSC.Service_Sub_Category_Name, 'Designer')
UNION
SELECT B.Business_Id as Business1_0_,
B.Place_Of_Business_Id as Place2_30_0_,
B.Business_Type_Id as Business3_30_0_,
B.Business_Name as Business4_30_0_,
B.Business_Description as Business5_30_0_,
B.Last_Update_Timestamp as Last6_30_0_
FROM Busines as B
LEFT OUTER JOIN Business_Service as BS
ON B.Business_Id = BS.Business_Id
LEFT OUTER JOIN Business_Service_Category as BSC
ON B.Business_Id = BSC.Business_Id
LEFT OUTER JOIN Business_Sub_Category as BSSC
ON B.Business_Id = BSSC.Business_Id
where BS.Service_Id IN (Select S.Service_Id
from Service as S
where FREETEXT(S.Service_Name, 'Designer')
Again, I am not sure this is EXACTLY right, but it's close. Once you convert it to UNION it should run much faster.
Also, if you went with EXISTS() instead of IN(), you would lower your I/O and it would run even faster.
Cheers.
P.S. Don't forget to use the code tags around your code to make it more readable for everyone as I did above,
Thanks.
G. Milner
October 29, 2007 at 12:48 am
You've got quite a few table scans in that query (Address, Business_Service, Business_Service_Category, Business_Sub_Category). Without the schema I can't offer suggestions, but if those are large tables, you may want to consider some index changes.
Try G. Milner's solution. It may help, though the trick of replacing OR with union is generally less effective on 2005 (because the optimiser has more options for OR than it had in 2000, including mmost of the options it has for unions). If the table scans are still present, then look at your indexing
p.s. since you're using SQL 2005, the best way to show an exec plan to the forum readers is to save the graphical plan as a .sqlplan file, then attach that to your post.
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
October 29, 2007 at 1:19 am
Just my 2 ct.
I'd first convert the where ... in (select .. ) or ... in ( select ...)
to correlated subqueries lik :
WHERE A.City_Name like '%New York%'
and A.state = 'NY')
AND ( FREETEXT(B.Business_Name, 'Designer')
or exists ( Select * from Service_Category as SC
where FREETEXT(SC.Service_Category_Name, 'Designer')
and SC.Service_Category_Id = BSC.Service_Category_Id )
or exists ( Select 1
from Service_Sub_Category as SSC
where FREETEXT (SSC.Service_Sub_Category_Name, 'Designer' )
and SSC.Service_Sub_Category_Id = BSSC.Sub_Category_Id )
or exists ( Select 1 from Service as S
where FREETEXT(S.Service_Name, 'Designer')
and S.Service_Id = BS.Service_Id )
)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 29, 2007 at 2:38 am
Union wouldnt make big difference that will increase the execution Time.
October 29, 2007 at 2:59 am
just another thought ...
avoid FREETEXT !
Freetext <> like !
Maybe a like predicate serves you better.
Check BOL !
WHERE A.City_Name like '%New York%' and A.state = 'NY')
AND ( B.Business_Name like '%Designer%')
or exists ( Select * from Service_Category as SC
where SC.Service_Category_Name like '%Designer%')
and SC.Service_Category_Id = BSC.Service_Category_Id )....
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 29, 2007 at 8:51 am
The biggest thing for me on this query is that 1690 page reads for the query ran to 27 seconds to get the output. That is just way out of line. 27 seconds of run time should be able to get you low 4 figures of of I/Os. Are you sure there wasn't any blocking during query execution? Also, how much total data is returned? Perhaps a good bit of that time is simply presenting the data on the client side?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 29, 2007 at 5:01 pm
Thanks for your responses. However, after adding the UNION to query, the execution time was still slow.
We chose the FREETEXT function, because it does phonetic word matching just in user misspells the search text and matches words in different order as well.
Finally, the query only returns 10 records back and there are less than 1000 records in the test database.
The FreeText functionality seems to be a powerful tool that why we decide to use it; for its high precise matching.
Please let me know what else I can try, because I've exhausted other alternatives.
October 29, 2007 at 6:33 pm
Also...I forgot to add the my query logic...maybe that's the problem 🙂
1. Find all Businesses in a given city and state.
2. From the Businesses from step #1, extract the businesses that have businessName, categoryName, subcategoryName or serviceName matching the user's KEYWORD (e.g. "designer").
I hope this helps....
October 29, 2007 at 9:54 pm
Hi,
Did you try using EXISTS() -- correlated subqueries -- instead of IN() along with the UNION ?
That might help, as well.
Cheers.
G. Milner
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply