Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Is a SQL View the fastest way to manage multiple count fields, or does SQL 2014 have something better ? Expand / Collapse
Author
Message
Posted Sunday, May 11, 2014 9:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:30 PM
Points: 36,765, Visits: 31,222
Heh... 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1569627
Posted Sunday, May 11, 2014 9:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:18 AM
Points: 7,051, Visits: 6,807
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.

Post #1569628
Posted Sunday, May 11, 2014 11:35 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
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.
Post #1569641
Posted Sunday, May 11, 2014 12:19 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
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

Post #1569646
Posted Sunday, May 11, 2014 1:08 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
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
Post #1569648
Posted Sunday, May 11, 2014 9:27 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:30 PM
Points: 36,765, Visits: 31,222
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...

Indexed Views
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. They are not well-suited for underlying data sets that are frequently updated. 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1569674
Posted Monday, May 12, 2014 4:49 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Saturday, July 19, 2014 9:15 PM
Points: 537, Visits: 1,110
Jeff,

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

Ian
Post #1569759
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse