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

6
votes
1answer
7k views

Why does a query run slower in a Stored Procedure than in the Query window?

I have a complex query which runs in 2 seconds in the query window, but about 5 minutes as a Stored Procedure. Why is it taking so much longer to run as a stored procedure? Here's what my query looks ...
5
votes
3answers
4k views

Is it better to separate a big query into multiple smaller queries?

There are situations which require having really big query joining several tables together with sub select statements in them to produce the desired results. My question is, should we consider ...
5
votes
3answers
8k views

How to make multiple counts in one query?

I count records with queries like SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%something%' SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%another%' SELECT COUNT(col1) FROM table1 WHERE col1 ...
5
votes
3answers
307 views

Which is the most efficient way to run a particular select query?

If I run the following code: select PolicyNumber, MAX(decpageid) as decpageid, Risk from StatRiskDecpages where PolicyNumber = 'AR-0000301132-04' group by PolicyNumber, Risk I get the following ...
5
votes
4answers
2k views

MySQL subquery slows down drastically, but they work fine independently

Query 1: select distinct email from mybigtable where account_id=345 takes 0.1s Query 2: Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above ...
5
votes
3answers
450 views

Speed of MySQL query for the most recent 800 entries in chronological order

I have a MySQL database running on an Ubuntu box that pings 250 clients once a minute (24x7). My column names are: ip_address varchar(16), status varchar(7), timestamp datetime Been running it for ...
5
votes
4answers
25k views

High CPU usage on SQL server - Slow queries [closed]

Our MS SQL Server is using about 95% of the CPU-power. After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how ...
5
votes
2answers
263 views

Cannot tune database any further; what next?

We have a vendor-supplied application. It is under support and we are talking to their developers (we have their largest database size by an order of magnitude), but in the meantime, we have the ...
5
votes
6answers
3k views

Recommendations for MS SQL Server monitoring software or service? [closed]

I have a client with MS SQL Server instances (2000, 2005) scattered across 20-30 servers. I've been engaged to get things organized and will be periodically reviewing the state of the database ...
5
votes
2answers
2k views

Does a TRIGGER improve the performance?

I understand that TRIGGERs are useful, as they take care of subsequent queries, but do they have an advantage from performance point of view too? Replacing two or three queries as INSERT INTO table1 ...
5
votes
1answer
237 views

Logical reads amount difference with almost identical indexes

I'm trying to figure out why two identical queries are taking slightly different times when executed under two indexes that are almost identical, in SQL Server. The mentioned query is quite simple, ...
5
votes
2answers
385 views

Index seek much slower with OR condition compared with separate SELECTs

Based on these questions and the answers given: SQL 2008 Server - performance loss possibly connected with a very large table Large table with historical data allocates too much of SQL Server 2008 ...
5
votes
2answers
802 views

An efficient way to compare two large data sets in SQL

Currently, I'm comparing two data sets, that contain unique StoreKey/ProductKey combinations. The 1st data set has the unique StoreKey/ProductKey combinations for sales between begin January 2012 and ...
5
votes
2answers
3k views

Help to understand explain plan in Oracle

I am running a query in some big tables, and although it runs fine even tough is a lot of data, I'd like to understand what part of it weighs on the execution. Unfortunately I am not too good with ...
5
votes
1answer
483 views

How does order of index affect performance in SQL Server?

I am wondering how does order of index affect performance (ASC, DESC) ? If it affects, why ? And which order should I choose ?
5
votes
2answers
1k views

Optimisation of complex query - 100% cost index seek?

I'm a c# developer, however I'm quite experienced with SQL. I have had a bit of experience with optimising queries but I've come up across one that has me stumped. The query takes about 3 minutes to ...
5
votes
1answer
3k views

Could too many idle connections affect PostgreSQL 9.2 performance?

Some queries on my database server seem to take a long time to respond, and I believe the CPU usage is high. When running ps aux, I see ~250 "idle" connections (which I believe to be too many). I ...
5
votes
2answers
3k views

What can I do to speed up this SQL Query?

I've created this SQL query with the help of @Dems :-) Here is some detail, I tried to make a SQLFiddle but I kept getting errors with my variables... This works in Sql Server 2008... My question ...
5
votes
0answers
85 views

How to handle bad query plan caused by exact equality on range type?

I'm performing an update where I require an exact equality on a tstzrange variable. ~1M rows are modified, and the query takes ~13 minutes. The result of EXPLAIN ANALYZE can be seen here, and the ...
4
votes
5answers
1k views

How can I prove that the following T-SQL is bad for performance?

I have recently inherited a codebase with a large amount of Stored Procedures. The system they are supporting is encountering numerous performance problems which I am looking in to. A number of the ...
4
votes
2answers
5k views

Why won't SQL Server optimize the UNIONs?

Consider these queries (SQL Fiddle): Query 1: SELECT * INTO #TMP1 FROM Foo UNION SELECT * FROM Boo UNION SELECT * FROM Koo; Query 2: SELECT * INTO #TMP2 FROM Foo UNION SELECT * FROM Boo UNION ALL ...
4
votes
3answers
1k views

Some book suggestions for performance tuning

I am in the process of improving performance on our database application. While I am not a DBA, I am pretty comfortable with SQL. I am after a book that can help me understand how queries written in ...
4
votes
2answers
2k 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
3answers
2k views

Query runs slow in test site on first execution. Why?

I found this query by watching a test site with sql profiler for anything taking over 10 seconds. I plopped the code right out of sql profiler and into sql studio, where it was able to execute ...
4
votes
2answers
218 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
3k 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
79 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
93 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
138 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
4answers
677 views

optimizing a compare query

Suppose I have two tables, A and B, and I know that size(A) = size(B). I want to confirm that the data in both tables is the same in three given columns, suppose they are X, Y, and Z (there are no ...
4
votes
1answer
3k 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
228 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
1answer
905 views

Does the order of columns has a significant effect on INSERT?

Do we experience a noticeable difference in performance for these two queries? INSERT INTO table (col1, col2, col3, col4, col5) ... and INSERT INTO table (col5, col3, col1, col2, col4) ... Do we ...
4
votes
2answers
3k 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
2answers
565 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
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
2answers
129 views

Clustered Table Scan Because of “SELECT *”

I have a Records table with over 100 columns and very many rows, and a nonclustered index on 5 fields based on my access paths: CREATE NONCLUSTERED INDEX [IX_Records_CustomerID] ON [dbo].[Records] ( ...
4
votes
1answer
118 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
1answer
203 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 You can refer to this question to see how the query was produced. In the fiddle ...
4
votes
1answer
177 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
2answers
4k 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 ...
4
votes
1answer
689 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
2answers
607 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
272 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
352 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
3k 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
2answers
532 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
5k views

mySQL query optimisation — multiple joins or select … where not in (select distinct…)?

Background I have a Drupal install accessing a large users database (~200k rows) and my "People finder" functionality needs to access all those rows (in a random order). I don't seem to be able to ...