Questions tagged [query-performance]
Use this tag for questions about improving the performance and/or efficiency of database queries.
2,382
questions
2
votes
1answer
3k views
Index Strategies on Text or NVARCHAR(MAX) Fields
I have the following query (simplified for question) I'm trying to speed up for a read only DB ...
SELECT
[sysid]
,[Date]=CONVERT(CHAR, DATEADD(D, [date], '1800-12-28'),101)
,[From]=[from_addr] ...
7
votes
2answers
8k 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 ...
0
votes
1answer
183 views
Creating Indexes to optimize query
I have this query and I want to increase its performance by adding appropriate Indexes.
DELETE
FROM MYTAB1
WHERE MYID1 IN
( SELECT MYID2 FROM MYTAB2 );
I am not familiar with the ...
3
votes
2answers
2k views
How do I count rows with two properties in one index scan in SQL?
I have a table Units where I have rows ItemId, ItemCreationDate, ItemUnitsCount. ItemId is a primary key and I have a clustered index over it.
I need to output the following: for each day I need to ...
5
votes
2answers
1k views
Why is a secondary index chosen over a clustered index for SELECT COUNT(*) …?
In this query:
select count(*) from largetable;
a secondary index is chosen:
mysql> explain select count(*) from largetable;
+----+-------------+------------+-------+---------------+------+------...
3
votes
2answers
4k views
How expensive are SELECT FROM LIKE statements from a design perspective?
I have a situation running out of control regarding an ever increasing number of small static files I need to access randomly in a large directory structure. I MUST reduce the number of those files ...
3
votes
3answers
5k views
Is it normal to use many Triggers?
I have many so-called summary columns throughout my database to count number of rows in other tables. For example, counting the number of comments by a user (a column in the user table). Currently, I ...
4
votes
1answer
1k views
Measuring the relative performance of queries given the assertion that “cost” isn't reliable
The cost field is a comparative cost that is used internally to determine the best cost for particular plans. The costs of different statements are not really directly comparable.
Source
Does this ...
1
vote
3answers
1k views
Checking if the query is made async or sync on SQL Server 2008 R2
I have tried to make async calls to my SQL Server instance as you may see on my SO post:
ASP.NET MVC 4 asyncronous database call: never returns the HTTP response
I did a benchmarking and the sync ...
8
votes
3answers
340 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 ...
6
votes
1answer
7k views
mySQL query optimisation — multiple joins or select … where not in (select distinct…)?
Background
I have a Drupal install accessing a large users database (~200k rows) and my "People finder" functionality needs to access all those rows (in a random order). I don't seem to be able to ...
3
votes
2answers
3k views
Copy huge amount from 1 table to other
I have two tables with same structure I need to copy over all tables from table A to table B, the problem is that some records from table A already exist in table B so that made the Import Fail.
So I ...
6
votes
1answer
4k views
Does the order of columns has a significant effect on INSERT?
Do we experience a noticeable difference in performance for these two queries?
INSERT INTO table (col1, col2, col3, col4, col5) ...
and
INSERT INTO table (col5, col3, col1, col2, col4) ...
Do we ...
12
votes
3answers
27k 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 ...
28
votes
3answers
26k views
Are Views optimized when I add a WHERE clause to them?
Does it make a difference if you filter a View inside or outside the View?
For example, is there any difference between these two queries?
SELECT Id
FROM MyTable
WHERE SomeColumn = 1
Or
SELECT Id
...
16
votes
1answer
2k views
Monumental difference in execution time between queries when using RECOMPILE query hint
I have two almost identical queries running on the same SQL Server 2005 instance:
The first one is the original SELECT query as generated by LINQ (I know, I know... I'm not the application developer, ...
2
votes
4answers
3k views
Remove sub-string from WHERE clause
I have a stored procedure that is using the SUBSTRING function in the WHERE clause.
SELECT DISTINCT
ColumnBB,
ColumnCC
FROM TableAA WITH (NOLOCK)
WHERE SUBSTRING(ColumnAA, 1, 17) = @VariableA;
...
3
votes
3answers
4k views
Speed up search across multiple columns
We have a table:
user_info (user_id,domain_id,first_name,middle_name,last_name,work_place,.,.,.)
Now, we have a user search that search across three columns first_name,last_name and work_place.
If ...
2
votes
2answers
999 views
Attempting to avoid SORT in query planner
I have a query that is used VERY frequently in my application. Without modifying the query itself, I'm trying to optimize it by toying with different indexing schemes. Here's the query (basically):
...
8
votes
4answers
10k views
MySQL subquery slows down drastically, but they work fine independently
Query 1:
select distinct email from mybigtable where account_id=345
takes 0.1s
Query 2:
Select count(*) as total from mybigtable where account_id=123 and email IN (<include all from above ...
2
votes
1answer
608 views
Oracle ash report
Recently, i have been handed over with an ash report from DBA.
To me this report is like french. I have no idea what it is about and what all is written in this report. Can someone please guide me in ...
1
vote
1answer
843 views
Oracle Benchmarking (SQL_NO_CACHE …)
I want to benchmark some queries on an oracle System from a JDBC application.
However, the database seems to cache at some point in time. However, this is not intended in this case.
Is there ...
2
votes
1answer
307 views
Query tuning - SQL Server
One of our developers are trying to run the below query on a development server, which involves pulling data from a linked server, production. The query ran for more than 14 hours before it was ...
0
votes
1answer
145 views
Help me to better design my query so I can gain more performance
I need to find so called first entry in database for items regarding their partners.
I wrote query and it is giving me correct results but it sucks with performances.
I suspect that I have missed ...
2
votes
1answer
172 views
MySQL optimization question — how to speed up a simple query
I have a very simple database, one table with five columns (one INTEGER key, two VARCHAR, a DATE, and a DOUBLE). The table contains 909,000 records.
A simple query like
SELECT t.price
FROM commoddb....
0
votes
1answer
491 views
Returning Multiple Result-Sets or Single XML Blods
I've been writing some new Stored Procedures, and find myself returning between 6-8 result-sets in a few cases. I've always worked under the assumption that there were certain inefficiencies with ...
1
vote
2answers
141 views
Improving MySQL efficiency
I have a table now containing over 43 million records. To execute SELECT, I usually select records with the same field, say A. Will it be more efficient to divide the table into several tables by ...
4
votes
1answer
2k views
Can you have a WHERE clause with a greater than operator on a composite key?
I'm a web developer (not DB admin!) having performance problems processing a 1.7m record table on MySQL. Here is that table :
CREATE TABLE `bbcmapimp_pc_raw` (
`postcode` char(8) NOT NULL,
`...
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 ...
2
votes
1answer
594 views
Rewriting a query
I have a database with the following structure (star schema):
facttable:
pros integer
mena integer
me integer
vahet double precision
slutttp timestamp without time zone
Structure of each ...
4
votes
2answers
5k views
MySql - How can I speed up this query
I have the following tables:
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`last_name` varchar(255) COLLATE ...
4
votes
2answers
1k views
Same queries, different servers, different exec plans, stats are up to date
I've got the same query running on two different servers, and getting vastly different performances. I have updated all of the stats on dependent objects and it has not fixed the issue. I'm lost on ...
