December 6, 2006 at 9:19 am
I have created the following view:
Create VIEW dbo.TD_EMP_Assignment
AS
SELECT [EMP_GRP_ASS_SK],
[EMP_SK],
[EMP_GRP_NODE_SK],
[START_NOM_DATE],
[STOP_NOM_DATE],
[LDR]
FROM dbo.EMP_GRP_ASS
UNION ALL
SELECT [EMP_GRP_ASS_SK],
[EMP_SK],
[EMP_GRP_NODE_SK],
[START_NOM_DATE],
[STOP_NOM_DATE],
[LDR]
FROM dbo.EMP_GRP_ASS_Local
GO
Now I would like to improve performance on this View. I know that I can not create any indexes because this uses the Union All command.
If I index the "parent" tables, will those indexes persist to the view? (I have created identical indexes on both tables.)
Is there any other way to improve the performance of the view?
Thankyou,
Bryan
December 6, 2006 at 9:25 am
Is this a true partitioned view, in which the underlying tables have a check constraint on the partitioning column or columns ?
December 6, 2006 at 9:37 am
At the risk of sounding like a newbie, I am not sure.
The two tables are identical, except for the following:
The EMP_GRP_ASS has a unique key EMP_GRP_ASS_SK, which is generated on a foreign database. This key is a Unique Key in their entire database. All of their keys are negative. When I import this record, I have the column set as the Primary Key, but it is not an Identity field.
The EMP_GRP_ASS_Local has the same EMP_GRP_ASS_SK, which has the Identity set on it. All of the local keys are positive.
I did this so that I could add local changes to the table without having to worry about "interfering" with the imported table. I use the above view to "combine" the two tables.
I am now hitting the view rather heavily, and it drags down my performance. (I have 1 query that when I hit the view, takes 13:43 to run, but when I just hit the EMP_GRP_ASS table directly, runs in 1:10.)
I have not created any constraints/triggers on either table.
December 6, 2006 at 4:18 pm
The best way to improve the performance is to analyze your query on the view. Try to see its query execution plan and find out what's causing the slow performance and start from there.
Yes, the view will benifit from indexing the underlying tables. But, of course, this will be dependent on your query. Your query may not necessarily use the indexes you've created.
Please post the query you use that run for 13:43. Also include the DDLs of the tables.
April 5, 2010 at 1:15 pm
I'm attempting to implement my first partitioned view. According to BOL I think I've got all of the necessary components and it seems to complete close to the normal timeframe it take to run as a single table.
I looked at the execution plan and it appears that the member tables still account for 1% of the cost (24 member tables, monthly sales data for 2 years). I thought the constraints (date range for the month of the table) would eliminate the need for these extra tables to be included in the plan.
Another strange thing is that I had to create statistics 3 times per table. The execution plan gave a warning that stats on 3 fields were not available. I created stats on those 3 fields, it then gave warning for 2 of the 3 fields I just created. So I created a 2nd set of stats. Then it gave a warning for 1 field. I create that and then there were no more warnings.
Each member table:
1) has the same layout as the others
2) has a primary key (mine had to be composite - saledt, acct_id, addr_id, order_src_id)
3) has a constraint on the saledt field where the value must be between beginning and end of the member month.
4) statistics 3 sets of statistics (acct_id, addr_id, order_src_id and addr_id, addr_id and acct_id)
Any suggestions?
April 5, 2010 at 5:42 pm
Warning about missing statistics usualy means that your WHERE clause uses non-indexed columns.
Please read the post from Ronald San Juan above and answer the questions in there.
_____________
Code for TallyGenerator
April 6, 2010 at 7:44 am
Thanks for the tip about the stats. I will analyze that part of the query seperately.
I've included a simple query that produces different execution plans. I'm wondering if it's just the nature of the best in SQL 2000 that when you use variables in the predicate that the showplan shows all possible paths.
DECLARE @ST_TY INT
DECLARE @EN_TY INT
SET @ST_TY = 2009221
SET @EN_TY = 2009237
Query 1: hard-coded dates
select sum(CASE WHEN BUSINESS_DAY_DT BETWEEN 2009221 AND 2009237 THEN SALES ELSE 0 END) from sales_dly where business_day_dt between 2009221 and 2009237
Query 2: variable dates
select sum(CASE WHEN BUSINESS_DAY_DT BETWEEN @ST_TY AND @EN_TY THEN SALES ELSE 0 END) from sales_dly where business_day_dt between @ST_TY and @EN_TY
Here's the SHOWPLAN for the first:
|--Compute Scalar(DEFINE:([Expr1242]=If ([Expr1243]=0) then NULL else [Expr1244]))
|--Stream Aggregate(DEFINE:([Expr1243]=COUNT_BIG(If ([sales_dly_2009_06].[BUSINESS_DAY_DT]>=2009221 AND [sales_dly_2009_06].[BUSINESS_DAY_DT]<=2009237) then [sales_dly_2009_06].[SALES] else 0.00000), [Expr1244]=SUM(If ([sales_dly_2009_06].[BUSINESS_DAY_DT]>=2009221 AND [sales_dly_2009_06].[BUSINESS_DAY_DT]<=2009237) then [sales_dly_2009_06].[SALES] else 0.00000)))
|--Compute Scalar(DEFINE:([sales_dly_2009_06].[BUSINESS_DAY_DT]=[sales_dly_2009_06].[BUSINESS_DAY_DT], [sales_dly_2009_06].[SALES]=[sales_dly_2009_06].[SALES]))
|--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2009_06].[pk_bddt_said_aid_sid_0609]), SEEK:([sales_dly_2009_06].[BUSINESS_DAY_DT] <= 2009237) ORDERED FORWARD)
2nd Query showplan:
|--Compute Scalar(DEFINE:([Expr1242]=If ([globalagg1244]=0) then NULL else [globalagg1246]))
|--Stream Aggregate(DEFINE:([globalagg1244]=SUM([partialagg1243]), [globalagg1246]=SUM([partialagg1245])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:([partialagg1243]=COUNT_BIG(If ([Union1234]>=[@ST_TY] AND [Union1234]<=[@EN_TY]) then [Union1238] else 0.00000), [partialagg1245]=SUM(If ([Union1234]>=[@ST_TY] AND [Union1234]<=[@EN_TY]) then [Union1238] else 0.00000)))
|--Concatenation
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2010177 AND [@EN_TY]>=2010141)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2010_04].[pk_bddt_said_aid_sid_0410]), SEEK:([sales_dly_2010_04].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2010_04].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2010137 AND [@EN_TY]>=2010091)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2010_03].[pk_bddt_said_aid_sid_0310]), SEEK:([sales_dly_2010_03].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2010_03].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2010087 AND [@EN_TY]>=2010051)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2010_02].[pk_bddt_said_aid_sid_0210]), SEEK:([sales_dly_2010_02].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2010_02].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2010047 AND [@EN_TY]>=2010011)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2010_01].[pk_bddt_said_aid_sid_0110]), SEEK:([sales_dly_2010_01].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2010_01].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2009527 AND [@EN_TY]>=2009481)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2009_12].[pk_bddt_said_aid_sid_1209]), SEEK:([sales_dly_2009_12].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2009_12].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2009477 AND [@EN_TY]>=2009441)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2009_11].[pk_bddt_said_aid_sid_1109]), SEEK:([sales_dly_2009_11].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2009_11].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2009437 AND [@EN_TY]>=2009401)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2009_10].[pk_bddt_said_aid_sid_1009]), SEEK:([sales_dly_2009_10].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2009_10].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2009397 AND [@EN_TY]>=2009351)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2009_09].[pk_bddt_said_aid_sid_0909]), SEEK:([sales_dly_2009_09].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2009_09].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2009347 AND [@EN_TY]>=2009311)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2009_08].[pk_bddt_said_aid_sid_0809]), SEEK:([sales_dly_2009_08].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2009_08].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2009307 AND [@EN_TY]>=2009271)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2009_07].[pk_bddt_said_aid_sid_0709]), SEEK:([sales_dly_2009_07].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2009_07].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2009267 AND [@EN_TY]>=2009221)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2009_06].[pk_bddt_said_aid_sid_0609]), SEEK:([sales_dly_2009_06].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2009_06].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2009217 AND [@EN_TY]>=2009181)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2009_05].[pk_bddt_said_aid_sid_0509]), SEEK:([sales_dly_2009_05].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2009_05].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2009177 AND [@EN_TY]>=2009141)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2009_04].[pk_bddt_said_aid_sid_0409]), SEEK:([sales_dly_2009_04].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2009_04].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2009137 AND [@EN_TY]>=2009091)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2009_03].[pk_bddt_said_aid_sid_0309]), SEEK:([sales_dly_2009_03].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2009_03].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2009087 AND [@EN_TY]>=2009051)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2009_02].[pk_bddt_said_aid_sid_0209]), SEEK:([sales_dly_2009_02].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2009_02].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2009047 AND [@EN_TY]>=2009011)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2009_01].[pk_bddt_said_aid_sid_0109]), SEEK:([sales_dly_2009_01].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2009_01].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2008527 AND [@EN_TY]>=2008481)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2008_12].[pk_bddt_said_aid_sid_1208]), SEEK:([sales_dly_2008_12].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2008_12].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2008477 AND [@EN_TY]>=2008441)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2008_11].[pk_bddt_said_aid_sid_1108]), SEEK:([sales_dly_2008_11].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2008_11].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2008437 AND [@EN_TY]>=2008401)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2008_10].[pk_bddt_said_aid_sid_1008]), SEEK:([sales_dly_2008_10].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2008_10].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2008397 AND [@EN_TY]>=2008351)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2008_09].[pk_bddt_said_aid_sid_0908]), SEEK:([sales_dly_2008_09].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2008_09].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2008347 AND [@EN_TY]>=2008311)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2008_08].[pk_bddt_said_aid_sid_0808]), SEEK:([sales_dly_2008_08].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2008_08].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2008307 AND [@EN_TY]>=2008271)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2008_07].[pk_bddt_said_aid_sid_0708]), SEEK:([sales_dly_2008_07].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2008_07].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2008267 AND [@EN_TY]>=2008221)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2008_06].[pk_bddt_said_aid_sid_0608]), SEEK:([sales_dly_2008_06].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2008_06].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2008217 AND [@EN_TY]>=2008181)))
| |--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2008_05].[pk_bddt_said_aid_sid_0508]), SEEK:([sales_dly_2008_05].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2008_05].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
|--Filter(WHERE:(STARTUP EXPR([@ST_TY]<=2008177 AND [@EN_TY]>=2008141)))
|--Clustered Index Seek(OBJECT:([TAM].[dbo].[sales_dly_2008_04].[pk_bddt_said_aid_sid_0408]), SEEK:([sales_dly_2008_04].[BUSINESS_DAY_DT] >= [@ST_TY] AND [sales_dly_2008_04].[BUSINESS_DAY_DT] <= [@EN_TY]) ORDERED FORWARD)
IO Stats:
Query 1 directly accesses the table I would expect it to:
Table 'sales_dly_2009_06'. Scan count 1, logical reads 4859, physical reads 1, read-ahead reads 4856.
(1 row(s) affected)
Query 2 seem to take into account all member tables, but only actively reads from one:
(5 row(s) affected)
Table 'sales_dly_2008_04'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2008_05'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2008_06'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2008_07'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2008_08'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2008_09'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2008_10'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2008_11'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2008_12'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2009_01'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2009_02'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2009_03'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2009_04'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2009_05'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2009_06'. Scan count 4, logical reads 4914, physical reads 55, read-ahead reads 0.
Table 'sales_dly_2009_07'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2009_08'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2009_09'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2009_10'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2009_11'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2009_12'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2010_01'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2010_02'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2010_03'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'sales_dly_2010_04'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
April 6, 2010 at 6:58 pm
Make business_day_dt (or whatever the names are in background tables) a 1st column of clustered index on every table.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply