← All posts tagged SQL

netneladno

как вы думаете, эти два запроса одинаковые?

SELECT * FROM register_uz_accounting_entities
WHERE effective_on > '2014-07-11' OR (effective_on = '2014-07-11' AND
id > 1459)
ORDER BY effective_on, id
LIMIT 100

A more readily optimizable query is
SELECT * FROM register_uz_accounting_entities
WHERE (effective_on, id) > ('2014-07-11'::date, 1459)
ORDER BY effective_on, id
LIMIT 100

netneladno

еще немного про новую опердень от клаудеры
As of the initial Impala release(s):
• Impala will run against a variety of storage managers, choices among which will have different performance implications. HDFS (Hadoop Distributed File System) and HBase will both be supported. Multiple HDFS formats will be supported, both row-based and columnar. (See the Trevni comments in my first Impala post.)
• In the simplest of scanning scenarios, Impala can read row-based data at near the theoretically optimum speed, while Hive runs at 1/3 of that.
• Initially, all Impala joins will be (distributed) hash joins. These seem to start at 10X Hive’s performance and go up from there.
• The fastest Impala queries take > 1 second.
• One test showed Impala surviving a load of 100 concurrent queries. Another test showed Impala running 10 cloned copies of a query with 25%ish performance degradation.
• Impala will have Microstrategy support on Day 1, so it obviously can handle fairly complex SQL. (Also Pentaho, Tableau, and QlikView.)
• Column statistics and the like are under active development, which will help in query optimization. A true cost-based optimizer is, of course, further off.

netneladno
SQL

есть у нас табличка в биллинге, на которую ребята не повесили ни юник индексов, ни нот_нулл, которые описывают бизнес логику

вобщем там внезапно накопилось 17кк записей, абсолютно нелепых. запросики стали тормозить, почему собственно и заметил табличку
почистил от говна, повесил пару чеков — 7048 записей

netneladno
SQL

SEQUEL was introduced in 1974 as "a query facility oriented toward users who are not computer specialists."

"[T]here is also a large class of users who, while they are not computer specialists, would be willing to learn to interact with a computer in a reasonably high-level, non-procedural query language. Examples of such users are accountants, engineers, architects, and urban planners. It is for this class of users that SEQUEL is intended."

D.Chamberlin, R.F. Boyce, SEQUEL: A Structured English Query Language. Proc. ACM SIGFIDET Conference, Ann Arbor, MI, May 1974 (Page 258)

netneladno
SQL

Про нормализацию
In math, two terms are used as synonyms, orthogonal and normal. Remember from geometry class that a 90 degree line is the normal line? It's also orthogonal. Orthogonal as a concept means independence of influence (just as the X axis is independent of the Y axis; there some math), and Codd uses that term liberally in his paper.

So, the normal forms have nothing to do with not insane or seeking standards, but with data independence. Which is normal.