Use this tag for questions about improving the performance and/or efficiency of database queries.
0
votes
1answer
15 views
MySQL/MariaDB: Comparing performance between two queries
I am trying to compare the performance between two queries which give otherwise the same results.
The idea is that I want to compare using Windowing Functions available to newer versions to faking ...
5
votes
1answer
104 views
Query is fast but becomes sluggish when created as a view
Execution Plan as Query
SELECT VP.Branch
, VP.ROUTE AS Route
, VP.SAPCustomerID
, S.CustomerID
, S.ProductID
, S.Date
-- Group by Customer
, CustomerQuantity = SUM(...
11
votes
3answers
190 views
Why is selecting all resulting columns of this query faster than selecting the one column I care about?
I have a query where using select * not only does far fewer reads, but also uses significantly less CPU time than using select c.Foo.
This is the query:
select top 1000 c.ID
from ATable a
join ...
-1
votes
0answers
51 views
Select count(*) query very slow response [on hold]
On our dev server running MS SQL 2016 with similar data as our SQL 2016 val server, this query comes in at 1 second (on dev), but our val server comes in around a minute.
SELECT Count(*) AS ...
0
votes
0answers
30 views
Calculating monthly data as it is inserted [on hold]
I'm very new to databases and database design, so apologies if this is a silly question or has already been asked - I was unable to find any answers to this via Google, though.
I am currently ...
0
votes
0answers
53 views
Troubleshooting Slow SQL which runs for milliseconds [on hold]
I am tasked with troubleshooting a stored procedure which normally runs for less than 100ms and sometimes it takes about 800ms to a sec. Now I have experience troubleshooting SQL but not at this ...
0
votes
0answers
23 views
MYSQL - Best way to search for a name or category like in the Yellow Pages
I have a website that is a business directory like YellowPages.com .
I have the following table containing all the companies:
mysql> describe companies;
+-------------+--------------+------+-----+...
0
votes
1answer
20 views
Oracle PK index extremely low performance
There's a table MYHIST containing 42M rows.
The table has a primary key column MYHIST_ID NUMBER(22) with a valid index PK_MYHIST on it, and 50 more columns (of types VARCHAR2, NUMBER and DATE), which ...
0
votes
1answer
25 views
Varying query runtime in tag based reporting and aggregation system
I'm evaluating an approach for reporting in a tag based time tracking system. I'm struggling to understand varying query runtimes or coming up with a different system to produce the desired query ...
2
votes
0answers
106 views
why is update from a join containing zero records taking a minute and 20 seconds?
SQL Server 2008R2. I have a simple join that returns zero rows in less than a second. When I try to run an update on that join, it should also take less than a second but it is taking one minute and ...
0
votes
1answer
49 views
What to consider when generating rows via cross joins vs recursive unions? [closed]
These two queries return the same result (generating a sequence of integers):
1
2
3
4
5
...
16
What performance/other considerations should be taken into account when choosing one or the other?
A (...
5
votes
2answers
93 views
How to teach the optimizer to use index instead of fts with join on data logging master/detail tables?
SQL Server 2012 optimizer does not get it right.
Test case, summary:
This is a simplified test scenario. DDL statements at the bottom.
I have two tables for data logging, A and B. There is a 1:n ...
4
votes
2answers
75 views
MySQL Geo Spatial Query is very slow although index is used
I need to fetch records from a InnoDb Table by distance (must not be exactly) and sort by distance.
The table has 10 million records.
My best time is so far 8 sec (3 sec without order by distance), ...
2
votes
1answer
33 views
Multi-column and single-column index on the same column
I have two indexes on the same column:
store_deleted_idx (store_id, deleted)
store_idx (store_id)
DDL:
CREATE TABLE `stores_shoppers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`store_id` int(11) ...
-1
votes
1answer
53 views
Analysis of queries or stored procedures which ran on the database for last 15 days [duplicate]
We had a site slowdown between 11.57 am and 12:02 pm on 6th Aug 2018,
To trace back the queries which were ran our production database (SQL Server Standard Edition 2012) and which might have caused a ...
0
votes
1answer
32 views
Query slower with index on 14M rows table
DB: Amazon RDS MySQL (OS: Linux, 2 vCPU, Memory: 8GB)
I have a table with almost 14M rows of data.
CREATE TABLE `meterreadings` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`meterid` varchar(16) ...
2
votes
3answers
129 views
Why does using Format vs Right to apply padding cause estimated number of rows to dramatically change?
I was working on a query at work, that had a left join like
cast(cola as varchar) + '-' + right('000' + cast(colb as varchar), 3) = x
The actual execution plan for this query was fairly close, est ...
3
votes
2answers
68 views
Performance considerations regarding Date Vs. Char/Number/Varchar2 in Oracle
I've been working with Oracle 11g for about 7 years and I was asked for a consulting job on a small project.
The problem they had was high CPU usage (more than 90%) on a 11gR2 with Windows2008 as ...
0
votes
1answer
32 views
MySQL Very Slow Performance Using Order by Clause
I have a one table with millions of entry.Below is table structure.
CREATE TABLE `useractivity` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`userid` bigint(20) NOT NULL,
`likes` bigint(20) DEFAULT ...
0
votes
0answers
26 views
MYSQL Multiple table optimization for advanced search
I have a users table where there is the basic information of each user:
CREATE TABLE `users` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`account_type` varchar(10) NOT NULL,
`email` varchar(...
3
votes
2answers
218 views
Clustered Index Scan on partitioned table even when there is covering index
I have a partitioned table which is partitioned based on col1 int. I also have a covering index for the query that I am trying to troubleshoot.
https://www.brentozar.com/pastetheplan/?id=BkNrNdgHm
...
2
votes
1answer
48 views
Hash Match Spill
I've got a hash match spill going on here.
I've updated statistics with FULLSCAN on the tables involved so it isn't that.
Any pointers much appreciated.
https://www.brentozar.com/pastetheplan/?id=...
0
votes
1answer
47 views
Optimize Bitmap Heap Scan
I am trying to understand why my query takes a really long time even though I have the required columns indexed:
SELECT entity_id,
id,
report_date
FROM own_inst_detail
WHERE ( ...
0
votes
1answer
37 views
How to find value for specific dates for a process stored in table
I have a table called as tab where we store all the process running on machine in below format
proc_name start_date Duration
proc1 24-Jul-18 0:0
proc2 24-Jul-18 0:0
...
0
votes
2answers
35 views
How to improve SELECT performance on a big ORACLE view? [closed]
I cannot use a materialized view for the base table data changed frequently. There are about 0.6 million rows in the_big_view. The query I need to do is: SELECT * FROM the_big_view WHERE ...
1
vote
0answers
20 views
Looking for a way to measure trail and error query alternatives
I would like to tune my query but i found that i can not measure the execution time correctly because SQL is using cache and some other optimizations so every time i'm executing the same query i am ...
1
vote
2answers
92 views
Improving statistics on a large “WHERE IN” SQL Server query
Currently, I'm trying to run the following example query:
SELECT [DATA1], [DATA2] FROM TABLE WHERE
[DIMENSION0] IN (1, 5, ... (possibly 10s of numbers)) AND
[DIMENSION1] IN (5) AND
[...
1
vote
1answer
59 views
How to improve performance of query for distinct suppliers in big sales table?
I have two tables, one with store data:
Table "public.store"
Column | Type | Modifiers
-----------+------------------------+-----------
id | integer ...
0
votes
0answers
42 views
Postgres - how to speed up query over trading history data? [duplicate]
I want to store a huge amount of data like this:
DateTime (for filtering dates from ... to ...)
SomeId (for filtering data only for specific id)
Ask
Bid
etc...
First I tried PostgreSQL, but it's ...
2
votes
1answer
75 views
SQL server slow query
I have the following query that is taking a long time to execute (nearly 20 seconds).
SELECT Count(*) FROM "workorder" "wo"
LEFT JOIN "workorder_fields" "wof"
ON "wo"."...
0
votes
1answer
51 views
How to make subtraction without repeating the subqueries? [closed]
I need to efficiently obtain:
players who play more than one world cup and who have never been champion.
primary-key | foreign-key | pk-and-fk
PLAYER<player_id, player_name, country_player, .....
2
votes
0answers
57 views
Plan Guide for the LINQ-SQL Query
I have a query which is slow and it takes 15-20 sec when i run from the application.So i captured that query using SQL profiler and executed on SSMS with OPTION(RECOMPILE) and the query runs faster in ...
4
votes
3answers
263 views
Will this trigger significantly degrade performance?
I have this trigger to (do my best) to prevent brute forcing logins. It has two seperate functions:
If a failed login has been attempted more than 4 times (so read that exactly 5 times) from the same ...
1
vote
2answers
61 views
How to use GROUP BY in an efficient way without losing attributes? [closed]
I am working with PostgreSQL.
My idea is to group players who appear more than 3 times in table-a.
table-a = <player_id, year_world, c, d, e>
With the first subquery I get player_id and the ...
0
votes
0answers
26 views
Is there any performance benefit of using ENUM instead of TinyInt in big data for MySQL?
I read a lot of articles about ENUM. but most of them are comparing ENUM to joins not tinyint.
I need fields with 2-5 different statuses.
In this situation is there any performance or storage benefit ...
2
votes
3answers
133 views
Serial Plan in plan cache
I have a query which was executing slowly earlier. Later I found out that it was not running parallel and this is making the query execution slow.
The query involves a big view and then querying the ...
0
votes
1answer
62 views
Query and index optimization for a table containing 10 million records [closed]
I was trying to retrieve data from a table A to retrieve data for 1 month, which has around 10 million records and 21 indexes built on it, It took me 3 min to retrieve around 20 columns and total of ...
2
votes
3answers
54 views
How MySQL use the InnoDB Buffer?
For example, when using mysqltuner.pl, it told me to increase the InnoDB Buffer Size to > 5GB as my data is 5.6GB
Does it mean: if I allocate enough for the InnoDB Buffer, data will be loaded from ...
1
vote
1answer
26 views
MySQL - Query Main Table Rows and All Children from it's rows on the same query
I have the view_product which contains what i need and i have many child tables for it.
Currently i have a query where i get everything in a single query (unions) and programmatically split and ...
-3
votes
1answer
33 views
Mysql : performance Index on Varchar (100) vs index on date for joining
What will perform better if I join a million rows table to equally big table with a varchar 100 length field vs date field. For simplicity consider both column have same number of match.
-3
votes
1answer
52 views
MY Sql group by taking too much time [closed]
My sql query is taking 8 sec to run that is worst see my query below. I needed total users count month wise logged in on my server from last year
SELECT `updated_at`, COUNT(`id`) AS `total`
FROM `...
2
votes
2answers
29 views
How to accurately measure query time in PostgreSQL (because EXPLAIN ANALYZE does not seem to do it)?
I know that EXPLAIN ANALYZE returns the query time, but it does not appear to do so for my use case. (Unless I am missing something...what could it be...?)
SELECT cars.*, users.*, makers.*, places.*
...
2
votes
0answers
67 views
Problem query - execution plan
I have a problem with a long running query, I'm not a DBA but it's become my problem. I've been doing a lot of reading over the last few weeks but for the life of me can't figure out why this query is ...
0
votes
2answers
53 views
Query Plan - Estimated Row Count Very High
I have a query that populates a temp table with 1692 rows. I had used a table variable but performance was terrible. Now it is improved but still not ideal.
However, I then need to split the TaskName ...
1
vote
0answers
49 views
Scalar function with while loop to TVF
Hi I have a view which is used in lots of search queries in my application.
The issue is application queries which use this view is is running very slow.I am investigating this and i found out a ...
1
vote
3answers
17 views
Same time even after deleting the row
I have a table which has 1 million record.
When I run
Select count (*) from accounts;
It took around 2 min.
But when I deleted 300 thousands record and run the same query it is still taking same ...
0
votes
2answers
68 views
One table to save all records vs create one table each day
One vendor need to store in a database multiple records. There are 5 tables (a table per element - elements are: pressure, flow, volume, temperature and distance). Each table have the following ...
4
votes
1answer
31 views
Will increase in Innodb_buffer_pool_size increases insert query performance?
We have 3 identical database boxes(M1<->M2->S1) with Mysql 5.7.17 installed and having memory 96GB RAM. In these boxes nearly 42GB occupied by Index and data length of Innodb Tables and 38G ...
1
vote
1answer
53 views
Find outlier statistic in SQL server?
I have a stored proc which searches "bad" data in our application database (sql server 2016 enterprise, ~500gb data). "bad" in a business/process sense.
It normally takes 10-30s to execute.
After ...
1
vote
1answer
88 views
Is there a way to speed up a DISTINCT query?
I have a table t in a database (PostgreSQL 10.4):
\d t;
Table "public.t"
Column | Type | Collation | Nullable | Default
----------+------------------------+------...
