1

I am trying to implement a "Broad search" functionality, which should be used to search all fields in the DB.

The parameters of the query below is all "%horse%", and the my local DB executes this in about 15 sec.

When i use the parameter "%[^A-Za-z0-9]horse[^A-Za-z0-9]%", to make "horse" an independent word. The query takes 30 + seconds.

The db contains like 3000 objects. Can the query be optimized to run in respectable time? or should i consider something like full-text index?

GINGERT
  • 35
  • 3
  • Please provide your execution plan and `DDL` for your tables. – Devart Apr 18 '16 at 10:56
  • while dynamic SQL is a pain and can be dangerous, sometimes it is better to build the query string with just the options selected, then execute it rather than a lot of or statements – Jeremy Apr 18 '16 at 11:00
  • also check out this article on a bunch of different options. http://www.sommarskog.se/dyn-search.html – Jeremy Apr 18 '16 at 11:02
  • thank you both, as you might suspect im a rookie. Didn't even know about DDL and execution plan - i will check it out. – GINGERT Apr 18 '16 at 11:05

1 Answers1

1
  1. Using DISTINCT indicates you may have a flawed data model.
  2. Checking for NULL seems redundant.
  3. Adding space to all column values is a terrible idea which will slow down your query because it is not sargable:

Try this syntax(for all columns) in the WHERE clause instead

(tblCollection.CollectionID like '%[^A-Za-z0-9]horse[^A-Za-z0-9]%' escape '\' or
tblCollection.CollectionID like 'horse[^A-Za-z0-9]%' escape '\' or
tblCollection.CollectionID like '[^A-Za-z0-9]horse' escape '\')
Community
  • 1
  • 1
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
  • Thank you! 2. and 3. reduced time from >30sec to about 15 sec. Can you explain why you would check for all 3 cases in your example? wouldt just the first be sufficient? – GINGERT Apr 25 '16 at 10:23
  • it wpuld not select rows with columns starting or ending with horse if you exclude the last 2 – t-clausen.dk Apr 25 '16 at 12:19