September 7, 2016 at 12:24 pm
I have a 2.5 billion record table that i broke out into a table for all of 2013. One table for all of 2014 then a table per monthfrom 1/1/2015' till 12/31/2016. So there are 36 individual tables w/ a constraint on the "DateOfData" coulmn which is how the data is separated.The primay key is on 5 columns and dateofdata is the third column in the key. I'm using sql server 2014 standard. I created a partitioned view on top of all the tables. I have a query that appears to be trying to read from all 26 tables even though i'm only selecting a handfull of dates from the past week
notice the below
--print 'start - '+cast(getdate() as varchar)
IF Object_id('tempdb..#dates') IS NOT NULL
DROP TABLE #dates
DECLARE @entdt DATETIME;
DECLARE @startdate DATETIME;
SET @startdate = Dateadd(dd, -8, Getdate())
SET @entdt = Getdate()
DECLARE @dcnt INT;
WITH DateList
AS (SELECT Cast(@startdate AS DATETIME) datevalue
UNION ALL
SELECT datevalue + 1
FROM DateList
WHERE datevalue + 1 < CONVERT(VARCHAR(15), @entdt, 101))
SELECT *
INTO #dates
FROM DateList
OPTION (maxrecursion 0);
SELECT *
FROM #dates
PRINT 'del - ' + Cast(Getdate() AS VARCHAR)
SET statistics io ON
SELECT [clientid],
[dateofdata],
[dimension line_item_id],
[dimension creative_id],
Sum([impressions]) imp,
Sum([clicks]) clk
from [dbo].vw_report_history rh(nolock)
where dateofdata in ('9/1/2016','9/6/2016')
GROUP BY [clientid],
[dimension line_item_id],
[dateofdata],
[dimension creative_id]
-----io stats--------------
Table 'testtable_201509'. Scan count 1, logical reads 61, 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.
Table '#dates______________________________________________________________________________________________________________00000002BF17'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--end io stats -----------------------
--query two
sELECT [clientid],
[dateofdata],
[dimension line_item_id],
[dimension creative_id],
Sum([impressions]) imp,
Sum([clicks]) clk
FROM (SELECT DISTINCT datevalue dt
FROM #dates) dt
JOIN [dbo].vw_report_history rh(nolock)
ON rh.dateofdata = dt.dt
GROUP BY [clientid],
[dimension line_item_id],
[dateofdata],
[dimension creative_id]
-------------io stats ---------------------
Table 'DFP_Reports_History_2013'. Scan count 33, logical reads 3592388, physical reads 6, read-ahead reads 3580434, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_2014'. Scan count 33, logical reads 9350518, physical reads 16, read-ahead reads 9306283, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201501'. Scan count 33, logical reads 784547, physical reads 3, read-ahead reads 778613, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201502'. Scan count 33, logical reads 690363, physical reads 3, read-ahead reads 684032, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201503'. Scan count 33, logical reads 1009663, physical reads 8, read-ahead reads 1003467, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201504'. Scan count 33, logical reads 1069080, physical reads 3, read-ahead reads 1061425, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201505'. Scan count 33, logical reads 1200269, physical reads 3, read-ahead reads 1192159, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201506'. Scan count 33, logical reads 1174801, physical reads 5, read-ahead reads 1166932, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201507'. Scan count 33, logical reads 1448145, physical reads 4, read-ahead reads 1440241, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201508'. Scan count 33, logical reads 2727251, physical reads 7, read-ahead reads 2709691, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201509'. Scan count 33, logical reads 2799816, physical reads 15, read-ahead reads 2782194, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201510'. Scan count 33, logical reads 2336149, physical reads 4, read-ahead reads 2325155, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201511'. Scan count 33, logical reads 2933287, physical reads 6, read-ahead reads 2918749, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201512'. Scan count 33, logical reads 3129165, physical reads 9, read-ahead reads 3114056, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201601'. Scan count 33, logical reads 1857644, physical reads 4, read-ahead reads 1849663, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201602'. Scan count 33, logical reads 1751051, physical reads 11, read-ahead reads 1738872, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201603'. Scan count 33, logical reads 1999681, physical reads 6, read-ahead reads 1991852, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201604'. Scan count 33, logical reads 1843576, physical reads 78, read-ahead reads 1830819, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201605'. Scan count 33, logical reads 2132316, physical reads 8, read-ahead reads 2125305, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201606'. Scan count 33, logical reads 2127417, physical reads 10, read-ahead reads 2114634, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201607'. Scan count 33, logical reads 1812176, physical reads 3, read-ahead reads 1801911, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201608'. Scan count 33, logical reads 1929416, physical reads 3, read-ahead reads 1920810, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201609'. Scan count 33, logical reads 114150, physical reads 3, read-ahead reads 112568, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201610'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201611'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DFP_Reports_History_201612'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
September 7, 2016 at 12:31 pm
the first query only tries to read from one table because of the data constraint. This is correct behavior
The second querry tries to read from all of the tables even though the #data table only has a finite list of dates in it. Each of there table has about 150 million records. Lastly, i cant duplicate this. When i create three tables and a partitioned view on them with the date constraint and load 10 records into each then run the second query on the test partitian it only tries to read from the proper tables.
Is there some sort of row limit that forces it to try and read from all tables pending the way you query it?
September 7, 2016 at 8:30 pm
Re-reading.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 7, 2016 at 8:47 pm
In the absence of seeing your view, I can only see one thing that jumps out at me. You are starting your second query with FROM (SELECT DISTINCT...
Some things you might try:
First, try storing your SELECT DISTINCT subquery to a new temp table #distinctdates. Build a unique clustered index on it, and join to it, rather than joining to the (SELECT DISTINCT subquery. The optimizer has no statistics on what the results of the subquery will be and may be choosing an ultraconservative execution plan. Joining to an indexed #distinctdates table will at least give it some stats to work with.
Second, if #1 is ineffective, try CROSS APPLYing a subquery instead of using a join.
Finally, if date ranges are highly selective, you might benefit from having nonclustered indexes with date as the first column of the index.
Good luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 8, 2016 at 6:22 am
sorry. i created a script to basically do the same thing i'm doing minus the 115 million records in each table and it works properly. I cant script the version that's not performing properly because it's too much data. I'm working through my issue. Once i figure it out i'll reply. I think it has something to do w/ the size of the data. I'm still in the process of finding the issue
September 8, 2016 at 6:36 am
Please do post the results when you find your answer. I'm curious myself.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply