-1

I'm trying to wire up some generic search results, and would like it to perform as well as I could possibly make it happen. It's not terrible right now, but I feel like I could potentially improve upon the "where" part of this, or even condense this down to a single where clause. I'm also concerned about how this will perform as the database grows over time.

My question is, whether or not there is a better way of joining this information together (an alternative to union all, when we're basically using a similar subset of tables repeatedly). This is all pretty well indexed, and the execution plan shows that most of the costs are pretty evenly distributed. 

Select Distinct m.MLSDataId as ResultId, m.MLSNumber as ResultName, '#/mlsrecord/'+convert(varchar(20),m.MLSDataId) as ResultLink, a.StreetLine1 as Description, 'fa-home large' as Icon,
    IsNull(m.UpdateDate,m.CreateDate) as ModifiedDate, 'Property' as TypeName
    From MLSDatas m
    Inner Join Addresses a on a.AddressId = m.AddressFK 
    Left Join MLSContacts mc on mc.MLSDataFK = m.MlsDataId
    Left Join Contacts c on ContactId = ContactFK
    Left Join People p on PersonId = PersonFK
    Left Join MLSAgents ma on ma.MLSDataFK = m.MlsDataId
    Left Join Agents ag on ag.AgentId = ma.AgentFK
    Left Join People p2 on p2.PersonId = ag.PersonFK
    Left Join PersonPhones pp on pp.PersonFK = p.PersonId
    Left Join Phones ph on ph.PhoneId = pp.PhoneFK
    Left Join PersonEmails pe on pe.PersonFK = p.PersonId
    Left Join Emails e on e.EmailId = pe.EmailFK
    Where m.MLSNumber like '%'+@term+'%' or StreetLine1 like '%'+@term+'%' 
    or p.FirstName like '%'+@term+'%' or p.LastName like '%'+@term+'%' or p.FirstName + ' ' + p.LastName like '%'+@term+'%'
    or p2.FirstName like '%'+@term+'%' or p2.LastName like '%'+@term+'%' or p2.FirstName + ' ' + p2.LastName like '%'+@term+'%'
    or ph.PhoneNumber like '%'+@term+'%'
    or e.EmailAddress like '%'+@term+'%'

    Union ALL

    Select Distinct l.ListingId as ResultId, l.DisplayTitle as ResultName, '#/listing/'+convert(varchar(20),l.ListingId) as ResultLink, l.DisplayTitle as Description, 'fa-globe large' as Icon,
    IsNull(m.UpdateDate,m.CreateDate) as ModifiedDate, 'Listing' as TypeName
    From Listings l
    Inner Join MLSDatas m on m.MLSDataId = l.MLSDataFK
    Inner Join Addresses a on a.AddressId = m.AddressFK 
    Left Join MLSContacts mc on mc.MLSDataFK = MlsDataId
    Left Join Contacts c on ContactId = ContactFK
    Left Join People p on PersonId = PersonFK
    Left Join PersonPhones pp on pp.PersonFK = p.PersonId
    Left Join Phones ph on ph.PhoneId = pp.PhoneFK
    Left Join PersonEmails pe on pe.PersonFK = p.PersonId
    Left Join Emails e on e.EmailId = pe.EmailFK
    Where m.MLSNumber like '%'+@term+'%' or StreetLine1 like '%'+@term+'%' 
    or p.FirstName like '%'+@term+'%' or p.LastName like '%'+@term+'%' or p.FirstName + ' ' + p.LastName like '%'+@term+'%'
    or ph.PhoneNumber like '%'+@term+'%'
    or e.EmailAddress like '%'+@term+'%'

    Union All

    Select Distinct c.ContactId as ResultId, p.FirstName + ' ' + p.LastName as ResultName, '#/contact/'+convert(varchar(20),c.ContactId) as ResultLink, p.FirstName + ' ' + p.LastName as Description, 'fa-book large' as Icon,
    IsNull(p.UpdateDate,p.CreateDate) as ModifiedDate, 'Contact' as TypeName
    From MLSDatas m
    Inner Join Addresses a on a.AddressId = m.AddressFK 
    Left Join MLSContacts mc on mc.MLSDataFK = m.MlsDataId
    Left Join Contacts c on ContactId = ContactFK
    Left Join People p on PersonId = PersonFK
    Left Join PersonPhones pp on pp.PersonFK = p.PersonId
    Left Join Phones ph on ph.PhoneId = pp.PhoneFK
    Left Join PersonEmails pe on pe.PersonFK = p.PersonId
    Left Join Emails e on e.EmailId = pe.EmailFK
    Where m.MLSNumber like '%'+@term+'%' or StreetLine1 like '%'+@term+'%' 
    or p.FirstName like '%'+@term+'%' or p.LastName like '%'+@term+'%' or p.FirstName + ' ' + p.LastName like '%'+@term+'%'
    or ph.PhoneNumber like '%'+@term+'%'
    or e.EmailAddress like '%'+@term+'%'

    Union All

    Select Distinct ag.AgentId as ResultId, p.FirstName + ' ' + p.LastName as ResultName, '#/agent/'+convert(varchar(20),ag.AgentId) as ResultLink, p.FirstName + ' ' + p.LastName as Description, 'fa-suitcase large' as Icon,
    IsNull(p.UpdateDate,p.CreateDate) as ModifiedDate, 'Agent' as TypeName
    From MLSDatas m
    Inner Join Addresses a on a.AddressId = m.AddressFK 
    Left Join MLSAgents ma on ma.MLSDataFK = m.MlsDataId
    Left Join Agents ag on ag.AgentId = ma.AgentFK
    Left Join People p on PersonId = ag.PersonFK
    Left Join PersonPhones pp on pp.PersonFK = p.PersonId
    Left Join Phones ph on ph.PhoneId = pp.PhoneFK
    Left Join PersonEmails pe on pe.PersonFK = p.PersonId
    Left Join Emails e on e.EmailId = pe.EmailFK
    Where m.MLSNumber like '%'+@term+'%' or StreetLine1 like '%'+@term+'%' 
    or p.FirstName like '%'+@term+'%' or p.LastName like '%'+@term+'%' or p.FirstName + ' ' + p.LastName like '%'+@term+'%'
    or ph.PhoneNumber like '%'+@term+'%'
    or e.EmailAddress like '%'+@term+'%'

Thanks in advance!

Middle Class Lowlife
  • 33
  • 6
  • It might not be your fault, but you should consider denormalizing your database. Then for sure you will have easier time querying it. – Paul Kar. Oct 01 '14 at 18:58
  • There are lots of many to many relationships, and we're also using it with Entity Framework. Addresses / phones / email / people / etc get stored once. The naming convention for the most part is also relational, and makes it easier for the next guy walking in to identify where the foreign key is pointing to... Denormalizing becomes a real headache when a person (first and last name etc) in this database could apply to a User, Employee, Agent, Contact, or other.. That same person is likely to be more than 1 or two of those entities at the same time. – Middle Class Lowlife Oct 01 '14 at 19:34
  • I see your point, but I have to disagree. You code looks very messy and cumbersome and it's mostly due to its design. Even if it's painful to denormalize it, it will be a huge benefit in the future. I also included some feedback in my answer (to your reasoning why not to denormalize your database). Hope it helps. – Paul Kar. Oct 01 '14 at 19:52
  • You are worried about performance and checked a solution with same load time. And my answer that would fix performance is -1. – paparazzo Oct 02 '14 at 14:23

2 Answers2

-1

Lot of problems
You break the left on the where

You could bring this out into a #temp so it is only evaluated once
But since you don't always label the table I had to do some guessing
You then join to #temp
And declare PK on #Temp as it should help the query optimizer 

insert into #temp (ID)
select m.MLSDataId   
  from MLSDatas m --
  Join People p on PersonId = PersonFK  --
  Join PersonPhones pp on pp.PersonFK = p.PersonId
  Join Phones ph on ph.PhoneId = pp.PhoneFK  --
  Join PersonEmails pe on pe.PersonFK = p.PersonId
  Join Emails e on e.EmailId = pe.EmailFK --  
Where m.MLSNumber like '%'+@term+'%' 
   or p.FirstName like '%'+@term+'%' 
   or p.LastName like '%'+@term+'%' 
   or p.FirstName + ' ' + p.LastName like '%'+@term+'%'
   or ph.PhoneNumber like '%'+@term+'%'
   or e.EmailAddress like '%'+@term+'%'


...
From Listings l 
join #temp as m 
  on m.MLSDataId = l.MLSDataFK

  on #tempID =
paparazzo
  • 44,497
  • 23
  • 105
  • 176
  • It wasn't me, just saying. – Paul Kar. Oct 02 '14 at 15:38
  • @PaulKar. And I did not down vote you. Even though I don't think you answer addresses efficiency. – paparazzo Oct 02 '14 at 15:41
  • after looking and thinking about your answer I can't seem to see where did several tables go (e.g. Addresses, MLSContacts, Contacts, MLSAgents, Agents)? In addition to that you only select 1 column when Brad used much more columns. Seems like your answer is not complete. – Paul Kar. Oct 02 '14 at 16:01
  • @PaulKar. Do I need to draw a picture? You bring that into a #temp and then join to the #temp 4 times. #temp is materialized and only evaluated once. The hard stuff is only done once. If OP is not even going to label what table the columns c0me from and supply a definition of MLSDatas I am not going to take more effort. – paparazzo Oct 02 '14 at 16:42
-1

I would suggest using a CTE, but as I mentioned in a comment above, you should consider denormalizing your database design. Even if, "a person (first and last name etc) in this database could apply to a User, Employee, Agent, Contact, or other". One suggestion for that problem is to use type (e.g. User, Employee, Agent, Contact, or other). About, "makes it easier for the next guy walking in to identify where the foreign key is pointing to", I don't know, maybe. However, it does look very messy. Your choice at the end.

Here is what I suggest if you end up not denormalizing: 

with cte_noWhere as
(
Select Distinct m.MLSDataId as ResultId, m.MLSNumber as ResultName, '#/mlsrecord/'+convert(varchar(20),m.MLSDataId) as ResultLink, a.StreetLine1 as Description, 'fa-home large' as Icon,
IsNull(m.UpdateDate,m.CreateDate) as ModifiedDate, 'Property' as TypeName,
m.MLSNumber as MLSNumber, StreetLine1, p.FirstName as FirstName, p.LastName as LastName, ph.PhoneNumber,
e.EmailAddress
From MLSDatas m
Inner Join Addresses a on a.AddressId = m.AddressFK 
Left Join MLSContacts mc on mc.MLSDataFK = m.MlsDataId
Left Join Contacts c on ContactId = ContactFK
Left Join People p on PersonId = PersonFK
Left Join MLSAgents ma on ma.MLSDataFK = m.MlsDataId
Left Join Agents ag on ag.AgentId = ma.AgentFK
Left Join People p2 on p2.PersonId = ag.PersonFK
Left Join PersonPhones pp on pp.PersonFK = p.PersonId
Left Join Phones ph on ph.PhoneId = pp.PhoneFK
Left Join PersonEmails pe on pe.PersonFK = p.PersonId
Left Join Emails e on e.EmailId = pe.EmailFK
Where p2.FirstName like '%'+@term+'%' or p2.LastName like '%'+@term+'%' or p2.FirstName + ' ' + p2.LastName like '%'+@term+'%'

Union ALL

Select Distinct l.ListingId as ResultId, l.DisplayTitle as ResultName, '#/listing/'+convert(varchar(20),l.ListingId) as ResultLink, l.DisplayTitle as Description, 'fa-globe large' as Icon,
IsNull(m.UpdateDate,m.CreateDate) as ModifiedDate, 'Listing' as TypeName,
m.MLSNumber as MLSNumber, StreetLine1, p.FirstName as FirstName, p.LastName as LastName, ph.PhoneNumber,
e.EmailAddress
From Listings l
Inner Join MLSDatas m on m.MLSDataId = l.MLSDataFK
Inner Join Addresses a on a.AddressId = m.AddressFK 
Left Join MLSContacts mc on mc.MLSDataFK = MlsDataId
Left Join Contacts c on ContactId = ContactFK
Left Join People p on PersonId = PersonFK
Left Join PersonPhones pp on pp.PersonFK = p.PersonId
Left Join Phones ph on ph.PhoneId = pp.PhoneFK
Left Join PersonEmails pe on pe.PersonFK = p.PersonId
Left Join Emails e on e.EmailId = pe.EmailFK

Union All

Select Distinct c.ContactId as ResultId, p.FirstName + ' ' + p.LastName as ResultName, '#/contact/'+convert(varchar(20),c.ContactId) as ResultLink, p.FirstName + ' ' + p.LastName as Description, 'fa-book large' as Icon,
IsNull(p.UpdateDate,p.CreateDate) as ModifiedDate, 'Contact' as TypeName,
m.MLSNumber as MLSNumber, StreetLine1, p.FirstName as FirstName, p.LastName as LastName, ph.PhoneNumber,
e.EmailAddress
From MLSDatas m
Inner Join Addresses a on a.AddressId = m.AddressFK 
Left Join MLSContacts mc on mc.MLSDataFK = m.MlsDataId
Left Join Contacts c on ContactId = ContactFK
Left Join People p on PersonId = PersonFK
Left Join PersonPhones pp on pp.PersonFK = p.PersonId
Left Join Phones ph on ph.PhoneId = pp.PhoneFK
Left Join PersonEmails pe on pe.PersonFK = p.PersonId
Left Join Emails e on e.EmailId = pe.EmailFK

Union All

Select Distinct ag.AgentId as ResultId, p.FirstName + ' ' + p.LastName as ResultName, 
'#/agent/'+convert(varchar(20),ag.AgentId) as ResultLink, p.FirstName + ' ' + p.LastName as Description, 
'fa-suitcase large' as Icon,
IsNull(p.UpdateDate,p.CreateDate) as ModifiedDate, 'Agent' as TypeName,
m.MLSNumber as MLSNumber, StreetLine1, p.FirstName as FirstName, p.LastName as LastName, ph.PhoneNumber,
e.EmailAddress
From MLSDatas m
Inner Join Addresses a on a.AddressId = m.AddressFK 
Left Join MLSAgents ma on ma.MLSDataFK = m.MlsDataId
Left Join Agents ag on ag.AgentId = ma.AgentFK
Left Join People p on PersonId = ag.PersonFK
Left Join PersonPhones pp on pp.PersonFK = p.PersonId
Left Join Phones ph on ph.PhoneId = pp.PhoneFK
Left Join PersonEmails pe on pe.PersonFK = p.PersonId
Left Join Emails e on e.EmailId = pe.EmailFK
)
select *
from cte_noWhere w
Where w.MLSNumber like '%'+@term+'%' or w.StreetLine1 like '%'+@term+'%' 
    or w.FirstName like '%'+@term+'%' 
        or w.LastName like '%'+@term+'%' or w.FirstName + ' ' + w.LastName like '%'+@term+'%'
    or w.PhoneNumber like '%'+@term+'%'
    or w.EmailAddress like '%'+@term+'%'

I use most of your code in the CTE and pull out the common where clause. This might work faster and more efficient, but I am not sure. If it is then it's probably because you now do one where clause instead of 4.

Paul Kar.
  • 1,293
  • 2
  • 21
  • 32
  • I hadn't considered a CTE. I gave it a go, and it's virtually identical in the load times (>1 second), although cleaner (so I'll take it). With regard to the type field, this would still allow for multiple rows of the same name, which I've been trying to avoid. When a person has multiple phones and emails, those don't tie back to each of the rows with the unique types you're suggesting. In addition, this pattern works well in the C# application, as the user inherits a type of "Person", and so do the other person type entities. It seems overly complicated, but it works for many reasons. – Middle Class Lowlife Oct 01 '14 at 20:30
  • Also... I ran out of lines... Thank you for taking the time to answer. I'm still weighing options with database design, but we have a customer that was promised this application about 6 months ahead of when I quoted it... so I'm in sort of a crunch for time now. – Middle Class Lowlife Oct 01 '14 at 20:32
  • @BradL I totally understand. Been there, done that. Just thought you should be aware of alternatives and the reason why another way might work better. If my answer was helpful an upvote or choosing the answer by clicking the check mark would be appreciated. :) Good luck. – Paul Kar. Oct 01 '14 at 20:40
  • A cte is just syntax. It is evaluated. – paparazzo Oct 02 '14 at 01:02
  • 1
    @Blam could you elaborate on your point? Not sure what you mean. – Paul Kar. Oct 02 '14 at 06:19
  • What part of a cte is just syntax is not clear? It is not materialized. You may have one one where statement but that does not mean one where execution. As stated by OP - identical load times. My answer with #temp is actually evaluated once - and it has a -1. Those left joins are broken by the where - a join would be cleaner and most likely faster. Did you vote me down? – paparazzo Oct 02 '14 at 14:25
  • @Blam I can assure you I have not voted you down. I rarely vote people down. More to your point, why would you state that a where clause is executed more then once if I have CTE? To quote Microsoft website, "A CTE is similar to a derived table". Hence when I run the CTE I get a table and then only once the where clause runs (not 4 times). – Paul Kar. Oct 02 '14 at 15:50
  • And a derived table in not materialized. No you do NOT get a table from a cte - you get syntax. Why would even want that where to run once against as virtural table. That virtual table would need to return all possible rows. It is way more efficient to eliminate rows early - that is what the query optimizer does. – paparazzo Oct 02 '14 at 15:59
  • @Blam take a look here http://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx – Paul Kar. Oct 02 '14 at 16:03
  • I didn't say you get a table, I said "A CTE is similar to a derived table". – Paul Kar. Oct 02 '14 at 16:04
  • You said "Hence when I run the CTE I get a table". Neither a derived nor a CTE are materialized - they are syntax only. Look it up. There are even top 100% tricks to get cte to materialize. Oh, so you got a rare down vote - good for you. – paparazzo Oct 02 '14 at 16:10
  • I misspoke, that happens. I suggest you don't take things personal. I wish you a good day. – Paul Kar. Oct 02 '14 at 16:12
  • http://dba.stackexchange.com/questions/13112/whats-the-difference-between-a-cte-and-a-temp-table – paparazzo Oct 02 '14 at 16:16