Member Avatar for Celt.Max

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

First select:

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!

  • 2 Contributors
  • 1 Reply
  • 61 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by dickersonka
Member Avatar for dickersonka

Do you have indexes on any of these columns?

Also, consider revising to not search based upon text strings, you have the id's right there.

Also what are you trying to select, you trying to select count on distinct textcodes?

I don't get what you are asking, if you said the 2nd query isn't what you are wanting. Are you wanting the highest id's of all the distinct textcodes?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.