texts: How to optimize a query that's running slow on Nested Loops (Inner Join)

texts:
TL;DR

Since this question keeps getting views, I'll summarize it here so newcomers don't have to suffer the history:

JOIN table t ON t.member = @value1 OR t.member = @value2 -- this is slow as hell
JOIN table t ON t.member = COALESCE(@value1, @value2)    -- this is blazing fast
-- Note that here if @value1 has a value, @value2 is NULL, and vice versa

I realize this might not be everyone's problem, but by highlighting the sensitivity of the ON clauses, it might help you look in the right direction. In any case the original text is here for future anthropologists:

Original text

Consider the following simple query (only 3 tables involved)

    SELECT

        l.sku_id AS ProductId,
        l.is_primary AS IsPrimary,
        v1.category_name AS Category1,
        v2.category_name AS Category2,
        v3.category_name AS Category3,
        v4.category_name AS Category4,
        v5.category_name AS Category5

    FROM category c4
    JOIN category_voc v4 ON v4.category_id = c4.category_id and v4.language_code = 'en'

    JOIN category c3 ON c3.category_id = c4.parent_category_id
    JOIN category_voc v3 ON v3.category_id = c3.category_id and v3.language_code = 'en'

    JOIN category c2 ON c2.category_id = c3.category_id
    JOIN category_voc v2 ON v2.category_id = c2.category_id and v2.language_code = 'en'

    JOIN category c1 ON c1.category_id = c2.parent_category_id
    JOIN category_voc v1 ON v1.category_id = c1.category_id and v1.language_code = 'en'

    LEFT OUTER JOIN category c5 ON c5.parent_category_id = c4.category_id
    LEFT OUTER JOIN category_voc v5 ON v5.category_id = c5.category_id and v5.language_code = @lang

    JOIN category_link l on l.sku_id IN (SELECT value FROM #Ids) AND
    (
        l.category_id = c4.category_id OR
        l.category_id = c5.category_id
    )

    WHERE c4.[level] = 4 AND c4.version_id = 5

This is a pretty simple query, the only confusing part is the last category join, it's this way because category level 5 might or might not exist. At the end of the query I am looking for category info per product ID (SKU ID), and the that's where the very large table category_link comes in. Finally, the table #Ids is just a temp table containing 10'000 Ids.

When executed, I get the following actual execution plan:

As you can see, almost 90% of the time is spent in the Nested Loops (Inner Join). Here's extra information on those Nested Loops:

Note that the table names don't match exactly because I edited the query table names for readability, but it's pretty easy to match (ads_alt_category = category). Is there any way to optimize this query? Also note that in production, the temp table #Ids doesn't exist, it's a Table Valued Parameter of the same 10'000 Ids passed on to the Stored Procedure.

Additional info:

category indices on category_id and parent_category_id
category_voc index on category_id, language_code
category_link index on sku_id, category_id

Edit (solved)

As pointed out by the accepted answer, the problem was the OR clause in the category_link JOIN. However, the code suggested in the accepted answer is very slow, slower even than the original code. A much faster and also much cleaner solution is simply to replace the current JOIN condition with the following:

JOIN category_link l on l.sku_id IN (SELECT value FROM @p1) AND l.category_id = COALESCE(c5.category_id, c4.category_id)

This minute tweak is the fastest solution, tested against the double join from the accepted answer and also tested against the CROSS APPLY as suggested by valverij.


texts:
i have one server with SQL server 2012 standard on cloud environment and one linked server with sql express on remote location connected with VPN , im using transactional replication

every time when internet conection is dropped and regain on remote location, i try to connect with SSMS im getting error

  "A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network
  name is no longer available.) (Microsoft SQL Server, Error: 64)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476

  The specified network name is no longer available"

But when i test the connection in server object/linked server, the connection is ok and the replication is running

one resolution was to restart the cloud server and everything is normal i can connect to remote server via ssms, but this is no resolution for me

can somebody help me with this one


texts:
How can I create a "root" (like on MySQL) in Oracle?

I installed Oracle database on my system, for homework, for school (faculty) but it didn't give me the option to make a root user. Well at least I don't think so.

It asked me on preparation for install to create a password but when I tried connecting with the id and password it failed. (So that is why I presume it did not make a root user)

Connected on the databse with connect /as sysdba but don't know how to create a user with admin privileges.

Tried

create user "user" identified by "password" with grant option;

(error)

create user "user" identified by "password";

(succes)

grant all on "user" with grant option;

(error)

I want to have all privileges on all tables, etc.


texts:
I'm trying to get the kWh from multiple entities in InfluxDB in Grafana.  I have the following raw query.

SELECT ("a" + "b")  / 3600000 FROM (
    SELECT spread("watt_seconds") AS "a" FROM "W"
    WHERE ("entity_id" = 'cabana') AND $timeFilter fill(null)
),(
    SELECT spread("watt_seconds") AS "b" FROM "W"
    WHERE ("entity_id" = 'pool_parcel') AND $timeFilter fill(null)
)

this always returns 0.

If I use ("a" + "a") I get 2*a, and if I use ("b" + "b") I get 2*b, as expected.  But when I add the two subqueries, I always get 0.

What am I doing wrong.


texts: PostgreSQL 12: column proisagg does not exist

texts:
I created a PostGIS database using the following images here (i.e. 9.3-2.1, 11.0-2.5 and 12.0 tags), however I'm getting the following errors when I try to open the 'public' schema:

An error has occurred:

11:43:59: Error: ERROR:  column "proisagg" does not exist
LINE 9:  WHERE proisagg = FALSE AND pronamespace = 2200::oid
HINT:  Perhaps you meant to reference the column "pr.prolang".

An error has occurred:

11:46:24: Error: ERROR:  column rel.relhasoids does not exist
LINE 1: ...t_userbyid(rel.relowner) AS relowner, rel.relacl, rel.relhas...

I found possible solutions here and here. I tried to ask how should I update the query, but I need at least 50 reputation to comment.

Would anyone know how should I fix this question? Or how should I change the definition of the query on pgAdmin?

Thanks in advance.

System:

PostgreSQL 12.0 (Ubuntu 12.0-2.pgdg16.04+1)
pgAdmin III
Ubuntu 16.04 LTS


texts: How to model this scenario in conceptual erd?Bridge entity needed?

texts:
I have a house-share entity( primary key : house-share id) and housekeeper entity (primary key: housekeeper id).

At any point in time a housekeeper can only work in one house-share.

When a housekeeper finishes an allocation with a particular house-share the allocation is recorded(start date and end date).

A house-keeper may return to a house-share they previously worked in; if they do so, a new allocation is recorded.

I believe the relationship between house share and housekeeper must be many to many. I can come up with a bridge entity(e.g. housekeepingwork)  which have pk's housekeeper id, start_date and end_date. But usually a bridge entity will have primary keys from both entities but not in this case. I'm wondering if I'm modelling this wrong?


texts:
If the two updates given in https://www.postgresql.org/docs/current/static/explicit-locking.html#LOCKING-DEADLOCKS happen in one UPDATE query will it still result in deadlock?

Transaction 1 executes the following statement:

-- transaction 1
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum IN (11111, 22222);

Transaction 2 executes the following statement:

-- transaction 2; note the acctnum order is different
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum IN (22222, 11111);

Please note that acctnum order provided in queries is different. Please ignore the fact that account balance will be incorrect. I am interested in the behavior as I have a similar situation.


texts: Error passing variable table type to a stored procedure

texts:
I'm having a noob problem passing a data table type to a stored procedure.

Here is the implementation :

DECLARE @T TVARIABLE

    INSERT INTO  @T   VALUES ( '#var1','1')
    INSERT INTO  @T   VALUES ( '#var1','1')
    INSERT INTO  @T   VALUES ( '#var2','2')
    INSERT INTO  @T   VALUES ( '#var3','3')
    INSERT INTO  @T   VALUES ( '#var4','4')
    INSERT INTO  @T   VALUES ( '#var5','5')
    INSERT INTO  @T   VALUES ( '#var6','6')
    INSERT INTO  @T   VALUES ( '#var7','7')
    SELECT * FROM @T
    EXEC SP_NUEVO_VAR   @T

Here is the type I have created:

CREATE TYPE  [dbo].[TVARIABLE] AS TABLE
      (
         variable varchar(25),
         valor    varchar(25)
      )

Here is the stored procedure:

 CREATE PROCEDURE SP_NUEVO_VAR
    @T_Variable TVARIABLE READONLY
AS
BEGIN
BEGIN TRAN SAV_VAR
BEGIN TRY
    DECLARE @Output_T TVARIABLE
    DECLARE @variable       as varchar(50),
            @valor          as varchar(50),
            @folio_terminal as int,
            @new_var        as int,
            @sp             as varchar(50),
            @LOG            AS VARCHAR(Max)

    DECLARE VARIABLES CURSOR FOR SELECT * FROM  @T_Variable;
     SET @sp='SP_NUEVO_VAR'
     EXEC SP_LOGDB 'START',@sp

    SELECT @folio_terminal = folio_terminal FROM terminal
    --WHERE no_serie=@no_serie

    SET @LOG='@folio_terminal:['+CAST(@folio_terminal AS VARCHAR(10))+']'
     EXEC SP_LOGDB 'START',@sp

    OPEN VARIABLES
    FETCH NEXT FROM VARIABLES INTO @variable,@valor
      WHILE @@FETCH_STATUS = 0
       BEGIN
              SELECT @new_var=NEXT VALUE FOR dbo.seq_variable;
              INSERT INTO dbo.variables (id_var,   folio_terminal,  variable,   valor)
              VALUES                    (@new_var, @folio_terminal, @variable, @valor)
              FETCH NEXT FROM VARIABLES INTO  @variable,@valor
       END
       COMMIT TRAN SAV_VAR
END TRY
BEGIN CATCH
    ROLLBACK TRAN SAV_VAR
END CATCH
END
COMMIT
GO

When I run the implmentation I get this error:

  Mens 2766, Nivel 16, Estado 4, Procedimiento SP_NUEVO_VAR, Línea 12
  La definición del tipo de datos definido por el usuario 'TVARIABLE' ha cambiado.

The error in English is that the type of data defined by the user 'TVARIABLE' has changed.

When I use this stored procedure from C#, the stored procedure works FINE, but running the procedure in T-SQL is not working.


texts: Can I restore a SQLServer db from URL but not from azure?

texts: I have backups in S3.  If I create presigned URLs, can I restore directly from the URL to my on premise SQL Server instance?


texts: How are multiple my.cnf's loaded by MySQL?

texts:
If I want to replicate how the MySQL/Maria client reads ~/.my.cnf, /etc/my.cnf, etc, how would I do it?

The MySQL docs give a list of paths and mention "top files are read first, files read later take precedence", is that on a key-value basis or the entire file?


texts: Performance Issues on MSSQL Server Web Edition

texts:
I am facing some performance issues on my MSSQL Server which is a Web Edition. My database size is around 3 TB and there are continuous inserts that happen through various processes. We also have heavy select queries running. I have 128GB RAM on the server and since SQL is a Web Edition we only get to use 64GB RAM and due to this the CPU utilization sometimes goes up to 100%. My question is if we upgrade to Enterprise edition and then reduce 4 cores (to save on cost) will we be able to get better performance? As we are also planning to increase the RAM to 256 GB. The other option is to go for Standard edition and keep 16 cores. As we know with Standard edition the max RAM that I can get for SQL is 128 GB.

Please help me as to what will be the best solution to increase my performance.


texts: Conceptual data model for warehousing objects

texts:
I'm student and new to databases. I must conceptualize a database model for warehousing video games using a relational database management system (MySQL).

Every objects in the warehouse have common fields (unique ID, picture url, price, weight, name, etc.).

Then, there is three object's types with their own specific fields :

Video Games
Consoles
Accessories
Controller
Hardware
Etc...

What would be the best relation model to link a common object to its specific category, fields and sub-category?

I know that there is document-oriented database management system that could do this easily, but I must work with MySQL.


texts: SQL Server Management Studio 2012 Application Cannot Start

texts:
I'm trying to install SQL Server Management Studio on my laptop.

Operating System: Windows 7 Service Pack 1

But I got an error message saying, "The application cannot start.":

What shall I do? I'm a student and I need this for my thesis / school project.


texts: Cassandra multidatacenter configuration with 1 external ip

texts:
I'm trying to setup a multi-datacenter Cassandra cluster.
The problem is that my datacenters have only 1 external IP (wan IP), I can setup port forwarding on the data centers switchs to access each node from the outside world using a different port, but I don't know how to setup the cassandra.yaml file properly.

Is there a way to setup a multidatacenter cassandra cluster in this scenario?.

Thanks in advance!


texts:
I want to create a script that will create some custom named schema and within it some tables and functions. Like this:

The example.sh file:

#!/bin/bash

# this is the only place I want to set the schema name
SCH="ex"

export SCH

export PGPASSWORD="*******"

PSQL="psql \
  -X \
  -U postgres \
  -h localhost \
  --single-transaction \
  --echo-all \
  --set SCH=$SCH \
  --set ON_ERROR_STOP=on "

eval $PSQL "-f ./example.sql"

The example.sql file:

DROP SCHEMA IF EXISTS :SCH CASCADE;
CREATE SCHEMA :SCH;

CREATE TABLE :SCH.my_table
(
  id SERIAL PRIMARY KEY,
  my_col text
);

INSERT INTO :SCH.my_table (my_col) VALUES ('abc'), ('def');

CREATE OR REPLACE FUNCTION :SCH.getLast()
RETURNS text AS $$
SELECT my_col FROM :SCH.my_table ORDER BY id DESC LIMIT 1;
$$ LANGUAGE sql STABLE;

SELECT * FROM :SCH.getLast();

It works fine until the:

CREATE OR REPLACE FUNCTION :SCH.getLast()
RETURNS text AS $$
SELECT my_col FROM :SCH.my_table ORDER BY id DESC LIMIT 1;
$$ LANGUAGE sql STABLE;

As the function body is a text constant, the :SCH is not substituted by the schema name and we get an error:

psql:./example.sql:16: ERROR:  syntax error at or near ":"
LINE 3: SELECT my_col FROM :SCH.my_table ORDER BY id DESC LIMIT 1;

Is there a neat way to make it work?

Workaround

This can be done in the following way, but it seems there should be an easier way:

CREATE OR REPLACE FUNCTION :SCH.makeFunction(schema_name text)
RETURNS VOID AS $body$
BEGIN
    EXECUTE format('CREATE OR REPLACE FUNCTION %1$s.getLast()
    RETURNS text AS $$
    SELECT my_col FROM %1$s.my_table ORDER BY id DESC LIMIT 1;
    $$ LANGUAGE sql STABLE;', schema_name);
END
$body$ LANGUAGE plpgsql VOLATILE;

SELECT :SCH.makeFunction(:'SCH');


texts: Altering Column Size On a Super Large Table

texts:
I have a table with a column of type NVarChar(Max).

I didn't take good precautions before allowing users to add data so right now I have more than 2.5 billion rows and the database is becoming super large.

I'm trying to alter the column to change the size to a fixed length where records > that width should be deleted.

This is not an indexed column.

I tried LEN(Text)>width but this is a very slow function as it would scan billions of rows.

We've also experimented later with changing Int to BigInt by creating a new table and moving data there but it took a week or so.

Alter Column would freeze the system.

Can you kindly suggest any other way or suggest the method you feel is best?

The database is hosted on Azure SQL

Thank you so much :)


texts: How to restore by-default saved 'sys' database in MySQL 8.0?

texts: By mistake I deleted by-default saved 'sys' database in Mysql version 8.0.21 So, how to restore it?


texts: Very slow insert performance on large table (EF)

texts:
One of our systems has a table that receives around 20k inserts per day - it currently contains ~10m rows. We've just pushed out a system upgrade and I'm intermittently getting some shockingly slow performance on inserts to this table now (anything from 5 to 40s traces). Inserts will only ever insert a single row at a time. Entity Framework is generating the following SQL:

declare @generated_keys table([ID] uniqueidentifier)

insert [dbo].[Table]([Col1], Col2], etc)

output inserted.[ID] into @generated_keys values (@0, @1, etc)

select t.[ID] from @generated_keys as g join [dbo].[Table] as t on g.[ID] = t.[ID] where @@ROWCOUNT > 0

Nothing out of the ordinary - at least as far as I can see.

The table has 12 FKs. As the entity represents a time-based event, the clustered index is on a DateCreated column. There are 8 further non-clustered indexes, with various included columns. The ID column is the PK and has a unique, non-clustered index.

As a note, I have noticed that, due to an omission, the ID column is using newid() instead of newsequentialid().

The actual execution plan comprises the following major parts:

7x 3% - Clustered Index Seek on FK tables

1x 8% - Table Insert (@generated_keys)

1x 74% - Clustered Index Insert, 1 actual row, 0.09 cost

I'd really like to get to the bottom of this but I'm stumped now. Any advice?


texts: Error: This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table

texts:
I'm trying to run this query in mysql  Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1 but got this error all the time:

CREATE TRIGGER trig_companies_table_insert BEFORE INSERT ON `companies`
    FOR EACH ROW SET NEW.createdAt = NOW();
[Err] 1235 - This version of MariaDB doesn't yet support 'multiple triggers with the same action time and event for one table'

What this means? How I can avoid and/or fix it?

This is the queries I am trying to execute:

-- brands
ALTER TABLE `brands`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_brands_table_insert BEFORE INSERT ON `brands`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- companies
ALTER TABLE `companies`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_companies_table_insert BEFORE INSERT ON `companies`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- emails
ALTER TABLE `emails`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_emails_table_insert BEFORE INSERT ON `emails`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- events_tracking
ALTER TABLE `events_tracking`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_events_tracking_table_insert BEFORE INSERT ON `events_tracking`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- media
ALTER TABLE `media`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_media_table_insert BEFORE INSERT ON `media`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- messages
ALTER TABLE `messages`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_messages_table_insert BEFORE INSERT ON `messages`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- reps
ALTER TABLE `reps`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_reps_table_insert BEFORE INSERT ON `reps`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- targets
ALTER TABLE `targets`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_targets_table_insert BEFORE INSERT ON `targets`
    FOR EACH ROW SET NEW.createdAt = NOW();
-- territories
ALTER TABLE `territories`
  CHANGE COLUMN `createdAt` `createdAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  CHANGE COLUMN `updatedAt` `updatedAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

CREATE TRIGGER trig_territories_table_insert BEFORE INSERT ON `territories`
    FOR EACH ROW SET NEW.createdAt = NOW();


texts: PowerShell Script Not Working on SQL Server Job Step

texts:
I've some .CSV files generated in a shared folder \\SERVER01\DIR\Current and would like to copy to another folder in same server \\SERVER01\DIR\Archive.
The script needs to copy, rename files adding timestamp, and then remove from the source folder. PS script works very well on PowerShell ISE or Windows PowerShell, but, when I try to schedule same script in a SQL Server Job, I'm receiving errors below:
$source = "\\SERVER01\DIR\Current" $destination = "\\SERVER01\DIR\Archive"

Get-ChildItem $source -Recurse -Include *.csv | % {
    $name = $_.Name.Split(".")[0] + "_" + ($_.CreationTime | Get-Date -Format yyyymmdd) + "_" + ($_.CreationTime | Get-Date  -Format hhmmss) + ".csv"
    #$name = "Finished_" + ($_.CreationTime | Get-Date -Format yyyymmdd) + "_" + ($_.CreationTime | Get-Date  -Format hhmmss) + ".csv"
    #$name = "Finished_" + $_.Name.Split(".")[0] +  "_" + ($_.CreationTime | Get-Date -Format yyyymmdd) + "_" + ($_.CreationTime | Get-Date  -Format hhmmss) + ".csv"
    Rename-Item $_ -NewName $name
    Move-Item "$($_.Directory)\$name" -Destination $destination }

I've received error below:
Message
Unable to start execution of step 1 (reason: line(9): Syntax error).  The step failed.

After receiving the error above, I've changed Line(9) as below:
$source = "\\SERVER01\DIR\Current"
$destination = "\\SERVER01\DIR\Archive"

Get-ChildItem $source -Recurse -Include *.csv | % {
    $name = $_.Name.Split(".")[0] + "_" + ($_.CreationTime | Get-Date -Format yyyymmdd) + "_" + ($_.CreationTime | Get-Date  -Format hhmmss) + ".csv"
    #$name = "Finished_" + ($_.CreationTime | Get-Date -Format yyyymmdd) + "_" + ($_.CreationTime | Get-Date  -Format hhmmss) + ".csv"
    #$name = "Finished_" + $_.Name.Split(".")[0] +  "_" + ($_.CreationTime | Get-Date -Format yyyymmdd) + "_" + ($_.CreationTime | Get-Date  -Format hhmmss) + ".csv"
    Rename-Item $_ -NewName $name
    Move-Item "\\SERVER01\DIR\Current\$name" -Destination $destination
}

Then, received another error:
Message
The job script encountered the following errors. These errors did not stop the script:
A job step received an error at line 4 in a PowerShell script.
The corresponding line is 'Get-ChildItem $source -Recurse -Include *.csv | % {  '.
Correct the script and reschedule the job.
The error information returned by PowerShell is:
'Cannot find path '\\SERVER01\DIR\Current' because it does not exist.  '.
Process Exit Code 0.  The step succeeded.

After that, I've tried to change line(9) again, using $source parameter:
Move-Item $source + "\" + $name -Destination $destination

But, it didn't work and return same error above: 'Cannot find path '\SERVER01\DIR\Current' because it does not exist.'.
It looks like $($_.Directory)... is not working in a SQL Server job. Not sure why and how to solve it?
I'm using SQL Server Agent to schedule it to be executed after some others steps which are being executed on SQL Server in the same job.


texts: Implementation and contraindications for OPTIMIZE_FOR_SEQUENTIAL_KEY

texts:
SQL Server 2019 CTP3.1 introduced an optimization to address last-page insert contention. This takes the form of an index option called OPTIMIZE_FOR_SEQUENTIAL_KEY.
One imagines this could be an adaptation of Bw-Tree or Bz-Tree. However, these rely on variable-sized pages whereas the current storage engine requires fixed-size pages.
How is the optimization implemented? How are current B-Tree algorithms altered by this optimization? In what circumstances would I choose not to deploy this option?
Research
A patent for a reverse key approach.
I had a quick look using DBCC PAGE, comparing 2017 to 2019 and 2019 with and without OPTIMIZE_FOR_SEQUENTIAL_KEY on an unique clustered index for an int IDENTITY column. There was nothing that obviously explained the new behaviour. This makes me think its an algorithmic thing, rather than a structural thing, which makes sense.
A blog post from MS.
This feature seems to centre on the detection and avoidance of convoys.


texts: Estimate query speed for +1.5 billion rows mysql table

texts:
I'm working on audio fingerprinting problem where I need to
query a very large table in terms of number of rows (at least 1.5 billion rows), but relatively OK in size (23G), and retrieve about 50K to 100K rows in total, using multiple queries (between 20 and 50 queries).
The table has 3 columns, a hash and two int values. No constraints whatsoever. The hash column has a lot of collisions/duplicates. Here is the output for show create table
CREATE TABLE `fingerprints` (
  `hash` binary(10) NOT NULL,
  `int1` mediumint(8) unsigned NOT NULL,
  `int2` mediumint(8) unsigned NOT NULL,
  KEY `hash` (`hash`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The query is simple, here is an example:
select int1 ,int2 from fingerprints where hash in (UNHEX("1ff99335cce004f2765d"),UNHEX("14c4b93ed575982ed2e4"),UNHEX("41044b0cf21dc8ac8f9b"),UNHEX("a791403ca116b4da53dd"),UNHEX("d9f91514b900c25fa095"),UNHEX("3349f906deae6cd32883"),UNHEX("221c0e3e2bc243fb0fe5").... more here);

I've tried different hardware specs (using AWS with only one machine/instance). Different my.cnf configurations but no significant performance boost.
the target speed threshold for this operation (total queries time) is 5 sec. But the best I've got in average is 3 sec for only one single query (if I have 20 queries, the total operation time is 1 minute).
final note: when profiling the query, SHOW profile command shows that the slowest part was (SENDING DATA) state. The query becomes slower when the result set is larger (i.e retrieving 10k rows takes about 6 sec, while retrieving 1000 rows takes 2 sec)
Questions:

What is the speed estimation for such query scenario for an SSD machine with enough RAM to hold indices. I have no experience working
at this scale.
Do you have recommendation for specific db setup? should I try mysql Memory engine? partitioning here is a necessity with distributed
machines? should I switch to innodb?

my setup:

read only myisam table compressed with myisampack and indexed on the where (hash) column.
the index table (MYI file) is fully loaded to RAM
SSD hard disk with limited iops (amazon AWS). According to AWS graphs i'm hitting 700 Iops sometimes.

Edit:
SHOW INDEX output:
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| fingerprints |          1 | hash     |            1 | hash        | A         |        NULL |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

EXPLAIN QUERY output (for the example query)

+----+-------------+--------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table        | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | fingerprints | NULL       | range | hash          | hash | 10      | NULL | 4912 |   100.00 | Using index condition |
+----+-------------+--------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+


texts: How to connect to pluggable database using ipc in Oracle XE 18c?

texts:
Here is my listener configuration:
LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 20-NOV-2020 20:01:25

Copyright (c) 1991, 2018, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=NoteBook)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date                20-NOV-2020 20:00:55
Uptime                    0 days 0 hr. 0 min. 29 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /opt/oracle/product/18c/dbhomeXE/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/NoteBook/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=NoteBook)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=127.0.0.1)(PORT=5500))(Security=(my_wallet_directory=/opt/oracle/product/18c/dbhomeXE/admin/XE/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "XE" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "XEXDB" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "b470a0c2708d9acee0530401a8c035ac" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
Service "xepdb1" has 1 instance(s).
  Instance "XE", status READY, has 1 handler(s) for this service...
The command completed successfully

I can connect to xepdb1 through tcp using sqlplus user/password@NoteBook/xepdb1, however, if I use use ipc (sqlplus user/password@xepdb1) to connect, it fails with an error ORA-12154: TNS:could not resolve the connect identifier specified.
How can I connect to a pluggable database using ipc?


texts:
My backend is writing The Soap request within a table using a BLOB column so that I have very big tables in mysql with more of 100GB.

I need store this information out of dabase in file for example.

Oracle has the BFILE datatype this works as softlink to a file, Could I simulate the same in mysql ?

Thanks.


texts: Datatype for different categories in a database

texts:
I have got the following categories for accounting and cash flow purposes:

Equity
     Contribution
     Distribution
     Valuation
Debt
     Issuance
     Payment
     Valuation Allowance
Interest
     Accrued
     Paid
     Valuation Allowance

I have broken them into 3 tables: equity, debt, and interest. Each table has the following columns:

ID
Date
Amount
Type

My question is, within the "type" column, should I be using a number, i.e. 1,2,3, or text to indicate the value?


texts: Collect SQL Server Query Execution time in seconds

texts:
I'm trying to find a way of collecting SQL server query response times over say for the last two days. Is there a way to achieve this? I know there are the query stats DMV's but I dont seem to be achieving what I required. I dont have access to query store therefore unable to use that at the moment either.

I'm looking for query response time from SQL server as I have people blaming the sql server but I can't see no memory pressure / cpu pressure or any bottlenecks. Therefore now trying to get the response times for queries ran against the server.

Thanks


texts: Job for mysql.service failed because the control process exited with error code

texts:
Today I was working on my project which has the SQL and sending and receiving the information from flask to SQL and vice-versa, but all of the sudden without any change of the configuration it should me this error:

  ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost'(111)

I tried to stop the service and work at the safe-mode but once I want to start again the service it gives me:

  Failed to start MySQL Community Server

I tried to swap the extra space but it did not work, I tried to remove the log but did not work, I tried to bind and unbind the 127.0.0.1 but it did not work.

I have attached my cnf below:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket      = /var/run/mysqld
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
#skip-external-locking
#skip-grant-tables
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address       = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size     = 16M
#max_allowed_packet = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_open_cache       = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#slow_query_log     = 1
#slow_query_log_file    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

Edit

Here is the problem that error.log showed me :

2020-03-17T10:32:14.514845Z 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-03-17T10:32:14.514867Z 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files


texts: How does a users DEFAULT_SCHEMA get set to something other than "dbo"

texts:
One of the settings for a SQL Server user is the DEFAULT_SCHEMA.  This is frequently dbo but not always.  I realize you can change the setting by using this code:

ALTER USER [UserName] WITH DEFAULT_SCHEMA = [SchemaName]

From what I can tell whenever I create a USER and don't specify a DEFAULT_SCHEMA it uses dbo.  But I'm still finding USERS that have a different default schema.

If I remember correctly when you have an instance that is upgraded from SQL 2000 the default schema is the schema matching the users name.  I'm guessing this is where most if not all of what I'm seeing comes from.

Are there any other ways that you can create a USER without specifying DEFAULT_SCHEMA and have it be created with something other than dbo?

Note: This is for versions of SQL Server 2005 and up.


texts: What is a good way to query data stored in multiple identical tables split by date/time

texts:
I have a 3rd party piece of software which records data samples from industrial equipment.  This data logging software stores data in sample data tables with approximately one day's worth of samples for a particular piece of equipment. There are many pieces of equipment being monitored so the database sample tables end up looking like this:

Equipment1_SampleData_1
Equipment1_SampleData_2
Equipment1_SampleData_3
...
Equipment1_SampleData_31

So those 31 tables contain sample data for one piece of equipment over the most recent rolling 31 day period.

There is a set of those tables for each equipment type. This makes it difficult to query for a specific sample value, given a specific date/time.

I was thinking of creating a view which unions all the equipment sample tables into one big view so that I can write a function which returns a particular sample given an equipment name and date/timestamp. Is that a bad way to do this? Can you suggest other or better ways of doing this? I do not have any control over the way the 3rd party data logging/sample tool stores data.

The end result of this is that I want to be able to say: "Give me the sample value recorded for equipment N, nearest (>=) the specified date/time" and get a value back.

This is on SQL Server 2008.


texts: Gather values from different related rows with JSONB fields

texts:
Say I have the following table structure:

jobs
+----+------------+
| id | some_field |
+----+------------+
|  1 |  some_val  |
|  2 |  some_val  |
|  3 |  some_val  |
+----+------------+

events
+----+--------+----------------------------------------+
| id | job_id |            payload (JSONB)             |
+----+--------+----------------------------------------+
|  1 |      1 | {'type':1, 'a':'some_val', ...}        |
|  2 |      1 | {'type':2, 'c':'some_other_val1', ...} |
|  3 |      2 | {'type':1, 'a':'some_other_val2', ...} |
|  4 |      2 | {'type':1, 'a':'some_other_val3', ...} |
|  5 |      3 | {'type':1, 'a':'some_other_val4', ...} |
|  6 |      3 | {'type':1, 'a':'some_other_val5', ...} |
|  7 |      3 | {'type':2, 'c':'some_other_val6', ...} |
|  8 |      3 | {'type':3, 'd':'some_other_val7', ...} |
+----+--------+----------------------------------------+

I want to create a query/view that ...

... selects one row for each job IF it has an event with payload->>'type' = 2
... in that row, shows values from other events that are associated with this job.
...  in that row, shows values from other events that are associated with this job if that event type occurs multiple times.

For example, with the above data I'd like to select all jobs that have an event of type 2, and find out the value of the d field from events with type 3 and some aggregate (say, COUNT()) of the a field from events with type 1. The result would be:

Results
+--------+------------------+----------+
| job_id |        d         | COUNT(a) |
+--------+------------------+----------+
|      1 |  NULL            |        1 |
|      3 |  some_other_val7 |        2 |
+--------+------------------+----------+

As a bonus: Each job can have only one event of type 2, so the basic query can be structured like:

SELECT
  job.id
FROM
  event
LEFT JOIN
    job ON event.job_id = job.id
WHERE
  event.payload ->> 'type' = 2;

How can I get the result table? Do I need to do sub queries for each field/event type, which would result in relatively bad performance, or can I use some GROUP BY job_id on the event table and maybe join the JSONs? (Note that my data is more complex and has many more fields in the payload that I would like to select).

Here is a DB Fiddle with the example data and my test query:

CREATE TABLE job (
      id SERIAL PRIMARY KEY,
      some_field TEXT
);

CREATE TABLE event (
      id SERIAL PRIMARY KEY,
      job_id INTEGER,
      payload JSONB
);

INSERT INTO job (some_field) VALUES ('val1'), ('val2'), ('val3');
INSERT INTO event (job_id, payload) VALUES
    (1, '{"type":1, "a":"some_val"}'::json),
    (1, '{"type":2, "c":"some_other_val1"}'::json),
    (2, '{"type":1, "a":"some_other_val2"}'::json),
    (2, '{"type":1, "a":"some_other_val3"}'::json),
    (3, '{"type":1, "a":"some_other_val4"}'::json),
    (3, '{"type":1, "a":"some_other_val5"}'::json),
    (3, '{"type":2, "c":"some_other_val6"}'::json),
    (3, '{"type":3, "d":"some_other_val7"}'::json);

SELECT job.id, job.some_field
FROM event
LEFT JOIN  job
     ON event.job_id = job.id WHERE event.payload ->> 'type' = '2';

id | some_field
-: | :---------
 1 | val1
 3 | val3

db<>fiddle here


texts: Why does retrieving fewer rows take longer?

texts:
I have a view that (left outer) joins multiple recursive CTEs. There are about 10k rows. The results of running SELECT * FROM MyView ORDER BY MyPrimaryKey, with statistics time and io on:

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(11063 row(s) affected)
Table 'Worktable'. Scan count 49743, logical reads 296781, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableA'. Scan count 7, logical reads 23690, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableB'. Scan count 18832, logical reads 38207, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableC'. Scan count 6, logical reads 5458, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableD'. Scan count 2, logical reads 112, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableE'. Scan count 2, logical reads 184, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 688 ms,  elapsed time = 881 ms.

The results of SELECT TOP 25 * FROM MyView ORDER BY MyPrimaryKey:

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(25 row(s) affected)
Table 'Worktable'. Scan count 48353, logical reads 801727, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableB'. Scan count 9360, logical reads 20006, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableA'. Scan count 4, logical reads 7359, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableD'. Scan count 1, logical reads 17944, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableE'. Scan count 2, logical reads 184, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'MyTableC'. Scan count 5, logical reads 226, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 29765 ms,  elapsed time = 30865 ms.

Why on earth are the execution times so drastically different?


texts: Is high index fragmentation an issue on performance

texts: Below is a small sample (top 20ish) from over 8000 indexes in avg_fragmentation_in_percent descending order. Will this cause any performance issues?


texts:
Is there a list of already set standards for some common fields like: firstName, lastName, email, phone?

I see most people just set varchar(300) to all fields, does it affect performance in any way?


texts: DB schema for storing temperature sensor data from different recorders - 1 or many tables needed?

texts:
I'm trying to get my head around the following problem:
I have 4 Temperature recorders that will be sending measurements over to my server.
The interval is 1 set of data per minute. Each recorder is only working for about 3 to 4 days per month and has 6, 12 or 18 sensors depending on the type of recorder. How can I best store these readings?
I was thinking of making 1 table for all the measurements each recorder and a separate table that list the data about the recorder. This way I don't end up with allot of empty fields.
Any thoughts?
Table name = measurements_recorder_3

Id
TC-1
...
TC-6
Timestamp
Recorder_Id

53
248,5
...
255,9
2021-05-10 16:33:45
3

54
248,8
...
256,5
2021-05-10 16:34:45
3

55
249,3
...
257,2
2021-05-10 16:35:45
3

Table name = recorders

Id
Name
Location
Description
...
Timestamp

3
REC6003
50KVA008
6-TC recorder in mobile 50KVA unit
...
2018-09-15 11:38:05

4
REC18002
REC18002
18-TC recorder in a standalone case
...
2019-02-14 16:12:59


texts: SQL Server : how to disable connections to a linked server?

texts:
SQL Server Linked Server to default instance - service is disabled but connection is still possible

I have a server called SWLON1 that has SQL Server 2005 and SQL Server 2008 r2 installed.

The SQL Server 2005 is running as the default instance, the SQL Server 2008 R2 is running as a named instance sql2008.

I have linked servers to swlon1.

I don't want the linked servers to the SQL Server 2005 to work anymore, so I stopped and disabled the swlon1 SQL Server services related to the default instance as you can see on the picture below.

Still people can browse the catalogs and see the tables on the SQL Server 2005 instance, even though the services are disabled.

I installed a named instance SQL2008 and I want that to be the only one available to the users.

Is there a way to achieve this, or I will have to drop all the linked servers to the default instance (SQL Server 2005)?

I could also uninstall the SQL Server 2005 from that machine.


texts: Is >58 hours to create an index normal? Should I cancel and try again?


texts: Does this medical database look correct?

texts:
I'm pretty new to databases and I'm trying to create some schemas.

I've been working away at this one for a while, but I can't help feeling that it doesn't look right.

Essentially I have the following situation:

A given medical trial (of which there can be many) takes place in a varying number of medical centres.
Each centre has patients and practitioners.
Practitioners and patients have a consultation, and data is created.

It seems simple enough, but I've a feeling I've made a mess of it. The resultant database will be MySQL if that's relevant.

Does that look right?


texts: How can I use MySQL variables in subqueries?

texts:
I'm trying to run the following query, but it keeps getting "reduced" and failing. Cannot get MySQL to provide any information as to why.

Here's the query:

SELECT * FROM animals WHERE name IN(SELECT @animal_names);

Here's the information underneath the variable and table.

TABLE:

mysql> mysql> SELECT * FROM animals;
+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+
6 rows in set (0.00 sec)

VARIABLE:
mysql> SELECT GROUP_CONCAT(QUOTE(name)) INTO @animal_names FROM animals WHERE name LIKE '%%';
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> SELECT @animal_names;
+-----------------------------------------------+
| @animal_names                                 |
+-----------------------------------------------+
| 'dog','cat','penguin','lax','whale','ostrich' |
+-----------------------------------------------+
1 row in set (0.00 sec)

ISSUE:
mysql> SELECT * FROM animals WHERE name IN(SELECT @animal_names);
Empty set (0.00 sec)

mysql> EXPLAIN SELECT * FROM animals WHERE name IN(SELECT @animal_names);
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | animals | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1249): Select 2 was reduced during optimization

MySQL refuses to tell me why the "SELECT @animal_ids" is not working! Is there some MySQL variable that needs to be adjusted for that subquery to work?

This is a test sample. I'm trying to do this on a larger scale with about 800 values in a list. I want the list in a variable so I don't have to do A SELECT query multiple times to DELETE, INSERT, SELECT the values I need across multiple databases and tables.

Obviously this can be easily done with a Python or Golang script, but I'm trying to do this in MySQL directly. While I appreciate it, please refrain from offering script-based solutions.


texts:
I have installed the scripts for First Responder Kit 2020 release. I have a central server (A) and remote server (B). I am trying to run stored procedure sp_blitzCache on remote server B without installing the scripts on server B. I need the results of server B in Server A.
Due to the new additions, I am able to get results of server B in server A but I have to install the scripts in server B.
I have added a parameter @ServerName to the script but I am not sure which parts to edit.
I found EXEC AT as one way to execute sp_BlitzCache from server A on server B but I am getting an error:
Msg 8114, Level 16, State 5, Procedure sp_BlitzCache, Line 0 [Batch Start Line 11]
Error converting data type varchar to bit.
Thanks in advance!


texts: Should I create a new table with redundant data for optimization reasons?

texts:
I have a table with multiple columns with numeric data, and it is inserted every second by a few microcontrollers.

I need to query that data in the following manner:

The averages of all columns every 1, 5 and 15 minutes (three different queries).
Also, those averages are requested relatively often (hundreds of requests per day by a website).

I'd like to know what are some options I may try for optimizing those queries and not having to create other three tables with redundant data (the averages are already calculated, so I would just need to query them without performing a lot of math).

It's worth mentioning that those queries can't take longer than a few miliseconds.


texts: Minimum/Maximum Server Memory in SQL Server 2005 with /3GB and /Userva switches

texts:
I have a 32-Bit Windows 2003 machine with 3.75GB RAM running one SQL Server 2005 instance. I am trying to optimize memory. If I use the switches in boot.ini like below,

/3GB /Userva=2490

should I set the min and max server memory on the SQL Server instance to that 2490, or less than that? (2490 = 3840MB available - 1350MB for Windows)


texts: How to determine if SQL Server will not execute parts of the script in parallel?

texts:
Are there any general rules or maybe easy way to tell if SQL script will execute in batches instead of "all at once"?

USE UserDB;
GO
DBCC SHRINKFILE (DataFile1, 5);
GO
DBCC SHRINKFILE (DataFile2, 5);
GO

Will it start shrinking DataFile2 only after DataFile1 has finished, or simultaneously?


texts: How to make value in a row equal to now() in postgres

texts:
I'm new to PostgreSQL, and new to DB administration - please excuse a naive understanding of terms. Searching for answers to this question has proven difficult - if anyone can suggest better wording please advise.

I have a table containing historical and ongoing experiments that I would like to query for assessing past and present performance. I've been so far keeping this table in Excel and now as it's growing past Excel's usefulness I want to add it into my database.

Essentially, the columns of the table are Experiment_Id (string), StartDateTIme (datetime), and EndDateTime (datetime). There are some experiments that are still ongoing, so in my Excel worksheet there are rows with EndDateTime values of =Today(). The actual timestamp during the day is not important - these are tests which run for weeks and I can live with a timestamp of midnight.

I have read that PostgreSQL has a now() function, but I've only read of it being used in queries, not as values in tables. Is what I'm asking for possible in PostgreSQL?

I realize that a workaround to this problem could be to have a separate table containing a single column of Experiment_id and either a binary key of 'yes/no' is currently running. This would then be a massive list of zeros/falsies and with a few ones/truthies littered at the bottom.

The downside of this workaround is that then two tables would need to be updated when an experiment is completed instead of just one, and this could cause more errors. These tables are being updated manually.


texts:
I'm completely new to buying a database hosting and I would greatly appreciate your opinion about the following situation. I just created an App and I want to go live this weekend. I want to be prepared as good as possible so I will write some statistics of the database usage I think to see every 24 hours.

- 20 000 000 inserts into a table which contains 1 bigint, 7 ints and 1 date.
-  5 000 000 updates on above table
- 10 000 000 inserts into mapping table 1 bigint 1 int
-  2 000 000 inserts into table with 3 ints
-  5 000 000 select queries with WHERE pk reference of first table described above

I will use the MySQL 5.5 database with all InnoDB tables. At the moment I am thinking about buying a dedicated server with these stats:

Intel Xeon E3-1220LV2 2.3GHz
100 Mbps Uplink
4 GB DDR3 Memory
500 GB RAID-1 Drives
10 TB Bandwidth
Up to 5 Dedicated IPs

For a price of $140 dollar a month. What do you think, will this be enough? Would it be overkill? You know a better hosting? At the moment I am on a shared webhost ($12 a month) and I get a fifth of the numbers above, but the shared database is really not optimized for innoDB.


texts: Actual Number of rows is too high even with new tables

texts:
I have a query based on two newly created tables in SQL Server 2005. I have index created on these tables. But when I write a query with JOIN condition, the number of actual rows returned is too high whereas the estimated number of rows is less. And the query plan uses a nested loop. [Query plan diagram is given below.] Since these are new tables, I think the usual stale statistics is not the reason here.

I can rewrite this query by using NOT EXISTS (as shown in Query 2) and the actual number of rows is reduced. But I have other requirements to fetch details from LWTest table using INNER JOIN – there the high number of actual rows is a problem.

So, any clue why is the actual number of rows is so high even though there is index and statistics? What can be done to bring it down?

Note: Number of rows in TransmittedManifests –904. Number of rows in LWTest -- 829785

UPDATE

Note2: Compatibility_Level is 90. Elapsed time for Query 1 is 64 ms. Query 2 elapsed time is 6 ms.

Note3: Tried OPTION (RECOMPILE), rebuild index and UPDATE STATISTICS WITH FULLSCAN on these two tables. But the actual number of rows is still high.

Thansk to Martin Smith for details of difference in count per execution (estimate) and total count (actual).The actual rows is count across all executions, the estimated is count per execution. Estimated rows, actual rows and execution count.

For Query 1, the ActualExecutions="904" for LWTest table.

Query

DBCC FREEPROCCACHE
GO
DBCC DROPCLEANBUFFERS
GO

SET STATISTICS IO ON
PRINT 'BEGIN -----------------------------------------'

--Query 1
SELECT  T.[Manifest]
FROM dbo.TransmittedManifests T
LEFT OUTER JOIN dbo.LWTest LW (NOLOCK)
    ON LW.Manifest = T.Manifest
WHERE LW.Manifest IS NULL

PRINT 'QUERY 1 COMPLETED -----------------------------------------'

--Query 2
SELECT  T.[Manifest]
FROM dbo.TransmittedManifests T
WHERE NOT EXISTS (SELECT Manifest FROM dbo.LWTest L
                  WHERE L.Manifest = T.Manifest
                 )

PRINT 'QUERY 2 COMPLETED -----------------------------------------'

--Query 3
SELECT  T.[Manifest],LineItemID,InvoicedQuantity
FROM dbo.TransmittedManifests T
INNER JOIN dbo.LWTest LW (NOLOCK)
    ON LW.Manifest = T.Manifest

Table Schema

Query Plan 1

Query Plan 2


texts:
I have a table tree:

+------+-------+
| id   | name  |
+------+-------+
| 0    | tree1 |
| 1    | tree2 |
| 2    | tree3 |
| 3    | tree4 |
+------+-------+

A table pen:

+------+------+
| id   | name |
+------+------+
| 0    | pen1 |
| 1    | pen2 |
| 2    | pen3 |
| 3    | pen4 |
+------+------+

And a third table task that "attaches" a task to either a tree or a pen:

+------+------+-------+
| type | id   | name  |
+------+------+-------+
| 0    | 1    | foo   |
| 0    | 2    | bar   |
| 1    | 1    | fee   |
| 1    | 2    | beer  |
+------+------+-------+

When type is 0, it means id references a tree. When type is 1, it references a pen (and so on with many different tables).

How can I do this and ensure referential integrity?


texts: SQL Server 2016 ISNULL Returning 1900-01-01

texts:
I have a datetime2 column called DocDate that I am trying to evaluate an ISNULL check against to return a blank '' value for instead of NULL.

A snippet of the statement:

SELECT TOP 100
    DocumentId
    ,DocDate
    ,ISNULL(DocDate,'') AS 'DocDateEmpty'
FROM
    DocDetails
WHERE
    DocDate IS NULL

This returns: 1900-01-01 00:00:00.0000000

My WHERE clause clearly brings back only the rows that are explicitly NULL, so it is not an issue of DocDate being an empty string and evaluating to 0.

How can DocDate IS NULL evaluate correctly but ISNULL fails?

Per MSDN, ISNULL should evaluate the literal NULL value correctly and return an empty value.

  Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.


texts: How to backup to local drive and network drive simultaneously?

texts:
I can backup to a local drive and mirror:

BACKUP DATABASE [xxx]
TO  DISK = N'XXXX.bak'
MIRROR TO DISK =N'Network path \XXX.bak'
WITH INIT

--directly to network drive
BACKUP DATABASE [xxx]
TO  DISK = N'Network path \XXX.bak'
WITH INIT

The mirror option is not available in Standard Edition (SQL Server 2012), so what's the best approach to resolve the issue?


texts: restore postgres BBDD - message "already exists"

texts:
I do a pg_dump from RDS, this dump need to restore en local develop context
psql -U postgres -d devDDB -f /tmp/prodDDBB.sql

and too many errors jump, like:
psql:/tmp/prodDDBB.sql:123255: ERROR:  duplicate key value violates unique constraint "operation_keys_pkey"
DETAIL:  Key (id)=(1088) already exists.

psql:/tmp/prodDDBB.sql:226882: ERROR:  relation "brands_vehicles_account_index" already exists

this is normal on a restore operation? the database already exist, i guess this s normal.


texts: Create File Stream Enabled Database in Network File (NAS Server)

texts: I want to create a filestream enabled SqlServer database using NAS server as the storage. I already passed the creation of DB step. Look at my previous question https://stackoverflow.com/questions/16516515/creating-sql-server-database-with-data-and-log-file-on-synology-nas-server. I can create an ordinary db but couldn't managed to create filestream enabled DB. Is it possible to do this?


texts: Clustered index in SQL Server vs index organized tables in Oracle

texts:
I am making the transition as a database developer from SQL Server to Oracle and found some fantastic resources here already (How to make a transition from SQL Server DBA to Oracle? and As a DBA, how would I go about transitioning from Oracle to SQL Server?) but I am having a hard time finding good information on the use of index organized tables in Oracle.

In my previous life, we made extensive use of clustered indexes in SQL Server in our OLTP-ish datamart with great success.  Are index organized tables as handy a tool in Oracle?


texts: Audit all insert, delete, update and select statements on a schema in Oracle 12.2

texts:
I cannot understand how to create a Unified Audit policy in order to audit all INSERT, UPDATE, DELETE and SELECT statements performed on all the objects within a specific schema.

The goal is to track the tables and views involved in a specific PL/SQL procedure for a reverse engineering task.

For what I understood from the documentation, the only option is to specify each table/view to track within the policy. Is there an "audit all objects within a schema" construct for the create audit policy statement?


texts:
I have a DB2 table where one of the columns is encrypted using the two parameter variant of ENCRYPT (so the password is supplied in the SQL itself). Unfortunately due to an ETL issue the column got populated by data with two different encryption keys. I want to fix this, by running some kind of script, but unfortunately I cannot do a mass update as DECRYPT_CHAR will throw an exception on the first row where it couldn't use my encryption key.

Is there a way to write a SQL script that would go through all of the rows and re-encode the rows which use the wrong encryption keys without throwing an exception? I'd prefer plain SQL if possible.


texts: Service Broker "Broker Enabled" option goes from true to false when performing backup/restore of DB

texts:
We have two production servers joined by an AAG, and for a specific database, the Service Broker is enabled (Options -> Service Broker -> Broker Enabled : true. We use this for SignalR.
However, every time I perform a backup and then restore the database on a test environment, I have to manually enable the broker again (ALTER DATABASE DatabaseName SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE), as it's disabled when the database has been restored on the test server.
Can any option be set when performing either the BACKUP or RESTORE command that retains this database option? I use a script for generating the BACKUP/RESTORE statements, but I don't understand why a specific database option goes from true to false just by performing a BACKUP/RESTORE. Here are examples of the BACKUP and RESTORE commands I generate:
BACKUP DATABASE [DatabaseName] TO DISK = 'B:\backups\full_DatabaseName_20200902_1.bak', DISK = 'B:\backups\full_DatabaseName_20200902_2.bak', DISK = 'B:\backups\full_DatabaseName_20200902_3.bak', DISK = 'B:\backups\full_DatabaseName_20200902_4.bak' WITH COPY_ONLY, NOFORMAT, CHECKSUM, INIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10, BLOCKSIZE=65536

RESTORE DATABASE [DatabaseName] FROM DISK = 'B:\backups\full_DatabaseName_20200902_1.bak', DISK = 'B:\backups\full_DatabaseName_20200902_2.bak', DISK = 'B:\backups\full_DatabaseName_20200902_3.bak', DISK = 'B:\backups\full_DatabaseName_20200902_4.bak' WITH FILE=1,MOVE N'DatabaseName_data' TO N'E:\Data\DatabaseName.mdf',MOVE N'DatabaseName_log' TO N'F:\Logs\DatabaseName.ldf',MOVE N'DatabaseName_index' TO N'E:\Data\DatabaseName.ndf',MOVE N'DatabaseName_data_io' TO N'f:\Data\DatabaseName_io.ndf', NOUNLOAD, STATS = 10, RECOVERY, REPLACE


texts:
Our company is looking for functionalilty/or automated way to archive data in SQL Server.
Currently, we create archive tables with identity surrogate, and develop stored procedures to import data. We are conducting this for 100+ tables, data is 2 years or older. Does SQL Server have functionality or automated way to archive data (Wizard tool, UI) or does person have to develop stored procedures? I am thinking of creating dynamic sql to write script for 100+ tables.

Thanks,

-- If no functionality exists, will create dynamic sql for tables using template below

create table dbo.CustomerTransaction
(
    CustomerTransactionId bigint primary key identity(1,1) not null ,
    CustomerName varchar(255) null ,
    ProductName varchar(255) null,
    Quantity int null ,
    CreateDatetimestamp datetime not null
 )

create table dbo.CustomerTransactionArchive
(
    CustomerTransactionArchiveId bigint primary key identity(1,1) not null ,
    CustomerTransactionId bigint null,
    CustomerName varchar(255) null ,
    ProductName varchar(255) null,
    Quantity int null ,
    CreateDatetimestamp datetime not null
 )

create procedure dbo.CustomerTransactionArchive
as

    declare rowmin bigint = (Select min(CustomerTransactionId) from dbo.CustomerTransaction where Createdatetimestamp < dateadd(year,-2,CreateDatetimestamp ))
    declare rowmax bigint = (Select max(CustomerTransactionId) from dbo.CustomerTransaction where Createdatetimestamp < dateadd(year,-2,CreateDatetimestamp ))
    declare rowcounter bigint = rowcountermin

      -- insert into 100,000 record block, do not want to lock too many pages in table
    while rowcounter <= rowmax
    begin

        insert into dbo.CustomerTransactionArchive  (
        select * from dbo.CustomerTransactionArchive
        where CustomerTransactionId between rowcounter and (rowcounter +100000)
        and Createdatetimestamp < dateadd(year,-2,CreateDatetimestamp ))

        rowcounter = rowcounter + 100001
    end

Archive tables will be in different database


texts: How to restore output from pg_dump into a new table name

texts:
I am dumping a large postgres table (~15m records) like this:

pg_dump -h myserver  -U my_user --table=mytable  -Fc -Z 9 --file mytable.dump mydb

The above creates a mytable.dump file. I now want to restore this dump into a new table called mytable_restored

How can I use the pg_restore command to do this?


texts:
Basically, we have a table with the following structure:

CREATE TABLE `reqs` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `reqid` BIGINT(20) NOT NULL,
    `from` BIGINT(20) NOT NULL,
    `to` BIGINT(20) NOT NULL,
    `rdate` DATETIME NOT NULL,
    `state` ENUM('sent','approved','accepted','rejected') NOT NULL,
    `type` INT(11) NOT NULL,
    `subtype` INT(11) NOT NULL,
    `clusterid` INT(11) NOT NULL,
    `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `to_state_clusterid` (`to`, `state`, `clusterid`),
    INDEX `from_state_clusterid` (`from`, `state`, `clusterid`),
    INDEX `reqid_to` (`reqid`, `to`),
    INDEX `from_rdate_type` (`from`, `rdate`, `type`),
    INDEX `type` (`type`),
    INDEX `from_state_type_clusterid` (`from`, `state`, `type`, `clusterid`),
    INDEX `to_state_type_clusterid` (`to`, `state`, `type`, `clusterid`),
    INDEX `state` (`state`),
    INDEX `from_to_state_type_clusterid` (`from`, `to`, `state`, `type`, `clusterid`),
    INDEX `from_rdate_type_clusterid` (`from`, `rdate`, `type`, `clusterid`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB

It's currently around ~250M rows and we'd like to delete 200M+ of them. This is a production database and this table in particular gets dozens of INSERT/UPDATE/SELECT queries every second.

Finally, I don't know if it changes anything, but all SELECTs to this table have USE INDEX statements in them.

What would be the best way to do this with minimal impact to our application?

EDIT: There are 2 DELETE statements to be executed:

DELETE FROM `reqs` WHERE `state` = 'accepted' or `state` = 'rejected';
DELETE FROM `reqs` WHERE `rdate` < '2013-01-01 00:00:00';

These queries are expected to delete ~70M and ~130M rows respectively.


texts: Planning and Optimizing the table structure InnoDB MySql

texts:
I am planning to have an InnoDB MySql table with tens of millions of rows.

Each row represents a record that can belong to AT LEAST one of 200+ categories. Most of the records will belong to 2 or more categories. All the categories represented by a numeric id, from 1 to 200+.

What is the most efficient way to store these multiple categories within each record to get the fastest look up speed when querying to return all the records belonging to one of the categories?

I think the fastest way will be to have a separate column for each of the categories and then just query that column. But the problem is I will have to make 200+ columns and will be new categories added in the future. Also, I don’t think having 200+ indexes will do any good for my server.

Then I thought of creating a separate record for each of categories, so there would be only one column for categories and the index will work just fine. The problem with this approach is that the table is already in tens of millions and it will keep on growing over time. If I will throw in duplicate records for each of the current multi-category records, the table size will quickly become unmanageable plus it will affect the performance.

The other approach I can think of will be to store all the categories related to a record in one column separated by comma or any other separator and then do a LIKE %% search. But If I understand correctly, this method doesn’t use an index and will result in the slow lookups.

So what is the correct way to approach it? I was even thinking of creating a separate table for each category, but that would be unacceptable when I will need to do some other types of queries across whole data.


texts: Different lock types on source table when I change method of inserting data?

texts:
Why do I encounter IS(intent shared) lock on source table when I insert into target table using SSIS data flow task and S(shared) lock for same task when using T-SQL statement?

I am trying to insert around 10M rows. Concurrent inserts on the source happens all the time. So, according to lock compatibility matrix, SSIS data flow task does not block parallel inserts, but T-SQL insert does.

Why is that?


texts:
I'm MySQL DBA having 4 years of experience.
My Question is:
1. Can I consider MySQL as long term technology.
2. Apart from MySQL which database I should learn, so that I can get job easily outside.

Regards,
Sudeep


texts: SQL Server Client Tools: How can you tell what SQL edition the client tools are from?

texts:
While working on SQL Server: Unable to save import export package, but am on Standard Edition , the question came up:

We know how to ask SQL Server what edition it is. (Look at properties of server in SSMS, "select @@version" etc.). But how do we look at an install of client tools and tell what version the client tools are?


texts: Azure SQL very slow INSERT in stored procedure

texts:
I have the following stored procedure, inserting new rows in a join table:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[InsertNewRows]
    @PropertyId int,
    @ItemGroupId int,
    @Discriminator nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO ItemProperty (PropertyId, Discriminator, ItemId)
    SELECT @PropertyId, @Discriminator, Id FROM Item WHERE ItemTypeId IN
        (SELECT Id From ItemType WHERE ItemGroupId = @ItemGroupId)
END;

In this case, items belong to a type which in turn belongs to a group. I want to create the rows for all items in a group.

I execute the stored procedure for test purposes using:

BEGIN TRANSACTION

EXEC InsertNewRows 1, 10, 'Discriminator';
EXEC InsertNewRows 2, 10, 'Discriminator';
EXEC InsertNewRows 3, 10, 'Discriminator';
EXEC InsertNewRows 4, 10, 'Discriminator';
EXEC InsertNewRows 5, 10, 'Discriminator';
EXEC InsertNewRows 6, 10, 'Discriminator';
EXEC InsertNewRows 7, 10, 'Discriminator';
EXEC InsertNewRows 8, 10, 'Discriminator';
EXEC InsertNewRows 9, 10, 'Discriminator';
EXEC InsertNewRows 10, 10, 'Discriminator';

COMMIT

The group used for testing contains around 80,000 items.

On my local SQL Server 2014 Developer Edition, the execution takes around 10 seconds to perform. When i try to run it on a S2 instance of Azure SQL it takes around 1 minute per row so in total over 10 minutes. I even tried scaling up to a P15 for testing, and it performs worse (twice the time) than my local SQL Server, running on a regular laptop. Disabling the indices for ItemId and PropertyId before executing roughly halves the speed both locally and in Azure, but the relative performance is the same.

I am aware of the fact that Azure SQL is generally quite slow on large INSERTs, but it seems like at least a P15 instance should outperform a local SQL Server 2014 Developer Edition. Is there anything i can do to tweak my stored procedure or database setup to make it work faster in Azure?

EDIT: Here is the execution plan for Azure:

... and local:


texts: Is there any limit on how deep the Server Owner can access my data?

texts:
this is my first post on DBA. I'd like to get some clear ideas from you.
First of all, this may sound as a discussion but it's not a debate.
What I'm looking for could be something like "Yes, they can do whatever they want" or "No, no way!"

Recently I've finished developed a web application. Once it's done I was told to contact Server Area department so they could help me out with uploading my SQL db, grant permissions, etc etc.

So, I did that.
They explained how they were used to work and we agreed on uploading a non-operational version db, so we could test permissions etc. They told me to handle them a back up of the db. They would restore it.
First thing making noise within me. Shouldnt I be the one restoring back ups? "Well.. I'm no dba yet, I'm sure they know better." was my answer to myself since those could be their practices.

Two days later they send me an email, saying the backup is up and giving some suggestions.
They needed my db tables to be normalized and have the proper indexes.

My reaction wasn't sweet. First of all, we both had talked about it and the back up wasnt not the operational one... So, are any conclusions relevant?... and bay the way, tables are all fully normalized. Second, is it ok for them to access that deep? What if the information contained in my db is critical?

Thanks a lot, hope for some insights on this matter.


texts: .bat file works manually, but does nothing in SQL Server Agent job

texts:
I am able to run a .bat file that copies an .exe file into a specific folder, and runs it to extract some files manually.

When I use it as part of a SQL Server Agent job (step 1), I get success for the job called "Daily_Update", but it doesn't actually do anything.

The server that the SQL Server is on is different from where the file location where the files are moved and executed. I am specifying the file location with the UNC Path. \\A10 is the server with the scripts and .dat files that I am manipulating, \\A08 is the one with the SQL Server.

With the disk designator c:\ notation, I get:

  The system cannot find the path specified.

When I use the UNC path name, I get:

  Access is denied.

When I run the .bat file from the desktop of the server where the SQL Server lives, it works fine manually.

Any ideas why this might be happening?


texts:
We are in the process of spring-cleaning our production MySQL 5.0 database, and this includes the renaming a number of tables.

We are using the following syntax for the rename

ALTER TABLE `mydb`.`table_a`
RENAME TO `mydb`.`table_b`

The expected behaviour would be that the GRANTS for table_a would be shifted across to table_b.  This does not seem to be the case.

Assuming that what we see is correct is there a simple solution we can add to our scripting?

It looks like we should be able to update the TABLE_PRIVILEGES.TABLE_NAME column in the information schema, but we are assuming that whilst reading the schema is OK, we shouldn't be manually updating it.

Which leads to reading TABLE_PRIVILEGES and Granting / Revoking privileges line by line...

Any other options?


texts:
I am writing scripts to backup the content of all my Postgres 9.x and 10.x databases. I need to determine when the last change occurred in a database, or whether the database has changed since the last backup. I would like to know whether a backup is required before I call pg_dump, for example.

Currently I do pg_dump and use md5sum before and after to detect wither something changed. That means I do the pg_dump work anyway, even though it may not have been necessary.
Any thoughts?


texts: Log Shipping copy job doesn't delete old files on a secondary node that is part of AlwaysOn configuration

texts:
I have the following setup, all servers are SQL 2014 Enterprise:

2 node AlwaysOn availability group;
third server for off-site DR.

Log backups are being taken on the primary node, copied to the secondary node (restore job is disabled of course) and copied and restored to the DR server.

Everything is working great, except the fact that the log backup files are not being deleted on the secondary node. I noticed it because the folder kept growing, when I opened it I saw all the logs are there since day 1 when log shipping was set up. All jobs are executed without errors and the interval to keep the log backups is set. How can I troubleshoot this?

UPDATE: Apparently, the process of deleting the old files is actually part of the RESTORE job and hot the COPY job.

Now the question is, can I safely run the RESTORE job on a secondary AlwaysOn node as well?


texts: Closed loops in database model, does it affect the performance?

texts:
I have heard that having loops in the database relational model should be avoided. From what I can think of, it is not a good practice, because there will be more than one different pathes to get the same results and might lead to inconsistent records in the query result.

Is that true? Also I'm wondering if it affects the query plans and SQL Server performance.

What do you think? What are the other troubles it might cause?


texts: How to connect to a Postgres server in a Docker (1.12.0) container on a Mac?

texts:
The latest release of Docker doesn't use a virtual machine anymore, instead using a hypervisor to connect to the containers. This means I can no longer login to postgres with psql:

➜  postgres git:(master) ✗ docker run -d -p 5433:5432 db postgres
<sha>
➜  postgres git:(master) ✗ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
111f3bed4c52        db                  "/docker-entrypoint.s"   17 minutes ago      Up 17 minutes       0.0.0.0:5433->5432/tcp   zen_hugle
➜  postgres git:(master) ✗ psql -p 5433 -U postgres
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5433"?

I have also tried specifying localhost as the host, but that results in a strange output:

➜  postgres git:(master) ✗ psql -h localhost -p 5433 -U postgres
psql: %

Does anyone know what to do in this case? Thank you.


texts: sp_Blitz - RegOpenKeyEx() returned error 2

texts:
I'm running the latest sp_Blitz script on a SQL 2016 Std server on a Windows Server 2016 physical box. I'm getting the following error. What could be causing this and how do I troubleshoot it?

RegOpenKeyEx() returned error 2, 'The system cannot find the file
specified.' Msg 22001, Level 1, State 1 Warning: Null value is
eliminated by an aggregate or other SET operation. Msg 0, Level 11,
State 0, Line 0 A severe error occurred on the current command.  The
results, if any, should be discarded.


texts:
I have two table one is photos and another one is photo_detail, this is how i have created both tables:

/* creating photos table */
CREATE TABLE photos(
->  pid INT(10) NOT NULL AUTO_INCREMENT,
->  purl VARCHAR(200) NOT NULL,
->  uid INT(5) NOT NULL,
->  time TIMESTAMP NOT NULL,
->  title VARCHAR(80) NOT NULL,
->  PRIMARY KEY (pid)
->  )
->  ENGINE = InnoDB DEFAULT CHARSET = utf8;

/* creating photo_detail table */

CREATE TABLE photodetail(
->  pid INT(6) NOT NULL,
->  ptext TEXT NOT NULL,
->  location VARCHAR(40) NOT NULL,
->  FOREIGN KEY (pid) REFERENCES photos(pid)
->  )
->  ENGINE = Innodb DEFAULT CHARSET = utf8;

My question is how i can add the automatically incremented ID from photos table to photo_detail table  while storing the photo? i am using PHP language! any suggestion on this table design?


texts:
I have issues importing "not-so-small" dumps to MariaDB 10.4.6 in CentOS, but the error log and stderr is empty.

I dumped many databases with mysqldump sized around 50MB / 500.000 rows each, and tried to import them to my MariaDB server in a VPS. The problem is for tables that have many consecutive large INSERT statements, only the last one gets inserted, all the previous INSERT for the same table are completely ignored. I tried importing the same dump in my local machine to the same MariaDB version in Arch-linux and it works (the configuration variables are the same in both servers*). Tried setting max_allowed_packet=1G and adding BEGIN and COMMIT surrounding each sequence of INSERT, but the results are the same. The only way it works is running by hand each INSERT one by one. I spent many hours already googling and trying many different configurations without success.

So, my question is: is there any way to debug why all the previous INSERT are being ignored when I import the full dump? Any ideas?

* I checked it making a diff of the output of mysqladmin variables of both servers.


texts: How to forcibly flush the undo log/tablespace of UNDOTBS1

texts:
I'm looking for a way to flush the undo logs so that the UNDOTBS1 doesn't give me an error saying that my tablespace isn't large enough. I tried using a commit, but without success, it still gives me the UNDOTBS1 tablespace not large enough.

I'm aware of something like CHECKPOINT NUM, which normally allows you to flush the undo logs at the given NUM. I was wondering if there was a similar functionality for anonymous blocks in PL/SQL given that I do not have the rights to modify UNDOTBS1.

    SET serveroutput ON;
    DECLARE
      --Cursor declaration
      CURSOR a_cur IS
      SELECT * FROM aTable
      FOR UPDATE;

      --Cursor row
      a_rec a_cur%ROWTYPE;

    BEGIN
      -- TABLE
     OPEN a_cur;
      LOOP
       FETCH a_cur INTO a_rec;
       EXIT WHEN a_cur INTO %NOTFOUND;

       --Update the record
       UPDATE aTable SET
         field1 = 'aVal',
         field2 = 'aVal',
         field3 = 'aVal'
       WHERE CURRENT OF a_cur;

       -- Attempting to force flush the UNDOTBS1 since it is not large enough
       --COMMIT;

      END LOOP;
      CLOSE a_cur;
      COMMIT;
    END;


texts: DB2 CASE statement faster with parentheses around entire WHEN

texts:
Trying to optimize slow queries, someone on my team come across this. Having this in our stored proc:

CASE
  WHEN MYCOL BETWEEN @STARTNUM AND @ENDNUM
    AND RECTYPE = 'RO'
    THEN 1
    ELSE 0
END AS MYRESULT

runs ~10 seconds slower than having this:

CASE
  WHEN (MYCOL BETWEEN @STARTNUM AND @ENDNUM
    AND RECTYPE = 'RO')
    THEN 1
    ELSE 0
END AS MYRESULT

The only difference is the parentheses around the entire WHEN clause. We're at a loss as to what is going on. This with DB2 on an iSeries.

What could be causing this performance difference, and how can we take advantage of it to speed things up? As a programmer, those extra parentheses mean nothing. As a DbA, what could the optimizer be doing here to have such a drastic effect on performance?

Edit: Here's the query; it's pretty nasty, but that's the data we have to work with. I've run just a single sub-select (before the union) and still had a significant performance difference, so the union and final select aren't causing the bulk of the performance hit.
I realize some of the date manipulation can be fixed to remove the function calls; someone will get to that eventually.

SELECT
ID ,
ABBREVIATION ,
METRICS.NAME ,
METRICS.TYPE AS TYPE_ID ,
JOBTYPE.NAME AS TYPE_NAME ,
SUM ( CREATEDORDUE ) AS CREATEDORDUE ,
SUM ( COMPLETED ) AS COMPLETED ,
SUM ( COMPLETEDINSIDE ) AS COMPLETEDINSIDE ,
SUM ( CASE WHEN DAYS <= 7 THEN 1 ELSE 0 END ) AS ONTIME ,
SUM ( CASE WHEN DAYS > 7 AND DAYS <= 14 THEN 1 ELSE 0 END ) AS ONEWEEKLATE ,
SUM ( CASE WHEN DAYS > 14 AND DAYS <= 21 THEN 1 ELSE 0 END ) AS TWOWEEKSLATE ,
SUM ( CASE WHEN DAYS > 21 THEN 1 ELSE 0 END ) AS MORETHANTWOWEEKSLATE

FROM (

-- Active File --

SELECT EQMDOM AS ID ,
CT1TID AS ABBREVIATION ,
CT1NAM AS NAME ,
JBCTL ,
JBWKA AS TYPE ,
CASE
    WHEN JBCDT BETWEEN @STARTDATENUM AND @ENDDATENUM THEN 1
    ELSE 0
END AS COMPLETED ,
1 AS CREATEDORDUE ,
-- Stackexchange question
CASE
    WHEN ( JBCDT BETWEEN @STARTDATENUM AND @ENDDATENUM AND JBDTC = 'RO' ) THEN 1
    ELSE 0
END AS COMPLETEDINSIDE ,
-- /Stackexchange question
MASTER.USDATE_DATEDIFFERENCE (
    CASE WHEN JBCDT = 0 THEN @ENDDATENUM WHEN JBCDT > @ENDDATENUM THEN @ENDDATENUM ELSE JBCDT END ,
    CAST ( MASTER.DATETOCYMDDATE ( DATE ( JBCRT ) ) AS DEC ( 7 , 0 ) ) ,
    CAST ( 'DAYS  ' AS CHAR ( 6 ) )
    ) AS DAYS

FROM EMJOB
JOIN EMEQM ON JBUNT = EQMNUM
JOIN TABLE ( MASTER.CSVTABLE ( @LOCATIONS ) ) AS LOCATIONS ON VAL = EQMDOM --CSVTABLE function separates a csv list into a usable table
JOIN AAP030 ON CT1NUM = EQMDOM

WHERE JBSYS = 'PM'
AND ( JBCDT >= MASTER.DATETOCYMDDATE ( DATE ( @STARTDATE ) ) OR JBCDT = 0 )
AND JBCRT <= @ENDDATE
AND JBCRT > '2012-01-01-00.00.00.000000'  -- bad data before 2012
AND JBSTS <> 'D'

UNION

-- History File --

SELECT
EQMDOM AS ID ,
CT1TID AS ABBREVIATION ,
CT1NAM AS NAME ,
JHCTL ,
JHWKA AS TYPE ,
CASE WHEN JHCDT BETWEEN @STARTDATENUM AND @ENDDATENUM THEN 1 ELSE 0 END AS COMPLETED ,
1 AS CREATEDORDUE ,
-- Stackexchange question
CASE
    WHEN ( JHCDT BETWEEN @STARTDATENUM AND @ENDDATENUM AND JHDTC = 'RO' ) THEN 1
    ELSE 0
END AS COMPLETEDINSIDE ,
-- /Stackexchange question
MASTER.USDATE_DATEDIFFERENCE (
    CASE WHEN JHCDT = 0 THEN @ENDDATENUM WHEN JHCDT > @ENDDATENUM THEN @ENDDATENUM ELSE JHCDT END ,
    CAST ( MASTER.DATETOCYMDDATE ( DATE ( JHCRT ) ) AS DEC ( 7 , 0 ) ) ,
    CAST ( 'DAYS  ' AS CHAR ( 6 ) )
    ) AS DAYS

FROM EMJOBH
JOIN EMEQM ON JHUNT = EQMNUM
JOIN TABLE ( MASTER.CSVTABLE ( @LOCATIONS ) ) AS LOCATIONS ON VAL = EQMDOM
JOIN AAP030 ON CT1NUM = EQMDOM

WHERE
JHSYS = 'PM'
AND ( JHCDT >= MASTER.DATETOCYMDDATE ( DATE ( @STARTDATE ) ) OR JHCDT = 0 )
AND JHCRT <= @ENDDATE
AND JHCRT > '2012-01-01-00.00.00.000000'
AND JHSTS <> 'D'

)
AS METRICS
JOIN JOBTYPES AS JOBTYPE
    ON METRICS.TYPE = JOBTYPE.TYPE
GROUP BY METRICS.ID , METRICS.ABBREVIATION , METRICS.NAME , METRICS.TYPE , JOBTYPE.NAME ;


texts: How can I guarantee the integrity and consistency of “distant” relationships?

texts:
We have a database structure like the one shown in the (very simplified) diagram below:

As you can see, there is a table Categories, holding data like this:

Id Description
1  Car
2  Truck

The table VehicleParts holds the data that correlates which vehicle has which part.

My question

How can I guarantee that one row in the VehicleParts table does not reference a Version of a Car and a Part of a Truck or vice versa?


texts: access denied when mysql to localhost using public ip

texts:
I would like to use the command below to connect to my Mac's local database (xx.xxx.xxx.xxx is the Mac's ip address)

mysql -u cgao -h xx.xxx.xxx.xxx -p

and I got the error message:

ERROR 1045 (20000): Access denied for user 'cage'@'dhcp-xx-xxx-xx-xxx.dyn.mycompany.com' (using password: YES)

But it will succeed when I enter the same command in another computer. How can I solve it? Can I only use localhost or 127.0.0.1 to local machine?

I feel it is somehow related to the dynamic ip address in my company. But I didn't find the exact answer on the web. Can someone elaborate the reason and share suggestions?


texts: Are there performance benefits to turning off archivelogging? Is it wise?

texts:
The first part of the question is pretty simple; has anyone run a DB in both archivelog and noarchivelog mode? Were there any measurable performance benefits to turning off logging that you could share?

The second half is a guess more specific to my circumstances but if anyone has any thoughts that'd be good. Currently our Master ( main writing box ) is running in archivelog mode complete with standby and table-level backups. The standby is tested twice weekly, and the backups more rarely. Not as evil as some but still not good. So, we've finally persuaded senior management to shell out for a shiny new box. It's lots better and hopefully won't blow up or fry the motherboard like the last one etc etc.

On this box we'll have hourly backups, replicated offsite down redundant fibre optics, and the backups will be tested at least daily, i.e. everything we can throw at it.

In this situation, and if the answer to the first part is positive, is it actually wise to run a DB in noarchivelog mode? If the box, or the discs die, it'll be quicker to start something up to takes it's place than it was previously and we don't mind losing an hours work if it means a significant improvement in speed.

It still seems a little bit dodgy though? I understand that we'd lose an hours work and are less worried about this than anything else we might have missed in our analysis of the implications of not having a standby.


texts: Select Column Values from all the tables of database ORACLE

texts:
I am trying to fetch column value from all the tables of database where column value matches.

Table A
CAMPUS_ID
1

Table B
CAMPUS_ID
1

Expected Result

TABLE   VALUE
A       1
TABLE   VALUE
B       1

Query

SELECT CAMPUS_ID FROM (SELECT A.TABLE_NAME FROM
user_tables A JOIN user_tab_columns C ON C.TABLE_NAME =
A.TABLE_NAME WHERE C.COLUMN_NAME = 'CAMPUS_ID')
WHERE CAMPUS_ID = '1'

Error Invalid Identifier

Inner query return the correct tables.


texts: Using or not using Gin or Gist index in PostgreSQL, make no differences?

texts:
Question:

Here are about 50 millions rows data, and i would like use PostgreSQL to manage that. The main purpose is text serching, it usually takes several minutes to get the results, and i wondering if PG's trigram indexes is the solution for speed that up.

But after i create the pg_trgm extension, create (gist/gin) index for certain column, and query, the query is not faster than not using none of indexes.

I wondering if i'm using it the right way. Any advice would be greatly appreciated!

Environment:

OS: Windows 10 x64
HDD: 1TB
RAM: 8GB, 1600MHz
CPU: Intel Core i5-4460, 3.2GHz
PostgreSQL: 9.6

Procedure:

Here is the procedure in psql terminal:

1. install the extension:

 $ create extension pg_trgm;

2. normal search:

$ explain (analyze,buffers) select * from tbl_sample where hr_name
like '%ojan%';

QUERY PLAN
Seq Scan on tbl_sample  (cost=0.00..4629896.15 rows=19053316 width=506) (actual time=1.491..171900.251 rows=18623285 loops=1)
  Filter: ((hr_name)::text ~~ '%ojan%'::text)
  Rows Removed by Filter: 37999208
  Buffers: shared hit=2830 read=3919285
Planning time: 0.101 ms
Execution time: 172525.172 ms

3. gist search:

$ create index hr_name_gist_idx on tbl_sample using gist (hr_name
gist_trgm_ops);

time:5513539ms

$ explain (analyze,buffers) select * from tbl_sample where hr_name
like '%ojan%';

QUERY PLAN
Seq Scan on tbl_sample  (cost=0.00..4629896.15 rows=19053316 width=506) (actual time=58.490..172845.089 rows=18623285 loops=1)
  Filter: ((hr_name)::text ~~ '%ojan%'::text)
  Rows Removed by Filter: 37999208
  Buffers: shared read=3922115
 Planning time: 66.508 ms
 Execution time: 173668.369 ms

4. gin search:

$ drop index hr_name_gist_idx;
$ create index hr_name_gin_idx on tbl_sample using gin (hr_name
gin_trgm_ops);

time: 476069ms

$ expalin (analyze,buffers) select * from tbl_sample where hr_name
like '%ojan%';

QUERY PLAN
Bitmap Heap Scan on tbl_sample  (cost=175607.20..4335888.65 rows=19053316 width=506) (actual time=6385.809..179910.643 rows=18623285 loops=1)
  Recheck Cond: ((hr_name)::text ~~ '%ojan%'::text)
  Rows Removed by Index Recheck: 32648417
  Heap Blocks: exact=18691 lossy=3554283
  Buffers: shared hit=3664 read=3578519
  ->  Bitmap Index Scan on hr_name_gin_idx  (cost=0.00..170843.87 rows=19053316 width=0) (actual time=6353.253..6353.253 rows=18623285 loops=1)
        Index Cond: ((hr_name)::text ~~ '%ojan%'::text)
        Buffers: shared hit=3664 read=5545
Planning time: 20.645 ms
Execution time: 180633.642 ms


texts: I’m running sql server 2012 and the installed ram on that machine is 128 GB.  The performance of the bulk insert is very low. The bulk insert of 10 million records takes about 2 hours. We have checked the cpu, disk io, memory and everything seems to be ok. I’ve just checked the wait_type and found out the CXPACKET wait_time_ms is   79346890761 and max_wait_time_ms is 2164694 and signl_wait_time_ms 2849080871. Are these numbers too high and could it be the reason for slow performance? The degree of parallelism is set to 0.


texts: SQL Server: Mixed mode security: Can a non AD user (a local user, remote client) have a trusted connection to SQL?

texts:
I have used SQL Server trusted connections for years. It works well when the client app is a domain user.

Imagine this three server config:

dbserver
webserver
domain controller

I now have a situation of a web app that can no longer run on as a domain user (long story). This is a problem because this app stores its db connection string in the registry. If I use SQL auth, the SQL password is in clear.

Is it possible to ?

IOW: Is it possible to add webserver\localuser to SQL server as a trusted user, or can SQL server only trust domain users?


texts: Partitioning a 4TB table while staying online (SQL Server Standard)

texts:
We have a table that is currently ~4TB in size. We wish to introduce partitioning on this table. The table is already clustered on the key we wish to partition on.

The options appear to be

Use ALTER TABLE SWITCH to switch the data of the table to a range-partitioned table that has only one partition (zero cost). Then call ALTER PARTITION FUNCTION for each partition we wish to introduce (definitely not zero cost)
re-create the clustered index using DROP_EXISTING=ON

We're not using SQL Enterprise, so there's no ability to do this online.

Running these queries locks the entire table.

Even if we accepted the lock, we have no real way of estimating how long this would take (and whether we could complete it in time over a weekend)
Most of the data is archive data, there's only a small slice of data that's actually 'live' and would be a problem when it's locked. Trying to figure out if there's a strategy using ALTER PARTITION and ALTER TABLE SWITCH that allows us to do most of this with the data swapped out on a staging table.
Can anyone suggest a way we can do this progressively?

Thanks!


texts: Relationship with optional in-between table

texts:
I am currently implementing a self-assessment tool. The requirements for its questionnaires are as follows:

  A questionnaire has one or more dimensions and every dimension has either one or more questions or one or more subdimensions (and every subdimension has one or more questions).

Sub dimensions are irrelevant for the evaluation (while dimensions are relevant) and are only used for separating questions of dimensions, providing introduction texts and categorization inside reports that will be generated after filling out a questionnaire.

When I directly translate those requirements into an ERD, it looks like this:

Or, written out (I left out most irrelevant columns for clarity):

CREATE TABLE IF NOT EXISTS questionnaire
(
    id BIGSERIAL NOT NULL PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS dimension
(
    id               BIGSERIAL NOT NULL PRIMARY KEY,
    order_index      SMALLINT  NOT NULL,
    questionnaire_id BIGINT    NOT NULL REFERENCES questionnaire ON DELETE CASCADE,
    UNIQUE (questionnaire_id, order_index)
);

CREATE TABLE IF NOT EXISTS subdimension
(
    id           BIGSERIAL NOT NULL PRIMARY KEY,
    order_index  SMALLINT  NOT NULL,
    dimension_id BIGINT    NOT NULL REFERENCES dimension ON DELETE CASCADE,
    UNIQUE (dimension_id, order_index)
);

CREATE TABLE IF NOT EXISTS question
(
    id              BIGSERIAL NOT NULL PRIMARY KEY,
    order_index     SMALLINT  NOT NULL,
    dimension_id    BIGINT REFERENCES dimension ON DELETE CASCADE,
    subdimension_id BIGINT REFERENCES subdimension ON DELETE CASCADE,
    CHECK ( dimension_id IS NOT NULL AND subdimension_id IS NULL
        OR subdimension_id IS NOT NULL AND dimension_id IS NULL ),
    UNIQUE (dimension_id, order_index),
    UNIQUE (subdimension_id, order_index)
);

While this works, I am not very happy with this solution. For example, if I would want to get all questions of a questionnaire, the best query that comes to my mind would be this:

SELECT q.id
FROM question q
         INNER JOIN dimension d ON q.dimension_id = d.id
WHERE questionnaire_id = 1
UNION
SELECT q.id
FROM question q
         INNER JOIN subdimension s ON q.subdimension_id = s.id
         INNER JOIN dimension d ON s.dimension_id = d.id
WHERE questionnaire_id = 1;

Alternatives that came to my mind:

always referencing the dimension in the question table (so it won't be an either/or anymore and the CHECK constraint would be obsolete).
always using a subdimension, even though there does not need to be one (if a dimension has only one subdimension it would be some kind of 'dummy' subdimension that would not provide an introduction text etc). This would mean there is no direct reference from a question to a dimension.

Does anyone have any better ideas? I feel like I am missing the obvious.