texts: Type of relational/object-oriented database with improved joins for object graphs?

texts:
I would like to know if there is a specific name or term for the kind of database described below:

I notice one of the biggest problems when working with relational databases and object trees/graphs is fetching of sub-/child-/grandchild rows(objects in code), especially over many hierarchy levels.

Doing this by join results in a kind of cartesian product between result rows. Example with only two top level table rows and three levels in hierarchy:

SELECT p.[columns], c.[columns], gc.[columns]
FROM Parents p
LEFT JOIN Children c ON p.ID=c.ParentID
LEFT JOIN Grandchildren gc ON c.ID=gc.ParentID
WHERE p.Name in ('A','B')

-- result:

Parent A | Child AA | Grandchild AAA
Parent A | Child AA | Grandchild AAB
Parent A | Child AA | Grandchild AAC
Parent A | Child AB | Grandchild ABA
Parent A | Child AB | Grandchild ABB
Parent A | Child AC | Grandchild ACA
Parent A | Child AC | Grandchild ACB
Parent B | Child BA | Grandchild BAA
Parent B | Child BA | Grandchild BAB
Parent B | Child BB | Grandchild BBA
Parent B | Child BB | Grandchild BBB

So, even a few results with all sub-elements quickly make hundreds or thousands of rows, and if the parent table has tons of heavy NCLOB/BLOB/NVARCHAR(MAX) columns, there's a HUGE amount of data to be transmitted as duplicates.

Some object-relational mappers work around this by SELECTing child and grandchild rows by their parents' IDs (the good ones loading them in groups/batches, not single SELECTs for each parent row!), but this is likely not transactional (subordinate/child rows may have been changed after parent rows were selected, not possible in a join - and of course you don't want to do this in "serializable" isolation level).

A possible solution would be to transmit join results in a different format: instead of duplicating parent rows, they would transmit them only once, plus a reference between them and subordinate rows (likely the original PK/FK). In the most simple case, multiple result sets from all joined tables would be transmitted, with each row (or row projection) only once, to be easily transformed into objects by the client application:

Parent A | Child AA | Grandchild AAA
                    | Grandchild AAB
                    | Grandchild AAC
         | Child AB | Grandchild ABA
                    | Grandchild ABB
         | Child AC | Grandchild ACA
                    | Grandchild ACB
Parent B | Child BA | Grandchild BAA
                    | Grandchild BAB
         | Child BB | Grandchild BBA
                    | Grandchild BBB

only 2 parent table rows and 5 child table rows. The whole thing as transactional as a classic join. If somebody wants a classic join result (for example, when using classic relational in-memory structures like ADO.NET DataSets), the person can easily transform it from the multi-table or "jagged" to the traditional, bloated "rectangular" join result.

The data backend could still be relational and have tables, but object graphs and joins would be much better to handle.

There have been multiple buzzwords around in the recent decades, including object databases, XML databases, graph databases, some of which claim to keep full relational integrity and ACID between all elements (I do not mean document only or non-/partially ACID databases in the NoSQL area - I mean with full ACID, also indices transactional, OLTP usable, but possibly limited scalability).

Also, the DB should not be too close to specific programming languages, but basically provide sets of common data types, which can be loaded into or extracted from language specific data structures.

The abovementioned database types may fit my definition, but in my idea, there should be a schema and table-like structures which allow both retrieval of object graphs and large numbers of rows and related rows by conditions. I am not sure whether object or graph databases are internally heap or table (relational) organized.

Can anybody name me the DBMS type which fits my definition, possibly name products?


texts:
I am using a Postgres DB to implement a scheduling of jobs for a large number of computers/processes. To make the story short, every job has its id, all scheduling is implemented with three tabes: all jobs, currently running jobs, and already completed jobs.

The key functionality of the scheduling is to (1) request a job and (2) inform DB about a completed job. Requesting a job takes any id from the job list, which is not in the running table and not in the completed table:

insert into piper.jobs_running
select x.fid from (
  SELECT fid FROM piper.jobs
  except
  select fid from piper.jobs_running
  except
  select fid from piper.jobs_completed
 ) as x limit 1
returning(fid)

Completing the job removes it from the running list and inserts it into the completed list. Since it is not concurrency-specific I ommit the SQL commands (it takes something from tens of minutes to a couple of hours to finish a job.)

It came as a nasty surpirse to me, that two processes runing exactly the same query above (pretty much requesting the job at the same time) may get the same job id (fid). The only possible explanation that I am comming up is that Postgres is not rely ACID conform. Comments?

Additional information: I set the transactions to be serializable (in postgresql.conf set default_transaction_isolation = 'serializable'). Now the DBMS fails the transactions in case the isolation is not fullfilled. is it possible to force Postgres to restart them automatically?


texts: Indexed query fails to return rows in large (15 billion rows) MyISAM (MySQL) table

texts:
I have a very large table (15 billion rows) with a structure like this:

CREATE TABLE `textlines` (
  `PGUID` binary(16) DEFAULT NULL,
  `StatID` int(11) DEFAULT NULL,
  `Time` datetime(3) DEFAULT NULL COMMENT 'local time',
  `UTCOffset` float DEFAULT NULL COMMENT 'UTC offset: UTC time = Time (local) -
UTCOffset',
  `Text` varchar(255) DEFAULT NULL,
  KEY `TimerOrder` (`StatID`,`Time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

The table is loaded with a LOAD INFILE call on flat files.  (I disable keys before loading the table and enable keys after). I have also tried creating the table without an index and adding the composite index (StatID, Time) after the table is loaded.  Logs show no errors whatsoever. Once it is finished, if I query the data, e.g.,

>select count(*) from textlines where StatID=1014;

I get zero results returned (and no errors reported).  I know the data is in the table, which I verify by e.g.,

>select * from textlines limit 10;

| PGUID         |  StatID   | Time                                | UTCOffset | Text                                      |
+---------------+-----------+-------------------------------------+-----------+---------------------------------+
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:14.009 |        -4 | Could the black cat   |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:17.010 |        -4 | collapse the limit.   |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:20.011 |        -4 | And never shall there be, |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:21.012 |        -4 | fast the pulse of america.     |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:23.013 |        -4 | retire to the front.      |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:26.014 |        -4 | Also tuna is delightful, |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:29.015 |        -4 | mark is an avid fisherman |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:30.016 |        -4 | chapter six scene eighteen     |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:33.017 |        -4 | would be detrimental to  |
| YId??ÆD2½Am?\>av          |      1014 | 2012-09-12 04:36:36.018 |        -4 | replace the last cog.                |
+---------------------------+-----------+-------------------------+-----------+---------------------------------+
10 rows in set (0.00 sec)

I have tried partitioning the table (by HASH(StatID)).  Again the indices for each partition build fine, but the problem of missing rows when querying using the index persists.  I have also tried reducing table size by importing only some of the data into the table (e.g., only 1 billion rows) -- again with the same results.

The issue seems to be similar to the one experienced here, as the results seem to be missing in groups.

I am using MySQL 5.6.19 x64 on Windows 2012 Server with 128GB RAM.

The strange thing is, the same approach described above seems to have worked with a somewhat smaller table (250 million rows) on an earlier version of MySQL (5.6.12) with no issues.


texts: Query slows with additional FROM item (even with no extra actions)

texts:
I have a locally hosted table comment on Postgres 9.5 with ~ 2.6 million rows. When I run the following query, it executes in .867 ms:

SELECT COUNT(body)
FROM comment
WHERE body LIKE '%[deleted]%';

But the following query takes > 50 seconds (stopped measuring at this point):

SELECT COUNT(c1.body)
FROM comment c1, comment c2
WHERE c1.body LIKE '%[deleted]%';

I ask because I'm trying to implement a query like the following:

SELECT CAST(COUNT(c1.body)/COUNT(c2.body) as percent_deleted
FROM comment c1, comment c2
WHERE c1.body LIKE '%[deleted]%'
AND c1.id = c2.id;


texts:
I can see the owners of my databases usign this query

SELECT  name ,
        SUSER_SNAME(owner_sid)
FROM    sys.databases

What are the things I should consider when choosing an account for database owner (account fro dbo user)? I'm thinking about a scenario when I assing normal admin account for database owner and when the time passes this account is disabled from AD when the ownwer of this account leaves his job. Is it good practice to assign a system account for database owner?


texts:
I am trying to run below query in Maria DB (10.1.26-MariaDB-0+deb9u1 Debian 9.1)

with ind as
(
   select index_name,
          table_name,
          group_concat(column_name) column_name,
          index_type
   from   information_schema.statistics
   where  table_schema = 'mydb'
   and    index_name != 'primary'
   group by 1,2,4
)
select concat('create index ', ind.index_name, ' on ', ind.table_name,
              '(', ind.column_name,') using ', ind.index_type, ';')
from   ind;

Im running the above query and getting some syntax error. What I am missing?


texts: How to find which edition the SQL Server installation media belongs to

texts: Without installing SQL Server media 2008 and 2012, how can we know that the media belongs to a certain edition (like Developer, Standard or Enterprise)?


texts: Oracle RAC 11g R2 DB silent install: parameter needed for system class

texts:
I'm installing Oracle RAC DB 11gR2 using response file and silent mode. The installation is failing with the error message:

  [INS-35421] This options installs a single instance database only
  You have chosen to perform a Desktop class install on a cluster. This options will not install oracle RAC

Which parameter is needed for a server-class install? I tried changing the value for oracle.install.db.config.starterdb.control from DB_CONTROL to GRID_CONTROL, but I still get the same warning.


texts: Multiply by value from related row with latest date

texts:
I have two tables, one with staff time entries, another with staff rates starting from a particular date:

time_table

id | staff_id | entry_date | hours
----------------------------------
1  | 1        | 15-01-2019 | 1
2  | 1        | 15-02-2019 | 2
3  | 1        | 15-03-2019 | 3
4  | 2        | 15-01-2019 | 4
5  | 2        | 15-02-2019 | 5
6  | 2        | 15-03-2019 | 6

rates_table

id | staff_id | start_date | rate
----------------------------------
1  | 1        | 01-01-2019 | 1
2  | 1        | 01-02-2019 | 2
3  | 1        | 01-03-2019 | 3
4  | 2        | 01-01-2019 | 4
5  | 2        | 01-02-2019 | 5
6  | 2        | 01-03-2019 | 6

I would like to multiply the time entries by the rate for that staff member that is the most recent, occurring on or before that time entry.

I have this query but I have no idea how to select the most recent rate that occurs before the time entry:

select t.staff_id, t.entry_date, t.hours * r.rate as total_rate
from time_table t
left join rates_table r on r.staff_id = t.staff_id and r.start_date < t.entry_date;

https://rextester.com/DDK49143

I would like a result like the following:

staff_id | entry_date | total_rate
----------------------------------
1        | 15-01-2019 | 1
1        | 15-02-2019 | 4
1        | 15-03-2019 | 9
2        | 15-01-2019 | 16
2        | 15-02-2019 | 25
2        | 15-03-2019 | 36

How could I do this in Postgres?


texts: Grant sysadmin permissions to 'NT AUTHORITY\SYSTEM'

texts:
For SQL Server 2012 and above, as What's New in SQL Server Installation states:

  BUILTIN\administrators and Local System (NT AUTHORITY\SYSTEM) are not automatically provisioned in the sysadmin fixed server role.

In my case this has become a problem: I have a WiX installer which uses a Custom Action C++ code to setup database data (create the database, views, procedures, data etc.). Custom Action runs as user NT AUTHORITY\SYSTEM, but this account does not have permission to execute CREATE DATABASE scripts.

Is it correct to give sysadmin role to NT AUTHORITY\SYSTEM while installing software? Or maybe there is a better solution?
If it is correct, is it possible to find the proper user name for NT AUTHORITY\SYSTEM? I need this name in order to change permissions*. In different locales this name is different, for example, NT AUTHORITY\SYSTEM or NT AUTHORITY\СИСТЕМА.

Adding NT AUTHORITY\SYSTEM to the sysadmin role:

IF NOT EXISTS
(
    SELECT name
    FROM master.sys.server_principals
    WHERE IS_SRVROLEMEMBER ('sysadmin', name) = 1
    AND name LIKE 'NT AUTHORITY\SYSTEM'
)
EXEC master..sp_addsrvrolemember
    @loginame = N'NT AUTHORITY\SYSTEM', @rolename = N'sysadmin'

Searching sys.server_principals for name LIKE 'NT AUTHORITY\%' will not work because sometimes there will be more than one NT AUTHORITY\... user. I encountered a case like that on one of my virtual machines.


texts: Optimize schema and query for overlapping date ranges query

texts:
We have a database where we track the price of products from different sellers. If I strip everythin else it looks like this:

I need to make a report where given a date range I have to return a row for every day and the prices for every seller. There will be days that some seller don't have a price for a product and there will be days that a seller will have more than one price for a given product.

I'm doing it by creating a temp table with the date ranges then join them with the sellers and prices for this but once product_price have a couple thousands rows it get VERY slow (about 4'' to return a 200 rows) and I don't know how to optimize it to get it fast enought.

I can't think a workaround for the temp table nor which index should I create.

schema and query here: DB-fiddle


texts: Best practices for transferring data across long, wide tables

texts:
As part of my data warehouse project, I'll be transferring data from our OLTPs to the Data Warehouse. Some of the tables are long and wide so obviously, I'll only be transferring required columns.

To reduce the overhead, I'm considering data tracking on these tables so we only look at changed values rather than scanning the complete table. Unfortunately our source system runs on SQL Server 2014 Standard Edition and CDC is an option we can not take advantage of.

Is there an alternative method that would be better than change tracking?


texts:
we have been struggling to understand this behabiour on mariadb. we have some jobs that run every minute and they do a lot of updates on some metadata tables inside mariadb, however we have noticed that simple updates where id=X are taking more than 5 seconds, and when we get the show engine innodb status we get the following:

--------------
ROW OPERATIONS
--------------
16 queries inside InnoDB, 266 queries in queue
0 read views open inside InnoDB
51 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
51 out of 1000 descriptors used
Main thread process no. 389, id 139921595545344, state: sleeping
Number of rows inserted 2770109, updated 5194512, deleted 907329, read 35768640153
1.36 inserts/s, 69.81 updates/s, 0.64 deletes/s, 923868.28 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

We think that the CPU is strugling to handle all 300 updates at the same time. Can you please help me understand this behaviour?


texts: How to count in relational algebra without aggregate functions?

texts:
Find the user who has liked the most posts. Report the user’s id, name
and email, and the id of the posts they have liked. If there is a tie,
report them all.

The schema:
User(uid, name, website, about, email, phone, photo)

Likes(liker, pid, when)

Likes[liker]  is contained in  User[uid]

I think I need a "function" NumberOfLikes or similar where I can do something like:

We aren't allowed to use aggregate functions in this exercise. I assume the way to count in RA is by performing some sort of cross product black magic, but I don't know how.
Help?


texts: how is synchronization maintained between different write and read database

texts:
One of my friends was asked this question in interview. Given two different databases one on which only write is done and the other on which only read is done. How does one maintain synchronization between the two given a specified time lag. I would like to add that these databases have same schema.

Eg: R is my Read database and W is my Write database. I want to ensure that read database should be the replica of Write database say a minute earlier.

What I am keen here is on the techniques(concepts) this is done rather than the implementation. Lets say the RDBMS is SQL Server.


texts: Is using dollar quoting $$ and removing $ from input enough to avoid SQL injection in PostgreSQL?

texts:
I'm coding some logic in embeded Freeswitch LUA and built in database handling
does not allow parametric input for SQL, so I need to escape the input.
I'm sure my username should not have $ symbols in, is it safe to escape
all $ symbols from input and use dollar quoting or there are some
workaround to hack and insert injection here?

username = string.gsub(username,'[$]','')
string.format([[SELECT id FROM users WHERE username =  $$%s$$]], username)


texts:
I am new to mysql cluster I was reading around the web how to set it up. I came up with 2 different set up not sure which one is better fail safe vs cost efficient. Our new site have clients in USA and Japan. We are not big site, and saving money is high priority for us.

V1)

US Servers, Japan Servers

Front End 3 servers
  Apache
  local memcached + connects to mysql memcached

Mysql cluster
  One management node (This will be aware of japanese/us management node)
  Two data Nodes
  Two Sql Nodes

V2)

US Servers, Japan Servers

Front End 3 servers
  Apache
  local memcached + connects to mysql memcached

Mysql cluster
  One management node (This will be aware of japanese/us management node)
  Two data Nodes/ Sql Nodes combined

Thanks


texts:
Which query is faster:
Query A
SELECT I.ItemName
FROM Location L
INNER JOIN Items I ON I.LocationID = L.LocationID
WHERE L.LocationID = 1

Execution Plan

Query B
SELECT ItemName
FROM Items
WHERE ItemID IN (
SELECT ItemID
FROM  Items
WHERE LocationID = 1)

Execution Plan


texts:
I understand that you cannot have ORDER BY in a view. (At least in SQL Server 2012 I am working with)

I also understand that the "correct" way of sorting a view is by putting an ORDER BY around the SELECT statement querying the view.

But being relatively new to practical SQL and the usages of views, I would like to understand why this is done so by design. If I've followed the history correctly, this was once possible and was explicitly removed from SQL Server 2008 and so on (don't quote me on the exact version).

However, the best reason I can come up with as to why Microsoft removed this feature is because "a view is an unsorted collection of data".

I am assuming there is a good, logical reason as to why a View should be unsorted. Why can't a view just be a flattened out collection of data? Why specifically un-sorted?  It doesn't seem that hard to come up with situations where (at least to me / IMHO) it seems perfectly intuitive to have a sorted view.


texts:
I have 2 MySql nodes with Master-Master Replication set-up.
MySql1 has read and write access and is in current state.
MySql2 has only read access and stopped working due to space issue.
How can I fix this issue and resync it.


texts: Running REINDEX in a live environment (POSTGRESQL 9.6.10)

texts:
I have a live app where I have some big tables (~100+M rows), which I recently truncated to ~20M rows. One behavior I've noticed after making the said change is that DB response time has escalated in some cases (SELECT queries highly affected). I've been doing research on why this could have happened.

One thing that has popped up is that I may need to reindex the indexes on these tables - original indexes may have become fragmented/unoptimized. However, it being a live environment, that could impose write locks. I've read that a better way to do this is:

Create an index concurrently with a different name,
Drop the original index
Rename the new one to the old one

Being an accidental DBA, I want to be sure about how to do this. So here's an example of how I'll go about reindexing one index.

Please correct me (or improve my approach) if you feel something's left wanting:

Imagine the following table with 4 indexes:

                                      Table "public.links_publicreply"
     Column      |           Type           |                           Modifiers
-----------------+--------------------------+----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id | integer                  | not null
 answer_to_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 description     | text                     | not null
 category        | character varying(20)    | not null
 seen            | boolean                  | not null
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "id_answer_to_id" btree (answer_to_id, id DESC)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_report" CONSTRAINT "links_report_which_publicreply_id_fkey" FOREIGN KEY (which_publicreply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

Starting with "links_publicreply_submitted_by_id" btree (submitted_by_id). I'll try:

1) CREATE INDEX CONCURRENTLY temp_index ON links_publicreply (submitted_by_id DESC)

2) Then I'll run the following:

BEGIN;
DROP INDEX links_publicreply_submitted_by_id;
ALTER INDEX temp_index RENAME TO links_publicreply_submitted_by_id;
COMMIT;

Does that sound right? If not, please correct/improve as necessary.

Follow-up question: Should I attempt to re-index the primary key index in similar fashion?


texts: Data structure design for supporting arbitrary number of columns in table or database

texts:
I am currently working on a sort-of a meta-modeler to build a free web service so that people can input data and run several models on that data.

The task I am currently struggling is: user needs to enter data column by column, which would consist of a number n of ID's , a number m of attributes and a number k of classes, with the conditions that n, m > 0 and k >= 0. Data is heterogeneous, so indexes can be both numeric or text, and the same goes for attributes and classes. I'm supposing there will be no null in the data for simplicity.

I'm currently thinking on:

Creating a table with more than enough columns (all with null values), so that I can work using only the non-null columns (which will be got from user input). However this would limit the size of the datasets people could input.
Resorting to create an specialized data structure on a programming language, do all the work there and finally, create a table dinamically to store the result data there.
Using a database specialized for this kind of data (maybe a document-based DB).
Create a data structure on the RDBMS itself (I'm using PostgreSQL), let's say a variable size array, so that I can create the table directly from the user input, using only 3 variable arrays (one for indexes, one for attributes and one for classes). However, I keep in mind that attributes and indexes could be of different types, so the array would have to support heterogeneous data type and I don't know if this is possible on a RDBMS or SQL.

I've been looking for information on information but got no result until now. Any guidances to a package, language library, extension or paper, thesis, technical report with relevant information would be appreciated. Also, personal experiences with doing something similar could be useful.

Edit:

Some example records:

ID NAME   CODE  HEIGHT WEIGHT       CLASS1     CLASS2
1  HARRY  100    170    70         SURVIVOR    VICTIM
2  ALBUS  101    185    82
4  REMUS  103    177    60

This is an example dataset where the intention is predicting if a person will survive or die based on its height and weight. The output should be lines consisting of SURVIVOR or VICTIM lines.
The ID's would be ID, NAME and CODE columns; HEIGHT and WEIGHT are the attributes and CLASS1 and CLASS2 are the classes.

The same structure should support something like this:

ID   CODE  INCOME  AGE  NUMBER_OF_HOUSES  CHILDREN   C1    C2   C3
1    101   9000    21       2                NO      HIGH  LOW  MED
2    110   5700    30       1                YES
7    111   4000    37       0                TES

In this example, the idea is to preddict the future level of income of a person, based on it's actual age, income, number of houses and if they have children currently. The result is either HIGH, MED or LOW.

The data in this case would be: ID and CODE represent the ID's, AGE, NUMBER_OF_HOUSES, INCOME and CHILDREN represent the attributes, and C1, C2, C3 represent the classes.

The data structure should support both inputs, even though they have different number of columns for each category. And also, each one of the columns have different data type. The number of columns per category shoudn't be fixed.


texts:
Is it possible to apply restrictions on how much data a user / login may store in a database? I found no way to apply such restrictions to schemas, for instance.

If there is no built-in mechanism then I'm curious if someone had implemented some kind of it.

Seems a bit ridiculous, I know but we have some users and want them to restrict writing too much data into WorkDB so that their activities (queries, inserts) wouldn't eat out all the resources.

Resource Governor can only maintain CPU/RAM.

Environment: SQL Server 2008 R2 Enterprise edition.


texts: I inherited a database in which many of the tables have bigint as a datatype for many of the fields, and when you see the content it does not requiere all the space that bigint offers. Does using bigint for fields that dont need it affect the database performance?


texts: Would CRUD operations get very slow on an mysql innodb table which got more than 50 million rows?

texts:
I'm about to design a DB for a finance institution and the last normalization of tables shows that the necessity of creating a single table for all the bank's transaction.

As per the calculations, several thousand transactions per day and this table would hit few million entires withing next few months.

1) When querying and writing data, would server perform very slow?

2) Is there any best way to handle the situation?


texts:
So i am implementing a system that has PIN (Personal identification number) codes. The PIN is a simple combination of a few letters and some numbers. The PIN could be something like AX7500.

Now, a entity in my database can have many PIN codes, and they must all be unique, but ONLY to that entity ID.

So two different entities can have the same PIN code.

How should i implement this? Should i implement this on the application level, or is it possible to do in the database with some constraints?

Im using Postgres 9.5


texts:
There is a table my_table which has not been created any partition in MySQL 5.6. And I'm trying to create partitions by procedure in my_table, when I run this code:

alter table my_table remove partitioning

I'm getting this error:

1505 - Partition management on a not partitioned table is not possible

But when I run above code in MySQL version 5.5, this code can be run.

If my_table has been created partitions, and when I run above code, not only MySQL 5.5 can run, but also MySQL 5.6 can run too.

I guess that if this code can be run, MySQL 5.6 need to be config some files.But I am not able to why.

Can you solve it?


texts:
DBMS allows the admin to grant table access to a user using something like:

GRANT ALL ON mydb.mytbl1, mydb.mytbl2 TO 'someuser'@'somehost';

However, is it possible to grant all tables to a user and explicitly block access to some of them? For example (The BLOCK key word is a fake one and is only used for illustration.):

GRANT ALL ON mydb.* TO 'someuser'@'somehost';
BLOCK ALL ON mydb.mytbl3 TO 'someuser'@'somehost';

Another idea is to add a where clause to the GRANT statement. However, it seems that the GRANT statement syntax does not allow that (http://dev.mysql.com/doc/refman/5.1/en/grant.html).

This kind of access control might be convenient if the admin trust the user in most cases (e.g. 1000 tables) and only want to block access to a small number of top sensitive tables.

Could you please tell me whether such kind of mechanism exists?


texts: What are the most compelling reasons to upgrade from SQL Server 2005 to SQL Server 2012?

texts:
We currently have 2 SQL Server 2005 Enterprise machines in a mirrored environment running on Dell R815 servers.  These machines consist of:

Four AMD Opteron 6176 Processors (12 cores at 2.3Ghz), for a total of 48 cores.
512GB DDR3 1033Mhz RAM
10GB Ethernet SAN connected LUNs for MDFs, Logs, TempDB, Backups, etc.

We are looking at upgrading to SQL Server 2008 R2 Enterprise or SQL Server 2012 Enterprise.

I understand all the features that Microsoft presents in their standard comparison guides, however it seems to me there is likely a lot of other smaller features that are not present in these official guides that might make a DBA's life easier in SQL 2012.

Can anyone suggest killer features that would sway towards SQL Server 2012 over 2008 R2?

As a result of Microsoft changing their licensing scheme from socket-based to core-based, we are looking at a huge expense to license all 48 cores.  To combat this unexpected rise in price, we are considering swapping these CPUs out for faster CPUs with a lower core count.  We are considering using 4 x Opteron 6204s, which are quad-core and run at 3.2GHz.  Our current systems are typically running at around 25 to 30% utilization; does anyone have experience dropping from 48 cores at 2.1Ghz to 16 cores at 3.2Ghz (or similar), and can we expect a perceivable decrease in speed?  I know that's a loaded question, and am expecting a fair amount of flack - however it just helps to think this through by getting the help of other more experienced DBAs.


texts:
I am learning Oracle and have to do roles. I created an c##admin from a sys, granted him all grants. Then I created a c##manager_role and c##manager. I granted this role to c##manager. After that I started to grant privileges to c##manager_role. In addition I granted the following:
CREATE VIEW                              YES YES NO

CREATE ANY VIEW                          NO  NO  NO

DROP ANY VIEW                            YES YES NO

Above output from:
select * from role_sys_privs where ROLE = 'C##MANAGER_ROLE' order by 1;

Now, I can't understand, why CREATE ANY VIEW has 3 "NO" and why I can't create any view from c##manager.
From c##admin:
grant CREATE ANY VIEW TO c##manager_role with admin option CONTAINER=all;

From c##manager:
create view friends_v4 as select t1.nickname as "USER", t2.nickname as "FRIEND" from c##admin.clients t1, c##admin.clients t2, c##admin.friends where (t1.id = c##admin.friends.user_id and t2.id = c##admin.friends.friend_id);

Error:

Error report -
ORA-01031: привилегий недостаточно
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
the necessary privileges.
*Action:   Ask your database administrator or designated security
administrator to grant you the necessary privileges

All tables noted exist.


texts: How to group and count by two different dates?


texts: SSMS sessions calling CLR packages intermittently stops processing completely, but SSMS appears to still be processing

texts: It's a third party app on SQL Server 2008R2. In Test, they are trying to run some reporting, and they have to use the SPs with many built in CLR functions. Running the script in SSMS on a system with no other activity, occasionally their sessions will just spin for hours. Normally the process takes 30 seconds. When it occurs, checking their session shows "Sleeping". No locks. Just dead process, but SSMS never returns. Due to the intermittent nature, I haven't been able to catch it in Extended Events and see what portion ran right before it went idle. My only thought here would be it's dropping off in a CLR package but not reporting back to SSMS. This is a theory. Anyone run into this?


texts: Is there a data type I can use for RA & Dec in astronomy

texts:
I need to have fields in my database for the following:

RA (hh:mm:ss.00)
Dec (Degrees:arcseconds:arcminutes.00)

I need to have the facility to search for a range within each field.

Any ideas?


texts:
In Oracle 12c or 11g, is it possible to fetch the history of user privileges held by a schema? Say from last two months, I might have granted different roles and privileges. I can get the current privileges the schema holds, but is it possible to know the privileges I previously granted?

Edit:
I have standard auditing enabled 'DB'. FGA is not enabled.


texts:
We have a problem adapting a recursive SQL from 11g to 10g.

Oracle 11g Query ( Working as expected )

WITH ADRECURSIVEUSERLEADER (CDLEADER,CDUSER,NMUSER,FGUSERENABLED)
AS
(
    SELECT US.CDLEADER AS CDLEADER
          ,US.CDUSER AS CDUSER
          ,US.NMUSER
          ,US.FGUSERENABLED
      FROM ADUSER US
     WHERE 1 = 1
     UNION ALL
    SELECT RS.CDLEADER
          ,US.CDUSER
          ,US.NMUSER
          ,US.FGUSERENABLED
      FROM ADRECURSIVEUSERLEADER RS
           INNER JOIN ADUSER US
               ON RS.CDUSER = US.CDLEADER
     WHERE 1 = 1
)
SELECT *
  FROM ADRECURSIVEUSERLEADER T
 WHERE 1 = 1;

Oracle 10g Query ( What we are trying )

WITH ADRECURSIVEUSERLEADER
AS
(
    SELECT US.CDLEADER AS CDLEADER
          ,US.CDUSER AS CDUSER
          ,US.NMUSER
          ,US.FGUSERENABLED
      FROM ADUSER US
     START WITH US.CDLEADER IS NULL         -- parent
   CONNECT BY PRIOR US.CDUSER = US.CDLEADER -- child / parent
)
SELECT *
  FROM ADRECURSIVEUSERLEADER T
 WHERE 1 = 1;

What is the problem?

We have the following leader structure:

User code (CDUSER)    5 is the CEO
User code (CDUSER)   21 is the Manager   (Leaded by 5)
User code (CDUSER)  937 is the Leader    (Leaded by 21)
User code (CDUSER) 1276 is the Developer (Leaded by 937)

If I search for a leader on 10g and 11g :

AND T.CDLEADER = 21

11g shows me two records:

One for  937 ( Leader )
One for 1276 ( Developer )

10g shows me only one record:

One for  937 ( Leader )

SQL Fiddle Example

DDL for Oracle:

CREATE TABLE ADUSER (CDUSER          NUMBER(10)
                    ,CDLEADER        NUMBER(10)
                    ,FGUSERENABLED   NUMBER(2)
                    ,NMUSER          VARCHAR2(255) );

INSERT INTO ADUSER VALUES (   5, NULL, 1, 'CEO Name');
INSERT INTO ADUSER VALUES (  21,    5, 1, 'Manager Name');
INSERT INTO ADUSER VALUES ( 937,   21, 1, 'Leader Name');
INSERT INTO ADUSER VALUES (1276,  937, 1, 'Developer Name');

What I need?

I need to know everyone under the leader I search and the filter must me apllied on the (ADRECURSIVEUSERLEADER T) because it's going to be a view . Is that possible on Oracle 10g?

Thanks a lot


texts: MySql LOAD DATA with subquery performance issue

texts:
I'm migrating data into a MySql (5.7.26) database (32GB ram), running as a managed service on AWS. While importing the data, I need to map one of the columns of the CSV being imported to another value using a MEMORY table lookup; so my LOAD DATA resembles the following:

LOAD DATA LOCAL INFILE 'file.csv'
INTO TABLE table_1(col_1, @var1)
SET col_2 = (select mapped_value from table_2 where id = @var1)

table_2 is a 2-column (id, mapped_value) MEMORY table with 3.4MM rows.

When I import the CSV without the subquery, I get several million inserts per minute. However, when I run the same import with the subquery the LOAD DATA performance degrades to near zero (~100 inserts per minute). Is this to be expected with a subquery, or is there something I'm doing wrong in the example above?


texts: Data truncated after importing a MySQL 5.7 dump into MySQL 8

texts:
I am trying to import a 12G MySQL 5.7 dump (on ubuntu 16.04) into a MySQL 8.0 database (on ubuntu 20.04).
Half way through the import exits and returns the following error:
ERROR 1265 (01000) at line 4439: Data truncated for column 'NORM' at row 14

Now this seems a likely problem as the original data was having trouble to import from a CSV file for that particular table, however I believed this has been solved after all it is stored now inside the table.
SELECT DISTINCT  NORM from table

N1

N3
N2

Create table:
CREATE TABLE `table` (
  `NORM` enum('N1','N2','N3') COLLATE utf8_unicode_ci DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=COMPRESSED

How can I verify and fix this on the original database and how can I sort of ignore this error while importing from this particular dump for now?


texts: 'Out of memory' Error for MariaDB on RDS

texts:
We have a t2.medium instance on AWS' RDS platform (4gb, 2xCpu). We're using MariaDB 10.1.19 with a single database, ~120 tables and ~2gb of data in total.

After running for about a week we get this error, at first only some requests, then on the heavy requests and then it starts affecting more queries. Eventually resulting in a reboot.

Php (PDO) error:

General error: 5 Out of memory (Needed 262680 bytes)

Mysql (from error.log)

2017-06-14 10:55:29 47531169896192 [ERROR] mysqld: Out of memory (Needed 262680 bytes)

This shows the 'freeable memory' on the instance (the big spike being when we rebooted).

We're were using default mysql config. We ran mysqltuner it mentioned:

Variables to adjust:
query_cache_size (=0)
query_cache_type (=0)
join_buffer_size (> 512.0K, or always use indexes with joins)
thread_cache_size (start at 4)
performance_schema = ON enable PFS
innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=1G) if possible.
innodb_buffer_pool_instances(=2)

So we've since tried increasing the join_buffer_size (to 512k) and increased the key_buffer_size (to 16M) but didn't seem to make any difference. Not sure if the error is the result of the OS having no ram available, or the mysql process itself.

Any suggestions? (Thanks)


texts:
MySQL's command line tool has a flag --i-am-a-dummy that sets execution limits:

  With --safe-updates, you can modify rows only by specifying the key values that identify them, or a LIMIT clause, or both. This helps prevent accidents. Safe-updates mode also restricts SELECT statements that produce (or are estimated to produce) very large result sets.

Is there a way to turn on similar features in psql?


texts: Getting error running DBCC Checktable(tablename, repair_allow_data_loss)

texts:
I ran DBCC CheckDB and got some consistency errors in the database.  In an attempt to recover all the data I have exported out all the data from the database to a new database except for one table.  When I try to copy all the data from that table to the new database I get an error that says

  "A severe error occurred on the current command.  The results, if any,
  should be discarded."

So I ran DBCC CheckTable(tablename) on the table to try to ensure that this was the table that was causing the issue.  It gave the same consistency errors that were coming from the CheckDB.

Once I ran CheckTable it told me the minimum I could do is run with repair_allow_data_loss.  I set the database into SINGLE_USER mode and then I ran the DBCC CheckTable(tablename, repair_allow_data_loss) command and now I get this error

  Repair statement not processed.  One or more files in the database are
  read-only and must be made writeable in order to run repair.

I checked to make sure the files and the file groups are all writeable and they are, so I'm not sure what is causing the issue.

I've tried taking a backup and restoring that to a different server, I've tried making a new copy of the database, I've tried making the files read-only and then making them writeable again.  None of those things worked and all produced the same error about the files being readonly.

Anybody have any thoughts on what else I might try?

For reference, I am running SQL Server 2014 SP2.

EDIT: Here is there output using WITH NO_INFOMSGS, ALL_ERRORMSGS

Msg 8952, Level 16, State 1, Line 1
Table error: table 'sys.sysschobjs' (ID 34). Index row in index 'nc1' (ID 2) does not match any data row. Possible extra or invalid keys for:
Msg 8956, Level 16, State 1, Line 1
Index row (1:18806:22) with values (nsclass = 0 and nsid = 1 and name = 'tmp324504_734167628_1' and id = 385811436) pointing to the data row identified by (id = 385811436).
CHECKDB found 0 allocation errors and 1 consistency errors in table 'sys.sysschobjs' (object ID 34).
Msg 8944, Level 16, State 12, Line 1
Table error: Object ID 1993058136, index ID 1, partition ID 72057684034650112, alloc unit ID 71906736119218176 (type LOB data), page (1:28439), row 0. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 32512 and 8037.
Msg 8944, Level 16, State 12, Line 1
Table error: Object ID 1993058136, index ID 1, partition ID 72057684034650112, alloc unit ID 71906736119218176 (type LOB data), page (1:28439), row 0. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 32512 and 8037.
Msg 8965, Level 16, State 1, Line 1
Table error: Object ID 1993058136, index ID 1, partition ID 72057684034650112, alloc unit ID 71906736119218176 (type LOB data). The off-row data node at page (1:28439), slot 0, text ID 69999198208 is referenced by page (1:27785), slot 0, but was not seen in the scan.
Msg 8928, Level 16, State 1, Line 1
Object ID 1993058136, index ID 1, partition ID 72057684034650112, alloc unit ID 71906736119218176 (type LOB data): Page (1:28439) could not be processed.  See other errors for details.
Msg 8929, Level 16, State 1, Line 1
Object ID 1993058136, index ID 1, partition ID 72057684034650112, alloc unit ID 72057693155753984 (type In-row data): Errors found in off-row data with ID 69999198208 owned by data record identified by RID = (1:869011:3)
CHECKDB found 0 allocation errors and 6 consistency errors in database 'DataCorruption'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DataCorruption).


texts: how to get any record in any table that would violate a foreign key constraint that would prevent my delete?

texts:
basically I am looking for something like:

before you can delete record x from my_table you need to:

1) delete x,y and z from table1

2) delete m and n from table2

3) delete a,b,c from table3

I have not starting coding yet, I am posting the question because sometimes other DBAs have had the same idea and they might be willing to share the code.

I have though, seen similar questions (see below - but they haven't fulfilled my requirements)

Checking referential integrity rule violation before deletion

Checking rule violations before deleting a record

and this is an interesting article related to this matter:

Handling Constraint Violations and Errors in SQL Server

Please note that I don't want to disable any constraints. I just want to check all that will break if I do a delete - before I go ahead and do it.


texts: Digital content eCommerce schema advice?

texts:
I don't have much experience designing databases, I'm looking for some advice on my design from more seasoned designers/developers. I'm developing a digital content eCommerce store which will be desktop based and mobile based similar to iTunes.

Schema

The store will most work like iTunes, it's just that it's focused on music at the moment. I'm not sure if I should explain each table responsibility. Users can reload monetary credits to their profiles from an external application.

Please advice if this design will work and is sufficient. Thank You.


texts: How much "Padding" do I put on my indexes?

texts: How do you know how much "Padding" an index should have?


texts:
I need help creating an MSSQL query to pass in every table name to the query below, which returns the number of rows.

This is how I get the row count for a single table:

SELECT Total_Rows = SUM(st.row_count)
FROM sys.dm_db_partition_stats st
WHERE object_name(object_id) = 'TABLE_NAME' AND (index_id < 2)

What is the way to loop this query on all tables, returning an output of each table's name and row count?

Example output:

TableName     RowCount
---------------------
FirstTable    109
SecondTable   2195
ThirdTable    0


texts: Patroni : How to handle a replica which has been disconnected from primary for long time?

texts:
Let's say I am using asynchronous streaming replication with the below configuration in a 3 node cluster with Postgres 10.4 and Patroni 1.4.4
    bootstrap:
      dcs:
        ttl: 30
        loop_wait: 10
        retry_timeout: 10
        maximum_lag_on_failover: 1048576
        postgresql:
          use_pg_rewind: true
          use_slots: true
          parameters:
            max_wal_senders: 10
            wal_keep_segments: 100
            max_replication_slots: 10

Let's assume that one of the replica nodes suddenly loses its connection to primary for a long time.

In this case I think the size of WAL on the primary will keep on growing as it is not being consumed by the disconnected replica's replication slot. So is there any setting in patroni configuration which will remove the replica and remove its replication slot if it is disconnected from primary for x time duration?
What is the recommended way to handle this case?


texts: Change default collation for character set utf8mb4 to utf8mb4_unicode_ci

texts:
When creating a database without specifying a character set or collation the servers defaults are used (as expected).

MariaDB [(none)]> SHOW VARIABLES LIKE '%_server' ;
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| character_set_server | utf8mb4            |
| collation_server     | utf8mb4_unicode_ci |
+----------------------+--------------------+

MariaDB [(none)]> CREATE DATABASE `test-without-charset` ;
MariaDB [(none)]> SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME` LIKE 'test-without-charset';
+------------------------+
| DEFAULT_COLLATION_NAME |
+------------------------+
| utf8mb4_unicode_ci     |
+------------------------+

However, when specifying the character set within the CREATE DATABASE-query, the default collation changes to utf8mb4_general_ci.

MariaDB [(none)]> CREATE DATABASE `test-with-charset` CHARACTER SET utf8mb4 ;
MariaDB [(none)]> SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME` LIKE 'test-with-charset';
+------------------------+
| DEFAULT_COLLATION_NAME |
+------------------------+
| utf8mb4_general_ci     |
+------------------------+

I already found out that (mysql-manual)

  If CHARACTER SET charset_name is specified without COLLATE, character
  set charset_name and its default collation are used. To see the
  default collation for each character set, use the SHOW CHARACTER SET
  statement or query the INFORMATION_SCHEMA CHARACTER_SETS table.

And indeed it shows utf8mb4_general_ci, so it is following the rules

MariaDB [(none)]> SHOW CHARACTER SET LIKE 'utf8mb4';
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+

So my question is: How do I change this default collation for the character set utf8mb4. Is there some configuration-file I can change to alter this behaviour? I'd really like those two to be consistent.

Off course I tried Google to find anything relevant, but all I can find is changing the collation_server-setting.

  Server version: 10.3.15-MariaDB-log MariaDB Server


texts: Can we configure Certificate-based Authentication to connect(logon) sql client? or any other method to connect sql client using mutual authentication(sql server and sql client)?


texts: SQL Server Core - Installed SQL Server features

texts:
When using a traditional installation of SQL Server one has the Installed SQL Server features discovery report.

Googled and found no info if some equivalent was available for the core installation. It outputs to HTML so that is another reason why I believe it is not available (though one could still type its text).

So basically, I'd like to know if there is an equivalent on the core version, or what powershell (or command line) could be run to get the same info (version, installed features)?


texts: How to compare EF core code-first database with existing Azure Sql Server database

texts:
we have multi-tenants databases and we have special stored procedure to compare the structure of the tenents after each release. Now, we are moving to Azure SQL Server and the stored procedure is not working anymore because we use the INFORMATION_SCHEMA.TABLES, INFORMATION_SCHEMA.COLUMNS, ... like this:

SET @SelectTables = 'SELECT TABLE_SCHEMA + ''.''+ TABLE_NAME FROM '+ @SourceDatabase + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE'' '
INSERT INTO #SourceTableNames EXECUTE sp_executesql  @SelectTables

SET @SelectTables = 'SELECT TABLE_SCHEMA + ''.''+ TABLE_NAME FROM '+ @DestinationDatabase +'.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''BASE TABLE'' '
INSERT INTO #DestinationTableNames EXECUTE sp_executesql  @SelectTables

and we got an error:

    Reference to database and/or server name in 'Database.INFORMATION_SCHEMA.TABLES' is not supported in this version of SQL Server.

In an emergency situation we use Visual Studio SQL Compare or RedGate Compare tools but it would be better to compare the structure of the existing database with a newly created database based on EF Core after each release. Would you please guide me about that? I do not like to compare the tenant one by one after each release with the mentioned tools.


texts: Master/Slave - streaming replication restrict function to master

texts:
How can I run a function a on master only?

I don't want to connect directly to my master server, because the master server might change.

Or can I change my function to work on this setup? Because at the moment using the below function, will say that the server is read-only?

ERROR:  cannot execute SELECT in a read-only transaction

CREATE FUNCTION.....
 WITH sel AS (
   SELECT id
   FROM   univ.domains
   WHERE  name = domain_name
  )
, ins AS (
  INSERT INTO univ.domains (name)
  SELECT domain_name
  WHERE  NOT EXISTS (SELECT 1 FROM sel)
  RETURNING id
  )
SELECT id
FROM   sel NATURAL FULL OUTER JOIN ins
INTO   domain_id;
 ...
LANGUAGE plpgsql

Also tried adding SET TRANSACTION READ WRITE; after begin and got:

cannot set transaction read-write mode inside a read-only transaction


texts: Issue with running multiple mysql commands in a bash for loop

texts:
I try to restore some .sql backups using a shell script with a for loop.

I run by bash command.sh 'password' using the command.sh below. However, when I typed it out, it didn't run and instead, mysql --help message showed up multiple times.. I think somehow the mysql command wasn't invoked. How to do it here?

#!/bin/bash

for i in *sql;
do
    fname=$(basename $i .sql)
    echo "working on $fname"
    mysql -uroot -p $1 $fname < $i
    wait
done


texts: SQL Server: Performance Insert Into vs Select Into

texts:
I am hearing different things from colleagues/research. What are good guidelines in performance for, Select Into vs Insert into when creating a temp table? I know difference is minimal for small tables.

Eg: Table has 20 columns, 50 million rows.

I've had DBAs state, Insert into is faster, since compiler/parser, does not need to find Column data types on the fly. Others stating Select into is faster.
We conducted performance testing, and seems select into is slightly faster.

What are good principles in figuring which is faster and why? I would think Microsoft would optimize to make insert into , just as fast, for careful programming.

Article states following.

SQL Server Performance of SELECT INTO vs INSERT INTO for temporary tables

  The INSERT...INTO command will reuse data pages which are created in
  cache for insert/update/delete operations. It will also truncate the
  table when it is dropped. The SELECT...INTO command will create new
  pages for table creation similar to regular tables and will physically
  remove them when the temporary table is dropped.

Question is, why wouldn't Microsoft optimize to make insert into as fast as select into?

We have over 500 stored procedures to write for data warehouse, and require good guidelines for temp usage.

This article does not really focus on performance and reasons:

INSERT INTO vs SELECT INTO

Person in article mentioned good point:

  that's mostly because SQL Server knows that there is no contention for
  the destination table. The performance for insert into #temp
  with(tablock) select * from .. is roughly the same as the performance
  for select * into #temp from


texts: How to choose which backup files to restore when identical timestamps exist for diff and log backups

texts:
I can see from an earlier question that log file backup may be performed simultaneously with either full or differential data file backups.

Imagine for a moment that full backups run during a window of time that doesn't overlap with any other backup. However differential and log backups may overlap.

If, for example, this produces a differential backup at 11am on a given day, but also produces a log file backup at 11am on the same day, how do you decide which files to restore?

For example:

01:00 AM = full backup
10:00 AM = differential backup
10:00 AM = log backup
10:15 AM = log backup
10:30 AM = log backup
10:45 AM = log backup
11:00 AM = differential backup
11:00 AM = log backup
11:05 AM = disaster

After restoring the full backup, do you restore the second differential backup and the last log backup? Or do you restore the first backup and all the log backups (even though the 10AM log backup really has the same issue as the 11AM backup - coinciding with the differential backup in each case).

I can see another question here for which some answers suggest third-party scripts to restore backup file headers in order to determine sequence numbers - do we really have to resort to header restores to determine which files are able to be restored in sequence?

Edit (19/12/18): I think my original question was worded poorly, so the answers it has received are varied. The key part I wanted to understand was actually mentioned by Jonathan in a comment on Sean's answer, and Jerry mentioned it too - that the restore sequence will handle the scenario if the log file contains information about data changes that have already been accounted for by the differential backup restore. (Sorry I didn't word the question well!) Sean's answer gives lots of additional info that is also helpful to understanding the restore sequence, so I can't pick an individual answer here, but I will upvote everything that helped with clarifications. Thanks.


texts:
Two node Always On availability group, one synchronous replica.

My synchronous replica falls out of sync very regularly. I see a pattern in which, when a log backup occurs on the secondary replica a short period of lag will occur during which the redo_queue_size fills up quickly, like so:

Looking at the guidance in the following link, it seems that my problem is mostly due to contention the redo thread experiences when trying to harden the transactions:

https://technet.microsoft.com/en-us/library/dn135335(v=sql.110).aspx

The replica falls further out of sync when a transaction log backup runs and this problem is exacerbated by reports running on the secondary replica also.

All the while, my transaction log backups are huge - on average 1.2GB but can be larger.

As far as I am aware, my log backups will be large because I have TDE enabled on the database, but I really didn't expect them to be this large. I suspect this is what is contributing the most to the slow commits on the secondary replica.

Are there any performance counters recommended to diagnose slow commits on a synchronous replica? What else can I do to validate my theory?

My problem seems to be the same as the one described here:
https://www.sqlservercentral.com/Forums/1871286/AlwaysOn-Missing-Redo-Thread

Can I just enable this trace flag on the secondary replica or does it need applying to both nodes?

EDIT: I checked the redo queue at 6am and found a huge number, with a recovery time of 15-20 minutes and increasing slightly all the time. I then applied the traceflag with DBCC TRACEON (3459, -1) and found after a few minutes, the number of redo commands dropped extremely quickly. So far the problem seems to have been mitigated by this traceflag but presumably this will put all transactions hardened to the secondary replica's log in single-threaded mode, like SQL Server 2014 and therefore, there is still the potential for the secondary replica to fall behind as a result of the non-parallel threading, when the primary is under heavy write load.


texts: Many queries are slower on new hardware and newest version of PostgreSQL

texts:
Note: I have significantly re-worked this question based on the direction the commentators have taken me and what I have learned on the way.

I am setting up a new development database server. The new one is a 6th Gen i7-6700 with 16GB DDR4 memory and an Intel 750 SSD with PostgreSQL 9.6. The old one is a 4th gen i5-4570 with 16GB DDR3 memory and a Crucial SSD Raid 0 array with PostgreSQL 9.2. I benchmarked the 750 and it is much faster than the RAID. However, the read performance below 256KB is slower.

I used pg_dump and pg_restore to move the data and I ran vacuum analyze on all databases.

The new computer should be quite a bit faster than the old one and in some types of queries is it, but in others it is several times slower. I also installed 9.2 on the new computer to try and isolate the database versions from the hardware. I kept the postgresql.conf identical where possible (9.6 changes some wal settings.)

I set up pg_stat_statements to get some statistics to narrow down the problem.  I have 500+ schemas and 3000+ tables. The transformations I am running are creating new tables from some of these tables and updating those new tables from these 3000 other tables. I am bouncing all over this 50GB of data.

On the new server and 9.6 create table, copy, vacuum, analyze, drop table, updates that affect many rows, create index, create table as are usually faster, up to twice as fast. But occasionally they will be several times slower. The new server running 9.2 sees a similar pattern, but the performance increases are not nearly as pronounced. Interestingly, when 9.6 is slower on some particular copy or vacuum, 9.2 will usually be similarly slow.

drop index, drop table, select queries that return few rows, select count(*), update queries that affect a single row are all usually 50%-300% slower in the new 9.6 server. 9.2 on the new server is also slower, but usually only 20%-30%. I do not know how PostgreSQL accesses the disk, but I suspect that the Intel 750's slower read performance on smaller files is having an effect as seen in the slower 9.2 speed. But that does not explain the 5x-10x performance degradation between 9.2 and 9.6 on the same server.

So my conclusion is 9.6 is significantly slower for certain types of queries.

I have run explain on many of the queries in question and the plans are identical.

Here are some representative examples (from pg_stat_statements) of the queries that are slow. Note that they are all primary keyed, indexed vacuumed and analyzed and otherwise identical between the two databases. Also, I only selected queries that have at least 50 calls.

Update finalal Set jsondone=?, json=? Where id=? //20% slower
SELECT * FROM archive_natrilis_2015_q3.natrilis_releases WHERE siteid = ? ORDER BY reporting_year ASC LIMIT ? // 404% slower
SELECT * FROM work_narcra00_2016_q3.narcra_handler WHERE siteid = ? ORDER BY handler_sequence_number ASC LIMIT ? // 182% slower
select * from listtypedesc // small 20 row table, 173% slower.
select * from INFORMATION_SCHEMA.schemata where schema_name not like ? and schema_name not like ? AND schema_name like ? Order by schema_name Desc // 52% slower
Select count(*) From work_allustis_2016_q3.allustis_details Where siteid = ? // 234% slower
SELECT * FROM archive_naerns14_2015_q2.naerns14_material_involved WHERE siteid = ?  LIMIT ? // 287% slower

Is my conclusion correct? And if so is there a 9.6 specific way to optimize these slow queries? If not, are there other debug actions to recommend?


texts: What are the optimal mysqldump settings?

texts:
After some search I ended up with the following settings:

    mysqldump\
    --host=localhost\
    --port=3306\
    --databases ****\
    --user=****\
    --password=****\
    --default-character-set=utf8\
    --add-drop-database\
    --add-drop-table\
    --add-locks\
    --complete-insert\
    --extended-insert\
    --lock-all-tables\
    --create-options\
    --disable-keys\
    --quick\
    --order-by-primary\
    --set-charset\
    --tz-utc\
    > dump/test.sql

So far so good but I have some questions regarding the parameters as well as the execution.

Regarding my first concern, I just want to make sure that all of them are required and do not cause any conflicts when used together. Ultimately, I would like to make a very robust and consistent dump file, with millions of records, that creates tables, databases, and inserts data. The good thing is that making the database not-available for a while is not a problem for me. My only goal is to make a robust and consistent dump file.

Regarding my second concert, I would like to know how to get informed when then command goes wrong and if so throw an exception.

Any ideas?

EDIT

This is my updated mysqldump command based on RolandoMySQLDBA's feedback.

mysqldump\
--host=localhost\
--port=3306\
--databases ****\
--user=****\
--password=****\
--default-character-set=utf8\
--opt\
--single-transaction\
--routines\
--triggers\
--events\
--add-drop-database\
--add-drop-table\
--complete-insert\
--delayed-insert\
--tz-utc\
> dump/test.sql


texts: What reason could cause AWS RDS connections to spike

texts:
It's my first time using RDS on AWS, I use a t2.medium instance running MySQL Aurora with default configs. The CPU usage and DB connections is quite normal until "something" happens, which causes the DB connections go all the way up to its maximum (with t2.medium it is 80 connections).

I only have one web app, running on an EC2 instance. When the DB connections reaches its maximum, the EC2 instance's CPU usage is absolutely normal (25-30%), but all attempts to connect to the DB instance results in "Too many connections".

I also checked the DB instance's CPU Utilization at the time -- it was showing no signal of high load. During the strike time, CPU Utilization dropped to 20% and kept that rate consistently.

The thing I don't understand: DB connections is at its maximum, but why is DB CPU Utilization dropped? Shouldn't it be at its maximum as well because of the computation for the queries in those connections?

Please help me understand, thank you very much.

(I had to resize the RDS instance to r4.large when the second strike happens; I'm still running it now until I find out the problem...)


texts:
I don't know why I am getting a syntax error. I have checked my query carefully many times and read up on mysql reference for load data infile and everything seems fine. Here is my query:

LOAD DATA LOCAL INFILE '/tmp/phpnm6Zek' IGNORE INTO TABLE loaddata_temp FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (@sku, @model, @option, @option_value, @required, @quantity, @subtract, @price, @points, @weight) SET sku=@sku, model=@model, option=@option, option_value=@option_value, required=@required, quantity=@quantity, subtract=@subtract, price=@price, points=@points, weight=@weight

This is the error that I am getting:

Notice: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'option=@option, option_value=@option_value, required=@required, quantity=@quanti' at line 1
Error No: 1064
LOAD DATA LOCAL INFILE '/tmp/phpO6k4u3' IGNORE INTO TABLE loaddata_temp FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (@sku, @model, @option, @option_value, @required, @quantity, @subtract, @price, @points, @weight) SET sku=@sku, model=@model, option=@option, option_value=@option_value, required=@required, quantity=@quantity, subtract=@subtract, price=@price, points=@points, weight=@weight


texts:
I need the ability to associate two entities of the same or different types. Take the concept of planes and trucks that have the ability to radio to each other.

+----------------------+
|        Airplane      |
+----------------------+
| AirplaneId           |
| Wingspan             |
| MaxTakeoffWeight     |
| etc...               |
+----------------------+

+----------------------+
|         Truck        |
+----------------------+
| TruckId              |
| CargoCubicFt         |
| IsResidential        |
| etc...               |
+----------------------+

+----------------------+
| CommunicationBetween |
+----------------------+
| [AirplaneOrTruckId]  |
| [AirplaneOrTruckId]  |
+----------------------+

What's the best way to accomplish these associations?

Notes

I can't combine or abstract out the two entities (e.g. can't combine planes and trucks into a Vehicles table) because each entity has many unique properties that the other doesn't have.

The only other idea I have is to create a table like this:

+----------------------+
| CommunicationBetween |
+----------------------+
| Entity1              | -- "Airplane" or "Truck"
| Id1                  | -- AirplaneId or TruckId
| Entity2              | -- "Airplane" or "Truck"
| Id2                  | -- AirplaneId or TruckId
+----------------------+

... but that's not an elegant solution.


texts: How can I list all user-owned functions?

texts:
Is there a way to quickly list all the current user-owned functions in Oracle?

Something along the same lines as this, which lists the current user's tables

select * from user_tables;


texts: Upgrading Cassandra from 1.1.0 to latest

texts:
I have a single node cassandra database running since 2 years now... never had any issues with it, but I would like to upgrade it to the latest cassandra (2.1.2 at the time I write those lines), of course without having to shutdown the service.

I have multiple servers at my disposal, that can be brought up to add nodes into a cluster if needed, what would be the procedure to update from my 1.1.0 version to the latest version without having any downtime ?

I know that it will be hard work, since I can't upgrading from 1.1.0 to 2.1.2 straight, I'll have to go through steps because of un-interoperable versions...


texts: Strange issue with mysql index cardiality

texts:
I am facing a weird issue when trying to understand (explain) a query.

I have this table:

CREATE TABLE `trk_track` (
 `track_id` int(11) NOT NULL AUTO_INCREMENT,
 `track_date` date NOT NULL,
 `track_hour` int(11) NOT NULL,
 `track_createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `user_id` int(11) NOT NULL,
 `action_id` int(11) NOT NULL,
 `url_utm_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`track_id`),
 KEY `track_date` (`track_date`,`track_hour`),
 KEY `user_id` (`user_id`),
 KEY `action_id` (`action_id`),
 KEY `url_utm_id` (`url_utm_id`)
) ENGINE=InnoDB AUTO_INCREMENT=284538 DEFAULT CHARSET=utf8

There are about 200k lines in this table.

But something is very confusing:

1-) First the cardiality of url_utm_id index should be 200, but it shows '3' and after analyze table it shows '493', which is still wrong;

2-) When I try to run this query:

explain select count(track_id) from trk_track where url_utm_id in(185);

it shows:

id  select_type     table   partitions  type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   SIMPLE  trk_track   NULL    ref     url_utm_id  url_utm_id  5   const   53  100.00  Using index

It is ok because there are only 53 url_utm_id(185) in trk_track.

But if I try to do:

explain select count(track_id) from trk_track where url_utm_id in(select url_utm_id from trk_url_utm where utm_id=102);

it shows:

id  select_type     table   partitions  type    possible_keys   key     key_len     ref     rows    filtered    Extra
1   SIMPLE  trk_url_utm     NULL    ref     PRIMARY,utm_id  utm_id  4   const   1   100.00  Using index
1   SIMPLE  trk_track   NULL    ref     url_utm_id  url_utm_id  5   db_trk.trk_url_utm.url_utm_id   577     100.00  Using index

This subquery:

select url_utm_id from trk_url_utm where utm_id=102

will return same '185' ID

But anyway it sounds like that it tries to make a join, look in explain and it scan 577 rows, it doesnt' make sense for me.

It should scan 53 also, shouldn't it?


texts:
I am trying to figure out what permissions would still be missing if the SPN is created manually for SQL Service account and I am seeing this error message:

  The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.


texts:
Alrighty so I'm trying to setup a procedure to do a count of locations returned from a geosearch. Geo-search for a specific area returns 2640 records (with a limit of 5000, and offset of 0) but the count returns 3179 records. I'm sure I'm messing something up with it. Here's the two procedures:

Geo Search

BEGIN
declare lon1 float; declare lon2 float;
declare lat1 float; declare lat2 float;

set lon1 = lon-distance/abs(cos(radians(lat))*69);
set lon2 = lon+distance/abs(cos(radians(lat))*69);
set lat1 = lat-(distance/69);
set lat2 = lat+(distance/69);

SELECT
    campaign_inventory.*, 3956 * 2 * ASIN(
        SQRT(
            POWER(
                SIN(
                    (lat - campaign_inventory.lat) * pi() / 180 / 2
                ),
                2
            ) + COS(lat * pi() / 180) * COS(campaign_inventory.lat * pi() / 180) * POWER(
                SIN(
                    (lon - campaign_inventory.lon) * pi() / 180 / 2
                ),
                2
            )
        )
    ) as distance_in_mi
FROM
    campaign_inventory
WHERE campaign_inventory.lon BETWEEN lon1 AND lon2
AND campaign_inventory.lat BETWEEN lat1 AND lat2
AND (campaign_inventory.inventory_copy like CONCAT('%',search,'%')
OR campaign_inventory.item_name like CONCAT('%',search,'%'))
HAVING
    distance_in_mi < distance
ORDER BY
    distance_in_mi, cpl desc
LIMIT `start`,`limit`;

END

Search Count

BEGIN
declare lon1 float; declare lon2 float;
declare lat1 float; declare lat2 float;

set lon1 = lon-distance/abs(cos(radians(lat))*69);
set lon2 = lon+distance/abs(cos(radians(lat))*69);
set lat1 = lat-(distance/69);
set lat2 = lat+(distance/69);

SELECT
    count(*) as search_results, 3956 * 2 * ASIN(
        SQRT(
            POWER(
                SIN(
                    (lat - campaign_inventory.lat) * pi() / 180 / 2
                ),
                2
            ) + COS(lat * pi() / 180) * COS(campaign_inventory.lat * pi() / 180) * POWER(
                SIN(
                    (lon - campaign_inventory.lon) * pi() / 180 / 2
                ),
                2
            )
        )
    ) as distance_in_mi
FROM
    campaign_inventory
WHERE campaign_inventory.lon BETWEEN lon1 AND lon2
AND campaign_inventory.lat BETWEEN lat1 AND lat2
AND (campaign_inventory.inventory_copy like CONCAT('%',search,'%')
OR campaign_inventory.item_name like CONCAT('%',search,'%'))
HAVING
    distance_in_mi < distance
ORDER BY
    distance_in_mi desc;

END

I'm sure there's something funky. This is the first procedure I've put together and I'm not very familiar with them. But I think the issue may be in the query itself. Any input and suggestions are greatly appreciated. Let me know if there any additional info that would help.

Thanks a lot.


texts:
I am using the code below to insert a 2.2MB file into a MySQL database in the cloud using Azure (the free version) with 36 thousand rows and 8 columns of doubles.

MySqlCommand sqlCmd = new MySqlCommand(
    "LOAD DATAINFILE 'Data4.csv'
    INTO TABLE ljmuracing.record
    FIELDS TERMINATED BY ',';", mConnection);
sqlCmd.ExecuteNonQuery();
mConnection.Close();

The problem is that it's extremely slow; about 2 thousand rows uploaded every 5 minutes. Sometimes, the connection times out.

How can I speed this up? Am I doing something extremely inefficient?

My upload speed as of now is 14 MB/s, so I don't think it is that.


texts:
Is there another way to create a script that I can see all database files with their disk space details across databases and across servers?

I can do it through Registered Servers.

But I want to know if I can do it using 1 instance with linked servers for all the servers. My idea is to generate a script that will read each database for each linked server then when I have all the data, it can now be passed to the SQL Server Job.

Thanks.


texts: How to read from Mongodb secondary Only in a replica set?

texts:
Mongodb version 2.6.10
Ubuntu 14.04

Currently having a replica set of 4 nodes , my read preference is Primary Only , I configured one node with priority 0 Also i have a file on the priority 0 node /etc/mongorc.js that contain

rs.slaveOk()

so you can always read from this node.

Does that guarantee that reads are only done on this secondary Only, Considering that the application is connecting to the Machine using ODBC driver on windows server


texts: Generate DACPAC diff using predefined config

texts:
I would like to generate a diff file based on the database vs DACPAC comparison. Currently what I am doing is extracting DACPAC from the target database and trying to create a diff file based on that (I am not sure if the DACPAC extraction is the needed step). Everything is OK until the moment when I am trying to use the same config I am using for publishing (to make sure that I am generating the script with the same configuration as I am publishing) using the following command:

"C:\Program Files (x86)\Microsoft SQL Server\130\DAC\bin\sqlpackage.exe"
/a:Script /sf:F:\tmp\o.dacpac /tf:F:\tmp\n.dacpac /tdn:ScreenerDb
/op:F:\tmp\ScreenerDb.sql
/p:SqlPublishProfilePath="F:\TMap\Root\Application\Dev\Database\Financials.SQL\Financials.SQL\Publish Configs\Financials.SQL_Dev.publish.xml"

I am getting the following error:

  'SqlPublishProfilePath' is not a valid argument for the 'Script' action.

To be fair, I am just trying to achieve following:

Create a diff file based on the DB DACPAC and the real database using the same configuration as for publishing.

How can I achieve this?


texts: how to prevent dropping sysadmin user by another user in sql server

texts:
I have a legacy application installed on my server which has configured to work only with one user "sa" and there is time bomb like script written inside this application which whenever it finds another user it will delete that user without any hassle.

We need to have access to database with another application and password of "sa" is lost so the only way remaining is to create another user with sysadmin privileges.

but since the script deletes all the users I can't do anything special, so my question is :

How can I prevent that script or rollback its job every time it is trying to delete my user?


texts: New SQL Server from backup; application has successful login on 1 database, but fails on another: 0x80040e4d

texts:
Our SQL Server crashed and burned, the hard drives destroyed. No hope of recovery.

We need to get a very important application working again; this is on a different server.

We've set up a new SQL Server using SQL Server Express 2014 and attached the .MDF files from the most recent backup.
We created a user and gave access to all the databases.
We changed the connection string in the application to use the new SQL Server and the new user. Connection string:
Provider=sqloledb;Data Source=<IP-address>;Network Library=DBMSSOCN;Initial Catalog=<DB>;User ID=<UID>;Password=<PWD>;
The application successfully connects and reads data from one of the databases, but fails with error: "Login failed for user 'goa'." on another database.
The SQL Server log files shows the following:
Login failed for user 'goa'. Reason: Password did not match that for the login provided. [CLIENT: <IP Address>] and Error: 18456, Severity: 14, State: 8.

The only difference between the connection string that succeeds and the one that fails is the database.

The user does have access to both databases, and we are able to log in with that user in SQL Server Management Studio and do anything with either of the databases. Both databases are on the same SQL Server instance.

What puzzles me is that it claims wrong password when the same password worked fine less than a second before, when it connected to the other database.

Does this have anything to do with the limitations of SQL Server Express?

Application log file

Microsoft OLE DB Provider for SQL Server
Description = Login failed for user 'goa'.
HelpFileContext = 0
Error 0
Number = 0x80040e4d
Source = Microsoft OLE DB Provider for SQL Server
Description = Login failed for user 'goa'.
HelpFileContext = 0
SQL State = 42000
NativeError = 18456

Some attempts

We've tried changing the order of the connections, so that database B goes first, but it still fails at B; so it is NOT a matter of a limit to the number or frequency of connections.
We've made sure the user has access to both databases under *User Mapping* and by right clicking the databases themselves, going to Properties >> Permissions, and granting everything. It still can't connect.
We've tried changing the passwords: in which case neither connection worked. This gives me the impression that there is some encryption problem(?), and that the SQL Server tries to decrypt the password with the wrong key or something, and by chance uses the correct key in certain circumstances.
Adding Integrated Security=SSPI;, to the connection strings, did not help. Nor did Integrated Security=yes; or Trusted_connection=yes;.


texts: Set non-negative floor for rolling sum, in PostgreSQL

texts:
This is a really fun question (asked for SQL Server) and I wanted to try it out to see how it was done in PostgreSQL. Let's see if anyone else can do it better. Taking this data,
CREATE TABLE foo
AS
  SELECT pkid::int, numvalue::int, groupid::int
  FROM ( VALUES
    ( 1,  -1   , 1 ),
    ( 2,  -2   , 1 ),
    ( 3,  5    , 1 ),
    ( 4,  -7   , 1 ),
    ( 5,  1    , 2 )
  ) AS t(pkid, numvalue, groupid);

We're trying to generate this:
PKID   RollingSum    GroupID
----------------------------- ## Explanation:
1      0             1        ## 0 - 1 < 0  => 0
2      0             1        ## 0 - 2 < 0  => 0
3      5             1        ## 0 + 5 > 0  => 5
4      0             1        ## 5 - 7 < 0  => 0

The problem is described as,

When adding a negative number will cause the sum to be negative, the limit will be activated to set the result as zero. Subsequent addition should be based on this adjusted value, instead of the original rolling sum.
The expected result should be achieved using addition. If the fourth number changes from -7 to -3, the fourth result should be 2 instead of 0
If a single sum can be provided rather than a few rolling numbers, it would also be acceptable. I can use stored procedures to implement a non-negative addition, but that would be too low-level.
The real life problem for this is that we record order placed as positive amount and cancelled as negative. Due to connectivity issues customers may click the cancel button more than once, which will result in multiple negative values being recorded. When calculating our revenue, "zero" need to be a boundary for sales.

Their solutions are all using recursion.


texts: How should I configure the RAID array of SSD drives on my SQL Server?

texts:
I'm building a SQL Server with 48 GB RAM, 1 CPU, & 8 SATA III (6GB/s) SSD drives (128 GB Crucial m4) and an LSI MegaRAID controller (SAS 9265-8i). I expect the typical work load to be mostly reads. There will be some periods of heavier write activity (hourly data syncs w/ 3rd party data providers - nightly backups), but the I suspect the typical read/write ratio is about 90% reads/10% writes.

Option 1:
Logical Drive C: - RAID 1 (2 physical drives) - OS
Logical Drive D: - RAID 10 (6 physical drives) - DB files/logs/tempdb/backups?

OR

Option 2:
Logical Drive C: - RAID 1 (2 physical drives) - OS
Logical Drive D: - RAID 1 (2 physical drives) - Db Files
Logical Drive E: - RAID 1 (2 physical drives) - log files/backups?
Logical Drive F: - RAID 1 (2 physical drives) - tempdb

OR

Option 3:
Other suggestions?

I'm thinking option 1 would give me better performance, since all DB activity would be striped across 3 drives (and mirrored across the other 3 in the array), although option 2 seems to mimic conventional wisdom (which appears to apply more to mechanical drives than SSDs). It seems like Stack Overflow has gone with option 1.

I'm guessing with SSD's it's OK to put every thing on a single logical drive since your server is probably more CPU constrained instead of I/O constrained at that point?

Another question I have is where should I place the nightly backups? We don't want backups slowing down the rest of SQL server, and I'm guessing writing the backups the same location as the logs is a good practice because the read/write behavior in both those cases is sequential writes.


texts: Is it possible to set a composite NOT NULL constraint in PostgreSQL

texts:
For logging purposes, in a table I design, I want to store the IP information of users when they logged in.

When a user logs in, IPv4, IPv6 or both IP addresses should be stored. But I want to set a constraint that both cannot be NULL.

Is it possible to do that in Standard SQL with constraints or should I do that using PL/pgSQL in PostgreSQL or in the business tier?


texts: SQL Server Transaction Commits but results are missing

texts:
I am not sure how to describe the problem, so I will do the best I can and answer any questions.

My project is a Database Project for SQL Server 2008 (client dependent). Within that project I have a series of Seed Scripts that are compiled into a single sql script. The bulk of those seed scripts deal with inserting a large number of VARBINARY records (200MB+ worth of inserts). Since Visual Studio (and most other text editors) have trouble dealing with large files, I have the inserts logically split out into different files. Each file is wrapped in a transaction. The inserts themselves are constructor inserts.

The problem I have is each transaction commits without error. However I am getting the incorrect number of records in the finished database. Or the other effect I am seeing is zero records in the resulting table. While the scripts run, I see records inserted but once the script completes all the records are gone! So ... very confused. Any insight as to why I might be seeing this behavior would be great!

Each is laid out like e.g.

BEGIN TRANSACTION AdBannerBlackEagle
PRINT N'BEGIN TRANSACTION AdBannerBlackEagle'

PRINT N'AdBanner BlackEagle English'

INSERT INTO [dbo].[ContentImageTemplateDetails]
    ([ContentImageTemplateId]
    ,[ContentImageTemplateDetailsCodeKey]
    ,[Data]
    ,[Checksum]
    ,[ContentType]
    ,[RegionID]
    ,[LanguageCode]
    ,[DisplayOrder]
    ,[CreatedDate]
    ,[ModifiedDate]
    )
VALUES
    (
        1,
        'AdBanner'
        ,0xFFD8FFE10018457869660000<reduced for brevity>
        ,'5F44C068DEE6507FDB01F52C9D66C291E06896DAACB2A6D687BDCE0EE3A5CE47'
        ,'jpg'
        ,5
        ,'en-DE'
        ,1
        ,SYSDATETIMEOFFSET()
        ,SYSDATETIMEOFFSET()
    ),
    (
        1,
        'AdBanner'
        ,0xFFD8FFE10018457869660000<reduced for brevity>
        ,'059FF9C3FCAF65EEDC01719D791AE88791C5C1A647BBC7F229A5F3FB0862D54D'
        ,'jpg'
        ,5
        ,'de-DE'
        ,2
        ,SYSDATETIMEOFFSET()
        ,SYSDATETIMEOFFSET()
    )
GO

COMMIT TRANSACTION AdBannerBlackEagle
PRINT N'COMMIT TRANSACTION AdBannerBlackEagle'


texts:
I am building an appointments web app...the user will be able to see available/closed appointment slots for a business entity.
I am trying to use this schema  here

In it, there is a possible appointments table...left joining it with booked appointments table we arrive at the available appointments table.

It's structure though as depicted in the example does not reflect my needs...in my app the business entity  enters its schedule...for example:
Monday-to-Friday 10:00AM-16:00PM...

The question is what structure a table must have that will hold data like this...as I said this table will hold all possible appointment slots and its data must be based on the schedule...like the one above.


texts: What happens when I reach the last id with MySQL InnoDB?

texts:
So perhaps I don't know the technical name of the 'id' column which has autoincrement so if a similar question exists, I cannot find it. I was wondering about the limit of the number of rows in an InnoDB table and the id reference that goes with it, and what happens when the limit is reached (assuming it can be reached).

It's a risk I have to consider. I have a table which contains a series of tens to hundreds of records which can be added/removed regularly. Assuming my product reaches enormous success (?), assuming records are changed regularly, the id field will quickly tick by. Add a year or more, even five years...

I asked a similar question to this on stackoverflow some time ago and from what I recall I was just asked not to worry about it but nobody could direct me to a hard answer.

Anyone care to share?

Thanks...


texts:
New to the database side of IT and I have a few questions...

I'm trying to figure out what servers and databases I can access through SSMS.

What is the best practice for installing/using SQL Server Management Studio?
Should I install it on my laptop, then connect to servers remotely?
Or install it on one of our SQL Servers and access with a remote desktop connection to that server from my laptop?

Also, do all servers have databases that can be accessed or do they have to be "SQL Servers"?

Thank you for replies in advance!


texts: Generate create table DDL from any arbitrary select query?

texts:
Using allroundautomations PL/SQL Developer, is there a way, given any arbitrary select query like this:

select myfavoritenum, myfavoritetxt
from tablewithmanycolumns

to magically receive a create table statement:

create table justmyfavorites (
    myfavoritenum number,
    myfavoritetxt varchar2(30)
)

I'm aware of CREATE TABLE AS SELECT, but I don't necessarily want to create the table right then, or in that particular database, etc.

Attempting to Google up a solution, I also became aware of DBMS_METADATA.GET_DDL, but that appears to be for existing objects, not arbitrary queries.


texts: How to increase fast backup and restore of 500GB database using mysqldump?

texts:
There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables.

Backup and restore using mysql dump taking quit a long time or days.

max_allowed_packet=1G
foreign_key_checks=0
auto_commit = off


texts: MySQL Slave SQL Running State - “Waiting for dependent transaction to commit” meaning

texts:
Quite often the Coordinator thread on our replica database has the status "Waiting for dependent transaction to commit".
I'm a little confused as to what this means, and I can't see this status in the list of thread states in the MySQL documentation.
I'm mainly curious if the "dependent transaction" is a transaction currently running on one of the worker threads on the replica (as in a table is potentially locked), or if the "dependent transaction" is something occurring on the Master that the replica is waiting for?


texts:
I own a WordPress installation, and Updated to 3.4.1.

After the upgrade, I experienced a very strange slow-down.

After all, I used the

define('SAVEQUERIES', true);

in my wp-config.php with combined with the following code in my theme footer.php

global $wpdb;
echo "<pre>";
print_r($wpdb->queries);
echo "</pre>";

and I found that while I don't have many queries (49 queries I got), there are

4 Queries took more than 9''
3 Queries took more than 8''
4 Queries took more than 7''
4 Queries took more than 6''
8 Queries took more than 5''
1 Query took more than 4''

Here I will show you some of the queries:

/* Execution time 7.0095062E-5 */
SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') AND tt.count > 0 ORDER BY t.name ASC

/* Execution time 5.3167343E-5 */
SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (77375,77379,77381)

/* Execution time 9.2029572E-5 */
SELECT wp_posts.* FROM wp_posts WHERE ID IN (77381,77379,77375)

In most of the queries, the query is simple, with no complexity. I cannot imagine why the queries are so slow.

The WordPress database contains in about 60,000 records and running on Percona-Server.

Any ideas for for what the issue might be, and how I could fix it ?


texts:
I am using the query below to generate a report. I have a requirement to generate the report so it only applies up to a maximum timestamp (such as until yesterday, 2016-08-02). To do so, I need to modify the second line of the query so that I no longer get values of eventsink.blueboxevent.created that are greater than the limit of yesterday.

For example, if I ran the report on the sample data below with a requirement to only look at the timeframe up until yesterday (2016-08-02), I would want to see a record as follows:

Desired Result

device1 | 2016-08-02

Sample Data

DeviceID | eventsink.blueboxevent.created
device1 | 2016-08-03
device1 | 2016-08-02
device1 | 2016-08-01

Current query to be modified

SELECT eventsink.blueboxevent.deviceid     AS 'Device ID',
       Max(eventsink.blueboxevent.created) AS 'Last Connection',
       provisioned,
       Max(softwareversion)                AS 'S/W Ver.',
       buildingname                        AS 'Name',
       buildingaddr1                       AS 'Address',
       buildingcity                        AS 'City',
       buildingstate                       AS 'State',
       zipcode                             AS 'Zip Code',
       countrycode                         AS 'Cnty'
FROM   eventsink.blueboxevent
       JOIN md.elevator
         ON md.elevator.deviceid = eventsink.blueboxevent.deviceid
       JOIN eventsink.blueboxmasterdataevent
         ON eventsink.blueboxmasterdataevent.deviceid =
            eventsink.blueboxevent.deviceid
       JOIN (SELECT Min(eventenqueuedutctime) AS 'Provisioned',
                    deviceid
             FROM   eventsink.blueboxevent
             WHERE  id = '1'
             GROUP  BY deviceid) t
         ON eventsink.blueboxevent.deviceid = t.deviceid
WHERE  ( id = '9'
         AND softwareversion LIKE '[1-2][.]%' )
GROUP  BY eventsink.blueboxevent.deviceid,
          provisioned,
          buildingname,
          buildingaddr1,
          buildingcity,
          buildingstate,
          zipcode,
          countrycode
ORDER  BY provisioned DESC


texts: SQL Server - Alter table vs drop and create

texts:
Our database is SQL Server 2008 R2. We have some tables that have some varchar(500) columns that I want to switch to datetime2 or bigint. I can guarantee all the data in the columns to be switched are valid for the proper type. The column changes do affect indexes, but not keys.

While discussing with colleagues, we have come to two ways to approach the problem. Both these would be done through T-Sql scripts.

Create a temp table via select into, drop the old table and recreate the table with the proper datatypes. Recreate the indexes.
Alter the current table/data types via
ALTER TABLE x ALTER COLUMN Y datetime2 and then rebuild or recreate the indexes.

Because I am confident the data will convert cleanly, I am leaning towards #2. My colleague and a DBA friend prefer #1 but my colleague can't remember why they trained him that way. The DBA friend is on vacation so I didn't ask him why.

Can someone provide insight on which option they think is better and why? Ultimately it is my decision and I am wondering why #1 would be preferred over #2?


texts: How can I create a trigger that prevents column delete, data deletion and table truncate in the same time?

texts:
Having written this trigger create:

CREATE OR REPLACE TRIGGER PROTECT_DB BEFORE DROP OR TRUNCATE ON X.SCHEMA

How can I "add" a DELETE in the trigger definition ? (the DELETE should prevent data deleting from tables and column erasing).


texts: Database design for monthly leaderboards

texts:
I have a system where users can make predictions on events. There are about two events a week. When the event ends the system scores all the predictions and updates this in the database. Here is an approximate representation of my current DB strucutre:

Users table

+----+-----------+--------+
| ID |  username |  score |
+----+-----------+--------+
|  1 |  alice    |     12 |
|  2 |  bob      |     22 |
+----+-----------+--------+

Games table

+----+-----------------+----------------------+------------+----------+
| ID |    gameTitle    |       gameTime       |  questions |  answers |
+----+-----------------+----------------------+------------+----------+
|  1 |  Football match |  2019-08-11 14:00:00 |  {json}    |  {json}  |
|  2 |  Hockey game    |  2019-07-11 15:00:00 |  {json}    |  {json}  |
+----+-----------------+----------------------+------------+----------+

Predictions table:

+----+--------------+--------------+-------------+--------+-------+
| ID |  gameID (FK) |  userID (FK) |  prediction |  score |  rank |
+----+--------------+--------------+-------------+--------+-------+
|  1 |            1 |            1 |  {json}     |      6 |     1 |
|  2 |            1 |            2 |  {json}     |      4 |     2 |
|  3 |            2 |            1 |  {json}     |      2 |     2 |
+----+--------------+--------------+-------------+--------+-------+

So initially 'score' and 'rank' in predictions are NULL, and then after the event ends I run a script that goes through all entries for that game and scores it for the user and generates a rank.

With this structure I can do things like:

List the highest scoring users of all time (SELECT from USERS, score descending)
List the rankings for each event (SELECT rank from PREDICTIONS where gameID = X, descending)

However, what I now want to do is be able to get time-constrained rankings. So I want to be able to see which users scored highest for all events in the current month, or for the month of February etc.

I know I could do this manually by going through the Games table and getting all gameIDs that are in the given month, and then going through the Predictions table for all predictions with these IDs, and adding up the score for each user and then sending this. However that seems crazily inefficient and resource-intensive. Especially considering that this will be an API call

I'm therefore wondering how I can accommodate this in my current database - if it's possible and/or advised, or if I should use some other technology.

One idea would be have generate a new table for each month, and
update and pull from this when relevant.
Another would be to have a 'monthly score' column in the User table,
that resets each month. However this wouldn't have historical data,
which would be nice (though I guess could always be generated)

I feel like there might be some database paradigm trick that I'm missing, so it'd be great to hear a proposed solution for this.


texts: How do I select the total count of records over the past week, including days which have no records in MySQL


texts: Column as an Included column or as a key column

texts:
I have a query which has an optional where condition on column classID(not unique).
This same classID is being displayed as a select column in various other queries.
Currently i have classID as an included column in an index idx,so i think that the idx will be used when classID is in select statement.
Do i have to create a a non clustered index for the classID as a key column when the query has where condition for classID.?

Confused on this whether to create separate non-clustered indexes for all the where clause columns.?