In an database I have a main table include million records, this table include duplicate records
that inserted when special event occurs.
Now I need to have an view from main table by eliminate duplicate records, I create 3 view:
- view1: select all record from main table
- view2: eliminate duplicates record by using group by
- view3: select records from view1 where ID exist in view2 (eliminate duplicates record)
Seems all things is ok, view3 execute time is about 1 minute (not bad), but we have dynamic query
tools desinged for end user, when he/she try to create very simple query from view3 like:
"reg_date between date1 and date2 and num_count > 3",
is took long time and finally get "execution time out expire" ! more than 10 minutes ?
How can write above view better, what is wrong ?
-- view 1: [tbv_view1] select all record from main table
-- view 2:
if exists (select * from sysobjects where id = object_id('tbv_view2') and OBJECTPROPERTY(id, 'IsView') = 1)
DROP VIEW tbv_view2
CREATE VIEW tbv_view2
SELECT TOP 100 PERCENT MAX(ID) AS ID, ID_Number
WHERE [status] <> 1 -- remove some events...
GROUP BY ID_Number
ORDER BY ID