July 26, 2006 at 5:14 am
Hi All
I currently have 2 databases, which run on my SQL box. Its a SQL 2k Standard edition. I find that i need to create views in order to isolate tables from users etc.
But I realised that i cannot use indexed views on standard edition, besides I need to create views for over one table, so the need to use unions etc.
I have a report that runs, in blue but it takes forever to run. What can I do here, what i tried to do was to create a temp table, index it then run the query in blue from it. But that seems to be taking up scarce space and not a faser option either. Profile is the database, Fusion is where the tables are, they are both on thesame box. one table is abound 6GB in size with approx 30m rows on average.
Use Profile
SELECT [Switch] = SelectionView.[Switch] , [Date] = convert(datetime, SelectionView.[Connect_Date],103) , [Duration(mins)] = SUM(convert(numeric(19,4), SelectionView.[Duration])/60) , [Answered] = SUM(convert(int, isnull(SelectionView.[Answer],0))) , [Total Calls] = (count(SelectionView.[CDR_Fingerprint]))
FROM Database_View SelectionView (NOLOCK)
GROUP BY convert(datetime, SelectionView.[Connect_Date],103) , SelectionView.[Switch]
DDL for Database_View SelectionView
Text
CREATE VIEW dbo. Database_View SelectionView as select * from
FUSION..DailyRecordFile_20060706 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060707 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060709 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060712 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060716 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060718 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060720 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060724 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060725 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060704 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060713 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060717 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060702 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060723 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060701 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060705 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060708 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060710 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060711 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060714 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060715 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060719 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060721 (NOLOCK)
UNION ALL select * from FUSION..DailyRecordFile_20060722 (NOLOCK)
July 26, 2006 at 8:12 am
It looks from your post as if new tables are created every day - possibly with identical formats, and presumably storing data applicable to that day?
It's probably a major change for you, but can you consider just amalgamating all of this data into one table and adding a 'date created' (or whatever) field.
If you get your data into this format, your reports will run a lot faster.
Alternatively, perhaps you could create a DTS job that puts the data into a table in this format (leaving the existing data as is) and report off that?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 26, 2006 at 8:49 am
Thanks for your earlier post.
Can someone confirm the following,
Thanks
July 27, 2006 at 11:02 am
Actually, you can create and use indexed views is SQL Server 2000 standard edition. However, the query optimizer must be told to use view index by using WITH (NOEXPAND) in your SELECT statement. Look at the very simple example below.
Unfortunately, you can't create an index on a view if the view contains a UNION.
You mentioned that your tables are 6 GB / 30 million rows each, and you are trying to UNION 24 tables together. My math says that's a result set of 144 GB and 720 million rows. I hope you've got powerful hardware!
As the others mentioned, I would use one table from the beginning, if possible, so you don't have to combine the data during the query.
DROP VIEW vViewA
DROP TABLE tblA
GO
CREATE TABLE tblA
(
tid int
, tdata varchar(20)
)
GO
CREATE VIEW vViewA WITH SCHEMABINDING
AS
SELECT A.tid, A.tdata FROM dbo.tblA A
GO
CREATE UNIQUE CLUSTERED INDEX ix_vView
ON vViewA (tid)
GO
SET NOCOUNT ON
INSERT tblA (tid, tdata) VALUES (1, 'AAA')
INSERT tblA (tid, tdata) VALUES (2, 'BBB')
INSERT tblA (tid, tdata) VALUES (3, 'CCC')
INSERT tblA (tid, tdata) VALUES (4, 'DDD')
INSERT tblA (tid, tdata) VALUES (5, 'EEE')
INSERT tblA (tid, tdata) VALUES (6, 'FFF')
INSERT tblA (tid, tdata) VALUES (7, 'GGG')
INSERT tblA (tid, tdata) VALUES (8, 'HHH')
SET NOCOUNT OFF
SELECT * FROM vViewA
SELECT * FROM vViewA WITH (NOEXPAND)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply