texts: optimizing a select query of 1.5 million entry

texts:
I have the following query:

SELECT s0_.id, s0_.lowresimageurl
FROM app_instagram_shop_picture s0_
FORCE INDEX ( idx_deleted_shopId )
LEFT JOIN app_instagram_shop s1_ ON s0_.shop_id = s1_.id
AND (
s1_.deletedAt IS NULL
)
WHERE (
s1_.expirydate IS NULL
)
AND (
s0_.deletedAt IS NULL
)
ORDER BY s0_.checked ASC
LIMIT 0 , 30

the table of app_instagram_shop_picture has 1.5 million entry and the query above takes about 10 seconds to finish. Here's the structure and indexes that is in that table:

http://cl.ly/image/3E3T2B001W0Q

Here's the structure and indexes of the app_instagram_shop table.

I am running out of ideas on how I can further optimize this again.


texts: How can I pass a string as where clause to a function?

texts:
How can I convert this:

CREATE OR REPLACE FUNCTION getFooData()
RETURNS TABLE(foo_name name text) AS
$BODY$
BEGIN
    RETURN QUERY select foo_name from foo where x = 3;
END
$BODY$
LANGUAGE plpgsql VOLATILE

into this:

CREATE OR REPLACE FUNCTION getFooData( IN criteria text )
RETURNS TABLE(foo_name name text) AS
$BODY$
BEGIN
    RETURN QUERY select foo_name from foo where $1; -- > I CAN'T DO THIS
END
$BODY$
LANGUAGE plpgsql VOLATILE

I need to create a flexible query criteria in my function or else I'll be in a nightmare to create several functions to cover all foo fields combinations and pass only the values thru parameter. The problem is in some situations I may use foo.x, foo.y and foo.z in other I may use foo.h, foo.x. I may use any fields in search criteria in any combinations.


texts: Why does SQL Server Mangement Studio not script Indexes by default?

texts:
Recently I realized by default, scripting the database does not including indexes, and I have to change the option every time.

Could anyone please explain the reason? Is there any insight about this default behavior, or just because Microsoft likes to do so?


texts: Efficient way to get one value from each table list partition?

texts:
I use PostgreSQL partitions to store OHLCV data for multiple markets and timeframes:
create table candles (
  market character varying(64) not null,
  timeframe character varying(64) not null,
  "timestamp" timestamp without time zone not null,
  open numeric not null,
  close numeric not null,
  high numeric not null,
  low numeric not null,
  volume numeric not null
) partition by list(market)

# Partitions
create table candles_btc_usd partition of candles for values in ('btc_usd') partition by list(timeframe)

create table candles_btc_usd_one_minute partition of candles_btc_usd for values in ('one_minute')
create table candles_btc_usd_five_minutes partition of candles_btc_usd for values in ('five_minutes')
...

Now, let's say I want to get the most recent candle from btc_usd for timeframe five_minutes, for that I can run this simple query:
select * from candles where market = 'btc_usdt' and timeframe = 'five_minutes' order by timestamp desc limit 1

Now, what if I want to select the most recent candle from all timeframes of btc_usd? Is there some way that I can do that query efficiently?
I was able to get it with this query:
select distinct on (timeframe) timeframe, timestamp from candles where market = 'btc_usdt' order by timeframe, timestamp desc;

But it is very inefficient, it takes some time to conclude, and EXPLAIN ANALYZE shows that PostgreSQL does a seq scan in each timeframe partition of candles_btc_usd.
Also, let's say I have other markets, what would be an efficient query to get the most recent candle from all timeframes from all markets?
Note:
I don't know if this helps, but if changing the order of my partitions from market -> timeframe to timeframe -> market would allow a more efficient query, I'm ok making this change in my database.


texts:
Brief Architecture:

I am creating a SSIS package to generate Excel reports. There are different types of reports, So I have created a table to track each user request for report generation and tracking the status of request. A Sql job picks each request and generates the excel file on FTP server.

Problem:

In control flow, I am executing select on "ReportRequests" table and iterating recordset using a For Each Container. I have created a variable named "ReportType" to get column value from database.
In Data flow task, on basis of "ReportType" variable, I want to execute different queries on different databases to generate report. I will have 3-4 different ado.net sources with different queries. But those should work on basis of value of "ReportType"

How can I implement this conditional data flow in data flow task ?


texts: Sum multiple columns, based on distinct values in different Columns

texts:
I have the following table, in an Azure SQL DB that has duplicate values that I'm trying to Sum.
Here is the Logic: If the PaymentID is unique, then Sum Payment,
If the creditID is unique, then sum credit,
if the debitid is unique, then sum debit.
And take the max(source)
The idea is to get a single line, per ID with distinct values for the ID summed.

id
payment
credit
debit
Source
paymentid
creditid
debitid

1510142123
-589.53
0
0
CC
5831879
NULL
NULL

1510142123
-589.53
0
0
CC
5831882
NULL
NULL

1510142123
-155.06
0
0
CC
5898896
NULL
NULL

157771145
-126.42
0
0
CC
5885900
NULL
NULL

157771145
-58.73
0
0
CC
5885903
NULL
NULL

158088837
-55.14
0
-3.45
CC
5897306
NULL
5897303

158088837
-5.75
0
-3.45
CC
5897309
NULL
5897303

158464166
-161
0
-3.45
CC
5910551
NULL
5910548

158464166
-24.15
0
-3.45
CC
5910554
NULL
5910548

1591970734
-111.61
0
0
Bank
5939648
NULL
NULL

1591970734
-0.01
0
0
Cash
5939711
NULL
NULL

1591970734
-0.01
0
0
Cash
5939714
NULL
NULL

159297565
-708.93
20
0
CC
5943728
5910848
NULL

159297565
-0.02
20
0
Cash
5948207
5910848
NULL

For example:
158464166 | -185.15 | 0 | -3.45 | CC | 5910551 | 5910548
(in the above - I've taken the min(paymentid) to make it look nicer
Please note that although in the above snippet, Creditid and Debitid only have a duplicate ID, it's possible that they may have distinct IDs, so any code will have to be able to handle that. PaymentID will always be unique.
It is also possible that the values for payment, credit and debit may not be unique (e.g. a payment of $50 is made twice to a single ID), so we can't group on payment.
I got as far as this:
SELECT id, sum(payment), sum(credit), sum(debit), max(source), creditid, debitid
FROM (
  SELECT *,
         COUNT(*) OVER (PARTITION BY id) AS cnt
  FROM Temp_Payment) AS t
WHERE t.cnt > 1

group by id, creditid, debitid

but it's not giving the expected outcome.


texts:
I am trying to create table with summary of few other tables. All solutions i came up with are very slow and clearly not optimal. Initially I tried with multitude of subqueries but that was very slow. Now I have JOIN but I am not fully grasping which join I should make.

2 tables are like following

Table 1: recent_items

ID, timestamp, Text1...TextN

Table 2: insider_trades

ID, timestamp, Text1...TextM

Where all columns but ID are not related at all and # of cols is not the same. And no IDs are not foreign keys, long story short DB has different collations and i wasn't able to unite them yet. (I didnt create the DB itself)

My best attempt with join so far is:

SELECT
    DATE(recent_items.time),
    SUM(CASE WHEN crit1 = "twitter" THEN 1 ELSE 0 END) Social,  # one that works
FROM
    recent_items
JOIN
    (SELECT crit2, crit3, DATE(filedate) AS fdate
     FROM insider_trades
    ) AS InInfo ON DATE(recent_items.time) = DATE(InInfo.fdate)
GROUP BY
    DATE(recent_items .time)

I need to do 2 things

Sum from table 1 (done)
Sum from table 2, irrelevant of ID but based on date specified by
    SELECT date(table1.time) . Ex. total value of crit2 on certain
    date or something like SUM(crit2 where crit3 = "A" and date(recent_items.time) = date(InInfo.fdate))

For 2 I tried passing user defined variable but with not much luck either


texts: How to find DBCC events reported by sp_Blitz

texts:
sp_Blitz reports:

Overall Events
7 DBCC events have taken place between Oct 14 2019  3:22PM and Nov 19 2019 12:24PM. This does not include CHECKDB and other usually benign DBCC events.

How do I find out what the 7 events were?

Thanks


texts: Copy Database option not found in SQL Server Management Studio 2008 R2

texts:
I am unable to see the Copy Database option in SQL Server Management Studio 2008 R2.

Can anyone tell me whats going on please?


texts: Error 18456 when moving database to new environment

texts:
Last night I moved a database from Server Q (QA) to Server P (prod). Part of the process was to remove some QA logins we use and replace them with production logins.

Things seemed to go well but during testing a job running as Login F failed with error 18456. Simple, it wasn't on the list of logins sent to me to provide access to. We changed the job that runs on a separate server to run under another login and it worked.

Seems simple, but investigating I found that this login F didn't have access to the database in our QA environment either. It was set up as a database user for another database and we have cross database permissions enabled.

Checking Server P (prod), it's set up the same way. So I'm trying to figure out why a job running as this database user worked in one environment but failed in production where the configuration seems to be the same.


texts: How to resolve unmodifiable replica set state

texts:
We are using mongoDB 3.0, and our setup uses six instances with one PRIMARY and the rest of them as SECONDARY.

We were trying to remove three of these using the following command sequence -

rs.remove("host1:port") //same for host2, host3

Now that we wanted to add those back, there is a weird state that the rs.conf() results in. i.e

The replica set before removal was at the _id:1,2 and 3 Now that we have added them back, they are at the _id:7,8, and 9

Why does this happen so, and how do I correct the rs.conf()?

Also if I again try and remove host1, host2 or host3, they are not getting removed.

rs.remove("host1:port")

displays the conf() value back again, so we are unable to remove them now. Is there a way to overcome from such state?

rs.conf()

{
    "_id" : "shard1",
    "version" : 15,
    "members" : [
        ...
        {
            "_id" : 4,
            "host" : "host4:port",
            "arbiterOnly" : false,
            "buildIndexes" : true,
            "hidden" : false,
            "priority" : 1,
            "tags" : {

            },
            "slaveDelay" : 0,
            "votes" : 1
        },
        {
            "_id" : 5,
            "host" : "host5:port",
            "arbiterOnly" : false,
            "buildIndexes" : true,
            "hidden" : false,
            "priority" : 1,
            "tags" : {

            },
            "slaveDelay" : 0,
            "votes" : 1
        },
        {
            "_id" : 8,
            "host" : "host1:port",
            "arbiterOnly" : false,
            "buildIndexes" : true,
            "hidden" : false,
            "priority" : 1,
            "tags" : {

            },
            "slaveDelay" : 0,
            "votes" : 1
        }
    ],
    "settings" : {
        "chainingAllowed" : true,
        "heartbeatTimeoutSecs" : 10,
        "getLastErrorModes" : {

        },
        "getLastErrorDefaults" : {
            "w" : 1,
            "wtimeout" : 0
        }
    }
}


texts:
I'm developing a trigger which starts and stops services when the pdb is open. The point is that my trigger doesn't stop the service if I execute the

ALTER PLUGGABLE DATABASE ALL SAVE STATE;

with some services up. So it gives me the error:

ORA-00604: error occurred at recursive SQL level 3
ORA-44303: service name exists
ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 21
ORA-06512: at "SYS.DBMS_SERVICE", line 316

Is there any other way to start services on standby database?

CREATE OR REPLACE TRIGGER managed_service_start
    AFTER STARTUP ON DATABASE
DECLARE
    pdb_role VARCHAR2(64);
    host VARCHAR2(64);
    pdb_name VARCHAR2(64);
    rw_service VARCHAR2(64);
    ro_service VARCHAR2(64);
    rw_up VARCHAR2(1);
    ro_up VARCHAR2(1);
BEGIN
    SELECT database_role INTO pdb_role FROM v$database;
    SELECT host_name INTO host FROM v$instance;
    SELECT Upper(sys_context ('userenv', 'con_name')) INTO pdb_name FROM dual;
    SELECT pdb_name || '_RW' INTO rw_service FROM dual;
    SELECT pdb_name || '_RO' INTO ro_service FROM dual;
    -- validate that services are up. if they are down the variable will be null.
    SELECT max('Y') INTO rw_up FROM v$services WHERE name = rw_service;
    SELECT max('Y') INTO ro_up FROM v$services WHERE name = ro_service;
    IF pdb_role = 'PRIMARY' THEN
        IF (rw_up IS NULL) THEN
            DBMS_SERVICE.START_SERVICE(rw_service);
        END IF;
        IF (ro_up = 'Y') THEN
            DBMS_SERVICE.STOP_SERVICE(ro_service);
        END IF;
    ELSE
        -- IF host IN ('host_name1','host_name2') THEN
        IF host LIKE '%de%' THEN
            IF (ro_up IS NULL) THEN
                DBMS_SERVICE.START_SERVICE(ro_service);
            END IF;
            IF (rw_up = 'Y') THEN
                DBMS_SERVICE.STOP_SERVICE(rw_service);
            END IF;
        ELSE
            IF (rw_up = 'Y') THEN
                DBMS_SERVICE.STOP_SERVICE(rw_service);
            END IF;
        END IF;
    END IF;
END;
/


texts: Finding the query causing the missing index DMVs

texts:
How can I find the query causing the missing index DMVs suggesting to create a particular index?

Thank you.


texts: Store Procedure Two different database and join help

texts:
I try to write store procedure that ı have a question.Firstly I have two db in same local server.One of them is hfcenta_test db has agency old datas and it has country code column with country name.Now I have different db and ı have coursesystemdb that has country data but it has country_ıd column and ı have countries tables extra. I want to old tables data transferred according to new agencies table in different db.
At the picture ı tried some things and achieved two tables combined. But how to update old db tables according to new db agency table ?
Thanks
enter image description here


texts: Is there a way to tell if a GUID column contains sequential or traditional / random GUID's?

texts: Have a table that is populated by Entity Framework 3.1.1 code.  The table contains a clustered index / primary key on ID which is defined as of type GUID with no default value.  Is there a way in SQL to look at the row data and tell if the GUID values are a traditional random GUID vs a sequential GUID?


texts: Extremely Slow StatMan query issue with #SP2010 farm

texts:
The issue I’m experiencing is explained in a blog-post I created for this, (easy to refer to and to manage the responses / solutions in 1 location) which can be found here: http://schoennie.blogspot.com/2013/09/slow-statman-query-issue-with-sp2010.html

Short summary is that this single server install of SharePoint 2010 on top of SQL 2008R2 (same server) is experiencing a VERY slow response for every first upload during a certain interval (seems to be every morning).
Analysing the SQL activities revealed to me that when you start an upload, this query get’s executed before the “insert part” of the upload:

SELECT StatMan([SC0], [LC0])
FROM   (SELECT TOP 100 PERCENT CONVERT(VARBINARY,
                                        SUBSTRING ([Content], 1, 100) +
                                        +SUBSTRING([Content], CASE
                                                                WHEN LEN([Content]) <= 200 THEN 101
                                                                ELSE LEN([Content]) - 99
                                                            END, 100)) AS [SC0],
                                        DATALENGTH([Content])   AS [LC0]
        FROM   [dbo].[AllDocStreams] WITH (READUNCOMMITTED)
        ORDER  BY [SC0]) AS _MS_UPDSTATS_TBL

I spent a couple of days now trying to get more info on this StatMan query and why it’s causing the disk i/o to go through the roof and
The Disk Queue Length to grow above the 5 value (normally around 0.01)

Please share your thoughts on this or maybe point me in a certain direction / resource ? I’m involved as SharePoint Consultant & DBA in this software space for about 8 years now, but I have not yet seen something like this !

Thanks so much in advance,
Jeroen


texts: Replication reverted partitioned table to primary filegroup, can I re-link to the partitions?

texts:
We have 2 servers:

Server A. production (no partitions)
Server B. New Server (partitions and compression)

We restored a backup to the new server, partitioned a large table by datetime and turned on compression, and we were happy.
Trying to be clever, we turned on replication to update the new server with the latest data on the large table, but that broke the partitioning and compression.
We cannot query any old data on the new server, only data from when we turned on replication.
The clustered index is the old one, and compression is not on, and no partitions to be seen in SSMS, it's using the PRIMARY filegroup  (same as the current live server is using).
On the physical disk, I can see the partition files, and they are still massive with GBs of data, so I know the data is there, but it appears the table has lost the link to it.
It appears turning on replication replicated the filegroup side of things. Is there a way to point the table back to partitions and save all the work we did getting it ready for switch over?
It's a pretty large database. This one table is 11TB on the production server.
SQL Server 2017 Enterprise Edition.


texts: Can I connect to SQL Server 2008 R2 database from SSIS 2012?

texts:
I need to redevelop an ETL process and I'm evaluating options.

I'm running SQL Server 2008 R2 database, and SSIS 2008 will take too long to develop and test the required packages. I understand that SSIS 2012 is significantly improved and will help me develop the packages far more efficiently, but I don't know about the versioning considerations.

Can I run an SSIS 2012 package on a SQL Server 2008 R2 server? Or can I run it on a SQL Server 2012 database and connect to SQL Server 2008 R2 databases? Are there any issues that I'm going to come across connecting in this way?


texts: Can Postgres restrict usage of the SET and SHOW commands?

texts:
Is it possible to restrict a role from being able to set arbitrary session variables, or from being able to view them?

select current_setting('x');

show x;

Even after I've switched roles to a one with lesser permissions, I'm able to run both of the above and retrieve a value I might want hidden from that role. I think that it might be possible to lock down the first statement (that's a built-in function, is it not?), but I can't find anything in the documentation about the latter statement concerning grants or permissions.


texts: How do I join on a timestamp and include NULL entries?

texts:
I am attempting a query to compare the sum of daily accounting invoices to a daily budget. The budget is stored as one value for a month, so I divide that amount by the total number of days as in April would have 30 entries.

However, I want the budget amount over days to exist permanently even if there are no invoices for that day, either yet or actually on that day.

My current query joins on the date_part('day',a.date) of a timestamp which only hits if there are invoices on that day. I am using PostgreSQL version 11.3. I have tried all join possibilities, and  end up with the same result each time. Unless I use CROSS JOIN which does give all 30 days, but the sum of ALL invoices against each day. I had thought that a FULL OUTER JOIN would work, or some other way of using UNION ALL, but as yet I am unsuccessful.

WITH RECURSIVE t(days,budget) AS (
 SELECT 1 as days,b.amount/30 AS budget
  FROM budgets b
  WHERE b.id = 10
 UNION ALL
  SELECT t.days + 1,t.budget
   FROM t WHERE t.days < 30)
SELECT
 t.days AS "Day",
 COALESCE ( SUM ( CASE WHEN a.account_id IN (17,43,44)
       THEN a.credit - a.debit ELSE 0 END),0) AS "Invoices",
 t.budget AS "Budget"
FROM t
LEFT JOIN accounting a
 ON date_part('day',a.date) = t.days
WHERE a.date >= '2020-04-01'
 AND a.date <= '2020-04-30'
 AND a.account_id = 20
GROUP BY 1,3;

For the ouput, lets assume days 1, 2, and 3 have passed where day 2 had no entries.
Current Output
Day Invoices Budget
1 100 50
3 75 50

Desired Output
Day Invoices Budget
1 100 50
2 0 50
3 75 50
4 0 50
5 0 50
...
30 0 50

I am open to other query ideas to accomplish this. I appreciate the help!


texts:
I am a coder, not a database admin, so my question may be a little obvious to some. I just cannot find the correct search term to find the answer.

I'm trying to create a database structure for both currency and language management.
I'm thinking of following structure:

Language

LanguageId int not null primary key
iso2 char(2) not null

For example:

1, EN

2, SV

Then I need to store the name of the language as well, which should be translatable into other languages. For this, I have a table:

Language_Text

LanguageId int not null
LanguageCulture char(5) not null
LanguageName varchar(20) not null
Primary Key(LanguageId, LanguageCulture)

For example:

1, en-US, English

1, sv-SE, Engelska

2, en-US, Swedish

2, sv-SE, Svenska

The issue comes when setting up keys and relationships. In Language there is only 1 key, while in Language_Texts there is an aggregate key. I would like a relation between the two tables, but having problems with this due to difference in keys.

I want to be able to get all various texts for a specific language, as well as query to get a specific culture if need be.

What is the best practice to use in this case?

Thanks in advance for any help in this matter!

Best regards

Peter Kindberg


texts:
Is it possible to downgrade oracle 11.1.0.6.0 to 10.2.0.4.0?
The Compatible parameter need to be changed to "10.2.0"?
Today I have Compatible set to 10.2.0.4.0.

It's not clear on support.oracle if I need to change or not.
This document
http://www.oracle.com/technetwork/database/upgrade/upgrade11gr2-downgrade-methods-179029.pdf

state:

  The major restriction on the downgrade script is that it cannot be run
  if the COMPATIBLE parameter has been raised as part of, or after, the
  upgrade. This is because raising the COMPATIBLE parameter will allow
  on-disk changes that cannot be understood by the earlier version of
  Oracle Database. For this reason, downgrades from Oracle Database 11g
  Release 2 to Oracle Database 9i are not supported. Downgrades from
  Oracle Database 11g Release 2 to the following versions are supported:
   10.1.0.5  10.2.0.2, 10.2.0.3, 10.2.0.4, 10.2.0.5  11.1.0.6,
  11.1.0.7

but this one
http://docs.oracle.com/cd/E11882_01/server.112/e17222/downgrade.htm#UPGRD12554

says:

  If you are downgrading to Oracle Database 10g Release 2 (10.2), then
  the COMPATIBLE initialization parameter must be set to 10.2.0 or
  lower.

thx


texts: What is the fastest way to fetch the last row from a table?

texts:
I have a PostgreSQL table, Prices, with the columns:

price (Decimal)
product_id (Int)

There are also created_at and updated_at columns.

Prices get updated regularly and I keep old prices in the table. For a given product, the last price in the table is the current price.

What is the most efficient way to get the last price for a specific product:

Index product_id and query for the last record
Add a third column active (Boolean) to mark the latest price and create a composite index (product_id and active)
Or something else?


texts: How to convert list of dates to list of date ranges grouped by a condition

texts:
Given a data set in the below format:

State   AuditDate
1       2016-11-01
1       2016-11-02
1       2016-11-03
0       2016-11-04
1       2016-11-05
0       2016-11-06
0       2016-11-07
1       2016-11-08
1       2016-11-09
1       2016-11-10
0       2016-11-11
0       2016-11-12

Is there an easy way to convert it to a list of date ranges grouped (and ordered) by the state? (with the last record ending on GETDATE()) E.g.

State   StartDate   EndDate
1       2016-11-01  2016-11-04
0       2016-11-04  2016-11-05
1       2016-11-05  2016-11-06
0       2016-11-06  2016-11-08
1       2016-11-08  2016-11-11
0       2016-11-11  2016-12-05

I initially tried doing this using Row_Number and Dense_Rank but couldn't get it to work, eventually I did come up with a solution (see below) however it feels very clunky and inefficient, there must be a better way to do this?

IF OBJECT_ID('tempdb..#Records') IS NOT NULL DROP TABLE #Records
Create Table #Records
(
     State bit
    ,AuditDate date
    ,StartDate date
    ,EndDate date
)

Insert Into #Records
(
     State
    ,AuditDate
)
Select 1, '2016-11-01'
union Select 1, '2016-11-02'
union Select 1, '2016-11-03'
union Select 0, '2016-11-04'
union Select 1, '2016-11-05'
union Select 0, '2016-11-06'
union Select 0, '2016-11-07'
union Select 1, '2016-11-08'
union Select 1, '2016-11-09'
union Select 1, '2016-11-10'
union Select 0, '2016-11-11'
union Select 0, '2016-11-12'

Update r1
    Set StartDate = SP.StartOfPeriod
From
    #Records r1
    Outer Apply
    (
        Select
            Max(AuditDate) as LastTimeDifferent
        From
            #Records r2
        Where
            r2.AuditDate < r1.AuditDate
            and r2.State != r1.State
    ) as LD
    Outer Apply
    (
        Select
            Min(AuditDate) as StartOfPeriod
        From
            #Records r3
        Where
            (r3.AuditDate > LD.LastTimeDifferent or LD.LastTimeDifferent is null)
            and r3.State = r1.State
    ) as SP

Update r1
    Set EndDate = isnull(ND.NextTimeDifferent, GETDATE())
From
    #Records r1
    Outer Apply
    (
        Select
            Min(AuditDate) as NextTimeDifferent
        From
            #Records r2
        Where
            r2.AuditDate > r1.AuditDate
            and r2.State != r1.State
    ) as ND

Select Distinct
     State
    ,StartDate
    ,EndDate
From
    #Records
Order By
    StartDate


texts: SQL Server "fix" query for Hash Match instead of Nested Loops

texts:
At the moment I have a view that is running very slow. It takes about 3 minutes to execute. This suddenly happened.

To debug what was going on I used an old dataset and compared both queries. On the old dataset the view returns the data within a second.

I noticed one difference: the current dataset runs on "nested loops" while my old dataset runs with a Hash Match.

You can see that in the picture:

I'm aware of the fact that you simply cannot "force" it to use Hash Match as I saw in other topics, yet I'm having a really hard time finding out why its going for a nested loop.

Is this because something in my dataset is not unique?

How can I fix my current dataset to start using this Hash Match again?


texts: Why is this second query more expensive than the first?

texts:
While reading Graph Databases book, I came across this excerpt:

I can't figure out why the second query is more expensive than the first.
Indeed, both filter on the Bob person.

May anyone explain it?


texts:
I did some research and finally came to this conclusion:

Database instance = processes + memory
Database = (physical) log files + control files + data files.

Is this understanding correct?
Can an application use multiple database instances of the same database?
Can a table in database be used by multiple database instances at the same time?

Thanks.


texts: Problem creating database using default mdf and ldf path

texts:
I'm trying to create a database using the default paths on several SQL Servers at once.
To achieve this I'm trying to use a script that should create the database on each server, however I've run into some difficulties trying to use a variable for the file paths.

Declare @logpath nvarchar(256),
        @datapath nvarchar(256),
        @filenameLog nvarchar(256),
        @filenamedata nvarchar(256);

SET @logpath = (select
                   LEFT(physical_name, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name)) + 1)
                from sys.master_files
                where name = 'modeldev')

SET @datapath = (select
                    LEFT(physical_name, LEN(physical_name)  - CHARINDEX('\', REVERSE(physical_name)) + 1)
                 from sys.master_files
                 where name = 'modellog')

set @filenamelog = @logpath + 'test.ldf'
set @filenamedata = @datapath + 'test.mdf'

CREATE DATABASE [test]
 CONTAINMENT = NONE
 ON  PRIMARY
  ( NAME = test, FILENAME = @filenamedata , SIZE = 51200KB , FILEGROWTH = 10240KB )
 LOG ON
  ( NAME = test_log, FILENAME = @filenamelog , SIZE = 5120KB , FILEGROWTH = 5120KB )
GO

This gives:

  Msg 102, Level 15, State 1, Line 14
  Incorrect syntax near '@filenamedata'.

What am I missing? I feel like it's something obvious staring right at me but I can't seem to find it.

My source for this script was an msdn article here!


texts: How to store many smallish time-series in a relational DB?

texts:
Input Data

Multiple Tests Beds generate measurement data of various complexity.

In it's most basic form, not considering any meta-data, one measurement on a Test Bed will be a small (1 - a few thousand samples) time-series with a couple of dozen channels/signals/attributes per sample.

Measurements across time and Test Beds will have a similar set of signals, but not always the same as sensors are added and removed for the test setups.

Data volume

Currently we estimate our data rate at 6 testbeds x 4 test per hour x 12 hours a day x 4000 samples per test == 1,152,000 samples per day x 365 == 420,480,000 samples per year

_ x 48 columns per sample (currently 32 bit floats, mostly) ~~~ 75 GB per year

(columns in this case refers to channel/signal)

If/When more testbeds are added the data volume might increase accordingly.

Data Input

The test beds generate the data locally and the data is then imported asynchronously into the db. (A few thousand samples might be generated in a the time of one second, then reviewed locally and then either scratched or imported.)

Queries

We expect queries to be mostly on aggregates of the single measurements. I.e., you like to find all measurements (each having 4k samples) where e.g. the mean of channel_output_voltage is within a certain range.

Database layout?

What is a good way to set up tables for this? What factors have to be taken into account?

Theoretically I could go with one table per measurement generating 100,000 tables per year, but it doesn't strike me as a good idea.

Or I could stick everything into one big table (with hundreds of columns) that has room for all channels and channels get added as needed: One row per sample. Unused channels remain NULL.

MEASUREMENTS
------------
measurement_id } PK
time_stamp     }
channel_1 (may be NULL for a certain measurement_id ...)
channel_2
...
channel_n(+1)

Or I could go with an approach of having one table for the samples (timestamps) and one table containing all the values: (one row per sample in MEASUREMENTS table and n rows per sample in the SAMPLE_VALUES table)

SAMPLES            SAMPLE_VALUES
------------       -------------
measurement_id     sample_id
time_stamp         channel_id (links to a channels table where there is a name etc.)
sample_id          channel_value

What other options are there? How to further investigate which option we should choose?

Database products

Due to customer constraints we would like to put this in MS SQLS or Oracle.

From one answer:

  Don't store the raw data, only store aggregates. Seriously.

This assumes that there is a meaningful way to determine ex antes what queries the customer is going to want to run against their data. No way :-)


texts: Postgresql schema only works when wrapped in quotation marks

texts:
I'm seeing an odd behaviour - if I don't wrap my schema name in quotes, the server returns

  [42601] ERROR: syntax error at or near "."

The query is simply:

select count(*) from "authorization".logins

Here's what psql \dn shows:

     List of schemas
     Name      |  Owner
---------------+---------
 authorization | user_login
(1 row)

Schema name doesn't contain upper case letters, only English ones. Tried it using DataGrip and Npgsql (.NET driver for PostgreSQL).
DataGrip added quotes around schema name, when with others it doesn't do so.

Table and schema was created and applied via Flyway


texts:
I have created a PHP site using MySQL. It works well on localhost. However, when I moved it to a remote server, it stopped working.

The error shown is:

  "No connection could be made because the target machine actively refused it".

I am using MySQL Workbench. How do I go about resolving this error?


texts: Checking of one specific element of one array is in another array?

texts:
Suppose I have two arrays. One looking like (3, 9, 10, 39), and one looking like (4, 10, 9, 48, 391, 2).

How would I (through pure MySQL) check if one of the elements in the first array is inside the other array?

Edit By checking, I mean returning true if one item of the first array is occuring in the second array.

Example:

SELECT IsElementFromFirstArrayInAnotherArray(array1, array2) AS isInArray


texts:
This could be a Rookie problem , but it seems that i am not able to solve it.

I have MySQL server installed on my windows machine(MySQL 5.5) under the Admin username root.
Some How i lost the password of MySQL and want to change it , i m going through this link in order to solve it.
under c:> mysql-init.txt is :

UPDATE MySQL.user SET Password=PASSWORD('root') WHERE User='root';
FLUSH PRIVILEGES;

and on command line

C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld --init-file=C:\\mysql-init.txt

it give an message like 'c:\program' is not recognized as internal or external command.

So basically i am stuck at point 6 of MySQL documentation. any help ??


texts: Delete data from a specific partition in MS SQL 2012

texts:
I would like to delete data from a specific partition using the partition ID. I got queries to truncate data from specific partition for SQL 2016 but did not find any query for lower versions.

I tried below query to delete only data from partitions with partition id 14 and 15.

delete from  partitiontable1 WITH (PARTITIONS (14 to 15))

Will the delete statement remove the partition itself along with data?

I want to keep the partition intact and remove data from specific partitions. Say, I have created partitions on a table, it has 5 partitions (1,2,3,4,5) and I want to remove data only from 2nd and 3rd partition.


texts: SQL Server Data Transactional Replication of huge data, locking down table

texts:
We have setup data transactional replication from Server-A(SQL Server 2008 R2) to Server-B(SQL Server 2008 R2). We have a SQL Job scheduled to run everyday on Server-A, which executes single UPDATE command which causes ~120K record updates on a specific table. Now the problem here is, when that single UPDATE command gets executed on Server-A it updates nearly 120K records within few seconds. This is a single transaction on Server-A but it is equivalent to ~120K transactions on Server-B. This causes, the table on Server-B gets lock down for more than 20-30 min. everyday, whenever that SQL Job runs.

I read about MaxCmdsInTran option, but it isn't a recommended one, so cant use it. As an alternate option, we are planning to exclude that particular table from Data Replication and we are planning to execute that SQL Job on both Server-A and Server-B.

I am just wondering if there is any better solution available for this problem.


texts: Can I kill these MSCRM related SQL processes?

texts:
The SQL backups have been failing for a few days... I tried to just do a quick manual full backup and got an error:

sp_who2'active' (screenshot below):  Is it ok to kill SPID 186 and/or what's the general course of action here needing to be taken? I also feel perhaps a restart of the server wouldn't hurt. (Our VSS backups have been failing on this server too).

I am on SQL server 2014 SP1 / Windows server 2012 R2


texts:
I creating scheduled events in MySQL 5.5 (yes, I know, and we do have plans for upgrading, but we're kind of trapped here atm). I have done this before, like:

create event upd_ga_sum_1
on schedule every 1 day
  starts current_date + interval 1 day + interval 3 hour
  do call update_ga_sum_1();

But this one gives me an error:

mysql> create event fin_daily_sum_evt
    -> on schedule
    ->   every 1 day
    ->   starts current_date + interval 1 day + interval 1 hour
    -> comment 'Updates TABLE fin_daily_sum every day at 01:00'
    -> do
    ->   begin
    ->     call logmsg('fin_daily_sum_evt',concat('Daily update for ',now()));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 8
mysql>     call fin_daily_sum_upd(date_sub(now(),interval 1 day));

Query OK, 0 rows affected, 1 warning (4.18 sec)

Is it not permitted to use compund statements (begin ... end;)? Or it is something obvious that my tired, old eyes just can't see?

This is the statement, for clarity:

drop event if exists fin_daily_sum_evt;
create event fin_daily_sum_evt
on schedule
  every 1 day
  starts current_date + interval 1 day + interval 1 hour
comment 'Updates TABLE fin_daily_sum every day at 01:00'
do
  begin
    call logmsg('fin_daily_sum_evt',concat('Daily update for ',now()));
    call fin_daily_sum_upd(date_sub(now(),interval 1 day));
  end;


texts: Automatic Reboot after installing SQL Server 2012?

texts: I'm being paranoid here I'm sure. I'm at the end of an SQL Server 2012 install and this message appears.  I know it says that I must restart the computer but I've had instances where installer automatically restarted the server when I didn't want it to. I cannot reboot this production server right now.  Am I safe to click OK to complete the installation?


texts:
I have a question regarding PostgreSQL DB performance.  I am given a CSV file with a lot of rows (let's say around 1 million rows) that represents updates to a database table (let's call it MY_TABLE).  I would like to copy the data into MY_TABLE from the CSV file.  It is presumed that all the rows in the CSV are updates to the data in MY_TABLE and are not new inserts.  I have 2 options for doing this, and I would like to know which one I should expect to be more performant and why (if possible):

Option 1:

1) Create a secondary table, let's say TEMP_TABLE.
2) Upload all the data from the CSV into TEMP_TABLE.
3) Natural join (on PK) MY_TABLE x TEMP_TABLE.
4) Select rows from MY_TABLE x TEMP_TABLE into MY_TABLE where the PK from the join matches the PK in MY_TABLE.

Option 2:

1) Create a SQL script on client-side containing N UPDATE operations of the form:

UPDATE MY_TABLE SET VALUES (data-from-csv) WHERE MY_TABLE.PK = (PK-data-from-csv)

where N is the number of rows in the CSV.

2) Upload that script to the database and execute it.

Which of these options is more performant and why?  Thanks.


texts: How to change SQL Server master key password?

texts:
I created a master key, then a certificate and encrypted my database.

CREATE MASTER KEY ENCRYPTION
BY PASSWORD='A Very Strong Password';
GO

CREATE CERTIFICATE foo
WITH
SUBJECT='Database Encryption for Multi';
GO

USE mydatabase
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE foo;
GO

ALTER DATABASE mydatabase SET ENCRYPTION ON;
GO

How do I change the MASTER KEY password?

Thanks


texts: I need to know if a SQL Server 2000 database has been changed. The changes are not important, just to know if any change has been made. Maybe SQL Server keeps a timestamp or an incremental field that provide that information for internal uses. Has any one idea about it? Thanks...


texts: SQL Server database corruption in multi-user mode but not single-user mode

texts:
Using Microsoft SQL Server 2012 SP2 Standard Edition on a 2 node SQL Cluster.

When I run dbcc checkdb on a certain database when it is in multi-user mode, it finds corruption in the clustered index of a table.

I put the database in single-user mode and run checkdb with the repair_allow_data_loss option to correct the issue.  In single-user mode, it finds no problems and so does no repair.

Put it back in multi-user mode, run checkdb again, no errors are found.

Other databases on the server have no issues.

Nothing in either the Windows Event logs or the SQL logs indicate an issue (except for the dbcc logging in the SQL log file).

Anyone ever see this behavior before?

Checkdb error:

Msg 8939, Level 16, State 98, Line 1
Table error: Object ID 1163971323, index ID 1, partition ID 72057594205241344, alloc unit ID 72057594209107968 (type In-row data), page (1:127104). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.

Msg 8928, Level 16, State 1, Line 1
Object ID 1163971323, index ID 1, partition ID 72057594205241344, alloc unit ID 72057594209107968 (type In-row data): Page (1:127104) could not be processed.  See other errors for details.

Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1163971323, index ID 1, partition ID 72057594205241344, alloc unit ID 72057594209107968 (type In-row data). Page (1:127104) was not seen in the scan although its parent (1:127199) and previous (1:127103) refer to it. Check any previous errors.

Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1163971323, index ID 1, partition ID 7205759420541344, alloc unit ID 72057594209107968 (type In-row data). Page (1:127105) is missing a reference from previous page (1:127104). Possible chain linkage problem.

CHECKDB found 0 allocation errors and 4 consistency errors in table 'BI_trStoreSalesDetail_Hourly' (object ID 1163971323).

Database is not in production just yet, so data loss is not an issue.

In single-user mode checkdb returns: Command(s) completed successfully.


texts: How to create a SELECT statement involving a subtype

texts:
Okay, so all I'm trying to do here is "list" the managers who supervised the employee that charged a customer $100 for a rental on November 1st, 1998.

My issue is that I don't know how to select the managers' name because they're listed under MANAGER, which is a subtype of the EMPLOYEE supertype. I've posted my EER diagram below:


texts:
I have a SQL Server 2014 up and running and I am trying to backup a specific database to Azure storage. I created a credential using the storage account name as identity and the primary key as password. Then I tried to backup the database to URL but I get the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Backup failed for Server '*****'.  (Microsoft.SqlServer.SmoExtended)

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: A nonrecoverable I/O error occurred on file "https://******.blob.core.windows.net/backupcontainer/demo_backup_2016_06_22_110957.bak:" Backup to URL received an exception from the remote endpoint. Exception Message: The remote server returned an error: (400) Bad Request.. (Microsoft.SqlServer.Smo)


texts:
I know the common recommendation for the data volume in SQL Server is to use 64 KB blocks/stripes, since the I/O is typically done by entire extents. I can't find any good information regarding log file I/O, however.

I've been watching I/O activity in Process Monitor for a little while, and it appears that the log file I/O sizes range from 512 bytes to just under 64 KB. I'm guessing this is dependent on the size of the transaction being logged, and large ones get split up using multiple ~64 KB writes.

So, assuming I have the partition aligned to the RAID stripes, would it be safe to assume that 64 KB blocks/stripes will yield the best performance, all other things being equal? I would expect that the smaller transactions, i.e. the ones with 512-byte writes, aren't coming heavily enough for the large block size penalty to have a significant impact, whereas the much larger transactions writing lots of 64 KB blocks in rapid succession would be more important to tune for.


texts: What is the best way to filter or otherwise manipulate the results returned by a stored procedure?

texts: Often I find myself wanting to filter or order the results returned by execing a stored procedure - for example to order the output of sp_who2 by login or CPU Time, or filter it by host name. What is the best way to achieve this?


texts: ERROR: invalid input syntax for type oid: "english"

texts:
While creating an index in Postgres I am getting an error:

invalid input syntax for type oid: "english"
LINE 1: ...s ON entity_attributes2 USING gist ( (to_tsvector('english',...

The query which I have used to create the index:
CREATE INDEX json_fts ON table USING gist ( (to_tsvector('english', abc->>'email_address')));

The abc column is of jsonb type and it contains the following data:
{
    "account": "10",
    "name": "Scott Theisman",
    "manageremail": "eric.larsen@xxx.com",
    "active": "t",
    "email_address": "scott.theisman@xxx.com",
    "employeeid": "74322509"
}


texts: NoSql - Dynamo db - when do you need multiple tables?

texts:
I'm experienced in RDBMS, but new to NoSql.  This aws dynamodb design advice says that "most well designed applications require only one table".  I'm trying to wrap my head around that.  I don't see a useful single-table solution for anything but a completely trivial application.

Please tell me that they are wrong or that they are right.  If you agree that they are right, I'll give an example of requirements and ask how it would be resolved in one table.

Thanks.


texts:
The following Error occurs while updating extended property of a table in SQL  Server 2008 R2.
What is the reason for this error and how to find who is causing lock?
Error:
===================================

Alter failed for Table 'dbo.Ticket'. (Microsoft.SqlServer.Smo)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000.0+((KJ_PCU_Main).120628-0827+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Table&LinkId=20476

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()
at Microsoft.SqlServer.Management.Smo.Table.Alter()
at Microsoft.SqlServer.Management.SqlManagerUI.ExtendedProperties.OnRunNow(Object sender)
at Microsoft.SqlServer.Management.SqlMgmt.PanelExecutionHandler.Run(RunType runType, Object sender)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplFinish(StringCollection alterQuery, ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplWorker()
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()

===================================

Lock request time out period exceeded. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.6000&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476

------------------------------
Server Name: PC\SQLEXPRESS,25111
Error Number: 1222
Severity: 16
State: 56
Procedure: sp_updateextendedproperty
Line Number: 36

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)?


texts:
Aurora PostgreSQL: How the process work on failover,I have a primary DB-prod for transaction database and read replica for reporting.
where reporting will be pointed if primary fail.

similar question on, "If I have a primary database and an Amazon Aurora Replica actively taking read traffic and a failover occurs, what happens?"  https://aws.amazon.com/rds/aurora/faqs/


texts: Bottleneck when inserting many rows into different tables

texts:
TLDR: I want to bulk insert into many initially empty tables at once, without the performance of each insert being affected too badly. I seem to be hitting a performance bottleneck I can't yet explain. I am looking for performance debugging avenues or advice on parallel inserts in Postgres.

I am prototyping a system and trying to import data into many Postgres tables at once as fast as possible. My example table has 100 columns (all short strings) and 20k rows. I can insert this data into a fresh Postgres table in around 30 seconds (*). However, if I run the same insert script 10 times against different (unrelated) tables, the performance of each individual insert drops dramatically - up to around 3.5 minutes for each table. The total insert (3 tables per process, 10 processes) takes around 8 minutes, so I am getting less than 2x speedup by running these queries in parallel vs. running them all one after the other for 30s each. I was hoping to get ~10x speedup with 10 parallel unrelated imports, until I reach some resource limit of the database host.
I am using an SSD-based Google Cloud SQL database with Postgres 12, 20vcpus and 75GB memory. While handling my 10 parallel fills, it uses less than 15% CPU, around 200 write ops per second (I am not clear what counts as one op in GCP), and around 10-15MiB/s data ingress. None of these feel like they should be a bottlneck for a relatively beefy host instance, but I can't understand what is slowing down these queries.
What should I check to investigate this bottleneck, and what should I be doing to import many tables in parallel efficiently?
My ideal solution would be to use 10x as much CPU (or whatever other resource) on the host instance, and complete the 10 parallel table loads in roughly the same time as a single one. I am hoping to be able to reach around 200 imports in parallel, even if it has to be spread over a few separate database hosts (non-ideally).
It seems clear that it is not (yet) CPU bound; att the tables at once can fit into memory a few times over, so it is not memory bound. I would be surprised to find it is IO or disk bound at this scale, but I don't know how to check or investigate these.
All the information I have been able to find has been about improving the speed of a single-table insert. Some examples:

Populating a Database
How to speed up insertion performance in PostgreSQL
What's the fastest way to do a bulk insert into Postgres?

I haven't found anything addressing concurrent inserts into separate tables or databases on the same host.
I also tried having my 10 client processes import data into differewnt databases to each other, with almost exactly the same performance.
I tried switching my tables to be UNLOGGED. This made a big improvement to the speed when running 10 imports in parallel (down to around 45 seconds), but running 20 imports immediately jumped back up to 2-4 minutes per table.

(*) Currently for each single-table load I am using a single insert statement with all the rows at once:
insert into test_table values ('id1', 'Sample data 1', ...), ... ('idN', 'Sample data N', ...);

I have also tried bulk loading from a CSV file via copy, inserting in batches of 100 or 1000 records at a time, and increasing the power of the host instance, none of which makes any significant difference on my example. The tables have an externally-specified primary key and no other constraints. But improving the performance of a single-table import is not what this question is about!


texts:
I'm trying to backup to a NAS box but I'm getting the following error:

Cannot open backup device '\\nas02\backup\SQL\instance\Full-Backups\OtherDBs\Daily\MGMT_TEST.bak'. Operating system error 1326(failed to retrieve text for this error. Reason: 15105).
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.

This was working before and I have checked permissions to the location by logging onto the server as the account sql is running under and I can go to the location above. From there i can create new files and update existing ones etc.

There is no space limitation
The user running the statement is a sysAdmin

I'm thinking some permissions of some type but struggling to see how.


texts: postgresql update with a subquery limit 1 that has joins sometimes doesn't respect the limit?

texts:
As per Postgres UPDATE ... LIMIT 1 I'm trying to update a single record but based on a join.  However, ~30% of the time on the pre-installed postgresql 9.5.5 from travis-ci the following SQL actually returns multiple records.  Is there something I'm misunderstanding about this query like it's running one instance of the subquery for each tuple the update sees or is this a potential bug in postgresql?

UPDATE "users" SET touched_at = now()
FROM (
   SELECT "some_table".*
   FROM "some_table"
     INNER JOIN "users" ON "users"."id" = "some_table"."user_id"
   WHERE "some_table"."handled_at" IS NULL
     AND ("users".touched_at IS NULL OR "users".touched_at < '2016-12-21')
  ORDER BY "some_table"."created_at" ASC
  LIMIT 1
  FOR UPDATE OF "users"
) dt
WHERE "users".id = dt.user_id
AND ("users".touched_at IS NULL OR "users".touched_at < '2016-12-21')
RETURNING dt.*


texts: Distinguishing data from different sources

texts:
I want to store data that comes from multiple data sources, the issue is that the row identifiers have no guarantee to be unique across sources, which wouldn't satisfy the unique key constraint.

I'm not really familiar with databases, and if I had to model this in an imperative programming language I would have probably just stored data from each source apart in different arrays, this (storing in different tables) doesn't look at all like a correct approach for databases, though.

What is the usual approach for storing data that shares the exact same column types, but that we want to be separated, for example because the IDs might conflict? Should I instead put the original IDs in a different column, add another column to specify the "row origin" (ie: what source does it come from) and yet another column for the original ID that it would have had?

Thank you


texts: Postgres Index a query with MAX and groupBy

texts:
Is there any way to index the following query?

SELECT run_id, MAX ( frame ) , MAX ( time ) FROM run.frames_stat GROUP BY run_id;

I've tried creating sorted (non-composite) indexes on frame and time, and an index on run_id, but the query planner doesn't use them.

Misc info:

Unfortunately (and for reasons I won't get into) I cannot change the query
The frames_stat table has 42 million rows
The table is unchanging (no further inserts/deletes will ever take place)
The query was always slow, it's just gotten slower because this dataset is larger than in the past.
There are no indexes on the table
We are using Postgres 9.4
The db's "work_mem" size is 128MB (if that's relevant).
Hardware: 130GB Ram, 10 core Xeon

Schema:

CREATE TABLE run.frame_stat (
  id bigint NOT NULL,
  run_id bigint NOT NULL,
  frame bigint NOT NULL,
  heap_size bigint NOT NULL,
  "time" timestamp without time zone NOT NULL,
  CONSTRAINT frame_stat_pkey PRIMARY KEY (id)
)

Explain analyze:

HashAggregate  (cost=1086240.000..1086242.800 rows=280 width=24) (actual time=14182.426..14182.545 rows=280 loops=1)
  Group Key: run_id
  ->  Seq Scan on zulu  (cost=0.000..770880.000 rows=42048000 width=24) (actual time=0.037..4077.182 rows=42048000 loops=1)


texts:
I upgraded from Oracle 10g to Oracle 11g in Windows Server 2003.
New Oracle home : C:\app\oracle\product\11.2.0\db_1
Old Oracle 10g was installed on: C:\oracle\product\10.2.0\
Questions

Is the 10g folder is still useful?
Could I delete the 10g folder without a problem?


texts: Run SQL Server agent job in a particular resource governor pool

texts:
I read Considerations for Writing a Classifier Function on MSDN. The only way I see possible is to create a separate user account for each resource governor pool and to classify sessions based on that username.

Is there a better way?


texts: Unexpected result from PostgreSQL information schema

texts:
I have written a simple query that should show all table sizes of any given schema in human-readable format:
select table_name, pg_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'my_schema'
order by 2

When I run this query in PgAdmin, I get the following error:
ERROR:  relation "my_table" does not exist
SQL state: 42P01

How can this error even be possible?  I have not changed the information_schema at all and, if a relation doesn't exist in the first place, why would it be in the information_schema ?   Any idea how this could happen?


texts: Does Index Rebuild Reduces physical read from disk

texts: Recently I have rebuild indexes uisng Ola Hallengreen script which were 99% fragmented. After rebuilt i have noticed the physical reads have reduced a lot. Does this has anything to do with Index Rebuilt ?


texts: What is an advantage or disadvantage for not upgrading database server?

texts:
I have a legacy database server. The person who wrote it is long gone. There is no documentation at all just some comments in the code. Luckily, it is not that complicate but if I want to fix it (requests from users), it takes time to investigate all related database objects (tables, views, or stored procedures).

Recently, I received suggestions from our IT departments person that I should upgrade our database to a newer database version (2005/2008). Right now, it is SQL Server 2000. He said it will have better performance and security. I think if I upgrade to newer version, our old box (hardware) may not powerful enough for new database engine. Currently, according to users, the response time is acceptable.

I try to think all pro(s) and con(s). I prefer for not upgrade but I want to make sure that I don't miss anything.

Not upgrade

Better ROI (not pay for everything)

Upgrade

Pay for new server/license
Redo some works to get the same functionality
Better performance/security

Question is: What should I do? Upgrade or Not upgrade our database server.


texts: Oracle Triggers - before any UPDATE or DELETE or INSERT

texts:
I am trying to create a database trigger in Oracle SQL Developer that would fire before any UPDATE, DELETE, or INSERT statement occurs on the sessions table.

My sessions table:

CREATE TABLE sessions (
SessionNo NUMBER (2,0),
SessionTime VARCHAR2 (5),
SessionDay VARCHAR2 (20),
SessionDate DATE,
SpecID VARCHAR2 (2),
RoomNo CHAR (3),
BuildingNo CHAR (2),
FormID CHAR (4));

My example insert statement:

INSERT INTO sessions VALUES (1, '9', 'Monday', TO_DATE ('02/06/2016', 'mm/dd/yyyy'),
                        12, 108, 'M', 0050);

This trigger is supposed to check the date and time attributes, and if the day is a Saturday or a Sunday, or if the time is outside the school business hours (9am to 3pm), a message:

  Out of business hours – this transaction has not completed, please change the
  date/time

...should be displayed.


texts: I got a 2 core Mysql Server with 25 GB of RAM, 1 TB of Hard drive running on windows server. What is the proper configuration?

texts:
I am relatively new in running enterprise db server and as a developer, I am used to running my DB in the default setting on more focused on the code. Now, I saw that the DB is not properly configured in an enterprise level with at least 2,000 user and generating > 1000 tickets per day.

How should I configure my DB server? I read this article https://medium.com/@richb_/tuning-mysql-3-simple-tweaks-6356768f9b90 and somehow I got lost on the process of getting the innodb_buffer_pool_instances and more.

Below is my current configuration:

https://pastebin.com/1TaKpRaB -> my.cnf

https://pastebin.com/pWF6SV6W -> show variables;

https://pastebin.com/CnwiWmx6 -> show global status;


texts: How to manipulate bytea data when using backup script as a template

texts:
I have a project with a web app that has many configurable options, with the configurations stored in a PostgreSQL database. Most fields are text or ints, but there are a couple configurations stored as JSON decoded into bytea.

We have one "default configuration" setup script that my team (Software Quality Assurance) utilizes to put everyone's testing server on the same page. The script takes a few hours to manually create data and store configurations via the web app. I'd like to save our team hours of setup time by simply sharing a SQL file with the create / insert statements necessary to get the database to the same configuration in way less time.

So with a script such as:

CREATE DATABASE example;

...

CREATE TABLE example_table (
    id integer NOT NULL,
    ip_address text,
    domain text,
    configuration bytea
);

...

COPY example_table (id, ip_address, domain, configuration) FROM stdin;
1   0.0.0.0 invalid.domain.com  \x7b277265666572656e63655f746f5f69705f61646472657373273a27302e302e302e30272c277265666572656e63655f746f5f646f6d61696e273a27696e76616c69642e646f6d61696e2e636f6d277d
\.

--- The above bytearray is "{'reference_to_ip_address':'0.0.0.0','reference_to_domain':'invalid.domain.com'}"

Originally, my thought was to replace "0.0.0.0" and "invalid.domain.com" with whatever the user wanted, and then the user would execute the script to setup their database with the common configuration. However, with the encoded data, the end result would have invalid references to the server's IP address and domain. Has anyone ever had a problem like this and figured out a solution? Is there a better way of doing this? I'm open to any suggestions other than changing the database organization (I can't swing that one).


texts: SQL Select records where any word / value contained within one string is also contained within another string

texts:
I need to find a SQL select query that will allow me to find records in [KnowledgeArticle] Where any word contained in the string [KnowledgeArticle].[Keywords] is contained within [Incident].[Description].

I'm working with an IT Service Management application, and want to provide a list of Knowledge articles that match an Incident record's description, automatically. I don't have direct access to change the way this ITSM application queries the database, it's all managed via a series of GUI's.

However, I can connect the ITSM app to views that I create in the database, and filter results of the view from there.

So, my challenge is that I need to get, within a view, a list of all [KnowledgeArticle] records, duplicated once for each [Incident] record, where any word within [Incident].[Description] is contained within [KnowledgeArticle].[Keywords].

This query on it's own would be quite ridiculous, and would present a very large subset of records, however once I have the query built and set up as a view, I'll be able to configure the ITSM application to filter the results of this query / view to only the current [Incident] record, providing me with the data that I need (Knowledge articles where any keyword in the knowledge article is present on the Incident).

Hopefully this makes sense, I'm not sure what kind of operator I might be able to use in a comparison clause to bring up that list of knowledge article records.

(If you're not familiar with IT Service Management jargon, the words Incident and KnowledgeArticle are just table names, what they do is less important to the technical question being asked).


texts: MySQL replication - independent data update on slave

texts:
I have an unusual situation where I am migrating websites and their corresponding databases from one server to another.

I have a database that is hosted on one server, A, where new records are created, and these are accessed and modified on the second server B (but no new records are created).

I have set up A as master, and B as slave to ensure that B has access to new records created by A, but A does not need access to the amended record data that are changed on B.

I know there are lots of warnings about not changing data on the slave, but it seems to me that this should work OK (until I finish the migration, at which point I will move the record creation facilities to server B and break the slave link).

Any thoughts?


texts: What is the advantage of setting a character column as VARCHAR(32) over VARCHAR(255)

texts: What is the advantage of setting a database column VARCHAR(32) over VARCHAR(255) even though the two of them consume 2-byte memory?


texts: MySQL - using result of Procedure in Trigger to Insert in new table

texts:
I'm guessing I'm doing something that's prohibited by MySQL, but can't see what.

Basically, I have a set of tables that hold lists of things (item name, item types, item groups etc.), and I need to create a joining table to hold each of the ID's (e.g. item_id, item_type_id, item_group_id). At present there is no real relationship between the various lists. This table needs to be automatically populated each time I add a new item.

To get the matching entry, I need to use a CASE statement to match the first letters of the item_name, and get the relevant entry from the other table(s).

My plan is to have a set of Procedures (one for each table I need to join):

DELIMITER $$
CREATE PROCEDURE pr_get_item_type_id( item_name varchar(45) )
BEGIN
    SET @itemTypeID := (SELECT
            CASE
                WHEN LEFT(`item_name`,6)  = 'abcdef' THEN (SELECT t.`id` FROM `item_type` t WHERE t.`item_type_name` = 'type1')
            #repeats multiple times . . .
            ELSE 0
            END AS `item_type_id`);
    SELECT @itemTypeID;
END;
$$
DELIMITER ;

This works fine.

I then have a trigger:

DELIMITER $$
CREATE TRIGGER tr_item_details
    AFTER INSERT
    ON `item` FOR EACH ROW
    BEGIN
        SET @itemId := new.`id`;
        CALL pr_get_item_type_id(new.`item_name`); #-->this Sets var @itemTypeID

        INSERT INTO `item_details` (`item_id`,`item_type_id`) VALUES (@itemID,@itemTypeID);
    END;
$$
DELIMITER ;

I then test this as follows:

INSERT INTO item (id,item_name) VALUES (1000,'abcdef')

But this results in:

Error Code: 1415. Not allowed to return a result set from a trigger

I tried setting the variable inside the trigger instead of the procedure:

SET @itemTypeID = (CALL pr_get_item_type_id(new.`item_name`));

But that didn't work either.

Is there a way to do this?

Example Tables:

CREATE TABLE `item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `item_name` varchar(45) NOT NULL DEFAULT 'UNKNOWN', PRIMARY KEY (`id`));
CREATE TABLE `item_type` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `item_type_name` varchar(45) NOT NULL DEFAULT 'UNKNOWN', PRIMARY KEY (`id`));
CREATE TABLE `item_details` (`item_id` int(10) unsigned NOT NULL,`item_type_id` int(10) unsigned NOT NULL,`item_group_id` int(10) unsigned NOT NULL,PRIMARY KEY (`item_id`,`item_type_id`,`item_group_id`));
INSERT INTO `item_type` (`item_type_name`) VALUES ('type1'),('type2');


texts:
According to the PostgreSQL documentation for producing Standalone Hot Backups:

These are backups that cannot be used for point-in-time recovery, yet
are typically much faster to backup and restore than pg_dump dumps.

It seems that all that is required to produce these kinds of backups is to use pg_basebackup utility and include the -X option, which essentially includes the required WAL files necessary to start the cluster. Why can't these backups be used for PITR? If I don't start this backup immediately, shouldn't I be able to place it in recovery mode and allow it to restore up to any point after the backup was created?


texts: MySQL table with multiple values in one field

texts:
I'm building a database for a real estate company.

The real estate properties are managed by sales people using a CodeIgniter website.

How should I proceed with the database design for this table?  Fields such as address, location, price etc., are pretty straightforward, however there are some sections like Kitchen Appliances, 'Property Usage' etc., where the sales people can check multiple values for each field.

Furthermore, a property can have multiple people attached to it from the table people, such as owner, seller, property lawyer, etc.  Should I use one field for these people or create another table and normalize the bindings?

Is the best way to proceed just using one field and using serialized data or is there a better way for this?


texts:
I adapted a SQL statement to work with SQLite 3. But unfortunately Common Table Expressions and WITH clause works only in SQLite 3.6 ahead, but the users of my Open Source application are using SQLite 3.2 and I cannot force them to update the whole Linux system to get the new packages. Is it possible to adapt the code to work without using a CTE and  "With" Clause using only SQL Language?

Here's the code:

WITH
cte AS
   (SELECT 0 AS level, collectionID, collectionName, parentCollectionID, CAST(collectionID AS VARCHAR(128)) AS Sort
           FROM collections WHERE parentCollectionID IS NULL
    UNION ALL
    SELECT p.level + 1, c.collectionID, c.collectionName, c.parentCollectionID, CAST(p.Sort || '/' || CAST(c.collectionID AS VARCHAR) AS VARCHAR(128))
          FROM collections c
          INNER JOIN cte p ON p.collectionID = c.parentCollectionID)
SELECT
collectionID,
printf('%*s', level * 4, '') || collectionName AS collectionName,
Sort,
parentCollectionID
FROM cte
ORDER BY Sort;

Here's the result:

collectionID    collectionName            Sort     parentCollectionID
1               Dissertação               1        0
10              Filosofia Reformacional   10       0
11              Dooyeweerd                11       0
14              ZotPad favorites          14       0
15              Diversos                  15       0
2               Bíblia                    2        0
3               Políticas Públicas        3        0
4               Zotero                    4        0
5               Linux                     5        0
6               Tese Doutorado            6        0
12                  Pontal Do Paraná      6/12     6
7                   Multimodal            6/7      6
13                     Modalidades        6/7/13   7
8                      Base Histórica     6/7/8    7
9              Artigo Weber               9        0

Thank you so much,
Best regards,
Christian


texts: Why does pg_last_xact_replay_timestamp() sometimes return extremely old timestamps?

texts:
In PostgreSQL 9.5, to monitor streaming replication lag, I use a combination of:

pg_current_xlog_location()
pg_last_xlog_replay_location()
pg_last_xact_replay_timestamp()

The first two functions are used to determine if there is any lag between the master and standby. If there is some lag, then I determine it with:

select now() - pg_last_xact_replay_timestamp();

Mostly it returns reasonable amounts -- seconds to minutes of lag.
However occasionally, that statement will return unlikely, large values. Several days of lag.

We don't have any transactions in our system that run for that long, not even close.

Can anyone suggest what is going on here?


texts:
This question only needs one answer: a list of great resources for getting started with Oracle, including Database Administration, PL/SQL and SQL.

You may find your question has been closed as a duplicate of this one, if so, please don't be offended: we are trying to be as helpful as we can without diluting the expert focus of this site. Closing a question as a duplicate of this one is us saying "What you really need is to spend some time reading some of the best resources available rather than have your specific question answered right now. If you do get through these resources, you'll soon be back with more advanced questions!


texts: Influx + Grafana: Subtract values from two queries

texts:
I'm using iobroker to collect data from my smart meter and photovoltaic system and write it to influxdb.
Now I'm struggeling with displaying some of the data in grafana.
I have the following two queries:
Query1:
Select Last(value) from photovoltaic WHERE $timeFilter

Query2:
Select Max(value)-Min(value) from smartmeter WHERE $timeFilter

How can I subtract the result from Query2 and Query1 and display it?
versions:
influxdb/stable,now 1.6.4-1+b22
grafana/stable,now 7.3.5
Thanks for your help


texts: Does a table with an index ever slow down?

texts:
We've got a single table where we store Events.
The table has an index on it's primary key

Assuming we're only doing simple SELECTs or INSERTs on that table - would the retrieval or insertion time get noticeably* slower as more and more rows are added?

My (very crude) understanding is that an index makes the lookup time non-linear (non-O(N)), therefore, at least theroreticaly, it won't be a problem - but I'd like to confirm this.

We're working with PostgreSQL 9.5.3 and at some point we expect this table to have billions of rows.

*noticeable: It takes < 1 second to retrieve 5000 events right now, noticeable would be > 2 seconds


texts:
Is is possible to return the first (or last) row from a NTILE partition in SQL SERVER?

For example, on this fiddle I wish I could have the last member from each group without the comma.

SQL Fiddle:
http://sqlfiddle.com/#!18/15223/4


texts: Execute SSIS procedure from stored procedure using non-privileged SQL login

texts:
I have a web app running on a different server that uses a SQL based login (not a windows based login) to connect to the SQL server. That app is calling a stored procedure that is located in a database other than SSISDB and the stored procedure is attempting to call the following SSIS procedures:

[SSISDB].[catalog].[set_environment_variable_value]
[SSISDB].[catalog].[create_execution]
[SSISDB].[catalog].[set_execution_parameter_value]
[SSISDB].[catalog].[start_execution] @execution_id

When the attempt is made we get the following error:

  The operation cannot be started by an account that uses SQL Server
  Authentication. Start the operation with an account that uses
  Integrated Authentication.

My challenge is how to work around this problem within the confines I have:

I cannot change the code on the web app or the authentication method.
Only minimal, non-impactful changes can be made to the stored procedures such as using "Execute As"
I cannot make changes that require additional code changes - such as creating a wrapper procedure or job.

I came across this thread which describes the problem perfectly but ends in "I'll try something else." The thread also leads to this thread which has great information but does not address starting the procedure as a non-windows user.

I've been working with impersonation attempts, and various combinations of execute as and have been unsuccessful. All of the information I find gives me clues to what I need to do, but none of them directly address this exact scenario and I am hitting a wall.

Can someone please describe how to work around this specific scenario? I don't need exact step by step details (although that would be great), but I need information on specifically how to get from point A (The web app calling a stored procedure with an SQL account) to point B (A SSIS package being executed).

I feel the solution is to make the stored procedure impersonate a windows login that can run the SSIS procedures. But how to do that properly?

Other suggestions that can be used in the interim are also appreciated, but they have to fit in with my confines listed above.

Other resources I have used are here:
https://blogs.msdn.microsoft.com/sqlserverfaq/2009/07/24/how-to-impersonate-the-privileges-to-create-a-login-using-the-stored-procedures-using-execute-as-clause/

https://www.sqlservercentral.com/articles/using-certificates-to-sign-stored-procedures

http://rusanu.com/2006/03/07/call-a-procedure-in-another-database-from-an-activated-procedure/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/79391dd7-9afb-4bfb-aa90-1fc1cc82b305/impersonation-on-server-level-doesnt-work?forum=sqlsecurity

http://blog.sqlxdetails.com/procedure-with-execute-as-login/


texts:
Suppose that i select into a temptable as follow by performing grouping from an existing table, and this is current temptable value:

Title Count
-------------
Sr.   11
Mrs.  2
Sra.  3
Ms.   415
Mr.   578

What I want to achieve is a comma separated value for title as follow, which is order by count

[Mrs.], [Sra.], [Sr.], [Ms.], [Mr.]

However, currently i can only able to achieve a comma separated value as follow, which is the default ordering in #temptable

[Sr.], [Mrs.], [Sra.], [Ms.], [Mr.]

Below is my code. How could i achieve what i wanted?
Note: I don't assume nor expect the sequence is already in order when inserting into the #temptable

DECLARE @Columns as VARCHAR(MAX)
SELECT @Columns =
COALESCE(@Columns + ', ','') + QUOTENAME(Title)
FROM
   (
    SELECT Title FROM #tempTable1
   ) AS P


texts:
Running backups on a Mongo Replica set - 1 primary, 1 secondary, 1 arbitrar.  Had a instance where all three stopped talking to one another and all went to secondary.

I ran a backup against the the Replica set with Mongodump.  Would that cause the DB's to switch?  Is there harm in running mongodump against a running production system on the Primary?


texts: How to add sequential numbers to an existing table?

texts:
I'm seeking your support as I'm new in using Toad.
I need to insert sequence numbers based on columns subno and AR_INVDATE.
So if the same subno has more than one AR_INVDATE then I need a sequential number based on sorting the AR_INVDATE in ascending order.


texts: EAV, multiple left join query optimisation

texts:
Any assitance would be greatly welcomed. Pardon my ignorance however I am wondering if anyone else has had to encounter bad EAV performance issues. I have a particular query which takes a long time. What is the best way to increase the speed on this?

 SELECT * FROM
(
    SELECT f.*, p.`timestamp`
    FROM `tObjectValues` ov
    INNER JOIN `tObjectProperties` op ON ov.`propertyId` = op.`id`
    INNER JOIN `tObjects` o ON op.`objectId` = o.`id`
    INNER JOIN `files` f ON SUBSTRING(ov.`value`, CHAR_LENGTH('cache://content//')) = f.`filename`
    LEFT JOIN `publishFiles` pf ON f.`id` = pf.`fileId`
    LEFT JOIN `publishes` p ON p.`id` = pf.`publishId` AND o.`appId` = p.`appId` AND p.`type` = 'l'
    WHERE o.`status` = 1 AND op.`status` = 1 AND ov.`status` = 1 AND ov.`value` LIKE 'cache://content/%' AND o.`appId` = 2
    ORDER BY p.`timestamp` DESC
    LIMIT 0, 10000000
) s
GROUP BY s.`id`

I have run the explain function to delve further into the issue however i was wondering if anyone who has had similar problem could help in the optimisation.


texts: Can I run an index rebuild on a sql server HA replica or a copy the DB, and then swap with the primary?

texts:
We have a large database hosted in sql azure and we need to occasionally run maintenance tasks such as index rebuild.
Pretty common of course, and we also try to minimize downtime.

We use the premium sql azure database, which comes with replicas.
It got me thinking whether it would be possible to run expensive maintenance tasks on a replica and then somehow switch this replica to become the primary.
Things like index rebuild, but also potentially adding indexes and altering tables.

I have a feeling this is not going to work because the replicas must be identical to the primary.
But, if I understand correctly, whenever I change my azure plan, behind the scene azure spins up a database, copies the database over and then switches the 2 database once the new server has completely caught up on all transactions from the original server.
Is it possible to use a similar technique to run tasks on the new server before it switches with the original server?

I should say that we are fine with the db being down for writes for a while, but we want minimal downtime for reads.


texts:
Is there any way to give conditional alias to SQL columns?.

Because I have two tables for Fields and Category. Meaning of each field changes conditionally.

 DECLARE @f1 NVARCHAR(10);
 set @f1= (select Field1 from Category);
 select Field1 as @f1 from AdditionalFields


texts: How to export oracle 11g database partially

texts: I have a huge oracle test database whose size is more than 100 GB. I want to export only 1% of all the data and import it into my local database to have a local replica of that application. Is it possible in oracle 11g?


texts: Is there a way to retrieve the logical filename from a backup file?

texts:
I would like to write an automation script that restores a SQL Server DB from its backup file. However, doing this is not a straightforward procedure in SQL because the main query needs additional inputs which can actually be obtained using another query. Can I do this in one query?

There is a question already on SO; but the solution is not very flexible. RESTORE FILELISTONLY's definition changes very frequently. Even otherwise, the solution seems very verbose.

Isn't there a simpler way to store the results of a query into a variable and use them? This is a cakewalk in any programming language.

Get the Logical Name:

RESTORE FILELISTONLY
FROM DISK = 'D:SourceBackUpFile.bak'
GO

Restore the DB:

RESTORE DATABASE DBName
FROM DISK = 'D:SourceBackUpFile.bak'
WITH RECOVERY
MOVE 'SourceMDFLogicalName' TO 'D:TargetMDFFile.mdf',
MOVE 'SourceLDFLogicalName' TO 'D:TargetLDFFile.ldf'


texts: What can I do about this MariaDB 10.0 error?

texts:
I have a serious error that crashes the mariadb service, and it won't start up again until I delete the database folder in the data directory of the database I was working on at the time. Here is the .err file printout. Anyone know what to do about this?

2015-02-21 13:48:23 7dc InnoDB: FTS Optimize Removing table bolingerfield/tag
2015-02-21 13:49:44 4bc4  InnoDB: Error: space id and page n:o stored in the page
InnoDB: read in are 13924:3, should be 13877:3!
2015-02-21 13:49:44 4bc4  InnoDB: Error: space id and page n:o stored in the page
InnoDB: read in are 13924:1, should be 13877:1!
2015-02-21 13:50:23 798  InnoDB: Assertion failure in thread 1944 in file btr0btr.cc line 713
InnoDB: Failing assertion: mach_read_from_4(seg_header + FSEG_HDR_SPACE) == space
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.


texts: Can I create a random number in an SSIS derived column

texts:
I have a task where I am trying to assign a random PIN to an account. I have SQL code that will generate a random integer between 1000 and 9999. I was wondering if I could put that in a Derived Column task in SSIS and not do it in the source data?

Also, I can probably write a Script Task to accomplish this. At this point it's more an exercise to see if it's possible since I have alternatives.


texts: Determining the tables and impact of a large number of SPs

texts:
I'm trying to track down what is causing some response times to increase when data is compressed by page compression, when the system is not CPU bound and 95% of responses see a decrease in response time.

So what I did is track it down to a single process happening in the system, and I profiled that process to determine what SPs and UFNs are fired off during the process. My original thought was that I could run each SP and UFN in isolation and take a look at the query plans to see where full scans are happening, this might require the data to be unpacked from the compression and might cause a wait to fire.

So what I have right now is:

A list of SPs with the parameters used
The database that the SPs are run on
Unfettered administrator access to the system to reproduce the issue
A profile trace of the process in question

Since there's something like 35 SPs/UFNs I'd have to sort through, I'm wondering what the most efficient way is to narrow down the cause. I can infer that some SPs are more likely culprits than others from my experience with the system, but I'd like to try to narrow it down in a more scientific way. Are there any tools or methodologies that might help me figure out the most likely offenders?

If I can determine the objects that are slower when compressed than when not, this is going to help inform our strategy around page compression.


texts: Mysql 5.5: Optimizing all tables with mysqlcheck - is it useful to run the -check option along with -optimize?

texts:
I plan on running 'mysqlcheck --optimize --all-databases' to clear up some fragmentation.

Would it be useful to run -check with this option or does the -optimize already take care of that?
I am only concerned because we have large tables (over 100GB) and this is on a production system and we don't want to have unnecessary downtime.

tom.


texts: How to enforce that a parent must have at least one child when the PK is composite?

texts:
Consider the following business domain:

An Airline has a unique airline id (aid) and contains zero-or-more Planes.
A Plane has a unique plane id (pid) in the Airline it flies for (but planes from different Airlines can have overlapping ids).
Each Plane has one-or-more Seats.
A Seat has a unique seat id (sid) in its plane (but Seats from different Planes may have overlapping ids).

My attempt so far
Here is my attempt at solving this:
CREATE SEQUENCE planes_seq;

CREATE TABLE Airlines (
    aid INTEGER PRIMARY KEY DEFAULT nextval('planes_seq')
);

CREATE TABLE Planes (
    aid INTEGER REFERENCES Airlines(aid)
,   pid INTEGER
,   PRIMARY KEY(aid, pid)
);

CREATE TABLE Seats (
    aid INTEGER
,   pid INTEGER
,   sid INTEGER
,   PRIMARY KEY(aid, pid, sid)
,   FOREIGN KEY(aid, pid) REFERENCES Planes(aid, pid)
);

ALTER TABLE Planes ADD CONSTRAINT fk_seats FOREIGN KEY(aid, pid)
REFERENCES Seats(aid, pid);

However, it fails because the final ALTER TABLE is illegal since the pair (Seats.aid, Seats.pid) is indeed not unique.
Questions

How could I enforce the "a plane has at least one seat" constraint?
Is this scheme in 3rd Normal Form?

PS: I am new to SQL and this is a (small part) of a homework assignment. I have tried following the parent-with-at-least-one-child example from another DBA question (Constraint to enforce "at least one" or "exactly one" in a database) but there are way too many tricks there that I cannot understand (multiple WITH queries, a RETURNING clause and so on). It looks to me like there must be a simpler way to do it.


texts: Isn't contradictory that MongoDB may lose updates with journaling in WiredTiger?

texts:
This is what MongoDB says about journaling:

  To provide durability in the event of a failure, MongoDB uses write
  ahead logging to on-disk journal files.

But with WiredTiger:

  Important

  In between write operations, while the journal records remain in the
  WiredTiger buffers, updates can be lost following a hard shutdown of
  mongod.

If journaling in WiredTiger can't fully guarantee that there will not be lost updates we have to write our application to be aware that some documents may just "disappear", so why enable journaling at all, considering that it will slow down writes?

It seems that if you can't lose updates* you have to choose MMAPv1 with journaling and if you can you may prefer to choose WiredTiger without journaling.

(*) You still may lose updates in a MongoDB cluster due to rollbacks altought you can avoid rollbacks with w: majority write concern or do something with the files in the rollback/ folder.


texts: Is it possible that information_schema.columns doesn't hold an user table column information?

texts:
I am facing a strange issue here. I have a table which is not visible in information_schema.columns view. What could be the reason for that.

Note: I am using RDS Aurora Postgresql 9.6.12


texts: Oracle 12cR2 - How To Create DDL Script Of A View With All Dependencies and Permissions

texts:
I have a view in my production database and I want to create same view in my test database.
I tried this commmand:
select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual;

It generated a create script and I run it. But it didn't copy all of the dependencies, data types, etc.
How can I do this?
Regards,


texts: Mysql query to find nth largest entry in a column

texts:
I searched on internet to find the nth largest entry in a particular column for Mysql. I found the following query:

select salary from employee e1
where (n-1) = (select count(distinct(salary))
from employee e2 where e2.salary > e1.salary);

Here employee is my table name and I want to select nth highest salary from it. The query is running fine. But I don't understand how it's actually working.

Can anybody explain it?


texts: Using a many-to-many table with a one-to-many relation?

texts:
I'm building a platform where Suppliers can add products, and shop owners can 'copy' those products to their shops and sell them.

A Supplier can create products, and he can add all sorts of attributes to them, like the size, colors.

When a Shop wants to use one of the Supplier's products, he can choose which attributes he wants to offer. For example if the Supplier has a T-Shirt in offer with 3 sizes. The shop may choose to sell that T-Shirt with only 2 sizes.

So I have a Products table which looks like this. The Supplier adds products in here.

+----+-------------+-------+
| id | description | price |
+----+-------------+-------+
|  1 | T-Shirt     |    10 |
|  2 | Car         |   100 |
+----+-------------+-------+

I have a Product attributes table, Which is also filled by the Supplier

+------------+-------------+------------------------------+
| product_id | description |           options            |
+------------+-------------+------------------------------+
|          1 | size        | ["small", "medium", "large"] |
|          1 | color       | ["white", "black"]           |
+------------+-------------+------------------------------+

Shop Side

When a Shop owner wants a Product. I make a record in the shop_products table, referencing which Shop wants what Product, and adding an optional image, as following

+---------+------------+-----------+
| shop_id | product_id |   image   |
+---------+------------+-----------+
|       1 |          2 | image.jpg |
|       1 |          3 | image.jpg |
+---------+------------+-----------+

  Now the only issue remaining is: How do I make sure the Shop can choose which attributes he wants to take.

Should I create a shop_product_attributes table.. But I can't put a relation on the many-to-many because it doesn't have an id?


texts: How can I change default backup location for oracle11g using RMAN?

texts:
I want to change to another drive due to lack of disk space.

How can I change the default location of backup files used by oracle to another drive using RMAN?

By default it's in the folder ORACLE_HOME from variables.

edit:

Found some links with problems like mine here, but they didn't help.

My script looks like this one:

run {
    allocate channel d1 type disk;
    allocate channel d2 type disk;
    backup
    format 'd:/backups/%U.bkp'
    database plus archivelog;
    release channel d1;
    release channel d2;
}

This backup never finishes.


texts: generating all substrings (n-grams) for bit-strings

texts:
I followed SELECT all substrings (n-grams) of length n? to get a function for generating all n-grams for text types.  Works great.  I figured out how to cast my bit-strings to text, and the function from that link works.
However I need it to be as fast as possible, thus I wanted a bit-string native implementation, and I came up with this:

CREATE OR REPLACE FUNCTION public.ngrams(in input bit varying, in len integer)
 RETURNS SETOF bit
 LANGUAGE sql
AS $function$
SELECT cast((input << alpha) as bit(4))
FROM GENERATE_SERIES(0, LENGTH($1)-($2+1), 1) alpha;
$function$

This works, for N=4.  However when I tried to change it to $2 or len I would get:

ERROR:  22P02: invalid input syntax for type integer: "len"
LINE 5: SELECT cast((input << alpha) as bit(len))
                                        ^

How come I can use the named parameter from the function declaration for input but not for len? Or better yet, how can I feed a variable into that spot?


texts: Postgis reporting a point is 1216m away from itself using ST_Distance

texts:
I have a table containing a geometry column in 4236 projection. I take the ST_X and ST_Y values from one row, and create a point from them. I then transform the point and the original geometry column value into 3857 projection to get a 2d distance between then using ST_DISTANCE. I expect the distance to be ~0. The answer I actually get it 1216.something metres.
Here is example code which creates two points, which should be identical, and takes the distance between them, giving the same ~1216m answer
select ST_Distance(
               ST_Transform(
                       ST_SetSRID(
                               ST_MakePoint(5.6100, 58.8900), 4236),
                       3857),
               ST_Transform(
                       ST_SetSRID(
                               ST_MakePoint(5.6100, 58.8900), 4326),
                       3857));

I'm presumably making some stupid mistake here (I am by no means a GIS expect), but what is it that I'm doing wrong?


texts: Explicitly acquire EXCLUSIVE LOCK on a SEQUENCE OBJECT

texts:
How can we explicitly acquire a exclusive lock on a Sequence object in SQL Server 2012?

I am creating an application and need to lock a sequence object so no other transaction fetch data from it.

Can we achieve this by using get_applock procedure?

Thanks


texts:
We are using OLAP in our project and we are facing a scenario where we need to update the cube with real time data on daily basis.

I need some suggestions about an optimal way for updating the data in the cube.

Whenever new data arrives and made an entry in that table we need to update the current values in the cube like a trigger.


texts:
I have 4 Servers, Servers A, B, C, D.

Server A publishes a Replication instance, and Servers B, C, D have Pull Subscriptions to it.

Server B's subscription needs to be Reinitialized, but I am concerned that reinitializing it (Using the "current snapshot", not by generating a new one) will impact Replication Performance to the other Servers.

Should I be concerned, or because Servers B, C, and D have Pull (instead of Push) subscriptions, am I just over-thinking this?


texts:
Right now I have a MySQL database with a couple of tables with around 20MM rows each; This DB is located on a single server ( 4gb RAM, 60GB storage, 2 CPUs) each table size is 4gb +-, but my server storage is about 75%, and I'll need to upgrade the server.

Instead of growing vertically I want to grow horizontally, after reading about sharding, partitions and clusters i try the cluster solution, using MySQL cluster server.

I quote the MySQL Cluster docs

  MySQL Cluster automatically shards (partitions) tables across nodes,
  enabling databases to scale horizontally on low cost, commodity
  hardware to serve read and write-intensive workloads, accessed both
  from SQL and directly via NoSQL APIs.

My current setup is:

server-db0 - management
server-db1 - SQL node
server-db2 - SQL node
server-db3 - Data node
server-db4 - Data node

Each server 2gb ram 2CPUs 40GB storage

My cluster config file is:

[ndbd default]
NoOfReplicas=2
DataMemory=750M
IndexMemory=200M

[tcp default]
portnumber=2202

[ndb_mgmd]
hostname=10.132.36.121
datadir=/var/lib/mysql-cluster

[ndbd]
hostname=10.132.36.251
datadir=/usr/local/mysql/data

[ndbd]
hostname=10.132.36.104
datadir=/usr/local/mysql/data

[mysqld]
hostname=10.132.36.112

[mysqld]
hostname=10.132.36.117

All is working as expected

-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2    @10.132.36.251  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0, *)
id=3    @10.132.36.104  (mysql-5.6.28 ndb-7.4.10, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.132.36.121  (mysql-5.6.28 ndb-7.4.10)

[mysqld(API)]   2 node(s)
id=4    @10.132.36.112  (mysql-5.6.28 ndb-7.4.10)
id=5    @10.132.36.117  (mysql-5.6.28 ndb-7.4.10)

Memory Usage

ndb_mgm> all report memory
Node 2: Data usage is 0%(68 32K pages of total 24000)
Node 2: Index usage is 0%(81 8K pages of total 25632)
Node 3: Data usage is 0%(68 32K pages of total 24000)
Node 3: Index usage is 0%(81 8K pages of total 25632)

In my tests, I saw that all the data is replicated on each SQL node I inserted 750K rows and the Data usage, and Index usage are the same on both servers.

My main question is

If my big table size is 4GB, I will need to upgrade my node servers to a bigger server with more RAM? For what I see this is not a horizontal growing, I'll still need bigger servers instead of more small servers.

I don't see the how this shards works, I missing something?

Would be great if you point me to the right path.

Thanks in advance.