Menu

Search function includes every field?

0 votes

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

in Unknown by (700 points)

1 Answer

0 votes

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.

by (44.5k points)
edited by
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...
Welcome to the dbFront Q&A site, where you can ask questions and receive answers from other members of the community.
 | Minimalist Answer Theme by Digitizor Media
 |
Powered by Question2Answer
...