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
343 views

Subselect of MAX(Version) takes many minutes, though there are only ~20K records

I have a pretty complicated data model. I can't understand most SQL examples without an explanation of what's being modeled, so I'll try to explain. Mainlines -> Releases -> Overlays -> Calibrations ...
4
votes
2answers
4k views

GROUP BY with MAX versus just MAX

I am a programmer, dealing with a big table which the following scheme: UpdateTime, PK, datetime, notnull Name, PK, char(14), notnull TheData, float There is a clustered index on Name, UpdateTime ...
4
votes
4answers
227 views

Are we a good fit for SQL Server Enterprise and table partitioning?

We currently have a production database that contains millions of records (14 million in the largest table). One function of the database is to provide time sheet information so a single table could ...
4
votes
2answers
264 views

Query tuning to meet timeout settings [closed]

We have an web app with a 30 second time out on search screens. I've analyzed the original query and added a missing index for one of the query's 9 joins. Other things like expected and actual ...
4
votes
2answers
4k views

Is high Extent Fragmentation a problem?

DBCC SHOWCONTIG scanning 'MyTable' table... Table: 'MyTable' (2048062382); index ID: 1, database ID: 28 TABLE level scan performed. - Pages Scanned................................: 1019182 - Extents ...
4
votes
1answer
82 views

Covering Index Question

I have the following query Select Pt.PRODID, PT.INVENTREFID, Inv.ItemName, PT.ItemID, Configid, Pt.QTYSCHED, PT.DLVDATE, Pt.CREATEDDATETIME, pt.SCHEDEND, CASE Left(PT.INVENTREFID, 3) WHEN ...
4
votes
2answers
167 views

Spatial index not used

I have a table with a geometry column. For one record, there is only one Point stored. A spatial index has been created but queries searching for the nearest location do not use this index resulting ...
4
votes
2answers
190 views

Performance of different precedence pseudocolumn solutions with LIMIT

I was wondering about the relative performance of the solutions provided in the answers to a question on stackoverflow, I decided to run some tests. The OP wanted to get the first matching row given ...
4
votes
1answer
4k views

I want to know about the IOPS (I/O Per Second) and How it influences the DB CRUD operation

I want to get a dedicated server for my Database (MySQL) which runs InnoDB Engine. There is this option in InnoDB capacity that you can define the IOPS in it. Let's say that I have a SATA 72K-RPM HDD ...
4
votes
1answer
266 views

What is the name of this type of query, and what is an efficient example?

The purpose is to find a parent, given it's children. For example, say you have a marketing package (aka a "combo"), and want to match it based on the products in it. Example table/data: create table ...
4
votes
2answers
4k views

MySql - How can I speed up this query

I have the following tables: CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `last_name` varchar(255) COLLATE ...
4
votes
3answers
5k 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 ...
4
votes
2answers
683 views

Why is my plan guide not being used?

We recently ran into the tipping point issue and some of our report queries that used to complete execution within a couple of seconds are now taking more than 2 min because the query optimizer simply ...
4
votes
1answer
150 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
2k views

Is there a difference in performance between @date and getdate()?

Usually I use the getdate() function in my where clauses to go back in time. Something like: DOC.DATUM >= DATEADD(DD,-1*SSN_SDO.DANA_ZA_POVRAT,GETDATE()) Will SQL Server 2008R2 perform faster ...
4
votes
2answers
103 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
406 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
235 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
965 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
219 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
134 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
760 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
305 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
2answers
2k 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
11k 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
651 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
95 views

Should I use a subquery to help SQL Server find the correct plan

I have a query (which has components which are built on the fly depending on the selections a user makes in the interface) that runs in SQL Server 2008-R2 that runs in about 100 databases. Users are ...
4
votes
1answer
407 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
2k 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
760 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
1k 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
209 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
804 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
193 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
578 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
1answer
37 views

Fast alternative for “NOT IN”

I have a table A that got a field called id that is the primary key of this table. I also have a table called B which also have a field called id as primary key. Now I want to get all rows from ...
4
votes
0answers
96 views

Why is query thousands of times slower with DESC?

I have a PostgreSQL database (AWS RDS) and ran the following queries in psql: livenet=> EXPLAIN ANALYZE SELECT * FROM outputs ORDER BY height LIMIT 10; Limit (cost=0.57..36.82 rows=10 ...
4
votes
0answers
45 views

6GB Table - 37Mi Rows - 'System.OutOfMemoryException' - SQL 2008 R2

I have a proc, looping through databases( the insert is in a fix database...the select uses the loop ), making inserts (Will hide names for security): insert into Main_Database..Table(16 Fields) ...
4
votes
0answers
542 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 ...
3
votes
2answers
238 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
677 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
103 views

Postgres 9.4.4 query takes forever

We're running Postgres 9.4.4 on CentOS 6.5 and have a SELECT query that has worked for years, but stopped working and hangs after we upgraded from 9.2 (it took a while to notice it, so I don't know if ...
3
votes
2answers
252 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
450 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
4k 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
284 views

which one is more efficient query?

I have a table called STUDENT +-----------+-----------+-----------+---------------+ | StudentID | FirstName | LastName | EnrollmenDate | +-----------+-----------+-----------+---------------+ | ...
3
votes
3answers
147 views

Optimizing a query which runs through all hours in a day

I need help optimizing a query. I'm using PostgreSQL 9.3.4 currently but can upgrade to 9.4 if needed. I have a table with 60+ millions of records which looks like this: ...
3
votes
1answer
182 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 ...