Tag Info

Hot answers tagged

51

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 ...


32

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. ...


31

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 ...


29

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 ...


23

This is a really tough question to answer. You can take the fastest database platform in the world, design a horrible schema, write a crappy application around it, and then be tempted to blame the database platform. At the same time, you could take the free SQL Server Express and, with the right design, application logic, and an approach at sensible ...


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 ...


18

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 ...


17

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 ...


13

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, ...


12

Let me put it this way, remember GameSpy Arcade from the early/mid 2000s? It ran thousands of games, and was all running on SQL Server and supported tens of thousands of users at once (yes we had several SQL Servers in there doing various things). It's all about the design of the database and how you use the system. Do so correctly and your project will ...


11

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 ...


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 ...


11

The right answer depends a lot on the platform you are programming for. It just so happens that someone asked this question for a specific platform in StackOverflow about 1.5 years ago. Whether it is MySQL, SQL Server, Oracle, PostgreSQL or some other RDBMS, you have to be very creative with the database infrastructure. If you have an open checkbook for ...


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 ...


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

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

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 ...


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 ...


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

Some thoughts.... Typically one does not want to store pieces of tightly interrelated information in different systems. The chances of things getting out of sync is significant and now instead of one problem on your hands you have two. One thing you can do with Mongo though is use it to pipeline your data in or data out. My preference is to keep ...


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

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

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

If you really want to limit SQL Server memory, look at the Maximum server memory option. Getting memory usage is possible, but it depends on what you really want. Do you want to see memory usage as a percent of the "Maximum server memory" option? If so, look at sys.dm_os_process_memory: select convert(float, memory_utilization_percentage) / 100 as ...


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 ...



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