Use this tag for questions about improving the performance and/or efficiency of database queries.

learn more… | top users | synonyms

7
votes
0answers
134 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 ...
6
votes
4answers
5k views

What can speed up a SQL count query?

When doing a count (aggregate) SQL query, what can speed up the execution time in these 3 database systems? I'm sure many things could speed it up (hardware for one), but I'm just a novice DBA, so ...
6
votes
3answers
14k 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 ...
6
votes
2answers
962 views

Parameter Sniffing: Why Does This Become An Issue?

Today, I had an issue with a SPROC Timing Out (took longer than 30 seconds) when it was run from an ASP.NET webpage, but executed quickly when run from SSMS (took 5 seconds). After suspecting ...
6
votes
2answers
162 views

Hindrance in TSQL SVF Call

I have a simply Products data table with 100k records (just generates some random data): set nocount on create table dbo.temp_Products ( [ProductID] int, [Type] tinyint, [Price] float, ...
6
votes
4answers
1k views

Gaps and islands: client solution vs T-SQL query

Can a T-SQL solution for gaps and islands run faster than a C# solution running on the client? To be specific, let us provide some test data: CREATE TABLE dbo.Numbers ( n INT NOT NULL ...
6
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 ...
6
votes
2answers
311 views

Unexpected Implicit conversion in a procedure

I have a procedure like this (simplified): CREATE PROCEDURE test @userName VARCHAR(64) SELECT * FROM member M INNER JOIN order O ON M.MemberId=O.MemberId WHERE M.Username = @userName There is a ...
6
votes
1answer
682 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 ...
6
votes
1answer
260 views

How to Optimise Query

I have a database structure similar to this, CREATE TABLE [dbo].[Dispatch]( [DispatchId] [int] NOT NULL, [ContractId] [int] NOT NULL, [DispatchDescription] [nvarchar](50) NOT NULL, ...
6
votes
2answers
5k views

How can I improve the speed of a query on a 20 million+ row table?

I have a query that is used for getting internet traffic statistics of certain IP addresses. There are separate IP address fields for hosts and blocks of IPs called assignments. The data is stored ...
6
votes
3answers
184 views

Convert date in date list condition to list of date ranges

I want to look for all the records that occur on specific dates. SELECT * FROM table1 WHERE date(column) in ($date1, $date2, ...); However, as many of you, know this kind of comparison doesn't get ...
6
votes
2answers
10k views

Learning to Optimize SQL Queries and Understand Execution Plans - Resources?

I find myself writing more and more SQL queries at work (mostly Oracle 11g, but some SQL Server 2005-2008) and have started creating some pretty complex views for the rest of the analyst team. They ...
6
votes
2answers
1k views

Why does MySQL choose this execution plan?

I have two queries, select some_other_column from `table` order by primary_index_column asc limit 4000000, 10; and select some_other_column from `table` order by secondary_index_column asc ...
6
votes
2answers
635 views

Filter table before join

I have a stored procedure that populates the temp table #employee_benefits with a list of Ids. This table ends up being roughly 10,000 rows long. The query below then selects from a table called ...
6
votes
2answers
3k 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 ...
6
votes
1answer
207 views

How should we handle rows which won't be queried once they are old in PostgreSQL?

We have a table in a PostgreSQL database which is growing on the order of millions of rows per day. Each row consists of: ID Foreign user ID Date and time Other data The date and time aren't ...
6
votes
1answer
240 views

Is it recommended to clear the query plan cache

Pretty much as the title describes. I have just checked a SQL Server and noticed there is a lot of query plan bloat that can be fixed by proper parametrisation. After making the changes to the code, ...
6
votes
3answers
7k 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 ...
6
votes
1answer
359 views

Landed as BI, but databases are a big WTF, what to do?

Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
6
votes
2answers
2k 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 ...
6
votes
2answers
439 views

Postgres performance difference on insert

I was tried to partition one large table, and come with such Postgres behavior that I could not explain. Maybe you will have any suggestions? I went quite straight forward way (i know that it is not ...
6
votes
3answers
682 views

Single query sending and retreiving duplicate data on MS SQL Server?

Very odd thing happening. It would appear that all queries against a particular database in our system are periodically "running slow". Ie "normal speed" for 5 minutes then slow for 5 minutes ...
5
votes
3answers
4k 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 ...
5
votes
2answers
7k 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 ...
5
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 ...
5
votes
3answers
320 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
3k 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
534 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
1answer
784 views

SQL Server, TOP versus ROW_NUMBER

I'm learning about Execution plans and am trying out different queries and comparing their performance and stumbled upon this: SELECT StatisticID FROM ( SELECT StatisticID, ROW_NUMBER() OVER ...
5
votes
2answers
276 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
4answers
1k 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 ...
5
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 ...
5
votes
1answer
1k 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 ...
5
votes
2answers
164 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] ( ...
5
votes
2answers
3k 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
3answers
268 views

Postgres is performing sequential scan instead of index scan

I have a table with about 10 million rows in it and an index on a date field. When I try and extract the unique values of the indexed field Postgres runs a sequential scan even though the result set ...
5
votes
1answer
337 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
1answer
83 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 | | ...
5
votes
1answer
6k 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 ...
5
votes
2answers
5k 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
3answers
71 views

Slow query performance due to temporary file?

Here is the query: SELECT "products".* FROM "products" WHERE (status > 100) AND "products"."above_revenue_average" = 't' AND ("products"."category_id" NOT IN (5)) ORDER BY ...
5
votes
2answers
234 views

how to increase speed of this mysql query?

How I can increase speed of this search query? This search bring news based on time and title and text. each item has own factor. SELECT * , MATCH (`title`) AGAINST ('tax in work') * 1.65 AS ...
5
votes
2answers
136 views

Sporadic execution time causing timeouts [closed]

We have a table with 100 million records holding 24 hour worth of data of our clients' customers actions (actions which are performed on 3rd party sites and replicated from there to our server) The ...
5
votes
1answer
724 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
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 ...
5
votes
1answer
108 views

'SELECT my_table.id' slower than 'SELECT my_table.*'

Running MySQL with InnoDB: I have a SELECT wide_table.* query that I want to refine to SELECT wide_table.id since that's all the calling code needs. Testing it out I've found that the execution time ...
5
votes
1answer
56 views

Joining in query decomposition

I have a question about query decomposition and localization, as in here: When joining 3 tables (here EMP, ASG and PROJ) is there any specifiy order, when to join which table? Why ASG and EMP are ...
5
votes
1answer
1k 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 ...
5
votes
1answer
4k 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 ...