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 |
|
benricho
Yak Posting Veteran
84 Posts |
 Posted - 2004-07-01 : 21:44:37
|
| I am working on an application where we have subscribers that can be active or inactive (generally a lot more active subscribers) and we display the users in a grid, with the ability to display active, or inactive subscribers. We had all subscribers in the one table, with an row (int) showing the subscribers state (one of 3 values, active, inactive and deleted).This was working well but we tested the application with a large amount of sample data, and it became quite slow because of the full table scan on the where clause used to only grab data with a certain state (such as active). So we split the table into seperate tables, one for active_subscribers, one for inactive_subscribers (with the state row for inactive or deleted), and a subscriber table which holds the subscriberID and DateAdded. The change in structure ran this grid much faster.But when we added a reporting feature to the application, we had to do a union of the active and inactive tables (into a view so it was easy to work with). Now the problem is that we can't index the view since we are using a union all in the view, so it looks like the report will be slow as the tables get bigger (we have indexed the active and inactive subscriber tables, but looking at the Execution Plan it still does a Hash Match/Inner Join on the union and doesn't seem to take advantage of the indexes).Any suggestions on the best trade off so it will be the fastest in both situations? |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-02 : 00:57:37
|
| If both tables have identitical structure, AND a [redundant] column for Active/Inactive (even though each table only contains ONE of those "states"), AND add a Check Constraint to the State column AND put the State column in the Primary key - e.g. [code]CREATE TABLE ... MyState int CHECK (MyState = 1), -- Other table has "2" etc....PRIMARY KEY(..., MyState, ...)[/CODE]Then SQL will, aledgedly, be smart enough to only use the appropriate table in a VIEW - i.e. if you search the view for MyState=1SQL will only use one of the tables.There's some stuff in BOL about it ... hang on ... "Using Partitioned Views"But I dunno if that will solve your problem in generalKristen |
 |
|
|
|
|
|
|
|
Table scan or Union all view...which is better?