question

martingp avatar image
martingp asked

How to get best performance from filter definition

Hi experts,

I'm relatively new to SQL server and I have a challenge taking the right approach and creating the correct select statement for optimal performance in the scenario below.

Basically I need to return books matching a filter definition stored in the database with fast and efficient performance.

Table 1: Books (book_id, publisher_id, type, name, publication date)

Listing of about 1 million books.

Table 2: Book_content (book_id, content)

Content of a book. Full text search indexing on content.

Table 3: Filter (user_id, filter_id, filter_name)

Contains filter definitions that different users have against the books. The query I'm after will return the results for just 1 filter (with the criteria listed below)

Table 4: Filter_publisher (filter_publisher_id, filter_id, free_text_search)

List of publishers (publisher_id) for the filter - typically 30-100 publishers. The free_text_search has optional CONTAINS sql search statement e.g. 'forrest NEAR dear' - which are different for each publisher under the filter.

Table 5: Filter_type (type_type_id, type_id)

Contains filters on (book) type for each publisher (see table 1), typically 20-50 types out of a 100, so each publisher in the filter have a limited scope based on the type_ids specified for the each filter_publisher record.

So with that setup in mind I have been struggling to figure out the most efficient approach to create a select statement that returns the books (table 1) matching a particular filter fast considering multiple publishers, each potentially with a free text search or not, and each publisher with a different set of book types.

I have tried an approach where I loop through the publishers in Filter_Publisher and for each record create a text string with the SQL statement containing the type_ids and sql contains free text search and then UNION ALL but that doesn't perform very well and it results in a very large SQL statement.

Any advice on what approach to take and how a well performing select statement could look like would be much appreciated.

Thank you in advance for any input.

performanceselectcontains
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.