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

Why to create a composite non-clustered index

I am trying to learn the basic indexing and query optimization techniques. I don't understand why should one create a composite non-clustered index. Let's consider a scenario as given below. Composite Non clustered Index on ColumnA and ColumnB …
Vaibhav Jain
  • 33,887
  • 46
  • 110
  • 163
0
votes
3 answers

How to minimize time in mySQL select query?

I have this query wherein I want to find out the sales for the current year and the sales for last year. I cannot make it into 2 separate queries since it has to be of the same item code. Meaning the item codes used in the sales for the current year…
user3345272
  • 13
  • 2
0
votes
2 answers

sql query taking time, as i am looking into three tables

I am executing this query the results are coming fine but the thing is that it is taking too long, can someone advise me how to make this efficient (SQL is showing that this query is taking 2.8 sec but actually its taking more than 10 sec) at first…
Syed Raza Mehdi
  • 4,067
  • 1
  • 31
  • 47
0
votes
3 answers

How to use SQL to avoid queries within loops

I currently have two related tables, one for 'groups' and another for 'items'. Items are stored in groups by an associated group ID. Very basic table relations but unfortunately my SQL is not very good so I'm unsure what is the best way to loop…
Roy
  • 705
  • 2
  • 11
  • 32
0
votes
1 answer

Single change to query, vastly increased execution time

I have a query that looks like this: SELECT DISTINCT SUM(SFRSTCR_CREDIT_HR) AS NUM_CREDIT_HRS, COUNT(SFRSTCR_PIDM) OVER(PARTITION BY SUM(SFRSTCR_CREDIT_HR)) AS NUM_STUDENTS FROM SATURN.SFRSTCR WHERE SFRSTCR_TERM_CODE = '&TermCode' AND…
anbisme
  • 163
  • 2
  • 11
0
votes
4 answers

Optimizing tricky SQL search query

I am trying to come up with a simple, performant query for the following problem: Let's say there are several entities (items) which all have a unique ID. The entities have a variable set of attributes (properties), which therefore have been moved…
marsze
  • 15,079
  • 5
  • 45
  • 61
0
votes
1 answer

MySQL: efficiently converting event logs to time series

I have a table recording the start time and end time of events of interest: CREATE TABLE event_log (start_time DATETIME, end_time DATETIME); INSERT INTO event_log VALUES ("2013-06-03 09:00:00","2013-06-03 09:00:05"), ("2013-06-03…
andrewtinka
  • 593
  • 4
  • 10
0
votes
1 answer

Advices/tips on optimizing an Oracle SQL query executing on significant volumes

Don't run just after seeing the Oracle SQL query below ! :) I put the complete query in ordrer to ask for some advices on optimizations. I used the Oracle explain plan tool to help me identify some ways of optimizations but I'm quite stuck with it.…
Clem
  • 231
  • 1
  • 9
  • 24
0
votes
1 answer

How to optimize this nested SQL query

Here is the database schema: [redacted] I'll describe what I'm doing with the query below: Innermost query: Select all the saleIds satisfying the WHERE conditions Middle query: Select all the productIds that were a part of the saleId Outermost…
Hrishikesh Choudhari
  • 11,617
  • 18
  • 61
  • 74
0
votes
1 answer

optimize select with SUM ... CASE

is there any way to optimize this query: select orders_id, orders_products_id, sum(CASE products_options_id WHEN 1 THEN products_options_values_id ELSE 0 END) as color, sum(CASE products_options_id WHEN 2 THEN products_options_values_id ELSE 0 END)…
luciano
  • 3
  • 2
0
votes
1 answer

Execution Plan Optimization when where clause is removed then added back

I have a stored procedure that uses a table valued function which executes in 9 seconds. If I alter the table valued function and remove the where clause, the stored procedure executes in 3 seconds. If I add the where clause back, the query still…
nmushov
  • 145
  • 1
  • 8
0
votes
1 answer

Retrieve multiple ResultSets without using StoredProcedures

Is there a way in Java 1.5 to retrieve multiple ResultSet objects from a DB2 database with a single SQL call without resorting to StoredProcedures / CallableStatements?
BenCole
  • 2,092
  • 3
  • 17
  • 26
0
votes
2 answers

Can I copy & paste a SQL result that is over 1 million rows long into Excel?

I have been running a mammoth SQL query that is as so: select SessionID, PID, RespondentID from BIG_Sessions (nolock) where RespondentID in ( '1407718', '1498288', /* ETC ETC */ ) I heard that Excel has a maximum of 1 million rows. Not sure…
Caffeinated
  • 11,982
  • 40
  • 122
  • 216
0
votes
1 answer

Rails 3.1: Optimizing many_to_many through join table in single query?

User has many Tracks, through Favorite. Favorite has some extra per-user meta-data about the related track, and the whole thing is returned as a json blob using custom :as_public hashing method. Even though I'm accessing the related objects using a…
Jordan Warbelow-Feldstein
  • 10,510
  • 12
  • 48
  • 79
0
votes
4 answers

Can this SQL statement be Optimized?

This seems to be taking a very long time on large data sets. Will combining the first and last 3 queries into 1 make it faster? Does anyone have any input on what might make it faster? I appreciate it. update "detail" set bal = (units * amount)…
Trevor
  • 16,080
  • 9
  • 52
  • 83
1 2 3
…
19
20