0

I'm exploring an idea to add a search box on my enterprise application that deals with car parking for staff. There is a simple textbox on the portal in the header that doesn't relate to any particular context. I deal with the following main entities:

  • Companies
  • Staff
  • System Users
  • Parking Requests

I want the user to type something like 'taxi' and the search box should show a list of results in a table with the first column showing the full keyword and a second column showing the type of entity searched.

So, the actual searching in DB will be more of a LIKE operation but we deal with that later (I'll ask the user to enter at least 3 characters on UI and then start sending queries with every keypress to keep filtering). Here is what I have right now:

SELECT
  NEWID() [Id]
 ,CompanyId [EntityId]
 ,NameE [Keyword]
 ,'C' [Type]
FROM dbo.Companies
UNION
SELECT
  NEWID() [Id]
 ,InternetUserId [EntityId]
 ,NameE [Keyword]
 ,'IU' [Type]
FROM InternetUsers
UNION
SELECT
  NEWID() [Id]
 ,InternetUserId [EntityId]
 ,UserName [Keyword]
 ,'IU' [Type]
FROM InternetUsers
UNION
SELECT
  NEWID() [Id]
 ,ParkingCardId [EntityId]
 ,ParkingCardId [Keyword]
 ,'P' [Type]
FROM ParkingCards

This works for me as a view. It works a bit slowly and with hiccups. You can see, I have the InternetUsers table entered twice for each column of interest.

Am I on the right track here? is there a better way of doing this? Is there a fancy name for this feature that I'm trying to implement?

DoomerDGR8
  • 4,840
  • 6
  • 43
  • 91
  • 1
    Why are you using `UNION` here when it's impossible for your 4 queries to return the same result set, due to the use of `NEWID`? – Thom A Jan 25 '23 at 12:16
  • As for the problem, the aforementioned `UNION` is going to harm performance. I'm also not sure *why* you have the `NEWID()` usage if this is in a `VIEW`; the value will change every time a user references the `VIEW` so why have it when it's not going to have any meaningful information. As for `InternetUsers`, unpivoting the data might be a little more performant as well. – Thom A Jan 25 '23 at 12:20
  • 1
    Yep you are on the right track, just maybe the use of `UNION ALL` can increase your performances if you know that all your results are unique, because `UNION` does a `SELECT DISTINCT` that can increase the cost of performances https://learnsql.com/blog/sql-union-all/ – Max Jan 25 '23 at 12:38
  • And if the `[Id]` is not relevant, maybe set it to `NULL` if it does not break your application, it will increase your performance as well – Max Jan 25 '23 at 12:40
  • @Larnu The results are supposed to be unique as the tables are completely different. I have NewId so that EntityFramework 5 doesn't complain in EDMX. – DoomerDGR8 Jan 25 '23 at 17:31
  • @Max, so as simple as replacing all `UNION` with `UNION ALL`? – DoomerDGR8 Jan 25 '23 at 17:32
  • So do you want unique results and *then* a `uniqueidentifier`? The `UNION` is forcing the data engine to do a (potentially expensive) *distinct* operation of the data, but as you have used `NEWID` in each query there will **never** be any duplicates so the operation is both costly and pointless – Thom A Jan 25 '23 at 17:55
  • @Larnu which is why I thinking if `UNIQUE` is the approach here or a completely different type of query. I'll ask the user to enter at least 3 characters on UI and then start sending queries with every keypress to keep filtering. – DoomerDGR8 Jan 26 '23 at 04:01
  • 1
    @HassanGulzar if you want to understand the performance gap between `UNION` and `UNION ALL` => https://stackoverflow.com/a/35628095/8404545 and https://stackoverflow.com/a/49928/8404545 – Max Jan 26 '23 at 10:31

0 Answers0