Stack Overflow
Stack Overflow

Questions tagged [sqlperformance]

This tag pertains to performance issues with Structured Query Language (SQL): performance bottlenecks faced when managing data in relational database management systems. The improvements could mean improving SQL queries, changing database design, or changing the way queries are called.

This tag pertains to performance issues with Structured Query Language (SQL): performance bottlenecks faced when managing data in relational database management systems. The improvements could mean improving SQL queries, changing database design, or changing the way queries are called.

615 questions
293
votes
9 answers

Difference between Key, Primary Key, Unique Key and Index in MySQL

When should I use KEY, PRIMARY KEY, UNIQUE KEY and INDEX?
HELP
  • 14,237
  • 22
  • 66
  • 100
214
votes
49 answers

Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc

I've heard that SELECT * is generally bad practice to use when writing SQL commands because it is more efficient to SELECT columns you specifically need. If I need to SELECT every column in a table, should I use SELECT * FROM TABLE or SELECT…
Dan Herbert
  • 99,428
  • 48
  • 189
  • 219
75
votes
9 answers

Database/SQL: How to store longitude/latitude data?

Performance question ... I have a database of houses that have geolocation data (longitude & latitude). What I want to do is find the best way to store the locational data in my MySQL (v5.0.24a) using InnoDB database-engine so that I can perform a…
Timtom
28
votes
8 answers

SQL Server SELECT INTO and Blocking With Temp Tables

So, recently a DBA is trying to tell us that we cannot use the syntax of SELECT X, Y, Z INTO #MyTable FROM YourTable To create temporary tables in our environment, because that syntax causes a lock on TempDB for the duration of the stored procedure…
Mitchel Sellers
  • 62,228
  • 14
  • 110
  • 173
26
votes
6 answers

Should every User Table have a Clustered Index?

Recently I found a couple of tables in a Database with no Clustered Indexes defined. But there are non-clustered indexes defined, so they are on HEAP. On analysis I found that select statements were using filter on the columns defined in…
Sreedhar
  • 29,307
  • 34
  • 118
  • 188
22
votes
3 answers

"select count(id) from table" takes up to 30 minutes to calculate in SQL Azure

I have a database in SQL Azure which is not taking between 15 and 30 minutes to do a simple: select count(id) from mytable The database is about 3.3GB and the count is returning approx 2,000,000 but I have tried it locally and it takes less than 5…
chrisb
  • 1,395
  • 1
  • 14
  • 35
18
votes
3 answers

Performance issue in update query

I have one small doubt in query performance. Basically, I have a table with more than 1C records. sl_id is the primary key in that table. Currently, I am updating the table column status to true (default false) by using the sl_id. In my program, I…
vara
  • 816
  • 3
  • 12
  • 29
15
votes
3 answers

Simple select count(id) uses 100% of Azure SQL DTUs

This started off as this question but now seems more appropriately asked specifically since I realised it is a DTU related question. Basically, running: select count(id) from mytable EDIT: Adding a where clause does not seem to help. Is taking…
chrisb
  • 1,395
  • 1
  • 14
  • 35
12
votes
1 answer

Using tuple comparison in mysql is it efficient?

I have a table of books : CREATE TABLE `books` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `nameOfBook` VARCHAR(32), `releaseDate` DATETIME NULL DEFAULT NULL, PRIMARY KEY (`id`), INDEX `Index 2` (`releaseDate`,…
dop
  • 121
  • 1
  • 5
12
votes
5 answers

are performance/code-maintainability concerns surrounding SELECT * on MS SQL still relevant today, with modern ORMs?

summary: I've seen a lot of advice against using SELECT * in MS SQL, due to both performance and maintainability concerns. however, many of these posts are very old - 5 to 10 years! it seems, from many of these posts, that the performance concerns…
Ben
  • 719
  • 6
  • 25
10
votes
4 answers

How to measure performance of query in oracle

I'm new to Oracle db. I have 2 queries which return the same result set. I want to measure the performance of each of them and choose the better one. How do I do that using Oracle SQL developer? I remember reading that certain tools provide stats.…
Cshah
  • 5,612
  • 10
  • 33
  • 37
9
votes
1 answer

Presto performance tuning, queries are much slower when performed in parallel

I have a presto cluster configured with 12 workers that is being queried by Java applications. The cluster is capable of performing 30 concurrent requests (if there are more, they are queued). The applications might send around 80-100 distinct…
Sasha Shpota
  • 9,436
  • 14
  • 75
  • 148
9
votes
2 answers

SqlDependency performance

I have a web application that use a SQL Server database that is shared with others web applications (over which I have no control). I must know when any of the web apps makes changes to some tables in the database. My first idea was to use…
Johna
  • 2,623
  • 3
  • 21
  • 36
8
votes
1 answer

Index on join and where

Given the next SQL statement: Select * from A join B on A.id1=B.id1 and A.id2=B.id2 where A.year=2016 and B.year=2016 and knowing table A is much smaller than table B, so I need the database first to access A table…
Alex
  • 327
  • 1
  • 3
  • 12
8
votes
2 answers

What tools are people using to measure SQL Server database performance?

I've experimented with a number of techniques for monitoring the health of our SQL Servers, ranging from using the Management Data Warehouse functionality built into SQL Server 2008, through other commercial products such as Confio Ignite 8 and also…
Paul McLoughlin
  • 2,283
  • 1
  • 15
  • 15
1
2 3
…
40 41