Hi everybody, I have really damned trouble here...
On MSSQL 2000 I have something about 140 log tables with three columns in each one. "ID" AS INT, "SomeTextCODE" AS NVARCHAR(20) and "SomeTime" AS DATETIME
I made a view (v_SomeView_ALL) in which there are all these tables united.
But some SomeTextCODEs are included more often, so it looks like:
ID SomeTextCODE SomeTime
1 2ACD2 19.05.2008 18:12:12
2 35GQG 19.05.2008 19:20:24
3 2ACD2 19.05.2008 19:35:32
4 829DK 19.05.2008 19:40:01
SELECT COUNT(*) FROM v_SomeView_ALL
Returs the number of 8 965 272 in 12 seconds on my sql server(thats quite ok), but
SELECT COUNT(DISTINCT SomeTextCODE) FROM v_SomeView_ALL
takes 3:36min (4 783 272 rows returned) ... oooouch, but I can survive that time
But I need only last occurence for every code with time, so I tried
SELECT COUNT(*) FROM v_SomeView_ALL SVM WHERE SomeTime IN (SELECT TOP 1 SomeTime FROM v_SomeView_ALL SVS WHERE SVS.SomeTextCODE = SVM.SomeTextCODE ORDER BY SomeTime DESC)
But this took over 1hour and half and still nothing! Please HELP! I need id under 5 minutes! Please! Please! Please! I'm agonized!