SQL: optimization problem, is there a row?

Question

I received a query with five joins on some fairly large tables (the largest table is 10 million records), and I want to know if rows exist. So far, I have been doing this to check if strings exist:

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

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

I have the indexes of the fields I am joining and the fields in the WHERE clause.

Any ideas?

Answer 1

4 options

• Try using COUNT (*) instead of TOP 1 tbl.id

• :

SQL Server 2005? , .

• , 5 .

-- .., ( , )

,

SELECT TOP 1
   tbl.Id --or count(*)
FROM
   grandchildtable tbl
   INNER JOIN
   anothertable ON ... = ...
WHERE
   tbl.xxx = ...

• EXISTS.

5 ,

SELECT TOP 1 --or count(*)
   tbl.Id
FROM
   grandchildtable tbl
WHERE
   tbl.xxx = ...
   AND
   EXISTS (SELECT *
       FROM
           anothertable T2
       WHERE
           tbl.key = T2.key /* AND T2 condition*/)
-- or
SELECT TOP 1 --or count(*)
   tbl.Id
FROM
   mytable tbl
WHERE
   tbl.xxx = ...
   AND
   EXISTS (SELECT *
       FROM
           anothertable T2
       WHERE
           tbl.key = T2.key /* AND T2 condition*/)
   AND
   EXISTS (SELECT *
       FROM
           yetanothertable T3
       WHERE
           tbl.key = T3.key /* AND T3 condition*/)

Answer 2

EXISTS. , , , 1 ..

, IF EXISTS (SELECT * FROM table tbl INNER JOIN table tbl2 .. do your stuff

Answer 3

, ( , ).

MSSQL .

Oracle MySQL EXPLAIN, , .

, 22 - , . , , . , , , , , .

Answer 4

, ; , .

Select top 1 tbl.id
From 
(
Select top 1 * from 
table tbl1
Where Key = Key
) tbl1
inner join ... 

, , , , .

Answer 5

, / . , , . , , , , . , , .

: , . , , ! , .

Answer 6

Use the maximum row table first in each connection, and if more than one condition is used in which then, when the condition condition is important, the condition that gives you the maximum rows is used.

Use filters very carefully to optimize your query.