All Questions

1
vote
1answer
58 views

How to Prevent heavy queries on one database, affecting the performance of other databases on same server?

I have a linux server running just Postgres with PostGIS Extension. There are several databases on this instance of the Postrges Server. One of the databases has some computationally intensive ...
0
votes
0answers
10 views

MySQL query is slow - Need to understand EXPLAIN output

Dear Database Administrators community, I have a query which I would like to find out what causes it to be slow and what can I do to improve it. This is the query I am using: select ereturn1_....
0
votes
0answers
20 views

MySQL automatic import of (huge) csv files fails using mysqlimport

For testing purposes, I have to insert large Time Series data on a MySQL database, running on a Windows remote server. Firstly, the (very basic) table: CREATE TABLE 'sensor_data' ( '...
0
votes
0answers
15 views

Improving/ Optimizing Performance Query in MySQL PHP

I have a invoices, invoices_items, order, order_items. Invoices and Orders tables contains around 1 Millions records. Invoices_items and Orders_items tables contains more than 2 Millions records. ...
0
votes
0answers
12 views

PgAdmin3 connection to database failed with no error messages

I have a local postgres database with PgAdmin3 LTS by BigSQL on my laptop that until today I could access without any problems. When I tried to access it today by typing the password I get only a "...
0
votes
0answers
24 views

Replication Alternatives for Contained Databases

My team and I are in the process of designing a new application using the microservice architecture approach. Each application is going to to be responsible for releasing changes to its own database ...
0
votes
0answers
14 views

mysql: make the server fail instead of just giving warnings

I have a mysql 5.7 server. I am trying to import some .csv files. Sometimes it may so happen that these are malformed. Here is a sample case: - 'mysql: [Warning] Using a password on the command ...
0
votes
0answers
12 views

Ιndexed view after replication

I am trying to find a smart (no redundunt dataflow) way to aggregate data on views/tables to be used from our reporting tools. One given is that the reporting database is different from the oltp one, ...
4
votes
1answer
184 views

SQL Server: are backups between same major/minor version but different build compatible among them?

For example a backup executed on SQL Server 10.50.6000.34 can always be restored on SQL Server 10.50.4000.0 or 10.50.2500.0. All of them indicates an SQL Server 2008 R2 (10.50.xxxx.xx) but different ...
-1
votes
1answer
27 views

Does Lazy Writer spend resources on flushing out pages from the buffer pool?

In MS SQL Server, does Lazy Writer spend resources to make pages out of the buffer pool after periodically checking free buffer list and marking some clean pages as free? Or free marked clean pages ...
0
votes
0answers
25 views

Kimball Property Data Warehouse Modeling

I have many different flat files. We have Property Owners, who own Properties, which are under a Tax Assessment (Tag Code). Property Owners flat file does not have SSN/Id, so sometimes same property ...
-1
votes
0answers
25 views

Showing the report date wise in SQL SERVER REPORTING SERVICES

I have MS SQL database having multiple records for each date. Now I want to fetch these data and prepare a report showing the sum of all multiple records in a single row for each date. Could anyone ...
-1
votes
1answer
40 views

Empty closing XML tag instead of self-closing tag

My query is USE ... SELECT @JNo=jour_no from WB_PMS..tblJournal WHERE jour_scode=@Jcode IF EXISTS (SELECT 1 FROM WB_PMS..tblArticle WHERE jour_no=@JNo AND art_no=@ArtNo) BEGIN SET @XML= ...
0
votes
0answers
33 views

Some question about FBI and Index on unique column

Anyone can explain to me these issues: Which function-based-index(FBI) should I create and not? I heard from someone active in this forum that we should avoid create FBI as RTRIM, TO_CHAR, UPPER, ...
0
votes
0answers
18 views

Is it possible to queue PostgreSQL logical replication messages from multiple sources?

I want to use multiple "forward" PostgrSQL instances which serve distinct applications but share overlapping subsets of the same schema to update a single master which holds a full version of the ...
0
votes
0answers
18 views

SQL Throw with TableErrorLog

I read Raiserror is being deprecated in SQL Server, our team uses an error log table. How would I change our previous code to utilize throw? I want to learn the appropriate modification for legacy ...
0
votes
1answer
12 views

MySQL synchronization

I have a cloud-based MySQL instance (slave) and am planning a solution where a remote instance (master) will be based on a ship. The ship will go in and out of international waters and therefore will ...
0
votes
1answer
20 views

Sqlplus not using NLS_LANG env variable

I am trying to login to an Oracle 10g database vía sqlplus. In the nls_session_parameters I need NLS_DATE_LANGUAGE in American, but I always have Spanish. In the database server my NLS_LANG is ...
0
votes
1answer
22 views

Creating login/user for Always on Environment

How can i use by making changes to below 2 scripts to run in the environments with Always ON enabled. Below are working fine but fails for an always on enabled instance or AG with readable secondaries....
1
vote
0answers
22 views

Modelling a database for a shopping list - should I represent the scenario with a “redundant” relationship?

[I may provide some pictures later] I'm facing this issue in modeling an entity-relationship diagram. I'll have a shopping list, where for each item the user inserts, it'll be associated with one ...
15
votes
1answer
1k views

Why Do My Nonclustered Indexes Use More Space When I Delete Rows?

I have a large table with 7.5 billion rows and 5 indexes. When I delete roughly 10 million rows, I notice that the nonclustered indexes seem to increase the number of pages they're stored on. I ...
1
vote
2answers
44 views

How to cast (a) a date value recorded as a VARCHAR to (b) a specific DATE format for (c) use in an ORDER BY clause

How would I cast a column value (a "non-MySQL" DATE string retained as a VARCHAR) to a "MySQL date" so it can be used for sorting? I have dates in the format of 06-02-2019 (6 feb) but MySQL itself ...
1
vote
1answer
36 views

How to (a) rewrite a query that JOINs three tables and (b) make its execution more efficient

I have three tables (A, B and C) with various data regarding mining facilities in them. But only one table (C) has two columns with coordinates. My final goal is to produce a table which pulls similar ...
1
vote
5answers
292 views

Filter by using in for SQL Server

I have the below table Col1 Col2 1 ACA,ABS,PHR 2 PHR 3 MCM 4 ABC Now I want to filter the data from this so if I have a filter parameter as say 'ABS,MCM' I ...
-1
votes
2answers
143 views

Queries key look up

SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col25... FROM Tabl1 WHERE ( Col30 IS NOT NULL AND Col28 <> @P0 AND DATEDIFF( dd, Col31, ...
1
vote
2answers
24 views

How to implement a dynamic referral program?

Example: Currently, we will provide 100 credits each to both the referrer and referred. Then in the future, we might want to implement a different referral program. Such as change credits to 200 ...
0
votes
0answers
43 views

High Availability Group configuration failure

Trying to configure a High Availability Group using SQL Server 2017 without clustering, but it's failing when I try to add the secondary replica with the following error message: Failed to join ...
0
votes
2answers
20 views

postges: psql csv import fails although file exists

Trying to import a csv file as follows: psql -h 127.0.0.1 -d jira_statistics -U admin -p 5432 -c "COPY bugs FROM '/home/centos/bugs.csv' delimiter '|' csv;" Password for user admin: ERROR: could ...
1
vote
2answers
51 views

What is the best way to calculate year over year inflation?

My ultimate goal is to calculate money from previous years as 2019 dollars. I've got these numbers from the BLS, and create the CPI_U in sql like so: create table CPI_U (year int, dec decimal(4,...
1
vote
0answers
27 views

Order of logical decoding output on Postgres 11

Given the following table in PostgreSQL 11.1, what is the order of logical decoding output rows? CREATE TABLE t1 ( t1_id INTEGER PRIMARY KEY ,name TEXT ) ; CREATE TABLE t2 ( t2_id INTEGER ...
0
votes
0answers
24 views

One way replication between 2 sql servers on different networks

I would like to replicate between 2 sql servers that exist in different networks. This link is not relevant to my case as the servers are not connected: https://docs.microsoft.com/en-us/sql/relational-...
0
votes
1answer
21 views

File Share Witness with 4 Node Cluster

I am building a cluster of 4 nodes along with a file share witness. Every month due to server updates the server that holds the file share will need to be rebooted. My question is while the file ...
1
vote
1answer
31 views

Setting up a many-to-many relationship based off of two other many-to-many relationships

I have 3 main top level entities: Project (PK: Id) Owner (PK: Id) Product (PK: Id) Here are the rules: A Project can have many Owners. An Owner can have many Projects. A Project can have many ...
1
vote
1answer
23 views

Added a new listener to my 'listeners.ora' file. How can I connect directly to it with SQLPlus? (skipping tnsnames.ora for now)

I updated my listeners.ora file to include a new listener on port 1523. I then started the listener with lnsrctl start. I'd now like to connect to the database on that port via SQLPlus without ...
1
vote
0answers
15 views

Design table that solve a service payment or a package service payment?

I am learning and practicing some database designing and case study on Selling Services on Internet. In my requirement, user can buy a post service and add more extra option if they need. In fact, ...
1
vote
0answers
19 views

FOR XML could not serialize the data for node 'col_definition'

looking for some help. I have just started looking at the Blitz stats but getting the following error. This is whilst running sp_BlitzIndex. I am using Version 7.2 (dated 28/01/2019). FOR XML ...
0
votes
1answer
18 views

csv import fails without throwing an error

Ι am trying to import a csv file into an existing mysql 5.7 table. Here is the command (the db is in docker with bind mount for persistence) mysql -h 127.0.0.1 -uroot -prootpass --local_infile=1 ...
0
votes
0answers
11 views

Is it possible to make thosunds of concurrent transactions even if the max_connections is set to 100 in Postgres?

This is maybe a dummy queestion but I couldn't understand to have thousands of concurrent transactions in a database even if the max_connections=100. Definition of max_connections from postgres ...
0
votes
0answers
35 views

SQL Server Execute Permission Denied

I have a small app which calls a stored procedure over ODBC using a sysadmin user with login and password. The problem is that some (Windows Domain) users get an error saying execute permissions were ...
0
votes
2answers
28 views

Select records with the same first column value and different second column value? SQL Server

Assume we have a SQL Server table as the following: ContentId | TagId -------------------- 1 2 1 3 2 1 2 2 2 3 1002 1 ...
-4
votes
0answers
25 views

Q: optimization and performance [on hold]

I have sql server up and running now but it appears that sql is using quite a bit of memory. How can I check the settings and make sure I am running at optimal performance. Also, do you have any ...
0
votes
1answer
23 views

Is possible to use ORDER BY with IF ELSE condition?

Here I have some data. I want to show the records which have fav=1 at fast, then I want to show the data where the date is greater than the current date in ascending order(date) and at last the ...
0
votes
0answers
16 views

How to implement database versioning just like Rest API using dotnet core postgresql?

We are working on a Social App and wanted to implement back-end API with versioning feature and hoping database versioning as well. We decided to write the back-end with Dot.Net Core and the database ...
0
votes
0answers
24 views

ORA-16706: Data Guard broker worker process not available

Database version is 12.1.0.1. We have one primary database and two physical standby database and dataguard also configured. Dataguard sync is fine working between primary and physical standby.(No lag)...
0
votes
1answer
26 views

PostgreSQL. Shared_memory and sessions

I have a standalone server which is running Jira and PostgreSQL 9.6. I have noticed that PostgreSQL has shared_buffers parameter is equal 128MB where RAM is 32GB. Because server is shared with ...
2
votes
1answer
54 views

Splitting a year into pieces

I want to split a given year into given number of date range pieces using SQL. Following points need to be considered while splitting it: Given year will always start from 01-01 and end at 12-31 and ...
0
votes
1answer
25 views

How to delete all but the latest entry per customer in the fastest way?

I have a table containing around 4 million unique customers (with customerId each), but each customer can have multiple entries. There is no primary key. Each row has a creation dateTime saved as ...
1
vote
1answer
53 views

Sql server deadlock when insert and update in one transaction

I'm using Azure SQL + Java + Spring Boot 2. Currently I'm trying to understand the reason why deadlocks occur. In transaction I'm doing insert and later update on the same table by different rows. ...
0
votes
2answers
26 views

Can any database table name support double quotes "?

I need to know whether any database supports double quotes as a part of their table name. I am especially interested in JDBC compliant databases. And if they do, can I please know how?

15 30 50 per page