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 |
|
malvernlloyd
Starting Member
3 Posts |
 Posted - 2011-11-23 : 12:58:13
|
| Let's say we have three databases, db1, db2, and db3. db1 and db2 have data, whereas db3 merely points at both of them and consists of views, like this:create view db1_customers asselect 'db1' as db, customers.* from db1.dbo.customerscreate view db2_customers asselect 'db2' as db, customers.* from db2.dbo.customerscreate view all_customers asselect * from db1_customersunionselect * from db2_customersWould the following query, executed in db3, be able to make use of the ix_customer_zipcode index on each of the customers tables in their respective databases, db1 and db2?select * from all_customers where zipcode = '10025' |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
malvernlloyd
Starting Member
3 Posts |
Posted - 2011-11-23 : 14:31:22
|
| When I examine the actual query Execution Plan, there is no use of the index on zip. Is the use of the index not automatic?Select ..Stream Aggregate .. Sort....Concatenation..Compute Scalar......Table ScanCost: 0% .. Cost: 0%........Cost:63%...Cost: 0%........Cost: 0%..........Cost: 18%......................................................Compute Scalar.....Table Scan........................................................Cost: 0% ........Cost: 18% |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-23 : 14:51:17
|
| Maybe your particular query wasn't going to use an index anyway?If you don't already know the Ins & Outs of partitions suggest you look into that ... your example looks like that might be exactly what you are actually trying to achieve. |
|
|
|
malvernlloyd
Starting Member
3 Posts |
Posted - 2011-11-23 : 15:26:55
|
| What we have is a scenario where satellite locations with identical database structures are sending full backups periodically to a central data warehouse for consolidated reporting. These databases predate my arrival--I've inherited the situation.Each of these databases contains about 150 tables. I don't think partitioning would simplify the logistics of this kludgy replication scenario. Right now it's a simple restore. If we used partitions, we would have to load 150 tables.The databases were not designed with replication in mind: there are PK collisions because of autoincrementing integer PKs; each site was not assigned a range of values to prevent collisions. So I was hoping to be able to create views as below, but I wasn't sure whether the indexes on the base tables would be used because I've never used views that are created by UNIONing several views, each of which goes against a table in some other database:create view FOO asselect * from someView [which is based on a table from db1]unionselect * from anotherView [which is based on a table from db2]unionselect * from yetAnotherView [which is based on a table from db6]The test table I'm working with has a single index on zipcode. It appears in db1 and db2 (and so forth) and each table contains about 1 million rows. The indexes are never used when view FOO is queried; each of the underlying tables is scanned.I don't see where a table scan of a million rows would be more efficient than using the index, so something appears to be keeping the query against FOO from using the indexes on the ZIP column in the underlying base table. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-23 : 15:56:35
|
| "I don't see where a table scan of a million rows would be more efficient than using the index"Statistics not up to date? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
views, unions, indexes