Os SGBD são muito bonitos e tem um bom desempenho, mas...... isso também depende do programador que criou as querys...
o SGBD não faz milagre... se a sua query for mal feita, o sistema vai continuar lento...
Então, antes de colocar a query no programa, utilize um profiler para verificar o desempenho da sua query, veja se está faltando indexes e etc....
Eu utilizo um padrão para os meus sistemas, querys que demorem mais do que meio segundo para retornar, tem coisa errada...
claro que, não irei colocar em um programa um:
SELECT COUNT(*) FROM TABELA_GIGANTE_COM_BILHOES_DE_REGISTROS
Em um programa...., como em um programa que vai rodar processos comerciais, esse tipo de coisa não existe ( assim eu acho )....
E, se for necessário fazer algo do tipo, utilizo uma outra estrategia... apenas uso a contagem de elementos do índice da tabela, que retorna instantaneamente....
Um exemplo, tanto eu quanto o Sr. José Quintas, possuímos em um banco de dados os dados abertos dos CNPJs... é aproximadamente 40 milhões de registros....
Operar uma tabela desse porte não é algo simples... os índices nas tabelas tem que estar MUITO, mas MUITO BEM FEITOS!!!!, coisas que podem enforcar o SGBD nesses casos é:
SELECT * FROM TABELA_DE_CNPJS WHERE NomeDaEmpresa LIKE '%PLS%'
Se o seus sistema necessitar de algo do tipo, utilize um tipo de índice especifico para busca de texto... no caso do MS-SQL server, esse índice se chama FullText Search.
Query com LIKE são muito perigosas.... é sempre bom tomar muito cuidado com isso, quanto mais profunda for a sua tabela, mais tempo o resultado vai demorar para chegar...
Os indices, tem que ser planejados inclusive para os JOINS!!!!
Então, MUITO cuidado ao desenvolver as querys, para não enforcar o seu sistema em um futuro, quando as tabelas estiverem mais recheadas....