Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

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
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
2k 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
36k views

query performance gains by removing operator hash match inner join

While trying to apply the contents of this question below to my own situation, I am a bit confused as how I could get rid of the operator Hash Match (Inner Join) if any way possible. SQL Server ...
8
votes
2answers
20k views

Are there advantages to using temporary tables over derived tables in SQL Server?

I read the derived tables have better performance than temporary tables, but anyway many SQL Server developers prefer the second ones. Why? I must do queries with large data (millions records) and I ...
8
votes
3answers
216 views

How can I get running totals of recent rows faster?

I'm currently designing a transaction table. I realized that calculating running totals for each row will be needed and this might be slow in performance. So I created a table with 1 million rows for ...
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
4k 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
2answers
2k 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 ...
8
votes
1answer
11k 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 ...
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
147 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' ...
8
votes
1answer
865 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 ...
8
votes
1answer
202 views

how come a table variable is improving the performance of a query in this circumstance?

for this specific case, that I will try to explain below, using a table variable is performing better than not using a table variable. I would like to know why, and if possible, get rid of the table ...
8
votes
1answer
10k 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 ...
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
4answers
371 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
2answers
4k views

What is causing high CPU usage from this query/execution plan?

I have an Azure SQL Database that powers a .NET Core API app. Browsing the performance overview reports in the Azure Portal suggests that the majority of the load (DTU usage) on my database server is ...
8
votes
1answer
886 views

100% CPU with bad execution plan

I have a massive problem with 100% CPU spikes because of a bad execution plan used by a specific query. I spend weeks now solve with by my own. My Database My sample DB contains 3 simplified tables. ...
8
votes
3answers
1k views

Query against sys.schemas and sys.synonyms runs very slow for one user

Scenario: SQL Server 2014 (v12.0.4100.1) .NET Service runs this query: SELECT name, base_object_name FROM sys.synonyms WHERE schema_id IN (SELECT schema_id FROM sys.schemas ...
8
votes
1answer
418 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
1answer
791 views

What does duration measurement in sql server actually measure?

What precisely does the query duration measure when conducting a profiler trace? I have a stored procedure which performs data access (SELECT), and it is taking an average of around 30 seconds to ...
7
votes
4answers
6k 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 ...
7
votes
3answers
4k views

Why does my query run fast in Environment A, but slow in Environment B?

I have a piece of SQL that seems to run really fast in Environment A, but the exact same query runs really slow in Environment B! Environments are supposed to be the same, so what should I do and/or ...
7
votes
4answers
553 views

DATEADD not producing a SARGable expectation of an index seek

I have a basic [UserActivity] table that captures an ActivityTypeId per UserId and the ActivityDate at which the Activity occurred. I am writing a query/stored procedure that allows for input of the @...
7
votes
3answers
3k views

Warning about implicit convert

I have table with 2 name columns: CREATE TABLE Test ( TestID int identity primary key clustered , Name_Eng nvarchar(50) , Name_Nat nvarchar(50) ) Now I need a query to get this Name column ...
7
votes
3answers
14k 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 ...
7
votes
2answers
16k 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 ...
7
votes
3answers
3k 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 ...
7
votes
1answer
3k views

Better execution plan if parameter is stored in local variable

I have two stored procedures. This one is incredibly fast (~ 2 seconds) CREATE PROCEDURE [schema].[Test_fast] @week date AS BEGIN declare @myweek date = @week select distinct serial ...
7
votes
3answers
1k views

Is there a SQL Server implementation of the Longest Common Substring problem?

Is there a SQL Server implementation of the Longest Common Substring problem? A solution that checks with all rows of a column in SQL Server? I have seen solutions that take two strings as input, but ...
7
votes
1answer
403 views

Query optimizer recommends adding index instead of using existing index

I am trying to determine why the query optimizer in SQL Server is recommending creating a new index instead of using an existing index that appears to be sufficient for the query. First the table. ...
7
votes
2answers
1k views

Parameter Sniffing: Why Does This Become An Issue?

Today, I had an issue with a stored procedure timing out (took longer than 30 seconds) when it was run from an ASP.NET web page, but executed quickly when run from SSMS (took 5 seconds). After ...
7
votes
3answers
28k 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 ...
7
votes
2answers
10k views

Is high Extent Fragmentation a problem?

DBCC SHOWCONTIG scanning 'MyTable' table... Table: 'MyTable' (2048062382); index ID: 1, database ID: 28 TABLE level scan performed. - Pages Scanned................................: 1019182 - Extents ...
7
votes
5answers
2k views

Most efficient way to retrieve a sub-query COUNT grouped by top table?

Given the following schema CREATE TABLE categories ( id UNIQUEIDENTIFIER PRIMARY KEY, name NVARCHAR(50) ); CREATE TABLE [group] ( id UNIQUEIDENTIFIER PRIMARY KEY ); CREATE TABLE logger (...
7
votes
2answers
439 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 ...
7
votes
2answers
609 views

can anyone help me with this awful query plan?

The query: SELECT Object1.Column1, Object2.Column2 AS Column3, Object2.Column4 AS Column5, Object3.Column6, Object3.Column7,Object1.Column8, Object1.Column9, Object1.Column10, Object1.Column11,...
7
votes
3answers
12k views

how to test if XML = '' in sql server?

I am working with sql server 2005 and I had a situation where many values can be passed in a parameter. Based on this: Passing multiple values for one SQL parameter this procedure uses XML as a ...
7
votes
4answers
10k 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 ...
7
votes
1answer
6k views

Query is slow in SQL Server 2014, fast in SQL Server 2012

During the migration of one of our databases from SQL Server 2012 (SP1, CU2) to SQL Server 2014 (SP1), we have experienced some strange issues. One of the queries that completes within seconds on SQL ...
7
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 ...
7
votes
1answer
5k views

Warning about memory “Excessive Grant” in the query plan - how to find out what is causing it?

I am running a query that is giving the warning about a memory Excessive Grant. There are too many tables and indexes used, including a complex view, and therefore it is difficult to add all of the ...
7
votes
1answer
2k views

using $Partition function to improve query performance

I have tables which are partitioned based on a INT column. I see some queries that are using $Partition function to compare partition number instead of comparing the actual field data. For example ...
7
votes
1answer
2k views

When does an execution plan not show in sp_WhoIsActive?

I use Adam Machanic's excellent sp_WhoIsActive procedure to see activity and to log to a table to troubleshoot long running queries. Why does the execution plan not show all the time in the results? ...
7
votes
1answer
9k views

Prevent a single query from hogging all resources

I have a production database server running SQL Server 2008 R2. How can I keep a user query on one database from hogging all the system resources and bogging down all the databases on the server? The ...
7
votes
2answers
7k views

Will altering order of columns in GROUP BY affect performance?

I have a SQL query like this: SELECT A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END), COUNT(D) FROM SomeTable GROUP BY A, B, (CASE WHEN C=0 THEN 0 ELSE 1 END) On a huge dataset the actual execution plan ...
7
votes
2answers
924 views

On SQL Server, is it possible to restrict certain users from using certain functions, operators or statements?

we support a large number of developers and analysts who either do not know much or don't care at all about query performance. We have every day hundreds of poorly conceived queries (also due to ...
7
votes
3answers
866 views

Reasonable way to predict query performance over time

I have a SQL Server 2012 database that I am using to save data from processed files. We read data from a folder, process it with python and save the results into the database. One of the first things ...
7
votes
3answers
10k views

EXEC vs SP_EXECUTESQL Performance

Recently we used a sql code reviewing tool against our database. It is suggesting to use SP_EXECUTESQL instead of EXEC. I know SP_EXECUTESQL helps us to avoid sql injection. Is there any difference ...