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

Page numbers & total result count in a cursor based pagination

Though it may sound like a stupid question, sometimes it is necessary to show page numbers (and also the last page). What is the best way to calculate total row counts and also calculate page numbers (by providing a page query to return specific…
Sakibur Rahman
  • 834
  • 3
  • 10
  • 26
2
votes
5 answers

MS SQL: Performance for querying ID descending

This question relates to a table in Microsoft SQL Server which is usually queried with ORDER BY Id DESC. Would there be a performance benefit from setting the primary key to PRIMARY KEY CLUSTERED (Id DESC)? Or would there be a need for an index? Or…
Seb Nilsson
  • 26,200
  • 30
  • 103
  • 130
2
votes
1 answer

Mysql query joining 5 tables

I am trying to join 5 tables in which i want to get different currency mentioned on different tables against same contract id. It is giving me results when i join any three tables but when I add one more table in query the server gets unresponsive…
sidra ahsan
  • 21
  • 3
2
votes
2 answers

Recursively get last record from same table parent child

I want to get last reference Id from same table. I have following table. ID UserId DelegatedToUserId 1 100 101 2 101 102 3 102 103 4 103 NULL 5 104 109 I just can't get my head…
Muhammad Saifullah
  • 4,292
  • 1
  • 29
  • 59
2
votes
1 answer

Optimizing query for updating millions of data in Oracle?

I have 3 tables: Table A: 170 million data Table B: 170 million data Table C: 130 million data Table Log I want to update column colA1 with the value of colC4 if colA2 == colB2 and colB3 == colC3. I created a table: Table D: 80 millions data to…
Hary Kamaro
  • 121
  • 1
  • 1
  • 10
2
votes
2 answers

How do you show the actual query plan for a single query in a batch in SSMS?

I have a stored procedure I'm trying to optimize which accepts a table variable as parameter. The table variable has a single column and is used to pass in a list of primary keys. When testing, I create a dummy table variable, run a bunch of INSERT…
Andrew
  • 789
  • 7
  • 13
2
votes
5 answers

Computing number of days which fall between predefined date ranges

this is a complex situation I am ain. I have a booking system where rooms are booked. The thing is that the rates for the rooms aren't stored as a single value but are period based. Like a room can have one daily rate between September to December…
Ali
  • 7,353
  • 20
  • 103
  • 161
2
votes
0 answers

Query plan for merging sub tables into master table suddenly incorporating eager spool with huge performance loss

As written in my question, I have a problem with my execution plan as you can see in the title, a group of queries that was taking around 30-40 seconds to complete now have incorporated this section in the execution plan: While the execution plan…
Forna
  • 149
  • 3
  • 13
2
votes
1 answer

Optimise multiple OR query

I have a Database table where I need to validate if a user has entered in the same or partly the same information. Here is what I'm thinking The db layout rec_id (pk), user_id, name, phone, address_1, address_2, zip, company, co_phone,…
Phill Pafford
  • 83,471
  • 91
  • 263
  • 383
2
votes
3 answers

Force SQL subquery to be run first

I have a (simplified) query like so: SELECT DISTINCT this_.person_id AS y0_ FROM coded_entity this_ WHERE this_.code = ? AND this_.tenant_id = ? AND this_.person_id IN (SELECT person_id FROM person that …
AHungerArtist
  • 9,332
  • 17
  • 73
  • 109
2
votes
1 answer

SQL optimize "IS NULL" query with LEFT JOIN

I'm working on a project involving words and its translations. One of the queries a translator must task frequently (once every 10 sec or so) is: SELECT * FROM pwords p LEFT JOIN words w ON p.id = w.wordid WHERE w.code IS NULL OR (w.code <> "USER1"…
deFunc
  • 33
  • 4
2
votes
7 answers

SQL Optimization

How to optimize this? SELECT e.attr_id, e.sku, a.value FROM product_attr AS e, product_attr_text AS a WHERE e.attr_id = a.attr_id AND value IN ( SELECT value FROM product_attr_text WHERE attribute_id = ( SELECT attribute_id …
powtac
  • 40,542
  • 28
  • 115
  • 170
2
votes
2 answers

Optimizing SQL query with multiple joins and grouping (Postgres 9.3)

I've browsed around some other posts and managed to make my queries run a bit faster. However, I've come to a loss as to how to further optimize this query. I'm going to be using it on a website where it will execute the query when the page is…
andrewjdg
  • 71
  • 2
  • 7
2
votes
1 answer

Can I speed up this query by using EXISTS instead of a JOIN?

I have the below query, which is running on a db with over 24 million rows, as such it is taking too long and timing out. I was hoping to rewrite this to use an EXISTS to try and save on performance. Is this possible? SELECT DISTINCT…
Gordon
  • 108
  • 7
2
votes
1 answer

Nonclustered primary key dilemma

Suppose we'd have to define optimal indexing for Stackoverflow questions. But let's not take the schema of the actual Posts table, let's just include those columns that are actually relevant: create table Posts ( Id int not null …
Robert Koritnik
  • 103,639
  • 52
  • 277
  • 404
1 2 3
…
19 20