Is a SQL View the fastest way to manage multiple count fields, or does SQL 2014 have something better ?

Question

Post reply

Is a SQL View the fastest way to manage multiple count fields, or does SQL 2014 have something better ?

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

isuckatsql SSChampion Points: 10381 More actions · Answer 1

Thanks again Eirikur!

I think i found part of my problem.

"On average, this query takes 600.4 milliseconds on my system. We can speed it up using a nonclustered index:

CREATE NONCLUSTERED INDEX [IX_RFM] ON [WEBLOG] ([COOKIE_ID])

INCLUDE ( [TRANSACTION_VALUE]) ON [PRIMARY]

It now takes 336 milliseconds to run the query, a performance improvement of 44%. We can increase this performance gain by using an indexed view instead of creating the previous index:"

I used a nonclustered index AND an Indexed View. The Indexed View should have been INSTEAD of the nonclustered index.

Question: If i have created an Indexed View, then run my execution plan, and it suggests creating a nonclustered index, should i ignore the plans suggestions ?

I also found an interesting article regarding a view being faster than a query.

http://stackoverflow.com/questions/439056/is-a-view-faster-than-a-simple-query

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Views are helpful but no a panacea, with or without index. Other options include filtered indexes, column store indexes, partitioning and compression to name few. And as with (almost 😀 ) everything, there is no one-size-fits-all!

😎

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 3

If you want real performance, I'd recommend that you stop counting altogether. Indexed views have an overhead on inserts in that they have to recalculate the totals for the affected aggregates. Other methods end up counting everything more than one. I believe that it would be much less expensive in all cases if you treated this whole thing like an inventory system where you start out with a known inventory and then debit or credit each line of the inventory for only those things that change. SELECTS would be instantaneous because there would be nothing to calculate... it would be a simple lookup.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

Jeff Moden (5/10/2014)
If you want real performance, I'd recommend that you stop counting altogether. Indexed views have an overhead on inserts in that they have to recalculate the totals for the affected aggregates. Other methods end up counting everything more than one. I believe that it would be much less expensive in all cases if you treated this whole thing like an inventory system where you start out with a known inventory and then debit or credit each line of the inventory for only those things that change. SELECTS would be instantaneous because there would be nothing to calculate... it would be a simple lookup.

Thanks Jeff,

got too focused on the problem to step back and see if it was the right approach:w00t:

😎

David Burrows SSC Guru Points: 65138 More actions · Answer 5

Eirikur Eiriksson (5/11/2014)
Jeff Moden (5/10/2014)
If you want real performance, I'd recommend that you stop counting altogether. Indexed views have an overhead on inserts in that they have to recalculate the totals for the affected aggregates. Other methods end up counting everything more than one. I believe that it would be much less expensive in all cases if you treated this whole thing like an inventory system where you start out with a known inventory and then debit or credit each line of the inventory for only those things that change. SELECTS would be instantaneous because there would be nothing to calculate... it would be a simple lookup.
Thanks Jeff,
got too focused on the problem to step back and see if it was the right approach:w00t:
😎

Well Jeff is SQL God and overall Guru 😛

Beware if you annoy him though, better have a good duck technique for that pork chop launcher 😉

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 6

Heh... :blush: No. It's nothing like that. Actually, it's because I'm a bit lazy. All those recalculated aggregates would mean that I'd have to worry about scalability in the future and how to exclude things that were no longer available, etc, etc. I started thinking of a check book and asked myself if it would be better to recalculate all transactions in the checkbook before I wrote a check or just to keep a current total that I would add or subtact to or from depending on the transaction. The later won out, of course. Then I asked myself if the checkbook was on a computer, would I ask the computer to do a full aggregation of all transactions or just do like I had done manually. Again, the latter won out because it seemed like less work for the computer especially in the face of high scalability. Simple update of a inventory/category table with a true up before archiving older rows just seems to make sense both manually and computationally. Might not even need to do the true up if the code is air tight and deadlock free (not so hard to do, either).

As to how someone like EBay would do it? Rumor has it that they decided to do everything without stored procedures and that they have more server instances than you can shake a stick at. Even with such massively parallel processing (MPP) capabilites, I just couldn't imagine them doing a full inventory aggregate or even using indexed views just to display what the current inventory was especially since there are multiple categores.

It's a little more work up front and takes a bit more planning but, once done, it'll be scalable no matter the throughput.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65138 More actions · Answer 7

You're far too modest Jeff 😀

Would love to me you in person, if I could ever get across that big pond 🙁

Far away is close at hand in the images of elsewhere.
Anon.

isuckatsql SSChampion Points: 10381 More actions · Answer 8

Thanks Jeff.

Regarding the Ebay or Amazon concept, they may have lots or servers but don't have a one to one relationship between server and user.

At the moment i do, and with a powerful, dedicated, development server all to myself, i was wondering what methods they and others were using to get their inventory performance count.

Many websites on the internet seem to use this method, and i was wondering if there was a common approach, that i was not aware of.

isuckatsql SSChampion Points: 10381 More actions · Answer 9

It seems the jury is still out on the best way to do this.

http://stackoverflow.com/questions/7160411/is-it-better-to-maintain-a-seperate-count-table-vs-running-count-query-every-tim

http://stackoverflow.com/questions/3498646/select-count-vs-keep-a-counter

isuckatsql SSChampion Points: 10381 More actions · Answer 10

Jeff Moden (5/10/2014)
If you want real performance, I'd recommend that you stop counting altogether. Indexed views have an overhead on inserts in that they have to recalculate the totals for the affected aggregates. Other methods end up counting everything more than one. I believe that it would be much less expensive in all cases if you treated this whole thing like an inventory system where you start out with a known inventory and then debit or credit each line of the inventory for only those things that change. SELECTS would be instantaneous because there would be nothing to calculate... it would be a simple lookup.

Just to be clear, you are suggesting a SQL Trigger rather than an Audit ?

Thanks

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 11

isuckatsql (5/11/2014)
It seems the jury is still out on the best way to do this.
http://stackoverflow.com/questions/7160411/is-it-better-to-maintain-a-seperate-count-table-vs-running-count-query-every-tim
http://stackoverflow.com/questions/3498646/select-count-vs-keep-a-counter

While I appreciate the concern of the 2 fellows whose posts you cited, I'll suggest that a data warehouse is filled with "other tables" and can be kept up to date in a 100% accurate manner on a real time basis.

Heh... the "jury" has been out on this for a while. You just feel like arguing with the jury and conversing with reporters. 😀 In this business, that's a very good thing because that's how new techniques are discovered and old ones are revalidated. The next thing you need to do is to setup some multi-million row (a millions rows is pretty small, anymore... especially for a high turnover inventory system) test tables and start retesting. I could be wrong but it doesn't make sense to be to recalculate balances over and over.

As for the EBay and Amazon stuff, I'm not privy to their code so I can't actually say what method they use but I would imagine that it's closer to being an aggregate table or tables (think real-time data-warehouse), like I suggested, than anything else.

Last but not least, take a look at Books Online on the subject of "Indexed Views" where they say, and I quote...

[font="Arial Black"]Indexed Views[/font]
An indexed view is a view that has been materialized. This means it has been computed and stored. You index a view by creating a unique clustered index on it. Indexed views dramatically improve the performance of some types of queries. Indexed views work best for queries that aggregate many rows. [font="Arial Black"]They are not well-suited for underlying data sets that are frequently updated.[/font] For more information, see Designing Indexed Views.

The bottom line is, as with all else, "It Depends". You keep asking about how Ebay and Amazon do it. I'd be really surprised if they recalculated balances using a table aggregation (or materialized view) on the fly for every transaction.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

isuckatsql SSChampion Points: 10381 More actions · Answer 12

Jeff,

Many thanks for your time, i really appreciate your knowledgeable advice!

Ian