Sql-server – SQL Select taking too much time to execute

querysql serversql-server-2005statistics

It's a simple select from a temporary table, left joining an existing table on its primary key, with two sub selects using top 1 referring the joined table.

In code:

SELECT
    TempTable.Col1,
    TempTable.Col2,
    TempTable.Col3,
    JoinedTable.Col1,
    JoinedTable.Col2,
    (
        SELECT TOP 1
            ThirdTable.Col1 -- Which is ThirdTable's Primary Key
        FROM
            ThirdTable
        WHERE
            ThirdTable.SomeColumn = JoinedTable.SomeColumn
    ) as ThirdTableColumn1,
    (
        SELECT TOP 1
            ThirdTable.Col1 -- Which is also ThirdTable's Primary Key
        FROM
            ThirdTable
        WHERE
            ThirdTable.SomeOtherColumn = JoinedTable.SomeColumn
    ) as ThirdTableColumn2,
FROM
    #TempTable as TempTable
LEFT JOIN
    JoinedTable
ON (TempTable.PKColumn1 = JoinedTable.PKColumn1 AND 
    TempTable.PKColumn2 = JoinedTable.PKColumn2)
WHERE
    JoinedTable.WhereColumn IN  (1, 3)

This is an exact replica of my query.

If I remove the two sub selects, it runs just fine and quickly. With the two sub selects, I get about 100 records per second, which is extremely slow for this query because it should return almost a million records.

I've checked to see if every table has a Primary Key, they all do. They all have Indexes AND statistics for their important columns, like the ones in those WHERE clauses, and the ones in the JOIN clause. The only table with no primary key defined nor index is the temporary table, but it's not the problem either because it's not the one related to the slow sub selects, and as I mentioned, with no sub selects it runs just fine.

Without those TOP 1 it returns more than one result, and raises an error.

Help, anyone?

EDIT:

So the execution plan told me I was missing an Index. I've created it, and recreated some of the other indexes. After a while, the execution plan was using them, and the query now runs fast. The only problem is I'm not succeeding in doing this again on another server, for the same query. So my solution will be to HINT which index SQL Server will use.

Best Answer

I think in a million records query, you have to avoid things like OUTER JOINS. I suggest you use UNION ALL Instead of LEFT JOIN. As long as I think CROSS APPLY is more efficient than sub-query in the select clause I will modify the query written by Conard Frix, which I think is correct.

now: when I started to modify your query I noticed that you have a WHERE clause saying: JoinedTable.WhereColumn IN (1, 3). in this case, if the field is null the condition will become false. then why are you using LEFT JOIN while you are filtering null valued rows? just replace LEFT JOIN With INNER JOIN, I guarantee that it will become faster.

about INDEX:

please note that when you have an index on a table, say 

table1(a int, b nvarchar)

and your index is :

nonclustered index ix1 on table1(a)

and you want to do something like this:

select a,b from table1
where a < 10

in your index you have not included the column b so what happens?

if sql-server uses your index, it will have to search in the index, called "Index Seek" and then refer to main table to get column b, called "Look Up". This procedure might take much longer than scanning the table itself: "Table Scan".

but based on the statistics that sql-server has, in such situations, it might not use your index at all.

so first of all check the Execution Plan to see if the index is used at all.

if yes or no both, alter your index to include all columns that you are selecting. say like:

nonclustered index ix1 on table1(a) include(b)

in this case Look Up will not be needed, and your query will execute so much faster.

Related Question