Questions tagged [query-tuning]
123 questions
106
votes
1 answer
SQL Server - When to use Clustered vs non-Clustered Index?
I know primary differences between clustered and non clustered indexes and have an understanding of how they actually work. I understand how clustered and non-clustered indexes improve read performance. But one thing I am not sure is that what would…
armulator
- 1,269
- 2
- 12
- 16
14
votes
1 answer
Case of using filtered statistics
I was going through filtered stats in below link.
http://blogs.msdn.com/b/psssql/archive/2010/09/28/case-of-using-filtered-statistics.aspx
Data is Skewed heavily,one region is having 0 rows,rest all are from diferent regions.
Below is the entire…
TheGameiswar
- 27,855
- 8
- 56
- 94
7
votes
1 answer
retrieve SET STATISTICS IO and SET STATISTICS TIME values via ADO.NET?
When executing T-SQL queries via Management Studio, I can use SET STATISTICS IO ON and SET STATISTICS TIME ON to capture statistics for query tuning.
How can I gather the same statistics info when I'm using .NET client APIs to execute T-SQL queries…
Justin Grant
- 44,807
- 15
- 124
- 208
6
votes
1 answer
How to avoid too many joins?
I would like your help to discuss how would I avoid too many joins using a generic approach. Is there a general rule for this?
Currently, I have a very complex query that is joining 11 tables and the performance is very poor (even with indexes and…
Ricardo
- 448
- 4
- 7
- 19
6
votes
1 answer
MySQL Query Tuning - Why is using a value from a variable so much slower than using a literal?
UPDATE: I've answered this myself below.
I'm trying to fix a performance issue in a MySQL query. What I think I'm seeing, is that assigning the result of a function to a variable, and then running a SELECT with a compare against that variable is…
gbro3n
- 6,729
- 9
- 59
- 100
5
votes
1 answer
Cypher: Use WHERE clause or MATCH property definition for exact match?
In Neo4j (version 3.0), the following queries return the same results:
1. MATCH (a:Label) WHERE a.property = "Something" RETURN a
2. MATCH (a:Label {property: "Something"}) RETURN a
While playing with some large datasets, I noticed (and verified…
ADTC
- 8,999
- 5
- 68
- 93
5
votes
2 answers
SQLServer's UNION has better performance than UNION ALL?
I know that UNION ALL supposed to have better performance than UNION (see: performance of union versus union all).
Now, I have this huge stored procedure (with a lot of queries), where The final result is a two section SELECT with a UNION between…
user1271762
4
votes
2 answers
Oracle optimizer hints xmlagg function
I have a function which calls several tables / views etc. with a few xmlaggs of the data.
For some reason I am getting a performance increase when I am pulling in additional information even though this extra information isn't used for the rest of…
bob dylan
- 1,458
- 1
- 14
- 32
4
votes
4 answers
SQL Query Theory Question
I have a large historical transaction table (15-20 million rows MANY columns) and a table with one row one column. The table with one row contains a date (last processing date) which will be used to pull the data in the trasaction table…
Keng
- 52,011
- 32
- 81
- 111
3
votes
3 answers
Is there an alternative to SET STATISTICS TIME which also shows the statements?
SET STATISTICS TIME statement is only useful while developing as with it one can performance tune additional statement being added to the query or UDF/SP being worked on. However when one has to performance tune existing code, e.g. a SP with…
Mediterrano
- 61
- 4
3
votes
2 answers
Create Index on partial CHAR Column
I have a CHAR(250) column being used as a foreign key to a varchar(24) column.
In MySQL I recall that I could create an index specifying column(24) in order to create an index on the leftmost 24 characters. This doesn't appear to be possible on MS…
Kevin Stricker
- 17,178
- 5
- 45
- 71
3
votes
0 answers
sql query takes too long to execute
I am having trouble with this query. It takes 10 hours to execute.
WITH CONSUM AS (
SELECT C.ID_MEASURER,C.ID_MEASURING_POINT,C.CONSUM_TYPE
FROM GCGT_RE_CONSUM_H_E C
WHERE C.DAY BETWEEN 20130201 AND 20130228 AND ACTIVE = 1
…
IGNACIO CEMELI
- 41
- 1
- 5
3
votes
5 answers
Creating index on timestamp column for query which uses year function
I have a HISTORY table with 9 million records. I need to find year-wise, month-wise records created. I was using query no 1, However it timed out several times.
SELECT
year(created) as year,
MONTHNAME(created) as month,
count(*) as…
Vinayak Dornala
- 1,609
- 1
- 21
- 27
3
votes
2 answers
Execution plan quotes spill level 1
Running a very simple query:
SELECT TOP 10 *
FROM WH.dbo.vw_data m
ORDER BY DateCompleted
Takes around 4 minutes.
96% of the execution is taken up by the following:
What does the warning mean and how is it interpretted?
The field DateCompleted…
whytheq
- 34,466
- 65
- 172
- 267
2
votes
2 answers
query error in oracle 11g
My session is hanging when I execute this query in oracle 11g SQL*Plus:
SQL>
declare maxid number;
begin
delete from measure_details
where id in (select id from general_measures where code in ('12345','12346'));
delete from…
jaiswal
- 21
- 2