Camillia
asked on
How do sites do a Global search of their data?
We have a site and it has a global search feature which is very very slow. It looks up 4 tables and does a wildcard.
This is how it looks like
1. My manager doesn't want to do a FullText Search
2. We have used indexes
3. The wildcard slows down the search, I think
4. If I join the 4 tables without the where clause, I get 2,827,623 rows. Should we dump this data in a table and just search this table? (update the table every night with fresh data)
5. Tried this as well... I broke down the joins by creating temp tables. First search Units, dumped in the temp table, #Units. Then Schedules in #Scheudles , then MasterLease in #ML. Then did the join like I have above but what if there's nothing in #Units table...meaning...search didn't bring anything back from and #units is empty. I don't the join going to be correct.
I think #4 is a good option. How do others do a global search?
This is how it looks like
select ....
from
[dbo].[Unit] AS u
LEFT join [dbo].[Schedule] AS s
ON u.[ScheduleID] = s.[ID]
LEFT OUTER JOIN [dbo].[MasterLease] AS m
ON s.[MasterLeaseID] = m.[ID]
INNER JOIN dbo.Portfolio p ON p.ID = m.PortfolioID
WHERE
u.[Deleted] <> 1
AND (1 <> s.[Status])
AND (m.[Deleted] <> 1)
AND (m.[Active] = 1)
AND
(
(s.[Sched] LIKE @searchtext)
OR (u.[SerialNo] LIKE @searchtext)
OR (u.[CustomerRef] LIKE @searchtext )
OR (u.[Model] LIKE @searchtext )
OR (u.[Manufacturer] LIKE @searchtext)
OR (u.[SiteAddress] LIKE @searchtext )
OR (m.[CustomerName] LIKE @searchtext )
OR (s.[LesseeName] LIKE @searchtext )
);
1. My manager doesn't want to do a FullText Search
2. We have used indexes
3. The wildcard slows down the search, I think
4. If I join the 4 tables without the where clause, I get 2,827,623 rows. Should we dump this data in a table and just search this table? (update the table every night with fresh data)
5. Tried this as well... I broke down the joins by creating temp tables. First search Units, dumped in the temp table, #Units. Then Schedules in #Scheudles , then MasterLease in #ML. Then did the join like I have above but what if there's nothing in #Units table...meaning...search didn't bring anything back from and #units is empty. I don't the join going to be correct.
I think #4 is a good option. How do others do a global search?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
A wildcard search using like will tend to ignore indexes anyway as it has to search every row for a match.
Thanks, I didn't know that.
I'll keep this ticket open for another day or so to see if anyone else has an input.
ASKER
thanks, guys.
ASKER