Questions tagged [query-performance]

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

Filter by
Sorted by
Tagged with
10
votes
1answer
700 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
254 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
579 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
10k 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 ...
10
votes
2answers
2k views

Update queries slower after enabling SQL Server Full-Text Index

I have an asp.net website with many insert, update, and delete queries running against my database. A few days ago I create a Full-Text Index on two columns of one of tables. After that I realized ...
10
votes
1answer
18k 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 ...
10
votes
1answer
1k views

query optimization: time intervals

In the main, I've got two kinds of time intervals: presence time and absence time absence time can be of different types (eg breaks, absences, special day and so on) and time intervals may overlap ...
9
votes
3answers
627 views

What should I use? A string or 15 integer fields?

I am developing a student tracking program where I need to store 15 exam marks. I can store the marks as a string and split them up when I need to, for purposes such as performing arithmetical ...
9
votes
5answers
5k views

Optimize Subquery with Windowing Function

As my performance tuning skills never seem to feel sufficient, I always wonder if there is more optimization I can perform against some queries. The situation that this question pertains to is a ...
9
votes
3answers
981 views

Long Running Query on Read-Only Replica that takes moments on the Primary

I've got a 4 node AG setup as follows: VM Hardware Configuration of all nodes: Microsoft SQL Server 2017 Enterprise Edition (RTM-CU14) (KB4484710) 16 vCPUs 356 GB RAM (long story to this one...) max ...
9
votes
4answers
16k 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 I'...
9
votes
3answers
44k views

The fastest way to get the most recent records [duplicate]

I am looking for the fastest way to return the most recent record for each reference number. I quite liked the solution from BrentOzar.com, but it does not seem to work when I add a third condition (...
9
votes
2answers
2k views

Simple DELETE, but complicated execution plan

When I run this delete: DELETE FROM ETLHeaders WHERE ETLHeaderID < 32465870 ...it deletes 39,157 rows. It should be simple because it is deleting on ETLHeaderID which is the clustered index and ...
9
votes
3answers
11k views

Delete millions of rows from a SQL table

I have to delete 16+ millions records from a 221+ million row table and it is going extremely slowly. I appreciate if you share suggestions to make code below faster: SET TRANSACTION ISOLATION LEVEL ...
9
votes
2answers
249 views

Query doesn't respond when adding two columns

When I add two columns to my select the query doesn't respond. The type of column is nvarchar(2000). It's a little unusual. The SQL Server version is 2014. There is only one primary index. The whole ...
9
votes
3answers
14k 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 ...
9
votes
1answer
350 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, ...
9
votes
2answers
525 views

Performance tuning on a query

Seeking help to improve this query performance. SQL Server 2008 R2 Enterprise, Max RAM 16 GB , CPU 40, Max Degree of Parallelism 4. SELECT DsJobStat.JobName AS JobName , AJF.ApplGroup AS ...
9
votes
2answers
3k 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 ...
9
votes
1answer
659 views

Does SentryOne Plan Explorer count the reads in UDFs?

I have a query like this: select dbo.fn_complexFunction(t.id) from mytable t In SQL Sentry Plan Explorer, I noticed I have to run Get Estimated Plan to make the Query Plan include the UDF. When ...
9
votes
3answers
346 views

Help performance tuning master/detail (email like inbox) SQL query

I have spent the last couple days searching, watching videos, and I think I've gotten as far as I can get just fumbling my way through. I am looking for more specific direction given my example below. ...
9
votes
2answers
197 views

Very similar queries, vastly different performance

I have two very similar queries First query: SELECT count(*) FROM Audits a JOIN AuditRelatedIds ari ON a.Id = ari.AuditId WHERE ari.RelatedId = '1DD87CF1-286B-409A-8C60-3FFEC394FDB1' ...
9
votes
1answer
2k views

Recursive sql query performance issue [closed]

This is the sixth time I'm trying to ask this question and it's the shortest one too. All the previous attempts were resulting with something more similar to a blog post rather than the question ...
9
votes
1answer
2k views

How to get accurate query performance?

I'm attempting to improve performance of a stored procedure. When I run the SP, it finishes almost instantly, as if something were cached. I was told to use the following two lines of SQL before ...
9
votes
2answers
1k views

Most of the query plans recreated in the past 4 hours

I have got a problem with my SQL Server database's performance. I have found this tool sp_BlitzCache. After command's executing, I got this statement: You have 92.00% plans created in the past 24 ...
9
votes
1answer
12k views

SQL hourly data aggregation in postgresql

I am a newbie with database so I'm seeking your help with this one. I have a table containing time series data. 2012/01/01 00:10, 10 2012/01/01 00:30, 5 2012/01/01 01:00, 10 2012/01/01 01:40, 10 ...
9
votes
2answers
19k views

Slow Performance Inserting Few Rows Into Huge Table

We have a process that takes data from stores and updates a company-wide inventory table. This table has rows for every store by date and by item. At customers with many stores, this table can get ...
9
votes
1answer
770 views

Slow query times for similarity searches with pg_trgm indices

We added two pg_trgm indices to a table, to enable fuzzy searching by either email address or name, as we need to find users by name, or email addresses which have been misspelled during signup (e.g. "...
8
votes
4answers
7k 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 ...
8
votes
3answers
31k 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 ...
8
votes
2answers
9k 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 I ...
8
votes
3answers
6k views

Can I get SSMS to show me the Actual query costs in the Execution plan pane?

I'm fixing performance issues on a multistatement stored procedure in SQL Server. I want to know which part(s) I should spend time on. I understand from How do I read Query Cost, and is it always a ...
8
votes
3answers
353 views

How can I identify when to create a new table to hold data that can be obtained from a query?

We have a payment table, and agents get commission on payments. Commission is based on a few different factors, such as how long it took to get the payment, so there is some calculations involved when ...
8
votes
3answers
31k views

GROUP BY one column, while sorting by another in PostgreSQL

How can I GROUP BY one column, while sorting only by another. I'm trying to do the following: SELECT dbId,retreivalTime FROM FileItems WHERE sourceSite='something' GROUP BY seriesName ...
8
votes
6answers
4k 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 ...
8
votes
3answers
3k views

Is it better to populate variables using SET or SELECT?

What is the better way (with regards to performance) to set a value to variable? By SET command: DECLARE @VarString nvarchar(max); SET @VarString = 'john doe'; SELECT @VarString; By SELECT command: ...
8
votes
1answer
12k 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 ...
8
votes
1answer
1k views

Do fixed-width rows improve PostgreSQL read performance?

I have a table articles: Table "articles" Column | Type | Modifiers ...
8
votes
1answer
4k 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 (...
8
votes
1answer
5k 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 ...
8
votes
4answers
14k views

MySQL COUNT(*) performance

I have a table with more than 15m rows. I need the total number of rows. So: SELECT COUNT(*) FROM thetable; Which takes around 50s to complete. Explaining gives me Select tables optimized away. I ...
8
votes
2answers
16k 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 ...
8
votes
1answer
7k 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 ...
8
votes
1answer
2k views

Estimated vs Actual rows differences (actual much smaller than estimated) - sort

I'm running a query that is processing some nodes out of an XML document. My estimated subtree cost is in the millions and it seems it all comes from a sort operation sql server is performing on some ...
8
votes
2answers
12k 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 "...
8
votes
2answers
12k 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 ...
8
votes
1answer
176 views

Improve the poor performance of the sys.dm_db_stats_properties DMV

We have some databases with wide tables on COLUMNSTORE compression (21 or 30 COLUMNS) and 2500 partitions (by date). There are about 4000 stats objects in this database, of which most are INCREMENTAL ...
8
votes
4answers
394 views

How to handle queries of 500M+ items

The structure of my data is the following: date: <timestamp> filter_a: <integer> -> range [0, 1000] filter_b: <integer> -> range [0, 1000] filter_c: <integer> -> ...
8
votes
1answer
3k 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 products ...
8
votes
4answers
2k views

MySQL Geo Spatial Query is very slow although index is used

I need to fetch records from a InnoDb Table by distance (must not be exactly) and sort by distance. The table has 10 million records. My best time is so far 8 sec (3 sec without order by distance), ...

1 2
3
4 5
47