A message from our CEO about the future of Stack Overflow and Stack Exchange. Read now.

Hot answers tagged

49

I'm basing most of this on just trying to get the "right" answer, so you may discover there are some performance issues. No point in speeding up an incorrect query. Understand the table relationships - Most will be one to many. Know the "many" table. Identify the fields required for your joins. Think about LEFT join scenarios - Select all the employees and ...


28

Indentation would be the first thing to do, if you're not doing it already. Not only it's useful with even simple queries, but it is crucial when it comes to joins and queries a bit more complex than a select top 1 [ColumnName] from [TableName]. Once indented properly, nothing forbids to add comments inside the query itself, when appropriate. Don't overuse ...


17

I hesitate to call ElasticSearch a database. It is not a replacement for a database, but it makes a good addition to add functionality, specifically advanced text searching, along side your existing database. I see where you can get them confused. They can actually fit the same need, but not always. ElasticSearch does exactly what it sounds like, searches. ...


9

A bit of a shot in the dark here, but if you are writing lots of temporary views perhaps you haven't realized yet that most places you could put a table in an SQL statement, that table can be replaced by a query. So, rather than joining table A to temporary view B, you can join table A to the query that you have been using as temporary view B. For ...


8

First, a bit about the lingo. Insert, update are not queries. A query in RDBMS is strictly a SELECT (Or a a sub-statement having WHERE clause). The set of verbs: SELECT, INSERT, UPDATE, DELETE are action statements in Data Manipulation Language (DML). Most answers so far revlove around how to achieve your objective, but your question is: Should I have a ...


8

Selecting means choosing some records from a table and leaving others out. Projecting means choosing some columns from each record and leaving others out. Therefore, your query performs selection (records with name='zeus' are chosen, but others are rejected) but not projection (those records that are chosen are returned with all of their columns). Broadly,...


8

There's a few things to consider here, which I'll try to explain in enough detail to help you understand the problem space. Personally, for RDMS', I prefer using stored procedures, reasons for which I'll go into in a moment. In any discrete application layer, there is a need to ensure that the boundary of that layer has sufficient controls in place to ...


7

Instead of temporary views, use the WITH clause. This makes it much easier to break down large queries into more readable smaller parts.


6

Python lambda expressions are real, formal untyped λ-calculus lambda expressions. They fit the formal definition; they can only represent one python expression, based on variables (free or otherwise) and references to other functions (abstract symbols). Python uses parenthesis in expressions too. You use them wherever a lambda is more suitable and ...


6

If you're a programmer, you'll notice that SQL doesn't look much like normal code. Normal code is written in what's known as an imperative style: it tells the computer what to do. SQL, on the other hand, doesn't tell the database what to do; it tells the database what you want instead, and leaves it up to the DBMS engine to figure out what to do to ...


6

In larger organizations it can sometimes make sense to establish different access levels to different parts of a database for different groups of people. That's why most relational databases usually provide tools for access rights management. For example, if you want to restrict getting the credentials in a user table, you could let a trusted person add a ...


5

tl;dr I think you have it exactly backwards. A knowledgeable developer constructs his queries in the order you mention there because that is the order of dependencies - You don't know what relations you need to follow until you know what entities you need, you don't know what restrictions are necessary without first identifying your relations, and you can'...


5

Yes, there is a cleaner way, it is called temporal databases, in particular "transaction-time". The idea is to use an interval value [trans_start, trans_end[to denote, when a row was stored or changed in the database (trans_start) and how long it stayed this way (trans_end). Using a now between trans_start and trans_end and now <> trans_end condition, ...


5

The important thing is to have an abstraction layer between the raw SQL and the business logic. In the old days this would be a stored proc. All the apps would connect directly to the db and call the sproc rather than running random SQL. When the database changed, you could update the sproc and the apps would carry on working. When you had new sql to run ...


4

.NET already does this; it's called Linq. Linq is basically SQL for object collections. In C#, it looks like this: var q = customers. where(c => c.City == "Montreal"). select(c => c.CompanyName); and in Smalltalk, it looks like this: q := customers where: [ :c | c city = 'Montreal' ] select: [ :c | c companyName ]. or this: ((...


4

Is all permutations of a string a good way to index full text search? Uhh, no. That is a very, very bad idea. What happens when someone puts a moderately long string into your database? Say... like your question. Making a database of stack exchange questions seems like a normal sort of thing to do. At time of writing, just the content of the question is ...


3

In this particular scenario, I would consider using a join on the Passenger table and a where clause to filter the results. Something like the below: SELECT pid FROM Reservation INNER JOIN Passenger ON Passenger.pid = Reservation.pid WHERE class = 'AC' and age > 65 For me this is a clearer version and it is easier to understand what the query is ...


3

Best practice is not to write such a lib at all. Use an OR mapper like MS entity framework or any of the available lightweight micro-ORMs (for example, see here https://stackoverflow.com/questions/5829891/which-micro-orm-to-use)


3

Become more familiar with set theory if you aren't already. SQL is based in set theory and understanding more about sets will help you become more familiar with how SQL works. Practice more SQl, if you are just learning SQL its going to take time to understand how to do everything, somethings just take time before you really understand them, Joins are a ...


3

I am not familiar with Redis, however, some other key/value store databases have the following problems: Updates are not instantly visible immediately. SQL joins are to be performed by application. Some SQL features such as Distinct, Group By are to be performed by application. No stored procedures, triggers, etc. No FKs (2) and (3) above are particulary ...


3

Both of these database has their specific need to solve specific problem at certain level of application requirement. Although we have not used Graph Database. But we are using elasticsearch with MySQL in one of our project from last 5 years. That project has a massive data to be searched through 6m documents and has massive relationships between those ...


3

I'd try something like symbolic computing where we run the computation over an abstraction of all possible starting states, the goal of this symbolic execution is to produce an abstraction of the ending state, which is then reflective of the computations performed by the set of operations to be optimized. (Because we use an abstraction of all possible ...


3

It very much depends on the data. However what you really really want to avoid is getting a number of rows from one query and then for each row firing of a second query for each row returned. In this situation a single join query will perform an order of magnitude faster than several individual queries.


3

Mason Wheeler's answer is very good. There are a few more points to add: When the database engine first executes a query, it doesn't just come up with one execution plan. It comes up with a lot of them. Barring bugs, all of these execution plans are logically equivalent: that is to say, they will all generate correct results for the query. However, ...


3

... I get date from input date type html tag I'll wager you don't. Html tags tend to return String values. You have to parse (and, in this case, format) that [String] value before using it in SQL. Why Parse? Because the Html Control returns a String and you need a Date. Why Format? Because that Date value needs to be represented in the format of ...


3

I typically have a service that is dedicated to serve the needs of each frontend app. This service does any backend lifting on behalf of the frontend. This has a couple of pros: Format data for easiest consumption by the UI. Different UI's will have different requirements. For example if I have an Android app, I might choose the frontend service for this ...


3

If you want shared ownership of the objects, referencing them from both the vector and another collection of pairs, then you probably want something like a std::vector<std::shared_ptr<T>>. This way the vector can move/reorder the pointers however much it wants, and the actual pointed-to objects will still be in the same place, and copies of the ...


3

No, it's not bad to have private classes in repositories. I guess the confusion would be around why you need a private class. Rather than another option. Use an existing public class. Your Repository is a conceptual storage for public classes, If I need to ask a question about the state of that data, can I not use the public class it maps to? Don't map to ...


2

With torpedoquery your query could look like this public List<User> findUsers() { User from = from(User.class); City city = innerJoin(from.getCity()); with(city.getCode()).in("one", "two").or(city.getCode()).notIn("three", "four"); District district = innerJoin(city.getDistrict()); with(district.getCode()).notIn("exclude1", "...


2

As others have suggested, you can go with ORMs (unless you feel that ORMs are an antipattern). If for whatever reason you can't go with an existing ORM, you'll want to look up some DAO (Data Access Object) patterns to see if what fits. Another option would be to have the actual DML/SQL in a stored procedure and call the procedure by name. This would be more ...


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