All Questions

0
votes
0answers
5 views

work_mem setting for huge queries

I have a table which is ~600Gb in size, I am running copy (select * from tbl where id in (n) order by 1) command for the table and it generates around 200Gb of temp files since my current work_mem is ...
0
votes
0answers
11 views

Aggregate arrays with overlapping values

I have a PostgreSQL table with id and cluster columns with values like this: id clst 1 1 1 2 2 2 2 3 3 4 Each id can belong to several clusters and each cluster my cover several ids. Is it ...
0
votes
1answer
11 views

Another - ERROR: column “da2.dependency_device_name” must appear in the GROUP BY clause or be used in an aggregate function

I am attempting to aggregate dependency_device_name into one string, "Dependency List". Here is the error: ERROR: column "da2.dependency_device_name" must appear in the GROUP BY clause or be ...
0
votes
0answers
7 views

In mysql how to identify what caused a table lock

We are using MySQL 5.6 on rhel 7. Planning to migrate to 5.8 soon. We have a mysql database of size 4TB. It has around 3000 tables with few tables having size of 200-300GB. Sometimes queries takes ...
0
votes
1answer
18 views

Finding all users with access to a table in SQL server

Wondering if there is any script out there to extract a list of all database logins with access to a particular table, whether its given thru a view, database role, etc.
0
votes
0answers
23 views

Configure CPU/IO affinity - SQL 2017 Standard

In our current system – we have 2 Sockets, 16 cores, 32 logical CPU. So, as i understand it is 2 cpu per core? And we are planning to buy sql standard licences for 8 core, i.e., 16 cpu. In this ...
1
vote
1answer
35 views

Best method of creating constant value [duplicate]

I have a query with a hard coded value which is also hard coded into my application (which has a comment saying that the value should be the same as the value hard coded into the query). What would ...
0
votes
0answers
18 views

Optimize MySql Self Cross Join and Grouping - Preorder Tree Traversal Algorithm

I'm on MySql 8, I am trying to implement Preorder Tree Traversal (Referal : https://gist.github.com/tmilos/f2f999b5839e2d42d751) I have 65,000 rows which i traverse, the following T-SQL takes ...
3
votes
1answer
39 views

Show current row “win streak”

I need to show the running win/loss streak per row in a query, so given the table below, the query should return the "expected" column. I've tried some approaches with window functions, but no success....
1
vote
0answers
20 views

Why Mysql Fabric is end of life, and what is an alternative?

Recently I am looking for an application level Sharding solution for MySql, and come across Mysql Fabric, but after some investigation, I find it was already announced to be end of life. It seems ...
1
vote
0answers
25 views

In memory table vs temporary table - benefits if temdb in already on ram disk

In the docs, we have example of how temporary tables can be replaced with in-memory tables and because a memory-optimized table: is stored only in memory, and has no component on disk involves no IO ...
0
votes
0answers
17 views

Is it possible to create a has many relationship between a pivot table and another table?

There is pivot table called order_product like this: order_id | product_id | quantity | price 1 2 3 1500 I need to add a serial for each product, but it's not possible ...
1
vote
1answer
25 views

Postgesql non-deterministic order of result with LIMIT and ORDER BY

I understand that Postgresql may return results in non-deterministic order when the query parameters are non-unique. This is especially a problem when we use LIMIT since this may return entirely ...
0
votes
0answers
16 views

TimescaleDB performance

I am trying to optimize some queries on a huge table but I can't manage to make then faster. I was wondering is it possible to make timescaleDB run any faster? Test results: tracker=> select count(*)...
0
votes
1answer
25 views

Why does year 0 become year 2000?

select date '0-1-1'; 2000-01-01 Year 0 doesn't exist according to Wikipedia, so shouldn't Postgresql give an error instead of jumping to 2000? Year zero does not exist in the anno Domini system ...
1
vote
0answers
8 views

After SSRS to PowerBI Reporting Server migration, parameters are rendering goofy when trying to enter data

We just successfully migrated a SSRS 2016 instance to PBIRS. Users are reporting weird behavior when trying to enter data in a multi value parameter. When they click on a parameter to enter in data, ...
-1
votes
0answers
37 views

SQL Server: how to get statistics sizes? [on hold]

I'd like to query the sizes of all results created by CREATE STATISTICS: the histograms and the density vectors. I tried the following snippet: SELECT t.Name AS TableName, p.rows AS RowCounts, CAST(...
0
votes
0answers
13 views

Unique sets of IDs from table

I want to represent mixture of arbitrary numbers of chemicals. Since the relationship between chemicals and mixtures here is many-to-many, I thought I'd implement it like this (simplified): CREATE ...
0
votes
1answer
22 views

Entity-relationship model decision

I'm learning and practicing with the E-R model trying to design a music industry DBase. With this section of the diagram I have a doubt: I suppose an album can be had by an artist and a band, not ...
1
vote
1answer
8 views

Count orders 12 months back from a certain month group by that month

I want to create a table with months counting orders from current month to 12 months back. I have data with a create_date and an order_id. So i need to convert the date to a format of yyyymm and count ...
0
votes
0answers
8 views

Where is pt-table-checksum finding this hostname?

The Setup: An EC2 instance running MySQL 5.6 locally replicating to RDS MySQL. The VPC IP address range is 172.0.0.0/8. Somehow the slave got out of sync and I'm getting this error when I do SHOW ...
0
votes
0answers
15 views

Machine Requirement for Config Server of a MongoDB Sharding Cluster

I need to deploy a Sharded Cluster of MongoDB. I can imagine that my dataset could be no more than 100GB. Now, I need to set the hardware requirements for the server that should hosts the replica of ...
1
vote
0answers
33 views

is it possible to trim the table output for a specific job?

I have a routine to save the logins and apply them on a different server , and I run this routine from a job that I have scheduled to run everyday. On that job I have specified these 3 things: Log ...
0
votes
1answer
34 views

How to JOIN with missing rows?

I need to JOIN two tables to correct the price with exchange rates as SELECT date, price/rate AS adjusted FROM table1 a JOIN table2 b ON a.date=b.date The problem is that table2 does not have the ...
1
vote
0answers
17 views

SQL Server replication - Could not add new article to publication

I have following script for replication that I am running in source database (subscription is not added yet, target database does not exist yet in target server): use [myDatabase] exec ...
0
votes
0answers
14 views

RDS Aurora postgres temp files removal

A report application generates lots of temp files in RDS aurora postgresql. Temp files loggings shows average 20G files being generated for each query execution. But the query execution not taking ...
0
votes
0answers
21 views

Multiple MySQL database to One database - Bidirectional

So I have a scenario here. I have a requirement which needs both Offline and Online option. When it comes to Online, the app should also be managed by the central admin. So, I have an offline app in ...
-4
votes
0answers
36 views

need help to create an entity reltionship diagram (ERD)

I need to create an ERD for this following statement and only contain 3 tables. The statement is: an office supplier sells many products, each sourced from a different supplier. Here is the flt table ...
0
votes
0answers
64 views

Differences between CTE and subquery select tables

Recently, I had to overcome a slow executing query, basically a job of transferring entries, in my organization. The query was in a format similar to the below select <some columns> from ...
1
vote
1answer
22 views

PostgreSQL (10) show all NOTICE messages

I use https://console.bluemix.net/docs/services/ComposeForPostgreSQL/index.html#about-compose-for-postgresql And for some select with long column alias ex: select id as "...
1
vote
2answers
46 views

Database recovery pending on external drive

I have database with mdf and ldf file residing on external drive. There was a long powercut and the database restarted. Now the database has gone for recovery pending. The database is around 600 gb. ...
1
vote
1answer
27 views

Optimize MySQL InnoDB query for SUM and Order By

I have an MySQL InnoDB table with 1.3M rows and 670MB size: +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------...
0
votes
1answer
24 views

Upgrading PostgreSQL 9.3 to 9.5

I want to upgrade version 9.3 to 9.5 but after some steps I find that I upgraded to version 10, but I'm looking to go from 9.3 to 9.5. When I type the pg_lsclusters command I find myself with three: ...
1
vote
1answer
9 views

opened_tables count increasing rapidly

number of opened tables is increasing rapidly after sometime. MariaDB [(none)]> show global status like 'open%'; +--------------------------+--------+ | Variable_name | Value | +------...
1
vote
1answer
23 views

How should I build the architecture of my MongoDB database?

I have a project about e-Sports. In this project, I have VDS Game Servers for players. With this game servers, our players will play Ranked Games and players will earn Matchmaking Rank Points for ...
0
votes
0answers
30 views

Create indexed view with fluctuating data behind

I´ve heard about indexed-views in MSSQL which can increase the performance. My problem is that I want to create a view which joins round about 5 tables. The tables are constantly updated and are ...
3
votes
0answers
24 views

How are Transaction IDs compared in Postgres?

I'm trying to understand how Transaction IDs are compared in Postgres I have a two questions Transaction IDs (XIDs) are represented in 32 bits. Therefore there can be a maximum of 4,294,967,296 XIDs....
0
votes
1answer
24 views

SQLServer - Multiple PIVOT on same column

I have a table data like the below with Type Regular/Irregular from January to December. I need a query to do this data like the below table: January with below divided into two-jRegular & ...
0
votes
0answers
17 views

is there a way to let mysql join some external data without import those data into db

i have some static data which stored in a json file outinfo= [{out_id:123,out_name:'hello'}] and i have a table, ----tableA---- | id | label | out_id | | 1 | a | 123 | | 2 | b | ...
0
votes
1answer
29 views

Database Design for Cars

I'm designing a database to capture car data. Here's a simplified design for capturing year, make, model, and trim: This should handle data like this: 2017 Ford Mustang GT However, it turns out ...
0
votes
1answer
18 views

What are each of the separate mongodb-org packages for ubuntu?

I am installing mongodb on a local ubuntu vm and I'm using the official Mongo supported packages per the official instructions. However, I don't know what each of these packages contain. I know this ...
1
vote
0answers
33 views

Postgres serializable transactions not enforcing isolation when joins are involved

Edit: I must've pasted something into my psql wrong (like 8 times). This behaves as expected, not as I originally thought I saw. I guess this is still useful as an example? But what I said at the ...
0
votes
1answer
16 views

PHP integration with mongodb for queries

I am trying to query a mongodb collection using php and a dropdown menu to provide the options for actually querying the database. However, this does not return anything. The dropdown menus allow ...
-5
votes
2answers
46 views

SQL operators Doubt

I have a doubt about SQL. Sometimes is necessary to have some sort of ifs in queries, however, there are no ifs in SQL right? So it seems that we should handle these cases with logic operators but I'm ...
0
votes
0answers
10 views

How do I see the mysql status message in bash stdout over ssh?

If I'm passing a command to mysql in bash via ssh: mysql --defaults-file=~/.my.cnf -e "SELECT orderdate FROM marketdata LIMIT 1;" I see the following output: +------------+ | orderdate | +--------...
10
votes
3answers
268 views

Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode

One of my query in was running in serial execution mode after a release and i noticed that two new functions were used in a view which is referenced in the LINQ to SQL Query generated from the ...
0
votes
0answers
18 views

How to restore the missing mysql db schema?

My DB schema is missing/deleted by someone without my knowledge from ASW EC2 windows machine. Seems some unauthorized user entered into the system illegally and did something. Since I can find some ...
0
votes
0answers
23 views

pivot vs multiple columns

I have a table that I've been tasked to look at for optimization (both for entering data through stored procedure, and for views based off the table). The table has (currently) 115 columns, 2 make up ...
0
votes
1answer
23 views

Oracle Data Pump imported table, some columns not selectable

Imported a table into Oracle 12g using data pump. Some of the columns, though present in the table as shown by describe, cannot be selected but they show up in select *. Cannot select them ...
-2
votes
0answers
10 views

removing value and adding in percent of total column

Table_1: Date -- Date format dd/mm/yyyy Campaign_Id -- Integer Variable Campaign Name -- Categorical Variable Conversions -- Continuous Variable Device -- ...

15 30 50 per page