A class of issues where a database executes a query inefficiently due to the optimiser selecting an innapropriate query plan.

learn more… | top users | synonyms

3
votes
2answers
2k views

Copy huge amount from 1 table to other

I have two tables with same structure I need to copy over all tables from table A to table B, the problem is that some records from table A already exist in table B so that made the Import Fail. So I ...
3
votes
3answers
228 views

Nested table as condition prevents using index

The following query joining a subquery does a TABLE ACCESS FULL when the master-table uses nested table as condition. When the condition is a simple =, the same query does an INDEX RANGE SCAN as ...
3
votes
1answer
128 views

How do I deal with query getting slower when a select parameter is not specified inline?

I faced this problem twice with SQL Azure and two different queries so far. Something like this: select top(100) * from SomeView WHERE AccountId='actual-guid-typed-in-here' runs super fast but ...
3
votes
1answer
335 views

Most efficient queries on flags in TSQL: bit field or table join?

Looking for the most efficient database structure for selecting rows from a large table based on one or more true/false values. For example, whether or not a user has opted in to marketing ...
3
votes
1answer
209 views

Optimizing complex join predicates [closed]

Table Definitions CREATE TABLE [dbo].[Pin_Mtg] ( [MtgId] bigint NULL, [CntyCd] char(5) NOT NULL, [BatchDt] int NOT NULL, [BatchSeq] int NOT NULL, [MtgSeq] tinyint NOT NULL, ...
3
votes
3answers
381 views

Why does the query cost change so much and how to prevent it

The query causing me the issue is too big, but the piece that seems to be the core is quite simple, I will try to go with that: The query has an structure like: SELECT ... a lot of stuff ... ...
3
votes
2answers
289 views

Sudden Query Performance Degradation On A Linked Server

I have an application that uses a view on a SQL Server 2008 R2 instance. This view actually queries a linked server (SQL Server 2008) that has replicated data from our main ERP database. Yesterday, we ...
3
votes
1answer
1k views

MYSQL Slow SELECT with multiple joins and ORDER BY

I have this query SELECT r0_.id AS id0, r0_.deleted_at AS deleted_at1, r0_.updated_at AS updated_at2, r0_.created_at AS created_at3, r0_.is_vehicle_offer AS is_vehicle_offer4, ...
3
votes
4answers
908 views

Help with Query. Finding records that have the same relationships (MySQL)

I have a table of Listings that has a many to many relationship with a Taxons table. The table structure looks like this: listings ---------------- id (int) name (varchar) listings_taxons ...
3
votes
1answer
1k views

Is there any difference on performance when choosing DISTINCT or GROUP BY to bring distinct values?

I would like to know if there is any difference concerning performance when choosing DISTINCT or GROUP BY to bring distinct rows from a query. I've tried comparing the execution plans, but they seem ...
3
votes
2answers
162 views

Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

I have a poorly performing query: SELECT user_id, count(item_id) as count FROM table items WHERE category = 'magazine' AND created_at > 1384754400 GROUP BY user_id ORDER BY count(item_id) desc ...
3
votes
1answer
454 views

Need help with long running query

UPDATE: Adding a clustered index to each work table covering all fields reduced the query run times to su-bsecond. I consider this question closed. Thanks for taking the time to read this post - ...
3
votes
2answers
78 views

Looking for a possible multi-column index

I have a table highscore containing the columns: game (text) date (timestamp) score (integer) more irrelevant ones... The query most often run on it is: SELECT * FROM highscore WHERE game = ...
3
votes
1answer
750 views

MySQL Handler_read_rnd_next == tablescans without indexes?

I am in the process of tuning our Percona 5.5 server, and have been using percona's tools to plot our database's performance. In the MySQL Handlers Plot, i have been noticing alot of ...
3
votes
1answer
1k views

MySQL 5.5.8 InnoDB Foreign Key, JOIN performance

We are using aws/rds, MySQL 5.5.8. all InnoDB. For performance purposes, we have merged some data-elements into varchar fields; mini-sized int and date type fields; added covering indexes; ...
3
votes
1answer
135 views

How to search for an ID and its descendants in this query?

I have the following tables: Project Unit ------------- --------------- ID | IDUnit ID | IdParent I have to select all projects that have the IDUnit ...
3
votes
2answers
430 views

SQL server query is slow *without* including execution plan

I have just set up fresh installation of SQL Server 2012 on a new server and I'm experiencing very strange performance problems. Long story short, for some reason not including execution plan makes my ...
3
votes
3answers
1k views

PostgreSQL 8.3: Slow GROUP BY on large table

I have a table with about 10 million records. I want to do a simple group by, but it's using a sequential scan and is slow... select run_id, count(*) from result group by run_id; I have an index ...
3
votes
0answers
356 views

What does duration measurement in sql server actually measure?

What precisely does the query duration measure when conducting a profiler trace? I have a stored procedure which performs data access (SELECT), and it is taking an average of around 30 seconds to ...
3
votes
2answers
375 views

Query planner thinks doing a sequential scan on a very large table is faster than using an index?

I've got a query that selects the most favorited photo for each of the last 12 editions for a particular domain (this is just a segment of a larger query, but the problem seems to stem from here): ...
2
votes
2answers
224 views

DISTINCT not using an index [duplicate]

I have table (2139868 rows): COLUMN1 NUMBER(20,0) COLUMN2 XMLTYPE COLUMN3 XMLTYPE COLUMN4 VARCHAR2(50 BYTE) COLUMN5 VARCHAR2(50 BYTE) COLUMN6 VARCHAR2(50 BYTE) COLUMN7 VARCHAR2(50 BYTE) COLUMN8 ...
2
votes
4answers
1k views

Remove sub-string from WHERE clause

I have a stored procedure that is using the SUBSTRING function in the WHERE clause. SELECT DISTINCT ColumnBB, ColumnCC FROM TableAA WITH (NOLOCK) WHERE SUBSTRING(ColumnAA, 1, 17) = @VariableA; ...
2
votes
1answer
309 views

Performance of query with complex where clause on 10M rows

We have a query that destroys our production server when run. It is part of a reporting functionality and the bad part looks as follows: SELECT DISTINCT mt.ID AS ID FROM ...
2
votes
2answers
798 views

Get table name from union query?

This is my query SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename1 where Active =1 union SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename2 ...
2
votes
3answers
319 views

selecting multiple instances of a record based on record lifespan over years

I have a table of items that have a origin date and a life span. I want to create a procedure that selects all of the items that will need to be replaced in a specific duration. I feel like there is a ...
2
votes
2answers
254 views

What can be done to further enhance performance of Multiple Join and Aggregate Queries?

I have a typical star schema simulated here, and I am mentioning two queries: first query simply joins the fact table with 2 dimension tables and 1 calendar table, and the second query joins and ...
2
votes
2answers
99 views

which option is better for not slow down the performance of queries?

I am developing a system that records customer payments, when a customer makes a payment, the system must update the account and show the payment made and all previous payments, is here when the ...
2
votes
1answer
230 views

Why is the following query so bad performance-wise

After asking this question, I got to thinking about why a certain query was doing all the problems. In short, there was a query which took 500ms, I ran some execution plans and applied the ...
2
votes
3answers
336 views

mariadb ignoring force index

I'm trying to tune a relatively simple query: select bt.col1,bt.col2 from bigtable bt join smalltable st on bt.smalltable_id = st.smalltable_id where st.name = 'some name occuring only once in st' ...
2
votes
1answer
259 views

Does SQL check each And condition if first condition fails? [duplicate]

For a query of below kind does sql process data for each And condition if first And condition fails? If Emp_ID is not 12 for a row then does it check condition of Emp_Name? So how SQL process this ...
2
votes
2answers
85 views

Comparing Queries

In SQL Server, if you're comparing the statistics IO from two queries, how do you ensure that caching doesn't become a factor? For example (I'm making this up as I type, not a real example): SELECT ...
2
votes
1answer
226 views

Getting each status change in a table

I have a table that holds status changes per item. The item can go in and out of a status multiple times. ItemTable (ItemId int, Status varchar(50), DateOfChange date) I need to pull the date of ...
2
votes
2answers
458 views

Optimize simple query using ORDER BY date and text

I have a query to a table in Postgres with an order based on a date field and a number field, this table has 1000000 records The data types of the table are: fcv_id = serial fcv_fecha_comprobante = ...
2
votes
2answers
7k views

Get username and/or IP address responsible for a query

I use the following query to find performance improvements in queries: SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN ...
2
votes
2answers
232 views

Changing ORDER BY direction causes performance issue

I use MySQL 5.6, and I have two tables each with 16M rows: CREATE TABLE IF NOT EXISTS `newsstudios` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL, ...
2
votes
1answer
289 views

Optimize simple query in SQL Server

This should be a quite easy query, but I honestly think its execution time can be improved. select idTag,MAX(pctimestamp) AS PCTIMESTAMP,getdate() AS NOW, datediff(SECOND,MAX(pctimestamp),getdate()) ...
2
votes
2answers
3k views

Will altering order of columns in GROUP BY affect performance?

I have a SQL query like this: SELECT A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END), COUNT(D) FROM SomeTable GROUP BY A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END) On a huge dataset the actual execution plan ...
2
votes
2answers
452 views

Query hangs forever at first but works after one day

I have two schemas namely A and B. I regularly copy contents of 10 tables from A to B. Here's is how I do it First I rename the original tables in schema B Then, using SQL Developer I copy the table ...
2
votes
2answers
564 views

Improve CTE performance

is there a way to improve the performance of a recursive CTE like below, I am unsure if I can add indexes on joins when the joins are using ROW_NUMBER? DECLARE @File_Name VARCHAR(8000), @Disk ...
2
votes
2answers
812 views

Why does my query run faster after I force full table scan?

I have a MERGE statement that took forever to execute the select as the table it is running from (SUB_DATA) is rather big (read 1.4 Tb, 700 million rows). My SELECT looks like this. SELECT ...
2
votes
1answer
160 views

Need some help with this query get all status and their associated comments of me and all my friends

I've several days dealing with this query but can't get it to works as I want. This post is related to this one Several values from different tables in which I tough found the solution but I was ...
2
votes
3answers
2k views

Is it normal to use many Triggers?

I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I ...
2
votes
2answers
38 views

Is it a better idea (performance is key) to store in a normalized table and retrieve by JOIN or in a single nvarchar column and retrieve using LIKE?

NOTE: The application is not really about websites or keywords, I am using that here only to provide reference points for discussion. I have a data set that is really just a string of words. Think of ...
2
votes
3answers
2k views

GROUP BY one column, while sorting by another in PostgreSQL

How can I GROUP BY one column, while sorting only by another. I'm trying to do the following: SELECT dbId,retreivalTime FROM FileItems WHERE sourceSite='something' GROUP BY seriesName ...
2
votes
2answers
1k views

Optimized query to fetch a record from 10 million entries

A table has 2 fields | UniqueKey | TimeStamp | ------------------------- | xfsddddq | 1024125412 | | xfstttdx | 1024125413 |< | xfsdxxau | 1024125415 | Estimated, there are 10 million ...
2
votes
1answer
2k views

Query taking non-consistent time to execute [closed]

I have a query which takes large time to execute sometimes.When i checked the speed it was taking 15 seconds.But actually it should run faster than that.When i again checked the query it again ...
2
votes
2answers
149 views

Different queries result in big difference in performance

I have 2 similar functions, different way result in big diff in performance. The PostgreSQL version: 9.2.1. Function 1 create or replace function func_1() returns text as $$ declare v_i ...
2
votes
2answers
1k views

Slow MySQL query with Order and Group by

I have a query that is taking a long time to execute (1.13s). The two tables user_articles and articles have around 25,000 records. This is the query: SELECT `user_articles`.* FROM `user_articles` ...
2
votes
1answer
106 views

Slow Query Gets Even Slower After Indexing

I have a MySQL database with just one table containing 8 million records. I need to query the table to find a single record that matches a specific number: select * from my_table where ...
2
votes
1answer
1k views

how to improve query performance

## Only 1 query which returns 10 rows. ## It took about 66 secs mysql> explain SELECT al.log_id , al.user_id , al.page_name , al.section_name , al.submit_time , al.url , ...