Indexed view to optimize search over multiple tables

Question

I have a question concerning an idea I have about optimizing performance of a complex search query. This is the situation:

Base table, with the fields to search on:

Server (uniqueNumber, serverName, hostName)

Tables to join, with the fields to search on:

Customer (Company, vatNumber)
CustomerContact (firstName, lastName, email, telephone, mobile)
Ip (ipAddress)

The idea I had was to create an indexed view like this:

viewServers (ServerID, Term)

(clustered index on ServerID, non-clustered on Term)

which is a union of all possible data:

SELECT * FROM (
    SELECT s.ServerID AS ServerID, c.Company AS Term FROM Customer c 
        INNER JOIN Server s ON s.CustomerID = c.CustomerID
    UNION
    SELECT s.ServerID AS ServerID, c.vatNumber AS Term FROM Customer c 
        INNER JOIN Server s ON s.CustomerID = c.CustomerID
    UNION
    SELECT s.ServerID AS ServerID, c.firstName AS Term FROM CustomerContact cc 
        INNER JOIN Customer s ON s.CustomerID = cc.CustomerID
        INNER JOIN Server s ON s.CustomerID = c.CustomerID
    UNION
    SELECT s.ServerID AS ServerID, i.ipAddress AS Term FROM Ip i 
        INNER JOIN Server s ON i.ServerID = i.ServerID
    UNION
) AS result ORDER BY Term

When I want to search, I was planning to this:

SELECT * FROM Server WHERE ServerID IN (SELECT ServerID FROM viewServers WHERE Term LIKE @Term

Good or bad idea?

You should look at full text indexing rather than indexed views for this. — Martin Smith, Oct 03 '12 at 16:22

score 0 · Answer 1 · answered Oct 03 '12 at 16:25

It's hard to say if it's a good or bad idea without knowing a lot of other things about your database. And indexed view will probably help that particular query but may hurt other database operations. Also, it's not a great idea to create a non-unique clustered index (ServerID).

Indexed view to optimize search over multiple tables

1 Answers1