March 12, 2012 at 9:25 am
opc.three (3/12/2012)
...
Table '#test_objects'. Scan count 1, logical reads 1174, physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#test_objects'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
....
We still see a scan (Scan Count = 1), but take a look at the reads. With the select count(*) we see 1174 reads. With the cte using TOP(n) we see a mere 2. A huge difference in a small and contrived example. With concurrency, and potentially much larger row counts you can see which technique gives us a better chance to scale.
1. In your previous post you claimed that COUNT(*) performs table scan unlike SELECT TOP N, which I've basically rejected as both do perform table scan.
2. Logical reads are not physical reads, they just show number of page requests from the buffer cache, not page reads! I wouldn't pay to much attention to them especially in this case as:
3. It's hard to call your solution scalable at all, as it's scale up to 20 rows update.
Also, if you're expecting multiple concurrent 50,000 row's updates I would try avoid having any trigger for a scalability sake...
I don't think it's a worth to argue on this case any longer...
March 12, 2012 at 9:36 am
Eugene Elutin (3/12/2012)
opc.three (3/12/2012)
...
Table '#test_objects'. Scan count 1, logical reads 1174, physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#test_objects'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0,
lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
....
We still see a scan (Scan Count = 1), but take a look at the reads. With the select count(*) we see 1174 reads. With the cte using TOP(n) we see a mere 2. A huge difference in a small and contrived example. With concurrency, and potentially much larger row counts you can see which technique gives us a better chance to scale.
1. In your previous post you claimed that COUNT(*) performs table scan unlike SELECT TOP N, which I've basically rejected as both do perform table scan.
If you re-read my post you'll notice it says "your example will force a full scan", keyword being full.
2. Logical reads are not physical reads, they just show number of page requests from the buffer cache, not page reads! I wouldn't pay to much attention to them especially in this case as:
Is this is a serious comment?
3. It's hard to call your solution scalable at all, as it's scale up to 20 rows update.
I never said the trigger solution would scale up, why do you think the short-wire is in there to prevent out-of-band updates from flooding people with emails they did not generate?
Again, if you re-read my post, I said the TOP(N) count solution would scale up better than the COUNT(*) solution. The trigger solution will only scale to a certain number, but if you only need to support a few rows being updated at a time it's a more expedient, safe and performant solution than building out an entire Service Broker infrastructure as you had suggested 😛
I gave the OP a simple, safe and performant solution for their specific use case (based on this thread and some PMs we exchanged).
Also, if you're expecting multiple concurrent 50,000 row's updates I would try avoid having any trigger for a scalability sake...
I don't think it's a worth to argue on this case any longer...
Agreed, it's OK though, I will not try to correct your manufactured arguments based on things I did not say. I will let you off the hook. Have a great day! 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply