texts: Logic to convert a UTC date-time to a specific time zone

texts:
I have a table which has 1 row per event. Each event has a datetime stamp column which has values in UTC time. Each row has an associated local timezone in which the event occurred.

I have a DIM table which contains timezones. This dimension provides the UTC offsets for each timezone, one offset for DST, one offset for STD.

We have a second DIM table which contains daylight savings periods for each year. This table is used to determine if an event occurred in DST period or not (if the event location is a location that observes DST).

I am trying to create a query which outputs the UTC stamps from the first table in their local zone.  What "gotchas" should I be watching out for?

There is one "gotcha" that I can't figure out:

The start and end times in the daylight savings dimension are like this:

BEGIN_DATETIME  END_DATETIME        IS_DST
2010-11-07 02:00    2011-03-13 01:59:59 NO
2011-03-13 02:00    2011-11-06 01:59:59 YES
2011-11-06 02:00    2012-03-11 01:59:59 NO
2012-03-11 02:00    2012-11-04 01:59:59 YES
2012-11-04 02:00    2013-03-10 01:59:59 NO
2013-03-10 02:00    2013-11-03 01:59:59 YES

This works great when looking up a LOCAL time to see if it was in DST period (because the 2:00 AM begin time is applicable to the local timezones), but does not work for looking up a UTC time.

HMM, I think I just answered my own question - I need 2 more columns, BEGIN_DATETIME_UTC, END_DATETIME_UTC. Sometimes it just helps to write out the problem I guess. :)  I will still post the question to see if there is anything else I am overlooking.

Are there any other gotchas like the one above that I should be considering? Again, I have UTC times, and need to convert them to a local timezone.

I don't think it matters for this question, but environment is SQL Server 2005, and server is configured to the Pacific Timezone.


texts: Alter auto-increment of table with 16 million rows

texts:
Learned this the hard way. Need to alter a table's auto-increment with 16 million rows.

Now, it's taking quite sometime.

Any estimate how long it would take? Sorry.


texts:
When I use a single mongos like 1.1.1.1:30000, the driver will return in 20ms to find a batch of documents.

When I use 1.1.1.1:30000,1.1.1.2:30000,1.1.1.3:30000,it costs 60ms,three times more to return.(to find the same batch)

And when I use 5 mongos instances, 5 times more.

It's better to use mongos locally, perhaps. But why?

Sorry for my nescience about mongo. What I know is that mongo client is always described some sort of like this

  mongoc_client_t is an opaque type that provides access to a MongoDB server, replica set or sharded cluster.

Which can't explain what happened and why mongo driver costs more time as using more mongos node in uri like this:

mongocxx::client client_;
client_.reset(new mongocxx::client{mongocxx::uri{"1.1.1.1:30000,1.1.1.2:30000,1.1.1.3:30000"}});
...
mongocxx::read_preference read_preference;
read_preference.mode(mongocxx::read_preference::read_mode::k_secondary_preferred);
find_options.read_preference(read_preference);
auto collection = (*client_)["db"]["col"];
auto cursor = collection.find(filter_builder.view(), find_options);
...

PS:
  All mongos refer to the same cluster.

I'm now trying to debug the latest driver.

My drivers in this case:

mongo-c-driver:
  #define MONGOC_VERSION_S "1.5.1"

mongo-cxx-driver:
  #define MONGOCXX_VERSION_STRING "3.1.1"

network & latency time:

1.1.1.1,1.1.1.2,1.1.1.3 all in the same room

latency:
  ping localhost 0.03ms
  ping 1.1.1.2(from 1.1.1.1) 0.2ms

batch size:
  about 100
  find{"uid":{"$in":[1,2,3...,100]}} uid is an ensured index.


texts: SQL Server-Table has many AFTER INSERT triggers that insert into the table itself

texts:
I purposely created this example to represent my problem.
I created table like this:
CREATE TABLE a
(
  id INT
)

I then created 2 AFTER TRIGGER like this:
CREATE TRIGGER insert_a
ON a AFTER INSERT
AS
BEGIN
  INSERT INTO a VALUES (1)
END
GO

CREATE TRIGGER insert_a2
ON a AFTER INSERT
AS
BEGIN
INSERT INTO a VALUES(2)
END
GO

After that I inserted into table:
INSERT INTO a VALUES (0)

I got the result :

Msg 217, Level 16, State 1, Procedure insert_a2, Line 5 [Batch Start Line 0].
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

I think there is cyclical situation here.The insert, inside the trigger, fires the trigger, and again and again. Will that happen?
I want to know what happens inside ?
Is there the way to solve this problem but still keep intact 2 AFTER INSERT triggers?
Crazy situation.


texts: Size of Binary log file in Mysql Replication

texts:
I have a master - server replication setup in Linux servers.

Master Configuration details :

 MySQL version is 5.1 and replication mode is MIXED type.

Slave Configuration details :

 MySQL version is 5.6

If i insert single record , then it is getting replicated to slave.

When i am running a script to insert thousand of records at master , it is not getting replicated.

When i see the size of binary log file , it is of 908 MB
and when i see the size of relay log file , it is 560 KB.

Does , size of a binary log file matters ?
or
what is the probable cause of this.

Below is master cnf details :

  [mysqld]

  datadir=/var/lib/mysql
  socket=/var/lib/mysql/mysql.sock
  user=mysql
  # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0
    bind-address = #master IP# ( not showing here )
    port=3306
    wait_timeout=90
    net_read_timeout=90
    connect_timeout=90
    max_connections=100
    lower_case_table_names=1
    log-bin=mysql-bin
    binlog_format=MIXED
    server-id=1

[mysqld_safe]
     log-error=/var/log/mysqld.log
     pid-file=/var/run/mysqld/mysqld.pid


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

texts: Tried running systemctl status mysql.service after mysql wont restart got the following log kindly help:


texts:
Can you please explain what they mean by 'physically corrupt data'?

Read uncommitted (the lowest level where transactions are isolated
only enough to ensure that physically corrupt data is not read)

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189122(v=sql.105)?redirectedfrom=MSDN


texts: MySQL - Many UPDATEs to run after INSERT

texts:
I have a basic CRUD application where people are adding records. When they INSERT someone new, there are many updates (about 100) that need to be done to the record after it's been added.

I have tried to put the UPDATE statements in a TRIGGER but it would either lock the table --OR-- give me the "can't update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger" error.

Then I tried to put them all in a PROCEDURE -- which took about 30 seconds to run. Unacceptable.

I don't think I'm doing anything difficult here. Mostly, the updates are updating one field based on another.

Example

INSERT INTO data_table (field1, field2, ...) VALUES ('$field1', '$field2', ...);

Then the updates:

UPDATE data_table SET field2 = 'New York City' WHERE field1 = '212';
UPDATE data_table SET field2 = 'Los Angeles' WHERE field1 = '213';

etc.

Would it be faster/more efficient to do a bunch of IF...THEN statements in PHP to change the variable before inserting?

I'm just trying to speed up the process of inserting the record and processing the updates. Any help?


texts: Combine multiple records into one, using name from only one record

texts:
I've got a query that returns a table of technicians and the number of work orders they've filled. The problem is that sometimes technicians get entered using their full name, and sometimes they get entered using their userID. I want this table to use full names, and combine records where the userID is used with records corresponding to their full name. UserID comes as either 'FirstInitial' + 'LastName' or '$FirstName' + '$LastInitial', where + is string concatenation

Example of my table:

 Maddoc 74 - 229
 M74 - 21
 Maddoc7 - 14
 John Smith - 215
 jsmith - 47
 johns - 19
 Anna Kellog - 251
 akellog - 67
 annak - 14

What I want it to look like:

 Maddoc 74 - 264
 John Smith - 281
 Anna Kellog - 332

Here's my current query:

SELECT "RESPONDEDBY" AS Technician, COUNT("RESPONDEDBY") AS Tickets
FROM TRACKIT_DATA2.TrackItApp_1.vTASKS_BROWSE
WHERE DATEPART(m,"CLSDDATE") = DATEPART(m, {?reportingDate})
AND DATEPART(yyyy,"CLSDDATE") = DATEPART(yyyy, {?reportingDate})
AND "RESPONDEDBY" NOT IN (SELECT "RESPONDEDBY" FROM TRACKIT_DATA2.TrackItApp_1.vTASKS_BROWSE WHERE "RESPONDEDBY" = 'EmailMonitor')
GROUP BY "RESPONDEDBY"
ORDER BY COUNT("RESPONDEDBY") DESC


texts: Calculating disk space usage per MySQL DB

texts:
I am currently using information_schema.TABLES to calculate the total disk space usage grouped by the database name, but it is running terribly slowly. On servers with hundreds of databases, it can take minutes to calculate.

What is the quickest method of calculating disk space usage by database? Should I just be looking at the filesystem? Is there a method for speeding up information_schema?


texts: Non-Clustered-Index on a temporary table

texts:
I am working on various query optimization techniques. I reduced the query execution time from 1 minute to 12 seconds just by adding a Non-Clustered-Index on a table including one column (which is used in multiple where conditions) but DBA is very picky about adding indexes.

I would like to know if it makes any difference by adding NCI on a temp table instead of actual table. If YES, how? If NO, why?


texts: How to monitor Mongo databases and Cloud servers?

texts:
We have mongo databases installed in AWS and Azure based servers. We need to monitor the Mongo databases and Cloud servers on daily basis. Is there any way to healthcheck the databases and servers ?

Thanks,


texts: How to see who made a change to a table in SQL Server

texts:
Is it possible to see who changed a table (or a stored procedure) in a database when the recovery model is simple? E.g. length of data type...
If the recovery model is full will it be possible so see this in the log file?
Is it only with a trigger that one can log every access to a table or is there a build in option for this?


texts: SQL Server Version Store Performance differences between RCS and ASI

texts:
I understand that Read Committed Snapshot (RCS) operates at statement level, and Allow Snapshot Isolation (ASI) operates at transaction level, and both store row versions in tempdb. I also understand that ASI must be enabled in the DB, and in the settings of each connection.

My question is concerning the level of load that is placed on tempdb under RCS versus ASI. For example, for the same workload, would the load on tempdb be similar, or would the load on tempdb typically be higher (because it has to store more row versions)?


texts: Exclude elements are already in file - Foreach loop powershell

texts:
Please help me to correct the below code . I am trying to exclude the filename which already received email but -and $i.file -ne $SentUsers not excluding the already sent filenames . Please help me .

I am trying below things :

Write filename to a file
Get filename to a file
Exclude filename which already recieved an email by using -and $i.file -ne $SentUsers
Copy new current file name to file by overwriting existing file

Below is my code full :

$Q =

"
*** SQLCode Here ***

"
$ab    = Invoke-Sqlcmd -ServerInstance Serv1 -Database DB1 -Query $Q

## Write filename to a file

$l = $ab | select file | Out-File C:\temp\fname.txt

## Get filename to a file

$SentUsers = Get-Content -Path C:\temp\fname.txt

 foreach($i in $cr  ){
 ## Exclude filename which already recieved an email by using -and $i.file -ne $SentUsers

                    If ( $i.Uid -eq 'No' -and $i.UIntheTeam -eq 'No' -and $i.file -ne $SentUsers ){
                        $user = $i.User

Send-MailMessage -To @("abc") -Cc $user -From 'abc1' -SMTPServer abc.co.jp  -Subject "TestSub " -Body "

** Mail Message Body Here **

" }
                    Else {
                        'Exit'
                    }

                }

# Copy new current file name to file by overwriting existing file

$ab | select file | out-file -FilePath C:\temp\fname.txt


texts: How to store formula method in the table

texts:
I am trying to figure out how to store formula method in the table (I could create category_formula table).

Each category will have different way of formula method to calculate the point and I need to assign formula method on each category. What the best way to do this?

For example:

CPU(category_id=1), to calculate the point the formula would be: (commision + fulfilment + bonus) - (cost) / 10

Hard Drive(category_id=2), to calculate the point the formula would be: (commision + cost) / 10

And formula would be different for other categories.

mysql> select * from category;
+----+------------+
| id | name       |
+----+------------+
|  1 | CPU        |
|  2 | Hard Drive |
+----+------------+

mysql> select * from items;
+----+-------------+-------------------------+--------+-----------+------------+-------+
| id | category_id | name                    | cost   | commision | fulfilment | bonus |
+----+-------------+-------------------------+--------+-----------+------------+-------+
|  1 |           1 | Intel CPU Core i7 3770K | 300.00 |     30.00 |       15.5 |    10 |
|  2 |           2 | 160GB Samsung Spinpoint |  50.00 |     15.00 |          0 |     0 |
+----+-------------+-------------------------+--------+-----------+------------+-------+
2 rows in set (0.00 sec)

Update:
I would use PHP to do the calculation. For example Admin on the website can select a formula setting on each category and save it. The formula setting could be saved in category_formula table but how category_formula table should be designed?

Create:

--
-- Table structure for table `category`
--

CREATE TABLE IF NOT EXISTS `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `category`
--

INSERT INTO `category` (`id`, `name`) VALUES
(1, 'CPU'),
(2, 'Hard Drive');

-- --------------------------------------------------------

--
-- Table structure for table `items`
--

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `cost` decimal(6,2) NOT NULL,
  `commision` decimal(6,2) NOT NULL,
  `fulfilment` float NOT NULL,
  `bonus` float NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`id`, `category_id`, `name`, `cost`, `commision`, `fulfilment`, `bonus`) VALUES
(1, 1, 'Intel CPU Core i7 3770K', '300.00', '30.00', 15.5, 10),
(2, 2, '160GB Samsung Spinpoint', '50.00', '15.00', 0, 0);


texts: SQL Server - convert complicated view to table - the best way

texts:
The view I'm building is getting more complicated and bigger every week. The idea for it was to have a source for a number of multiple reports for management, including ad-hoc queries done by some of them (Accountants - what can I say). I'm not worried about the load on the server - this one is dedicated just for that, so it will be a while until any serious resources got used. But it's taking longer and longer to do anything on it.

It's about 30% calculated fields, has at the moment with 25 joins of which I can drop maybe 8, but there will be more added at some point. No way to make it indexed, as it's spanning 4 different databases (for now), so performance is tortoisery at best.

So I'm thinking to dump the contents of the view into a table on schedule and the question is: is there a better way? And if not, what obstacles to look out for?

EDIT: Staleness of data: up to previous day. Data range: all rows, unfortunately, with current count 3.2 million.
Also, there is the issue of the development phase - right now we're working out what we need, want and desire, so the performance tuning is pointless ATT. Indexing would be a good start, but not right now on this view. Split into multiple views/queries sounds good, but will not cut down execution time in any meaningful sense (down 20 minutes sounds great until you realize that starting point is 75 minutes).

Data I'm using is already copied from other databases(and servers) via SSIS package. Reworking it to consolidate data in one database might be doable, but that's not my jurisdiction. This may involve financial costs and until all the dev work is finalized will be rejected by management.

All I know that I need to speed things up considerably and fast. May be dirty. Unless I'll spend more time waiting for queries to execute than on development proper.


texts: tSQL Stored Procedure with CTE; CASE statement in WHERE slowing run

texts:
I have a user-defined Stored Procedure
When calling without passing explicit values, I want to just pass all Locations (nvarchar(50)), which is the primary key'd field of a table: Monitor_Locations (with ~850 entries)

One part of the SP is defined as follows (clipped).

ALTER PROCEDURE [dbo].[dev_Tech@Locs2b] ( --CREATE or ALTER
     @Locations as nvarchar(MAX) = NULL -- = 'GG1,BenBr14,BenBr00,YB_ToeDrain_Base'
    ,@rangeStart as DateTime = '1970-01-01'
    ,@rangeEnd as DateTime = '2099-12-31'
) AS BEGIN
SET NOCOUNT ON; --otherwise concrete5 chokes for multi-table returns.
DECLARE @loclist as TABLE (
    Location nvarchar(50) PRIMARY KEY
)
IF @Locations is NULL
    INSERT INTO @loclist(Location)
        SELECT Location from Monitor_Locations order by Location
ELSE --irrelevant for this question
    INSERT INTO @loclist(Location)
        SELECT
            ML.Location
        FROM Monitor_Locations as ML join
            tvf_splitstring(@Locations) as ss ON
                ML.Location=ss.Item OR
                ML.Location like ss.Item+'[_]%'
        ORDER BY ML.Location;
With Deploys as (
    SELECT
        D.Location,
        MIN(D.Start) as Start,
        MAX(D.[Stop]) as Stop
    FROM
        Deployments as D
    WHERE
        D.Stop is not NULL
)

...do a bunch of other stuff...

in order to improve the speed of the stored procedure when a restricted list of sites is sent into the SP, I wanted to replacing the WHERE clause with

WHERE
    CASE
        WHEN D.Stop IS NULL THEN 0
        WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
        WHEN EXISTS (SELECT 1 from (SELECT Location from @loclist as l where l.Location=D.Location) as ll) THEN 1 --ELSE NULL which is not 1
    END=1

but where the SP once took 6-8 seconds to execute, now it takes 2.5 mins (for calling without a restrictive list).  I thought it would take roughly the same amount of time each way for the full list, as the second clause of the CASE should be fired very quickly and the third clause should never be examined.

So what's going on? This code:

WHERE
    CASE
        WHEN D.Stop IS NULL THEN NULL
        WHEN @Locations IS NULL THEN 1 -- full list, so binding to another list doesn't do us any good.
        WHEN EXISTS (SELECT 1 from (SELECT Location from @loclist as l where l.Location=D.Location) as ll) THEN 1 --else null
    END is not null

Takes a ~10 minute run time with this plan:

To contrast here's the WHERE D.Stop is not NULL plan (6s):

At one point, this SP was taking 1 second with this version, but by changing the SP and then back again, it took 6 seconds again. As mentioned in the answers, this was likely due to parameter sniffing.

Run times

My goal time for execution is less than 2 seconds, as this will be a frequently executed SP on a web application that uses this to populate and restrict other user selections.  Basically, I don't want this to be a noticeable bottleneck. Initial run times were on the order of 3 minutes, but after adding or altering some indexes, this dropped to the 6-8 second range.

On Monday (2016-08-29), prior to major alterations
Simple WHERE without input parameters: 5s
Simple WHERE with rangeStart and rangeEnd: 4s
Simple WHERE with @Locations set to a 7 element CSV variable
CASEd WHERE: up to 10 minutes

After re-working the CLR function (see my answer below)
Tuesday (2016-08-30)
Simple or CASEd WHERE without input parameters OR
Simple or CASEd WHERE with rangeStart and rangeEnd: 3s
Simple or CASEd WHERE with 7 element @Locations: 0-1s

After migrating table variable @loclist to temp table #loclist
All tested WHEREs/parameters: 0-1s


texts: Creating tables and triggers dinamically

texts:
Can I automatize the creation of tables and triggers to this table ?
I need to create one table every month and this table needs a trigger...
Is It possible ?


texts: I know that it's possible to set execute permissions for a stored procedure to a database role or user.  My question is, how is access to any tables that the SP accesses determined?  Does the principal who calls the SP also have to have requisite permissions for the underlying objects that are touched by the SP?


texts:
I have created a sqlcmd startup script that i have stored in c:\myscript.sql, and I want it to run every time i start sqlcmd.
     how do I configure sqlcmd to automatically run your script every time it starts?


texts: Creating backups on Alwayson without differential backups

texts: I went through this link on stack exchange and also Brent Ozar's . I don't plan to create any differential backups for the AAG group. Can you please let me know if enabling this option should work. I will be adding a new database for an application, and I then plan to create and schedule Ola's scripts. Is it true that for new databases, only full backup happens and not the log backup? What can I do to resolve the issue? Please let know. Thanks!


texts: SQL Server Data File/Log File poor performance

texts:
I've been asked to look at a database/application server that's "performing slow". No one could really tell me what was performing slow (except for the database backups) so this has been a discovery process.
This server is a Dell PowerEdge R720 running Windows Server 2008 R2 and SQL Server 2012 Standard (11.0.2100.60) (x64).
-Intel(R) Xeon(R) CPU E5-2640 0 @ 2.50GHz, 2500 Mhz, 6 Core(s), 12 Logical Processor(s)
-16GB RAM
-DELL PERC S110 SCSI Disk Device running as a software RAID 5.
The disk is partitioned into two local drives: C and D. The OS files are all on C. SQL Server is installed on D, and the database files (.mdf and .ldf) are also on D. In addition, we're writing our nightly backups to the D drive! :-)
The database mdf file is around 6GB. Backups are taking 30 minutes.
A quick look at the wait types revealed that the WRITELOG wait type was the most prevalent - consuming 69%:

Suspecting transaction log/disk performance issues, I enabled various PerfMon counters and established baseline averages over a couple of days. The PerfMon counters include, but are not limited to:
\PhysicalDisk(0 C: D:)\Avg. Disk sec/Write - Avg = 0.073
\SQLServer:Databases\Log Flush Wait Time - Avg = 71.403
\SQLServer:Databases\Log Flushes/sec - Avg = 1.910
\SQLServer:Databases\Log Bytes Flushed/sec - Avg = 1724.604

The transaction log was originally configured with an initial size of 100MB and set to grow at 10%. When I first logged onto the machine, the transaction log had grown to 5GB across 256 VLFs suggesting some fragmentation.
Paul Randall's query against sys.dm_io_virtual_file_stats reveals some serious write latency with both the data and log files - although contrary to the WRITELOG wait type, the data file write latency is quite a bit more:
Data File: 1369
Log File: 66

Based on the article by Kimberly Tripp, I "rebuilt" the transaction log and assigned it a size of 2GB (somewhat arbitrarily), yielding 24VLFs.
This seemed to decrease the WRITELOG wait type percentage some (now at ~61%) as well as the \SQLServer:Databases\Log Flush Wait Time (now at ~48ms).
Bus this still seems high.
I realize that the data and log files (and backups) should all be on a different drives for parallel reads/writes and maximum throughput. Best practices aside, we have other production systems configured the same way as this one, but that are running better, e.g. Avg Log Flush Wait Time = < 3 ms.
What else could I look at to figure out why this server in particular is slower with regards to disk read/write response time and backups?
Could index fragmentation/fill factor settings cause this poor of performance?
What about disk fragmentation?
If I need to tell the client we need another disk, then so be it - I just want to be certain before spending money.
Any other ideas???


texts: SQL Server with peer-to-peer replication migration from on-prem to AWS EC2 instances

texts:
I need to move three on-prem servers with peer-to-peer replication to AWS.
SQL Server version is 2012. The AWS SQL Server will be on EC2 instances as the SQL Server RDS doesn't support replication yet. Databases are relatively small with around 3 TB data.

I am thinking to have following steps:
On the day before the D-Day

Prep EC2 instances for SQL.
Install required versions and upgrade to same SP as on-prem.
Copy full and diff backups upto that day to EC2 servers.
Get TDE certificates.
Restore to common point in time for all three servers.
Establish replication between servers.

On D-Day

Copy differential backup and logs from each server to corresponding AWS Server.
Restore the differential and recover to same point in time by applying logs.

I might be able to get few hours of downtime but the servers are live and I need to accomplish this with minimal downtime.If someone has come across similar scenario, I would appreciate any inputs for best practices.
Also, DMS has limitations for us so we will need to skip that option and try to go for lift-and-shift.


texts:
I have a user that wants to use SQL Server Management Studio (SSMS) to run SELECT statements.  I don't want him to be able to modify data.

I can't regulate his permissions through Windows Authentication because his user account needs modify permissions for programs that we run.

I could create a separate Windows account for him with less permissions, however if he does not use this new account, he will be able to change data.

Does anyone else have this problem? Is there a simple solution?


texts: Postgresql: SQL query running very slow after two months on postgresql server

texts:
I have postgresql server install on two different servers. one server RAM is 30GB and another server RAM is 64 GB. i am executing below simple query.

SELECT  id,
        hour,
        dow,
        count(r_e_id) AS volume
FROM test
GROUP BY id,hour,dow

Server having 64GB ram is executing above query in 1.5 seconds where as server with 30 GB ram is taking 7 seconds. There are total 2.3 million records on both the servers. This query was used to take 1.5 seconds on 30 GB server 3 to 4 months ago but it is now taking 7 seconds. i have vaccume the table but still taking 7 seconds.

Below is the explain analyze output of 64GB RAM server for the above query

   "HashAggregate  (cost=122240.58..122348.10 rows=10752 width=16) (actual time=1482.720..1483.911 rows=5726 loops=1)"
"  Group Key: id, hour, dow"
"  ->  Seq Scan on test  (cost=0.00..98330.79 rows=2390979 width=16) (actual time=1.210..290.304 rows=2390970 loops=1)"
"Planning time: 0.184 ms"
"Execution time: 1484.253 ms"

Below is the explain analyze output of 30GB RAM server for the above query

"HashAggregate  (cost=3208512.50..3208616.66 rows=10416 width=16) (actual time=8009.038..8011.166 rows=5615 loops=1)"
"  Group Key: id, hour, dow"
"  ->  Seq Scan on test  (cost=0.00..3101655.75 rows=10685675 width=16) (actual time=2.139..6780.604 rows=2296651 loops=1)"
"Planning time: 0.126 ms"
"Execution time: 8011.987 ms"

Why my query is taking more time on 30GB Ram server. it was not taking more time two months before but i don't know what went wrong suddenly.

What could be the reason for the slow execution of the query

Thanks


texts: Confusing Query: Mixing Aggregates and Non-Aggregates

texts:
Hope someone can clarify how to do this query from SQL Server 2012, or maybe what it is asking;
it seems I am being asked to do a query that combines aggregates with non-aggregates, leading to what seems like an impossible query, since one can only group by fields that appear in the Select clause, which messes up
the query.

I am asked to do a query on the table Invoices:

Invoices (InvoiceID, VendorID,InvoiceDate, InvoiceTotal,...)

I am asked to do a query that produces 6 columns:
3  columns already appear "as is" in the table: VendorID, InvoiceDate, InvoiceTotal

And 3 other columns are aggregates:

4) Sum(InvoiceTotal)  AS VendorTotal: Sum of invoices for each VendorID

5) Count(InvoiceID) AS VendorCount: Count of Invoices for each VendorId

6) Avg(InvoiceTotal) AS VendorAvg: Average of Invoices per VendorId

Now, problem is that one cannot combine in the same query (with exceptions I cannot use here) aggregates and non-aggregates unless one groups by the non-
aggregates. So I can do:

Select
    VendorId, InvoiceDate, InvoiceTotal,
    Sum(InvoiceTotal) AS VendorTotal,
    Count(InvoiceId) AS VendorCount,
    Avg(InvoiceTotal) AS VendorAvg
GROUP BY
    VendorID

Now, I have no trouble if I only group by VendorID. But in order to get a
valid query, if I select either InvoiceDate or InvoiceTotal -- both non-aggregates -- I must group by each of them in order to get a valid query.

But grouping by these last two basically undoes the other aggregates: If
I group by invoice date, I lose the aggregation by Vendor, since each vendor
has different invoice dates.

I have tried doing self joins, using Invoices AS I1 join Invoices AS I2
and doing aggregates in I1 and non-aggregates on I2, but this does not seem to work.

Am I missing something obvious here? Any Ideas?

Edit: The answer was found to be using OVER (PARTITION BY VendorId) , like so:

Select
VendorId, InvoiceDate, InvoiceTotal,
    Sum(InvoiceTotal) OVER (PARTITION BY VendorId) AS VendorTotal,
    Count(InvoiceId) OVER (PARTITION BY VendorId) AS VendorCount,
    Avg(InvoiceTotal) OVER (PARTITION BY VendorId) AS VendorAvg
GROUP BY
    VendorID


texts: sql server - Restoring Windows Database Backup File on a Linux Server Instance

texts:
I need to work with a SQL server instance, however my workspace runs Linux and the database backup that was given to me was running on Windows.

I succesfully managed to install SQL Server on my Linux environment, and I'm using DBeaver to access it, the main problem I have is when I try to restore this .bkp file in my SQL Server instance.

I'm trying to restore the database with this SQL command I read in an answer in this site, however I need the Logical Data File Name and the Logical Data Log. So I ran the next SQL command.

RESTORE DATABASE FILELISTONLY
FROM DISK = '/var/tmp/(redacted).bkp';

I get SQL Error 5133

SQL Error [5133][S0001]: Directory lookup for the file "D:\SQL2012\DATA\(redacted).mdf" failed with the operating system error 2(The system cannot find the file specified.).

Is it possible to restore this file? If so what am I doing wrong?

Thanks in advance.


texts: Modelling a scenario involving architecture firms projects

texts:
I'm very new to Database Modelling (using the Finkelstein methodology) and I'm getting confused on a modelling scenario in particular.

I am able to draw out the basics, however am running into some confusion with the intersecting and possibly types of entities (when the scenario refers to building style preferences).

Business rules

The relevant business domain is as follows:

An architectural firm has a number of architects working for it.
An architect may have more than one style of buildings they like to design on.
The firm’s clients hire the architects to work on a building projects. Each building project may have a number of architects who work on the same project over a period of time.
We need to record the project start date and the amount of time each architect spent on the project.

My current advance

What I have so far is this:

Question

Can someone please tell me if I'm moving in the right direction?


texts: Multi-AZ failover - replica still read only after failover

texts:
Setup:
I have a single RDS cluster.  This cluster has two instances, one in each zone.  Naturally, one "replication role" reader and one writer.

What happened:
I manually triggered a failover of the main instance from the RDS console "Instance actions" menu.  After doing so, the console eventually showed this change including the swap of reader and writer roles.  However, the replica instance remained read only so all writes were failing.  This replica instance's Parameter Group settings have read_only:TrueIfReplica.

Questions:
What other information can I provide to clarify my configuration or issue?
Any clues on why this replica instance stayed read only?
If this is the expected behavior of read_only:TrueIfReplica, where else should I look to fix my configuration?

Cheers!


texts: What are some good resources for learning about document oriented databases?

texts: I'm interested in learning about NoSQL databases and I decided to start with document oriented databases.  What are some good resources to learn the concepts and good design of this type of database?


texts: SQLite: trigger for multiple tables and/or multiple actions

texts:
I am looking for a way to write SQLite trigger which will affect multiple actions and multiple tables (in fact I want it executed on a particular table each time any query is executed on any table in the database).

Here is an example, to explain what I mean.
I have 3 tables: Students, Courses, Professors.
Each student has activation_deadline field, which is set to NULL upon activation.
I want each time any query is executed, to check if there are students with expired activation_deadline and if there are -- remove them.
Currently I have three separate triggers on Students table for this.
One for UPDATE:
CREATE TRIGGER Remove_Unactivated_Students_Update
AFTER UPDATE
ON Students
FOR EACH ROW
BEGIN
    DELETE FROM Students
    WHERE (activation_deadline IS NOT NULL) AND (activation_deadline <= strftime('%s', 'now'));
END;

One for INSERT:
CREATE TRIGGER Remove_Unactivated_Students_Insert
BEFORE INSERT
ON Students
FOR EACH ROW
BEGIN
    DELETE FROM Students
    WHERE (activation_deadline IS NOT NULL) AND (activation_deadline <= strftime('%s', 'now'));
END;

And one for DELETE:
CREATE TRIGGER Remove_Unactivated_Students_Delete
AFTER DELETE
ON Students
FOR EACH ROW
BEGIN
    DELETE FROM Students
    WHERE (activation_deadline IS NOT NULL) AND (activation_deadline <= strftime('%s', 'now'));
END;

What I would like, is something like this:
CREATE TRIGGER Remove_Unactivated_Students
AFTER INSERT, UPDATE, DELETE
ON Students, Courses, Professors
FOR EACH ROW
BEGIN
    DELETE FROM Students
    WHERE (activation_deadline IS NOT NULL) AND (activation_deadline <= strftime('%s', 'now'));
END;

Which, of course, gives me:
Error: near line 1: near ",": syntax error

Given what is said above, here are the questions:

Is it possible to write SQLite trigger for multiple tables, at once (if so how)?
Is it possible to write SQLite trigger for multiple actions (e.i. UPDATE, INSERT, DELETE), at once (if so how)?
Maybe there is a better way to achieve what I am aiming for?


texts: Delete rule on view does not execute every statement

texts:
When I have more than one statements in a rule on a view, the second one is not executed in case the first one deletes the original entry. Even when the second statement does not have anything to do with the original entry at all.

Here is a simple example. Tables are reach and rp (reachpoint) and in reach we have an id that can be referenced to rp.

CREATE TABLE test_delete_rule.reach
(
  id integer NOT NULL,
  rp integer
);

CREATE TABLE test_delete_rule.rp
(
  id integer NOT NULL
);

INSERT INTO test_delete_rule.reach (id, rp) VALUES (1,10);
INSERT INTO test_delete_rule.reach (id, rp) VALUES (2,20);
INSERT INTO test_delete_rule.reach (id, rp) VALUES (3,30);
INSERT INTO test_delete_rule.reach (id, rp) VALUES (4,40);
INSERT INTO test_delete_rule.rp (id) VALUES (10);
INSERT INTO test_delete_rule.rp (id) VALUES (20);
INSERT INTO test_delete_rule.rp (id) VALUES (30);
INSERT INTO test_delete_rule.rp (id) VALUES (40);

And there is a view on reach called vw_reach:

CREATE VIEW test_delete_rule.vw_reach AS
    select * from test_delete_rule.reach

And on this view is a rule, deleting the reach and the referenced rp:

CREATE OR REPLACE RULE reach_on_delete AS
  ON DELETE TO test_delete_rule.vw_reach
  DO INSTEAD
  (
    DELETE FROM test_delete_rule.reach
    WHERE reach.id = old.id;

    DELETE FROM test_delete_rule.rp
    WHERE rp.id = old.rp;
  );

When we delete now an entry from the vw_reach

delete from test_delete_rule.vw_reach where id = 2

The entry with id 2 from reach is deleted, but not the entry with 20 from rp.

When I have the same rule on the table with DO ALSO ir works:

CREATE OR REPLACE RULE reach_on_delete AS
    ON DELETE TO test_delete_rule.reach
    DO ALSO
    (
       DELETE FROM test_delete_rule.rp
       WHERE rp.id = old.rp;
    );

So according the response here https://stackoverflow.com/questions/5534927/how-to-write-a-delete-rule-on-a-view the second statement is not executed because old.rp does not exist anymore. This makes sense but I cannot confirm.
Because it cannot be because of the old, because it does not execute the second statement neither if it has nothing to do with the original entry.

E.g.:

CREATE OR REPLACE RULE reach_on_delete AS
    ON DELETE TO test_delete_rule.vw_reach
    DO INSTEAD
    (
       DELETE FROM test_delete_rule.reach
       WHERE reach.id = old.id;

       DELETE FROM test_delete_rule.rp
       WHERE rp.id = 20;
    );

Although the the second command has nothing to do with the values of the original entry.

But what in fact it works, when the order changes and the statement concerning the original is at the end. The following works:

CREATE OR REPLACE RULE reach_on_delete AS
    ON DELETE TO test_delete_rule.vw_reach
    DO INSTEAD
    (
        DELETE FROM test_delete_rule.rp
        WHERE rp.id = old.rp;

        DELETE FROM test_delete_rule.reach
        WHERE reach.id = old.id;
    );

So the conclusion is, that the rule is not continued after the original entry is deleted. It kinds of escapes. It does not do that on the table with DO ALSO.

Does anyone have a explanation for that behavior? It's quite confusing.


texts: Slow search by LIKE and other values (1 million rows+)

texts:
I'm currently developing a kind of search website where one can search for business companies in my country. The database table has 1 million rows and quite a few colums (42 columns).

But I find it very slow. Is that because of the query I'm executing, or the database?

I have created indexes on several columns wich are used in the search query too, but often when a query is run, it times out on 30 seconds.

The query run would look something like this:

SELECT *
FROM companies
WHERE ( navn LIKE ?
      OR keywords LIKE ? )
   AND ( ( forretningsadresse_fylke = ?
           AND ( forretningsadresse_kommune = ?
                  OR forretningsadresse_kommune = ?
                  OR forretningsadresse_kommune = ? ) )
          OR ( forretningsadresse_fylke = ?
               AND ( forretningsadresse_kommune = ?
                      OR forretningsadresse_kommune = ?
                      OR forretningsadresse_kommune = ? ) )
          OR ( forretningsadresse_fylke = ? )
          OR ( forretningsadresse_fylke = ?
               AND ( forretningsadresse_kommune = ? ) ) )

Engine: InnoDB.
Collation: utf8 - default collation

If any other info is needed, please say so. What could I do to speed up the search? Thanks in advance.

Edit: extra info.
Indexed columns: PRIMARY (id), organisasjonsnummer, navn, forretningsadresse_fylke, forretningsadresse_kommune, keywords


texts: Concurrent update queries locking rows when `where` clause doesn't match

texts:
My application sends messages to Slack.  On very rare occasions we may need to retract the message going out.  These retractions are handled by queueing a job for each messageId that has been created thus far.

I noticed during our last retraction about a week ago that we saw about 2k:

try restarting transaction (SQL: update `message` set `deleted_at` = 2018-12-04 04:47:44 where (`slack_channel_id` = xxxxxxxxxxxxxxxx and message_id = xxxxxxxxxxxxxxxx))"

It seems like the queue jobs (which are absolutely happening concurrently) are stepping on each other's toes.  My expectation is that this query would only put a lock on the individual row it needs (we have a compound unique index on slack_server_id, slack_channel_id and message_id), but it seems like more than one job is locking this record.

Why are these concurrent update queries locking rows that don't match the where clause?

CREATE TABLE `messages` (
  `slack_id` bigint(20) unsigned NOT NULL,
  `slack_channel_id` bigint(20) unsigned NOT NULL,
  `message_id` bigint(20) unsigned NOT NULL,
  `slack_message_id` bigint(20) unsigned DEFAULT NULL,
  `premium` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  UNIQUE KEY `slack_message_slack_id_channel_id_message_id_unique` (`slack_id`,`slack_channel_id `,`message_id`),
  KEY `slack_message_message_id_foreign` (`message_id`),
  CONSTRAINT `slack_message_slack_id_foreign` FOREIGN KEY (`slack_id`) REFERENCES `slacks` (`id`),
  CONSTRAINT `slack_message_message_id_foreign` FOREIGN KEY (`message_id`) REFERENCES `messages` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Update

I've added an id auto increment primary column and it did not resolve the transaction issues.


texts: Why is DBMS_CRYPTO inaccessible for most users by default?

texts:
Oracle's DBMS_CRYPTO package is locked down by default. Very few users have permissions on it; the only ones on my database are SYSMAN, OWBSYS, and APEX_030200. (Of course the owning SYS has privileges, too.) These notably don't include even the default SYSTEM account. The documentation states

  Oracle Database installs this package in the SYS schema. You can then grant package access to existing users and roles as needed.

in the "Security Model" section, so the behavior is documented as normal.

But what I don't understand is why. I could not locate any information about the reason behind this default either in the package's documentation or the guide on using it. Is there some risk that these functions pose? Would granting access to PUBLIC be a bad idea for some reason?


texts: An INSERT statement triggers the SQL Server Audit to write it down in the logs although INSERT is not listed as an Audit Action

texts:
Background:
I am trying to keep a track record of what users are querying in the database. One option I found is to create a Server and Database Audit Specification. I did not want to include INSERT statements since those are going to be logged for each record inserted which will be a lot if there are bulks of data being added to the table(s).
Problem:
We have a SQL Job (within the Job Agent) that inserts data into a table using an INSERT query. I would expect these logs would not appear in our .sqlaudit file but it does. Even do INSERT is not apart of our Actions within our Database Audit Specifications. I am wondering if it is triggered do to another action? I have listed all the actions that I have included but from my understanding none of them will trigger the server to write the INSERT logs in the .sqlaudit file. What could be the issue?
NOTE: I have also executed an INSERT query using the query tool and it still logs that as well.
Audit Action Types Added to Database Audit Specifications:

DATABASE_OBJECT_ACCESS_GROUP
SCHEMA_OBJECT_ACCESS_GROUP
DATABASE_OBJECT_CHANGE_GROUP
SCHEMA_OBJECT_CHANGE_GROUP
DELETE
EXECUTE
SELECT
UPDATE

Here is the statement that it is logging in the .sqlaudit file:
INSERT [dbo].[table_name]([a],[b],[c],[d],[e],[f],[g],[h],[i],[j],[k],[l]) VALUES(@Param000004,@Param000005,@Param000006,@Param000007,@Param000008,@Param000009,@Param000010,@Param000011,@Param000012,@Param000013,@Param000014,@Param000015)


texts: Second Instance on SQL 2016 server - SQL Browser not seeing instance?

texts:
I just installed a second SQL instance onto a development server.  The first instance is remotely accessible from a SSMS session from my PC, but the second instance is not.  I have the server set up with dynamic ports enabled - the SQL Server Browser service is listening and port 1434 is open on the firewall, and I can open a session to the original session on the PC.
I've done the necessary on SQL config for the instance, (enabled TCP IP, ensured the IPs are enabled), and can connect to the instance locally.  But I can't connect remotely, which is needed.
I note there is a single SQL Browser service on the server.  Reading makes me think the Browser should be able to see both instances - but it doesn't appear to be.
Any ideas?  Do I need a second SQL Browser service running?
Thanks


texts: Linked Server Configuration using remote user logins

texts:
I'm trying to configure a Linked Server using remote logins reducing who has access to the linked server by taking advantage of the not be made option. For test purposes alone, I have used SA, obviously I won't be using that going forwards however I just wanted to make sure the procedure worked and SA having all privileges would cater for this.
Again for troubleshooting purposes, I initially tried SA using the 'be made using this security context' option which worked well but obviously is not secure as I want to reduce who has access to the linked server.
When I tried the 'not be made' option with a local and remote login I receive an error suggesting that no login mapping exists although the login worked when I used the 'be made using this security context' option. What am I doing wrong?


texts: How to index two tables for JOINed query optimisation

texts:
I'm using PostgreSQL 9.5 on High Sierra.

Over the two tables:

request_logs - ~ 26K rows

response_logs - ~ 9K rows

I've the following query (with JOIN):

SELECT
  req.uuid,
  res.status,
  req.method,
  req.requesturi,
  req.accessed,
  req.payload reqpayload,

  res.payload respayload,
  COUNT(*) OVER() AS total_rows
FROM
  request_logs req
  LEFT OUTER JOIN response_logs res ON req.uuid = res.uuid
WHERE
   req.accountid = 2 AND
   req.requesturi not ilike '/v1/sessions%' AND
   req.accessed BETWEEN “2018-01-01 15:04:05 +0000” and “2019-01-02 15:04:05+0000” AND
   res.status IS NOT NULL AND
   req.applicationid = 1
ORDER BY
   accessed DESC LIMIT 1000

As I'm trying to optimise the query, I've experimented with different indexes:
Here's a list of what I tried:

Configuration 1:
 1. request_log.uuid (pkey, unique)
 2. response_log.uuid (pkey, unique, foreign key)

Response time avg. : 260 ms

Configuration 2:
 1. request_log.uuid (pkey, unique)
 2. request_log.applicationid
 3. response_log.uuid (pkey, unique, foreign key)

Response time avg. : 230 ms

Configuration 3:
 1. request_log.uuid (pkey, unique)
 2. request_log.applicationid
 3. request_log.accessed (timestampz)
 4. response_log.uuid (pkey, unique, foreign key)

Response time avg. : 230 ms

Configuration 4:
 1. request_log.uuid (pkey, unique)
 2. request_log.applicationid
 3. request_log.accessed (timestampz)
 4. request_log.accountid
 5. response_log.uuid (pkey, unique, foreign key)

Response time avg. : 230 ms

Configuration 5:
 1. request_log.uuid (pkey, unique)
 2. request_log.applicationid, request_log.accessed (combined)
 3. response_log.uuid (pkey, unique, foreign key)

Response time avg. : 240 ms

As visible from the result, indexing by applicationid (an int8) did help a little, while indexing by the timestampz accessed didn't help at all.
Maybe the bad performance is due to the JOIN?
Altogether, it seems quite slow and I try not to think what will happen when these tables contain millions of record (10M+).

What would be a better way to index these tables to make this query run faster?

EDIT:

Here is EXPLAIN ANALYZE for the last configuration:

Limit  (cost=3489.80..3490.69 rows=356 width=823) (actual time=241.152..241.345 rows=1000 loops=1)
  ->  Sort  (cost=3489.80..3490.69 rows=356 width=823) (actual time=241.150..241.288 rows=1000 loops=1)
        Sort Key: req.accessed DESC
        Sort Method: top-N heapsort  Memory: 2064kB
        ->  WindowAgg  (cost=1829.41..3474.71 rows=356 width=823) (actual time=230.040..237.993 rows=3951 loops=1)
              ->  Hash Join  (cost=1829.41..3470.26 rows=356 width=823) (actual time=8.622..17.974 rows=3951 loops=1)
                    Hash Cond: (res.uuid = req.uuid)
                    ->  Seq Scan on response_logs res  (cost=0.00..1604.21 rows=8821 width=758) (actual time=0.006..4.527 rows=9124 loops=1)
                          Filter: (status IS NOT NULL)
                    ->  Hash  (cost=1816.39..1816.39 rows=1042 width=102) (actual time=8.243..8.243 rows=4046 loops=1)
                          Buckets: 4096 (originally 2048)  Batches: 1 (originally 1)  Memory Usage: 1122kB
                          ->  Bitmap Heap Scan on request_logs req  (cost=105.85..1816.39 rows=1042 width=102) (actual time=0.581..6.449 rows=4046 loops=1)
Execution time: 242.154 ms


texts: how to bring in the most recent record in SQL in Teradata

texts:
I have a table that contains store number, inventory $, inventory year from 2014 through 2018. so if store 111 has inventoried in all the years, I will have 5 records for that store:

Store Inventory year Inventory $
 111      2014         $$$$
 111      2015         $$$$
 111      2016         $$$$
 111      2017         $$$$
 111      2018         $$$$

and if store 121 has inventoried every year but 2018 I would have 4 records for that store:

Store Inventory year Inventory $
 111      2014         $$$$
 111      2015         $$$$
 111      2016         $$$$
 111      2017         $$$$

I would like to write a query that would bring one record per store, and only the one with the most recent inventory - so based on my above example, I would have the 2018 inventory result for store 111 and 2017 inventory result for store 121.

Can you help please?


texts:
Let me preface this question in that while I have a strong background in T-SQL but I am still just a newbie when it comes to DB2.

The root query looks roughly like this:

Select
    Col1, Col2, Col3
From (
    Select A.Col1, B.Col2, B.Col3
    From SchemaA.TableA as A
        Inner Join SchemaA.TableB as B
            On A.Id = B.ParentId
    Where
        A.Col2 = 'TypeValue'
    Fetch First 5 Rows Only -- This is not allowed in a union or sub select

    Union

    Select A.Col1, B.Col2, B.Col3
    From SchemaB.TableA as A
        Inner Join SchemaB.TableB as B
            On A.Id = B.ParentId
    Where
        A.Col2 = 'TypeValue'
    Fetch First 5 Rows Only -- This is not allowed in a union or sub select
) as Hybrid

Because Fetch First X Rows is not allowed in subselects or with unions, this isn't going to work. Since we're limiting the output, it makes sense to limit the input.

I figure I can achieve a fake union by feeding my subselects into temp tables instead and returning that output instead.

In T-SQL, I can create a temp table on the fly with a query like this how would I do that in DB2?

Select *
Into #Temp
From TableA

How would I do that in DB2? Or would do I need to create the table before inserting data into it?


texts: How to quickly increase column size in Oracle?

texts:
In our Oracle 11 database we have a table with more that 100 million rows and we would like to increase size of one column (not used in any index). I tried to execute command

alter table shipment modify (description varchar2(21) );

but it takes to much time. Is there any other (faster) way to increase column size?


texts:
I'm using Oracle Client 12.1.0.2 and I need to generate client trace because im unable to connect my sql developer to the database server. However, i'm unable to generate the trace.

Here is my sqlnet.ora:

SQLNET.AUTHENTICATION_SERVICES = (NTS)
TRACE_LEVEL_CLIENT = 16
TRACE_FILE_CLIENT = client
TRACE_DIRECTORY_CLIENT = 'D:\app\client\product\12.1.0\client_1\network\admin'
TRACE_TIMESTAMP_CLIENT = ON
TRACE_UNIQUE_CLIENT = ON
DIAG_ADR_ENABLED=OFF

I have the rights to write in the folder but unable to generate the logs.

I tried the same with net manager also.

But no luck.


texts: Is there an easier way to show why this query is always equivalent to bitor?

texts:
This works, and I can't see right off the bat why:

CREATE OR REPLACE FUNCTION bitor ( x bigint, y bigint ) RETURNS bigint AS $body$
BEGIN
     RETURN x + y - bitand(x,y);
END;
$body$
LANGUAGE PLPGSQL
;

I have posted a couple things around SE for a more mathematical explanation, so I understand why it works logically now (at least in circuitry) but I was curious to post it here as it ultimately started as a query problem. Or maybe, the linked post is already the clearest way to show this?


texts: How can I auto-create a partition in a declarative partitioned table in Postgres when a newly created row has no matching partition?

texts:
My goal here is to make a partitioned table that's simplified below

Group
Info

A
blah1

B
blah2

but instead of having that as one table, I want to partition my table per group so that there's a table_1 for group 1, table_2 for group 2, etc. because my queries are all bound by group.
What I tried so far is creating a partitioned table like so
CREATE TABLE main_table (
  group TEXT
  blah JSONB
) PARTITION BY LIST(group);

I created my first manual partition like so
CREATE TABLE table_a PARTITION OF main_table FOR VALUES IN ('A');

I then created a function like so in the hope that I could set up a trigger on each row
CREATE FUNCTION add_node_partition() RETURNS TRIGGER AS $auto_add_node_partition$
BEGIN
EXECUTE format('CREATE TABLE IF NOT EXISTS nodes_org_%I PARTITION OF nodes_main FOR VALUES IN ($1)', NEW.org_id) USING NEW.org_id;
RETURN NEW;
END;
$auto_add_node_partition$ LANGUAGE plpgsql;

But the attempt to add a trigger on the main_table  like so
CREATE TRIGGER auto_add_node_partition BEFORE INSERT ON nodes_main FOR EACH ROW EXECUTE PROCEDURE add_node_partition();

failed because of the following error
ERROR:  "nodes_main" is a partitioned table
DETAIL:  Partitioned tables cannot have ROW triggers.

and I can't use a statement trigger because the only way to access the passed in rows is with transition tables that are only accessible after the operation (which will fail since there's no partition).
Does anyone know how to build this partitioned table system? Thank you so much!


texts: AWS - MySQL server "Service CPU Usage" stuck at 50% / being throttled?

texts:
I have a c4.large EC2 instance which is running Windows Server 2012 and MySQL.

When I look at the CPU usage on the machine it seems that MySQL is capping out at 50% CPU usage when the system is under load, it also looks like this is relative to some kind of throttling but I am not sure why this is happening.

The c4.large instance comes with 2 vCPUs and dedicated ECU and I can see from the screenshot below that these are both being used but maybe the combined total of this is being capped?

I am not sure if this is a MySQL issue or relative to Amazon Web Services like a potential systems issue or if it's some kind of misconfiguration on my side.

Does anyone have any idea why this is happening?


texts: Database mirroring - link failure between principal and witness

texts:
I am using synchronous database mirroring with a witness and am testing the various failure scenarios and the impact on the mirroring session, such as whether failover occurs.

Starting with the principal, mirror and witness all being connected I break the link between the principal and witness (using some hackery with the hosts file and tcpview)

           W
           |
 P---------M

The DMVs correctly report this configuration - the principal says the mirror is connected but witness disconnected, mirror says both principal and witness are connected. The witness reports the session as being out of sync.

Next I kill the sqlservr.exe process on the principal, so the connection between P and M terminates:

           W
           |
 P         M

I would now expect failover to the mirror server as both M and W agree that P is unavailable and there is witness-to-partner quorum. However what happens instead is the database on the mirror goes into the Mirror/Disconnected mode and there is no failover.

Can anyone explain this behaviour? I'm using SQL 2008 SP3 on WS 2008 R2 SP1.


texts: Expanding a dataset based on a weight field

texts:
I have a table that contains records in an un-expanded form. Each record has an associated integer weight that essentially informs us how many times the record should be replicated in order to get the true population.

Say, I have 3 records in my table. sampn ids a unique record and weight is the frequency weight. The un-expanded dataset looks like this:

sampn   weight  attrib1 attrib2 attrib3
    1       2       23      32      65
    2       1       32      56      75
    3       3       54      25      87

Once expanded, the dataset will be like this (note - I removed the weight field - but this is not essential):

sampn   attrib1 attrib2 attrib3
    1       23      32      65
    1       23      32      65
    2       32      56      75
    3       54      25      87
    3       54      25      87
    3       54      25      87

I have tried to do this using cursors but it is taking a really long time to execute. Is there a clever way to do this really fast? Any predefined T-SQL stored procedure that achieves this?

Update
All,
Thanks for the answers! Really great learning experience! Performed the expansion operation on my dataset. Paul's auxiliary table of numbers had the best execution time.


texts: Benefits of reorging a new table with loaded data?

texts:
Given the following scenario:
DB1:
EXPORT TO T.IXF OF IXF ... modified by codepage=... SELECT * FROM T;

DB2:
CREATE TABLE T (...) COMPRESS YES ADAPTIVE ORGANIZE BY ROW;
CREATE INDEX ... ON T (...) COMPRESS YES;
...
LOAD FROM T.IXF OF IXF INSERT INTO T (...);

Indexes are the same in DB1 and DB2, besides being compressed in DB2, and cluster ratio seems ok in DB1 (not sure if that matters). Code page differs between DB1 och DB2
Is there any point in doing a reorg after loading? I've found some articles from Z that seem to suggest that the way data is exported is of importance (since it where Z I did not read that carefully though so I don't know if that is relevant anymore on Z and/or LUW).
I tried on a fairly large number of tables, and did a REORGCHK after loading and everything seems fine. Comparing CALL GET_DBSIZE_INFO(?, ?, ?, 0) before and after REORG shows a slight decrease in size (<1%), but I don't know whether that is a coincidence.
Is there any particular situation where a REORG would be beneficial after loading into an empty table?


texts: Get the old version of stored procedure code in sql server

texts: We have modified one of our stored procedures code and accidentally committed it in production DB also. Now we need the older version of the code. Is there any way to get the old version of a stored procedure from the DB? We didn't take any backup of the code. Please help.


texts:
I'm not asking "where is my home directory", or "how do I find it". I am pretty sure I don't have an Oracle_Home directory and I want one. What is the minimum software package that I should install to get that directory? Or, what is the simplest way to set up the directory odbcad uses for tnsnames.ora?

Background:

I have a Windows 7 32bit computer with an assortment of random non-oracle software on it, which needs to establish an ODBC connection to an oracle database. We have other similar computers with established connections using instantclient_12_1. I was not involved in setting up those other computers.

I installed instanclient_12_1 on the subject computer, set up the ODBC connection in ODBCAD32, and then I got an error:

  Unable to connect SQLState=08004 [Oracle][ODBC][Ora]ORA-12154:
  TNS:could not resolve the connect identifier specified

OK. That's probably because I forgot to copy over the tnsnames.ora file to get this set up. So I copied my tnsnames.ora from one of the running computers, and then I realized that I don't know where the oracle home directory is on the target computer. I checked my environment variables, and I don't think I even have an oracle home directory. There must be some other software package I need to install to get this running. What am I missing?


texts: T-SQL View -- How to 'pre-fetch' schema using scalar function, then populate using table query

texts:
I have an application that uses T-SQL views. A number of these views are quite complex, joining data from many tables. To avoid column-name collisions, the views often employ table-scalar functions to return table data under a modified/prefixed schema.

For example, I have two tables (parent ACTION table and child ACTIONSUB table), both with many columns, and both with identical columns names --

ACTION

  RowGUID  UID         Ref  Name  LastUpdate  etc...

ACTIONSUB

  RowGUID  UID  fkUID  Ref  Name  LastUpdate  etc...

As part of our build process, we regenerate our library of table-access functions, one function per table. These functions can accept a delimited list of UID values, or will return all rows if passed NULL. As stated above, these functions' main purpose is to return all column data from the table with a modified/pre-fixed schema, so that we can JOIN them together into a single View. So, using our table-access functions, our tables above would return --

fn_TAF_ACTION(null):

  AC_RowGUID      AC_UID                    AC_Ref     AC_Name     etc...

fn_TAB_ACTIONSUB(null):

  ACSUB_RowGUID   ACSUB_UID   ACSUB_fkUID   ACSUB_Ref  ACSUB_Name  etc...

This approach works, until we have very large tables and/or join data from many tables (functions); then our View performance can seriously degrade.

Our views are simple query statements, usually with calculated columns added to the select list. A simple example --

CREATE VIEW vw_ActionSub2Action_01
AS
   SELECT
      acsub.*, ac.*,
      IsNull(ac.AC_Date1, ac.AC_Date2) AS [AC_ComboDate],
      ac.AC_Ref +'.'+ acsub.ACSUB_Ref AS [ComboRef], etc...
   FROM
      fn_TAF_ACTIONSUB(null) acsub
   LEFT JOIN
      fn_TAF_ACTION(null) ac ON acsub.ACSUB_fkUID = ac.AC_UID

I'm wondering if these Views could be re-written using CTEs, to somehow 'pre-fetch' the modified/pre-fixed schemas using the functions, and then query the tables with SELECT INTO to populate data into the View.

Is it possible?

Something like --

-- first, setup View schema using TAF functions to return empty data set
SELECT acsub.*, ac.*, '' AS [AC_ComboDate], '' AS [ComboRef], etc...
FROM fn_TAF_ACTIONSUB(0) acsub
LEFT JOIN fn_TAF_ACTION(0) ac ON acsub.ACSUB_fkUID = ac.AC_UID

-- then, populate the View by querying the tables
SELECT acsub.*, ac.*, IsNull(ac.Date1, ac.Date2) AS [AC_ComboDate], ac.Ref +'.'+ acsub.Ref AS [ComboRef], etc...
FROM ACTIONSUB acsub
LEFT JOIN ACTION ac ON acsub.fkUID = ac.UID

Is there a way to combine the above process into a single view definition?

PREFACE

I realize our above use of Views may be inherently non-performant; however, our system uses these Views, and performance is reasonable in most cases, so pushing a 're-design' of our processing logic is not feasible.

UPDATE 1

Our Table-Access-Functions (TAFs) are Table-valued Functions that return a table with a modified schema. They do not use BEGIN...END blocks. Here is an example...

CREATE FUNCTION [dbo].[fn_TAF_ACTION] (@UID varchar(max))
RETURNS TABLE
AS
RETURN (SELECT
  AC_RowGUID = RowGUID,
  AC_UID = UID,
  AC_Ref = Ref,
  AC_Name = Name,
  ...etc...
FROM Action
WHERE UID IN (SELECT IntValue FROM dbo.CsvToInt(@UID)) OR @UID IS NULL)
-- CsvToInt function simply allows us to pass a string of comma-delimited UIDs


texts:
I am exporting an excel file through SSIS using excel connection manager. I am using SSIS 2008 version and the excel is .xlsx

CREATE TABLE `Sheet1` (
    `Date` DATETIME,
    `NMMO` NVARCHAR(30),
    `P1 #` NVARCHAR(30),
    `P2 #` NVARCHAR(30)
)

but when i see the excel sheet, it gets created but the # symbol is replaced by . I have used P1 '# but it does not make any difference. Is there any way i can retain the column names in excel as it is? Please Help


texts: How to clear ad hoc queries from plan cache?

texts:
As the title implies, I'm going to remove just ad hoc queries (not prepared queries) from plan cache in sql server 2014/2016 because it occupies more than 50% percent of my main memory. Do you have any suggestion?

Many Thanks.


texts: Conecting to MySQL through a 'double tunnel'?

texts:
I am working on database servers in what seems to be a typical setup now-a-days: the database servers can only be accessed from a bastion server, and they don't accept connections on the relevant port (MySQL: 3306) from the bastion. I want to be able to use MySQL Workbench from my desktop, so I tried something like this:
$ ssh -t -L 9999:db1:9999 demo 'ssh -t -L 9999:localhost:3306 db1 ping -i 60 localhost'

The ping is just for creating activity on the connection, which is dropped if nothing goes on. My reasoning is, I want to connect my local port 9999 to port 9999 on db1, then connect 9999 on db1 to 3306 on localhost (=db1); I feel it ought to work, but it doesn't. It is possible that firewall rules don't allow this - or my commands may be wrong.
I can see the ping running on db1, so that part is OK, and root@localhost exists and has the right grants; however, when I try to connect to the database:
$ mysql -P 9999 -h localhost -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

If my command is wrong, what is my mistake? Or, if it is OK, is there any way to reach mysql from my desktop?


texts: How to do a find query on the result of another find query?

texts:
I'm working with MongoDB because the company where I work is doing a migration from pSQL. We have reports with filters: first the report lets you select a range of dates by year and month, then you can add filters by date/hour (in the range previously selected) or another fields. The problem is how to query the first the range of dates and then the secondary filters to get the results?

For example, this is the range to display the data:

db.report.find(
    {
        Fecha:{
            $gte: ISODate("2018-08-01T00:00:00Z"),
            $lte: ISODate("2018-08-31T23:59:59Z")
        }
    }
)

After this first filter, I need to filter again with $gte: ISODate("2018-08-02T11:00:00Z") and $lte: ISODate("2018-08-02T12:00:00Z")

as well as other filters, but the main problem is the date filter.

Mongo Version: 3.6 and we use Python 2.7 on the backend.


texts: Adding Secondary Data file in SQL Always on

texts: How to add the Secondary Data file to the Database which is a part of Always on availability Group in SQL Server 2012.


texts: For merge replication, do I need to schedule index maintenance on all sites or just publisher?

texts: I have a very fragmented database that uses merge replication with remote sites. Do I need to schedule index maintenance (reorganize / rebuild) for all sites? Or just on publisher copy?


texts: Indexed (Materialized) View for Parent and Newest Child

texts:
Say I have parent->child tables setup.  Call them Shipment and Widget.

Is there anyway to create an Indexed view that will have the PK of the parent (ShipmentId) and the created when of the newest child (Widget.CreatedWhen).

This is the query I am looking for:

SELECT  widg.ShipmentId, MAX(widg.CreatedWhen) NewestWidget
FROM    dbo.Widget widg
GROUP BY widg.ShipmentId

Obviously this query cannot be an Indexed View because it has MAX in it.

But is there some other way via "Indexed View Legal" SQL to get a similar result set?


texts: SQL Server 2019 Developer Edition on Ubuntu 18.05 Linux seems to only use one processor

texts:
I set up this db on an old server that has Ubuntu 18.04 in order to test some new features of SQL Server 2019.  First time running SQL Server on Linux.  I am running an intense query and happened to pull up htop on the Linux machine and noticed that only one processor was reaching 100% while the 24 others were showing idle.

I was under the impression that the developer version would use all available cores and that this was abstracted away.  Am I mistaken in believing developer should use all available processors or am I missing a setting here?


texts:
New to SQL server.
I have to truncate log on printing database as the server is almost out of space:(other stuff on server too)
I know I must :

USE master
GO
ALTER DATABASE DB_Name SET RECOVERY SIMPLE WITH NO_WAIT
GO
DBCC SHRINKFILE (TransactionLogFileName, 1)
GO
ALTER DATABASE DB_Name SET RECOVERY FULL WITH NO_WAIT
GO

My question is.
Can I do it while database in use? It's used all the time being the printer database.


texts: Can upgrade from MariaDB 5.5 to MariaDB 10.3 directly?

texts:
We have a database running in MariaDB 5.5.60 and we want to upgrade it to the last 10.3.7 version.

However I'm looking at MariaDB knowledge base ( https://mariadb.com/kb/en/library/upgrading/ ) and I'm not sure if we can perform a direct in-place upgrade from 5.5 to 10.3, or we should perform a "step by step" upgrade (5.5 > 10.0 > 10.1 > 10.2 > 10.3 ).

All the documentation I see in the oficial MariaDB knowledge base seems to suggest to do a in-place step by step upgrade (furthermore, looks like a logical upgrade with mysqldump cannot be permormed from 10.2 to 10.3), however I've seen some other examples in blogs where a direct in-place upgrade from 5.5 to 10.2 seems possible ( http://www.linuxtopic.com/2017/10/upgrade-mariadb-5.2-to-10.2.html ).

Any help?
Regards


texts: Limit the degree of parallelism (DOP) available to any query

texts:
On Oracle Exadata (11gR2), we have a relatively beefy database.

cpu_count is 24
parallel_server_instances is 2
parallel_threads_per_cpu is 2

We noted, through observation in Oracle Enterprise Manager (OEM), that performance was terrible due to queries being executed serially. To resolve this, all tables, materialized views and indexes were altered to take advantage of parallelism. eg:

ALTER TABLE SOME_TABLE PARALLEL (DEGREE DEFAULT INSTANCES DEFAULT);

The system was altered to turn on parallelisation:

ALTER SYSTEM SET PARALLEL_DEGREE_POLICY = 'AUTO';

This resulted in better performance but we occasionally observed in OEM that a single query would tie up a DOP of 96 (all of the available resource). This resulted in subsequent queries being downgraded to a DOP of 1 (no parallelisation). Resulting in poor performance until the hogging query completed.

To resolve this we tried to limit the DOP available to any query with:

ALTER SYSTEM SET PARALLEL_DEGREE_LIMIT = 24;

This had no effect. We frequently observe queries that will use more than the limit (generally 48 or 96, but no real pattern).

How do we prevent any single query from hogging all of the available resource?


texts: Is possible to insert an array inside another array in postgresql?

texts: Let's say I have a table in postgresql called 'Test' which contains a single column called 'Column1' of the type int[]. So, it can contain an array of integers for each row, for example, {1,2,3}. I want to know if it is possible to insert an array of arrays in a table in postgresql, like {{1,2,3},{1,3,5},{9,10}} in a single cell of a table in postgresql? If so then what should be the datatype of the column, and how can we achieve that?


texts: How can I compare the number of transactions in the first weeks or months of different stores

texts:
I need to make a comparison of the number of transactions per week and month of different stores since its opening date. The problem is that the stores has a different opening date.

So, The idea is to compare the number of transactions in the first weeks or months

That's what I have:

 Canal Key 1

   Recount FactTransactions

                              Month1-2015 Month2-2015 Month3-2015 Month4-2015 Month5-2015 Month6-2015

 Type of store     Cod Store

Spain
         2          Store 1                                              5         6        10
                    Store 2      10          20            40            50        60       85
         4
                    Store 3      31          45            100           315       441      625
                    Store 4                                10            20        32       45
Portugal
        1
                    Store 5                                                                 12

That's what I want to obtain (comparing different stores since its opening date):

    Canal Key 1

   Recount FactTransactions

                              Month1 (or Week) Month2 Month3 Month4 Month5 Month6

 Type of store     Cod Store

Spain
         2          Store 1       5              6      10
                    Store 2       10             20     40     50     60       85
         4
                    Store 3       31             45     100    315    441      625
                    Store 4       10             20      32    45
Portugal
        1
                    Store 5                                                                 12

That's my data model:

 FacTransaccion: *Measures* Recount FactTransaction / Amount / Amount discount / Unit Price

 DimDate: *Hierachy* Date (YYYYMMDD) / Month / Month Name / Week of Month / Week of year / Year (It's the same Dimdate than AdventureWorksDW2008 with its member calculation and relationship)

 DimProduct: *Hierachy* Product Key

 DimStore: *Hierachy* Type of store / Store key / Opening date / Country

 DimType: *Hierachy* Type of transaction

 DimCanal: *Hierachy* Canal  (there are two channel where people can buy)

Reading a lot about MDX, after a thousand times of MDX. I don't have anything.

Several attemps:

Attempt #1:

SELECT CROSSJOIN({TAIL([Dim Store].[Opening date].Children, 30) }, { [Measures].[Transactiones] }) ON COLUMNS
    ,{ [Dim Store].[Store names].Children} ON ROWS FROM [CUBE]

Attempt #2:  (It does not work) It seems to me better than first attempt.

WITH MEMBER [Measures].[Count] AS
SUM([Dim Store].[Name Store].currentmember*Tail([Dim Store].[Opening Date].children,1),
[Measures].[Count Fact Transacciones])
SELECT {[Measures].[Count]} ON COLUMNS,
{[Dim Store].[Name Store].[All]} ON ROWS
from [CUBE]


texts: How to find percentage data change after last full backup?

texts:
I am trying to find the data change rate for all databases on a server. In the past, I used Paul Randal's script How much of the database has changed since the last full backup which worked great. Except for databases that have a '-' (hyphen) in the name.

I understand that using '-' in a database name is not really a good practice, but an application creates the databases that way.

Also looked into Paul Randal's another script  to trend growth of the databases from backups, but that's not something that will work for me.

Was able to calculate the average growth for data files over a period of time, but I am interested in the actual amount/percentage of data that got changed after the last full backup OR in the last one day/week etc..

Any ideas how to modify Paul Randal's stored procedure to work with hyphens in the database name? Or is something like this even possible?

Please suggest if there are any other options available.


texts: Evaluating most frequent values of columns in a single query

texts:
I have a table describing my application users with some details such as name, surname, birth date, nationality, email, etc...

I would like to known for each property, and for each user category, the most frequent value and the percentage of occurence.

For instance :

create table test ( userId int identity(1,1),
                    categoryId int,
                    name varchar(50),
                    surname varchar(50))

insert into test(categoryId, name, surname)
values  (1, 'John', 'Locke'),
        (1, 'John', 'Millar'),
        (1, 'James', 'Mill'),
        (1, 'John Stuart', 'Mill'),
        (2, 'Thomas', 'Bayes'),
        (2, 'Laurent', 'Schwartz'),
        (2, 'Herrmann Amandus', 'Schwartz'),
        (2, 'Thomas', 'Simpson'),
        (2, 'Leonhard', 'Euler')

Result should be:

+------------+-------+--------+---------+----------+------------+
| categoryId | total |  name  | namePct | surname  | surnamePct |
+------------+-------+--------+---------+----------+------------+
|          1 |     4 | John   |    0.50 | Mill     |       0.50 |
|          2 |     5 | Thomas |    0.40 | Schwartz |       0.40 |
+------------+-------+--------+---------+----------+------------+

For this simple example, I can figure how I can achieve that, with a query like:

select  t.categoryId,
        t.total,
        n.name,
        1. * n.total / t.total as namePct,
        sn.surname,
        1. * sn.total / t.total as surnamePct
from (
    select categoryId, count(*) as total
    from test
    group by categoryId
    ) t
join (
        select categoryId, name, total
        from (
            select categoryId, name, total, row_number() over(partition by categoryId order by total desc) as rn
            from (
                select categoryId, name, count(*) as total
                from test
                group by categoryId, name
                ) t
            ) t
        where rn = 1
        ) n on t.categoryId = n.categoryId
join (
        select categoryId, surname, total
        from (
            select categoryId, surname, total, row_number() over(partition by categoryId order by total desc) as rn
            from (
                select categoryId, surname, count(*) as total
                from test
                group by categoryId, surname
                ) t
            ) t
        where rn = 1
        ) sn on t.categoryId = sn.categoryId

But, in my real use case my table has millions of rows, hundreds of categories and a dozen of attributes.

Is there a way to make the query simpler and more efficient (ie. without a bunch of sub select for each attribute) ?

I'm currently using SQL Server 2008, but answers using more recent versions are welcome.


texts: Performance of query against column level dependencies much faster when using deprecated system view

texts:
Consider these two queries that get the names of triggers dependant on the JV_VoyageFlight column in table dbo.JobVoyage.

the first uses the 'old' mechanism that is deprecated (sys.sql_dependencies).

SELECT
    depObj.name
FROM
    sys.sql_dependencies dep
    INNER JOIN sys.tables tab ON tab.object_id = dep.referenced_major_id
    INNER JOIN sys.schemas sch ON sch.schema_id = tab.schema_id
    INNER JOIN sys.columns col ON col.object_id = dep.referenced_major_id AND col.column_id = dep.referenced_minor_id
    INNER JOIN sys.objects depObj ON depObj.object_id = dep.object_id
WHERE
    sch.name = 'dbo'
    AND tab.name = 'JobVoyage'
    AND col.name = 'JV_VoyageFlight'
    AND depObj.type = 'TR';

The suggested replacement view is sys.sql_expression_dependencies but this does not contain column level dependencies for non schema bound objects and the documentation advises to use the sys.dm_sql_referenced_entities function instead for that.

The second query uses the new mechanism.

select distinct name from sys.triggers
cross apply sys.dm_sql_referenced_entities('dbo.' + object_name(object_id), 'object')
where
    parent_Class = 1
    and Is_Ms_Shipped = 0
    and referenced_entity_Name = 'JobVoyage'
    and referenced_minor_name = 'JV_VoyageFlight'

One takes 3 milliseconds. One takes 3000 milliseconds.
Does anyone have a better version of this query to get dependencies out of triggers without using deprecated functionality?


texts: Can Oracle TimesTen (or 12c's In-Memory) benefit from GPU?

texts:
I have looked into TimesTen and In-Memory recently. They claim they use SIMD (Single instruction, multiple data) to speed up queries. We use HP DL380 G9 Servers for our DB. Since This series are limited to the Intel Core architecture, It can't benefit from more than 64 CPU cores (32 physical w/ hyperthreading) which Linux kernel 4 (Oracle Linux 7) can provide, opposite to the AMD64's HA which scales fine.

The question is: Can Oracle utilize some sort of GPU parallelism on the box, as OpenCL2 allows shared memory architecture? Is there a way to define GPU cores or compute units as processing units for Oracle?


texts: Can't Restore Database Because of Rogue Log File

texts:
Someone, somehow, made a database disappear from SQL Server 2008 R2. I'm assuming it was dropped, because it doesn't show in SSMS in the Object Explorer's list of databases. Let's call the database "Rogue". Now, there is a rogue log file, "Rogue_log.ldf" on the server's file system. The file cannot be deleted via Explorer.

Here is the restore script. Note that I tried to run it without "MOVE" first, and then got the error further below in this post. Yes, it's "Logs" and not "Log" in the path (don't ask).

RESTORE DATABASE Rogue
FROM DISK = 'E:\Rogue.bak'
WITH MOVE 'Rogue' TO 'E:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Rogue.mdf',
MOVE 'Rogue_log' TO 'D:\MSSQL10_50.MSSQLSERVER\MSSQL\Logs\Rogue_log.ldf',
RECOVERY, REPLACE
GO

Trying to run the script is giving the following error:

  Msg 3634, Level 16, State 1, Line 1 The operating system returned the
  error '5(Access is denied.)' while attempting
  'RestoreContainer::ValidateTargetForCreation' on
  'D:\MSSQL10_50.MSSQLSERVER\MSSQL\Logs\Rogue_log.ldf'. Msg 3156, Level
  16, State 8, Line 1 File 'RGA_log' cannot be restored to
  'D:\MSSQL10_50.MSSQLSERVER\MSSQL\Logs\Rogue_log.ldf'. Use WITH MOVE to
  identify a valid location for the file. Msg 3119, Level 16, State 1,
  Line 1 Problems were identified while planning for the RESTORE
  statement. Previous messages provide details. Msg 3013, Level 16,
  State 1, Line 1 RESTORE DATABASE is terminating abnormally.

I can restore a backup from Rogue.bak if I change the log file name to Rogue1_log.ldf (Star Wars puns NOT intended.) Can anyone help me figure out what's going on with this rogue Rogue_log.ldf on the server file system?


texts: PostgreSQL list of integers separated by comma or integer array for performance?

texts:
As per subject, I have a field which is varchar in which i store some numbers separated by commas. The list grows and shrinks based on clients choices. I then use it in queries to check if a particular value is in that list against

value || ','

It is working well but was thinking of converting it to an integer array.

Would this be a better solution both in the technical/performance way and to properly program the database?


texts: Have foreign key constraint and index key for the same column(s)

texts:
I am creating a composite index key for 2 columns and then adding foreign keys to its appropriate table. Do I need to create a multiple index key reference and then create separate foreign keys? My example of how I have achieved it is below:

CREATE TABLE IF NOT EXISTS customers_appointments (
appointment_id int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
merchant_id int(11) NOT NULL,
supplier_id int(11) NOT NULL,
appointment_date datetime NOT NULL,
appointment_description mediumtext NOT NULL,
appointment_confirmed tinyint(1) NOT NULL DEFAULT '0',
appointment_confirmed_date datetime NOT NULL,
date_created datetime NOT NULL
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

ALTER TABLE customers_appointments
 ADD KEY appointments_index (merchant_id,supplier_id),
 ADD KEY fk_merchant_appointment (merchant_id),
 ADD KEY fk_supplier_appointment (supplier_id);

ALTER TABLE customers_appointments
  ADD CONSTRAINT fk_merchant_appointment
    FOREIGN KEY (merchant_id) REFERENCES merchants (merchant_id)
    ON DELETE CASCADE,
  ADD CONSTRAINT fk_supplier_appointment
    FOREIGN KEY (supplier_id) REFERENCES suppliers (supplier_id)
    ON DELETE CASCADE;

This is working for me but I just need to know from an expert that in terms of performance and how it is designed is correct?


texts: Magic number in optimal redo size query in Oracle 9i

texts:
I got this query from this blog post to determine the optimal online redo log size in pre-Oracle10 databases.

I'm stuck with Oracle 9i for that particular instannce for reasons beyond my pay grade and in this version the view V$INSTANCE_RECOVERY lacks the OPTIMAL_LOGFILE_SIZE column):

SELECT
      (SELECT ROUND(AVG(BYTES) / 1024 / 1024, 2) FROM V$LOG) AS "Redo size (MB)",
      ROUND((20 / AVERAGE_PERIOD) * (SELECT AVG(BYTES) FROM V$LOG) / 1024 / 1024, 2) AS "Recommended Size (MB)"
FROM
     (
                SELECT AVG((NEXT_TIME - FIRST_TIME) * 24 * 60) AS AVERAGE_PERIOD
                FROM V$ARCHIVED_LOG
                WHERE FIRST_TIME > SYSDATE - 3
                AND TO_CHAR(FIRST_TIME, 'HH24:MI') BETWEEN
                '00:00' AND '23:59'
      );

I indented the query to understand in better but I fail to understand why the author uses the magic number 20.

Can someone tell me why the 20 and whether this query can be otherwise improved?


texts: Mongodb Cross-region failover replication aws

texts:
I have deployed 3 MongoDB nodes(node-1, node-2, node-3) in AWS. The first two nodes (node-1, node-2) are deployed in Virginia region and the third node(node-3) is deployed in Oregon region and enabled VPC peering and the replication is working fine.

Current Scenario ::

Virginia : Node-1 (Primary) and Node-2 (Secondary)

Oregon: Node-3 (Secondary)

Failover Scenario::

if the Virginia region goes down,

1. Both the Node-1 and Node-2 will also be down.
2. Third node (Node-3) has to be Primary

And the Virginia region comes back,

1. Node-1 and Node-2 has to be secondary
2. Node-3 has to continue being primary

But, Now if the Virginia goes down the third node(node-3) is still in Secondary. I have to manually make the node as Primary. Is there any automated way or alternate solution to do this.


texts:
I'm new to postgresql. I wanted to know, how we resolve 1 to many and many to many relationships in postgresql considering that we want to make our schema pure object-relational rather than conventional RDBMS.

For instance, in sql 1999 we have multisets, REF, un-nesting etc., what is it's replacement in postgresql.

CREATE TYPE PersonType AS (
Name CHAR(50),
Id CHAR(9),
PRIMARY KEY (Id)
)
CREATE TYPE BuyerType UNDER PersonType AS (
Transactions REF( TransactionType)
MULTISET SCOPE Transaction
)
CREATE TYPE SellerType UNDER PersonType AS (
Transactions REF( TransactionType)
MULTISET SCOPE Transaction
)
CREATE TYPE TransactionType AS (
Id CHAR(10),
Date date,
Buyer REF( BuyerType) SCOPE Buyer,
Seller REF( SellerType) SCOPE Seller
)
CREATE TABLE Person of PersonType;
CREATE TABLE Buyer of BuyerType;
CREATE TABLE Seller of SellerType;
CREATE TABLE Transaction of TransactionType;

Now, we can run this query

SELECT S.Id, S.Name, count(DISTINCT T->Buyer->Id)
FROM Seller S, UNNEST(S.Transactions) T
GROUP BY S.Id, S.Name

Consider the above example for designing a schema in SQL 1999/2003
How would to create similar schema when it comes to postgresql. Can we write such formulated queries in postgresql.


texts: PL/SQL: Selecting multiple values from the same column

texts:
We have a table in which all kinds of different Words are stored with a unique id each. In other tables, certain fields which should contain words instead only contain the id of these words. Let's look at an example.
This is our example "words" table:
+---------+--------+
| word_id |   word |
+---------+--------+
|       1 |  Apple |
|       2 |  Melon |
|       3 |    Car |
|       4 |   Boat |
+---------+--------+

In another table (example: "rows"), there are references to that table:
+--------+----------+------------+
| row_id | fruit_id | vehicle_id |
+--------+----------+------------+
|      1 |        1 |          4 |
|      2 |        2 |          3 |
|      3 |        1 |          3 |
|      4 |        2 |          4 |
+--------+----------+------------+

For my SSRS Report, I need to select many rows of the "words" table and insert the value of the "word" column into the report. For now I have solved it by creating a different SQL query for each field using report parameters to select the id, but I was wondering if I could make the report more efficient by cutting down the number of SQL queries.
How I did it for now using our example:
SELECT
    rows.id,
    words.word AS FRUIT
FROM
    rows
INNER JOIN
    words ON
    rows.fruit_id = words.word_id
WHERE
    words.word_id = :FRUIT_ID
----------------------------------------
SELECT
    rows.id,
    words.word AS VEHICLE
FROM
    rows
INNER JOIN
    words ON
    rows.vehicle_id = words.word_id
WHERE
    words.word_id = :VEHICLE_ID

The ideal output for row = 1 would be:
+--------+-------+---------+
| row_id | fruit | vehicle |
+--------+-------+---------+
|      1 | Apple |    Boat |
+--------+-------+---------+

Sadly, I have not found a way to select multiple values of the same column in just one SQL statement.
I would be very grateful for any ideas or solutions, Thanks!
EDIT: I know the "words" table setup is not ideal and can have its own problems, however, I cannot do anything about them since the database is to be used as-is and I am not allowed to change anything.


texts: Gaps and islands with GPS breadcrumb data

texts:
I've got a gaps and islands (GAI) problem that I can't seem to crack to save my life.  Most of the GAI problems I've seen involve data in the form of:

[Id] [StartDateTime] [EndDateTime]

But mine in this case comes from GPS breadcrumbs/data points, so we don't have a start and end, just a timestamp.

What I want to do is the following:

Given a dataset that contains a truck number, a location, and a datetime, if a row shares a truck, and location and is within 1 hour of another row, then join together.

Trucks can run 24/7 (team drivers).

My dummy data would look like this (the duplicate rows are intentional as they are a feature of the real data):

DECLARE @TruckLoc TABLE
(
    TruckId VARCHAR(50),
    LocationName VARCHAR(12),
    LocDateTime DATETIME
)
;

INSERT INTO @TruckLoc (TruckId, LocationName, LocDateTime)
VALUES
('MonsterTruck', 'Home', '2019-10-22 10:27:40.000'),
('MonsterTruck', 'Home', '2019-10-22 10:27:40.000'),
('MonsterTruck', 'Home', '2019-10-22 10:33:10.000'),
('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'),
('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'),
('MonsterTruck', 'Home', '2019-10-22 11:22:40.000'),
('MonsterTruck', 'Gas Station', '2019-10-22 12:43:00.000'),
('MonsterTruck', 'Gas Station', '2019-10-22 13:13:00.000'),
('MonsterTruck', 'Gas Station', '2019-10-22 13:43:00.000'),
('MonsterTruck', 'Home', '2019-10-22 16:43:00.000'),
('MonsterTruck', 'Home', '2019-10-22 16:49:00.000'),
('MonsterTruck', 'Home', '2019-10-22 17:43:00.000'),
('MonsterTruck', 'Home', '2019-10-22 20:43:00.000'),
('MonsterTruck', 'Home', '2019-10-22 20:56:00.000')

The output that I'm attempting to get is the following:

Monster Truck | Home | 2019-10-22 12:57:40.000 | 2019-10-22 11:22:40.000
Monster Truck | Gas Station | 2019-10-22 12:43:00.000 | 2019-10-22 13:43:00.000
Monster Truck | Home | 2019-10-22 16:43:00.000 | 2019-10-22 17:43:00.000
Monster Truck | Home | 2019-10-22 20:43:00.000 | 2019-10-22 20:56:00.000

What I've tried:

I've tried everything.  In my real statement, I have them ranked by truck and datetime.  But that's the furthest success I've had.  I've gone up to try to use LAG/JOIN functions and CTEs but the crux of the problem is that I don't need to look at the previous record, but I need to look at all the previous records that satisfy a condition concerning other previous records i.e each record is within an hour of the preceding one.

Any help on this is greatly appreciated.


texts:
I would like to extract all the rows from a table until and including check_type = 'schedule' and checked_at < '2016-09-26', in this situation I don't have a lower bound for checked_at, the lower bound could be the checked_at value of the first row which has the type scheduled, but I don't see how I can get that.

CREATE TABLE project_check_history (
  id serial PRIMARY KEY,
  project_id int4,
  check_type varchar,
  checked_at timestamp
);

INSERT INTO project_check_history(project_id, check_type, checked_at)
  VALUES
      (1, 'instant', '2016-09-26'),
      (1, 'instant', '2016-09-25'),
      (1, 'scheduled', '2016-09-24'),
      (1, 'instant', '2016-09-23'),
      (1, 'scheduled', '2016-09-23');
      (1, 'instant', '2016-09-22'),
      (1, 'scheduled', '2016-09-21');

This is part of a bigger question here, but I'm really stuck, and don't see how I can do it.

Expected output it will be:

checked_at
2016-09-26
2016-09-25
2016-09-24


texts: SSIS Package truncation and possible data loss (error code: 0xC020901C)

texts:
Good morning all,
It's been 2 days now trying to figure out how to solve this issue, still no answer. Maybe you can help me out.
Here is the thing. I have created an SSIS package to import data from another database server; here are the specifications of both servers:
I am using Sql Server Data Tools 2012

Source: SQL Server 2008 r2
Connection Type: OLE DB

Destination: SQL Server 2012
Connection Type: OLE DB

The package is an Import and Export package, having 2 tasks, a "Data FLow Task" and an "Execute SQL Task". The source is an SQL Command, with a simple select from a couple of joins, nothing that special. the Destination is Table in the destination server. Of course we have a Data Conversion task in between.
The package was working fine until we have added a new column to the Selection, of type varchar(50). I modified the destination Table with same, only i used NVARCHAR(50), and added the data conversion as UNICODE string [DT_WSTR] of length 50.

After this was done, I am receiving the error:

  Error: 0xC020901C at [Package], Source - Query [127]: There was an
  error with Source
  - Query.Outputs[OLE DB Source Output].Columns[COL] on Source
  - Query.Outputs[OLE DB Source Output]. The column status returned was:  "The value could not be converted because of a potential loss of
  data.".

I have tried to increase the sizes of the destination columns from 50 to 100, still the same error is happening. Weird thing is when I replace Col with NULL in the source query, I get the package to run perfectly.

Any suggestions?


texts:
I have FROM and TO date parameters. I need to assign default values for them.

For example, if we run the report today (12 September), the default values for FROM and TO should be 10/01/2016 and 03/31/2017. as the current month (9) satisfies in the second condition below:

Based on the report run date (current date):

IF Month                       then From Date               and To Date
>=1 and <=3                    4/1/prior yr                 9/30/prior yr
>=4 and <= 9                   10/1/prior yr                3/31/curr yr
>=10 and <=12                  4/1/curr yr                  9/30/curr yr

Could you tell me the SSRS expression?


texts:
Why am I getting:

ERROR: syntax error at or near "'SELECT '"

In:

CREATE OR REPLACE FUNCTION GetListings (lon double precision, lat double precision, schemas name[])
RETURNS TABLE(
  schema VARCHAR(128),
  id BIGINT,
  product_name VARCHAR(128),
  product_type SMALLINT,
  product_subtype SMALLINT,
  product_units SMALLINT,
  product_info JSONB,
  product_image VARCHAR(256),
  is_featured BOOLEAN,
  is_special BOOLEAN,
  is_alert BOOLEAN,
  distributor_name VARCHAR(128),
  distributor_image VARCHAR(256)
) AS $$
DECLARE
  sch name;
  n text;
  t text;
BEGIN

  n := to_char(lon,'999.999999999999999');
  t := to_char(lat,'999.999999999999999');

  FOREACH sch IN ARRAY schemas
  LOOP
    RETURN QUERY 'SELECT ''' || sch || ''', product_name, product_type, product_subtype, product_units, product_info, product_image, is_featured, is_special, is_alert, distributor_name, distributor_image FROM ' || quote_ident(sch) || '.listing WHERE ST_Contains(area, ST_SetSrid(ST_MakePoint(' || n || ', ' || t || '), 4326))';
  END LOOP;

  RETURN;

END;
$$ LANGUAGE plpgsql STRICT;


texts: How to enable remote access to wamp mysql

texts:
I have wamp server which is installed in windows server. Since database is becoming large want to move code to other machine from there I want logon to wamp mysql.

I have disabled bind-to-address and created new user with all permission, but unfortunately not able to connect mysql remotely.

Steps I followed:

CREATE USER 'user'@'x.x.x.x' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'user'@'x.x.x.x';

Please let me what is steps do I need to follow.