The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Questions tagged [query-performance]

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

Filter by
Sorted by
Tagged with
7
votes
1answer
2k views

How do I prevent a massive SELECT from blocking other statements?

Our SQL Azure database contains a massive SELECT statement that runs something like once per day. The heavy SELECT statement contains no locking hints. Recently we observed some stalls in production ...
7
votes
3answers
6k 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 "products"."...
7
votes
1answer
4k views

Memory usage on select for large number of rows

I am trying to dump the entire contents of a large table, from the command line using pqsl, but am running into a problem where memory usage goes up, to the point where the process is killed, before ...
7
votes
3answers
1k 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 ...
7
votes
2answers
7k views

Slow JOIN on tables with millions of rows

In my application I have to join tables with millions of rows. I have a query like this: SELECT DISTINCT "f"."id" AS "FileId" , "f"."name" AS "FileName" , "f"."year" AS "FileYear" , "vt"."value"...
7
votes
1answer
2k views

Improve CASE select performance

I have the following query which is taking up to 2 seconds to run on a data set of less than 100K rows. My SQL is very rusty but this just looks like it's doing a ton more work than it should. Can ...
7
votes
3answers
68 views

Is it possible to have two copies of a table clustered in different arrangements seamlessly?

I have a table with around 100M rows. It only gets data inserted once/day but we need to do selects a lot. The selects are usually simple but need to return 100s of thousands of rows sometimes. It'...
7
votes
1answer
5k views

How to optimise window queries in postgres

I have the following table with approximately 175k records: Column | Type | Modifiers ----------------+-----------------------------+-----------------------...
7
votes
2answers
731 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] ( ...
7
votes
1answer
1k views

Sargable Queries

Very simple question I hope, I'm currently having some scripts I inherited into my project reviewed by the database admins. This has never been done before apparently and lots of issues are being ...
7
votes
2answers
24k 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 L1,L2,...
7
votes
2answers
3k 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 ...
7
votes
1answer
366 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 ...
7
votes
1answer
2k views

SELECT COUNT(*) on a view is orders of magnitude slower than SELECT * on the same view

The View CREATE VIEW [dbo].[vProductList] WITH SCHEMABINDING AS SELECT p.[Id] ,p.[Name] ,price.[Value] as CalculatedPrice ,orders.[Value] as OrdersWithThisProduct FROM ...
7
votes
1answer
2k views

Lead/Lag Implementation in SQL Server 2008 R2: Max memory exceeded

Background I'm trying to establish a "visits" sequence, wherein if an animal is detected at essentially the same place (General_Location) it counts as a single visit, but if it goes elsewhere and ...
7
votes
2answers
371 views

Help finding join without predicate

Much like a related question by swasheck, I have a query that historically has suffered from performance problems. I was looking through the query plan on SSMS and noticed a Nested Loops (Inner Join) ...
7
votes
2answers
2k views

Finite State Machines in SQL

I'd like some input on an issue I'm having. We have a section of code that we repeat throughout our stored procedures, and every time it takes quite some time to process, and when combined the number ...
7
votes
1answer
687 views

Improving a DbGeography query

I am still new to database administration and I'm trying to optimise a search query. I had a query which looked like this and took 5-15 seconds to execute in some cases, and also was causing 100% CPU ...
7
votes
2answers
520 views

Improve Parameterized Query Performance

I have been tasked to improve the performance of a SQL Server 2008 R2 query (within a stored procedure) shown in the query execution plan link below. It currently executes in around 7 seconds and ...
7
votes
1answer
140 views

Strange optimisation effect using WITH construction

I have 4 tables, let's name them as: table A, 15M rows table B, 40K rows, table C, 30K rows, table D, 25M rows (kk - means millions) and I had a legacy query, which was constructed like this: ...
7
votes
2answers
3k views

Outer Join suppressing Index usage?

I have a client program that is executing a query against a View that outer joins one table to another. The performance is bad and I have been trying to tune it by adding the right index. The query ...
7
votes
3answers
1k views

Indexes/optimization for IN, JOIN, GROUP BY, ORDER BY query

I'm working on a query where I need to use IN, BETWEEN, GROUP BY, JOIN, ORDER BY all in one query. I am struggling with performance for that query, so I need help to choose indexes or to make changes ...
7
votes
2answers
321 views

High I/O for part of query

I have a large query that I'm trying to tune. I write lots of queries but haven't done a lot of tuning. I've included a screenshot from SQL Sentry Plan Explorer Free (SSPEF): In the above part of the ...
7
votes
1answer
416 views

Performance of query with a range condition and order by

We have the following table (in SQLite on Android) which a tree structure (Nested Set model) of words and their frequencies: lexikon ------- _id integer PRIMARY KEY word text frequency integer ...
7
votes
3answers
413 views

How to optimize indexes on MySQL query with various sorts

I have an INNODB table levels: +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+---------...
6
votes
2answers
3k views

WHERE vs. HAVING on non-aggregate columns. Advantages / Disadvantages / Irrelevant?

I'm in the process of rewriting queries that no longer pull in all the required data. My question is in regard to a practice I've never seen and haven't found any question on StackExchange that ...
6
votes
2answers
462 views

Running query against DMVs for Query Stats and Execution Plans joined to sys.databases

I originally posted this as a different question in Twitter #sqlhelp, but wanted to post it here differently. I'm trying to create a job that tracks worst performing statements for various statistics ...
6
votes
5answers
4k views

UNION ALL or other ways to return first row of result-set

I am writing a table valued function to geocode an address based on its Suburb, State and Postcode. I try to geocode the address using different methods, in order of decreasing accuracy: Exact match ...
6
votes
3answers
370 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 ...
6
votes
1answer
3k views

Which queries are faster with Postgres than with MySQL InnoDB [closed]

I've read Performance difference between MySQL and PostgreSQL for the same schema/queries. Here is a brief retelling of the article: PostgreSQL tables are heap tables (means no clustered index)... ...
6
votes
2answers
179 views

Eliminate key lookup in execution plan

I have the following query: DECLARE @p__linq__0 UNIQUEIDENTIFIER SET @p__linq__0 = '... some guid ...' SELECT TOP 1 [EventId] AS [EventId], [DateCreated] AS [DateCreated], ...
6
votes
2answers
409 views

Unusual column comparisons and query performance

We have some consultants working on expanding an inhouse data warehouse. I was doing a code review and ran across this pattern in all of the load procs: MERGE [EDHub].[Customer].[Class] AS ...
6
votes
2answers
4k views

best way to avoid redundant aggregate functions and/or group by columns

Suppose I have two tables: Foo: id baz Bar: id foo_id boom So a Foo has many Bars. I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set of ...
6
votes
2answers
2k 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 ...
6
votes
2answers
285 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
338 views

Counting occurrence of words in table is slow

Consider these simplified tables: CREATE TABLE dbo.words ( id bigint NOT NULL IDENTITY (1, 1), word varchar(32) NOT NULL, hits int NULL ) CREATE TABLE dbo.items ( id bigint NOT NULL ...
6
votes
1answer
170 views

Query is fast but becomes sluggish when created as a view

Execution Plan as Query SELECT VP.Branch , VP.ROUTE AS Route , VP.SAPCustomerID , S.CustomerID , S.ProductID , S.Date -- Group by Customer , CustomerQuantity = SUM(...
6
votes
1answer
6k views

In a CASE Statement, which is faster: “WHEN [field] in (0)” or “WHEN [field] = 0”?

I'm looking at a stored proc that another guy has written and every WHEN clause is using an IN statement instead of =. My gut tells me this isn't as fast, efficient, etc. For example: CASE WHEN a....
6
votes
1answer
2k views

How to tune the query on all_constraints in Oracle 12c?

How to tune the following query, as it's taking around 8 seconds: select constraint_name,table_name from all_constraints where r_constraint_name in ...
6
votes
1answer
10k views

Have Postgresql query planner use nested loop w/ indices over hash join

I'm having a problem with some StackOverflow-schema related data loaded into PostgreSQL 9.3.4. I have a query that is taking about 10x longer than it should, due to the fact that it is choosing to use ...
6
votes
4answers
2k 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 ...
6
votes
1answer
4k 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 ...
6
votes
1answer
223 views

SQL Server 2016 concurrency limitations? Tuning for db concurrency

I have a table with ~1 billion timestamped records, and each record holds an FK to a session table (one session per day & 3-500,000 records per day), so finding the records for a given day is ...
6
votes
2answers
1k views

SQL Prove memory pressure - high Buffer Cache Hit Ratio, but low Page Life Expectancy

Our production server (2012, VM + SAN) has 32 GB of RAM, the database size is ~80GB. The application uses TempDB heavily - disk is hit ~100 MBps both reads & writes. Seeing tons of SQL ...
6
votes
3answers
220 views

Query wont compile/run

I have a query that won't run on the production server but it runs on the test server. It seems like the query plan won't compile or its compiling a very bad query plan. I have updated all statistics ...
6
votes
3answers
886 views

Index scan of table with one record with 2.2 billion executions

There's something I'm not sure how to tackle with in the query I have. First, definitions: Courier Services table. With one record. CREATE TABLE [dbo].[CS]( [ServiceID] [int] IDENTITY(1,1) NOT ...
6
votes
2answers
4k views

Why does innodb_flush_method = O_DSYNC provide such a performance boost with SAN-backed storage?

I've seen some people suggest that O_DSYNC should be used with a SAN. The MySQL docs have this to say about O_DSYNC in general: In some versions of GNU/Linux and Unix, flushing files to disk with ...
6
votes
3answers
299 views

Slow query in PostgreSQL selecting a single row from between a range defined in two columns

I have imported a copy of the ip2location_db11 lite database, which contains 3,319,097 rows, and I am looking to optimize a numeric range query, where the low and high values are in separate columns ...
6
votes
1answer
1k views

Why does `SELECT count(*)` return 1?

Some RDBMS's seem to allow a query without a FROM clause, eg: postgres=# SELECT 'foo' bar; ┌─────┐ │ bar │ ├─────┤ │ foo │ └─────┘ But for those that allow it, why would SELECT count(*) not return 0?...
6
votes
2answers
6k 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 ...