Is your database scaly enough to scale?
-
My database is a large room full of small lizards trained to remember bits of data and press coloured buttons to communicate it on command.
-
I found some lizards, grabbed their jaws, and put them on like earrings.
-
My database is a large room full of small lizards trained to remember bits of data and press coloured buttons to communicate it on command.
LizardDB is so last year. You want to upgrade to MonkeyDB. The monkeys encode the data in the exact pattern of shit they throw on the walls. It's the future, man
-
What are you trying to achieve with that?
What value does timeStr have?
-
Con: If you're doing relational operations, you're going to reimplement a less efficient version of the same thing a proper RDBMS does.
DBMS' are components not entirely unlike libraries. They were implemented so you don't have to do it.
Yes, you can implement your own. But it won't be as good and it'll cost more. Any school of thought that promotes reinventing the wheel for ideological "process your data yourself" purposes is a stupid school of thought.
-
for ideological "process your data yourself"
nope. It's ideological "separation of concerns", ideological "DRY principle" (especially with data validation, lol), ideological "we don't understand SQL".That said, I have yet to find a language that embeds SQL in a nice way, what I had to roll often was dynamic SQL construction...
-
Okay, so open a SQL console and run this:
EXPLAIN SELECT DateAdd(Day, DateDiff(Day, 0, timeStr), 0) As MyDate, FROM [database].[dbo].[tableView] WHERE time >= @startGMT AND time < @endGMT GROUP BY DateAdd(Day, DateDiff(Day, 0, timeStr), 0) ORDER BY DateAdd(Day, DateDiff(Day, 0, timeStr), 0) ;
-
I've seen developers freeze in shock when shown window functions and WITH statements, replacing huge chunks of code that did it on application side.
... and sometimes the requirements are for the UI to display a consistent set of data and that the end of page 6 has to have the record just before the record at the beginning of page 7, all while the data is being modified by other processes.
A search engine doesn't need ultra-consistent paging, but a lot of business apps do. Server-side cursors are often worse than client-side cursors. Usually a client-side key-only cursor is a good compromise, in which case the window functions get replaced by an IN operator.
-
That said, I have yet to find a language that embeds SQL in a nice way, what I had to roll often was dynamic SQL construction...
jOOQ: The easiest way to write SQL in Java
jOOQ, a fluent API for typesafe SQL query construction and execution.
db.select().from(AUTHOR).fetch();
-
LizardDB is so last year. You want to upgrade to MonkeyDB. The monkeys encode the data in the exact pattern of shit they throw on the walls. It's the future, man
Sure sure, as long as you don't need to scale. Monkeys are not scaly at all.
-
Linq
-
It's time as a string.
This is a customer created query that our database is not optimized for, I was just looking for ways to make their query faster without changes to our database.
It appears that (other than a faster way to get date out of the string) I can't make this any better.
-
-
Also, quite skully at times:
Filed under: Let's see how many of you catch that one, Reverse GIS is cheating!
-
[spoiler]Skullmonkeys the game is a spiritual successor, and in some ways sequal, to neverhood.[/spoiler]
-
Damn it, did you have to give it away that fast?
-
-
I was going to say Trapdoor. Looks like the kind of monster that would appear on it
-
Dude, I walked that whole hallway in the first, and read all of it.
All.... of.... it.
That was ridiculous.
-
Con: If you're doing relational operations, you're going to reimplement a less efficient version of the same thing a proper RDBMS does.
DBMS' are components not entirely unlike libraries. They were implemented so you don't have to do it.
Yes, you can implement your own. But it won't be as good and it'll cost more. Any school of thought that promotes reinventing the wheel for ideological "process your data yourself" purposes is a stupid school of thought.
Agreed, for the most part; however, there are data processing operations you are better off doing yourself. Oracle sucks at string parsing! (30mins in SQL, 30s in C++ code, and that's just taking apart a comma-separated-values string and making a bunch of rows from it.)
nope. It's ideological "separation of concerns", ideological "DRY principle" (especially with data validation, lol), ideological "we don't understand SQL".
Point by point:- Data storage and retrieval is what databases are built for -- Postgres/Oracle/SQLServer/MariaDB/DB2 all have had far more time and effort put into how they handle queries than whatever you did to hand-roll a JOIN.
- You don't need to have a validating database schema -- you can have your server-side code validate instead even if you're otherwise using the DB for all it's worth, or split validation between the two layers so that one layer handles business-logic validation, while the other handles datatype validation. None of these decisions actually violates DRY, because nothing is being validated twice based on the same criteria.
- Meh -- once you wrap your head around sets, relational algebra is easy ;) (most of my SQL difficulties are caused by Orrible's atrocious error messages)
That said, I have yet to find a language that embeds SQL in a nice way, what I had to roll often was dynamic SQL construction...
Checked out SQLAlchemy lately?
-
It's time as a string.
This is a customer created query that our database is not optimized for, I was just looking for ways to make their query faster without changes to our database.
Make a function based indexes (Oracle) or an index on a computed column (MS SQL). Other database engines have equivalents too.
-
-
Checked out SQLAlchemy lately?
I have complicated relationship with ORMs. Impedance mismatch, I guess.
-
Not knowing the specifics I'd say this is the best way too.
-
I have complicated relationship with ORMs. Impedance mismatch, I guess.
Read the manual -- SQLAlchemy's not just an ORM...
-
But it's also an ORM. And that makes my relationship with it a bit complicated.
-
But it's also an ORM. And that makes my relationship with it a bit complicated.
Look more closely -- the ORM part is completely optional ;) (I haven't used the SQLAlchemy ORM, actually, just the database connection management and SQL-building parts of it)
-
I walked that whole hallway in the first, and read all of it.
That was a lot ... a lot of text.
-
Then I found a site where someone had copied it out of the game into text.
-
-
No,
I read it without cheating.
It took me the better part of a day, split between 3 nights.But when I found the site.
-
I fondly remember walking through that hallway countless time
-
Monkeys are great at scaling stuff.
Lizards are scaly. Monkeys are furry. Fur =/= scales.
-
Lizards are scaly. Monkeys are furry. Fur =/= scales.
I'm with @xaade on this one: monkeys are great scalers.
-
Did you tare† your database before using as a scale?
>†.**tare**6_(v)_ – to ascertain, note, or allow for the tare of.
-
I'm with @xaade on this one: monkeys are great scalers.
And they can also be great vectors.
-
Unapproved hatted snake!
There is an approved hatted snake in the Mad Hatter Hijinx thread.
Nanny states weren't enough? Now we have Nanny Hatters?
-
Nanny states weren't enough? Now we have Nanny Hatters?
No, that was just a PHA. If I were a Nanny Hatter, I would have found a way to enforce the PHA.
-
Nanny Hatters?
Mary Poppins was noted for being hatted much of the time.
-
Quick question to the RDBMS experts: how can you horizontally scale without loosing your mind? Or is the only way to effectively scale a RDBMS is by beefing up the hardware? I've seen the pain of Oracle & MySQL clusters and it's insane and error prone. Not something for the faint.
I know how to put several Cassandra and REDIS instances in commodity hardware without going batshit crazy in a few minutes.
-
Quick question to the RDBMS experts: how can you horizontally scale without loosing your mind?
What sort of mix of load are you talking about? Scaling a query-heavy load is much easier than scaling an update-heavy load. In the limit (pure query load) you can just replicate simply and use a load-balancer.
-
Mary Poppins was noted for being hatted much of the time.
obviously the hat is the source of her powers
-
Quick question to the RDBMS experts: how can you horizontally scale without loosing your mind?
There is no "standard answer". You usually end up doing some kind of partitioning. You must understand both the data and usage patterns to have a hope of doing it right.
-
This kind of thing is why I'm not a DBA.
-
By the time you have enough business to need to, you can afford a guy who knows how.
-
obviously the hat is the source of her powers
Don't you know anything? She was a Time Lady.
