What performs better: Bunch of UNION queries vs separate queries?

I'm building a custom car parts marketplace website that uses a set of rules (7 to be more precise) to locate parts for the user when they press the buy button. Some of these rules are something like:

• Look for first seller on the same state, city and district as the consumer (only one seller per district is allowed)
• If there's no seller in the district or the seller doesn't have the part, get a seller of the same group as the one above that is in the same city
• If the above seller doesn't have the part, get any seller that is in the same city
• and so on...

I've worked out a series of queries and got about 300 lines of SQL (when formatted for easy reading) in a giant union of each rule wrapped with a TOP to bring only the first result found.

I'm not sure if this is the best thing to do or is it better to make 7 conditional round trips in sequence checking if the later one has the result before calling the next one.

I estimate that the majority of users will fall through rule 4, thats 4 round trips to SQL Server, and the result of the giant union query returns in less than a second in a less than 100,000 records table.

Problem is, if this site begins to grow in terms of available parts and users, would it be better to leave SQL Server handle the work with the giant query that returns only what I need in 1 trip or is it better to call separate queries?

We are using classic ASP for this job (Yeah! Old school, you know?) if this would be relevant in any case.

Let me know if you need to take a look at the query itself.

Edit:

Here is the query I'm using now:

SELECT TOP 1 regras.ids, regras.idCadastro, regras.regra, e.descricao AS nomeEstado, cd.descricao AS nomeCidade, b.nome AS nomeBairro
FROM ( 
    SELECT dbo.group_concat(idPecaItem) AS ids, idCadastro, '1' AS regra 
    FROM ( 
        SELECT TOP 2 idPecaItem, idCadastro 
        FROM pecasItens 
        WHERE COALESCE(pecasItens.idCadastro, 0) = (CASE WHEN EXISTS( 
            SELECT TOP 1 COALESCE(pi.idCadastro, 0) 
            FROM pecasItens pi  
            LEFT JOIN cadastros c ON pi.idCadastro = c.idCadastro 
            WHERE (pi.idCadastro IS NULL OR c.tipoParceiro = 'c') AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
            GROUP BY pi.idCadastro 
            HAVING COUNT(pi.idPecaItem) >= 2
        ) THEN ( 
            SELECT TOP 1 COALESCE(pi.idCadastro, 0) 
            FROM pecasItens pi  
            LEFT JOIN cadastros c ON pi.idCadastro = c.idCadastro 
            WHERE (pi.idCadastro IS NULL OR c.tipoParceiro = 'c') AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
            GROUP BY pi.idCadastro 
            HAVING COUNT(pi.idPecaItem) >= 2
        ) ELSE NULL END) 
         AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
    ) regra1 
    GROUP BY idCadastro

    UNION  

    SELECT dbo.group_concat(idPecaItem) AS ids, idCadastro, '2' AS regra 
    FROM ( 
        SELECT TOP 2 idPecaItem, idCadastro 
        FROM pecasItens 
        WHERE COALESCE(pecasItens.idCadastro, 0) = (CASE WHEN EXISTS( 
            SELECT TOP 1 COALESCE(pi.idCadastro, 0) 
            FROM pecasItens pi  
            LEFT JOIN cadastros c ON pi.idCadastro = c.idCadastro AND c.tipoParceiro = 'r' AND c.idCadastroGrupo = 1
            WHERE pi.idCadastro IS NOT NULL  AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
            GROUP BY pi.idCadastro 
            HAVING COUNT(pi.idPecaItem) >= 2
        ) THEN ( 
            SELECT TOP 1 COALESCE(pi.idCadastro, 0) 
            FROM pecasItens pi  
            LEFT JOIN cadastros c ON pi.idCadastro = c.idCadastro AND c.tipoParceiro = 'r' AND c.idCadastroGrupo = 1
            WHERE pi.idCadastro IS NOT NULL  AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
            GROUP BY pi.idCadastro 
            HAVING COUNT(pi.idPecaItem) >= 2
        ) ELSE NULL END) 
         AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
    ) regra2 
    GROUP BY idCadastro 
    
    UNION 
    
    SELECT dbo.group_concat(idPecaItem) AS ids, idCadastro, '3' AS regra 
    FROM ( 
        SELECT TOP 2 idPecaItem, idCadastro 
        FROM pecasItens 
        WHERE COALESCE(pecasItens.idCadastro, 0) = (CASE WHEN EXISTS( 
            SELECT TOP 1 COALESCE(pi.idCadastro, 0) 
            FROM pecasItens pi  
            LEFT JOIN cadastros c ON pi.idCadastro = c.idCadastro AND c.tipoParceiro = 'r' AND c.idCadastroGrupo = 1
            INNER JOIN cadastrosCidades cc ON c.idCadastro = cc.idCadastro AND cc.idCidade = 4850
            WHERE pi.idCadastro IS NOT NULL  AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
            GROUP BY pi.idCadastro 
            HAVING COUNT(pi.idPecaItem) >= 2
        ) THEN ( 
            SELECT TOP 1 COALESCE(pi.idCadastro, 0) 
            FROM pecasItens pi  
            LEFT JOIN cadastros c ON pi.idCadastro = c.idCadastro AND c.tipoParceiro = 'r' AND c.idCadastroGrupo = 1
            INNER JOIN cadastrosCidades cc ON c.idCadastro = cc.idCadastro AND cc.idCidade = 4850
            WHERE pi.idCadastro IS NOT NULL  AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
            GROUP BY pi.idCadastro 
            HAVING COUNT(pi.idPecaItem) >= 2
        ) ELSE NULL END) 
         AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
    ) regra3 
    GROUP BY idCadastro 
    UNION  
    
    SELECT dbo.group_concat(idPecaItem) AS ids, idCadastro, '4' AS regra 
    FROM ( 
        SELECT TOP 2 idPecaItem, idCadastro 
        FROM pecasItens 
        WHERE COALESCE(pecasItens.idCadastro, 0) = (CASE WHEN EXISTS( 
            SELECT TOP 1 COALESCE(pi.idCadastro, 0) 
            FROM pecasItens pi  
            INNER JOIN cadastros c ON pi.idCadastro = c.idCadastro AND c.tipoParceiro = 'r' 
            INNER JOIN cadastrosGrupos cg ON c.idCadastroGrupo = cg.idCadastroGrupo AND cg.idMarca = 2
            INNER JOIN cadastrosCidades cc ON c.idCadastro = cc.idCadastro AND cc.idCidade = 4850
            WHERE pi.idCadastro IS NOT NULL  AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
            GROUP BY pi.idCadastro 
            HAVING COUNT(pi.idPecaItem) >= 2
        ) THEN ( 
            SELECT TOP 1 COALESCE(pi.idCadastro, 0) 
            FROM pecasItens pi  
            INNER JOIN cadastros c ON pi.idCadastro = c.idCadastro AND c.tipoParceiro = 'r' 
            INNER JOIN cadastrosGrupos cg ON c.idCadastroGrupo = cg.idCadastroGrupo AND cg.idMarca = 2
            INNER JOIN cadastrosCidades cc ON c.idCadastro = cc.idCadastro AND cc.idCidade = 4850
            WHERE pi.idCadastro IS NOT NULL  AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
            GROUP BY pi.idCadastro 
            HAVING COUNT(pi.idPecaItem) >= 2
        ) ELSE NULL END) 
         AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
    ) regra4 
    GROUP BY idCadastro 
    
    UNION  
    
    SELECT dbo.group_concat(idPecaItem) AS ids, idCadastro, '5' AS regra 
    FROM ( 
        SELECT TOP 2 idPecaItem, idCadastro 
        FROM pecasItens 
        WHERE COALESCE(pecasItens.idCadastro, 0) = (CASE WHEN EXISTS( 
            SELECT TOP 1 COALESCE(pi.idCadastro, 0) 
            FROM pecasItens pi 
            INNER JOIN cadastrosGrupos cg ON pi.idCadastro = cg.idCadastroMontadora AND cg.idCadastroGrupo = 1
            WHERE pi.idCadastro IS NOT NULL  AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
            GROUP BY pi.idCadastro 
            HAVING COUNT(pi.idPecaItem) >= 2
        ) THEN ( 
            SELECT TOP 1 COALESCE(pi.idCadastro, 0) 
            FROM pecasItens pi 
            INNER JOIN cadastrosGrupos cg ON pi.idCadastro = cg.idCadastroMontadora AND cg.idCadastroGrupo = 1
            WHERE pi.idCadastro IS NOT NULL  AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
            GROUP BY pi.idCadastro 
            HAVING COUNT(pi.idPecaItem) >= 2
        ) ELSE NULL END) 
         AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
    ) regra5 
    GROUP BY idCadastro 
    
    UNION  
    
    SELECT dbo.group_concat(idPecaItem) AS ids, idCadastro, '6' AS regra 
    FROM ( 
        SELECT TOP 2 idPecaItem, idCadastro 
        FROM pecasItens 
        WHERE COALESCE(pecasItens.idCadastro, 0) = (CASE WHEN EXISTS( 
            SELECT TOP 1 COALESCE(pi.idCadastro, 0) 
            FROM pecasItens pi  
            LEFT JOIN cadastros c ON pi.idCadastro = c.idCadastro AND c.tipoParceiro = 'r' AND c.idEstado = 25 AND c.atendeEstadoTodo = 1 
            WHERE pi.idCadastro IS NOT NULL  AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
            GROUP BY pi.idCadastro 
            HAVING COUNT(pi.idPecaItem) >= 2
        ) THEN ( 
            SELECT TOP 1 COALESCE(pi.idCadastro, 0) 
            FROM pecasItens pi  
            LEFT JOIN cadastros c ON pi.idCadastro = c.idCadastro AND c.tipoParceiro = 'r' AND c.idEstado = 25 AND c.atendeEstadoTodo = 1 
            WHERE pi.idCadastro IS NOT NULL  AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
            GROUP BY pi.idCadastro 
            HAVING COUNT(pi.idPecaItem) >= 2
        ) ELSE NULL END) 
         AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
    ) regra6 
    GROUP BY idCadastro 
    
    UNION  
    
    SELECT dbo.group_concat(idPecaItem) AS ids, idCadastro, '7' AS regra 
    FROM ( 
        SELECT TOP 2 idPecaItem, idCadastro 
        FROM pecasItens 
        WHERE COALESCE(pecasItens.idCadastro, 0) = (CASE WHEN EXISTS( 
            SELECT TOP 1 COALESCE(pi.idCadastro, 0) 
            FROM pecasItens pi  
            LEFT JOIN cadastros c ON pi.idCadastro = c.idCadastro AND c.tipoParceiro = 'r' AND c.atendeBrasilTodo = 1 
            WHERE pi.idCadastro IS NOT NULL  AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
            GROUP BY pi.idCadastro 
            HAVING COUNT(pi.idPecaItem) >= 2
        ) THEN ( 
            SELECT TOP 1 COALESCE(pi.idCadastro, 0) 
            FROM pecasItens pi  
            LEFT JOIN cadastros c ON pi.idCadastro = c.idCadastro AND c.tipoParceiro = 'r' AND c.atendeBrasilTodo = 1 
            WHERE pi.idCadastro IS NOT NULL  AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
            GROUP BY pi.idCadastro 
            HAVING COUNT(pi.idPecaItem) >= 2
        ) ELSE NULL END) 
         AND idPeca = 31740 AND idPedidoItem IS NULL AND dataEntrada <= '2014-3-13' AND devolucaoSolicitada IS NULL
    ) regra7 
    GROUP BY idCadastro 
) regras 
LEFT JOIN cadastros c ON regras.idCadastro = c.idCadastro 
LEFT JOIN listaEstados e ON c.idEstado = e.idEstado 
LEFT JOIN listaCidades cd ON c.idCidade = cd.idCidade 
LEFT JOIN listaBairros b ON c.idBairro = b.idBairro 
ORDER BY regra ASC

Note: dbo.group_concat() is a custom aggregate from this guys http://groupconcat.codeplex.com/

In nearly all instances the RDBMS is in a much better position to get you the first row.

If your query looks like this,

SELECT TOP 1 *
FROM (
    SELECT ... FROM ... -- Query #1
UNION ALL
    SELECT ... FROM ... -- Query #2
UNION ALL
    ...
UNION ALL
    SELECT ... FROM ... -- Query #7
)

the optimizer is smart enough to stop the query at whatever is the rule that produces the result that you need, be it rule one, rule four, or rule seven. On top of that, the optimizer can run your queries in parallel. Because it sits much closer to your data, it's hard to imagine that you could "outsmart" it by moving part of the query logic away from your RDBMS.