SQL: Optimization problem, has rows?

Question

I got a query with five joins on some rather large tables (largest table is 10 mil. records), and I want to know if rows exists. So far I've done this to check if rows exists:

SELECT TOP 1 tbl.Id
FROM table tbl
INNER JOIN ... ON ... = ... (x5)
WHERE tbl.xxx = ...

Using this query, in a stored procedure takes 22 seconds and I would like it to be close to "instant". Is this even possible? What can I do to speed it up?

I got indexes on the fields that I'm joining on and the fields in the WHERE clause.

Any ideas?

Answer 1

switch to EXISTS predicate. In general I have found it to be faster than selecting top 1 etc.

So you could write like this IF EXISTS (SELECT * FROM table tbl INNER JOIN table tbl2 .. do your stuff

Answer 2

Depending on your RDBMS you can check what parts of the query are taking a long time and which indexes are being used (so you can know they're being used properly).

In MSSQL, you can use see a diagram of the execution path of any query you submit.

In Oracle and MySQL you can use the EXPLAIN keyword to get details about how the query is working.

But it might just be that 22 seconds is the best you can do with your query. We can't answer that, only the execution details provided by your RDBMS can. If you tell us which RDBMS you're using we can tell you how to find the information you need to see what the bottleneck is.