Questions tagged [query-performance]

For questions about improving the performance and/or efficiency of database queries.

Filter by
Sorted by
Tagged with
14
votes
3answers
2k views

What is retrieved from disk during a query?

Fairly simple question, probably answered somewhere, but I can't seem to form the right search question for Google... Do the number of columns in a particular table affect the performance of a query, ...
14
votes
2answers
8k views

How to make DISTINCT ON faster in PostgreSQL?

I have a table station_logs in a PostgreSQL 9.6 database: Column | Type | ---------------+-----------------------------+ id | bigint ...
14
votes
3answers
34k 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 ...
14
votes
2answers
900 views

How to track blocking that happen for less than a second - SQL Server

I am trying to troubleshoot a blocking issue which happens for less than a second. The OLTP application is very sensitive and has to have an response time of less than 200ms for some transactions as ...
14
votes
1answer
387 views

Should NOT IN be avoided?

Among some SQL Server developers, it's a widely held belief that NOT IN is terribly slow, and queries should be rewritten so that they return the same result but do not use the "evil" keywords. (...
14
votes
1answer
50k 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 ...
14
votes
1answer
20k 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 ...
14
votes
2answers
37k 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 ...
13
votes
5answers
2k views

Does SentryOne Plan Explorer work?

Does SentryOne Plan Explorer work as advertised and is it legitimate? Are there any gotchas or something to be concerned about? It looks like it shows the hot path in color commpared to SSMS's ...
13
votes
3answers
76k 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 ...
13
votes
3answers
4k views

Query 100x slower in SQL Server 2014, Row Count Spool row estimate the culprit?

I have a query that runs in 800 milliseconds in SQL Server 2012 and takes about 170 seconds in SQL Server 2014. I think that I've narrowed this down to a poor cardinality estimate for the Row Count ...
13
votes
5answers
46k views

Improve delete speed for SQL Server

We have huge production database, its size is around 300GB. Is there any approach to improve performance of a delete query? Right now deletion speed is between 1-10k per minute, it is very slow for us....
13
votes
2answers
1k views

Cardinality estimation problem on inner join

I'm struggling to understand why row estimation is so terribly wrong, here is my case: Simple join - using SQL Server 2016 sp2 (same issue on sp1), dbcompatiblity=130. select ...
13
votes
5answers
46k views

How does paging work with ROW_NUMBER in SQL Server?

I have an Employee table that has one million records. I have following SQL for paging data in a web application. It is working fine. However what I see as an issue is - the derived table tblEmployee ...
13
votes
3answers
714 views

How can I convert the first 100 million positive integers to strings?

This is a bit of an diversion from the real problem. If providing context helps, generating this data could be useful for performance testing ways of processing strings, for generating strings which ...
13
votes
2answers
2k views

SELECT TOP 1 harms query performance; is there a dba-accessible way to overcome this?

In a production application (C# talking to SQL Server 2014 Standard) there's a query that looks like this, below. Most of the time it runs in milliseconds. But occasionally (for certain values of @Id),...
13
votes
3answers
677 views

Why is selecting all resulting columns of this query faster than selecting the one column I care about?

I have a query where using select * not only does far fewer reads, but also uses significantly less CPU time than using select c.Foo. This is the query: select top 1000 c.ID from ATable a join ...
12
votes
3answers
894 views

Emulate user-defined scalar function in a way which doesn't prevent parallelism

I'm trying to see if there's a way to trick SQL Server to use a certain plan for the query. 1. Environment Imagine you have some data which is shared between different processes. So, suppose we have ...
12
votes
1answer
7k views

Difference between Seek Predicate and Predicate

I'm trying to performance tune a query that we have in SQL Server 2014 Enterprise. I have opened the actual query plan in SQL Sentry Plan Explorer and I can see on one node that it has a Seek ...
12
votes
4answers
3k views

Dealing with CXPACKET waits - setting cost threshold for parallelism

As a follow-up to my previous question on perf troubleshooting a Sharepoint site, I was wondering if I could do something about the CXPACKET waits. I know the knee-jerk solution is to turn off all ...
12
votes
1answer
985 views

How can I force a scalar UDF to be evaluated just once in a query?

I have a query which needs to filter against the result of a scalar UDF. The query must be sent as a single statement (so I can't assign the UDF result to a local variable) and I cannot use a TVF. I ...
12
votes
3answers
29k views

MySQL: Which join is better between left outer join and inner join

Which join is better peforming if all of them provides the same result? For example, I have a two tables employees(emp_id,name, address, designation, age, sex) and work_log(emp_id,date,hours_wored). ...
12
votes
2answers
703 views

Can I refactor this query to get it to run in parallel?

I have a query that takes about 3 hours to run on our server--and it doesn't take advantage of parallel processing. (about 1.15 million records in dbo.Deidentified, 300 records in dbo.NamesMultiWord). ...
12
votes
2answers
17k 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 ...
12
votes
3answers
27k views

Why is an aggregate query significantly faster with a GROUP BY clause than without one?

I'm just curious why an aggregate query runs so much faster with a GROUP BY clause than without one. For example, this query takes almost 10 seconds to run SELECT MIN(CreatedDate) FROM MyTable WHERE ...
12
votes
1answer
7k views

Slow order by SQL Server

In my application, I have a query which performs a search in "files" table. The table "files" is partitioned by "f"."created" (see the table definition and has ~26 million rows for the client 19 ("f"...
12
votes
3answers
19k 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 ...
12
votes
3answers
6k views

Very slow simple JOIN query

Simple DB structure (for an online forum): CREATE TABLE users ( id integer NOT NULL PRIMARY KEY, username text ); CREATE INDEX ON users (username); CREATE TABLE posts ( id integer NOT ...
12
votes
1answer
5k views

postgres_fdw performance is slow

The following query on a foreign takes about 5 seconds to execute on 3.2 million rows: SELECT x."IncidentTypeCode", COUNT(x."IncidentTypeCode") FROM "IntterraNearRealTimeUnitReflexes300sForeign" x ...
11
votes
3answers
6k views

SQL Database Structure for RESTful API

I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources. Initially, I though a table per resource would be a good way to go, but I'm ...
11
votes
3answers
8k views

Algorithm for finding the longest prefix

I have two tables. First one is a table with prefixes code name price 343 ek1 10 3435 nt 4 3432 ek2 2 Second is call records with phone numbers number time 834353212 10 ...
11
votes
4answers
88k 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 ...
11
votes
1answer
9k views

PostgreSQL - If I run multiple queries concurrently, under what circumstances would I see a speedup? Under what circumstances would I see a slowdown?

I approach you all humbly as one who is NOT a DBA, and I'm sure that my question is fraught with conceptual shortcomings and "it depends on" land mines. I'm also pretty sure that all of you who ...
11
votes
2answers
3k views

Database for efficient range aggregate queries?

As a simplified example, suppose I have a table like this: seq | value ----+------ 102 | 11954 211 | 43292 278 | 19222 499 | 3843 The table may contain hundreds of millions of records, and I need ...
11
votes
3answers
6k views

Improving the performance of STIntersects

Table T_PIN has 300,000 pins and T_POLYGON has 36,000 polygons. T_PIN has this index: CREATE SPATIAL INDEX [T_PIN_COORD] ON [dbo].[T_PIN] ( [Coord] )USING GEOGRAPHY_GRID WITH (GRIDS =(LEVEL_1 = ...
11
votes
2answers
484 views

SARG cardinality estimate, why not full-scan?

Why there is no full-scan (On SQL 2008 R2 and 2012)? Test data: DROP TABLE dbo.TestTable GO CREATE TABLE dbo.TestTable ( TestTableID INT IDENTITY PRIMARY KEY, VeryRandomText VarChar(50), ...
11
votes
1answer
1k views

Partition constraint not used for joins involving tables partitioned by timestamp

I have a partitioned table structure like: CREATE TABLE measurements ( sensor_id bigint, tx timestamp, measurement int ); CREATE TABLE measurements_201201( CHECK (tx >= '2012-01-...
10
votes
3answers
678 views

Are these two queries logically equivalent?

Are these two queries logically equivalent? DECLARE @DateTime DATETIME = GETDATE() Query 1 SELECT * FROM MyTable WHERE Datediff(DAY, LogInsertTime, @DateTime) > 7 Query 2 SELECT * FROM ...
10
votes
2answers
359 views

Every batch causes a compile

We have a third party application that sends T-SQL statements in batches. The database is hosted on a SQL Server 2016 Enterprise SP1 CU7, 16 cores and 256GB memory. Optimize for Ad-Hoc is enabled. ...
10
votes
2answers
973 views

Do non-relevant columns affect query time of select statements?

I'm just curious. Say you have a table of 1 million records/ rows. select order_value from store.orders Does it make a difference whether that table has 1 field, 2 fields, or 100 fields, in actual ...
10
votes
3answers
567 views

Sort spills to tempdb due to varchar(max)

On a server with 32GB we are running SQL Server 2014 SP2 with a max memory of 25GB we have two tables, here you find a simplified structure of both tables: CREATE TABLE [dbo].[Settings]( [id] [...
10
votes
2answers
14k views

Is there any way to get the estimate cost of executing a query in MySQL?

In PostgreSQL, EXPLAIN or EXPLAIN ANALYZE will show the estimate cost of executing a query. But EXPLAIN in MySQL doesn't provide this information. How can I get the estimate cost without installation ...
10
votes
4answers
2k 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 ...
10
votes
3answers
11k views

PostgreSQL query very slow when subquery added

I have a relatively simple query on a table with 1.5M rows: SELECT mtid FROM publication WHERE mtid IN (9762715) OR last_modifier=21321 LIMIT 5000; EXPLAIN ANALYZE output: Limit (cost=8.84..12.86 ...
10
votes
1answer
12k views

Is it possible to avoid filesort?

Is it possible to avoid 'Using temporary' and 'Using filesort' for the following SELECT query? I can't figure out a way to do it. I tried adding indexes, for both top_expire and program, but didn't ...
10
votes
1answer
1k views

Does SQL Server Query Store capture parameter values?

The new Query Store introduced in SQL Server 2016 is great. It's a nice replacement for much of what I used to do with the older Profiler tool. However, I haven't found a way to capture parameter ...
10
votes
1answer
674 views

What Rules Determine When SQL Server Use a CTE as an “Optimization Fence”?

A while back, Brent Ozar published a post detailing some of the differences between the SQL Server and PostgreSQL: Two Important Differences Between SQL Server and PostgreSQL The first point (“...
10
votes
1answer
244 views

Optimize index on a 2,135,044,521 row table

I have an I/O problem with a large table. General stats The table has the following main characteristics: environment: Azure SQL Database (tier is P4 Premium (500 DTUs)) rows: 2,135,044,521 1,275 ...
10
votes
1answer
569 views

Why does the READPAST hint cause indexed views to be ignored?

I am investigating using the READPAST hint to reduce resource locking in our application's financial subsystem. It seemed like a good way to go because financial transaction records are only ever ...
10
votes
3answers
9k views

TSQL performance - JOIN on value BETWEEN min and max

I have two tables in which I store: an IP range - country lookup table a list of requests coming from different IPs The IPs were stored as bigints to improve lookup performance. This is the table ...

1
2
3 4 5
45