meta user
network profile
| bio | website | milesquaretech.com |
|---|---|---|
| location | Hoboken, NJ | |
| age | ||
| visits | member for | 1 year, 2 months |
| seen | Jan 14 at 15:11 | |
| stats | profile views | 4 |
Yep, I code in a tux. Don't you?
|
Oct 27 |
comment |
Design Pattern - One of Many Parent Tables @NathanLong - I've never come across a formal name for this pattern. Octopus isn't bad. I've also thought of it as a one-OF-many join. |
|
Oct 17 |
awarded | Commentator |
|
Oct 17 |
comment |
What factors go into an Indexed View's Clustered Index being selected? I get what you're saying about behavior & agree the only way to know what's happening is to test. It's not like there is a list of 300 optimizations & rules we can study. Yet, after working with the product 14 hours a day since SQL 4.2 I have a sense of it's personality, as I'm sure you do. Outside of indexed views, I can pretty much tell how adding/removing an index will affect a give query plan. I have a feel for the range of reads on a given query,etc... I'm just looking to become more familiar with the personality in this respect. |
|
Oct 16 |
comment |
What factors go into an Indexed View's Clustered Index being selected? Well said -- that explains it. I'm working through the white papers. Since my view was just a test, I'm working through expanding my data sets significantly (16k rows to a few million in the bigger tables). I'm looking for a work factor where the decision starts to tip towards using the index -- i.e. a rule of thumb. |
|
Oct 16 |
accepted | What factors go into an Indexed View's Clustered Index being selected? |
|
Oct 16 |
comment |
What factors go into an Indexed View's Clustered Index being selected? @SQLKiwi - now we're getting somewhere! What makes the clustered index on a view more expensive to pick than 12 other indexes? On successive runs my sqlplan file does show StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound", but after DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS it shows StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut". Yet the path to materialization stays the same. |
|
Oct 16 |
comment |
What factors go into an Indexed View's Clustered Index being selected? @MaxVernon - just for giggles I tried DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS before each run and multiple combos of 1,2,and 3 fields. No change to the above behavior. |
|
Oct 16 |
comment |
What factors go into an Indexed View's Clustered Index being selected? @SQLKiwi - I did see your comment. I understand forcing the issue (with...),and that SQL picks the 'most efficient' method. Yet, the Profiler images above & execution plans I posted on Gist show statement costs of 0.0527923 (no hint) and 0.00331306 (hint). According to measures of CPU, reads, duration, and statement cost -- the clustered index should have been chosen b/c it's more efficient by at least an order of magnitude. What am I missing? |
|
Oct 16 |
comment |
What factors go into an Indexed View's Clustered Index being selected? @MaxVernon - a single field, no. Explicit fields, yes. |
|
Oct 15 |
revised |
What factors go into an Indexed View's Clustered Index being selected? typo in title |
|
Oct 15 |
asked | What factors go into an Indexed View's Clustered Index being selected? |
|
Sep 29 |
comment |
Rules in SQL Server 2005 Just an FYI .... RULES have been marked deprecated in SQL 2005 & later. They exists, but you are cautioned against using them in new development work. msdn.microsoft.com/en-us/library/ms188064%28v=sql.90%29.aspx |
|
Sep 28 |
awarded | Teacher |
|
Sep 28 |
answered | For a boolean settings table, should I toggle a flag, or insert/delete? |
|
Sep 27 |
comment |
Design Pattern - One of Many Parent Tables Thanks for the input; I agree with most of your comments. I probably in-artfully described the required relationship (excluding solution 2 in your eyes), as it was a made up example. fn1 - understood, i was just simplifying tables a great deal to focus on the problem. fn2 - composite keys and I go way back! Re the lesser coupling schema I understand the simplicity, but personally try to design with DRI wherever possible. |
|
Sep 27 |
awarded | Scholar |
|
Sep 27 |
accepted | Design Pattern - One of Many Parent Tables |
|
Sep 26 |
comment |
Design Pattern - One of Many Parent Tables @ypercube I added that each of those tables has an associated language table. I was attempting to craft a scenario where "name" text can vary per request and thus cannot be stored in Alertable. |
|
Sep 25 |
comment |
Design Pattern - One of Many Parent Tables For some cases you make a good point -- effectively solution #3. Any time the data is fast moving or localized, however, it won't work. Say for example Product, Customer, and News each have a corresponding "Lang" table to support Name in several languages. If I have to deliver 'name' in the users native language I cannot store it in Alertable. That make any sense? |
|
Sep 25 |
awarded | Editor |
