Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Join us in building a kind, collaborative learning community via our updated Code of Conduct.

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 ----------+------------------------+------...