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
2
votes
1 answer

TVF is much slower when using parameterized query

I am trying to run an inline TVF as a raw parameterized SQL query. When I run the following query in SSMS, it takes 2-3 seconds select * from dbo.history('2/1/15','1/1/15','1/31/15',2,2021,default) I was able to capture the following query through…
phosplait
  • 285
  • 3
  • 15
2
votes
2 answers

How to choose a random record out of the greatest N records efficiently(not using sub query) in MySQL?

select .. from ( Select ... from ... order by weight desc limit N ) order by rand() limit 1 The above needs to create a temporary table each time,which is not efficient,so it doesn't qualify. How to do it properly?
SQL
  • 21
  • 1
2
votes
3 answers

Performance tuning of this query?

I have below query to support employee pagination sorted by employee name SELECT rowNumAlias ,Employee.employeeId ,Employee.NAME FROM ( SELECT row_number() OVER ( ORDER BY Employee.NAME ASC ) rowNumAlias …
user3198603
  • 5,528
  • 13
  • 65
  • 125
2
votes
3 answers

Query Optimization for MS Sql Server 2012

I have a table containing ~5,000,000 rows of scada data, described by the following: create table data (o int, m money). Where: - o is PK with clustered index on it. o's fill factor is close to 100%. o represents the date of meter reading, can be…
user3455395
  • 161
  • 10
2
votes
1 answer

Optimizing MySQL Query with "NOT IN"

I've seen a few questions dabbling with the inefficiency of "NOT IN" in MySQL queries, but I didn't manage to reproduce the proposed solutions. So I've got some sort of search engine. It starts with very simple queries, and then tries more…
Bootdisk
  • 75
  • 6
2
votes
2 answers

MySQL: Shouldnt using a routine to find a match be much faster in this example?

This query takes 0.0002 secs to execute: SELECT country,city FROM location WHERE locID = 30296 LIMIT 1 locID is obviously an INDEX. This other query uses a routine, and takes 0.0005 secs to execute (it returns 30296): SELECT…
Andres SK
  • 10,779
  • 25
  • 90
  • 152
2
votes
2 answers

Are Columns Not Selected in SQL Views Executed?

I wasn't able to come up with the right keywords to search for the answer for this, so apologies if it was answered already. Consider the following SQL view: CREATE VIEW View1 AS SELECT Column1 ,Column2 ,(SELECT SUM(Column3) FROM Table2…
Serital
  • 343
  • 3
  • 13
2
votes
4 answers

@ Symbol - a solution for Recursive SELECT query in Mysql?

there are a lot of questions about Recursive SELECT query in Mysql, but most of answers is that "There NO solution for Recursive SELECT query in Mysql". Actually there is a certain solution & I want to know it clearly, so this question is the…
Tum
  • 3,614
  • 5
  • 38
  • 63
2
votes
4 answers

Sqlite database optimization

I have sqlite3 database with three tables: CREATE TABLE document ( id Int PRIMARY KEY NOT NULL, root_id Int, name Varchar(100), active Tinyint ); CREATE INDEX IDX_documentId ON document (id); CREATE INDEX IDX_documentName ON document…
alexmac
  • 19,087
  • 7
  • 58
  • 69
2
votes
1 answer

Questions about multi-table query performance

There is two way to do multi-table query: Query 1: select t1.a, t2.b from t1, t2 where t1.a = t2.a Query 2: for each row: select t1.a from t1 do another query: select t2.b from t2 where t2.a = '??' which one has better performance when table is…
MoonFruit
  • 1,490
  • 1
  • 11
  • 11
2
votes
1 answer

Temporary table vs short-circuit operation for SQL query

Following is my query which I want to optimize, here the bottleneck for the optimization is CONDITION_B. select @COMPUTE_X = count(distinct TABLEA.COLUMN_T5) from #TMP_TABLEA TABLEA inner join TABLEB on TABLEB.ID = TABLEA.ID left join.... where ( …
Ankush Gupta
  • 205
  • 5
  • 14
2
votes
1 answer

Can I reuse the result of a subquery to optimize the overall query?

I am using below query to fetch the top two records for profile_run_key. I am using three almost similar queries to get this done. This means I am traversing the table thrice for the "where" clause. So I think this will take 3(n) time for execution.…
user1590695
  • 23
  • 5
2
votes
3 answers

Optimising COUNT(*) out of GROUP_BY queries

Below is a working SQL query that returns a list of unviewed message counts grouped by a user's multiple accounts. However, we don't actually need the counts, just a bit to indicate that unviewed messages exist. Without pulling apart the fairly…
Yarin
  • 173,523
  • 149
  • 402
  • 512
2
votes
1 answer

SELECT DISTINCT Extremely slow

I have a query that is taking 48 seconds to execute as follows: SELECT count(DISTINCT tmd_logins.userID) as totalLoginsUniqueLast30Days FROM tmd_logins join tmd_users on tmd_logins.userID = tmd_users.userID where tmd_users.isPatient = 1 AND…
ToddBFisher
  • 11,370
  • 8
  • 38
  • 54
1
vote
4 answers

Efficient search in many-to-many related tables

I have two tables related many-to-many via third connecting table: products and categories. Each product can be in a several categories. It's a typical many-to-many…
Silver Light
  • 44,202
  • 36
  • 123
  • 164
1 2 3
…
19 20