Partitioned view, GROUP BY

Question

Partitioned view, GROUP BY

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

xgoulay Valued Member Points: 73 More actions · Answer 1

Phillip, what you proposed above works exactly the same way as my previous function, the performance is very poor. But if I hardcode the date into the SQL statement the performance is far better as accessing only one table:

DECLARE @COBDate SMALLDATETIME

DECLARE @sql VARCHAR(8000)

SELECT @COBDate = '20080229'

SELECT @sql = 'SELECT COBDate,Account,SUM(Amount) AS Amount FROM uv_rules WHERE COBDate = ''' + CONVERT(varchar(255),@COBDate,112) + ''' GROUP BY COBDate,Account'

EXEC(@SQL)

But I would like to avoid using sp_executesql as this is hard to maintain and debug (also I can't use table user defined functions), I would like to add some joined tables to the result and the query will get far bigger. I am just surprised that we can't find a way to create an object (function or proc) which will give a dynamic access to any date root table.

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 2

Hi xgoulay,

Umm..they execution plan is exactly the same? Do you have an index on the CODOB column on each table?

Thanks,

Phillip Cox

MCITP - DBAdmin

xgoulay Valued Member Points: 73 More actions · Answer 3

No index on COBDate, find below the generic schema of one ut_rules_yyyy_mm_dd table:

CREATE TABLE [dbo].[ut_rules_2008_02_28](

[COBDate] [smalldatetime] NOT NULL,

[RuleID] [int] NOT NULL,

[IrecID] [int] NOT NULL,

[NB] [bigint] NULL,

[Account] [varchar](255) COLLATE Latin1_General_CI_AS NULL,

[Portfolio] [varchar](255) COLLATE Latin1_General_CI_AS NULL,

[Counterparty] [varchar](255) COLLATE Latin1_General_CI_AS NULL,

[Amount] [decimal](38, 2) NULL

)

ALTER TABLE [dbo].[ut_rules_2008_02_28] WITH CHECK ADD CONSTRAINT [CK_ut_rules_2008_02_28_COBDate] CHECK (([COBDate]='20080228'))

CREATE INDEX NCI_ut_rules_2008_02_28_RuleID ON ut_rules_2008_02_28([RuleID]) WITH FILLFACTOR=100,PAD_INDEX

CREATE INDEX NCI_ut_rules_2008_02_28_Counterparty ON ut_rules_2008_02_28([Counterparty]) WITH FILLFACTOR=100,PAD_INDEX

CREATE INDEX NCI_ut_rules_2008_02_28_Portfolio ON ut_rules_2008_02_28([Portfolio]) WITH FILLFACTOR=100,PAD_INDEX

CREATE INDEX NCI_ut_rules_2008_02_28_NB ON ut_rules_2008_02_28([NB]) WITH FILLFACTOR=100,PAD_INDEX

CREATE CLUSTERED INDEX CI_ut_rules_2008_02_28 ON ut_rules_2008_02_28([Account]) WITH FILLFACTOR=100,PAD_INDEX

I know I got lot of indexes on my table but I need all of them for various queries.

Here I illustrate my uv_rules:

CREATE VIEW [dbo].[uv_rules] WITH SCHEMABINDING AS

SELECT [COBDate],[RuleID],[IrecID],[NB],[Account],[Portfolio],[Counterparty],[Amount] FROM dbo.ut_rules_2008_02_05 UNION ALL

SELECT [COBDate],[RuleID],[IrecID],[NB],[Account],[Portfolio],[Counterparty],[Amount] FROM dbo.ut_rules_2008_02_06 UNION ALL

SELECT [COBDate],[RuleID],[IrecID],[NB],[Account],[Portfolio],[Counterparty],[Amount] FROM dbo.ut_rules_2008_02_08 UNION ALL

SELECT [COBDate],[RuleID],[IrecID],[NB],[Account],[Portfolio],[Counterparty],[Amount] FROM dbo.ut_rules_2008_02_14 UNION ALL

SELECT [COBDate],[RuleID],[IrecID],[NB],[Account],[Portfolio],[Counterparty],[Amount] FROM dbo.ut_rules_2008_02_18 UNION ALL

SELECT [COBDate],[RuleID],[IrecID],[NB],[Account],[Portfolio],[Counterparty],[Amount] FROM dbo.ut_rules_2008_02_19 UNION ALL

SELECT [COBDate],[RuleID],[IrecID],[NB],[Account],[Portfolio],[Counterparty],[Amount] FROM dbo.ut_rules_2008_02_25 UNION ALL

SELECT [COBDate],[RuleID],[IrecID],[NB],[Account],[Portfolio],[Counterparty],[Amount] FROM dbo.ut_rules_2008_02_26 UNION ALL

SELECT [COBDate],[RuleID],[IrecID],[NB],[Account],[Portfolio],[Counterparty],[Amount] FROM dbo.ut_rules_2008_02_27 UNION ALL

SELECT [COBDate],[RuleID],[IrecID],[NB],[Account],[Portfolio],[Counterparty],[Amount] FROM dbo.ut_rules_2008_02_28 UNION ALL

SELECT [COBDate],[RuleID],[IrecID],[NB],[Account],[Portfolio],[Counterparty],[Amount] FROM dbo.ut_rules_2008_03_03 UNION ALL

SELECT [COBDate],[RuleID],[IrecID],[NB],[Account],[Portfolio],[Counterparty],[Amount] FROM dbo.ut_rules_2008_03_05

The concept is very simple: every day I create a new table with only the data for the days (ut_rules_yyyy_mm_dd), this tables contains millions of entries. After I need to find a way to reference this new table through the partitioned view as I process several other views/functions/procedures for this specfic date. Currently I regenerate the uv_rules to include the new date and I use this object in all my other queries.

xgoulay Valued Member Points: 73 More actions · Answer 4

Hi,

I have tried many ways to fix that but couldn't find a solution yet (I am not very keen in using dynamic SQL).

Basically when I run the following query:

SELECT COBDate,Account,SUM(Amount) FROM uv_rules WHERE COBDate = '20080305' GROUP BY COBDate,Account

it accesses only one sub table as expected

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

(15236 row(s) affected)

Table 'ut_rules_2008_03_05'. Scan count 5, logical reads 58477, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 13437 ms, elapsed time = 3711 ms.

whereas when I use a variable for the date,

DECLARE @COBDate SMALLDATETIME

SELECT @COBDate = '20080305'

SELECT COBDate,Account,SUM(Amount) FROM uv_rules WHERE COBDate = @COBDate GROUP BY COBDate,Account

It takes much more time to process

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

(15236 row(s) affected)

Table 'ut_rules_2008_02_29'. Scan count 5, logical reads 57938, physical reads 3, read-ahead reads 54784, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ut_rules_2008_03_03'. Scan count 5, logical reads 58114, physical reads 3, read-ahead reads 57388, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ut_rules_2008_03_04'. Scan count 5, logical reads 58667, physical reads 3, read-ahead reads 57565, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ut_rules_2008_03_05'. Scan count 5, logical reads 58457, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 16894 ms, elapsed time = 19074 ms.

xgoulay Valued Member Points: 73 More actions · Answer 5

A very strange one: if I limit my query to a specific Account my partitioned view is used correctly (access only one table):

DECLARE @COBDate SMALLDATETIME

SELECT @COBDate = '20080305';

SELECT Account,SUM(Amount) FROM uv_rules WHERE COBDate = '20080305' AND Account = 'Account12233' GROUP BY Account

gives

Table 'ut_rules_2008_03_05'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

where as if I don't add the Account criteria

DECLARE @COBDate SMALLDATETIME

SELECT @COBDate = '20080305';

SELECT Account,SUM(Amount) FROM uv_rules WHERE COBDate = @COBDate GROUP BY Account

gives

Table 'ut_rules_2008_02_28'. Scan count 5, logical reads 57704, physical reads 20, read-ahead reads 57304, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ut_rules_2008_02_29'. Scan count 5, logical reads 57803, physical reads 7, read-ahead reads 57461, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ut_rules_2008_03_03'. Scan count 5, logical reads 57949, physical reads 6, read-ahead reads 57648, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ut_rules_2008_03_04'. Scan count 5, logical reads 58092, physical reads 6, read-ahead reads 57786, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ut_rules_2008_03_05'. Scan count 5, logical reads 58447, physical reads 3, read-ahead reads 57985, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 6

Hey,

Have you tried adding the covering index on:

COBDATE, ACCOUNT?

Thanks,

Phillip Cox

MCITP - DBAdmin

xgoulay Valued Member Points: 73 More actions · Answer 7

Already tried the covering index, this had no impact on the performance.

Is there a way to tell the sql engine to skip execution plan?

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 8

Hi,

No, as it needs to figure best way to get to the data? You can "guide" using hints, but other than that, you cannot tell it not to create an execution plan.

Unless someone knows of a different approach, dynamic SQL may be the only way to solve your issue.

Thanks,

Phillip Cox

MCITP - DBAdmin

xgoulay Valued Member Points: 73 More actions · Answer 9

Actually my question was: Is it possible to create a function and stored procedure with a date variable and then, instead of the sql engine directly creating the execute plan of the stored procedure and function, the sql engine creates the static SQL statement, eveything being hardcoding, and generates the execution plan based on it.

Also is there a way to trick the system in the way that it thinks the query is going to retrieve limited entries?

xgoulay Valued Member Points: 73 More actions · Answer 10

Hi,

I am running that on SQL server 2005 SP2. Actually it seems to be due to Parallelism, the sql engine shares the work load on the for processors which in this case causes the poor performance. When using the OPTION keyword with MAXDOP = 1, the sql engine direclty access the right table, which is exactly what I want to do. The new issue is I can't set this option on in a user defined function, so the only possibility seems to set the Max Degree of Parallelism to 1 for the sql server. Is there a way to set this option at database level or object level, I don't want to impact other database on this server.

Thanks,

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 11

hey,

Great result!

This is a server level option, thus you cannot set at db level.

Thanks,

Phillip Cox

MCITP - DBAdmin