June 5, 2014 at 3:54 pm
I have a view that strings together many large tables broken out by month. The query in the view is something like this.
select * from db.dbo.table_current
union all select * from db_archive.dbo.table_201406
union all select * from db_archive.dbo.table_201405
union all select * from db_archive.dbo.table_201404 ... and so on.
Each of these tables have all the same indexes applied, and each table has a check constraint that only allows records within the date range specified to be inserted.
Here is the interesting bit...
If I write a standard query with date parameters, the execution plan shows SQL is only querying the underlying tables in the given date range. For example:
SELECT
view.columnA, joined
view.columnB
FROM
view
WHERE
date > '2014-06-01'
The execution plan will show me SQL is only performing Index Seeks on db.dbo.table_current and db_archive.dbo.table_201406, as I would expect.
The problem is when I join another table and am only interested in records from the view that are created later than the record I'm joining. (Clear as mud?) Example:
SELECT
view.columnA,
view.columnB,
table.ColumnC
FROM
view
INNER JOIN table on table.tableId = view.tableId
WHERE
date > table.createdDate
This was taking way longer than I expected. When I took a look at the execution plan, it appears it is now hitting every table in the view.
Anyone have suggestions on how I can force SQL to only hit the tables required? I have some ideas using dynamic SQL, but would rather avoid doing that.
June 5, 2014 at 4:20 pm
Not a lot of actual details to work with here but have you tried moving your predicate to the join?
SELECT
view.columnA,
view.columnB,
table.ColumnC
FROM
view
INNER JOIN table on table.tableId = view.tableId
AND view.date > table.createdDate
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 6, 2014 at 7:18 am
Yes I have tried moving the predicate to the join and had the same result.
June 6, 2014 at 7:35 am
Still no actual details. Here is my last shot in the dark.
with ViewCTE as
(
SELECT
view.columnA
, view.columnB
FROM view
WHERE date > '2014-06-01'
)
SELECT
v.columnA,
v.columnB,
table.ColumnC
FROM ViewCTE v
INNER JOIN table on table.tableId = v.tableId
WHERE v.date > table.createdDate
Please take a look at this article which explains what to post for performance problems.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply