David Singleton:I am not sure if this is exactly correct, but I may have missunderstood. My understanding was that Indexed Views would be introduced in "A service pack for 5.0" but not necessarily SP1. So it could be SP2 or 3 etc. It was clear of course that Flow Fields are definitely gone for 6.0. Also my understanding is that SQL 2008 handles indexed Views very well, so I think that they need to wait till SQL 2008 is more main stream before killing Flow Fields.
I am not sure if this is exactly correct, but I may have missunderstood. My understanding was that Indexed Views would be introduced in "A service pack for 5.0" but not necessarily SP1. So it could be SP2 or 3 etc. It was clear of course that Flow Fields are definitely gone for 6.0.
Also my understanding is that SQL 2008 handles indexed Views very well, so I think that they need to wait till SQL 2008 is more main stream before killing Flow Fields.
Based on development schedule there is only on Service pack being released next year in 1st quarter. They did not mention anything about sp2 or sp3. I don't if they would released 2 more service pack during 6-8 month period till 6 comes out.
I would like to know what the performance difference is between the sift and indexed views. Specically the read perfromance.
Well, I guess we are all quite curious! But just "assuming":
Comparing standard SIFT handling now (SIFT tables) with SIFT handling in future (Indexed Views) I think there will be a big improvement. Currently, primarily the write performance (and related blocking) is the issue; this will be remarkably reduced as no more additional records will be created.
But as the SIFT Indexed Views will SELECT data from the source table - Ledger Entry tables etc. - it is crucial to have optimized indexes there. THen, when querying data from the Views, their indexes have to be optimized as well. If all is indexed properly, it will generally improve performance, at least up to a certain amount of records in the source table. But as mentioned: comparing standard setup before/after.
Between an optimized SIFT structure now and in future, there will not be such a big difference. And - I'm afraid - on pretty large tables the Indexed Views may even perform worse than with real aggregated values in dedicated SIFT tables ...
We will see ...
Jörg A. StrykSTRYK System ImprovementPerformance Optimization & Troubleshootinghttp://www.stryk.info
First, I have yet to be convinced that say in Customer Ledger Entry table with 20 million entries and say 5 to 10 major customers a Balance calculation will be faster with any kind of SQL query on the table than reading 1 entry in a SIFT table. Let's not forget that Indexes on a table are helping structures that offer a read speed increase at the cost of a write overhead. The fact that SIFTs are badly implemented now does not mean they are not a good idea.
Second I do realize this is a storm in a teacup since no offense Jorg but haven't heard this indexed views thing from anyone else. Can anyone else at Convergence confirm they heard this as well? I will make sure to ask at Directions anyway ...
P.S. Not that it matters to MS what we talk about it but what about offering both functionalities and letting us decide which one we need in a particular case.
DenSter:...The tricky thing about this type of webinar is that many times the viewer hears what they want to hear. ...
Yes thats the problem when the information is not correctly passed to the readers. There was a question at Convergence by someone that clearly believed that they were told they could double the performance of NAV by making this simple change. And was asking why it was not being done.
Much the same can be said for the Hardware sizing guide, which has some good facts in it, but enough mistakes to cause disasters for those that read it without really knowing what is going on.
cnicola:... Second I do realize this is a storm in a teacup since no offense Jorg but haven't heard this indexed views thing from anyone else. Can anyone else at Convergence confirm they heard this as well? I will make sure to ask at Directions anyway ... P.S. Not that it matters to MS what we talk about it but what about offering both functionalities and letting us decide which one we need in a particular case.
OK, here is your second source I also heard this. (Didn't I already mention that?)
They indicated that this is a REPLACEMENT to Sift, not an alternative. Mark, Eric, Erik, anyone else want to comment?
David Singleton: During Hynek's SQL Performance presentation at Convergence, there was one person that brought up this specific example, and was under the clear understanding that their NAV system would easily run twice as fast by using this suggestion. That is complete and utter nonsense, but it came from Microsoft, so it must be true.
During Hynek's SQL Performance presentation at Convergence, there was one person that brought up this specific example, and was under the clear understanding that their NAV system would easily run twice as fast by using this suggestion. That is complete and utter nonsense, but it came from Microsoft, so it must be true.
I was in that session as well, and Hynek replied very good on that. That specific insert might run 50% faster (which is 1.5 times faster, not 2 times faster), but you must be careful with as statement like that, and don't take it out of proportion. If your transaction contains 10% of that specific insert and 90% other stuff, then only the part of the 10% runs faster ... .
David Singleton: cnicola:... Second I do realize this is a storm in a teacup since no offense Jorg but haven't heard this indexed views thing from anyone else. Can anyone else at Convergence confirm they heard this as well? I will make sure to ask at Directions anyway ... P.S. Not that it matters to MS what we talk about it but what about offering both functionalities and letting us decide which one we need in a particular case. OK, here is your second source I also heard this. (Didn't I already mention that?) They indicated that this is a REPLACEMENT to Sift, not an alternative. Mark, Eric, Erik, anyone else want to comment?
If you're talking about 5.0SP1 and indexed views replacing sift, I didn't get this during Hynek's session, but I did in "Meet The Experts" from Jesper Falkebo himself. I didn't get if this was going to be a complete replacement though ... this was not "literally" said.
waldo: David Singleton: cnicola:... Second I do realize this is a storm in a teacup since no offense Jorg but haven't heard this indexed views thing from anyone else. Can anyone else at Convergence confirm they heard this as well? I will make sure to ask at Directions anyway ... P.S. Not that it matters to MS what we talk about it but what about offering both functionalities and letting us decide which one we need in a particular case. OK, here is your second source I also heard this. (Didn't I already mention that?) They indicated that this is a REPLACEMENT to Sift, not an alternative. Mark, Eric, Erik, anyone else want to comment? If you're talking about 5.0SP1 and indexed views replacing sift, I didn't get this during Hynek's session, but I did in "Meet The Experts" from Jesper Falkebo himself. I didn't get if this was going to be a complete replacement though ... this was not "literally" said.
It was not Hyneks session, it was the other one.
I don't think you did David. But that may be because I am seeing red in front of my eyes.
I am amazed this information did not make it into any of the blogs I read from Convergence from you guys.
Am I alone in thinking this is a huge change? And worried that with MS's tendency to test things in Cronus this could have some horrible performance effects?
I mean look at the outcry at MS adding Index Hinting which is a functionality that can be easily disabled. And no one reacts to this change?
I have 3 customers with dbs over 100GB and Value entry tables with 7-20 million entries and months put in to make them work at an acceptable speed. I have hard time getting clients to pay for this kind of work (you know Navision costs x$ but you will have to pay y$ to make it work with your volume of transactions). How would we be able to justify another round of SQL tuning?
Thing is as it has been proven by the SQL experts out there Navision can be tuned pretty well with current technology. Yes MS has not incorporated too much of this in the std db for now but we all thought it was a matter of time.
Oh well ... whatever ...
Sometimes I feel like writing some rants like NavStudent from Mibuso
waldo: If you're talking about 5.0SP1 and indexed views replacing sift, I didn't get this during Hynek's session, but I did in "Meet The Experts" from Jesper Falkebo himself. I didn't get if this was going to be a complete replacement though ... this was not "literally" said.
Well I must say that Eric does listen to this stuff closer than I do. So may be we will get an option. In fact I even asked "Will we have an option MaintainIndexedViews, just like we now have MaintainSIFTfields" and was told no, but who knows. if we got both, this would be a dream scenario.
cnicola: Sometimes I feel like writing some rants like NavStudent from Mibuso
Please no, we are a civilized bunch here.
Don't worry I won't ... I counted to 10 and then went to watch a little more of this crappy Giants vs. Dolphins game. Damn England and their rain.
Jörg Stryk:The problem is to find the right balance between write and read performance - have in mind that with NAV we have way more reading transactions than writing transactions!
Let's balance also this statement
Most read-statements should come from the DBcache. So disk IO should MOSTLY be for writing.
Current plan is to deliver the "Indexed Views" to maintain SIFT in 5.00 SP1. These changes should also be on the next major release "6.0".
There are no plans on keeping the current SIFT triggers together with Indexed Views, but we are in close collaboration with the SQL team to address any possible issue.
Also, on top of lab tests, we are working with partners interested in Beta testing the bits, so that we can see the results on a live environment.