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.
