What the Dell is hacked up with this query?

[Celt.Max]

I have a scary query on MSSQL 2005 here...

Got a wiew that returns 2110 rows... and a table of cca 1 500 000 rows. Now, when I run this query

SELECT TS.col1, TS.col2, count(*) AS Number
FROM view_scary VS
LEFT OUTER JOIN table_scary TS ON VS.IdContact = TS.IdContact
GROUP BY TS.col1, TS.col2

This returns me 210 rows in 2 seconds... great!

But when I run this one with the where clause

SELECT TS.col1, TS.col2, count(*) AS Number
FROM view_scary VS
LEFT OUTER JOIN table_scary TS ON VS.IdContact = TS.IdContact
          WHERE TS.col1 = 5 AND TS.col2 = 'a'
GROUP BY TS.col1, TS.col2

so this is working over one hour yet and still not done. Columns col1 and col2 are indexed.

I also tried to do a subselect like this one

SELECT * FROM (
     SELECT col1, col2, count(*) AS Number
     FROM view_scary VS
     LEFT OUTER JOIN table_scary TS ON VS.IdContact = TS.IdContact
     GROUP BY col1, col2
) SUB
WHERE TS.col1 = 5 AND TS.col2 = 'a'

and this was still working looong minutes, until I stopped the query...

any ideas?

Thank you for them... :)

[darkagn]

Have you tried a HAVING clause instead of a WHERE clause? I don't really understand the difference, but sometimes HAVING works when a WHERE doesn't.
Your query would become:

SELECT TS.col1, TS.col2, count(*) AS Number
      FROM view_scary VS
      LEFT OUTER JOIN table_scary TS ON VS.IdContact = TS.IdContact
      GROUP BY TS.col1, TS.col2
      HAVING TS.col1 = 5 AND TS.col2 = 'a'

[sknake]

>>Have you tried a HAVING clause instead of a WHERE clause? I don't really understand the difference, but sometimes HAVING works when a WHERE doesn't HAVING is used on aggregate comparisons while WHERE is used for row comparisons.

Regarding the original poster's query:

SELECT TS.col1, TS.col2, count(*) AS Number
FROM view_scary VS
LEFT OUTER JOIN table_scary TS ON VS.IdContact = TS.IdContact
          WHERE TS.col1 = 5 AND TS.col2 = 'a'
GROUP BY TS.col1, TS.col2

In that query you're forcing the database to join all records in the table in order to run the WHERE comparison. You should move the WHERE statement to be part of the join operation:

SELECT TS.col1, TS.col2, count(*) AS Number
FROM view_scary VS LEFT OUTER JOIN table_scary TS ON (VS.IdContact = TS.IdContact and TS.col1 = 5 and TS.col2 = 'a')
GROUP BY TS.col1, TS.col2