Tag Info

Hot answers tagged

46

NoSQL stands for "Not only SQL" and usually means that the database is not a relational database, which have been very popular the last decades. The reason why NoSQL has been so popular the last few years is mainly because, when a relational database grows out of one server, it is no longer that easy to use. In other words, they don't scale out very well ...


28

Yes, that's non-normalized, but occasionally non-normalized designs win out for performance reasons. However, I would probably approach it a little differently, for safety reasons. (Disclaimer: I don't currently, nor have I ever worked in the financial sector. I'm just throwing this out there.) Have a table for posted balances on the cards. This would have ...


26

If I was going to put this into SQL Server, I would suggest a table something like: CREATE TABLE tcp_traffic ( tcp_traffic_id bigint constraint PK_tcp_traffic primary key clustered IDENTITY(1,1) , tcp_flags smallint /* at most 9 bits in TCP, so use SMALLINT */ , src_as int /* Since there are less than 2 billion A.S.'s possible, use INT ...


25

In a company I work for we are dealing with similar amount of data (around 10 TBs of realtime searchable data). We solve this with Cassandra and I would like to mention couple of ideas that will allow you to do O(1) search on a multi TBs database. This is not specific to Cassandra db though, you can use it with other db as well. Theory Shard your data. ...


19

The index design that you put in place is something more of an art than a science. The RDBMS isn't smart enough to take common workloads and design a smart indexing strategy. It is up to human intervention (read: DBA) to analyze workload and determine what is the best approach. If there was no penalty of having indexes then it would be a shotgun approach ...


17

NoSQL is a very broad term and typically is referred to as meaning "Not Only SQL." The term is dropping out of favor in the non-RDBMS community. You'll find that NoSQL database have few common characteristics. They can be roughly divided into a few categories: key/value stores Bigtable inspired databases (based on the Google Bigtable paper) Dynamo ...


17

On the other side, there is an issue that we run into frequently in accounting software. Paraphrased: Do I really need to aggregate ten years of data to find out how much money is in the checking account? The answer of course is no you don't. There are a few approaches here. One is storing the calculated value. I don't recommend this approach ...


14

It's simple. MySQL has a single daemon that runs the database server. Within the server you can create any number of databases - these databases have no direct mapping to users. Oracle has a single database. When you create a user in an Oracle database, it also creates a Schema with the same name as the user that created it. This is equivalent to a ...


13

For SQL Server, you could argue that a commit operation is nothing more than writing LOP_COMMIT_XACT to the log file and releasing locks, which is of course going to be faster than the ROLLBACK of every action your transaction performed since BEGIN TRAN. If you are considering every action of a transaction, not just the commit, I'd still argue your ...


12

Some databases do already (kind of) create indexes automatically. In SQL Server the execution plan can sometimes include an Index Spool operator where the RDBMS dynamically creates an indexed copy of the data. However this spool is not a persistent part of the database kept in synch with the source data and it cannot be shared between query executions, ...


11

It is mostly a license issue. These developments end up patching the code quite heavily, so if you were to deal with MySQL, you'd either have to open-source your code or be at the mercy of MySQL's corporate owner for the life of your business. Some offers for MySQL get around that by implementing their work as a storage engine, but that doesn't offer all ...


11

For Oracle, rollback can take many times longer than the time it took to make the changes that are rolling back. This often does not matter because No locks are held while the transaction is rolling back It is handled by a low priority background process For SQL Server I'm not sure if the situation is the same but someone else will say if it isn't... As ...


10

I don't see how anyone could make such a statement without having some actual facts to back it up. If your queries are CPU bound, then you should look to find ways to reduce that bottleneck. It sounds as if your boss feels that a denormalized database will perform best, but I don't know enough about your application to say if that is right or not. What will ...


10

NoSQL is a kind of database that doesn't have a fixed schema like a traditional RDBMS does. With the NoSQL databases the schema is defined by the developer at run time. They don't write normal SQL statements against the database, but instead use an API to get the data that they need. The NoSQL databases can usually scale across different physical servers ...


10

Think of an index as "table of contents"... that is an ordered list of pointers to positions in a file, aka offsets. Say that you have millions of records stored in a table, rather than search the table for matching criteria, it's much faster to reference an ordered list for matches, then stack the pointers to the specific matching rows. A perfect example of ...


9

One point that a lot of people seem to miss is that a DBMS will often (or can only) only use one index per table reference in a query, and if it can and does use multiple indexes it would probably be faster to use a combined index if present. For instance, if searching a large table for rows WHERE AnIntegerColumn = 42 AND AnOtherInt = 69 the fastest route ...


9

The Quick Answer - Yes. Happens all the time. There are plenty of good solutions. What solutions are already in your environment? I am helping one client that takes their web site/session activity information from their web application, they write it to xml then deserialize that xml into Hadoop. They then use Hive on top of Hadoop to create aggregations and ...


9

I can't speak to advantages/disadvantages vis-a-vis MySQL, but the PostGIS code is pretty widely regarded as one of the best (in terms of speed/functionality) and most mature (in terms of testing/real-world exposure) available. By way of example, there was a talk at PGEast 2010 by some folks from the FAA on their converting their airport database (used by ...


9

You should never assume that a data point which is outside of the control of your system will never change. This means you shouldn't assume student names won't change. There are lots of reasons in the real world why names might change. Anything that is at reasonable risk of changing is a bad candidate for a primary key. Also, names are very unlikely to ...


8

I can see two reasons: 1) historically, PostgreSQL had better query planner and statistics analyzer. This might be not true now, but few years ago PostgreSQL was much better then MySQL on complex queries, which is OLAP ones. 2) PostgreSQL have better functions/triggers/etc programming support.


8

Rollback isn't just "oh, never mind" - in a lot of cases it really does have to undo what it had already done. There is no rule that the rollback operation will always be slower or always be faster than the original operation. If you are waiting I suggest it is safest to just keep waiting.


8

In fact, there are some databases that do this. For example, Google's BigTable and Amazon's SimpleDB automatically create indices (though neither are RDBMS's). There is also at least one MySQL RDBMS engine that does this. SQL Server also keeps track of indices it thinks you should create, though it doesn't go so far as actually creating them. The problem ...


7

Not all transactions will have their commit activity perform much better than their rollback. One such case is the delete operation in SQL. When a transaction deletes rows, these rows are marked as ghost records. Once a commit is issued and a ghost record cleanup task starts, then only are these records 'deleted'. If a rollback was issued instead, it just ...


7

To answer your question, yes normalization is needed. Common sense is a relative term and is open to interpretation. RDBMS's have been around since the 1970's. Normalization has been put into use on countless projects over the past 30 years, much to the benefit of the applications being developed. The most complex time-consuming problems I have worked ...


7

For performance reasons, in most cases we must store current balance - otherwise calculating it on the fly may eventually become prohibitively slow. We do store precalculated running totals in our system. To guarantee that numbers are always correct, we use constraints. The following solution has been copied from my blog. It describes an inventory, which is ...


6

Basically dispensing with the relational setup, with primary and foreign keys, and with the additional overhead involved in keeping transactional safety, often gives you extreme increases in performance. However this is not unique to the new databases/datastores, as eg MySQL has been tuned to perform at "NoSQL levels" by bypassing layers. In short, you can ...


6

This could depend a great deal on the storage engine. For MyISAM, I think it would be a great idea because you can make data as contiguous as a you want. That would benefit queries involving bulk operations and large range scans. The toolset for compressing and repairing MyISAM to improve the table format can also make access a little better. IMHO ...


6

Every rule, every process, every pattern that is taught in programming courses is an effort to try to "institutionalize" common sense. If all of your developers have perfect common sense at all times and are clear-headed and insightful, then you don't need to follow anybody's rules, processes or patterns. However, as the saying goes: "Common sense ain't" - ...


6

My recommendation is to start out with Postgres. The advantage is that it has a full feature set and it teaches you good habits from the start. Once you are familiar with it, it is easier to move to up to the Oracle/DB2 approach or down to the MySQL approach easily enough. On the other hand it is an advanced database and you can take it extremely far if ...


6

You have the basics right. There is only one type of commit (no normal, fast...). from the concepts doc: When a transaction commits, the following actions occur: A system change number (SCN) is generated for the COMMIT. The internal transaction table for the associated undo tablespace records that the transaction has committed. The ...



Only top voted, non community-wiki answers of a minimum length are eligible