texts: index by UDF not used when filtering by subquery column

texts:
I have created an index with an expression that calls a user defined function.
I expected that when a subquery selects this expression as a column I could filter by this column and have my index used. However, this works only for built-in functions like LOWER().

The reason why I'm filtering subquery columns is that due to our ORM engine I have to use a subset of SQL that does not allow using UDFs in queries, only the column names and string constants. So I have to create a view that calls my UDF internally.

See below the script that reproduces the problem in the last statement: it has Subquery Scan instead of Index Scan.

Is it possible to tweak the server the index or the function to be treated like a built-in function in this scenario?

select version();
                                                 version
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)

drop table if exists tab1;
DROP TABLE
create table tab1 ( r_object_id char(16) primary key, dcmi_title varchar(250) );
CREATE TABLE
create index tab1__dcmi_title on tab1 ( lower(dcmi_title) );
CREATE INDEX
create or replace function custom_lower(s text) RETURNS text as $$
  SELECT LOWER(s);
$$ LANGUAGE sql;
CREATE FUNCTION
drop table if exists tab2;
DROP TABLE
create table tab2 ( r_object_id char(16) primary key, dcmi_title varchar(250) );
CREATE TABLE
create index tab2__dcmi_title on tab2 ( custom_lower(dcmi_title) );
CREATE INDEX
explain SELECT * FROM ( SELECT r_object_id, lower(dcmi_title) s_dcmi_title from tab1 ) t WHERE s_dcmi_title = 'all aboard 1 poster pack'
;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using tab1__dcmi_title on tab1  (cost=0.14..8.16 rows=1 width=100)
   Index Cond: (lower((dcmi_title)::text) = 'all aboard 1 poster pack'::text)
(2 rows)

explain SELECT * FROM ( SELECT r_object_id, lower(dcmi_title) calculated_col from tab1 WHERE lower(dcmi_title) = 'all aboard 1 poster pack' ) t
;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using tab1__dcmi_title on tab1  (cost=0.14..8.16 rows=1 width=100)
   Index Cond: (lower((dcmi_title)::text) = 'all aboard 1 poster pack'::text)
(2 rows)

explain SELECT * FROM ( SELECT r_object_id, custom_lower(dcmi_title) calculated_col from tab2 WHERE custom_lower(dcmi_title) = 'all aboard 1 poster pack' ) t
;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using tab2__dcmi_title on tab2  (cost=0.14..8.16 rows=1 width=100)
   Index Cond: (lower((dcmi_title)::text) = 'all aboard 1 poster pack'::text)
(2 rows)

explain SELECT * FROM ( SELECT r_object_id, custom_lower(dcmi_title) s_dcmi_title from tab2 ) t WHERE s_dcmi_title = 'all aboard 1 poster pack'
;
                          QUERY PLAN
---------------------------------------------------------------
 Subquery Scan on t  (cost=0.00..13.25 rows=1 width=100)
   Filter: (t.s_dcmi_title = 'all aboard 1 poster pack'::text)
   ->  Seq Scan on tab2  (cost=0.00..11.62 rows=130 width=100)
(3 rows)


texts: Insert a 'NULL' string in a table in PostgreSQL

texts:
I would like to know if it is possible to insert a 'null' string in a column which has a NOT NULL constraint.
I'm using PostgreSQL, although this probably applies to other database management systems as well.
The query would look like the below
INSERT INTO originators(originator, id, regexp)
VALUES ('null', 1, -1);

This is the table design:
CREATE TABLE originators
(
      originator    varchar(30)    NOT NULL
    , id            int            NOT NULL
    , regexp        int            NOT NULL
);

Here the originator column has a NOT NULL constraint, so there should be no NULL values in the table.
However, when I query the table, I'm seeing this:
╔════════════╦════╦════════╗
║ originator ║ id ║ regexp ║
╠════════════╬════╬════════╣
║ NULL       ║  1 ║     -1 ║
╚════════════╩════╩════════╝
How is this possible?


texts: Next step in progression from 3NF to BCNF

texts:
I am working with relation H that is detailed below:

H = {A,B,C,D}

It shows the following functional dependencies (FD[s]):

F = { A -> B, B -> C, {C,D} -> A}

The candidate keys are

{A,D}, {B,D}, {C,D}

I belive relation H is in 3NF.

I also believe there are two FD that prevent this relation from satisfying BCNF:

A -> B and B -> C

Then I have:

A -> B
H1 = {A,B}  F1 = {A -> B} <- this is BCNF
H2 = {A,C,D} F2 = {A -> C, {C,D} -> A} <- this isnt' BCNF

B -> C
H11 = {B,C} F11 = {B -> C} <- this is BCNF
H12 = {A,B,D} F12 = {A -> B} <- this is in BCNF

The questions

I have partial information about this algorithm, so I am not sure about certain things:

What should I do with H2? Just throw it away and say that H1, H11, H12 are decompositions of H to meet BCFN?
Can this relation be converted to BCNF with functional dependency preservation? (I would say no! because it is impossible to derive the FD {C,D} -> A from F1 sum F11 sum F12. Can you confirm that?


texts: Synchronize creation of rows between tables

texts: Lets say I have two tables, posts(id_pk,title) and posts_content(post_id_pk,blob). Whenever I create a row in posts, I want a complimentary row to be created in posts_content, with the same id. If I just use auto_increment on both, when two posts are created simultaneously, they might get switched. What is the correct way to do this?


texts: Mysql/InnoDB says it doesn't have access rights to create temp tables, but it does

texts:
For mysql  Ver 14.14 Distrib 5.6.33, for Linux (x86_64) using  EditLine wrapper

in my.cnf:

tmpdir          = /var/mysql_tmpfs

in /etc/apparmor.d/usr.sbin.mysqld:

  /var/mysql_tmpfs/ rw,
  /var/mysql_tmpfs/* rw,

Permissions for folder:

drwxrwxrwt  2 mysql    mysql     4096 Nov 28 19:46 mysql_tmpfs

Which, looks right to me. However, it is failing. Right now, mysql will start. However, when I load certain pages of my application, this is the error message that I see:

2016-11-28 20:23:41 39465 [ERROR] InnoDB: Unable to lock /var/mysql_tmpfs/#sql9a29_2_0.ibd, error: 13
2016-11-28 20:23:41 39465 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2016-11-28 20:23:41 7fd2105d3700  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
2016-11-28 20:23:41 39465 [ERROR] InnoDB: Cannot create file '/var/mysql_tmpfs/#sql9a29_2_0.ibd'

However, I do not believe there is another process running. When I do a ps aux | grep mysqld, I get this:

mysql    38943  0.0  0.0   4396   736 pts/4    S    20:21   0:00 /bin/sh /usr/bin/mysqld_safe
mysql    39465 10.0  4.0 73330944 5301532 pts/4 Sl  20:21   0:16 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --log-error=/var/log/mysql/error.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306

In addition, I was able to create a file in that directory as the mysql user, which is why I'm extra sure that the user should have the required permissions.

I'm guessing this is something that I'm missing, but I've now had three other people take a look and we have not gotten anywhere. Please help.


texts: Most efficient way to sort data fields into SQL

texts:
I'm trying to decide on the most efficient way to sort various data values. Here's how the data arrives:
Device X sends a text string "name=value&name2=value&name=value"

On arrival that string is stuffed into a sql row along with the unique address of the sending device.
This keeps data flowing in easily to a SQLite database.

My parsing script first gets all unique device addresses. Those are put in a hash for the parser and inserted into a new database. (the hash contains the rowid from the db after the insert.) (with more logic to keep race conditions out of the mix)

Then each row of string data is split up by the &, then by the =.

Here's the general table layout:

rawData(address, string, timestamp, processed)

Each row is read from the rawData, sorted and marked as processed. (makes it easy to muck around with the parsing script)

Data is placed into these:

devices(rowid, address)
dataNames(rowid, devicesid, name)
dataValues(nameId, value, timestamp)

I'm trying to decide on the most efficient method of inserting this data. It ends up as thousands of rows. I supposed I could keep a hash of known device/name pairs. Then if the hash doesn't know about a new name I can go ahead and insert it and refresh the hash...

Am I missing something totally obvious? The goal is to keep selects to a minimum for efficiency!


texts: not a GROUP BY expression error when using a function in a query

texts:
I use a function in a query to select data but I have this error:

  not a GROUP BY expression

This is the query:

SELECT          PROF_EMPLOYEE_DATA.EMP_CODE, PROF_EMPLOYEE_DATA.EMP_NAME_AR, PROF_TREE_DETAILS.TREE_NAME, PROF_JOBS_DETAILS.JOB_NAME, PROF_EMPLOYEE_DATA.GROUP_START_DATE,
                         COUNT(DISTINCT ATT_EMPLOYEEATTENDANCE.ATTEND_DATE) AS WORKSDAYS
                         ,nvl(getEmployee_OffDays ('" + dtp_PrintManth.Text + "',PROF_EMPLOYEE_DATA.SHIFT_ID),0) - GETEMPLOYEE_OFFDAYSWork(extract(month from to_date('" + dtp_PrintManth.Text + "','dd/mm/yyyy')),extract(year from to_date('" + dtp_PrintManth.Text + "','dd/mm/yyyy')),PROF_EMPLOYEE_DATA.EMPLOYEE_ID)as OffDays
FROM            PROF_EMPLOYEE_DATA INNER JOIN
                         PROF_TREE_DETAILS ON PROF_EMPLOYEE_DATA.TREE_ID = PROF_TREE_DETAILS.TREE_ID INNER JOIN
                         PROF_JOBS_DETAILS ON PROF_EMPLOYEE_DATA.JOB_ID = PROF_JOBS_DETAILS.JOB_ID LEFT OUTER JOIN
                         ATT_EMPLOYEEATTENDANCE ON PROF_EMPLOYEE_DATA.EMPLOYEE_ID = ATT_EMPLOYEEATTENDANCE.EMPLOYEE_ID
WHERE        (PROF_EMPLOYEE_DATA.EMP_CODE = '12411') AND (ATT_EMPLOYEEATTENDANCE.ATTEND_DATE BETWEEN TO_DATE('10/1/2016', 'MM/DD/YYYY') AND TO_DATE('10/31/2016', 'MM/DD/YYYY'))
GROUP BY PROF_EMPLOYEE_DATA.EMP_CODE, PROF_EMPLOYEE_DATA.EMP_NAME_AR, PROF_TREE_DETAILS.TREE_NAME, PROF_JOBS_DETAILS.JOB_NAME, PROF_EMPLOYEE_DATA.GROUP_START_DATE

And this is the function that I created to display days number in month:

create or replace
FUNCTION "GETEMPLOYEE_OFFDAYS" (month_p nvarchar2,shift_id_p number)
  RETURN decimal
  IS VALUE_P decimal;
    VALUE1_P decimal;
   VALUE2_P decimal;
    i   NUMBER := 0;
BEGIN

for RC in
(SELECT LOK_DAYS.*
FROM ATT_SHIFTS
RIGHT JOIN ATT_SHIFTSDAYSOFF
ON ATT_SHIFTS.SHIFT_ID = ATT_SHIFTSDAYSOFF.SHIFT_ID
LEFT JOIN LOK_DAYS
ON LOK_DAYS.DAY_ID        = ATT_SHIFTSDAYSOFF.DAY_ID
WHERE ATT_SHIFTS.SHIFT_ID = shift_id_p
)
loop
i := i + 1;
IF i =1 THEN
      select
 count(to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDay')) into value_p
         from dual
         where to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDAY')=upper(rc.DAY_NAME_AR)
         OR to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDAY')=upper(rc.DAY_NAME_EN)
         connect by level<=to_char(last_day(to_date(month_p,'dd/mm/yyyy')),'dd')  ;

         else if i=2 then
         select
 count(to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDay')) into value1_p
         from dual
         where to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDAY')=upper(rc.DAY_NAME_AR)
         OR to_char(to_date(month_p,'dd/mm/yyyy')-to_char(to_date(month_p,'dd/mm/yyyy'),'dd')+level,'fmDAY')=upper(rc.DAY_NAME_EN)
         connect by level<=to_char(last_day(to_date(month_p,'dd/mm/yyyy')),'dd')  ;

    END IF;
     END IF;
END LOOP;
RETURN nvl(VALUE_P,0)+nvl(value1_p,0);
END;


texts:
Scenario

I have 4 front-end sql server.

All of them have the same db instances. Data are replicated from back-end databases.

Issue

In SQL Management Studio I have registered the servers and I have created a group (called front_end). The group can be inquired using the sql editor opening a connection to the servers group.

Is it possible to inquire the group programmatically setting the USE clause?

E.g.:

    use [front-end]..myDatabaseInstance
    select * from myTable


texts: SQL Server Stolen Server Memory - what it consists of?

texts:
Below what my server's performance counters show:

Questions:

1) What "Stolen Server Memory" consists of ?
I can assume "Plan Cache", "Lock Memory", "SQL Cache", "Connection", "Optimizer" are included into "Stolen Server Memory", but what else is included ?

2) How can I query rest of things that are included into "Stolen Server Memory" ?


texts: SQL Server 2012 DBCC memorystatus has wrong information. How can I correct it?

texts:
I have a Windows 7 64-bit machine with 16GB ram, 24GB pagefile, 500GB HD and SQL Server 2012 Standard Edition 64-bit running on it.

With 5 minutes of starting up, it routinely uses all my virtual memory and triggers warnings on my machine i.e. ProcessExplorer says sqlservr.exe is using Private Bytes of 38GB, Virtual Size of 61GB.

I believe the issue results from mis-information that I can see when I run DBCC MemoryStatus command, which indicates that the Available Virtual Memory is 8,763,135,270,912. This number is at least 8TB. Other stats returned by the command appear to be accurate.

Does anyone know how the DBCC MemoryStatus command gets this number and how I might correct it? I think this would affect how much virtual memory it would try to allocate.


texts: Migrating a SQL Server 2014 AG to a new 2017 AG

texts:
Our current production server runs on SQL Server 2014 AlwaysOn AG. We are planning to upgrade it to SQL Server 2017 AlwaysOn.

Due to the configuration issues with the present cluster, we can not use the existing cluster.

The production server has 52 databases with a total size of 2 TB. The databases servers an online system, so minimum downtime is our core requirement.

Our initial plan is to go for a side by side approach.

Provision 3 SQL Server 2017
Create new cluster
Logs from the existing 2014 AG Primary server to all the servers in the new 2017 group.

On the switchover day,

Disconnect all applications from the existing server.
Do a final logshipping.
Apply logs to all servers in the new group WITH NORECOVERY, except WITH RECOVERY on Primary_new
Create AG and add all database to AG
Change all applications to point to the new cluster.
Shutdown old cluster.

Is this a good approach? Are there any other approaches?
If I am missing anything, please guide me.

Thanks.


texts: CPU column 5x higher on one machine vs another?

texts:
We're running into a strange issue in a deployed version of our product where the performance is just not very good. When we restore a backup of their DB on one of our machines, it performs as we would expect.

While there are plenty of obvious things to look at, I'm focused on one area for the moment: the number of CPU cycles is 5-7 times higher in profile traces on their machine than it is on ours - running the same queries against the same database.

How does the CPU column in SQL perfmon get calculated on a vm? Do cycles get clocked while the virtual CPU is waiting for a real one? Could the switching of the real CPU under the virtual one lead to cache loss and higher CPU numbers? Am I wasting my time here?

Details:
- Both servers are on ESX hosts with SAN storage.
- I believe their infrastructure may be overloaded.
- VM has 4 CPUs, 16 (or more) GB ram minimal real load on either from within the VM
- SAN host is running loads far less than max
- ESX hosts are said to be not overloaded (although their infrastructure is expanding soon) - I will be digging into this more tomorrow.

I know there are a lot of other variables involved, but my curiosity is killing me about the huge difference in CPU numbers for the same queries on the same DB - even if it ends up not being the problem, I'd love to know more about what causes these differences!


texts: How can I set up SQL Server 2008 users without Windows/Domain accounts?

texts:
I'm trying to do this through the SQL Server Management Studio GUI.

The user accounts will be accessed by applications directly, so I don't want to create Windows user accounts.  I'd rather use something like the sa account.

Is there a way of creating users directly in SQL Server?


texts: Website is too slow when number of records increases?

texts:
I have developed a web application called Referral Program, It it developed by using PHP, MySQL. Users can joining the website and when they introduce new members, he/she gets a commission. The system working smoothly when the number of users is low. But the number of users over 50,000, then the system gets slows.

The problem is users comes into the system is like binary trees, and when he/she enter into the system, a commission is distributes  up to top level(Root node or admin). A tree traversing is need to be done on each registration. It takes some more time. Five (or more) types of commissions must be given to each user register in the system, when 100,000 users in the system 500,000 records will be there. Then the system gets too slow. And 600-1000 users will be online. They can't do anything because the slow.

At last we consolidate the 500,000 records to 100,000 (we keep the sum only), the n its OK. They can use the application. We changed InnoDB to MyISAM, its faster but doesn't support transaction.  But we should keep the original records.
How it to be done ?

EDITED - 1

I am listing the server features here.

Operating System : CentOS
RAM : 25 GB
Hard disc : 1500 GB
Current Data Base size : 800 MB
MySQL Version : 5.1 (or above)
PHP  : 5.3

MySQL error “Deadlock found when trying to get lock; try restarting transaction”

We are running a cron job (daily basis), it needs more time to complete because complex calculation is running in the huge database.  When a user register or login to the system, then shows the above error. I just look here and here dealing with the same error, but i didn't get the exact solution. We are doing lot of SELECT / UPDATE / INSERT

EDITED - 2
This is the database structure I used, also other table are there, but I think it is not important now.

The function I used is here

public function getAllUplineId($id,$i,$position,$profile_field_id){
$select = "SELECT father_id
            FROM 161_ft_individual
            WHERE id=$id ";
$result = db_query($select);
while($node = db_fetch_object($result))
    {
    $user_id = $node->father_id;
    if($user_id > 0)
        {
        $amount = 1;
        $this->update_profile_value($user_id,$profile_field_id,$amount);
        $i=$i+1;
        $this->getAllUplineId($user_id,$i,$position,$profile_field_id);
        }

    }
}

public function update_profile_value($user_id,$profile_field_id,$amount){
$query = "UPDATE 161_ft_individual SET total_leg=total_leg+$amount
            WHERE id='$user_id' LIMIT 1";
$result = db_query($query);
return $result;
}

The getAllUplineId() function takes and update all upline users from the point who register. The number of users more than 50,000 (in table 161_ft_individual) it has become too slow and nobody can do anything.  The table 161_leg_amount has 500,000 records.

Thanks in advance.


texts: Unit Testing ORDS - Oracle Rest Data Services

texts:
I've been wanting to develop some unit tests for my ORDS functions as part of my push for better CI/CD - I am running ORDS functions on an Oracle Autonomous Database 19c, and while I'm developing a testing suite for the functions I've developed, I am probably not going about it the right way.
I've done a lot of googling on the topic of specifically testing ORDS functions and there doesn't seem to be much out there that I could find, so I was hoping people on here might have some experience with this :)
I have been introduced to a couple of methods on another question (utPLSQL and a method on SQLDeveloper) but they seem to only test the code - not the restful services, as well as requiring other setup that I have not done - I just have the code in ORDS. I also don't have all my queries in PL/SQL, some of them are in SQL such as my most basic ORDS function:
-- GET REQUEST
-- gets all client info ordered by name
select * from clients order by name asc

Ideally I would just have some code that ran through the ORDS module of my choice and told me if it passed the tests I define.
I'm currently wondering whether to have a script to:

Set up a database with new schema etc and populate it with dummy data
Run all the tests
Drop all the tables to get back to before

Although that sounds like a hassle, it'll also take more and more time to run, seeing as it'll be doing many REST API calls by definition, while unit tests are supposed to be over and done with very quickly.
Things I'm wanting to test are:

Authentication is working properly (should return 401 error when there is no auth and when auth is incorrect.
Insert functions work as intended
Select functions work as intended - this is a key one as they are the most complicated functions we have at the minute
Delete functions work as intended

I'm quite new to unit testing with SQL, my previous experience being with JUnit and some Python/C++ unit testing libraries, but it does seem like there's an easier way, whether that be something that others have done in my situation, or whether there are steps I should take to make my own code more friendly to the system that already exists.
I'm also definitely open to the idea of having more than one set of tests, that run in different environments if there is a good case to do so. I'm still getting around CI/CD in general with databases so knowing where to start is a big help.


texts:
I have a problem with my secondary, which is constantly going down, in normal replica set (primary, secondary, arbiter).

What I saw while investigating that there is one long lasting operation in database which is connected to sync with master:

{
    "host" : "primary-ip:my-port",
    "desc" : "WT RecordStoreThread: local.oplog.rs",
    "threadId" : "140599714879232",
    "active" : true,
    "currentOpTime" : "2018-04-24T09:36:40.103+0000",
    "opid" : 9222195,
    "secs_running" : NumberLong(30849),
    "microsecs_running" : NumberLong("30849456103"),
    "op" : "none",
    "ns" : "local.oplog.rs",
    "command" : {

    },
    "numYields" : 0,
    "locks" : {

    },
    "waitingForLock" : false,
    "lockStats" : {
        "Global" : {
            "acquireCount" : {
                "r" : NumberLong(1),
                "w" : NumberLong(1)
            }
        },
        "Database" : {
            "acquireCount" : {
                "w" : NumberLong(1)
            }
        },
        "oplog" : {
            "acquireCount" : {
                "w" : NumberLong(1)
            }
        }
    }
}

One other thing that I saw is that some of my queries didn't have indexes and there were some operations in currentOp() that were lasting around 10 sec. One more thing is strange graphs on mms monitoring interface (left is secondary, right is primary):

Mongodb version: 3.6.0-rc3
Platform: Ubuntu 16.04
Cloud: AWS EC2
Usage: Lots of writes and updates

My question is what do you think is causing this behaviour? Is it possible that those queries without index are affecting this, and if they do, why are they affecting secondary instance and not primary? Should I just kill this long lasting operation?
Any kind of lead or advice is welcome.


texts: Optimizing query in PostgreSQL that tries to match a string and matches a timestamp range

texts:
I am building a database in PostgreSQL for financial data, where the table looks like this:

create table fin_data(
    brokerid text,
    stock int,
    holding bigint,
    stake float,
    value bigint,
    price float,
    holding_time tstzrange,
    unique(brokerid, stock, holding, holding_time));

This seems to already create a default index called: fin_data_brokerid_stock_holding_holding_time_key

How the data is organized: Each 'stock' has anywhere from 100-500 'brokers' holding a certain # of shares per day. I have around 95 million rows.

Example of my main query is:

select *
from fin_data
where brokerid='C00019'
AND holding_time @> '2015-09-28 00:00:00+08'::timestamp with time zone;

Which basically means that for a specified broker, I want to find out how many shares a certain broker holds on a certain day.

Unfortunately, this is not a very fast query, as my explain analyze looks something like this:

Bitmap Heap Scan on fin_data
    (cost=115762.08..1362596.53 rows=14079 width=64)
    (actual time=571.648..1729.416 rows=1840 loops=1)
  Recheck Cond: (brokerid = 'C00019'::text)
  Rows Removed by Index Recheck: 116323
Filter: (holding_time @> '2015-09-28 00:00:00+08'::timestamp with time zone)
  Rows Removed by Filter: 2054316
->  Bitmap Index Scan on fin_data_brokerid_stock_holding_holding_time_key
  (cost=0.00..115758.56 rows=1982116 width=0)
  (actual time=569.477..569.477 rows=2056156 loops=1)
         Index Cond: (brokerid = 'C00019'::text)
Total runtime: 1729.933 ms (8 rows)

Runtime is 1.73 seconds.

This could easily go up to 2-3 seconds for certain brokers, and is somewhat acceptable but I want it to be under 1 second if possible.

The strange thing is, when I later made an index on (stock, holding_time) only, some of those queries would be in the sub-100ms range, usually 100-300 ms. The analysis might look like:

Index Scan using stock_date_index on fin_data
    (cost=0.57..790795.62 rows=3136 width=64)
    (actual time=3.825..411.985 rows=644 loops=1)
    Index Cond: (stock = 5)
    Filter: (holding_time @> '2015-09-28 00:00:00+08'::timestamp with time zone)
    Rows Removed by Filter: 447426
  Total runtime: 412.123 ms (5 rows)

Which is much more desirable.

A hard fact about the data is that each stock might only have up to 500 brokers, but some brokers could have up to 1000+ stocks. Is that the only reason my queries are so much slower? How do I optimize this?


texts: If I fail over one database, do the others that share the same mirror endpoint fail over as well?

texts:
We have two databases setup for mirroring on a single SQL Server instance: a test database and a production database. Both get mirrored to another server using the exact same endpoints.

If I go into the Database Properties for the test database and click the "Failover" button, will it fail over the production database as well since both databases share a mirror endpoint and their Server Network Addresses properties are the same?

I am concerned because when I set up mirroring for the 2nd database, I did not have to configure anything new. It just used all the existing information.

If I use the "Failover" button from the Database Properties, will it result in failing over all databases that use that endpoint, or just the specific database that I am viewing the properties for?


texts: How can I do a Reorganize Index Task on SQL Server 2000 using a script?

texts: I have a Server that is running SQL Server 2000 and I need to add a Reorganize Index Task.  I haven't been able to find a way to create a Maintenance Plan so I am performing maintenance tasks using SQL Jobs.  One of the tasks I want to perform is to reorganize the index.  What sql code do I need to use to reorganize an index?  I've seen the code ALTER INDEX ALL ON [TABLE] but I need to reorganize the index on all tables in the database rather than just an individual table and I want to avoid a looping script if possible.


texts:
A number of texts signify that the most important aspects offered by a DBMS are availability, integrity and secrecy. As part of a homework assignment I have been tasked with mentioning attacks which would affect each aspect.  This is what I have come up with - are they any good?

Availability - DDOS attack Integrity
Secrecy - SQL Injection attack
Integrity - Use of trojans to gain access to objects with higher security roles


texts: Selecting the highest value in a column?

texts:
I am dealing with a table that has 3 columns and I need a way to select the record(s) that matches a certain PrntP_PstnGrpID(s) and is the highest ChldLevel ( The PrntP_PstnGrpID could have multiple entries but each entry will have an ever increasing ChldLevel starting at 1) How can I write a select statement to pull the row with the highest ChldLevel value? (I.E If PrntP_PstnGrpID = 10 has 3 entries of ChldLevel 1,2,3 I want to get the row with ChldLevel 3 but if PrntP_PstnGrpID = 5 has 5 entries of ChldLevel 1,2,3,4,5 I get the row with ChldLevel 5)

CREATE TABLE [dbo].[P_PositionGroupFlat] (
    [ID]                      INT            NOT NULL,
    [PrntP_PstnGrpID]         INT            NOT NULL,
    [ChldLevel]               INT            NOT NULL,
    [Sort]                    VARCHAR (8000) NOT NULL,
);


texts:
Here is the relational schema given :

employee (person-name , street, city)
works (person-name, company-name, salary)
company (company-name, city)
manages (person-name, manager-name)

Q: Find the names of all employees who live in the same city and on the same street as do their managers.

I find the solution somewhere :

but solution seems wrong which is just after second natural-join sign there is no select statement sign and just after that sign, a predicate appears 0_o , in that, from where does that manager-name attribute comes from?. I am completely lost.

My solution seems to be long since I used the Cartesian-product , and the query is bit long (of which I'm not even sure it's right or wrong) :(


texts: Setup new Window Server Failover SQL Cluster using an existing database

texts:
I see lots of resource but all of them are instructions on how to setup SQL fail over cluster (not availability group) from scratch using empty database instance.

I have a single non cluster database running now and I would like to convert it into HA fail over by adding another node/vm.   I'm not 100% sure but some Googled resources say you cannot convert a non-clustered SQL Server instance into a clustered instance.

What would be the best method or approach in my case to go about converting my existing Database Instance into HA using FCI (not Availability Group) ?


texts:
Here's a weird thing: In SQL Server 2017 Standard, I created a new index on one of my tables which already had a few indexes on it, by running the following in SSMS:
create unique index UQ_GtsCbotCurve_InvCatProTypMdOd
ON GtsCbotCurve (InventoryId, CategoryId, ProviderId, TypeId, MarkDate, ObservationDate)

SSMS said "Query executed successfully".
I hit F5 to refresh SSMS's view of the table, but the new index did not appear in the Indexes node under that table in the object explorer.
I listed the indexes on the table using the system catalog tables like this:
select *
from sys.indexes ix
     inner join sys.objects ob on ix.object_id = ob.object_id
where ob.name = 'GtsCbotCurve'

and the new index did not appear there.
But when I rerun the create index statement, it errors out, saying "an index or statistics with that name already exists on GtsCbotCurve".
I disconnected from the server, and reconnected, but the index still doesn't appear.
What could be going on here? Does the index actually exist? How do I get it to appear in the management tools?


texts: What postgres sync solution should I use to duplicate production, but read it from another application?

texts:
I'm a little out of my league with my current problem, but I'm looking for a solution where the following conditions are met:

My production database reads and writes as it currently does
My production application ALSO writes everything to a 2nd remote database
The writes to the 2nd database are frequent and near real time
I have a separate application that can read from the 2nd database

Both applications are rails, and the main application/database is the product. The 2nd application is a custom built analytics service that only reads from the 2nd database.

Which postgres solution(s) can support these requirements?  Thanks.


texts:
I have a Customer table, with the columns "Name", "Location" and "DateMovedIn" (amongst others), which contains c. 20,000,000 rows.

On this table, I have the index I_LOC_NAME_DATE, such that:

Column          Position  Descend
Location        1         ASC
Name            2         ASC
DateMovedIn     3         ASC

I'm writing a query which selects the customer's who moved into their location between two dates;

SELECT *
FROM   Customer
WHERE  Customer.DateMovedIn BETWEEN :start_date AND :end_date
AND    Customer.Location = :location;

From what I've read about Oracle's use of indexes, in this situation I would expect it to use a skip scan (filter by Location, and then hop betwen the names and get each move in date), but it turns out it uses a range scan.

An EXPLAIN reveals the range scan cost to be 77, and when I apply a index_ss hint as follows, the cost jumps to 401,096 (yes, really).

SELECT /*+ index_ss(Customer I_LOC_NAME_DATE) */ *
FROM   Customer
WHERE  Customer.DateMovedIn BETWEEN :start_date AND :end_date
AND    Customer.Location = :location;

Can anyone explain how Oracle is using a range scan in this circumstance without the Name being specified?


texts: Count childs of parent with sql or save in column?

texts:
In postgres I have a threaded setup where parent id has many childs and childs can have childs. What is the most efficient way to get the count with a sql statement similar to this:

WITH RECURSIVE cte (id, message, author, path, parent_id, depth)  AS (
    SELECT  id,
        message,
        author,
        array[id] AS path,
        parent_id,
        1 AS depth
    FROM comments
    WHERE parent_id IS NULL

    UNION ALL

    SELECT comments.id,
        comments.message,
        comments.author,
        cte.path || comments.id,
        comments.parent_id,
        cte.depth + 1 AS depth
    FROM comments
    JOIN cte
        ON comments.parent_id = cte.id
)
SELECT id, message, author, path, depth
FROM cte
ORDER BY path;`

Is it better to count the child, or have a column that keeps track when a row is insert an increment and pull that column with the query?

If counting from sql, how would I implement that?


texts: How to determine CHECK or NO CHECK for foreign Key using SQL Server System Tables

texts:
I need to know at a low level whether a foreign key is to be exported as CHECK or NO CHECK but can't find out where to find that information.  I am using

-- using INFORMATION_SCHEMA to enumerate foreign keys
-- for a particular referenced table
SELECT
C.CONSTRAINT_NAME [constraint_name]
,C.TABLE_NAME [referencing_table_name]
,KCU.COLUMN_NAME [referencing_column_name]
,C2.TABLE_NAME [referenced_table_name]
,KCU2.COLUMN_NAME [referenced_column_name]
,RC.DELETE_RULE delete_referential_action_desc
, RC.UPDATE_RULE update_referential_action_desc
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
     ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
        AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
   INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
     ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
        AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2
     ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA
        AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
     ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA
        AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME
        AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
WHERE  C.CONSTRAINT_TYPE = 'FOREIGN KEY' AND  C2.TABLE_NAME = 'SalesOrderHeader'

with the code coming from https://www.mssqltips.com/sqlservertip/4753/list-dependencies-for-sql-server-foreign-keys/

However I am unable to determine which field will tell me if a FK constraint should be CHECK or NO CHECK.  How can I determine this, thanks.


texts:
I am currently using scripts provided by Mr. Ola Hallengren for executing maintenance job and of-late I have been noticing that there are many tables (heaps) fragmentation level is alarmingly high and needs to be looked and taken action upon. I checked FAQ at the site and seems his script doesn't support rebuilding heaps. I used below query to find the fragmentation level:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() and dbindexes.name is null
ORDER BY page_count desc, indexstats.avg_fragmentation_in_percent desc

My application is supported by vendor and I have been communicating with them to change these heaps to tables and create clustered index however it hasn't yielded any meaningful result yet since they have defined primary key as unique non-clustered index and it is also part of foreign key, so needs to change at many level before doing any change. First of all, it took many days for me to explain the difference between clustered index and primary key with unique index.

I also went through the tweaks suggested by Mr. Brent Ozar for changing the defaults at script provided by Mr. Ola Hallengren for index optimize in order to make it more efficient however I didn't find any details of heap rebuilding.

As per my understanding heap's fragmentation can be handled in two ways as described here:

To create clustered index on table and drop it - This would clear all the fragmentation and also rebuild all non-clustered index however it would be time and I/O consuming.
Rebuilding the heap - This would also clear the fragmentation and rebuild all non-clustered index part of table rebuild.

I can't go for option 1 because I am not aware of columns where clustered index can be created and also this could take longer than option 2.

I am looking for possibility of implementing option 1 in the scripts by Ola Hallengren or alternative method for handling this. Also to add, I would like to rebuild my heaps only when the size of heap is more than 10,000 pages and fragmentation level is more than 80.

I am using Microsoft SQL Server 2014 SP3 Enterprise Edition.

As a DBA - I don't prefer to have heaps in my database however since it is vendor supported application and since they have already defined primary key as unique index and these keys are foreign keys, its very difficult to change them to clustered due to references as well as likeliness of down time.

EDIT: I went through the link provided by Mr. Erik Darling and I can confirm that I have number of heaps with forwarded records or deletes across the databases. Now, I am back to point from where I had started i.e. with those two options. As I mentioned earlier, creating clustered index is very very difficult in my scenario and will require at least months(being optimistic) with likeliness of downtime considering complex foreign key structure. Need advise on rebuilding the heaps and possible side effect.


texts: MySQL 5.5 not starting after master-master configuration

texts:
The following master-master configuration worked on MySQL Server 5.1.x(Ubuntu 10.04).

Master1(192.168.2.80)

##################################
#MySQL Replication
##################################
skip-host-cache
skip-name-resolve
event_scheduler = ON
max_connections = 500
max_connect_errors = 1000

server-id = 10
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 1

master-host = 192.168.2.81
master-user = repli
master-password = secret
master-connect-retry = 60

binlog-format = MIXED
#binlog-ignore-db = information_schema
#binlog-ignore-db = lb1

#max_binlog_size = 500M
log-bin = /var/log/mysql/mysql-bin
log-bin-index = /var/log/mysql/bin-log.index

log-slave-updates
report-host = 192.168.2.80
replicate-ignore-db = information_schema
replicate-ignore-db = lb2
replicate-ignore-db = performance_schema

relay_log_space_limit = 3G
relay-log = /var/log/mysql/relay.log
relay-log-index = /var/log/mysql/relay-log.index
##################################

Master2(192.168.2.81)

##################################
#MySQL Replication
##################################
skip-host-cache
skip-name-resolve
event_scheduler = ON
max_connections = 500
max_connect_errors = 1000

server-id = 20
replicate-same-server-id = 0
auto-increment-increment = 10
auto-increment-offset = 2

master-host = 192.168.2.80
master-user = repli
master-password = secret
master-connect-retry = 60

binlog-format = MIXED
#binlog-ignore-db = information_schema
#binlog-ignore-db = lb2

#max_binlog_size = 500M
log-bin = /var/log/mysql/mysql-bin
log-bin-index = /var/log/mysql/bin-log.index

log-slave-updates
report-host = 192.168.2.81
replicate-ignore-db = information_schema
replicate-ignore-db = lb1
replicate-ignore-db = performance_schema

relay_log_space_limit = 3G
relay-log = /var/log/mysql/relay.log
relay-log-index = /var/log/mysql/relay-log.index
##################################

Now it's NOT working on MySQL Server 5.5(ubuntu 12.04). I mean to say when I restart the mysql service after above configuration, mysql server takes long time and dont' start at all. Nothing in mysql logs and are empty. I tried killing mysql and starting it again but still it's not starting and if I remove the above master-master replication configuration then it starts usually. What's wrong I am doing with mysql 5.5?

Thank you!


texts: Effect on performance on retrieval of image column from a table with half a billion records on remote server

texts:
I require to loop through different servers to collect data in a temporary table. The table accessed on these servers have about half a billion records. To retrieve the data from these tables, I filter out rows using a list of values for one of the columns. The query takes about 5-10 mins for each server. However, if I add image column to the select list, the query hangs.

I was wondering if it is acceptable to obtain image column in such a scenario ? Will it hit the performance ?

CREATE TABLE #TempPI(
RowId INT IDENTITY(1,1),
[id]  [char](22)  NOT NULL,
[name]  [varchar](128)  NOT NULL,
[time]  [varchar](50)  NOT NULL,
[url_id] [char](22) NOT NULL
--file IMAGE NULL)

CREATE TABLE #TempBrowser(
[browserid]  [char](22)  NOT NULL)

BULK INSERT #TempBrowser
FROM '\\Shared\BulkLoadDocs\Input.txt' WITH (ROWTERMINATOR = '\n')

INSERT INTO #TempPI
SELECT [id] , [name] , [time] , [url_id]
FROM Server.Database.DBO.table1 a WITH (NOLOCK)
WHERE  exists (Select * from #TempBrowser b  where a.ID=b.BrowserId)

If I use File column, the query hangs.

Regards,
Kanu


texts:
I have to create a table. Its size is going to increase very fast.

Is there going to be any issue if many insert queries are fired at the same instant as mentioned here - User autoincrement ID for username

I want to use the key so that after the value reaches the maximum possible value next key is generated with value 1. How can this be achieved? Old data is not useful so I can delete the data at the starting rows. I know UNSIGNED BIGINT has a huge range but is there any way I can create the database with a trigger so that key reuses old values? What can be the downside of trying to write user defined function to accomplish this?


texts: What is the underlying representation of BLOB data when I export it?

texts:
When I export a blob object to an SQL file, I get some thing like this for a blob field:

0x4f3a383a227374644.....,

Questions

I would like to know how the BLOB is actually converted to the above format?
What is its underlying representation?


texts: Is there a reason my CTE with a basic subquery in the second stage of the CTE is failing?

texts:
Ok... anyone aid with this sorry...i've been playing with some of my works reports and i thought id found a way to mix two results into one when i came across a small issue...

if i run my CTE code below without the lower subquery it runs straight away...

WITH CTE_RealAudit (UserName,WorkstationName,LoginDate,LoginTime,"Days","Hours","Minutes","Seconds")
AS
(
SELECT UserName AS 'User',WorkstationName AS 'Workstation',
RIGHT('0'+CONVERT(varchar(2),DATEPART(dd, DATEADD(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))),2)+'/'+RIGHT('0'+CONVERT(varchar(2),DATEPART(m,dateadd(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))),2)+'/'+CONVERT(varchar(4),DATEPART(yy,dateadd(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))) AS 'Login Date',
RIGHT('0'+CONVERT(varchar(2),DATEPART(hh,DATEADD(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))),2)+':'+RIGHT('0'+CONVERT(varchar(2),DATEPART(mi,dateadd(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))),2)+':'+RIGHT('0'+CONVERT(varchar(2),DATEPART(ss,dateadd(ss, StartTime + DATEDIFF(ss, GETUTCDATE(), GETDATE()),'19700101'))),2) AS "Login Time",
CONVERT(varchar(2), SUM(EndTime-StartTime)/(3600*24)) AS 'Days',
CONVERT(varchar(2), SUM(EndTime-StartTime)/3600) AS 'Hours',
CONVERT(varchar(2), (SUM(EndTime-StartTime) % 3600) / 60) AS 'Minutes',
CONVERT(varchar(2), (SUM(EndTime-StartTime) % 60 )) AS 'Seconds'
    FROM IMuse.vAuditUsageAndNames
        WHERE StartTime BETWEEN DATEDIFF(ss,'19700101',DATEADD(dd,-3000,CONVERT(date,SYSDATETIME())))
        AND DATEDIFF(ss,'19700101',CONVERT(date,SYSDATETIME()))
        AND LEFT(UserName,2) <> N'P-'
        AND LEFT(UserName,3) <> N'EP-'
        AND EndTime <> StartTime
    GROUP BY UserName,WorkstationName,StartTime
)

SELECT * FROM CTE_RealAudit
    WHERE "Hours" <> N'0'
    AND "Minutes" <> N'0

but if i add a subquery to the last select such as

SELECT * FROM CTE_RealAudit
    WHERE "Hours" <> N'0'
    AND "Minutes" IN
    (select Minutes from cte_realaudit)

or my goal

SELECT * FROM CTE_RealAudit
    WHERE "Hours" <> N'0'
    AND "Minutes" IN
    (SELECT "Minutes" from CTE_RealAudit
        where "Hours" >= N'0'
        and "Minutes" >= N'0')

it completely freaks out and takes 20+ minutes? anyone aid in why this is occurring?

newbie here so sorry if im doing something totally stupid.

thanks


texts: What exactly does 'localtime' mean as value of configuration parameter timezone?

texts:
On PG 9.6, when I do SHOW timezone;, I get:

 TimeZone
-----------
localtime
(1 row)

which appears also in postgresql.conf as the value of configuration parameter timezone:

timezone = 'localtime'

On PG 9.5, that value used to be my time zone in "full name" format.  Hence my surprise.

I have been unable to find any reference to this value in the documentation.  I suppose it means that PG inherits the timezone of the OS.  But I'd like to be sure, and specially, find where it is documented.


texts: SQL Insert with soft row limit in stored procedure

texts:
I have a table ([Label]) which i would like to enforce a soft limit on the number of rows a company can insert (stored procedure code below)
If this stored procedure is being called frequently (e.g. a few times per second by different users), is it possible that it will insert a row over the limit due to concurrency issue? Should i use LOCK or TRANSACTION?

CREATE PROCEDURE [dbo].[admin_AddLabel]
@companyId INT,
@label NVARCHAR(50)
AS
DECLARE @limit INT = 10
DECLARE @count INT = (SELECT COUNT(1) FROM Label WHERE CompanyId = @companyId)
IF @count < @limit
BEGIN
INSERT INTO Label (Label, CompanyId)
VALUES (
  @label,
  @companyId
)
RETURN 0
END


texts: Permission denied with Copy command when using network share

texts:
I am running the following Copy command via npgsql (.Net) from a client on the network:

COPY work_narcra00_2017_q2.a2_aevent FROM '//BRAHMA//EnvironProcessing//dbprocessfixedasciiimport.tmp' WITH CSV

I am getting this error:

ERROR: could not open file "//BRAHMA//EnvironProcessing//dbprocessfixedasciiimport.tmp" for reading: Permission denied
SQL state: 42501

I also tried the command from PgAdmin III and I get the same error.

The obvious answer is the file/folder permissions, but both the share and NTFS permissions have read/write permissions to Everyone.

Another post suggested adding permissions for Network Service but that makes little sense for accessing a network share because I cannot add the DBServer/NetworkService account to the client computer's share (though I tried adding the local Network Service account just in case)

Moving the file to a local folder on the server of course works, but that is not a viable long-term option right now.

Edit: I can see and edit the file just fine from within Windows using Notepad.


texts: Postgres not using multicolumn index for queries on leftmost columns?

texts:
I'm working in Postgres 9.5.15 and have a table called project:

 id                             | integer                     |           | not null | nextval('project_id_seq'::regclass)
 user_id                        | integer                     |           | not null |
 is_deleted                     | boolean                     |           | not null | false
 is_listed                      | boolean                     |           | not null | false

And it has this multicolumn index:

"project_is_deleted_is_listed_user_id_ix" btree (is_deleted, is_listed, user_id)

But if I try to query the two left columns, the table is being sequentially scanned rather than using the index:

LIVE !!! => analyze;
LIVE !!! => explain analyze select * from project where is_listed and not is_deleted;

Output:

    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Seq Scan on project  (cost=0.00..36846.95 rows=460760 width=508) (actual time=0.006..281.478 rows=394615 loops=1)
   Filter: (is_listed AND (NOT is_deleted))
   Rows Removed by Filter: 491652
 Planning time: 0.091 ms
 Execution time: 356.221 ms
(5 rows)

I don't understand why this is - 356ms execution time seems quite slow, surely it'd be faster to use the index? And given that the two left-most columns are being queried, surely the index is usable?


texts: BCNF decomposition does not prevent insert anomaly?

texts:
I am in the process of designing a schema for a simple polling app and have tried to normalize it with the decomposition algorithm from this lecture.
Requirements
A poll has a unique id (poll_id), a question (question) and multiple options (option) that can be voted on.
A participant (participant_id) can vote for each option by giving it one to ten stars (stars).
What I have so far:
I came up with the following relation that contains all attributes:
(poll_id, question, option, participant_id, stars)
...and the following functional dependencies:

poll_id → question
poll_id, option, participant_id → stars

After applying the algorithm I have the following relations (keys are bold):

(poll_id, question)
(poll_id, option, participant_id, stars)

My Question:
At this point the schema should be in BCNF, but it's easy to see that I can't just create a poll with a question and some options, because with this design options can only exist if there are also participants who have voted. I should probably have another relation (question, option). Did I miss something important (dependencies, normalization rules)?


texts: There is any way to log all mongodb query into file in nodejs?

texts: There is any way to log all MongoDB query into a file


texts:
I want to collect data from differed tables grouped by year and month.
I'm using subquery for each sub data.
Using mysql.

    SELECT YEAR(td.db_date) AS stat_year, job.total_jobs
    FROM time_dimension AS td

    LEFT JOIN (

        SELECT YEAR(j.published_at) AS stat_year, COUNT(*) AS total_jobs
        FROM jobs AS j
        GROUP BY stat_year

    ) AS job ON job.stat_year = stat_year

    GROUP BY stat_year

When I'm joining data with id - it works perfect. But with this dates...

For time_dimenstion I'm using this https://gist.github.com/bryhal/4129042

Subquery return this data

2014 | 2334
2015 | 2000

But whole query starts with the first year and return same data but wrong matched with year

1 | 2334
2 | 2000


texts: Database link from Local Oracle 11g to Oracle Database Cloud Service 11g

texts:
We are using Oracle database from Oracle Database Cloud Service,
now we are not able to connect Cloud db from Local oracle db.

Somehow we created link using below commnd,

CREATE DATABASE LINK link_1 CONNECT TO "username"
IDENTIFIED BY "password" USING 'https://x.x.x.oraclecloudapps.com/apex/';

Now once i execute query select * from EMP@link_1 It throws an error

ORA-12154: TNS:could not resolve the connect identifier specified
12154. 00000 -  "TNS:could not resolve the connect identifier specified"

with Cause and Actions.

Let me know if some additional configuration/changes required.


texts: Executing same request from C# VS SSMS give different execution time

texts:
I have a request like this one

SELECT
[EstimateId],
[CreationUserId],
[EstimateStatusValueId],
[LanguageId],
[LocationId],
[EstimatorUserId],
[FilterUnitSystemTypeId],
[EstimateNumber],
[RevisionNumber],
[CreationDate],
[ModificationDate],
[ProjectDescription],
[IsBsdq],
[ClosingDate],
[ClosingTime],
[ClosingUpdatedOn],
[DeadLineDate],
[IsReceived],
[Inclusion],
[Exclusion],
[Misc],
[Note],
[WorkDeadLines],
[Comments],
[Validity],
[PlansLocation],
[PlansReceivedFrom],
[Price]
FROM [Estimate].[Estimates]
ORDER BY [ClosingDate] ASC, [ClosingTime] ASC

When i run this query in SSMS i get a executing time of 953ms, but when i run this query from a Linq Query in my C# i get a executing time of 1813ms.

The Linq Query use the ".Net SqlClient Data Provider" and is issued against EntityFramework (EDMX file). Is this can be an issue ?

Does anybody knows why i have a big difference between execution times of those requests that are the same but execute from different context against the same database ?

I verified all execution plans of both request and they use the same index to satisfy their respective query.

To see the execution plan of the C# request i use the SQL profiler to trap the Show Plan XML event and i compare it to the one of SSMS and both are the same.


texts:
I have a table which maintains a user chain

Here is the table structure

+------+--------+
| id   |   user |
+------+--------+
|    2 |      4 |
|   10 |      4 |
|   11 |      4 |
|    1 |      2 |
|    3 |     10 |
|    4 |      5 |
|   34 |      1 |
|   35 |     11 |
+------+--------+

I have to write a query which will out the chain of users

For example : if I have to find chain of user '4' then  result will be

2, 10, 11, 1,3,35, 34


texts:
I am running ruby and rails app with postgresql database, and I want to do batch import from text file to the database.

I've configured the database and if I run psql and \l to list all databases, I can see it.

However, after I run rake db:migrate no relations are created in the database and running psql\d in order to check tables, says "No relations found". But the schema is created and I can see the tables in Induction PostgreSQL client (http://inductionapp.com)


texts: Is there a cross-platform way to compare data in one columnd on each sitde of replicated data, like a checksum or hash?

texts:
I have an Oracle 12 database with lots of tables, and I am replicating several of the tables (a subset of rows) into a SQL Server 2016 database.   The subset of rows can be established with a WHERE clause on the Oracle side.
I have two web services that can expose anything I want to from that data, one on each side.
Do you have a suggestion for an approach of what I can expose, then compare to find out if the data between the systems matches?
I am currently exposing from one table, which has a few million rows the COUNT(*), which is a no-brainer since it is very efficient. So far, so good.
I'm also exposing the SUM of each of a few NUMBER(18,2) columns and comparing it to the corresponding SUM on the SQL Server side.  However, this is problematic, as it has to scan the whole table in SQL Server; it is sometimes blocked, and sometimes might cause other processes to block.  I imagine similar problems could occur on the Oracle side too.
Also, the SUM will not tell me if the rows match--it will only tell me that the totals match; if an amount was improperly added to one row and subtracted from another, I wouldn't catch it.
I've pondered whether Oracle's STANDARD_HASH might help me, but it seems cumbersome or error-prone to try to generate the exact same HASH on the SQL Server side, and also this doesn't help with the inefficiency/blocking nature of the call.
So is there any way to have both databases keep track of a hash, checksum, CRC code, or other summary of a column's data, that is efficient to retrieve, and that I can then use to compare to have some idea whether data is the same on both sides?  It need not be a perfect solution--for example, comparing SUMs is close but perhaps not quite good enough.
As a first stab, I created an "summary" indexed view, with columns derived from SUMs, on the SQL Server side.  This makes querying the view very fast, but incurs additional penalty on every write to the large table underneath.  Still, I think it will work, but I'd like to improve on it.  Other, better ideas?


texts:
This is a basic DB design question but I now run multiple times into the same situation which I try to illustrate in the following example.

Lets say I have make a DB-system for messaging in a classifieds platform.

A user publishes many ads and other users can message her for a specific ad.

I thus have an ads table, a user table and a message table in my DB.

Since users message people for specific ads, storing the ad AND the user is redundant.

On the other hand, when listing all incoming messages for a given user it would be much more efficient to have the user name as a field in the message table too, because a user might have many ads and only very few of them have messages.

So should the message table now have a user field or not?


texts: How to order by clause on distinct select stament condition value

texts:
This is my query,

SELECT DISTINCT
    BASE_Product.ProductId,
    (CASE WHEN (BASE_Art.ArtId IS NOT NULL AND BASE_Heel.HeelId IS NULL)
          THEN BASE_Art.ArtNo
          ELSE (CASE WHEN (BASE_Art.ArtId IS NULL AND BASE_Heel.HeelId IS NOT NULL)
                     THEN BASE_Heel.HeelNo
                     ELSE (BASE_Art.ArtNo + '-' + BASE_Heel.HeelNo) END)END) AS ArtHeelNo,
    BASE_InvoiceLine.Price
FROM
    BASE_Product
INNER JOIN BASE_ProductVariation
    ON BASE_Product.ProductId = BASE_ProductVariation.ProductId
INNER JOIN BASE_InvoiceLine
    ON BASE_ProductVariation.ProductVariationId = BASE_InvoiceLine.ProductVariationId
LEFT OUTER JOIN BASE_Art
    ON BASE_Product.ArtId = BASE_Art.ArtId
LEFT OUTER JOIN BASE_Heel
    ON BASE_Product.HeelId = BASE_Heel.HeelId
WHERE
    (BASE_InvoiceLine.InvoiceId = 2)

Once I execute the above query I'm getting the result like below screen shot.

This above result ordered by ProductId wise I want to get this order by HeelNo wise.

Issue is I can't order by HeelNo wise because of order by clause on distinct select and case..

I want the result should be,

How can I do this..


texts:
I've worked exclusively with RDBMSs for the past 10 years and am trying to explore something new to me like MongoDB. I’m trying to learn about data model design in this environment and because normalization is so engrained in my brain, it feels weird to denormalize and know how and when to do it. A number of questions are coming to mind:

What is Mongo’s answer for modeling a one-to-many relationship?
What is Mongo’s answer for modeling many-to-many relationships?
So, if I have a users collections and a comments collection, where as user has many comments, and a comment references the user document, how do we join this data functionally in code? For each comment displayed, do we “query” the users collection to retrieve their associated username? Or do we embed that user’s username directly into the comments and then push an update to the embedded username in comments in the event that the user’s username would change?
How do we make a decision about when to embed information versus creating a new collection?

Thanks.


texts:
I have 2 tables as below
CITY_A

ID
CODE
CITY

001
90
PARIS

002
90
PARIS

003
85
PARIS

004
60
SYDNEY

005
75
SYDNEY

006
75
SYDNEY

CITY_B

ID
CODE
CITY

001
IN
PA

002
OUT
PA

003
OUT
PA

004
IN
SYD

005
OUT
SYD

006
IN
SYD

The content is same in both tables but the representation is different. I have 2 mapping here for CITY column and CODE column.
Mapping for CITY_A

CODE
CITY
MEANING

90
PARIS
ACTIVE

85
PARIS
NOT_ACTIVE

60
SYDNEY
ACTIVE

75
SYDNEY
NOT_ACTIVE

Mapping for CITY_B

CODE
CITY
MEANING

IN
PA
ACTIVE

OUT
PA
NOT_ACTIVE

IN
SYD
ACTIVE

OUT
SYD
NOT_ACTIVE

Now i have to compare both tables based on the mapping and find the mismatches. So my expected output is

ID
CITY
RESULT

001
PARIS
MATCH

002
PARIS
MISMATCH

003
PARIS
MATCH

004
SYDNEY
MATCH

005
SYDNEY
MATCH

006
SYDNEY
MISMATCH

Currently I'm using CASE with Temp table. It is too big.
SELECT A.ID , CASE WHEN A.CODE = 90 AND A.CITY = 'PARIS' THEN 'ACTIVE'
                   WHEN A.CODE = 85 AND A.CITY = 'PARIS' THEN 'NOT_ACTIVE'
               -- FOR OTHER CITIES
               END AS A.CODE_MEANING,
               CITY
INTO #TEMP_CITY_A
FROM CITY_A A

SELECT B.ID,CASE WHEN B.CODE = 'IN'  AND B.CITY='PA' THEN 'ACTIVE'
                   WHEN B.CODE = 'OUT'  AND B.CITY='PA' THEN 'NOT_ACTIVE'
              -- FOR OTHER CITIES
            END AS B.CODE_MEANING,
            CASE WHEN B.CITY = 'PA' THEN 'PARIS'
                 WHEN B.CITY = 'SYD' THEN 'SYDNEY'
            END AS CITY
 INTO #TEMP_CITY_B
 FROM CITY_B B

SELECT A.ID ,A.CITY, CASE WHEN A.CODE_MEANING!=B.CODE_MEANING THEN 'MISMATCH' ELSE 'MATCH' END AS RESULT
FROM #TEMP_CITY_A A
JOIN #TEMP_CITY_B B ON A.ID = B.ID AND A.CITY = B.CITY

Which is the most efficient way?


texts: Silent truncation of last character when using SSIS with Oracle

texts:
I am using SSIS to export data from an Oracle database. Sometimes when I export a VARCHAR2 column to NVARCHAR the last character gets truncated (silently). It's completely random as far as I can tell. I have tried using different SSIS data types (DT_STR, DT_WSTR and DT_NTEXT) but the problem persists. The source string is always 12 characters long ([0-9A-Z] only), but when I check the destination database some columns are missing the last character.

I use the latest 32bit Oracle Instant Client (11.2) with ODBC and SQL Server 2008 R2. I use ADO .NET for both source and destination. The source character set is AMERICAN_AMERICA.WE8ISO8859P1 and the source and destination columns are declared as VARCHAR2 (12 BYTES) and NVARCHAR(12) respectively.


texts:
I am facing below error during postgresql db start.
FATAL: the database system is starting up
FATAL: failed to re-find parent key in index "16619" for split pages 915114/915115

What is the solution of this error?


texts: How to optimize inefficient query in PostGIS

texts:
I'm doing some queries on a PostGIS, and some of them take a really long time ( > 60 secs). My queries are like this, except querying different tables (like osm_placesbelow).

This query takes about 15 secs:

SELECT row_to_json(fc) FROM (
SELECT ST_AsGeoJSON(ST_Transform(ST_Intersection(lg.geometry, bbox.geom), 4326))::json As geometry, (
SELECT row_to_json(t) FROM (
SELECT "name","type","admin_level") t )As properties FROM osm_admin As lg, (
SELECT ST_Transform(ST_MakeEnvelope(-2.8125,54.162433968067795,25.312499999999986,67.60922060496382, 4326),3857) as geom)
    as bbox
WHERE ST_Intersects(lg.geometry, bbox.geom)
AND (admin_level < 5) ) fc;

The execution plan: http://explain.depesz.com/s/ZJU2

This query takes approx. 45 secs:

SELECT row_to_json(fc) FROM (
SELECT ST_AsGeoJSON(ST_Transform(ST_Intersection(lg.geometry, bbox.geom), 4326))::json As geometry, (
SELECT row_to_json(t) FROM (
SELECT "name","type","area","z_order") t ) As properties FROM osm_landusages As lg, (
SELECT ST_Transform(ST_MakeEnvelope(-2.8125,38.82259097617711,25.312499999999986,57.326521225217064, 4326),3857)
  as geom) as bbox
WHERE ST_Intersects(lg.geometry, bbox.geom)
AND (area > 200000000) ) fc;

http://explain.depesz.com/s/26L

Table info:

                            Table "public.osm_admin"
       Column    |          Type           |                       Modifiers
    -------------+-------------------------+--------------------------------------------------------
     id          | integer                 | not null default nextval('osm_admin_id_seq'::regclass)
     osm_id      | bigint                  |
     name        | character varying       |
     type        | character varying       |
     admin_level | integer                 |
     geometry    | geometry(Geometry,3857) |
    Indexes:
        "osm_admin_pkey" PRIMARY KEY, btree (id)
        "osm_admin_geom" gist (geometry)
        "osm_admin_osm_id_idx" btree (osm_id)

 Rows: 333319

                           Table "public.osm_landusages"
      Column  |          Type           |                          Modifiers
    ----------+-------------------------+-------------------------------------------------------------
     id       | integer                 | not null default nextval('osm_landusages_id_seq'::regclass)
     osm_id   | bigint                  |
     name     | character varying       |
     type     | character varying       |
     area     | real                    |
     z_order  | integer                 |
     geometry | geometry(Geometry,3857) |
    Indexes:
        "osm_landusages_pkey" PRIMARY KEY, btree (id)
        "osm_landusages_geom" gist (geometry)
        "osm_landusages_osm_id_idx" btree (osm_id)

Rows: 19934972

How can I optimize this query? Are there ways I can query more efficiently? Should I create more indexes? Does it make sense to create another index on the WHERE CLAUSE I'm using (admin_level < 5), since I'll be repeating the same query all the time?

The data is the OSM PostGIS database.


texts:
I have one big table that is used to generate business intelligence cube. Currently it has around 40M rows and 55 columns. A lot of the cube dimensions are generated by running 'select distinct' on a column of the big table.

Currently I have one index for each 'select distinct' and 16 indexes in total. Is it better to merge some of those indexes? My thinking is that the cube needs to process all rows anyway, and the indexes speed up the process because it is much smaller than the entire table, and if I put two or three columns in one index the index size will not grow so much. Maybe the good candidates are columns that have low specificity.

Am I going in the right direction? Can the query make use of the second column (or third, fourth, ...) of the index?

I'm using SQL Server 2005 Standard Edition.


texts: MySQL- Performance implications of changing a column collation from latin1_swedish_ci to latin1_bin

texts:
I am a developer so was hoping someone with DBA experience could help me out here.

I am using a MySQL database (MYSQL 5.6).

I came across a situation where a particular column stores Ids generated by any external system (to be provided by user input) and hence requires case sensitivity (abcd is a different id from ABCD). It seemed pretty natural to me to change the collation for that column to latin1_bin from latin1_swedish_ci where my database and tables all use latin1_swedish_ci collation.

I use the following statement to change the collation:

ALTER TABLE T MODIFY C VARCHAR(200) BINARY NOT NULL;

Are there any performance implications of doing this change? My application does not use COLLATE specifically in any query.

Also, column C is part of an index and a unique constraint with 2 other columns say A, B in table T (A,B,C). A and B are also foreign keys in table T.

Do I need to drop the index and unique constraints and re-create them?

Also, how do I rollback this change? Will this statement do the job:

ALTER TABLE T MODIFY C VARCHAR(200) NOT NULL;

The table T looks like :

|id|A|B|C|created_at|

id is auto increment.

Please help.


texts: Creating separate small table from big based on access frequency for "select" performance

texts:
I have table with several millions of rows which are selected a lot (many times in a second in average). It is indexed and everything is ok for now. But performance is always a question (sometimes workload is too high) and I am thinking about optimization (hehe).

Among this several millions of rows, 1% of them accessed like 80% of time, and I know exactly which rows. My plan is making separate table of this rows (so it will be several thousands), I will keep theirs keywords (unique indexed) in php array (persistant cache), and every time before big table is queried, I will check this array for this keyword, and if it exists (80% of time) I will search it in the small table, if not, search in the big. Data updated not often, so it will not be hard to maintain small table. Sounds like a good plan.

The question is, is it good idea, should I try this?


texts: SSAS -> MDX -> Create a price comparision against resellers

texts:
I want to write an MDX query that will do a price comparison for a product against each re-seller and return a count which a 'greater', 'lesser' or 'equal' for a given month.

Measure:
fact_reseller_price

Dimension:
dim_date
dim_resellers
dim_products

Data in my fact table:

fact_reseller_price

product      reseller price date_purchase
SSD          AMZ      10.00    01/5/2012
SSD          BB       15.00    01/5/2012
SSD          COSTCO   15.00    01/5/2012
SSD          COSTCO   15.00    01/5/2012
SSD          FRYS     20.00    01/5/2012
SSD          WALMART  10.00    01/5/2012
SSD          EN       5.00     01/5/2012
SSD          EN       8.00     01/5/2012
SSD          EN       4.00     01/5/2012

My resultant table from the MDX query (Price Comparison)

product reseller comparision_reseller count type    date_purchase
SSD     AMZ      BB                   1     GREATER 01/5/2012
SSD     AMZ      COSTCO               2     GREATER 01/5/2012
SSD     AMZ      FRYS                 1     GREATER 01/5/2012
SSD     AMZ      WALMART              1     EQUAL   01/5/2012
SSD     AMZ      EN                   3     LESSER  01/5/2012
SSD     BB       AMZ                  1     LESSER  01/5/2012
SSD     BB       COSTCO               2     EQUAL   01/5/2012
SSD     BB       FRYS                 1     GREATER 01/5/2012
SSD     BB       WALMART              1     LESSER  01/5/2012
SSD     BB        EN                  3     LESSER  01/5/2012


texts: How to query tabular data that has thousands of columns

texts:
I'm working in a module that at some point we will need to make queries to a tabular data that has ~25K columns and ~70K rows. Right now this data is persisted as a CSV file.

We've tried a few approaches to address it, but all of them failed or became too slow because of the number of columns we have. When we tried these approaches with a different file that has much more rows but not as many columns (~50M rows vs. 40~ columns), it work perfectly.

Having said that, I'd like to know if some of you has gone through this problem or has any idea on how to address it. There's no restrictions on technology or anything. I'm not listing what we've tried so far intentionally, in order not to bias your ideas. But if you'd like to know, I'll be happy to share our experiments with you.


texts: Design question about student assessment data

texts:
I am designing a relational database in Filemaker Pro that will be tracking assessment data for students in after school tutoring programs. Depending on the student's grade level and skill set, each student may have a number of different assessments. Thus, there will be a many-to-many relationship between students and assessments.

What I am trying to determine is whether I should make a single assessment table (example header row: foreign key student ID, foreign key assessment ID, student score, date) or whether I should have a separate table for each assessment (example header row: foreign key student ID, student score, date).

The reason I might want a separate table for each assessment is that not every assessment will have the same descriptions, so I may not be able to create a single assessment_info table that encompasses all that info. For instance, some assessments will have a total possible score, whereas others will only have a pass/fail indicator.

Any advice for how to think about this design decision would be appreciated.


texts: Error while restoring from Enterprise Edition to Standard Edition

texts:
I am restoring a database from a SQL Server Enterprise Edition onto a Standard Edition server.  I keep getting an error stating:

  Database 'MyDatabase' cannot be started in this edition of SQL Server because part or all of object 'containedmemebersnapshots' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition.

As a result, my database is in suspect mode.

Is there any resolution so I can restore this database onto SQL Server Standard Edition?


texts:
SELECT DISTINCT re.*
    FROM `reports` r
    LEFT JOIN `users` u
    ON u.`id_rep` = r.`id`
    JOIN `customers` c
    ON u.`id` = r.`uid`
    WHERE r.`uid` = '1' and r.`name` LIKE 'urgent'
    ORDER BY r.date DESC

Without making any modifications prior to the WHERE clause, would it be possible to use UNION (or a better approach) after the LIKE statement in order to SELECT from a fourth table called workers and ORDER the results BY r.date DESC?


texts: What will be the query to add UPPERCASE Constraint to a column of pre-existing table? The question demands to ensure that the entries are in UPPERCASE.


texts: SQL Server Disk throughput slower than diskspd

texts:
This is a bit of a broad question but I am trying to understand a storage performance behavior with two of our servers.

I was following https://www.brentozar.com/archive/2015/09/getting-started-with-diskspd/

On one server I ran dskspd with the below parameters on the same disk as the DB.

diskspd.exe -b2M -d60 -o32 -h -L -t56 -W -w0 -c10G G:\MP13\Data\test.dat

and got around 1400MB/s

I was also able to get comparable throughput using a T-SQL query as below and calculating the throughput from the number of reads and time.  I got this from Glenn Berry SQL Course on PluralSight "Improving Storage Subsystem Performance
".

set statistics io on
set statistics time on
checkpoint
dbcc dropcleanbuffers
select count(*) from table1 with(index(1))

On the other server though, I can get the high throughput numbers from the diskio tool but using SQL server method I am not able to get the throughput numbers.  the SQL numbers are close to what I get if I run diskspd in single thread, even though the plan is running in parallel.

So I was wondering what are the things I can check to see why SQL Server's IO is slow or why SQL Server is not able to push more IO's through.


texts: SQL Server Partitioning - what to use for partition key?

texts:
I've never worked with SQL Server partitioning but I currently faced with designing a database for which the volumes probably warrant it. The system is for coupons. The coupons are to be issued periodically, usually every six weeks although there will also be ad-hoc issuance - eg for a special event. There are 15 million customers and for each issuance event, every customer will receive 6 different coupon types, giving a total of 90 million coupon instances. We need to track the coupon instance redemption data and maintain this for 6 months, although typically a coupon is only valid for six weeks. Any redemption request for an invalid coupon will not reach the database because it will be validated by the POS till.

Over a six month period we'll need to store up 360 million rows in the Coupon Instance table and up to 72 million (assuming max 20% redemption rate) in the Redemption table. I get the feeling that these numbers are too big for a single partition?

My question is - what to use as the partition key? One obvious candidate would be by issuance event, giving approximately 6 partitions. But then I think that maybe even that would give a partition size that is too large to allow for optimal performance? Would it be possible to partition by two keys eg by issuance event + last digit of the customer id? So the logic would be:

If issuance event = 1 and last digit of customer id < 5 then
    Store in partition 1
Else if issuance event = 1 and last digit of customer id >4 then
    Store in partition 2
Else if issuance event =2 and last digit of customer id <5 then
    Store in partition 3
Else if issuance event =2 and last digit of customer id >4 then
    Store in partition 4
Etc...

Also, I'm not sure of the spec of the database server that we'll need. Will 16gb and 8CPUs be enough? The db needs to be able to return a result from the coupon instance table, keyed on a numeric barcode value in less than half a second. The expected transaction request for validate (select) and redeem (insert) is expected to peak at approximately 3,500 per minute.

The SQL Server 2008r2 64bit db server will be provisioned as VM from a very powerful host with access to a high performance and large capacity SAN.

I'd be very grateful for any advice from those that have deployed a SQL Server solution to manage similar volumes.

Regards

Rob.


texts: Always On Availability Groups with Encrypted Connections

texts:
We have a two-node SQL Server 2016 AOAG, TestA, TestB and listener TestL. We want to begin using encrypted connections between the app server/client and the db server.
I have found the section below in two MS documents about implementing connection encrypting. Just wanted to know if this holds true for AOAGs? I suspect it does since AOAG does require the use of a failover cluster, but would like confirmation.

Encryption on a Cluster
If you want to use encryption with a failover cluster, you must install the server certificate with the fully qualified DNS name of the failover clustered instance on all nodes in the failover cluster. For example, if you have a two-node cluster, with nodes named test1.your company.com and test2. your company.com and a failover clustered instance of SQL Server named fcisql, you must obtain a certificate for fcisql.your company.com and install the certificate on both nodes. To configure the failover cluster for encryption, you can then select the ForceEncryption check box on the Protocols for  property box of SQL Server Network Configuration


texts: select duplicated rows by modified_date - PGSQL 9.2

texts:
Query:

SELECT j.id INTO junk.ja_jobs
FROM (
  SELECT time_job, recurrenceid, clientid, creatortype, deleted, MIN(id) AS id
  FROM ja_jobs
  WHERE clientid = 33731 AND creatortype = 'legacyrec' AND deleted = false
  GROUP BY time_job, recurrenceid, clientid, creatortype, deleted
  HAVING count(*) > 1
  ) jd
JOIN ja_jobs j USING (time_job, recurrenceid, clientid, creatortype, deleted)
WHERE j.id <> jd.id;

The query above gets all duplicated rows by time_job,recurrenceid and put the them into the temp table. Only the oldest ID stays in the ja_jobs table.

ja_jobs table has created_date and modified_date column, as you can see below:

Question:

Find duplicate recurring jobs with identical recurrencid and time_job
and creator_type = 'legacyrec'
If created_userid = modified_userid AND the difference between
modified_date and created_date is smaller than 10 seconds (since
created_date is not necessarily smaller for strange reasons), we
treat them as not modified
If there are matches in step 2, we keep all of them and delete the rest, and if there is no match, we keep the oldest one and delete the rest

How can I do that? How would the select statement be?


texts:
I have a Wordpress site that uses WP Super Cache plugin in order to make my blog posts and pages stay as HTML so that less PHP/MySQL code is executed. However, there's a featured box that can't get cached. So, I configured it manually to bypass cache and stay dynamic (code is executed on every page load).

So, most part of the entire page is executed using HTML and a specific part (a featured bar box) uses a PHP/MySQL.

The code that runs on every page load are some simple <?php $query = mysql_query("SELECT * [... rest of my code here ...]"); ?> to select some data from my database and some $fetch and <?php echo [...] ?> to print the results in the webpage, of course. But instead of everytime calling database to get this results, it would be much better to cache this results. So, I have three options:

Let WP Super Cache caches my entire page. But I can't do that, because inside my box that currently isn't getting cached, the database data can change anytime (normally 1 time per day). So, I can't cache this box and we have to discart this option 1.
Activate query_cache in my.cnf. However, this will only cache MySQL and don't PHP. :(
Than, we have memcache (or APC, I don't know much about it's difference). After all my explanations, here's the final question: I'd like to know if installing memcache in my server is enought to get it working imediatally or I have to make any changes in my code to adapt it with memcache; I'd like to know if it will really help me with both PHP and MySQL for this specific part of code I have to cache or it's results is similar to enabling query_cache? Or is using APC rather than memcache better in my case?

Thanks,
André.


texts: Returning a json array slice in PostgreSQL

texts:
Postgres 9.5

Given a table with one field of type json, about 700 rows, and where each row has about 4,000 elements in a single array...

my_db_field:

[0.44577, 0.4855, 0.45429, 0.54437,...]
[0.45012, 0.48698, 0.45715, 0.55337,...]
[0.47347, 0.49156, 0.46079, 0.56818,...]
[0.4936, 0.49835, 0.46086, 0.58195,...]
[0.51068, 0.50511, 0.46228, 0.59482,...]

The PostgreSQL docs show how you can query for a single element inside an array.

 SELECT my_db_field->2 AS test FROM my_db_table

results:

test (of type json)
--------------------
0.4855
0.48698
0.49156
etc.

What I would like to do, though, is select multiple elements in the array and return that as an array in the same format the rows are. By multiple, I mean around 300 elements in the array; e.g., from element 0 to element 300. Does Postgres have a nice syntax for such a query?


texts:
I would need help with my SQL foo. I have 3 tables like this

TBL LEAD
id
email
activity_tracking_id
...

TBL ACTIVITY_TRACKING
id
uid
created_ad
action
referer
...

TBL BUYERS
id
email
...

Table activity reference itselft using uid field. Buyer is related by buyer.email to lead.email. Pageview is a value of activity_tracking.action

As a lead navigate a website, a log in TBL ACTIVITY TRACKING is registered. Referer is important here, I want to know how many buyers came from each distinct referer (Facebook, Instagram, Google, etc). Problem is, referer is external only on first pageview, afterward, the referer is the previous page on the site, following navigation.

Also, a user can visit again using a new referer or the same, I need only 1 once  per lead per referer.

How should I build my query to have something like this :

Referer | Count
Instagram 12
Facebook  25
Google    20

For info, I'm using MariaDB.

Thanks for your help.


texts: What does numeric operations mean in a sum in sql expression?

texts:
SUM(rating IN (4.5, 5))/COUNT(*)*100 AS pct_5_star,
SUM(CASE WHEN rating IN (4.5, 5) THEN rating ELSE 0 END) AS in_5_star

I don't know why I am finding it difficult to understand what exactly is happening in the above statement and what exactly is /COUNT(*)*100 doing here.


texts: transactional replication-snapshot agent failure ... can anyone help me out

texts: configured transactional replication ..source acting as publisher and distributor,target server acting as subscriber.. but I got error for snapshot agent at 93 % saying that in below screenshot


texts: MySQL Performance: conditionnal left outer join vs where only

texts:
I have three tables:

FORUM ( id PRIMARY KEY, public BOOLEAN )
MESSAGE ( id PRIMARY KEY, content VARCHAR(255), user_id NULL FOREIGNKEY user.id, forum_id FOREIGNKEY forum.id)
USER( id PRIMARY KEY, username VARCHAR(25) )

So, basically, one message can belong at most to one user, or can be anonymous. On the other hands, one user can have multiples messages. Messages always belong to a forum, which can be "public" or not.

I have to create this query: return true if the specified forum is public, OR if the specified forum contains at least one message from a specified user.

SQL:

SELECT EXISTS (
    SELECT 1
    FROM `forum` AS `f`
    LEFT OUTER JOIN `message` AS `m` ON `m`.`forum_id` = `f`.`id`
    INNER JOIN `user` AS `u` ON `u`.`id` = `m`.`user_id`
    WHERE `f`.`id` = 10 AND (`f`.`public` = 1 OR `u`.`id` = 5)
    LIMIT 1
) AS `boolean`

Such query works great. However my concerns is about performance. In my head, if the forum is not public, InnoDB has to do complete join between MESSAGE and USER, then filter the results with u.id = 5.

So If I wrote the query differently:

SELECT EXISTS (
    SELECT 1
    FROM `forum` AS `f`
    LEFT OUTER JOIN `message` AS `m` ON `m`.`forum_id` = `f`.`id`
    LEFT OUTER JOIN `user` AS `u` ON `u`.`id` = `m`.`user_id` AND `u`.`id` = 5
    WHERE `f`.`id` = 10 AND (`f`.`public` = 1 OR `u`.`id` IS NOT NULL)
    LIMIT 1
) AS `boolean`

The point of the second query is a new condition into the ON clause, to prevent InnoDB to do joins of the whole tables.

My question is: what's the fastest query ? Thanks!


texts:
I have a drive named J drive, where all temp db files(log and data files), main database log files and another database log and data files(which holds intermediate processing tables) exist.This drive has 1.5 TB space, and above mentioned files occupy 1.2 TB space and fluctuates extremely(as it contains log and temporary processing tables).

Main db database files(primary and secondary) exist on another drive( K drive) which has 2 TB space, and occupy around 1 TB space.

J drive has high I/O, as a  lot of calculations and processing exist in our DB.
I am planning to separate data and log files for each DB (Main DB,Temp DB and temp_data_processing db ) to J and K drive to reduce I/O.Will it help reduce I/O in the drives ?

Will it be better to add another drive ( e.g., P drive) and place temp DB log and data files in that drive ?

Thanks in advance for your kind reply !!!


texts:
I'm a student and i'm doing my own database. But i have some questions that i can't answer yet. So, here is my question:

What is better to use in terms of efficiency and security, Proxy tables or Remote procedures?


texts: Parallel insert and delete into staging tables

texts:
I work on a small system that receives quite a lot of data from multiple sources (hundreds of them). Depending on few factors I can get anywhere from few rows to a couple of thousands (in fact smallest message contains single row and largest contains at most five thousands, but if more data should be loaded then I get this data in few batches). Currently data is processed by few identical services. Each service load data into one of five staging tables and executes procedure that does the right thing - each staging table has different procedure associated with it, but it always boils down to changing unique identifiers (e.g. UUIDs) to proper database identifiers, inserting data into destination table and removing data from staging table. All procedures have this form:

INSERT INTO TARGET (A, B, C)
SELECT T1.ID, B, C FROM STAGING_TABLE
JOIN T1 ON A = T1.ID

DELETE FROM STAGING_TABLE

It is possible for all services to work on single staging (and destination) table in parallel. Currently this is done using snapshot isolation, but it is painfully obvious that for one reason or another we are losing data. What I mean by it is that there are messages that are properly processed by the services but all information from them is lost - we don't see records in the database. I can't prove that snapshot isolation is in responsible, but such incidents started to happen after snapshot isolation was introduced - which in turn was introduced after services loading data in parallel were introduced. Databases are currently far from my main field of expertise and I don't know why it happens, but it seems that snapshot isolation is the main culprit.

My question is: what is the lowest isolation level that can support this scenario? Is there better way to do it? I'm not fully aware what transaction isolation level was used earlier (when data was loaded by single service), but we never observed data loss. I tried (blindly) using "serializable" and "repeatable read", but "serializable" results in dropped messages due to deadlocks and "repeatable read" while seems to do the right thing (no data loss) also degrades performance to the level of serial writing.

EDIT:
Is it viable to load data using snapshot isolation, insert into temporary table (or table variable), THEN switch to some very permissive isolation level, insert data from temporary table into target table, revert to snapshot and delete data from staging table? If I read this correctly:

https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms173763(v=sql.90)

It should be possible, but I don't understand yet if any of this would have any effect in discussed case - target table is not read, only written in this scenario, and I think this means that write won't be any "more parallel" than under snapshot isolation. But maybe I'm wrong?

Note that we can't wait and for example load data from multiple sources into single staging table and then move it into target table. We're not aiming for real time, but we need data insert ASAP.


texts: Can I restore an uncompressed differential backup after a compressed full backup?

texts: If we enable compression on database backups (because the database backup size is huge) but don't enable it on differential which are relatively small in size, will a restore work? Because the full backup is compressed but the differential isn't compressed.


texts:
Here is the situation.  I have restored a database to replace an existing version (need to get it back to yesterday at a certain time).  So I do a RESTORE DATABASE ... WITH NORECOVERY, REPLACE, etc... with no problem.

There are about 16 transaction logs that I need to then restore as well to get the database to the point in time that I need it.  The first RESTORE LOG completes with success.  The second one tells me 100% complete, but immediately after that I get this message:

An error occurred while processing the log for database 'MyDb'.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

What exactly does this mean??  I did some research and it looks like a few things can cause this, and none of the examples/situations I read about had to do with a RESTORE LOG.  Does this possibly mean my transaction log backup is corrupt??

Is there any remedy to this?


texts: Issue when connecting to open shift via mysql workbench on Mac

texts:
I am trying to connect to open shift with mysql workbench on an iMac. A colleague has shown me his setup on Windows which looks exactly as follows (other than the usernames / passwords changing)

However I keep running in to the following error and I have no idea how to fix it - I am 100% sure my password is ok, so don't really understand why it would say password yes.

Is there any special configuration differences needed between a Mac and PC setup, or any setting/config you can think of that might solve this issue?


texts:
I am using RAP tool for SQL Server, I get the following alerts in RAP assessment report:

  SQL Server service is running under an account that is a member of the built-in Administrators group

Solution or suggestion provided by MS is

Create a new domain user account, MSA (Managed Service Account) or gMSA (Group Managed Service Account) without assigning any rights or permissions. Use SQL Server Configuration Manager to assign the account to the SQL Server service.
Avoid using the same Service Account for multiple SQL Instances, only use the same account for the instances that are member of the same cluster or availability group.
Review the Context and Best Practices and Learn More sections for additional considerations and details.

I am really confused exactly what i have to do or change to remove this Alert.
SQL Server is running under account MAD\SPITOPCMSQL01

What do I have to change so that this login is not member of built-in administrators?

Please help


texts: Fetch large data from Sql Server and process in chunks

texts:
I am working on an API which would need to process large dataset. The query to select records is complex and takes time to complete. I cannot use the pagination using ROW_NUMBER as not all records returned by query will be marked closed. The same records (potentially all but not necessarily) may get reselected by the query.

I am thinking to use global temporary table as working set. Below is my approach:

API will first create the global temporary table.
API will execute the query and populate the temp table.
API will take data in chunks and process it.
API will drop the table after processing all records.

The API can be scheduled to run at an interval of 5 or 10 minutes. There will not be concurrent instances running, only one instance will run at one time.

Do you see any issues with the usage of global temporary table? Would a permanent table be more appropriate in this scenario?


texts:
I am working on a project that needs a time clock solution.  The point of sale software they use has a clock in/clock out function built in.  But if an employee misses a punch or something needs to be modified the software does not have that built in. It also has a function of logging what type of break an employee takes.
My issue is this: The punch in and out data is in one table, the break punch in and out data is in another table.
Here is the data that I have in the database now.  I am just learning this and playing with fake data:

The startshift is the punch in time, the end shift is the punch out time.  Those are in 1 table.  The start time and the endtime are a break.  The name is the employee.
There should only be one break on one day but the line show up on every row pulled from the one table.
Here is my SQL. I apologize that this is probably a really newbie thing, but I am trying to help out a friend.
SELECT startshift, endshift, starttime, endtime, `NAME`
from shifts
JOIN people on shifts.PPLID=people.id
JOIN shift_breaks on shifts.PPLID=BREAKID
where PPLID="0"
ORDER BY STARTSHIFT

Here are my table columns:
People

Shifts

shift_breaks

The query returned the results I have above.  If you notice each line has a different startshift, and endshift, but the starttime and end time which are the actual break taken shows up in each row returned.  It should actually show up only in one row as only one break was taken in those 3 days.  When a person punches in the time gets logged into shifts as startshift, when they punch out it gets logged into shits as endshift. If they punch out for a break it gets logged into shift_breaks as starttime. when they punch back in from a break it gets logged into shift_breaks as endtime.


texts: Does SQL Server 2005 Express have a problem with installing on single user systems?


texts:
SQL Server version is 11.0.5569.0.

The user/login for the process on a server is as follows:

S-1-9-3-412646706-1242810017-220819094-3312857753

This is not an orphaned user (as suggested below) because I checked that before entering this post.

I looked at the items running using Ideradm, sp_blitzfirst, and sp_whoisactive.  The explain plan indicates that this is working with a couple of temp tables that it obviously created in a prior step.

When I ran a script against the 'principals' tables, NADA, nothing.

Any other suggestions?


texts: Hash Match in Query has high cost and high number of excecutions

texts:
I have the following query:

      declare @jobID NVARCHAR(20)
    declare @dataGroup BIGINT
    declare @lastSyncVer BIGINT

       set @jobid='P-0001'
       set @dataGroup= 5637144576
       set @lastSyncVer = 316244734

SELECT us.[DATASTOREID], us.[UPLOADSESSIONID], us.[UPLOADPATH], us.[STATUS], us.[DATEUPLOADED], us.[DATECREATED], us.[CHECKSUM], us.[FILESIZE], us.[ID], us.[RERUNFOR]
        FROM [dbo].[UPLOADSESSION] us
            INNER JOIN CHANGETABLE(CHANGES [dbo].[UPLOADSESSION], @lastSyncVer) AS CT ON us.[ID] = CT.[ID]
            INNER JOIN [dbo].[DATASTORE] ds ON us.[DATASTOREID] = ds.[ID]
            INNER JOIN [dbo].[DATAGROUP] dg ON ds.[DATAGROUPID] = dg.[ID]
        WHERE dg.[ID] = @dataGroup
          AND us.[JOBID] = @jobID
                AND us.[RERUN] = 0
        ORDER BY us.[ID]

When I run a query cost analyze of the query I see that the Hash Match takes up 83% of the cost. I also see that the hash match has a really high number of executions(over 800k). The main problem with the query is that it is very slow. I have let it run for over 40 minutes, and it still didn't finish. The query is written by Microsoft, and is part of their functionally in their ERP system Microsoft Dynamics AX. Can anyone come up with some ideas how we can speed things up? This query used to take under 30 seconds.

Perhaps the problem isn't in the query itself, but in other problems with the DB? I have tried updating stats.


texts: Attempted to import .csv into mySQL using workbench, but only imports 7 rows?

texts:
To start, I'm still a relative SQL noob so this question could have an obvious answer that I'm just missing. I'm trying to start a big practice project in my spare time using a dataset which has ~191000 rows and ~150 columns of data.
Attempting to use mySQLworkbench's table data import wizard to import the dataset after saving it as a .csv. When I run the import process, it successfully imports only 7 rows but doesn't give any error messages to indicate what stopped it.
What is preventing more than 7 rows from importing? How do I fix it?
Thanks for any help.