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

Does MERGE statement can benefit from BigQuery field clustering?

I merge two tables in Google BiqQuery ON several STRING fields (field_1, field_2, field_3, field_4). Will MERGE query be more efficient if I cluster my tables on field_1? field_1, field_2? All 4 fields?
gs_vlad
  • 1,409
  • 4
  • 15
  • 29
3
votes
3 answers

How to optimise massive updates in oracle

I need to update a huge table in oracle that contains ~40*10^6 records. The number of rows that will be modified will be approximately 10^7. The query that determines the rows that will be updated is complex and involves joins. It takes 30 mins just…
Ghazouani Med amine
  • 73
  • 1
  • 1
  • 9
3
votes
3 answers

Need to see if a range of dates overlaps another range of dates in sql

I have a table which stores bookings of rooms, the schema is: ID | ROOM_ID | CHECK_IN_DATE | CHECK_OUT_DATE | USER_ID I need to run a search query for rooms which are available/unavailable between a set range of dates. Also keep in mind that there…
Ali
  • 7,353
  • 20
  • 103
  • 161
3
votes
2 answers

Eliminating a Cross Join in SQL

I am trying to optimize a query that contains a cross join. I have large query that I proceed to cross join with a derived table. Would it improve the speed of the query by turning the derived table into a view? Or even capturing that information…
Spencer
  • 21,348
  • 34
  • 85
  • 121
3
votes
5 answers

LINQ or Entity Framework creates unbounded SQL statement

I work on speed optimization of my application, and what I have found is that LINQ (or EF) is creating some strange SQL for me that works slow. Here is some code : SomeList.AddRange(_databaseView .Select(l=> new SomeViewModel …
Naz
  • 5,104
  • 8
  • 39
  • 63
3
votes
1 answer

How to optimize join which causes very slow performace

This query runs more than 12 seconds, even though all tables are relatively small - about 2 thousands rows. SELECT attr_73206_ AS attr_73270_ FROM object_73130_ f1 LEFT OUTER JOIN ( SELECT id_field, attr_73206_ FROM ( SELECT m.id_field,…
Jacobian
  • 10,122
  • 29
  • 128
  • 221
3
votes
5 answers

SQL Server query optimization?

I have the following query: SELECT tl.*, d.* FROM TrackerLocations AS tl inner join Trackers t on tl.TrackerId = t.TrackerId inner join Devices d on t.UserId = d.UserId WHERE tl.ReceivedTime = (SELECT MAX(tl2.ReceivedTime) FROM…
Cesar
  • 497
  • 5
  • 15
3
votes
1 answer

Use of FInd_IN_SET vs IN clause MYSQL stored procedure

I have a stored procedure that is similar to below SELECT * FROM Table1 WHERE Tag IN (ids) here Tag is an Integer column. I tired to pass in comma separated values as string into the stored procedure but it does not work. Then I used stored…
Justin Homes
  • 3,739
  • 9
  • 49
  • 78
3
votes
4 answers

SQL - Too many calls to subquery

The below query is fairly slow, in terms of the subquery selection for the "skill name". When I run a profile against the SQL execution I am getting far too many queries per line from the ACDCallinformation table against the sub query for…
user1479931
  • 244
  • 1
  • 3
  • 11
3
votes
1 answer

MySQL: How to find leaves in specific node

I know this kind questions has been posted here many times, for exmaple: Java way I have huge amount of data (150k+) in standard tree pattern (id, parent_id, some_data) Question: How to get leaves for given node_id? Table structure: CREATE TABLE…
WBAR
  • 4,924
  • 7
  • 47
  • 81
3
votes
3 answers

Optimize a SQL query for tag matching

Example dataset: id | tag ---|------ 1 | car 1 | bike 2 | boat 2 | bike 3 | plane 3 | car id and tag are both indexed. I am trying to get the id who matches the tags [car, bike] (the number of tags can vary). A naive query to do so would…
Matthieu Napoli
  • 48,448
  • 45
  • 173
  • 261
3
votes
4 answers

what is the best way to optimize join query with multiple AND's?

I'm developing an online reservation system where people can reserve items based on availability for a particular hour of the day. For that i'm using two tables 1.Item 2.Reservation Item:(InnoDB) ------------------------- id INT (PRIMARY) …
ravi
  • 838
  • 1
  • 12
  • 25
2
votes
2 answers

Sql query optimization for calculating ratio

I have 2 tables (it is only part of table columns that are needed for this query): Items: Id int, OwnerId int ItemsRelations: RelationId int, ItemId Int (FK Items(Id)) For each OwnerId i need to calculate result of ratio function: Count of user…
Marek Kwiendacz
  • 9,524
  • 14
  • 48
  • 72
2
votes
3 answers

Optimizing a SQL Query for a Many to One Relationship

I've got two tables with a many to one relationship which I'll call Parent_Table and Child_Table (i.e. a parent has zero or more children, but children have exactly one parent). I need to count the number of parents who have at least one child that…
Yozarian22
  • 279
  • 4
  • 9
2
votes
3 answers

How bad is overupdating?

I have the following query, I was wondering how bad is CASE construct, It forces DB Engine to overwrite E.EAOpID with the value that is already contained in E.EAOpID when it is not null UPDATE E SET E.EAOpID = CASE WHEN E.EAOpID…
Lu4
  • 14,873
  • 15
  • 79
  • 132
1 2
3
…
19 20