Questions tagged [query-performance]

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

Filter by
Sorted by
Tagged with
15
votes
1answer
9k 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 ...
15
votes
1answer
2k views

How does SQL Server know predicates are correlated?

While diagnosing SQL Server 2008 R2 queries with poor cardinality estimation (despite simple indexing, up-to-date statistics, etc.) and hence poor query plans, I found a perhaps-related KB article: ...
15
votes
1answer
417 views

Poor performing subquery with date comparisons

When using a subquery to find the total count of all prior records with a matching field, the performance is terrible on a table with as little as 50k records. Without the subquery, the query executes ...
15
votes
1answer
53k 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 ...
15
votes
2answers
2k views

Join query taking 11 mins to run on 300,000 rows table

Below query is taking more than 11 minutes to execute. SELECT `c`.*, `e`.`name` AS `employee_name`, `e`.`emp_no`, `d`.`code` ...
14
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 ...
14
votes
3answers
93k 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 ...
14
votes
4answers
27k 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 ...
14
votes
3answers
3k views

Improve performance of query using IN()

I have the following SQL query: SELECT Event.ID, Event.IATA, Device.Name, EventType.Description, Event.Data1, Event.Data2 Event.PLCTimeStamp, Event.EventTypeID FROM Event INNER JOIN ...
14
votes
3answers
36k 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
2k 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 ...
14
votes
3answers
826 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 ...
14
votes
2answers
1k 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
390 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
21k 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
40k 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
3answers
5k 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
3answers
36k 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). ...
13
votes
5answers
47k 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
14k 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 ...
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
1answer
6k 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 ...
13
votes
3answers
2k 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
992 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
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
1k 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
1answer
14k 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 ...
12
votes
2answers
832 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
1answer
11k 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 ...
12
votes
2answers
18k 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
7k 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 = ...
12
votes
1answer
9k 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
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 ...
12
votes
3answers
28k 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
3answers
22k 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
8k 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
301 views

Why Does the SQL Server Query Optimizer Not Convert an OUTER JOIN to an INNER JOIN When a Trusted Foreign Key Is Present?

It appears that the SQL Server query optimizer does not translate an OUTER JOIN into an INNER JOIN when the join column in the first table is defined as NOT NULL and has a trusted foreign key ...
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
9k 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
89k 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
2k 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 ...
11
votes
1answer
796 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 (“...
11
votes
2answers
492 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
20k 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 ...
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-...
11
votes
3answers
12k views

Slow queries on billions-rows-table // index used

Since I'm a young developer and not really skilled in using databases (PostgreSQL 9.3) I ran into some problems with a project, where I really need help with. My project is about collecting data from ...
10
votes
4answers
8k views

Which is faster, SUM(CASE) or CTE PIVOT?

There are two types of ways to perform a PIVOT. Before SQL Server 2005, when PIVOT was introduced, most people did this: SELECT RateID SUM(CASE WHEN RateItemTypeID = 1 THEN UnitPrice ...
10
votes
3answers
698 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
3answers
36k views

Get table name from union query?

This is my query SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename1 where Active =1 union SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename2 ...
10
votes
2answers
416 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. ...

1
2
3 4 5
51