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

4
votes
1answer
77 views

Will these queries return the same results?

I have this simple query: SELECT friend_id FROM default_friend WHERE user_id = 1 And the results are below: +-----------+ | friend_id | +-----------+ | 2250 | | 4901 | | ...
4
votes
1answer
141 views

Drastic performance difference in View Dependencies between SQL Server 2005 and 2008 R2

Background I am a fledgling DBA who was hired in the middle of a migration from SQL Server 2005 x64 to 2008 R2 x64. We also migrated from a local server to a colo virtual server, and moved some of ...
4
votes
2answers
88 views

Take advantage of monotonic columns in PostreSQL

I have a PostgreSQL table with, among the other, two columns named col1 and col2, both of integer type (there are around 10M rows in the table). I want to perform SQL queries like: SELECT * FROM ...
4
votes
1answer
352 views

Pull data from multiple tables in a view or function

There are 3 tables from which I need to pull data, and print it in a specific way. http://sqlfiddle.com/#!15/59481/8 Refer to this question to see how the query was produced. The version in the ...
4
votes
1answer
217 views

Search, filter and group across all columns in a tall and wide dataset quickly [closed]

We have certain datasets which may span 80 to 300 fields, across 300,000 to 35 million rows. For each dataset, we want to produce a web based analysis tool (a web grid) which would allow our users ...
4
votes
1answer
876 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 ...
4
votes
2answers
214 views

Performance on SQL Join

I have a query which takes ages to run mainly due to a join between 2 tables on a PK and FK. The Key is prefixed with 2 letters (which is always the same, RN) followed by 7 digits So RN1234567 for ...
4
votes
1answer
131 views

Where to place non clustered index

Execution plan clearly showing that one of my table used in query is useing Clustered Index Scan. From this node, how can i guess that which columns should be part of my non clustered index key and ...
4
votes
2answers
737 views

Poor performance after changing SQL Server 2008 R2 database

Need some tips on what I can do to track down this problem. My client has a modest sized SQL Server 2008 R2 database (approx 6 gigs in size). Last week I modified the database to add new a single new ...
4
votes
2answers
294 views

Same queries, different servers, different exec plans, stats are up to date

I've got the same query running on two different servers, and getting vastly different performances. I have updated all of the stats on dependent objects and it has not fixed the issue. I'm lost on ...
4
votes
1answer
474 views

MySQL queries are 10 to 100 times slower after OS reboot

I have a MySQL server running on Ubuntu. If I restart the MySQL, everything is fine. If I however restart the OS, my queries take anywhere from 10 times to 100 times as long. The only "solution" to ...
4
votes
1answer
4k 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 ...
4
votes
2answers
1k views

Inner join using an array column

Having trouble indexing and executing a query in O (log n) time. The query includes an INNER JOIN, an ORDER BY, and an equality operation. If I understand the laws of databases correctly, a query can ...
4
votes
1answer
9k 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 ...
4
votes
2answers
612 views

Why is a secondary index chosen over a clustered index for SELECT COUNT(*) …?

In this query: select count(*) from largetable; a secondary index is chosen: mysql> explain select count(*) from largetable; ...
4
votes
1answer
389 views

performance issue caused by IO?

Just curious to get an issue sanity checked . . . I'm trying to ascertain whether a performance issue experienced by users is the result of the app, server it sits on or SQL Server. Looking at ...
4
votes
1answer
1k views

How to properly index hstore tags column to faster search for keys

I imported a large area of OpenStreetMap's planet.osm file into a postgresql database. The database contains a table called nodes. Each node has a geometry column called geom and a hstore column ...
4
votes
1answer
724 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 ...
4
votes
4answers
992 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 ...
4
votes
1answer
192 views

How to optimize: having COUNT(*)>1

Without considering the indexes, Is there a way to re-write this query and get the same result quicker? select fo.OrderNo, fo.OrderItemSeqNo, COUNT(*) from Facts.FactBackOrder fo group by fo.OrderNo, ...
4
votes
1answer
765 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 ...
4
votes
1answer
153 views

In SQL Server, what are the typical symptoms of parameter sniffing?

There is a wealth of information on the DBA.SE about what parameter sniffing is and how it relates to individual query performance circumstances. I was hoping that someone could provide some sort of ...
4
votes
1answer
189 views

Same Query takes 0 seconds on Server A, 7.5 minutes on Server B (same db/hardware/config)

I have a query (sql below) that is nearly instantaneous on 1 server, but takes 7.5 minutes on another virtually (key point) identical server. The estimated query execution plans have one obvious ...
4
votes
1answer
139 views

help required with indexing and query optimzation

After browsing most of the posts and after running out for options I am posting here. I have a table with 76,222,954 (76M) rows. It collects web site traffic based on country. Here is my table ...
4
votes
2answers
552 views

Separate Address Table, Latitude, Longitude

We are designing a DB for a limited functionality social networking application. Users (Registered Users), Retail Outlets, etc are several entities we need to store. We need to store Address of each ...
4
votes
0answers
231 views

Grant users privilege to use QUERYTRACEON 9481

Microsoft suggests using OPTION (QUERYTRACEON 9481) for queries that have "degraded" performance against SQL Server 2014 databases running under compatibility level 120. In my situation, "degraded" ...
4
votes
3answers
1k views

Slow queries on billions-rows-table // index used

Since I'm a young developer and not really skilled in using databases (PostgreSQL 9.3) I ran into some problems with a project, where I really need help with. My project is about collecting data of ...
4
votes
0answers
521 views

SQL server query is slow *without* including execution plan [closed]

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 ...
4
votes
0answers
371 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
227 views

How can I eliminate this costly index seek operation from my query?

I have two tables in Sql Server 2008 that look something like this: Transaction_Details: Dataset, Department, Charge_ID, Transaction_Type, dos_month, post_month, amount ...
3
votes
2answers
641 views

Do I risk losing the benefits of indexing if I have an index on every column?

I use PostgreSQL 9.2. I have a table with ~5 million rows and 150 columns. The table does not change at all (I replace it once a year). Users query this table with all kinds of filters on any some of ...
3
votes
2answers
236 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 ...
3
votes
2answers
435 views

Efficently Remove 90% of a table's data

I am working on a process to remove 90% of a table's data because only 10% is needed for testing. The best way I have found includes storing 10% of the table's rows into a temporary table. Current ...
3
votes
2answers
3k views

Are there advantages to using temporary tables over derived tables in SQL Server?

I read the derived tables have better performance than temporary tables, but anyway many SQL Server developers prefer the second ones. Why? I must do queries with large data (millions records) and I ...
3
votes
2answers
278 views

which one is more efficient query?

I have a table called STUDENT +-----------+-----------+-----------+---------------+ | StudentID | FirstName | LastName | EnrollmenDate | +-----------+-----------+-----------+---------------+ | ...
3
votes
1answer
168 views

Why does SqlSentry Plan Explorer not give the duration of my queries?

I have a large nasty stored procedure in a 13gig database that takes 45 minutes to run when I include the actual execution plan. I save the query plan from SSMS and open in up in SQL Sentry Plan ...
3
votes
2answers
983 views

How does compound index help speed up query?

Say we have a bunch of column in mysql table. Latitude Longitude Say we make an index (Latitude and Longitude) Well, the index will sort by Latitude first and IF the Latitude is the same it'll sort ...
3
votes
2answers
529 views

UPDATE query with JOIN taking forever

I'm not very good with databases so please keep in mind even if the solution sounds like something everyone would have already tried, please post it because I likely have not tried it and it would ...
3
votes
3answers
145 views

How can I improve this query?

About a year back I introduced a query which returns a sort of "Customers Also Purchased" data-set. At the time it ran reasonably fast however, as of late it's become very slow, sometimes taking up to ...
3
votes
2answers
462 views

How do I count rows with two properties in one index scan in SQL?

I have a table Units where I have rows ItemId, ItemCreationDate, ItemUnitsCount. ItemId is a primary key and I have a clustered index over it. I need to output the following: for each day I need to ...
3
votes
2answers
217 views

Slow fulltext search due to wildly innacurate row estimates

Fulltext queries against this database (storing RT tickets) seem to be taking a very long time to execute. The attachments table (containing the fulltext data) is about 15GB. The database schema is ...
3
votes
2answers
49 views

Why is MySQL ignoring my indexes?

I have a query select distinct id from patient where company_id in (1) and (name_last like 'peter%' or name_first like 'peter%' or remote_id like 'peter%') running on a table that looks like this ...
3
votes
2answers
451 views

Efficiently query MAX over multiple ranges

When performing a MIN() or MAX() over a single range covered by an appropriately sorted index, SQL Server does a TOP() and so returns the value after fetching just one row. When the search criteria ...
3
votes
3answers
2k views

MySQL optimization - year column grouping - using temporary table, filesort

I have a transactions table which is having 600,000 records, I need to list the count for the dashboard on financial year basis. The table used is MyISAM. I tried adding index for the transaction date ...
3
votes
1answer
1k views

Query taking long on table with low record count

I have an Oracle flat table that grew to just below 800MB. The table was truncated and new records are being inserted. Currently there are <40 records in the table but a select * takes >30secs. ...
3
votes
5answers
582 views

Query optimization [Oracle]

The Query: select JOIN_RESULT.batch_id as BATCH_ID, JOIN_RESULT.unique_doc_id as DOCUMENT_ID, JOIN_RESULT.brand as BRAND, JOIN_RESULT.message_date_time as UPLOAD_DATE, ...
3
votes
4answers
1k views

Optimize Query with Derived Table

I have the following query select ps.id, ps.title , IFNULL(s.likes,0) as num_likes , IFNULL(s.comments,0) as num_comments , IFNULL(s.ratings,0) as num_ratings , IFNULL(s.views,0) as num_views , ...
3
votes
1answer
1k views

Optimizing large database query (25+ million rows, using max() and GROUP BY)

I'm using Postgres 9.3.5 and I have a large table in the database, currently it has over 25 million rows and it tends to get even larger rapidly. I'm trying to select specific rows (all unit_ids with ...
3
votes
3answers
3k 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 ...
3
votes
2answers
8k views

High CXPACKET and LATCH_EX waits

I am having some performance issues with a data processing system which I am working on. I have gathered wait stats from a one hour peroid which show a large amount of CXPACKET and LATCH_EX wait ...