BestHub
Sign in
Tagged articles

Query Optimization

433 articles · Page 5 of 5
dbaplus Community
dbaplus Community
Feb 26, 2017 · Databases

Uncovering Hidden Pitfalls of Oracle's Cost‑Based Optimizer and How to Fix Them

This article walks through common traps in Oracle's Cost‑Based Optimizer (CBO), explains why query transformations, FILTER operations, and inaccurate statistics can cripple performance, and provides practical solutions—including NULL‑AWARE anti‑joins, hint usage, SQLT tooling, and SQL rewrites—to dramatically speed up problematic queries.

CBOOracleQuery Optimization
0 likes · 33 min read
Uncovering Hidden Pitfalls of Oracle's Cost‑Based Optimizer and How to Fix Them
ITPUB
ITPUB
Feb 13, 2017 · Databases

Mastering SQL: Joins, Normal Forms, Indexes, and Optimization Techniques

This article explains SQL join types, the three normal forms, table creation and alteration commands, data integrity constraints, practical query‑optimization tips, the roles and differences of clustered and non‑clustered indexes, and the distinctions between stored procedures and functions.

JoinsQuery OptimizationSQL
0 likes · 7 min read
Mastering SQL: Joins, Normal Forms, Indexes, and Optimization Techniques
ITPUB
ITPUB
Jan 18, 2017 · Databases

Essential Database Design and Query Optimization Tips for Faster SQL Performance

This article provides practical guidelines for designing efficient database schemas and optimizing SQL queries, covering row size limits, data type choices, index usage, common pitfalls in WHERE clauses, and best practices for temporary tables and transaction handling to improve overall database performance.

Performance TuningQuery OptimizationSQL
0 likes · 11 min read
Essential Database Design and Query Optimization Tips for Faster SQL Performance
ITPUB
ITPUB
Jan 10, 2017 · Databases

5 Practical SQL Query Tricks to Boost Database Readability and Performance

This article presents five useful Oracle SQL techniques—including string concatenation, DISTINCT, WHERE clauses, flexible COUNT usage, and selecting only necessary columns—to improve query readability, eliminate duplicate rows, and enhance overall database performance.

DatabaseOracleQuery Optimization
0 likes · 9 min read
5 Practical SQL Query Tricks to Boost Database Readability and Performance
Hulu Beijing
Hulu Beijing
Dec 20, 2016 · Big Data

How Hulu Supercharges OLAP Queries with CarbonData: Real‑World Optimizations

This article describes Hulu’s real‑world OLAP query optimization, covering the fundamentals of OLAP, comparisons of row‑ and column‑based storage formats, detailed indexing mechanisms of Parquet, ORC and CarbonData, and the specific schema, shuffle, block size, speculation and GC tuning techniques that enabled CarbonData to dramatically accelerate wide‑table queries on SparkSQL.

Big DataCarbonDataOLAP
0 likes · 17 min read
How Hulu Supercharges OLAP Queries with CarbonData: Real‑World Optimizations
Practical DevOps Architecture
Practical DevOps Architecture
Dec 8, 2016 · Databases

SQL Query Optimization Tips and Best Practices

This article presents a comprehensive collection of practical SQL performance tuning guidelines, covering index usage, query rewriting, avoiding costly operators, proper data types, efficient joins, and view creation to help developers write faster, more resource‑efficient database queries.

Query OptimizationSQLdatabase tuning
0 likes · 11 min read
SQL Query Optimization Tips and Best Practices
dbaplus Community
dbaplus Community
Nov 15, 2016 · Databases

How I Cut a 4‑Minute Oracle Query to 3.5 Seconds: A Step‑by‑Step SQL Optimization Tale

After a typhoon delayed work, the author tackled a 347‑line Oracle SQL that took over four minutes to return results, dissected its massive subqueries and views, applied three rounds of restructuring with WITH clauses and driver‑table tactics, and ultimately reduced the total runtime to just 3.5 seconds while dramatically speeding up deep data extraction.

Driver TableOraclePerformance Tuning
0 likes · 12 min read
How I Cut a 4‑Minute Oracle Query to 3.5 Seconds: A Step‑by‑Step SQL Optimization Tale
ITPUB
ITPUB
Nov 14, 2016 · Databases

Optimizing Large‑Scale Oracle Queries with Temporary Tables: Strategies and Trade‑offs

This article examines a high‑volume Oracle query scenario, critiques the current multi‑IN approach, and presents three alternative solutions—including a single‑SQL join, a UNION ALL view, and the use of global temporary tables—detailing their performance implications and practical trade‑offs.

Database PerformanceIndex ScanOracle
0 likes · 10 min read
Optimizing Large‑Scale Oracle Queries with Temporary Tables: Strategies and Trade‑offs
dbaplus Community
dbaplus Community
Nov 9, 2016 · Databases

Mastering SQL Server Timeout Errors: Diagnosis and Tuning Guide

This article explains the various timeout scenarios in SQL Server‑backed web applications—including ASP.NET, IIS, WebService, SSMS, remote login, and linked‑server timeouts—provides diagnostic queries such as the Connectivity Ring Buffer, and offers concrete tuning steps and best‑practice recommendations to resolve and prevent them.

Linked ServerQuery OptimizationSQL Server
0 likes · 16 min read
Mastering SQL Server Timeout Errors: Diagnosis and Tuning Guide
MaGe Linux Operations
MaGe Linux Operations
Oct 20, 2016 · Databases

Boost Your SQL SELECT Performance: Proven Checklist & Practical Tips

This guide presents a concise, experience‑based checklist for optimizing SQL SELECT statements, covering index verification, dataset reduction, column selection, unnecessary table removal, outer join elimination, and computed column cleanup, each illustrated with real‑world examples and visual aids.

Query OptimizationSQLdatabases
0 likes · 6 min read
Boost Your SQL SELECT Performance: Proven Checklist & Practical Tips
ITPUB
ITPUB
Oct 19, 2016 · Databases

Why InnoDB Returns Rows in Primary‑Key Order When Scanning Without Indexes

This article demonstrates how InnoDB returns rows in primary‑key order during full‑table scans, while queries that use a secondary index follow the leaf‑node order of that index, and it compares performance implications of index‑only scans versus clustered‑index scans.

DatabaseIndexInnoDB
0 likes · 12 min read
Why InnoDB Returns Rows in Primary‑Key Order When Scanning Without Indexes
Java Backend Technology
Java Backend Technology
Oct 15, 2016 · Databases

Boost Query Speed in Million-Row Databases: Proven Optimization Techniques

This article presents a comprehensive set of practical strategies for improving database query performance, covering index design, SQL statement refinements, Java backend considerations, hardware tuning, and storage‑procedure usage to accelerate operations on large‑scale relational databases.

IndexingPerformance TuningQuery Optimization
0 likes · 13 min read
Boost Query Speed in Million-Row Databases: Proven Optimization Techniques
ITPUB
ITPUB
Sep 13, 2016 · Backend Development

Auto‑Detect Useful Column Groups in Oracle 12c to Improve Query Plans

This guide shows how to use Oracle 12c's DBMS_STATS.SEED_COL_USAGE and REPORT_COL_USAGE to automatically identify useful column groups, create extended statistics, and verify the impact on execution plans with concrete SQL examples.

DBMS_STATSExtended StatsOracle
0 likes · 8 min read
Auto‑Detect Useful Column Groups in Oracle 12c to Improve Query Plans
ITPUB
ITPUB
Jul 29, 2016 · Databases

30 Essential SQL Query Optimization Tips to Avoid Full Table Scans

This guide presents thirty practical SQL optimization techniques—ranging from index usage and avoiding costly operators to rewriting expressions and minimizing temp objects—to help developers prevent full table scans and boost database performance.

Query OptimizationSQLindexes
0 likes · 11 min read
30 Essential SQL Query Optimization Tips to Avoid Full Table Scans
dbaplus Community
dbaplus Community
May 25, 2016 · Databases

How Parallel Execution Supercharges SQL Server Queries—and the Pitfalls to Avoid

This article explains the theory behind SQL Server's parallel execution, illustrates its performance gains with Amdahl's Law, lists operators that block parallelism, discusses configuration settings, warns of deadlocks and thread starvation, and presents practical MapReduce‑style optimizations for real‑world workloads.

Amdahl's lawDeadlockMapReduce
0 likes · 16 min read
How Parallel Execution Supercharges SQL Server Queries—and the Pitfalls to Avoid
ITPUB
ITPUB
May 5, 2016 · Databases

Top 50 SQL Server Query Optimization Tips to Boost Performance

This comprehensive guide enumerates over forty common causes of slow SQL Server queries and provides detailed optimization techniques—including indexing strategies, hardware upgrades, partitioning, cursor handling, and configuration tweaks—to dramatically improve query performance and reduce resource bottlenecks.

DatabasePerformance TuningQuery Optimization
0 likes · 21 min read
Top 50 SQL Server Query Optimization Tips to Boost Performance
Qunar Tech Salon
Qunar Tech Salon
Apr 30, 2016 · Big Data

Designing and Optimizing Log Storage and Query in HBase

This article analyzes the characteristics of log data, explains why HBase is chosen for log storage, discusses the shortcomings of self‑built indexes, and presents optimization strategies such as rowKey design, filter usage, coprocessor integration, and third‑party indexing to improve query performance.

HBaseIndexingQuery Optimization
0 likes · 12 min read
Designing and Optimizing Log Storage and Query in HBase
ITPUB
ITPUB
Dec 29, 2015 · Big Data

How SparkSQL Executes Queries Faster Than Hive: A Deep Dive

This article explains SparkSQL's query processing pipeline—from parsing and logical planning through optimization and physical execution—highlighting why it often outperforms Hive on MapReduce by reducing I/O, minimizing shuffle stages, and reusing JVMs.

Big DataDistributed ComputingHive
0 likes · 13 min read
How SparkSQL Executes Queries Faster Than Hive: A Deep Dive
ITPUB
ITPUB
Dec 21, 2015 · Databases

Master MySQL Indexes: BTREE, Hash, and Query Optimization Secrets

This article explains MySQL's indexing mechanisms—including BTREE and hash structures, page layout, index types, and practical query patterns—while showing how to interpret EXPLAIN output and avoid common pitfalls for efficient database performance.

BTreeDatabaseHash Index
0 likes · 19 min read
Master MySQL Indexes: BTREE, Hash, and Query Optimization Secrets
dbaplus Community
dbaplus Community
Nov 18, 2015 · Databases

Demystifying DB2 Optimizer: How Cost Models Shape Query Performance

This article explains the inner workings of the DB2 optimizer, its four-step processing flow, cost‑based decision making, and detailed examples comparing full‑table and index scans, followed by practical tuning tips and a Q&A session for real‑world query optimization.

Cost ModelDB2Database Performance
0 likes · 19 min read
Demystifying DB2 Optimizer: How Cost Models Shape Query Performance
Efficient Ops
Efficient Ops
Aug 5, 2015 · Databases

Mastering MongoDB Explain Plans: Optimize Queries with Index Strategies

This article explains MongoDB's three explain modes, details the fields returned by queryPlanner and executionStats, shows how IndexFilters influence the optimizer, and walks through practical examples that demonstrate how proper indexing eliminates collection scans and in‑memory sorts for faster query performance.

EXPLAINIndexMongoDB
0 likes · 15 min read
Mastering MongoDB Explain Plans: Optimize Queries with Index Strategies
Qunar Tech Salon
Qunar Tech Salon
May 3, 2015 · Databases

Optimizing PostgreSQL Queries for Flight Data Retrieval at Qunar

The article analyzes a slow PostgreSQL query that joins multiple tables to fetch flight numbers, compares several alternative query formulations with their execution plans, and presents a final solution using array aggregation to achieve sub‑millisecond performance despite existing indexes.

Database PerformancePostgreSQLQuery Optimization
0 likes · 7 min read
Optimizing PostgreSQL Queries for Flight Data Retrieval at Qunar
MaGe Linux Operations
MaGe Linux Operations
Aug 8, 2014 · Databases

Master MongoDB Indexes: Query Plans, Creation, and Optimization Tips

This guide walks through MongoDB index fundamentals, showing how to view query plans, create single and compound indexes, manage index types like unique, sparse, and multikey, force specific indexes or full scans, and handle in‑memory sorting and index management for optimal performance.

DatabaseMongoDBQuery Optimization
0 likes · 6 min read
Master MongoDB Indexes: Query Plans, Creation, and Optimization Tips