Why does SQL Server take much longer to run this query than SQLite?
<p>I've been writing a process to evaluate computers against specific business rules to figure out it they are eligible to have a product installed on them. I'd initially been working with a small SQLite database, but the project started getting big enough that I'm transitioning it to MS SQL Server. There are about 500k rows in the database.</p>

<p>A query that took about 30 seconds in SQLite is taking close to 30 minutes on the SQL server. I would expect it to be much faster, given that I'm moving to a dedicated server with a lot more system resources.</p>

<p>The query is below. The basic idea is that I have a table with computer information and a table with rules, and different rules need to be compared to different columns in the computer information table. I also need to account for "inclusions" - if a computer has an inclusion rule, that trumps any exclusion rules.</p>

<p>I'm fairly new to SQL in general, so it could just be a badly written query, but it seems to be providing the expected results. It's just so unbearably slow.</p>

<pre><code>WITH RulesWith
AS (
    SELECT *
    FROM Rules
    WHERE
        productName = 'Dlp'
        AND (
            _dateEffective IS NULL
            OR _dateEffective &lt;= '20160906'
            )
        AND (
            _dateExpired IS NULL
            OR _dateExpired &gt;= '20160906'
            )
    )
SELECT C.*
FROM Computers AS C
WHERE (
        (
            C._dateEffective IS NULL
            OR C._dateEffective &lt;= '20160906'
            )
        AND (
            C._dateExpired IS NULL
            OR C._dateExpired &gt;= '20160906'
            )
        )
    AND (
        (
            C.site_name IN (
                SELECT value
                FROM RulesWith
                WHERE propertyName = 'Site'
                    AND typeName = 'Include'
                )
            OR C.computer_fqdn IN (
                SELECT value
                FROM RulesWith
                WHERE propertyName = 'Computer'
                    AND typeName = 'Include'
                )
            OR C.computer_ou IN (
                SELECT value
                FROM RulesWith
                WHERE propertyName = 'ComputerOU'
                    AND typeName = 'Include'
                )
            OR C.user_ou IN (
                SELECT value
                FROM RulesWith
                WHERE propertyName = 'UserOU'
                    AND typeName = 'Include'
                )
            )
        OR NOT (
            C.site_name IN (
                SELECT value
                FROM RulesWith
                WHERE propertyName = 'Site'
                    AND typeName = 'Exclude'
                )
            OR C.computer_fqdn IN (
                SELECT value
                FROM RulesWith
                WHERE productName = 'MyProduct'
                    AND propertyName = 'Computer'
                    AND typeName = 'Exclude'
                )
            OR C.computer_ou IN (
                SELECT value
                FROM RulesWith
                WHERE propertyName = 'ComputerOU'
                    AND typeName = 'Exclude'
                )
            OR C.user_ou IN (
                SELECT value
                FROM RulesWith
                WHERE propertyName = 'UserOU'
                    AND typeName = 'Exclude'
                )
            )
        )
</code></pre>

<p><em>Edit:</em> Removing a lot of the UPPER() functions dramatically reduced the query time to about 5 minutes. I'd still like to clean it up further, but that's a huge difference right away.</p>