Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Fri Dec 15, 2017 10:09 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: PosgreSQL Performance Summing Records
PostPosted: Mon Jun 03, 2013 6:14 pm 
Offline

Joined: Sun Jan 22, 2006 2:37 am
Posts: 327
I have a table currently that adds up all the parts I have used to make products. I have a qty figure in there and I sum that. However the problem is at the moment I have to consume qtys of parts each time I build a product, but i would prefer to consume the parts 1 at a time. That would create many more records so I would need to sum a much larger data set to make that work because instead of a qty of 100 parts consumed, there would not be 100 separate records. But it would me closer to whats happening when I build products so it would be better from an operations point of view.

How many records can PosgreSQL sum without slowing down? I could wind up with millions of records if I use a separate record for each one.

Only one software tool needs those summaries, but it has to do it at launch, so the longer the sum takes the longer the tool will take to launch. I have a few thousand part types as well so I am doing a sum for each part type.

Grant


Top
 Profile  
Reply with quote  
 Post subject: Re: PosgreSQL Performance Summing Records
PostPosted: Mon Jun 03, 2013 6:37 pm 
Offline

Joined: Wed Mar 22, 2006 11:15 am
Posts: 712
Location: Southern California
I'm getting 1.6s to sum 1.2m rows on a Mac mini server with an SSD. This is a bigint column, not a regular 4-byte int column.

Your times will vary depending on the CPU, disk, server load, optimizations, and network latency.

_________________
Daniel L. Taylor
Custom Controls for Real Studio WE!
Visit: http://www.webcustomcontrols.com/


Top
 Profile  
Reply with quote  
 Post subject: Re: PosgreSQL Performance Summing Records
PostPosted: Mon Jun 03, 2013 6:37 pm 
Offline
Real Software Engineer

Joined: Sat Dec 24, 2005 8:18 pm
Posts: 7858
Location: Canada, Alberta, Near Red Deer
Not really sure how you're dong this
I'm assuming that each "product" has entries for "used 1 part of type X" and it if requires a hundred then there's 100 record like that ?
And what you're doing is summing up all the parts that have been used ?

This is a one liner as far as I can tell from what little you've given

select part, sum(qty) from partsUsed group by part

or something like that

with proper indexes this should go very quickly

However it MAY also be worth having a table that is updated each time a record is added to this table that just keeps the running totals so you Don't have to do sums across millions of records (just dont write it in client code - do it as a trigger on the server side of things so it works regardless of what adds a record to the parts used list)

_________________
Norman Palardy (Real Software)


Top
 Profile  
Reply with quote  
 Post subject: Re: PosgreSQL Performance Summing Records
PostPosted: Mon Jun 03, 2013 6:57 pm 
Offline

Joined: Sun Jan 22, 2006 2:37 am
Posts: 327
Thanks and I did have running totals tables but for some reason I found they were becoming inaccurate and I could never work out why. I used transactions so that any time I updated or added a record to the main table I also had to do the sum table or it would not do both. I also made sure I sent an update to the summing table to do the update based on what it had so if more than one client did it at the same time it should be ok. So I never worked out why I was drifting out.

So I run a replace into each day and updated all the tables based on a sum so even though that took a little time, it was ok because it was run at night time. But when I moved the database to Posgresql I could not do replace into anymore and never managed to make anything else work. But I found postgesql was so fast I did not need to using the summing ables so I just coded this out.

But perhaps its time to do it again. My problem is how to do it right so I don't drift out and become inaccurate?

Grant


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group