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

How to correctly return a query result only if it isn't NULL?

I'm writing a PL/pgSQL function that creates a cursor for a query that I need to check whether it returns something. What I'm doing is this: Run the query Check whether it returns something. If it ...
3
votes
2answers
119 views

Should I add multiple secondary indexes to search

I have a search screen on my app where the users can use up to 4 parameters to search. I have written a stored procedure to facilitate the search. Select ID, FirstName, LastName, ...
3
votes
1answer
418 views

Can you have a WHERE clause with a greater than operator on a composite key?

I'm a web developer (not DB admin!) having performance problems processing a 1.7m record table on MySQL. Here is that table : CREATE TABLE `bbcmapimp_pc_raw` ( `postcode` char(8) NOT NULL, ...
3
votes
1answer
60 views

Every query plan statistic says my query should be faster, but it is not

I have a bit of a odd situation. I have two queries that produce the same result, according to SSMS and SQL Sentry Plan Explorer the first query should cost 95% and the 2nd query should cost 5%. ...
3
votes
1answer
303 views

Comparing two queries in SQL Server 2012

I am comparing two queries in SQL Server 2012. The goal is to use all of the pertinent information available from the query optimizer when choosing the best query. Both queries produce the same ...
3
votes
1answer
160 views

SQL Server WHERE clause on CLR method (spatial) peformance

I have a query that joins to a table containing 2 million rows and need to filter on its geometry column by its geometry type (using geom.STGeometryType()). The query takes less than a second to run ...
3
votes
2answers
2k views

PostgreSQL Sequential Scan instead of Index Scan Why?

Hi All I've got a problem with my PostgreSQL database query and wondering if anyone can help. In some scenarios my query seems to ignore the index that I've created which is used for joining the two ...
3
votes
1answer
386 views

Slow SQL Server query using a DB2 linked server in virtual environment

I have a problem with a slow query which uses a linked server. Let me list out my environment first and I'll get to the issue: Virtual Windows 2003 R2 server (fresh deployments on XenServer 6.1 ...
3
votes
1answer
310 views

Query Performance Issue

I have this below query which is behaving bit weird . well Weird in the sense that I couldn't find complete explanation for this. Version : Sql Server 2008 R2 Enterprise No fragmentation . ...
3
votes
1answer
4k views

MySQL: Optimize UNION with “ORDER BY” in inner queries

I just set up a logging system which consists of multiple tables with the same layout. There is one table for each data source. For the log viewer, I want to UNION all the log tables, filter them ...
3
votes
2answers
214 views

Do MySQL sub-queries essentially use as much overhead as separate queries?

If I have a query like this: SELECT name FROM ( SELECT * FROM `table` WHERE id IN (1,40,300) ORDER BY AnotherColumn ) From a bandwidth and overhead standpoint, is that the same thing as doing ...
3
votes
1answer
200 views

Get profiles values where message.sender_user_id = profile.user_id

I have this tables: default_messages CREATE TABLE IF NOT EXISTS `default_messages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL, `message` text ...
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
617 views

Speed up search across multiple columns

We have a table: user_info (user_id,domain_id,first_name,middle_name,last_name,work_place,.,.,.) Now, we have a user search that search across three columns first_name,last_name and work_place. ...
3
votes
1answer
113 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
170 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
1answer
5k views

How to speed up queries on a large 220 million rows table (9 gig data)?

The issue: We have a social site where members can rate each other for compatibility or matching. This user_match_ratings table contains over 220 million rows (9 gig data or almost 20 gig in ...
3
votes
3answers
297 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
224 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
741 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
501 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
2answers
3k views

SQL SERVER - Linked Server and query performance

I have a performance problem related to SQL Server/linked servers/views. I hope you can help me understand what is the best way to do what I want =). I have a database K with 3 linked servers ...
3
votes
1answer
353 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
71 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
582 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
657 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
405 views

Measuring the relative performance of queries given the assertion that “cost” isn't reliable

The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable. Source Does ...
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
0answers
293 views

Optimising a query against sys.fn_dblog

Looking for assistance improving a query. This query is used to review the last N transactions in the LDF file and then add some useful information like the user, start time, query text, etc.. Here ...
3
votes
1answer
417 views

Oracle: How to gather stats in a logical standby database?

I have a Primary and a (logical) Standby Database. The Schema 'APP' gets synced. Now a User has performance issues running a query against some tables in this 'APP' schema of the Standby Database. On ...
3
votes
3answers
664 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
1answer
111 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
166 views

DISTINCT not using an index

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
806 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
3answers
220 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
114 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
147 views

Store categories in database or use hard coded integers?

An address can belong to either the mailing or billing categories. When a user adds an address to a table, should the address category be an independent integer value set by a constant somewhere in ...
2
votes
2answers
98 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
219 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
206 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
2answers
82 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
52 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
1k 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
168 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
218 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
2k 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
313 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
169 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
1answer
145 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 ...