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
0
votes
2 answers

Most optimized way to get column totals in SQL Server 2005+

I am creating some reports for an application to be used by various states. The database has the potential to be very large. I would like to know which way is the best way to get column totals. Currently I have SQL similar to the following: SELECT…
D.S.
  • 1,413
  • 2
  • 16
  • 26
0
votes
1 answer

Is it possible to eliminate PROJECTION operator when optimizing an SQL query?

For an SQL query: select * from t where R we'll first optimize the ast built from this SQL, and get a logical plan, like Projection -- > Selection -- > t Is it possible to eliminate the PROJECTION part?
HuaiyuXu
  • 1
  • 2
0
votes
1 answer

Optimizing data load in SQL Server

I have been trying to upload around 18 million records from Oracle to SQL Server. The problem is the loading task which take days to complete even though the data is not so much. I have marked parts of the code relative to their respective time…
Burhan Khalid Butt
  • 275
  • 1
  • 7
  • 20
0
votes
2 answers

SQL View Optimization

I am trying to build a view that does basically 2 things, whether a record in table 1 is in table 2 and whether a link to another table is still there. it worked on a subset of data, but when i tried to run the full query it timed out in the view…
Mark Southwell
  • 1
  • 1
0
votes
2 answers

Proper indexing for an InnoDB table

I have an InnoDB table with the following columns id MEDIUMINT UNSIGNED AUTO_INCREMENT a VARBINARY(16) b CHAR(2) c VARCHAR(100) d MEDIUMINT UNSIGNED e TINYINT UNSIGNED, range 0-49 I'll be performing such a query most of the time SELECT d, e, c…
pedmillon
  • 143
  • 3
  • 13
0
votes
0 answers

Joining more table is reducing execution time

I was under the impression that the more number of tables you join in your query the more its execution time increases, but something really opposite is happening in my case. Here is the query where I am joining two tables task_group_attempt and…
Rito
  • 3,092
  • 2
  • 27
  • 40
0
votes
1 answer

MySQL using index issue

I have a big table which has around 7 million records. (MySQL) Columns go like; id | atype | textbody | .... id is primary key, atype is index when I run select * from tablename where `atype`='doit' it uses atype index. (there are 1.7 million…
Hakan Meytan
  • 1
  • 1
0
votes
2 answers

SQL 2012 Query runs a long time, doesn't produce desired results

I've got a query I'm trying to run that runs a long time (~ 1 hour) and doesn't produce the right results. I'd like some recommendations on how to optimize this (we're going to need to run it several times a day), as well as some ideas on what…
TheDoc
  • 688
  • 2
  • 14
  • 33
0
votes
2 answers

Optimize MySQL COUNT / group by query: show only categories that have products associated

select COUNT(p.id) AS `num`, cat.id, cat.name, cat.parent_id AS `parent_id` from products p INNER JOIN `products_categories` AS `pc` ON p.id=pc.products_id INNER JOIN `categories` AS `cat` ON pc.categories_id=cat.id WHERE p.status = 1 AND…
AlfaTeK
  • 7,487
  • 14
  • 49
  • 90
0
votes
1 answer

SQL optimized IO calls (comparing mutiple IOs or single IO with huge data)

I have a SQL table Student. It has 30 columns (properties) for each student. I need 10 properties in a SQL query. Which one is better approach ? q1 = 'SELECT * FROM STUDENTS where uid in %s'%tuple(list1); for k in properties: `q1 = 'SELECT k FROM…
Prannoy Mittal
  • 1,525
  • 5
  • 21
  • 32
0
votes
2 answers

Optimized SQL query when there's no physical connection between tables

I need help with writing more optimized SQL query when there is no logical connection between tables. I wrote some subqueries but I'm not satisfied with it because I'm repeating almost the same subquery twice. The scheme goes like this: create table…
shx
  • 1,068
  • 1
  • 14
  • 30
0
votes
2 answers

Optimizing a group by MySQL query

I have an orders table that holds one item for one user order in a row. So each user can appear on many rows. Each order line has a status - PENDING, APPROVED and so on. I want to count for each user the number of orders per status, that is how many…
Martin Dimitrov
  • 4,796
  • 5
  • 46
  • 62
0
votes
3 answers

Optimizing queries for a database using SQL/XML. Suggested resources?

This question is theoretical as well as practical. Any results indicating useful resources on optimizing queries will be appreciated. There is a large SQL database which stores a large amount of data stored in SQLXML fields. Querying the XML…
smartcaveman
  • 41,281
  • 29
  • 127
  • 212
0
votes
1 answer

How to avoid spending time in creating tmp table in MySQL

I have the following MYSQL query. SELECT COUNT(analyzer_host.server) AS count, analyzer_host.server AS server FROM analyzer_host, analyzer_url, analyzer_code WHERE analyzer_host.server IS NOT NULL AND…
station
  • 6,715
  • 14
  • 55
  • 89
0
votes
2 answers

SQL Request time suddenly increase

I have an SQL request which executes usually with less than 1,5s. But sometimes, it takes more than 30s and this behaviour last hours. Here is the actual request : set dateformat ymd; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT …
Pablo Honey
  • 1,074
  • 1
  • 10
  • 23
1 2 3
…
19 20