Search function includes every field?

Question

I have a table with a large number of columns. Searching in the box at the top of the table takes longer than expected (a minute or more). I traced to see what's going on and it appears that every column in the table is search for the value put into the search box (not just those designated in the "Search Fields" tab).
There are 17 columns in the table. Only six of the columns are selected as the "Search Fields".

One of the columns in "Search Fields" has a label that contains the month name and year. So I search for 'Jul 2019' and the trace shows these params in the query:

   @P0M=N'%Jul%'

, @P1M=N'%Jul%'
, @P2M=N'%Jul%'
, @P3M=N'%Jul%'
, @P4M=N'%Jul%'
, @P5M=N'%Jul%'
, @P6M=N'%Jul%'
, @P7M=N'%Jul%'
, @P8M=N'%Jul%'
, @P9M=N'%Jul%'
, @P10M=N'%Jul%'
, @P11M=N'%Jul%'
, @P12M=N'%Jul%'
, @P13M=N'%2019%'
, @P14M=N'%2019%'
, @P15M=N'%2019%'
, @P16M=N'%2019%'
, @P17M=N'%2019%'
, @P18M=N'%2019%'
, @P19M=N'%2019%'
, @P20M=N'%2019%'
, @P21M=N'%2019%'
, @P22M=N'%2019%'
, @P23M=N'%2019%'
, @P24M=N'%2019%'
, @P25M=N'%2019%'
, @P26M=N'%2019%'
, @P27M=N'%2019%'
, @P28M=N'%2019%'
, @P29M=N'%2019%'

The WHERE clause has many OR statements with nested queries inside. Like this:
OR(
SELECT ISNULL(CONVERT(VARCHAR(255), P.columnname), '')
FROM dbo.table1 P
WHERE P.columnname=T.columnname
AND P.columnname=T.columnname
) LIKE @P4M

It looks like every column is compared to both search terms 'Jul' and '2019'.

Is this the expected search behavior or is there something I can do to limit the searched columns (other than selecting them in "Search Fields")?

Thanks

Answer 1

When you select the Search Fields you are specifying which columns should be available for the Advanced Search.

The Quick Search at the top is a very different type of search. The intention behind the Quick Search is to give the users a simple to understand full table search.

The Quick Search will parse the search terms as entered and use them to search:

• all displayed table columns with a matching type.
• all displayed table columns as they are displayed or formatted.

If the source table is very large or if a large number of table columns are selected then this could result in a slow query. The best way to improve the Quick Search performance is to minimize the number of displayed table columns.

In contrast the Advanced search allows for Range Searches on individual fields, searching for Null values, selection of multiple lookup values and more.

Comments

Yes, this makes sense. However, I've added these columns in order to enable the multi-key FK constraints on the drop down menus. Perhaps quick search can be configurable; either limiting the columns search to the ones specified in the form or user-specified prompts like google searches like "Column:SearchString" (e.g. Customer: smith)?

---

I will have to think on this...

---

With regards the "Quick Search" it would be good if there was an option to select which fields are used in the search.   For example, if there's a calculated field on the table view (as it's useful for users) such as a "Count" of records (in a SQL view)

If you then use a search which hits these criteria (e.g. search for a number), it then searches through this view for the value.

Graphically, it makes sense.  But from a user experience, it can lead to a very slow search as it has to search the SQL view as well as the static data.  Especially if the SQL view is very complex.

Alternative work around is to use SQL to store the information within the table itself, however this would mean that there needs to be a routine to move this data from the view and put in the table.

---

@JRevell, Could I ask you to turn that into a Feature request?  I will attempt to work that into the next release or so.