Counting and string compare (slow query)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Counting and string compare (slow query)

Author

Topic

skataben
Starting Member

4 Posts

Posted - 2011-05-19 : 11:55:45

My T-SQL database has a status column. I need to count the number of each status. Here are the possible values:

Passed *some code*
Failed *some code*
No Run
NA
Retest
Skipped

My query works but is very slow (18 seconds every time):

SELECT DISTINCT LEFT(statusColumn, 7) AS Status,
(SELECT COUNT(statusColumn)
FROM req_info
WHERE LEFT(statusColumn, 7) = LEFT(outerTable.statusColumn, 7)
) AS Count
FROM req_info AS outerTable
GROUP BY statusColumn

The results look similar to this:

Status Count
Failed 4964
No Run 383367
Passed 18074

The query is slowed down by the fact that the values 'Passed' and 'Failed' may have strings after them requiring me to do a LEFT string compare. I'm hoping some of the gurus here can help make this relatively simple query faster.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-19 : 11:59:29

isn't it just

select LEFT(statusColumn, 7) AS Status, count(*)
FROM req_info
group by LEFT(statusColumn, 7)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

skataben
Starting Member

4 Posts

Posted - 2011-05-19 : 12:10:56

Ok now I'm mad haha.

Seriously though, your query looks exactly like the query I came up with yesterday, but I was getting an error like 'not in aggregate function' or something (I don't remember exactly).

Who cares, it works now! Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources