Query optimization is a function of many relational database management systems. The query optimizer attempts to find an efficient way to execute a given query by considering the possible query plans.

learn more… | top users | synonyms

5
votes
1answer
97 views

Trace Flag 4199 - Enable globally?

This may fall under the category of opinion, but I'm curious if people are using trace flag 4199 as a startup parameter for SQL Server. For those that have used it, under what circumstances did you ...
1
vote
1answer
40 views

Generate rows based on value in 2 columns. Bug?

I have the following table in Oracle: ID START END _ID01 AB050 AB100 _ID01 AB101 AB150 _ID02 AB0251 AB0300 _ID01 AB00301 AB00302 I want to generate as many rows and every ...
0
votes
1answer
18 views

Connection problem different errors .net application

We have a SQL server with some relational databases. We have another webbserver with applications which communicate with the databases. We have an issue with one of our customers and we get some ...
0
votes
3answers
78 views

Adding order by to a query is slowing down the query even after Indexing

While joining columns with huge data even after indexing the column order by is slowing down the query. Without order by query is executing within 2 secs. But with order by query is taking 145 secs. ...
1
vote
2answers
104 views

SELECT and UPDATE in a single query while updating selective fields of duplicate records only

I have a MySQL table named Contacts_table with, say, the following contents: ID NUMBER NAME CITY 001 2222222222 John Doe Los Angeles 002 ...
2
votes
1answer
84 views

Query looks optimal but logical reads is huge

I run the following (really simple) query in SQL Server: SELECT MAX(PK_Field1) FROM MainTable kh WHERE kh.PK_Field1 >= '2014-12-01T00:00:00' AND kh.PK_Field2 = 1572 AND ...
2
votes
1answer
67 views

DB2 CASE statement faster with parentheses around entire WHEN

Trying to optimize slow queries, someone on my team come across this. Having this in our stored proc: CASE WHEN MYCOL BETWEEN @STARTNUM AND @ENDNUM AND RECTYPE = 'RO' THEN 1 ELSE 0 ...
2
votes
2answers
56 views

MySQL taking too much CPU

Below is my .cnf file and if I see my CPU consumption 99.99% of it my the mysqld commands. The MySQL server is connected from remote machines that update data in it frequently, but I make sure that ...
0
votes
1answer
19 views

MySQL 2 Million Rows, longtext and varchar indexes

I'm trying to find a solution for this, with no success. I have a table with ~2M rows, and I need to do a WHERE with 3 expressions using OR. name LIKE '%PARAM%' OR text1 LIKE '%PARAM%' OR text2 LIKE ...
0
votes
0answers
46 views

Optimizing a CTE query - overhead in inserting into a new table from select statement

I have two tables with the same structure like so (let us call them initial_table1 and initial_table2): tempid name num_records 1 a 2 2 b 2 3 c 2 4 d ...
0
votes
3answers
58 views

MySQL - How do I Calculate an Average Value Over a Period of Time When Only Value Changes are Recorded

I am tracking the sales price of various items, and I would like to calculate the average price of each item over a period of time. My simplified tables look like this: Items ------------------ |key ...
0
votes
2answers
64 views

Oracle query optimization

We have a query that runs on a view and its execution time is about 6 min. SELECT COUNT(*) FROM someschema.item_total_violations WHERE channel_id = 122507833 AND item_date >= ...
7
votes
1answer
155 views

How does SQL Server's optimizer estimate the number of rows in a joined table?

I am running this query in the AdventureWorks2012 database: SELECT s.SalesOrderID, d.CarrierTrackingNumber, d.ProductID, d.OrderQty FROM Sales.SalesOrderHeader s JOIN ...
0
votes
0answers
8 views

indexing table column on the go of mysql query commit

It is said that to optimize the query cost of execution, then it would be by indexing the field of tables in database. Since I don't have a permission to enter the phpmyadmin in my web server as I am ...
0
votes
0answers
24 views

Best way to run a formula (segment) a large number of users with custom attributes

Here is my question. I have a table that stores a large number of people (80 mil). Each person has a GUID id. Then I have another table (or set of tables) that stores information about people. e.g. ...
0
votes
1answer
21 views

Optimize Query in MySQL

I've following tables with respective fields 1) wi_individual_g(ind_id,ind_is_recepient,ind_deleted,ind_district_id,...) - Around 200K data 2) wi_individual_p(ind_id,prg_id,...) Around 250K data 3) ...
1
vote
0answers
54 views

Is there a way to improve this query further?

I've got a query that over time I've been improving. Originally it simply selected * from the records table, and the data was formatted on the webapp side. But that was slow and I wanted to make ...
2
votes
0answers
47 views

Performance differs on same query against two (almost) identical tables?

I'm running these two queries in SQL Server 2012: SELECT tpua.Field1, CAST(round(twg.Field2 * 2, 0) / 2 As Decimal(8,1)) As Field2, CAST((round((twg.Field3/10.0) * 2, 0) / 2) * 10 As int) As ...
0
votes
1answer
35 views

Replace nested “select with count” with outer join? is this possible?

SELECT c.classid , c.classname , (select count(s.id) from students s where s.classid = c.classid and status = 'failed') as noOfFailed, (select count(s.id) from students s where ...
1
vote
0answers
28 views

SQL - Optimize table population with dynamic queries

I need to optimize the following pl/sql procedure. So, fundamentally my question is: There is a way you can do a bulk insert in a pl/sql that could be more efficient than loop through an associative ...
0
votes
2answers
38 views

Is this query supposed to be slow?

Select * from (select tmp.userid userid, tmp.username ,tmp.likescore+tmp.commentscore+tmp.sharescore+tmp.tagscore+tmp.userscore+tmp.lsharescore+tmp.friendscore as score from ( SELECT ...
0
votes
1answer
62 views

What is the most efficient way to count the number of rows in a table?

I am using postgres with the following query: select count(*) from image; The primary key on this table is non-incrementing; it's a unique serial number for the images stored in the table. Our app ...
0
votes
2answers
44 views

Joining 4 tables and selecting distinct values based on 3 timestamps

I have been trying to remove duplicates from my query results based on the time but I would need to consider 3 different times and i am not sure how to do that. As some of the testing happen multiple ...
1
vote
1answer
58 views

What is causing an INTERSECT to run forever when the individual SELECTs run quickly? [closed]

I have a script that applies the INTERSECT operator to 2 separate queries. Query1 executes instantly and returns ~1k rows. Query2 executes almost instantly and returns 200k rows However, when ...
2
votes
1answer
130 views

Optimise select distinct from large table

I have a table that contains 320,071,712 number of records, the report that I am currently working on can filter the records by date, after which the number of records come down to 145,878,852. The ...
0
votes
1answer
42 views

Optimizing a MySql query

Here's the query: set @_YEAR = 2014; set @_MONTH = 1; set @_PRODUCTS = '53'; set @_START = date(@_YEAR * 10000 + 1 * 100 + 1); set @_END = date_add(date(@_YEAR * 10000 + 1 * 100 + 1), interval 1 ...
1
vote
1answer
77 views

Multi table GROUP BY query optimisation

Currently I have a multi table/column GROUP BY query which is pretty slow. Below is the tables schema used by the query: report Table ...
2
votes
0answers
19 views

Query optimization - contact should be extracted at most once for a particular customer

I have created a database for a telemarketing company Scenario is, the telemarketing company is selling contacts to the customers. Customers log on to the website and search for the contacts for ...
1
vote
2answers
92 views

Are nonclustered indexes used when joining tables?

I know that joins of type Sort-Merge don't use nonclustered indexes, because they work by sorting both tables and then joining them. But I've read recommendations to noncluster index all FKs, I ...
1
vote
1answer
228 views

Find out beforehand how many records a query has

I develop reports in ExtJS resulting of queries in SQL Server 2012. I use Ext.Grid with paging, therefore I can use offset to limit the amount of records a query will return. But for this ExtJS ...
0
votes
0answers
40 views

Issues with Optimizing TSQL Script

I have a temp table that has a SecurityCode column. This SecurityCode column gets updated based off value from a CrossReference table. The value selected from the CrossReference table depends on how ...
1
vote
1answer
34 views

How to get paged resultset from a table?

I am trying to get paged resultset from a table (Notes). So, I am passing the last offset id (@LastOffset) value along with the page size (@Count). My approach to achieving this is: Sort the table ...
0
votes
0answers
32 views

SQL Server 2012: query to see queries that are consuming more resources [duplicate]

I'm trying to find/build a query to show me those queries that are consuming more resources in SQL Server so that I can optimize them or at least have a statistical idea of what is really happening. ...
0
votes
1answer
101 views

mysql multiple column index Cardinality order

How does Multiple-Column Indexes work in MySQL? How is it stored internally? consider a table with Multiple-Column Indexes on two columns (X,Y). If X has High-cardinality than Y i.e., X is less ...
1
vote
2answers
58 views

Get the pais_id not associated to fabricante_distribuidor_id and producto_solicitud_id, the opposite from SELECT query

I have this query for get all the pais_id given a fabricante_distribuidor_id and producto_solicitud_id, the query works fine. SELECT DISTINCT nom.pais_id FROM negocio.fabricante_producto_solicitud ...
0
votes
1answer
42 views

optimizing a select query of 1.5 million entry

I have the following query: SELECT s0_.id, s0_.lowresimageurl FROM app_instagram_shop_picture s0_ FORCE INDEX ( idx_deleted_shopId ) LEFT JOIN app_instagram_shop s1_ ON s0_.shop_id = s1_.id AND ( ...
2
votes
1answer
112 views

Threshold for sort spill and random cardinality estimates

I wanted to test tempdb spill warnings so I run the following script on SQL Server 2014: USE tempdb IF OBJECT_ID('tempdb..tblTest') IS NOT NULL DROP TABLE tblTest CREATE TABLE tblTest ( ...
0
votes
1answer
32 views

optimize my query mysql

I have the following query: SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink FROM director_movie as d LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId WHERE d.castName ...
0
votes
1answer
107 views

SQL execution 2 seconds with LIMIT parameter, without only 0.2s why?

This is my query and I need to return only 12 rows, but it takes 2 seconds with parameter LIMIT 12 while it takes only <0.2 to process all ~600 results (without LIMIT 12 or with LIMIT 1200 for ...
2
votes
0answers
32 views

PLSQL using cursors or functions to query repeated data

Is it better to declare a commonly used lookup query as a package function or as a function (either returning rows or values or returning a sysref cursor)? Whats the difference? What are the best ...
1
vote
1answer
68 views

optimizing query for slow queries

I have this query in my slowqueries table which essentially is taking about 5-8 seconds to complete the query time: SELECT s0_.id AS id0, s0_.updated AS updated1, s0_.created AS created2, ...
0
votes
0answers
126 views

Performance tuning self-joining MySQL query

I asked a question recently on the correct way to update a table using multiple joins to itself in order to generate the desired values. Updating MySQL innodb table based on multiple joins After ...
0
votes
1answer
47 views

MySQL optimize range duplicate query

I need to eliminate duplicates from a time sheet. I found this solution and have adapted it for my own needs: DROP TABLE IF EXISTS `activity`; CREATE TABLE IF NOT EXISTS `activity` ( `id` int(11) ...
1
vote
0answers
75 views

Optimizing SELECT queries

I'm not very experienced with database query optimization. I've been reading through similar questions here and Postgres tuning articles online, but unfortunately I haven't had any luck. Here's what I ...
0
votes
1answer
79 views

Is there a way to force one optimization level in SQL Server?

Based on Paul White's blog, there are three optimization phases in SQL Server, search 0(Transaction Processing), search 1(quick plan) and search 2(full optimization). Each phase has entry and ...
3
votes
1answer
164 views

When is mariadb creating tmp tables?

I have found, that copying to tmp table is taking most of the time of a slow query, that I wanted to optimize. I watched the output of 'profiling' in a terminal. This happens on a pics table with 6000 ...
1
vote
1answer
319 views

Slow postgresql query with pagination and filtering

For the DB in question, I have followed the advice of Pagination Done the PostgreSQL Way That is, results are being paginated on a composite index with a definite sort order. Now, my problem is ...
0
votes
1answer
28 views

Join Operation using index defined?

I need to optimize some queries with several joins. I plan to create some indexes for these queries. I am currently using MariaDB with TokuDB storage engine. Should I expect the indexes for the join ...
0
votes
0answers
32 views

Ascending and descending semi-joins

I was reading about semi-joins for a research paper implementation. i have to select the best way to reduce tables in a distributed environment for a particular query. for that it has 2 different ...
0
votes
0answers
200 views

merge in cursor is slow ,any alternative for that in oracle 11g

we have a requirement like below. we need to update one table A from another table B based on joining criteria. both tables are having porper joining columns. Table A has 2 columns missing_order_cw ...