Questions tagged [query-performance]

1

votes
2

answer
19

Views

Combine 2 update querys in a single query - performance

Im programming a favourite function. For example we have multiple adresses and can choose one as favourite. At the moment i got 2 querys to do this job: UPDATE userdata SET maindata = 0 WHERE cid = :id; UPDATE userdata SET maindata = 1 WHERE cid = :id AND id = :id2 LIMIT 1 In the first query i mak...
delato468
0

votes
2

answer
22

Views

Optimize speed of Mysql JOIN query

I have 2 tables called T1 made of 1.6mln of rows and T2 made of 4.6mln of rows with with one-to-many relationship. The CREATE STMT of T1 is: CREATE TABLE `T1` ( `field_1` text, `field_2` text, `field_3` decimal(10,6) DEFAULT NULL, `field_4` decimal(10,6) DEFAULT NULL, `field_4` decimal(10,6) DEFAULT...
UgoL
1

votes
1

answer
40

Views

Performance: Using try (statement) and catch (output error) instead of checking if email already exist

Very often i need to prevent querys to get executed when a value like a email already exist. Until now i searched for the value like that: $checkemailexist = $X['db']->prepare('SELECT uid FROM userdata WHERE uid = :uid LIMIT 1'); $checkemailexist->execute(array( ':uid'=>$uid )); if(empty($checkemail...
delato468
1

votes
1

answer
483

Views

Searching 13 million records using full text search with additional conditions

Performance issue while doing SQL Server full text search with additional conditions. (SQL Server 2012) I am trying to filter the data based on search filters list (table value parameter), which will return all the records for match filters and single record for the filter doesn't have any record fr...
K.Engineer
1

votes
0

answer
99

Views

Query performance on AX vs StudioManagement

Sometimes, not always, I have a performance problem with some actions in our ERP, for example, when users finalize the shipment. So, the only solution is rebuild indexes on inventdim table, but the indexes are not fragmented! :( If I realize a trace I can see the execution plan is reading a lot of r...
TBP
1

votes
3

answer
68

Views

Query optimisation in SQL Server Management Studio

In the query given below, in the where condition the table 'a' is not accessible. How can I make it accessible without affecting the logic of the code? Thanks in advance. select * from Training_Data.dbo.test a cross apply (select * from Training_data.dbo.test_table) b where exists (select 1 from a)
AsRa
1

votes
1

answer
58

Views

Sort values from two tables

I have one table with products and one table with purchases. I want to show a top 10 list of products and order the list by Products that the current user has purchased Sorted by product name Here is a fiddle with example. Notice how it does a full scan of the product table In this example the use...
Patrik
1

votes
1

answer
163

Views

INNER JOIN with WHERE lower(…) like … performance

I am going to find all books which author's name like 'de%' case-insensitive. I write: SELECT * FROM authors a INNER JOIN books b ON books.author_id = a.id WHERE lower(a.first_name) like 'de%' Which results in FULL TABLE ACCESS for books, with cardinality 2037700 and cost 4342. Which is exactly the...
sinedsem
1

votes
1

answer
144

Views

Oracle “optimizes” OR + IN to OR + EXISTS which is very slow

I have the following query: select * from application_log log where log.tag_value = 'xxx' or log.tag_value in (select transaction.id from transaction transaction where transaction.sale_id = 'xxx') order by log.log_date asc; and based on the explain plan the subquery is transformed to something like...
martinsefcik
1

votes
0

answer
706

Views

How to check performance of psql queries

I know that I can get query execution time and planning time. using EXPLAIN ANALYZE. but every time I run this it gives different time(execution and planning) I know it depends on lots of factors like CPU load, memory usage, no off processes etc. Can we tell PostgreSQL that execute the query n no. o...
rahul mishra
1

votes
2

answer
34

Views

Slow Performance in UPDATE Query Based on SELECT TOP 1

I am trying to improve the performance of a query which takes long to run and would appreciate any pointers on what I need to do differently to get the performance to reasonable. (Index and estimated execution plan info is included below.) The table being updated, table1, has under 1000 records, b...
RiSt
1

votes
0

answer
40

Views

MySQL slow join on very basic query, indexes, foreign keys, and everything seem in alignment

I have two schemas, both are identical, one with about 5x less data in it, and the queries are slower by a factor of 120x with the same indexes, foreign keys, database server, and anything else I could think of to check. Here are the two queries: -- Bigger Database, takes .5 seconds, has ~900k and...
1

votes
1

answer
52

Views

How can I optimize this JOIN query?

From pg_stat_statements I have this query that's taking 900 ms on average. What is the recommended way going forward in optimizing this query? I do have indexes but not sure where the bottleneck could be. Here's the EXPLAIN ANALYZE. EXPLAIN ANALYZE SELECT 'listing_variants'.* FROM 'listing_variant...
axsuul
1

votes
1

answer
73

Views

SQL Query optimization without Not In and In using joins

I have a query like below which I want to re-write using joins. This query is taking 4-5hrs to execute in production and returning GBs of data. To restructure it, I checked its execution plan with a small data set in my local DB which shows cost as 782. On this, my peers suggest that we should not...
Nishant Mishra
1

votes
0

answer
93

Views

Postgres cube type distance vector index slower than seq scan

With a 128 dimension column and a distance query as below: CREATE TABLE testes (id serial, name text, face cube); CREATE INDEX testes_face_idx ON testes USING gist(face gist_cube_ops); explain analyse select name from testes order by face cube(array[-0.12341737002134323, 0.013954268768429756, 0.041...
Kobus
1

votes
0

answer
32

Views

INSERT Query with array_search() or SELECT Subquery would be faster?

I have two Arrays with Categories like the following example: $apply_func['categories'] = ['A', 'B', 'C', 'D', 'E', 'F', 'G']; $apply_func['subcategories'] = ['H','I','J','K','L','M','N']; Which I apply a custom function on them like the following: foreach ($apply_func as $name => $values) { $$name...
Toleo
1

votes
0

answer
155

Views

Select large amount of data from SQL server over high latency network

I need to query about 40000 rows from SQL Server 2012 SP1 over network with high latency (ping is about 39ms). When I run that query from SSMS on remote machine, it takes about 1 sec to load everything. However, executing same query on same machine with SqlDataReader takes about 19.1 seconds (in syn...
lorond
1

votes
0

answer
112

Views

Quickly finding users by phone number with Firebase backend

I’m working on an app with a Firebase backend. During sign up I would like to let new users see which of their contacts are already on the app to add them as friends. So basically, use phone numbers to match users with contacts. I am having a big performance headache when querying the database to...
colakollektiv
1

votes
1

answer
52

Views

Optimizing SQL query in Entity Framework for performance improvements

I have a query in Entity Framework which looks like this: var dbModels = context.BusinessRuleExceptions .Where(b => b.ResponseDateTime >= businessRuleSearchParameters.FromDate && b.ResponseDateTime new {x.BusinessRuleName, x.GenNextId}) .Select(p => p.OrderByDescending(pk => pk.ResponseDateTime).Ta...
HappyDeveloper
1

votes
1

answer
163

Views

My remote Postgres query seems to hang forever

I am running the following query on a remote Postgres instance, from a local client: select * from matches_tb1 order by match_id desc limit 10; matches_tb1 is a foreign table and has match_id as unique index. The query seems to hang forever. When I use explain verbose, there is no ORDER BY attache...
Friends_little_black
1

votes
1

answer
79

Views

Explain Plan of Oracle With Clause shows Merge Join Cartesian

I am trying to improve the performance of the query shown below by rewriting a conditional LEFT JOIN as a UNION of INNER JOIN and the base table. I am using Oracle 12c. The tables in concern are ASSIGNMENTS and CLASSES. The requirement is that for a given ITEM_ID value, we need to fetch details from...
Sarath Chandra
1

votes
1

answer
51

Views

MySQL joining tables of different structure

I am having trouble either getting any result or a correct result in the following problem - http://www.sqlfiddle.com/#!9/696ed2/4 Overall goal is to list all transactions of users who are linked together as 'Customers'. So if John is looking at his dashboard, he will see which books Alice (his cus...
nbo
1

votes
1

answer
60

Views

Why additional subquery for minimum id gives 10x faster results?

Ran into strange situation when additional subquery for limiting an id range gives 10 times faster results than same query in WHERE. This query takes around 10s: SELECT COUNT(DISTINCT i.id) AS count FROM items i INNER JOIN items_keywords ik ON ik.article_id = i.id AND ik.deleted = 0 INNER JOIN categ...
Simano
1

votes
1

answer
45

Views

Partial indexing of an embedded document in an array

I have the following data in a collection named articles : { '_id': '1', 'attributes': [ { 'id': 'providercode', 'value': 'code1' }, { 'id': 'otherAttribute', 'value': 'very long value than will be longer than 1024 bytes limit of an index value so I will get 'got unwanted exception: WiredTigerIndex:...
Julien TASSIN
1

votes
3

answer
58

Views

SQL Query optimization -performance issues

I have the following SQL query I want to optimize : select table1.tiers as col1, table1.id_item as col2 from items table1 where (table1.tiers is not null) and table1.tiers'' and table1.id_item = (select max(table2.id_item) from items table2 where table1.tiers=table2.tiers) and table1.n_version_...
user3474488
1

votes
0

answer
33

Views

Filtering based on optional data

I have a messaging system with three tables: Event_Types, Messages, and Event_Subscription. All messages have an event type, and each event type can have 0 or more subscriptions. In the Event Subscription table are a set of columns that can be used to further filter the messages coming in. So if...
DForck42
1

votes
0

answer
42

Views

Simplify forEach statement to update a mongoDB document with nested objects and arrays

I'd like to update the value of the key shouldSendAlert in the following document: { '_id' : ObjectId('5c61c4db46d18e1092c5b024'), 'service' : 'SRVPVD', 'menu' : [ { 'sub' : [ { 'options' : [ { 'item' : [ { 'name' : '', 'actions' : [ { 'name' : 'communicateClient', 'value' : true }, { 'name' :...
MatheusPedro
1

votes
0

answer
43

Views

Resolve Intermittent Performance Issue Due to Parameter Sniffing

I've got a procedure exhibiting performance issues due to parameter sniffing. The procedure is costly but generally executes in an acceptable amount of time amid typical load. However, it will periodically perform poorly. When this happens, we see sessions executing the procedure go parallel. We'll...
Adam
1

votes
1

answer
24

Views

Mongoose - how to replace _id as primary index?

Given the below example schema, is there a way to make the 'email' the primary index on UserSchema instead of the default '_id' (assuming email is always unique)? If possible, would performance be better searching on 'email' when it is the primary index? Also, as an alternative, is it possible to d...
user2741445
1

votes
0

answer
22

Views

MySQL timeseries weighed average using window functions

I have a simple data table for timeseries data, basically only timestamp and value: Field, Type, Null, Key, Default, Extra 'id','int(11)','NO','PRI',NULL,'auto_increment' 'channel_id','int(11)','YES','MUL',NULL,'' 'timestamp','bigint(20)','NO','',NULL,'' 'value','double','NO','',NULL,'' I have devel...
andig
1

votes
1

answer
99

Views

PostgreSQL different query plans on similar servers

On similar Amazon RDS PostgreSQL servers version 9.6.11 with the same databases I get different execution plans for one SQL query. I tried to re-create indexes and run ANALYZE and VACUUM. Nothing helped me. My query: SELECT 'users_employee'.'id', ( SELECT U0.'created' FROM 'surveys_surveyrequest' U0...
Mogost
1

votes
1

answer
25

Views

MySQL 8.0.15 Dam slow select query

Motto of the query is very simple, to find out the last entry on a foreign key column. the pseudo code I can say is select vehicleid , last_journey_point , last_journey_time from journeyTable. here is my SQL statement -- loconumber is a indexed column -- journeyserla is a autonumber primary key int...
balu
1

votes
3

answer
340

Views

Question on partial keys and an index on a database table

Suppose I have two queries on a database table. The queries are defined in terms of fields used in the query: Query1: depends on f1, f2, and f3 Query2: depends on f1, f2, f3 and f4 I remember reading somewhere that the SQL query engine (mySQL in this case) parses the index tree starting from the lef...
morpheous
1

votes
1

answer
2k

Views

SQL server Timeout – only happens very occasionally

I have a web application that occasionally will throw this error…. Exception message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. When I does I am unable to connect to SQL server, even through management studio, it’s says the...
JGilmartin
1

votes
1

answer
1.9k

Views

HBase scan with compare filters has long delay when returning last row

I have HBase running in standalone mode and encountered some problems when I query the tables using the Java API. The table has several million entries (but might grow to billions) which have the following row key metric : -- I use two compare-operation filters to query a specific row range which re...
Tobi
1

votes
1

answer
141

Views

Determine whether field in MySql indexed column is numeric using index semantics

I have a MySql table where I want to get the count of rows where a given VARCHAR column has a numeric value (convertible to number, you know). Right now, I'm doing a simple REGEXP check on this field. Since this table is very large, I'm using a series of indexes to REGEXP as few rows as possible....
Val Akkapeddi
1

votes
1

answer
248

Views

Temp table index/ performance help requested

This is a continuation of sorts from my prior post: RowNumber() and Partition By performance help wanted I have a query that needs significant performance improvement. With the suggestions in the prior post, I eliminated all but one cte in my query, and implemented a temp table, with an index on it....
StatsViaCsh
1

votes
1

answer
477

Views

SQL Server query plan generation optimization

I've got a quite complex view that has about 100 subqueries in it's definition. Simple statement like: SELECT * FROM MyView take 2 seconds to generate the plan and execute query.Subsequent selects when the plan is cached take less than 1ms to execute. This situation would be alright if I had only a...
kubal5003
1

votes
1

answer
121

Views

How do you handle range-date selection in postgresql?

I have a table with hundreds of thousands rows in it, and I have a necessity to select rows that were created in a certain date range, and this might be the only 'where' clause. Is there any acceptable way to increase query performance? Do I need daily(or maybe more often) table CLUSTERing upon b-t...
Alex
1

votes
1

answer
1.6k

Views

Cassandra CQL3 order by clustered key efficiency (with limit clause?)

I have the following table (using CQL3): create table test ( shard text, tuuid timeuuid, some_data text, status text, primary key (shard, tuuid, some_data, status) ); I would like to get rows ordered by tuuid. But this is only possible when I restrict shard - I get this is due to performance. I have...
Nishant Deshpande

View additional questions