Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MARKINDUNFERMLINE
Starting Member
3 Posts |
 Posted - 2007-08-29 : 08:04:09
|
| I have a union view that unions 36 tables which have a date constraint set on the datetime field, the data for which is is same for all records within the respective tables. The purpose of this is to allow users to go straight to the month that they require. This works fine with raw sql queries using hardcoded dates , the query plan shows that only the required tables are accessed. However, if i create a lookup table (this is for business objects), join the union view date field to date field and use a between criteria, it ignores the constraint and scans all 36 tables in the view. Changing the between to = shows that the constaint is used. Does anyone have any ideas here , i seem to have tried everythingMark |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
MARKINDUNFERMLINE
Starting Member
3 Posts |
Posted - 2007-08-29 : 08:58:11
|
| yes, each table has 3m rows and we keep a rolling 36 months , dropping of the last one and creating the new one. I would be a bit worried about a table with 100m + rows in it Mark |
 |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2007-08-29 : 12:44:25
|
| What if you use between criteria against the view without the lookup table? If this works, you could look at employing index awareness in the BO Universe (presuming you're not already doing this)?Mark |
|
|
|
MARKINDUNFERMLINE
Starting Member
3 Posts |
Posted - 2007-08-30 : 06:15:18
|
| yes that is the fallback, not the nicest way to implement in BO but it may be the only way. We are going to test this in sql 2005 and see if this is a problem in 2000 or it affects all versions.Mark |
|
|
|
|
|
|
UNION VIEWS and CONSTRAINTS