Stack Overflow
Stack Overflow

Questions tagged [sql-tuning]

SQL tuning commonly involves finding more efficient ways to process the same workload. It is possible to change the execution plan of the statement without altering the functionality to reduce the resource consumption.

328 questions
46
votes
3 answers

How to find out why the status of a spid is suspended? What resources the spid is waiting for?

I run EXEC sp_who2 78 and I get the following results: How can I find why its status is suspended? This process is a heavy INSERT based on an expensive query. A big SELECT that gets data from several tables and write some 3-4 millions rows to a…
Marcello Miorelli
  • 3,368
  • 4
  • 44
  • 67
27
votes
1 answer

SQL Server audit logout creates huge number of reads

I'm using SQL Server Profiler to figure out what process are consuming SQL process and I found that the event class Audit Logout is causing a huge number of reads and consume cpu process. Is it normal? Or do I have something wrong in the SQL Server…
AutoCiudad
  • 735
  • 1
  • 10
  • 14
25
votes
5 answers

Why does PostgresQL query performance drop over time, but restored when rebuilding index

According to this page in the manual, indexes don't need to be maintained. However, we are running with a PostgresQL table that has a continuous rate of updates, deletes and inserts that over time (a few days) sees a significant query degradation. …
Jim Rush
  • 4,143
  • 3
  • 25
  • 27
15
votes
6 answers

Is MySQL naturally slow at this kind of query, or do I have it misconfigured?

The following query is intended to receive a list of unread messages by user. It involves 3 tables: recipients contains a relation of users to message IDs, messages contains the messages themselves, and message_readers contains a list of which users…
levand
  • 8,440
  • 3
  • 41
  • 54
11
votes
2 answers

What is the meaning of Cost, Cardinality and Bytes in Explain Plan?

This is a simple query: select * from customers When I write this query in PL/SQL Developer and press F5, I see Explain Plan, but I don't know what are Cost, Cardinality and Bytes represent.
M.Minbashi
  • 244
  • 1
  • 3
  • 12
8
votes
2 answers

Does "TABLE ACCESS BY INDEX ROWID" means optimizer using index or table?

I have query that join two very big tables and ran explain plan on that it showing like this.. ---------------------------------------------------------------------------------------------------------------- | Id | Operation …
user3225011
  • 161
  • 1
  • 2
  • 12
6
votes
2 answers

need to tune this sql query to improve performance

I have the below query. which is slowing down the performance because of the subquery in it. I tried a lot to add Join instead of Subquery. but in vain. Can anybody tell me how can I rewrite this query using JOIN? update Table_1 set status =…
Shabeeralimsn
  • 797
  • 4
  • 11
  • 32
5
votes
3 answers

Query statement consume quite long time on execution on oracle

I got task to improve existing code / query from my company, Database version Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production "CORE 10.2.0.4.0 Production" TNS for IBM/AIX RISC System/6000:…
user2982040
  • 79
  • 1
  • 9
4
votes
1 answer

Merge join CARTESIAN

Is Merge join CARTESIAN always dangerous? I have many queries with cost ranging from 7 to 40 but follow merge join cartesian for the execution. When the cost of my query is less, should I really bother about Merge Join cartesian? I really need help…
Savitha
  • 405
  • 4
  • 15
  • 25
4
votes
2 answers

How to avoid OR expansion?

How to optimize the following query if the OR operator is used in a join condition to avoid the OR expansion in terms of SQL tuning? SELECT t1.A, t2.B, t1.C, t1.D, t2.E FROM t1 LEFT JOIN t2 ON t1.A=t2.A OR t1.B=t2.C;
general46
  • 800
  • 6
  • 16
4
votes
3 answers

Oracle Composite Index Performace

My sample oracle query structure is this: SELECT FROM WHERE COLUMN_01 = AND COLUMN_02 = AND COLUMN_03 = The table has over 1 Million records. I have indexed COLUMN_01, COLUMN_02 and…
user10606387
4
votes
3 answers

Oracle select sequence.nextval from dual sounds too slow

A while ago I had a database performance problem for inserting/updating several million records using jdbc. To increase performance I changed the code to use batch. Then I decided to monitor the code using jprofiler to know how much the performance…
faghani
  • 569
  • 1
  • 10
  • 25
4
votes
1 answer

What happens to result cache data when the actual data changes?

If I executed a query and its result is stored in the result cache, when I modify one of the tables used in my code, by an insert, delete, update, etc, what happens to the data in the result cache? Does it deleted completely and in the next…
oramas
  • 881
  • 7
  • 12
4
votes
3 answers

Is integer column index faster than string column index in case of index range scan?

I have a database implementation task on SQL Server, where there is a table A containing a column - yearMonth. I don't need date operations such as calculating how many days or month between two dates etc. currently. The yearMonth can be defined as…
Rui
  • 3,454
  • 6
  • 37
  • 70
4
votes
2 answers

Oracle SQL Index DATE vs Index TRUNC(DATE)

I have a Table named DEXTRACTO and I need to consult a period of time based on column F_EXTRACTO (witch is DATE format) with a BETWEEN DATE1 AND DATE2 condition (DATE1 and DATE2 can change). Here's some data of the table: SQL> SELECT…
Cero Silvestris
  • 127
  • 1
  • 1
  • 8
1
2 3
…
21 22