The Whiteboard

4:13 AM

@willOEM If you update your question to include the details you've shared here, it will turn it from an open ended and vague request for suggestions to a somewhat specific problem looking for a solution, and I can see it getting re-opened easily (and by that I mean I'll re-open it myself).

That said, your question might get better answers at our sister site Database Administrators.SE. The crowd there is more used to working with billion rows tables and they are probably a lot more familiar with the technologies you are using than we are.

I'm not a regular on DBA, so you should check out their FAQ to be sure that your question would be welcomed there, and it wouldn't hurt if you dropped by the Heap - their main chat room - and asked about it.

Jimmy Hoffa

4:34 AM

Billion row tables, fun. I miss working with those datasets.

willOEM

4:48 AM

@YannisRizos: Thanks, I'll update my question to try to make it more specific and less about the database side of this issue. DB aside, assuming that I find the perfect setup that returns all query results in a second, what is a good web app framework for dealing with such apps?

Is there any advantage to something Java/.NET/whatever-based? Right now I can get all of my client and server-side coding done with Javascript and Python, respectively, though it is all pretty vanilla, with no major third-party module support.

Yannis Rizos

@willOEM I'm primarily a PHP developer nowadays, therefore the worst possible guy to ask about a web framework for something that's more complicated than a CMS. The PHP ecosystem is more focused on quick and dirty solutions (and it works perfectly for them, but not for anything else).

And I kinda have lost touch with the Java ecosystem...

willOEM

I appreciate the input, I'll work on refining my question.

Yannis Rizos

5:15 AM

@willOEM Have you looked at solutions like Cassandra and hbase? And if your app makes a lot of selects on data that aren't likely to change often you might also want to look at short time in memory caching, memcached and redis being the more popular choices.

user55340

1:39 PM

My solution is tequila (for everything, ever). — Yannis Rizos 8 hours ago

user55340

This means that people need to start writing "the bar" superframework... kind of like Apache. You've got the tequila CMS, vodka db, gin ORM... And those make up the core components. Then you can mix them like drinks...

Thomas Owens

@MichaelT Would things like logging and unit testing be "orange juice" and "ginger ale"?

thorsten müller

No, it's more a kind of process: Tequila Driven Development. You must find development cycles and define the amount of teqila needed to finish them.

Jimmy Hoffa

Is "all of it" a spike, or just a really fun project?

Thomas Owens

1:54 PM

I think "all of it" means "all the tequila in the world". Kind of like a story can't have "all the points", you'd estimate based on number of shots instead of difficulty.

Except you estimate planning things in tequila, too. "It'll take me 3 shots to plan the iteration."

thorsten müller

I take five if I have to talk to this customer. Make it eight if he comes up with one of his weird ideas again.

Thomas Owens

Three shots of tequila for dealing with this design. We have two message queues - A and B. A contains responses to commands (all of which subclass CommandResponse) and B contains status messages (all of which subclass Event). Oh, but wait, one specific type of Event is going to be published to A instead of B.

Fortunately, they do have a parent class. That exposes a useless interface.

Jimmy Hoffa

@ThomasOwens sounds like a problem for, PHP-MAN! Go forth, hack terribly, solve quickly, causing other problems!

Thomas Owens

@JimmyHoffa Part of it is that this was designed by really smart people, just not smart in the technology they were using. It reminds me a lot of my early design/implementations when I'm new to something.

Yannis Rizos

2:14 PM

Sometimes in #TequilaDrivenDevelopment we do shot per passing unit test. After awhile it becomes more challenging. Like a moving target!

Tweeted by tequiladevelop on October 30, 2012 at 4:02 AM

Jimmy Hoffa

haha

willOEM

@YannisRizos I have only just started investigating NoSQL and other caching solutions. They are pretty new to me, so I am trying to understand the use-cases of each.

The bulk data in my application's database never changes, but the layers of metadata occasionally does (we are talking experimental genomic data here). The problem I have is that as the system grows vertically and horizontally, it becomes increasingly difficult to query across multiple table groups.

It is difficult to get my users to alter their habits so that I can streamline the query process, since I am supposed to cater to their every whim.

Yannis Rizos

@willOEM The more details you share about this, the more it sounds like a very good question for the site. Add the details to the question so I can re-open it. I can't answer it, it's outside my expertise, but I can certainly re-open it.

user55340

2:35 PM

The genomic data does help identify the problem domain - that type of data (as I understand it) has a different change pattern than say... corporate ERP style activity. It is possible that some of the things that corporate types desire in a database (foreign key constraints and transactional integrety) isn't critical (or even necessary?) for this problem.

willOEM

@YannisRizos I am working on rewriting the question, thanks.

Jimmy Hoffa

@willOEM What ratio of reads to writes do you have on the slower structures in your DB?

and updates for that matter

When you start seeing structures of the size you're referring to, it becomes really important to understand the difference between OLAP and OLTP and the balance between the two because if it's not already clear which you need, you likely need a mixture which get's tricky to balance right

willOEM

@MichaelT The trick to genomic data is that it is the large number of records and the many layers of metadata. A given experimental data record might have a foreign key relationship with a table that describes this data points assay. Assays can cover multiple genes, which can have multiple transcript, which can have multiple mutations, which can affect multiple pathways, etc.

Now I have a scientist who wants to approach this data from any point in those layers in the metadata. This results in some large, dynamic queries that are hard to predict.

Jimmy Hoffa

@willOEM sounds like a very tall tree structure, no?

@willOEM how current does that scientists view need to be on that "any point in thos e layers" approach?

because that is almost the definition of what OLAP is for, but the problem with OLAP is you're only going to get it somewhat current

willOEM

@JimmyHoffa I'd say for each insert instance (ie. new data set), there are between 10 and 100 selects run against it. Updates of job data almost never occurs, updates of associated metadata can be weekly, but these records are fairly small in number.

Jimmy Hoffa

2:50 PM

So the structures which do get updated aren't slow performers right now, yes?

Yannis Rizos

> my advice would be to seek professional help

I never thought I'd write that ^^^ in an answer...

Jimmy Hoffa

the ones that are write-once read-many-times are the slow performers?

@willOEM if the answer to both of the questions I just said is "yes" then, have you heard of OLAP? Because it sounds like you're precisely in the space of OLAP which may be where you're having trouble; trying to make a transactional (OLTP) db behave analytically (OLAP), is indeed going to result in bad performance and be very difficult

willOEM

@JimmyHoffa That is correct.

Jimmy Hoffa

Those few bits that get updates should live in a transactional database

user55340

@YannisRizos Would that be a good example question psycho.SE (I'm sure there's an area 51 proposal for it...)

Jimmy Hoffa

2:54 PM

and the way OLAP works is you take your transactional data, and basically explode it every so often, this process is called "spinning the cube"

> In computing, online analytical processing, or OLAP (pron.: /ˈoʊlæp/), is an approach to answering multi-dimensional analytical (MDA) queries swiftly.

user55340

(and no, I'm not being serious there)

willOEM

@JimmyHoffa Retrieved data has to be up-to-date, but if it takes time to load the data and build cached views of the data before making it available, it is not a problem.

Jimmy Hoffa

Online analytical processing

In computing, online analytical processing, or OLAP (), is an approach to answering multi-dimensional analytical (MDA) queries swiftly. OLAP is part of the broader category of business intelligence, which also encompasses relational database, report writing and data mining. Typical applications of OLAP include business reporting for sales, marketing, management reporting, business process management (BPM), budgeting and forecasting, financial reporting and similar areas, with new applications coming up, such as agriculture. OLAP tools enable users to analyze multidimensional data int...

@willOEM if it's write-once update-never then you can put it into an OLAP structure, "transactional" (OLTP) db refers to databases which have updates, that's basically what a "transaction" is. Writes are a form of updates, and can be done transactionally to an OLAP cube without much harm; make them atomic, allow dirty reads, and you're generally going to be totally fine so long as they're update-never structures

The structures you actually do updates to are your transactional ones, you want those to be in a separate DB, and then periodically you respin your cube to have the current version of those updates

but there's the rub: Those updates won't be current to the queries requested of you

willOEM

@JimmyHoffa This sounds as if it might be a good solution. I'll have to do some reading! Thanks!

Jimmy Hoffa

If your stakeholders are ok with that, then you can go mostly pure OLAP for them. If they require every query to be 100% current to that moment, then you need to start doing odd mixtures of OLAP and OLTP together which get very complex

willOEM

2:58 PM

@JimmyHoffa I'm willing to make them wait a bit. If I can boost performance significantly, they'll be happy to wait for the occasional update.

Jimmy Hoffa

Perfect, yeah learn about OLAP, the key to OLAP (and why it's data that does not handle updates well) is you duplicate lots of data so that it can be queried in a bunch of ways. So you might have one table that makes it easy to view all the mutations by pathways, then another table that makes it easy to see all the mutations by transcripts, then all the transcripts by pathways, and now across those 3 tables you've duplicated everything about transcript, everything about pathway twice etc

Yannis Rizos

@MichaelT Heh, OP just accepted my answer. Hopefully that means he will seek professional help in the near future.

Jimmy Hoffa

That's why you periodically update OLAP, because the update process has to update 20 tables just for an update to one entity because that entity is duplicated all over

but the result is the ability to quickly query all sorts of things from all sorts of perspectives (this is referred to as "slices" in "dimensions" of a cube, transcript may be a dimension for instance, and one slice of transcript is by mutation, one slice of transcript is by pathway etc)

@willOEM write the answers to the questions I've asked you in here in your question and post it to DBA.SE, those guys know OLAP way better than me, I've never worked in a real cube but they could probably give you some great insights. Mention what you said to @MichaelT about the relationship structure as well. The important things in identifying whether you need OLAP or OLTP is how often updates happen, and how current you need data. Mention all the info about that stuff in your Q to DBA

willOEM

@JimmyHoffa Will do, thanks!

Jimmy Hoffa

Yep, no problem

Jimmy Hoffa

3:15 PM

@YannisRizos why do you think he was asking on MSO, you guys are professionals, he wants your help :)

Well, most people there are, maybe calling you professional is a bit of a stretch... more like a tequillesionall maybe

willOEM

3:34 PM

@YannisRizos @JimmyHoffa: I've updated my original question. Please let me know if you think that it is still not appropriate for the Q&A format. Thanks for all of the help.

Jimmy Hoffa

@willOEM yeah, that still doesn't really focus on a particular question. You have lots of important questions to ask there

Wait

Maybe I'm looking at the wrong Q..

willOEM

1

I have a web application that I have been developing for a small group within my company over the past few years, using Pipeline Pilot (plus jQuery and Python scripting) for web development and back-end computation, and Oracle 10g for my RDBMS. Users upload experimental genomic data, which is par...

Jim G.

resource requests are not quite welcome at Programmers. As far as I understand, one would rather present an underlying problem instead - a problem that was intended to be solved with particular resource requested — gnat 4 hours ago

Jimmy Hoffa

Ah aye I was looking on SO

willOEM

yeah, that question is outdated, I should remove it.

Jim G.

3:37 PM

@gnat: Why do you say "not quite welcome"? Why not just say "not welcome"? Why do you insert the word "quite"?

Yannis Rizos

@JimmyHoffa And now that you found it, it's already re-opened. Sucks to be you I guess ;P

willOEM

@YannisRizos Thanks!

Jimmy Hoffa

@YannisRizos I've been writing a phone system for the past week now, it doesn't suck to be me at all! My new job is awesome :D

user55340

There are times I want to hunt down the people who wrote the code and really "talk" with them.

user55340

boolean productLookupShowPrices = new Boolean(((PreferenceBean) pricingPrefs.get(PreferenceBean.PRODUCT_LOOKUP_SHOW_PRICES)).getValue()).booleanValue();

user55340

3:48 PM

StringBuffer selectqryStr = new StringBuffer("stuff" + var + "other stuff" + var2 + "more stuff");

Jimmy Hoffa

oof

user55340

StringBuffer drpqryStr = new StringBuffer("DROP table testtmp"+terminalId);

user55340

I mean... why!?!?!?! They almost did it right... well, they did it right, but then they did a wrong around the right.

Yannis Rizos

4:04 PM

Welcome to Stack Overflow. It's hard to tell what you're asking, and it seems likely that this question will be closed. Consider editing your question so that it's easier to understand. — Caleb 48 mins ago

Welcome to Stack Overflow????

Jimmy Hoffa

@YannisRizos was it migrated?

Yannis Rizos

@JimmyHoffa Nope. Easy mistake to make though, or it could be a canned comment.

Jimmy Hoffa

ooo

Yannis Rizos

Edited it. It's silly but it bothered me.

Jimmy Hoffa

I want auto-comments

Yannis Rizos

4:06 PM

Then... install the pro-forma extension?

Jimmy Hoffa

....nahhhh

:)

@MichaelT Euler problem 1 can be done in O(1) ? Do you mean by like hard coding all of the multiples?

Ohh I guess it's probably a math thing starting with 1000 and doing some equation

Yeah? Hmm.. 1000/5 tells you the number of multiples of 5 under a thousand, but not their values...

is it a base conversion?

Chad

4:26 PM

3

Q: How do I take responsibility for my code when colleague makes unnecessary improvements without notice?

One of my teammates is a jack of all trades in our IT shop and I respect his insight. However, sometimes he reviews my code (he's second in command to our team leader, so that's expected) without a heads up. So sometimes he reviews my changes before they complete the end goal and makes changes r...

software-industry communication colleagues

is that OT here?

user55340

@JimmyHoffa you know the Sum(1 .. n) = (n * n + 1)/2... right?

user55340

Function SumDivisibleBy(n)
  p=target div n
  return n*(p*(p+1)) div 2
EndFunction

user55340

Thus, you do it for 3 and 5... and subtract 15 so you don't count those twice.

user55340

Euler #6 can also be done in O(1).

user41796

5:15 PM

@Chad - I would say it's on-topic here; similar but different questions have been asked and received a number of replies. As the question already has an accepted answer, I don't know if it's eligible to be migrated to Programmers. I think a mod would have to weigh in on that aspect. An additional complication is that there are a number of questions and aspects within the main question, so that will complicate creating a single answer.

Jimmy Hoffa

@MichaelT Never knew. Not been in a math class since I was 15

user55340

Take sum(1 .. 10). This can be written as 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10. However, you will note the pairs... (1 + 10) + (2 + 9) + (3 + 8) + (4 + 7) + (5 + 6). This is then 11 * 5. Or (10 + 1) * (10 / 2).

Jimmy Hoffa

5:46 PM

Neat

Chad

@GlenH7 there wasnt wheni posted it... ohh well

Jimmy Hoffa

7:14 PM

Is this idea even remotely correct? I have never dealt with signal processing so it's way out of my realm..

0

A: What is an algorithm to re-sample from a variable rate to a fixed rate?

Somebody whose aware of well-known algorithms/transforms/etc will likely give you a better answer than this; but the naive approach that I could imagine is to simply maintain a buffer previous samples reported by your sensor, and then resolve the fixed rate based on the previous samples and their...

I feel like I'm missing an important piece by not coming up with some predictive correction techniques, but I don't really know any other than taking a large sample and guiding towards the average until a new sample comes in

and that's probably way wrong

user55340

This might be something better suited for dsp.stackexchange.com

Jimmy Hoffa

there is a DSP.SE? cool

how do you guys keep track of all these??

user55340

Watching migrations to/from SO on a tag or two... occasionally hitting the SE site list and scrolling through it. Don't have to really read it, just seeing it can be enough (and then searching when you think there might be one)

Jimmy Hoffa

I should start hanging out on there, I've always wanted to know how to do signal processing. My dream project has for a long time been a distributed concurrent video decoder server

which is likely impossible, and definitely wayy beyond me, but would still be really cool to put together

user55340

There was a video of a guy writing a guitar hero player... it took in the video signal and then sent back commands as if it was a controller.

Jimmy Hoffa

7:24 PM

though the real black art there that keeps me from even looking into it is codec/envelope identification because as a combinatorial problem there's somewhere in the realm of 14 gajillion combinations and they all need special handling

Jimmy Hoffa

7:51 PM

@willOEM I just really don't see your Q fitting here... The stuff you mention about web frameworks etc is a red herring because the code has zero to do with your performance issues, your performance issues are 100% in your DB (or so you've alluded to) and given that, I think DBA.SE is a much better fit and you should detail a lot more about your current state of affairs in the database, which types of queries are slow and mention nothing about the rest of the app

gnat

8:05 PM

@JimG. I use "quite" when I am not quite certain

willOEM

8:16 PM

@JimmyHoffa You're correct that the primary issue here is database performance, but the existing and/or future applications resting on top of it are going to have to be moved from Pipeline Pilot. I'd like my development framework solution to work well with my database solution.

My question on DBA.SE was closed pretty quickly, due to it appearing on Programmers and SO as well. I'll reword and recreate it later.

Jimmy Hoffa

> I'd like my development framework solution to work well with my database solution.

I don't think that's really something you should think about

You want your development framework to work well for the domain and problem scope, you want your database solution to work well for the data scenarios, you don't want to think about them as a whole together because then you're focusing on tightly coupling them

willOEM

@JimmyHoffa I know in general this is true, but in my case (at least currently), I have to live with the limitations of the environment I am developing in. For example, Pipeline Pilot does not support all RDBMS products, nevermind newer NoSQL products.

Jimmy Hoffa

Ah, what is pipeline pilot? I thought you were using Python

Or java or something

willOEM

Pipeline Pilot

Pipeline Pilot is the authoring tool for the Accelrys Enterprise Platform. It is a scientific visual and dataflow programming language, used in various scientific domains, such as cheminformatics and QSAR, Next Generation Sequencing, image analysis, text analytics. History Originally created in 1999 by SciTegic, Pipeline Pilot is now developed by Accelrys. Pipeline Pilot was used at first in the pharmaceutical and biotechnology industries and by academics and government agencies. Then other industries started to adopt it, but always in science driven sectors such as Chemicals, Energ...

Jimmy Hoffa

Ahh

interesting

Throwing that into the equation, you are so on your own. Sounds like every programming-for-scientists scenario I've ever heard of: They require proprietary things nobody's ever heard of, and always want to hack up something new and different every week

willOEM

8:39 PM

@JimmyHoffa And that is really what Pipeline Pilot is great for. If you have no programming experience, you can create tools to handle what are usually complicated computational tasks.

If you do have development skills, you can wrap Java/.NET/Python/JavaScript/whatever code into a single application. But it also lacks a lot of the flexibility and portability of traditional development frameworks.

Jim G.

@gnat So you're not certain if the resource requests are welcome? Then why did you flag two of my answers for deletion?

gnat

8:55 PM

@JimG. I flag answers based on the contents of the answer not the question

user55340

9:07 PM

-1

Q: database auditing

I am creating an intrusion detection system for a database. For this project I have taken one application like an employee database. I need to create a system users form web pages. I have type the system command in .java file like PreparedStatement p2=con.prepareStatement("CONNECT sys/password ...

java database

user55340

Unless the OP does some work on that over at SO, its likely to get closed over there.

Transcript for