Stack Overflow
Stack Overflow

Questions tagged [sql-optimization]

SQL Optimization refers to the process of testing and debugging SQL servers and queries in order to increase overall performance.

SQL Optimization refers to the process of testing and debugging SQL databases, servers and queries in order to increase the speed and performance and to reduce used resources.

Usually, SQL optimization can refer to:

  • SQL Query optimization, where the SQL queries are being optimized internally in order to be optimal and to use as little resources as possible.
  • Database (schema) optimization, where the database or schema itself is being optimized in order to minimize redundancy. In literature, this is also referred to as normalization.
  • SQL Server optimization, where server configuration is being modified in order to be optimal for the needs of the used application(s).
295 questions
167
votes
14 answers

How to delete large data of table in SQL without log?

I have a large data table. There are 10 million records in this table. What is the best way for this query Delete LargeTable where readTime < dateadd(MONTH,-7,GETDATE())
user3107343
  • 2,159
  • 6
  • 26
  • 37
30
votes
13 answers

How to optimise this MySQL query? Millions of Rows

I have the following query: SELECT analytics.source AS referrer, COUNT(analytics.id) AS frequency, SUM(IF(transactions.status = 'COMPLETED', 1, 0)) AS sales FROM analytics LEFT JOIN transactions ON analytics.id =…
Abs
  • 56,052
  • 101
  • 275
  • 409
20
votes
2 answers

Updating multiple rows with different primary key in one query in PostgreSQL?

I have to update many columns in many rows in PostgreSQL 9.1. I'm currently doing it with many different UPDATE queries, each one that works on a different row (based on the primary key): UPDATE mytable SET column_a = 12, column_b = 6 WHERE id =…
Amandasaurus
  • 58,203
  • 71
  • 188
  • 248
13
votes
7 answers

Database Optimization techniques for amateurs

Can we get a list of basic optimization techniques going (anything from modeling to querying, creating indexes, views to query optimization). It would be nice to have a list of these, one technique per answer. As a hobbyist I would find this to be…
Zombies
  • 25,039
  • 43
  • 140
  • 225
8
votes
3 answers

SQL: How to select one record per day, assuming that each day contain more than 1 value MySQL

I want to select records from '2013-04-01 00:00:00' to 'today' but, each day has lot of value, because they are saving each 15 minutes a value, so I want only the first or last value from each day. Table schema: CREATE TABLE IF NOT EXISTS…
Alejandro L.
  • 1,066
  • 5
  • 17
  • 38
7
votes
9 answers

What's faster IN or OR?

In T-SQL what's faster? DELETE * FROM ... WHERE A IN (x,y,z) Or DELETE * FROM ... WHERE A = x OR A = y OR A = z In my case x, y and z are input parameters for the stored procedure. And I'm trying to get the performance of my DELETE and INSERT…
Yvo
  • 18,681
  • 11
  • 71
  • 90
6
votes
2 answers

Why SQL Server execution plan depends on comparison order

I was optimising a query on SQL Server and ran into something I was not expecting. There is a table tblEvent in the database, among other columns it has IntegrationEventStateId and ModifiedDateUtc. There is an index by these columns: create index…
Andrew
  • 1,139
  • 1
  • 12
  • 27
6
votes
1 answer

Slow query optimisation in Postgres

We have a performance issue with a specific SQL query and we're trying to figure out how could we improve here. It's execution time on is about 20 - 100 seconds! Here is the query and it's explain: SELECT "jobs".* FROM "jobs" WHERE…
Jack Juiceson
  • 830
  • 4
  • 12
  • 24
6
votes
5 answers

How to get the last record in MySql with 2.5m rows

I want to get the last record in my MySql table, but the table has 2.5 million rows. How to get the last row efficiently? I'm using order and limit but the query runs ~15sec. I have to decrease this value to nearly zero. My SQL Query : SELECT id…
totten
  • 2,769
  • 3
  • 27
  • 41
6
votes
2 answers

SQL optimization Case statement

I believe I can optimize this sql statement by using a case statement for the Left Outer Joins. But I have been having hard time setting up the cases, one for summing up the code types AB,CD and another for All the rest. Appreciate any help or…
Trevor
  • 16,080
  • 9
  • 52
  • 83
5
votes
2 answers

How can I make MS-Access choose a different/the right execution plan for my query

I have a problem with a relatively simple query and the execution plan Access choose for it. The query is of this form SELECT somethings FROM A INNER JOIN (B INNER JOIN (C INNER JOIN D ON ...) ON ...) ON ... WHERE A.primaryKey= 1 AND D.d = 2; C and…
Mathieu Pagé
  • 10,764
  • 13
  • 48
  • 71
5
votes
8 answers

How to optimize execution plan for query with multiple outer joins to huge tables, group by and order by clauses?

I have the following database (simplified): CREATE TABLE `tracking` ( `id` int(11) NOT NULL AUTO_INCREMENT, `manufacture` varchar(100) NOT NULL, `date_last_activity` datetime NOT NULL, `date_created` datetime NOT NULL, `date_updated`…
Kristian Vitozev
  • 5,791
  • 6
  • 36
  • 56
5
votes
2 answers

mysql "group by" very slow query

I have this query in a table with about 100k records, it runs quite slow (3-4s), when I take out the group it's much faster (less than 0.5s). I'm quite at loss what to do to fix this: SELECT msg.id, msg.thread_id, msg.senderid, …
Sherif Buzz
  • 1,218
  • 5
  • 21
  • 38
5
votes
1 answer

Sql Server: Selective XML Index not being efficiently used

I'm exploring ways of improving the performance of an application which I can only affect on the database level to a limited degree. The SQL Server version is 2012 SP2 and the table and view structure in question is (I cannot really affect this +…
Consulmagician
  • 73
  • 1
  • 8
5
votes
1 answer

Why do I need to rollback a SELECT in mysql (InnoDB)?

I ran an ill-advised SELECT * on a large InnoDB table in a mysql database. So after 10 minutes or so I realised the error, found the connectionid with show processlist, and attempted to kill both the connection and the query with the kill command.…
Dev Null
  • 75
  • 7
1
2 3
…
19 20