Stack Overflow
Stack Overflow

Questions tagged [query-performance]

For questions pertaining to the measurement or improvement of a SQL query (that deals with speed).

If you're asking "Why is my SQL query so slow?" or something like that, you need to do a little extra homework to get good answers from the Stack Overflow community.

What database?

Put this tag on your question along with the tag for your database: , . , or whatever database you use. Don't use more than one database tag, please. Optimization works somewhat differently from database to database.

How many rows and how fast?

Please figure out approximately how many rows you expect in your result set, and approximately how many rows are in each table involved with the query. Mention those numbers in your question. Please mention how long you hope your query needs to take, and how long it's taking. (That is, tell us what you mean by slow).

Don't oversimplify

Don't try to simplify your question by replacing your actual SELECT column lists with *. Using * in SELECT clauses can be harmful to query performance compared to specifying the list of columns you need.

Show your table definitions, with indexes

In your question include your table definitions and index definitions for each table and view involved in your query. Make sure your question shows all indexes in all relevant tables. When you're asking a performance question, please don't oversimplify your question by omitting indexes or columns you think might be irrelevant. The strangest things can be relevant to performance!

Show your database's explanation of how it satisfied your query

In your question please show the EXPLAIN PLAN output (it's just EXPLAIN in some table server makes and models) for your query.

Reference

Please be aware that query performance optimization can be an inexact science. This is a good reference. http://use-the-index-luke.com/

1932 questions
163
votes
5 answers

Select distinct values from a table field

I'm struggling getting my head around the Django's ORM. What I want to do is get a list of distinct values within a field on my table .... the equivalent of one of the following: SELECT DISTINCT myfieldname FROM mytable (or alternatively) SELECT…
alj
  • 2,839
  • 5
  • 27
  • 37
138
votes
10 answers

SQL Server IN vs. EXISTS Performance

I'm curious which of the following below would be more efficient? I've always been a bit cautious about using IN because I believe SQL Server turns the result set into a big IF statement. For a large result set, this could result in poor…
Randy Minder
  • 47,200
  • 49
  • 204
  • 358
54
votes
2 answers

Why is there a HUGE performance difference between temp table and subselect

This is a question about SQL Server 2008 R2 I'm not a DBA, by far. I'm a java developer, who has to write SQL from time to time. (mostly embedded in code). I want to know if I did something wrong here, and if so, what I can do to avoid it to happen…
Ward
  • 2,802
  • 1
  • 23
  • 38
54
votes
8 answers

How do NULL values affect performance in a database search?

In our product we have a generic search engine, and trying to optimze the search performance. A lot of the tables used in the queries allow null values. Should we redesign our table to disallow null values for optimization or not? Our product runs…
Jakob Ojvind Nielsen
  • 744
  • 1
  • 5
  • 10
53
votes
3 answers

Why is query with phone = N'1234' slower than phone = '1234'?

I have a field which is a varchar(20) When this query is executed, it is fast (Uses index seek): SELECT * FROM [dbo].[phone] WHERE phone = '5554474477' But this one is slow (uses index scan). SELECT * FROM [dbo].[phone] WHERE phone =…
Greg Gum
  • 33,478
  • 39
  • 162
  • 233
51
votes
8 answers

Query times out when executed from web, but super-fast when executed from SSMS

I'm trying to debug the source of a SQL timeout in a web application that I maintain. I have the source code of the C# code behind, so I know exactly what code is running. I have debugged the application right down to the line that executes the…
Michael Bray
  • 14,998
  • 7
  • 42
  • 68
40
votes
9 answers

How to improve performance on a clustered index seek

I'm trying to improve the performance on a query that is running very slowly. After going through the Actual Execution Plan; I found that a Clustered Index Seek was taking up 82%. Is there any way for me to improve the performance on an Index…
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
28
votes
2 answers

Does getting entities with AsNoTracking() disable the automatic call to DetectChanges()?

I've come to know this concept of AsNoTracking(), DetectChanges(), and AutoDetectChangesEnabled very recently. I understand that when fetching records from the database via Entity Framework with AsNoTracking() used, then Entity Framework does not…
Sayan Pal
  • 4,768
  • 5
  • 43
  • 82
22
votes
4 answers

Entity Framework include poor performance

Context We appear to be having an Entity Framework 6.x related issue. We've spent weeks attempting to nail down performance issues and fixed most if not all which we can find/think of. In short, we are seeing a massive drop in performance when…
user8996255
22
votes
2 answers

Conditional aggregation performance

Let us have the following data IF OBJECT_ID('dbo.LogTable', 'U') IS NOT NULL DROP TABLE dbo.LogTable SELECT TOP 100000 DATEADD(day, ( ABS(CHECKSUM(NEWID())) % 65530 ), 0) datesent INTO [LogTable] FROM sys.sysobjects CROSS JOIN…
Radim Bača
  • 10,646
  • 1
  • 19
  • 33
17
votes
5 answers

Loading multiple entities by id efficiently in Hibernate

So, I'm getting a number of instances of a particular entity by id: for(Integer songId:songGroup.getSongIds()) { session = HibernateUtil.getSession(); Song song = (Song) session.get(Song.class,id); processSong(song); } This generates a SQL…
Paul Taylor
  • 13,411
  • 42
  • 184
  • 351
15
votes
3 answers

SQL Server: Is there a performance cost to computed columns?

If I include a computed column in a table, does it place a perfomance burden on it? For example, if I have a table defined as follows: CREATE TABLE users( givenname …, familyname …, fullname AS givenname+' '+familyname ); … does it…
Manngo
  • 14,066
  • 10
  • 88
  • 110
15
votes
2 answers

Linq performance: should I first use `where` or `select`

I have a large List in memory, from a class that has about 20 properties. I'd like to filter this list based on just one property, for a particular task I only need a list of that property. So my query is something like: data.Select(x =>…
Akbari
  • 2,369
  • 7
  • 45
  • 85
14
votes
1 answer

MERGE JOIN on two indexes still causing a SORT?

This is a performance question simplified to join of two indexes. Take the following setup: CREATE TABLE ZZ_BASE AS SELECT dbms_random.random AS ID, DBMS_RANDOM.STRING('U',10) AS STR FROM DUAL CONNECT BY LEVEL <=1000000; CREATE INDEX ZZ_B_I ON…
Davor Josipovic
  • 5,296
  • 1
  • 39
  • 57
13
votes
6 answers

How to check if a particular mapping exists in a one-to-many mapping table

I am having a table that maintains the mapping of an EMPLOYEE_ID to the one or more ROLE_IDs that the employee can be assigned with. The ROLE_ID is a primary key of the ROLE table. Now, I am trying to find if a particular employee is a Team Leader…
Sarath Chandra
  • 1,850
  • 19
  • 40
1
2 3
…
99 100