texts: Adding a password authenticated user to a peer authenticated postgres setup not working

texts:
I would like to add a read-only user to a postgres setup on a remote server with default peer authentication, that uses a password as an authentication method.
I create my user like this:
CREATE USER my_user WITH PASSWORD 'some_pass';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO my_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT ON TABLES TO my_user;

and I have added a rule to the pg_hba.conf file, stating that this particular user should be able to access the database using a password.
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   my_db           my_user                                 password <- my new line
local   all             all                                     peer

And then I start psql from the command line like this:
psql -d my_db -U my_user -W

and provide 'some_pass' when prompted. This works fine locally, but on the server I get this error:
psql: FATAL:  Peer authentication failed for user "my_user"

Do I need to restart postgresql so that the changes in pg_hba.conf will take effect, or is there something else missing?  I am reluctant to restart postgresql since it is being used on a production environment.
EDIT: I mention that the server is remote, but I log into it first using ssh before accessing the database, meaning that the connection to the database is in fact local.


texts: If a CTE is defined in a query and is never used, does it make a sound?

texts: Do unused CTEs in queries affect performance and / or alter the generated query plan?


texts: MySQL error when trying to import tablespace

texts:
I'm following the steps described here, but the import of one of the tables is failing with the error below. It's a large table, with a ~22GB .idb file.

[ERROR] [MY-012762] [InnoDB] Page offset doesn't match file offset: page offset: 262144, file offset: 262144

For what is worth, I'm using Percona 8 and not the vanila MySQL.

Any help is much appreciated.


texts: Schema Changes Are Not Populating to The Subscribers

texts:
One of my publications was dropped mistakenly. I restored it from the latest backup but now schema changes are not replicated to the subscribers.

What can I do to re-initialize/fix the publication?


texts: What is the best relationship model for high performance?

texts:
I want to model a relationship between Customer and Transaction.

However, because Customer is only described by a unique string and no extra info, I have two possibilities to implement it in the database:

Customer(Id, NameString) --> Transaction(CustomerId, OtherDetails...)

or

Transaction(CustomerNameAsString, OtherDetails...)

I have to manage a few hundreds transactions per sec, and store a several million transaction in the table.

Each new transaction INSERT is done after 1 or 2 SELECT queries on the table, like SELECT count transactions (customer, lastMonth).

Is there a big performance penalty on one model or another?

In other words, what is the faster way?  Should I use a join and an integer index, or a string index with no join?

I am using Oracle.


texts: Truncate text output of COPY command for postgres

texts:
I've got a script that copies every table in a schema to a csv using the following:

psql -Atc "select tablename from pg_tables where schemaname='$schema'" --host=$host -w --user=$user $database |\

 while read TBL; do
    psql -c "COPY \"$schema\".\"$TBL\" TO STDOUT WITH DELIMITER AS '|' CSV HEADER" --host=$host -w --user=$user $database > "${TBL}_${timestamp}.csv"
  done

Which by and large works great.  Now I've been asked if I can:

Remove the milliseconds from the timestamp output
Stop CLOBS from being moe than 6000 characters

Is there any way I can continue doing what I'm doing?


texts: Oracle pivot on a column with delimited data

texts:
My data is like:

keycol,col1,col2,col3
1,a;b;c,some data,some other data
2,x,some data,some other data
3,y;z,some data,some other data

Where a column is delimited in the source system with semicolons.

And I want to pivot it to:

1,a,some data,some other data
1,b,some data,some other data
1,c,some data,some other data
2,x,some data,some other data
3,y,some data,some other data
3,z,some data,some other data

I found a technique here, but I can't quite get it to work:

CREATE TABLE yt
    (keycol int, col1 varchar2(5), col2 varchar2(9), col3 varchar2(15))
;

INSERT ALL
    INTO yt (keycol, col1, col2, col3)
         VALUES (1, 'a;b;c', 'some data', 'some other data')
SELECT * FROM dual
;

INSERT ALL
    INTO yt (keycol, col1, col2, col3)
         VALUES (2, 'x', 'some data', 'some other data')
SELECT * FROM dual
;

INSERT ALL
    INTO yt (keycol, col1, col2, col3)
         VALUES (3, 'y;z', 'some data', 'some other data')
SELECT * FROM dual
;

I thought I could include the keycol in the CONNECT BY this way to get parallel recursive chains, but I guess it doesn't work like that.  I'm pretty sure I've done this with recursive CTEs in SQL Server.

SELECT keycol
  ,trim(regexp_substr(col1, '[^;]+', 1, level)) col1
  ,col2
  ,col3
FROM yt t
CONNECT BY keycol = PRIOR keycol AND instr(col1, ';', 1, level - 1) > 0

http://sqlfiddle.com/#!4/3d378

FWIW, I'm on Oracle 10g.


texts: Find material for improvement in Microsoft BI

texts:
Which websites, books etc. can you recommend me to improve my basic knowledge in Microsoft Business Intelligence 2008, SSAS, SSIS and SSRS?

Please remember that I'm a newbie in technical BI tool.

If possible, it would be great to retrieve any material for task work that would be similiar as doing lab task at university.

// Fullmetalboy


texts: Message system - delete message, or flag for deletion?

texts:
I'm wondering, what is the best approach about message deletion in a mysql based message system.

Plan A:
When the sender sends a message, it will be saved in 2 versions. One for the sender and other for the recipient. This way everyone can immediately delete his own message from inbox/outbox.
This approach has an advantage of immediately delete a row in database, but there are 2 time more rows for same number os messages, than the plan B.

Plan B:
The sent message is saved once, the sender and recipient see the same message. They only mark that row for deletion and a cron job has to delete only those rows, that are marked by everyone for deletion.
Advantages: only one row/message,
Disadvantages: messages are deleted rarely, because they need to be marked by two users.

Are there other advantages/disadvantages for these plans?
Is there a better way to do this? The goal is to make a fast system, optimization for speed is significant.


texts:
We have a third party analytics platform, which allows the end users to create their own tables and charts from a selection of pre-defined views. This queries a MS SQL Database.

Unfortunately, it seems the SQL Syntax this software injects into the database to query the data, is massively inefficient, or so it appears, based on my knowledge and understanding.

For example, here is what a query could look like which joins two tables together:

SELECT tblOne.ColumnOne, tblOne.ColumnTwo, tblTwo.ColumnThree
FROM (SELECT * FROM tblOne) AS tblOne
JOIN (SELECT * FROM tblTwo) AS tblTwo ON tblOne.id = tblTwo.id

Now imagine these tables have many columns each, or there are many more joins to additional tables, each following the same pattern - am I correct in assuming this would perform a full table scan in the sub queries, effectively reading more than actually necessary? Am I also correct in assuming that the below would actually be more efficient?

SELECT tblOne.ColumnOne, tblOne.ColumnTwo, tblTwo.ColumnThree
FROM tblOne
JOIN tblTwo ON tblOne.id = tblTwo.id

I just want a second opinion, before I write a strongly worded email to the developers of this analytics solution, just in case I am misunderstanding how the engine would handle a query like this.

Thanks in advance.


texts: Best solution to fixing database design with GUID as primary key

texts:
I am after some confirmation of this idea to fix a badly performing database or a better suggestion if any one has one. Always open to better suggestions.

I have a very large database (20+ million records growing by about 1/2 million per day) which are using GUID as PK.

An oversight on my part but the PK is clustered on SQL server and is causing performance issues.

The reason for a guid - this database is partially synchronised with 150 other databases so the PK needed to be unique. The synchronisation is not managed by SQL Server, rather there is a custom process built which keeps the data in sync for the requirements of the system - all based on that GUID.

Each of the 150 remote databases don't store the full data as stored in the central SQL Database. they only store a subset of the data they actually require, and the data the require is not unique to them (10 out of the 150 database may have some of the same records from other sites databases for example - they share). Also - the data is actually generated at the remote sites - not at the central point - hence the need for the GUIDs.

The central database is used not only for keeping everything in sync, but queries from 3000+ users will be executed against that very large fragmented database. Already this is a big problem in initial testing.

Fortunately we are not live yet - so I can make changes and take things offline if required which is at least something.

The performance of the remote databases is not a problem - the data subsets are pretty small and the database usually never gets above 1GB in size in total. The records are fed back to the main system quite regularly and removed from the smaller BD's when no longer required.

The performance of the central DB which is the keeper of all records is woeful - due to a clustered GUID as a primary key for that many records. The index fragmentation is off the charts.

So - my thoughts to fix the performance issue is to Create a new column - Unsigned BIGINT IDENTITY(1,1) and then change the Clustered PK of the table BIGINT column.

I would create a Unique Non Clustered index on the GUID field which was the primary key.

The smaller remote 150 databases don't need to know about the new PK on the Central SQL Server database - its purely going to be used for organising the data in the database and stop the bad performance and fragmentation.

Would this work and improve the performance of the central SQL database and prevent future index fragmentation hell (to a degree of course)? or have I missed something very important here which is going to jump up and bite me and cause even more grief?


texts: InnoDB errors and crashes with MyISAM database?

texts:
I have one server with one mysql database containing only MyISAM tables. There is no other databases on the server (except system ones of course). Since it moved to mysql 5.7.27 there are InnoDB related errors and even crashes. The config is mostly the default one that comes with Ubuntu 18.04 package, I just adjusted some myisam buffers and cache. (Now I also increased innodb pool size, hoping it will make those errors go away)
What may cause those errors and how I get rid of them?
Examples of errors:

2019-09-19T04:39:34.464347Z 5169718 [Warning] InnoDB: Difficult to find free blocks in the buffer pool (21 search iterations)! 21 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version fsync is very slow, or completely frozen inside the OS kernel. Then upgrading to a newer version of your operating system may help. Look at the number of fsyncs in diagnostic info below. Pending flushes (fsync) log: 0; buffer pool: 0. 22967494 OS file reads, 55308825 OS file writes, 1252 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.
2019-09-20T08:27:30.209263Z 0 [ERROR] [FATAL] InnoDB: Page [page id: space=45, page number=4551] still fixed or dirty
2019-09-20 10:27:30 0x7f9350594740  InnoDB: Assertion failure in thread 140270684948288 in file ut0ut.cc line 910

Innodb tables:
+--------------------+---------------------------+
| table_schema       | table_name                |
+--------------------+---------------------------+
| information_schema | COLUMNS                   |
| information_schema | EVENTS                    |
| information_schema | OPTIMIZER_TRACE           |
| information_schema | PARAMETERS                |
| information_schema | PARTITIONS                |
| information_schema | PLUGINS                   |
| information_schema | PROCESSLIST               |
| information_schema | ROUTINES                  |
| information_schema | TRIGGERS                  |
| information_schema | VIEWS                     |
| mysql              | engine_cost               |
| mysql              | gtid_executed             |
| mysql              | help_category             |
| mysql              | help_keyword              |
| mysql              | help_relation             |
| mysql              | help_topic                |
| mysql              | innodb_index_stats        |
| mysql              | innodb_table_stats        |
| mysql              | plugin                    |
| mysql              | server_cost               |
| mysql              | servers                   |
| mysql              | slave_master_info         |
| mysql              | slave_relay_log_info      |
| mysql              | slave_worker_info         |
| mysql              | time_zone                 |
| mysql              | time_zone_leap_second     |
| mysql              | time_zone_name            |
| mysql              | time_zone_transition      |
| mysql              | time_zone_transition_type |
| sys                | sys_config                |
+--------------------+---------------------------+

Version
mysql> show global variables like '%version%';
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.7.27                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| tls_version             | TLSv1,TLSv1.1           |
| version                 | 5.7.27-0ubuntu0.18.04.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | Linux                   |
+-------------------------+-------------------------+


texts:
Need a suggestion from Datawarehouse and ETL experts. I have a project which has bunch of fact and dimension table, and we wire the facts using surrogate keys to dimension (religiously following kimball). We have had some incidents of worng wiring.

So I want to write few scripts that will do the data validation by doing the cross lookup and checking if each surrogate keys refer to the right record from appliaction database. I just wanted to ask you guys if you had to deal with any data validation in reports? My solution is not very efficient and very tedious to implement, wanted to check if any easier suggestion is there.
PS: I had this question on stackoverflow, but unfortunately got no response. So trying my luck here.


texts: Why are there Victimless Entries on the Deadlock Graph?

texts:
I am trying to learn how to analyze SQL Server 2008's deadlock graph, and I'm finding alot of entries with an empty <victim-list> node.  I don't understand what these entries represent: if there is no victim, how can I identify the waitresource that is causing the deadlock?  What do these entries mean?

Here's a quick example of the entries i'm seeing:

<deadlock-list>
 <deadlock>
  <victim-list />
  <process-list>
   <process id="processd2b6508" taskpriority="0" logused="10000" waittime="31" schedulerid="63" kpid="9104" status="suspended" spid="69" sbid="0" ecid="184" priority="0" trancount="0" lastbatchstarted="2012-07-30T01:10:45.550" lastbatchcompleted="2012-07-30T01:10:45.550" clientapp=".Net SqlClient Data Provider" hostname="XXXXXXX" hostpid="3648" isolationlevel="read committed (2)" xactid="30461033" currentdb="5" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="" line="1" sqlhandle="0x020000002340c50225c17d0eec9bf7c51129348edffd1c70" />
     <!--About 2 more frame tags... -->
    </executionStack>
    <inputbuf />
   </process>
   <!-- 3 or so more process tags... -->
  </process-list>
  <resource-list>
   <exchangeEvent id="Pipeb005eeba0" WaitType="e_waitPipeNewRow" nodeId="7">
    <owner-list>
     <owner id="processd23fdc8" />
    </owner-list>
    <waiter-list>
     <waiter id="processd2b6508" />
    </waiter-list>
   </exchangeEvent>
   <!-- 2 more exchangeEvents -->
  </resource-list>
 </deadlock>
</deadlock-list>

** edit **
As requested, here is the query used to identify a query by it's sqlhandle:

select sql_handle as Handle,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2) + 1) AS Text

from sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
where sql_handle = --0x04000D00E3572A56542E4601CE9E00010100001000000000

from RyanBoyer.net


texts: Mariadb support rtree index on innodb soon?

texts: I was looking through mariadb bug list online (https://mariadb.atlassian.net/secure/RapidBoard.jspa?rapidView=24&projectKey=MDEV), but I am unfamiliar with JIRA so I am not sure exactly what I am doing when looking, but will mariadb in the near future (few months) support rtree indexing for innodb? MySQL 5.7 has introduced boost libraries alongside rtree indexes to innodb quite a while ago, but I can't find any information on maria db including this into their build


texts: Find objects linked to a PostgreSQL role

texts:
Some times ago I created a PostgreSQL user named user1 (PostgreSQL 9.4.9).

I want to drop this user. So I first revoke all permissions on tables, sequences, functions, default privileges and ownership too:

ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON SEQUENCES FROM user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON TABLES FROM user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM user1;

REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM user1;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM user1;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM user1;

REASSIGN OWNED BY user1 TO postgres;

However it seems that one object remains linked to this user in 2 databases:

postgres=# DROP ROLE user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  1 object in database db1
1 object in database db2

It even seems to be a function:

postgres=# \c db1
You are now connected to database "db1" as user "postgres".
db1=# DROP ROLE user1;
ERROR:  role "user1" cannot be dropped because some objects depend on it
DETAIL:  privileges for function text(boolean)
1 object in database db2

But I can not determine which object is owned or related to user1.

If I pg_dump -s db1 | grep user1 I get no result! Could it be a global object?

How can I identify the missing object?

I have executed the commands in each database (db1 and db2). I do not want to drop objects owned by user1, just want to reassign or remove grants for this user.


texts: SQL Server 2012 linked server to SQL Server 2000

texts:
I am trying to select through an linked server from SQL Server 2012 to SQL Server 2000.

The linked server has been setup and I can browser the target database through the linked server which indicates that permissions are not a problem.

However, when I try to run a query, I get the following error (not my actual sql):

  Msg 7320, Level 16, State 2, Line 1
  Cannot execute the query "SELECT * FROM BOB" against OLE DB provider "SQLNCLI10" for linked server

Anyone have any ideas of what the problem is and how to solve it?


texts: ssas : "data type format not supported" with only data source, no data source view

texts:
I created a cube in SSAS for my company. This cube encompasses many dimensions and facts, but when I deployed it for the first time I got this error in SSAS : 'data type format not supported'.
I assumed some of the fields were not supported so I created an other multidimensional project and tried to add tables one by one. But with no table I got the same error!!
When I deleted the data source view I got the same error. The only object I kept in my project was th e data source, and this error was still there.
I did not have update in the msmdsrv.log file, and enable the query logs table was also a failure, as no line is added after a deployment try.
Can you either explain me this rather cryptic error(I don't think the datasource's fields are in fault).
By the way, the datasource point to an SQL Server database, with tables and views. All seem to be imported in my first attempt's project very well.
thank you.


texts:
I'm struggling to get my Oracle application working. I'm using the Oracle 11.2.0.4.0 instant client with a C++ application that is compiled using Visual Studio 2010. The machine is running Windows Server 2012 R2. The database is running on another machine.

The application is as basic as possible where I set up the environment and try to get the connection.

When trying to run it I'm faced with two different TNS errors:

ORA-12543: TNS:destination host unreachable (when using EZConnect string)

and

ORA-12154: TNS:could not resolve the connect identifier specified (when using the TNS name)

I'm running the application from the instant client directory, with ORACLE_HOME set as the same directory. I also export the directory of the oraocci11 dll files in the PATH variable.

What is strange is that sqlplus can connect without problems using both EZconnect and the TNS name (I have a tnsnames.ora in /network/admin).

What am I doing wrong?

Edit:
Here is tnsnames.ora:

G2CLIENT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.77)(PORT = 1521))
    )
  (CONNECT_DATA =
    (SERVICE_NAME = web)
  )
)

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  (CONNECT_DATA =
    (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

WEB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.77)(PORT = 1521))
  )
  (CONNECT_DATA =
    (SERVICE_NAME = web)
  )
)

and sqlnet.ora:

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, HOSTNAME)
SQLNET.AUTHENTICATION_SERVICES = (NTS)

The call that triggers the error is createConnection on a StatelessConnectionPool object which I have tried calling with different variations:

createStatelessConnectionPool("username","pwd","WEB")
createStatelessConnectionPool("username","pwd","10.0.1.77/WEB")
createStatelessConnectionPool("","","username/pwd@//10.0.1.77/WEB")

All of these seem to throw ORA-12154.

And as I mentioned above, sqlplus works fine.


texts: I apologize if this is not the correct place to ask this but something is bugging me. I could have swore that at a previous job I could be on the last line of code in SSMS and hit the down arrow and the cursor would move to the end of the line. Now it just stays in the middle of that last line. I'm using SSMS 11.0.2100.60. Is there a setting I'm missing or did I imagine that happening? :)


texts: Does setting DEADLOCK_PRIORITY to LOW cause a stored procedure to run slower?

texts:
I have a Stored Procedure that I have written that deletes rows from a table in the database.  When I will run the Stored Procedure in the Production environment it will run whilst another program is inserting rows into the same table.  In case there is a deadlock I want my Stored Procedure to be the one that is killed.

In the Stored Procedure I have set DEADLOCK_PRIORITY to LOW.

When doing testing in my test environment I am finding that having this option set it makes the deletion run a lot slower.  In the testing environment the process that inserts records is not running so, in theory, there is no locking issue.

I am finding that when the DEADLOCK_PRIORITY is set to LOW it is taking 1.5 hours to delete ~500,000 rows.  When the DEADLOCK_PRIORITY is not set (ie using the default setting) the deletion of the rows only takes around 15 minutes.

Is there a reason why there is such a difference?

The database is using Simple Recovery model in the test environment.  (In the Production environment it is using the FULL recovery model).

Thanks.


texts: MySQL server taking multiple seconds to run COUNT(*) query using an index on a MyISAM table

texts:
I have a MySQL database on a shared webhost, which has been running with its application for a couple years.

Today, this simple query:

SELECT COUNT(*) FROM tickets WHERE id = 123

Took 16 seconds to run. There is an index on id, the table is MyISAM, and this query usually takes milliseconds. The table has about 1 million rows, and the query should return a COUNT of about 250,000.

SHOW PROCESSLIST shows that all the time is being spent Sending data. I don't have root access, so no profiling help.

I have the same database on another server from the same host, and the query executes in 0.06 seconds.

The host thinks everything is running normally on the server. Is there anything I can point them at?

mysql> EXPLAIN SELECT COUNT(*) FROM tickets WHERE id = 214;
| 1 | SIMPLE | tickets | ref | id | id | 2 | const | 215307 | Using index |


texts: Why is this MySQL SELECT performing so slowly despite indexing?

texts:
I have a large MyISAM table A (500m entries) that connects two other tables B,C with a n:m relation.

So my table A looks like this:

CREATE TABLE `A` (
  `id_b` int(10) DEFAULT NULL,
  `id_c` int(12) DEFAULT NULL,
  UNIQUE KEY `Uniqueness` (`id_c`,`id_b`),
  KEY `id_b` (`id_b`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Every entity in B is related to 1-12 entities in C with an average of 8 and a mode of 10.

Every entitiy in C is related to 100-500 entities in B (but this is linearly growing over time).

Now for a specific entitiy c I want a list of all other entities in C that it is connected to through B and how many entities in B they are connect through.
Or more directly: I have a specific c, that is connected 100-500 b's which are each connected to a handful of other entities in C. I want to know which C's the b's are connect with, and how often.

My simplified query looks like this:

SELECT COUNT(OtherA.id_b), OtherA.id_c FROM A
INNER JOIN A OtherA
ON OtherA.id_b = A.id_b
WHERE A.id_c=[MY_SPECIFIC_C_ID]
GROUP BY OtherA.id_c

And takes over a second to execute. As described above, the inner join yields at max a few thousand results and the group reduces it to a few hundred. As far as I can tell I indexed correctly, so I am confused why this query is taking so long.

EXPLAIN yields the following result:

row1
id--------------1
select_type-----SIMPLE
table-----------A
type------------ref
possible_keys---Uniqueness,id_b
key-------------Uniqueness
key_len---------5
ref-------------const
rows------------462
Extra-----------Using where; Using index; Using temporary; Using filesort
row2
id--------------1
select_type-----SIMPLE
table-----------OtherA
type------------ref
possible_keys---id_b
key-------------id_b
key_len---------5
ref-------------THISDB.A.id_b
rows------------8
Extra-----------Using where

As suggested I have tried manually extracting the list of id_b's:

[concated_list] = select group_concat(distinct id_b) as id_list from A where id_c = ...;

And then running

SELECT count(id_b), id_c FROM A WHERE id_b IN([concated_list]) GROUP BY id_c

The first query is instant, the second query takes almost as long as the original query, so it was suggested that I need to fine-tune my MySQL Vars. Where do I start?

EXPLAIN SELECT count(id_b), id_c FROM A WHERE id_b IN([concated_list]) GROUP BY id_c

results in:

id--------------1
select_type-----SIMPLE
table-----------A
type------------range
possible_keys---id_b
key-------------id_b
key_len---------5
ref-------------NULL
rows------------802
Extra-----------Using where; Using temporary; Using filesort

MySQL version is 5.5.35-0ubuntu0.12.04.2.


texts: MySQL 8 more than doubles the size of MyISAM table after optimization

texts:
I recently upgraded from MySQL 5.7 to 8.0. I have noticed some changes from 5.7 to 8. But I have not been able to find any discussion on following situation that I have been puzzling for a while.

I have a few tables that contain some UTF8 texts. When I optimize the table (from within mySQL, using OPTIMIZE TABLE), I noticed the MYD file's size more than doubled, such as going from 4M to 8M, roughly (and not exactly double, more like 2.1 times).

I thought this could be due to my upgrade from MySQL 5.7 to 8.0. So I duplicated the entire table within MySQL 8. The newly duplicated table has the size as expected. Then I ran optimization again on this table, and its size doubles again!

I also noticed the following.

First: this does not occur to all MyISAM table. Just some of them.

Second: in the older systems (5.7 and before), If I repeatedly run OPTIMIZE TABLE on the same table, in the second and onward times, mySQL would tell me that the table was up to date, and the optimization takes almost no time. But in MySQL 8, and only for those tables that doubles the size after OPTIMIZE TABLE, every time I run optimization, it does optimization to reach a status OK. It never reaches "table is up to date" status, and the file size does not change after doubling.

Anyone runs into similar situations? Any explanations?


texts:
I am trying to enhance the search performance of a table based on whether certain columns contain values.

I have the following columns :

id |
link |
text |
date |
lat |
long |
type

If a record has a value in lat & long it can be shown on a map. It’s considered a “dot”

If a record has a value in date & lat & long it is considered an “event”

If a record has a value in type and text it is considered “news”

With over 1 million records in the table, what is the best way to index so I can quickly extract all “dots” or all “events” or all “news”?

To reiterate, I don’t care to index the actual values of these columns, only if there is ANY value in that column.

I tried creating an index of multiple columns but it used the values of the columns, and so many often unique values resulted in slow performance.

I considered using a “hash” based on which columns were not null, but I couldn’t extract those 3 variations beaded on the hash result.

Any advice appreciated.


texts: Backup all privileges related to specific user of MySQL

texts:
I want to backup all privileges related to specific user (for example u_1) from a MySQL database and restore that in another server. As described here, The 'mysql' database contains users/privileges/passwords. So I have to backup all related data from all tables of mysql database (mysql.user,mysql.db, ...). I can run this command:

$ mysqldump -u root --flush-privileges -r mysql.sql -p mysql

But of course the mysql.sql contains all users and all privileges.

Also I tried this command:

$ mysqldump -u root -p --where="user.user='u_1'" mysql user> mysql.sql

But as expected, it only contains a row of mysql.user table.

Is there a way to strip out other users except of u_1?


texts: Need help figuring out correct permissions for SQL Server Users

texts:
In most of my databases I create, I need to give permissions to do the following:

SELECT/INSERT/UPDATE/DELETE on all tables in the database
Execute all (user) stored procedures in the database

Will giving users datareader and datawriter be enough for the sproc's or what other roles/permissions do I need to give?

If I need to do more than just datareader and datawriter, could you point me in the right direction to creating a script to affect all users in the database.

Thanks in advance


texts: Why does coalescing make this query quicker?

texts:
I have the following query

SELECT SQL_NO_CACHE
    `table1`.*
FROM
    `table1`
LEFT JOIN `table2` ON table2.id = table1.table2_id
WHERE
    (`table2`.`date_assigned` >= '2015-06-21')
    AND (
        `attempt_1_date`IS NOT NULL
        OR `attempt_2_date`IS NOT NULL
        OR `attempt_3_date`IS NOT NULL
        )
    AND (
        `attempt_1_date` >= '2015-08-16'
        OR `attempt_2_date` >= '2015-08-16'
        OR `attempt_3_date` >= '2015-08-16'
        )
    AND (
        `callback_date`IS NULL
        AND `callback_by_account_id`IS NULL
        AND `callback_result`IS NULL
        )
    AND (
        (
            `attempt_1_result` NOT IN ('complete,incorrect,decline,prospecting')
            OR `attempt_1_result` IS NULL
            )
        AND (
            `attempt_2_result` NOT IN ('complete,incorrect,decline,prospecting')
            OR `attempt_2_result` IS NULL
            )
        AND (
            `attempt_3_result` NOT IN ('complete,incorrect,decline,prospecting')
            OR `attempt_3_result` IS NULL
            )
        )

    #AND (`table2`.`date_completed` IS NULL)
    AND (COALESCE(`table2`.`date_completed`, '') = '')

If I coalesce the date_completed field first, my Database program says the query comes back in 0.000 seconds, yet if I used the above (commented out) line, just checking IS NULL, it takes just over 10 seconds. Both only return the same 5 results.

table1 has 24 columns, and ~171,000 rows, and table2 has 172 columns*, and 1.7 million rows. In 1.4 million of those rows, date_completed is null.

If you need any more information, just let me know.

Few more details I was advised to include:

MySQL version: 5.5.41-0ubuntu0.14.04.1-log

Explain outputs:

Using COALESCE

`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`
1, 'SIMPLE', 'table1', 'index_merge', 'table2_id,attempt_1_date,attempt_1_result,attempt_2_date,attempt_2_result,attempt_3_date,attempt_3_result', 'attempt_1_date,attempt_2_date,attempt_3_date', '9,9,9', NULL, 8, 'Using sort_union(attempt_1_date,attempt_2_date,attempt_3_date); Using where'
1, 'SIMPLE', 'table2', 'eq_ref', 'PRIMARY,ind_table2_date_assigned', 'PRIMARY', '4', 'database.table1.table2_id', 1, 'Using where'

Using IS NULL

`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`
1, 'SIMPLE', 'table2', 'ref', 'PRIMARY,ind_table2_date_completed,ind_table2_date_assigned', 'ind_table2_date_completed', '9', 'const', 10, 'Using where'
1, 'SIMPLE', 'table1', 'ref', 'table2_id,attempt_1_date,attempt_1_result,attempt_2_date,attempt_2_result,attempt_3_date,attempt_3_result', 'table2_id', '5', 'database.table2.id', 1, 'Using where'

Few more notes.

date_completed is type DATETIME
There are no entries where date_completed = ''

*120 of those rows relate to 60 questions, in the format question_x_score, and question_x_value


texts: Do I need to execute "create extension pgcrypto" everytime?

texts:
I have used crypt() in one of my functions to hash user password. But what I observed the next day I started my pc that the same thing failed as cryptographic functions became unavailable upon restart. It showed message like functions gen_salt() / crypt() is unknown or not defined. I executed create extension pgcrypto; once and it started working.

How to handle this in prod environment? Is there a way to make this module permanently available in db even if db is restarted?

Or do I have to instruct server manager to run this command in case they required to restart db server?

Or can I write this statement in functions itself so that it will create the extension every time before using cryptographic functions?

Moreover, we are using a read-replica of db, where it did not allowed to execute this "create extension" statement as there is read permission only. So how to make this cryptographic functions available in such read-only environment?


texts: Partitioning a table on a column in another table

texts:
I have a fact table called FactAccount which has the following columns:

AccountId (identity)
AccountMonthId - FK referring to DimMonth
AccountYear

The DBA has done the partitioning on the AccountYear (2014, 2015, 2016...) column. But this table will be queried on the AccountMonthId column (with a join to DimMonth) where users will select the year and month from the DimMonth dimension table.

So in this case, I don't think the partitioning will make any difference as the WHERE clause doesn't filter by AccountYear.

The DimMonth dimension contains all combinations of year and month, i.e. 12 dimension members for each year and the AccountMonthId column is an identity column, not a natural key.

Is there a way to partition a table on a column in a table referenced by a foreign key? Or, to be very specific in my case, is it possible to partition the FactAccount table on DimMonth.AccountMonthId?


texts: Turn off telemetry in SQL Server install config file

texts:
I'm creating a SQL Server 2017 Image Completion config file, and the automatically generated one has a bunch of stuff about "telemetry". I clicked no to the prompts, but it still put several user accounts in the config file, yet no true/false option for the telemetry itself. Here are the user accounts it included in the config file:

  ; TelemetryUserNameConfigDescription

  SQLTELSVCACCT="NT Service\SQLTELEMETRY"

  ; TelemetryStartupConfigDescription

  SQLTELSVCSTARTUPTYPE="Automatic"

  ; ASTelemetryStartupConfigDescription

  ASTELSVCSTARTUPTYPE="Automatic"

  ; ASTelemetryUserNameConfigDescription

  ASTELSVCACCT="NT Service\SSASTELEMETRY"

  ; TelemetryUserNameConfigDescription

  ISTELSVCACCT="NT Service\SSISTELEMETRY140"

  ; TelemetryStartupConfigDescription

  ISTELSVCSTARTUPTYPE="Automatic"

I suppose I could change the startup type to manual, but I would rather just remove all this garbage. Any idea how to turn this off via config file?


texts: Is Oracle 11g database supported in windows 10?

texts:
When I tried to install oracle 11g on Windows 10 Home. I got the below error after the wizard started the installation:
ORA-03113: end-of-file on communication channel.

I tried in another PC also windows 10 but the installation failed with the same error.


texts: Reinitialize Table Values in SQL SSDT Unit Testing

texts:
I am creating SQL Server Unit Tests. We are testing various stored procedures.

In Unit testing principles, it is good practice to setup a small database table, populate values, and tear down (truncate/delete) the database tables, and resetup for each test. This way every unit tests will have a clean environment to validate sprocs which insert, select, update, delete, etc,

Does anyone where or how to reinitialize the tables values in Sql Unit Testing? Resources are pretty new for unit testing in SQL SSDT VS 2017, so I think lot of people are trying to figure out and understand.

Feel free to show or add pictures below.

http://www.sqlservercentral.com/articles/Unit+Testing/155651/
http://www.erikhudzik.com/2017/08/23/writing-sql-server-unit-tests-using-visual-studio-nunit-and-sqltest/

Pictures in Visual Studio SSDT:

Also, trying to review this class in SQLDatabaseSetup.cs:

 [TestClass()]
    public class SqlDatabaseSetup
    {

        [AssemblyInitialize()]
        public static void InitializeAssembly(TestContext ctx)
        {
            // Setup the test database based on setting in the
            // configuration file
            SqlDatabaseTestClass.TestService.DeployDatabaseProject();
            SqlDatabaseTestClass.TestService.GenerateData();
        }

    }
}

using Microsoft.Data.Tools.Schema.Sql.UnitTesting;


texts: Filtered index statistics refresh threshold

texts:
We have filtered indexes in our production environment. While doing some research about them, I came across this article "Filtered indexes and filtered stats might become seriously out-of-date"

It's a fairly simple filtered index based on a code value of 0

CREATE NONCLUSTERED INDEX
    [IX_InsuranceOffer_FIX_OfferCode0]
    ON [dbo].[InsuranceOffer]
(
    [OfferId] ASC
)
WHERE ([OfferStatus]=(0))
WITH (PAD_INDEX = OFF,   STATISTICS_NORECOMPUTE = OFF
, SORT_IN_TEMPDB = OFF,  DROP_EXISTING = OFF
, ONLINE = OFF,          ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

distribution looks like

code   codeCount   code_distribution
------ ----------- -----------------
6      26186769    93.7526
0      1743401     6.2416
5      1107        0.0040
7      495         0.0018

Our intention was to modify the existing index to include code 5. Based on this tipping point article, I believe both queries should continue to use the filtered index.

I have questions out to the system owners trying to understand the volatility of the codes.

Until then, I looked at sys.dm_db_index_physical_stats an attempt to understand whether our current index rebuild/reorganization strategy is sufficient for keeping up with the filtered index. I suspect it isn't but my internal-fu is weak.

index_level avg_fragmentation_in_percent            fragment_count       avg_fragment_size_in_pages page_count           avg_page_space_used_in_percent          record_count
----------- --------------------------------------- -------------------- -------------------------- -------------------- --------------------------------------- --------------------
0           0.6276                                  20                   143.4                      2868                 90.0984                                 1743401
1           42.8571                                 7                    1                          7                    86.0285                                 2868
2           0.0000                                  1                    1                          1                    1.4455                                  7

sys.stats for this index shows it was last updated '2012-01-06 22:03:11.147'

Is the above data sufficient information to base an index rebuild decision upon or would I need to have additional metrics involved?  Or, for filtered indexes do we even care about fragmentation and should just explicitly update statistics at X interval?

I claim the answer "it depends"

Only semi-related question was Minimum rowcount for filtered index?


texts: Entity with >150 attributes: One table or many tables with relationships

texts:
I have entity with 170 attributes. It's data from vehicle monitoring. Every entity has datatime label and unique id of gps terminal. Datetime and id of terminal - these are conditions for GROUP BY operations. I could create one table for all entity:

CREATE TABLE rows {
   terminal_id long reference terminals(id),
   time timestamp,
   -- description 170 attributes
   PRIMARY KEY(terminal_id, time)
}

or I could create many tables with relationships:

CREATE TABLE rows {
   row_id long PRIMARY KEY,
   terminal_id long  reference terminals(id),
   time timestamp -- need create index for group by
}

CREATE TABLE gps {
   row_id long references rows(row_id),
   -- description gps attributes
}

CREATE TABLE fuel {
   row_id long references rows(row_id),
   -- description fuel attributes
}
-- etc.

Please advise optimal structure for the database of this type.


texts: What are the downsides of using varbinary[max]?

texts:
I want to set up a table to store waveform data from an oscilloscope. The table would have a column of varbinary[max] because the waveform data from the scope is returned as a binary sequence that ranges from 1,400 bytes to 14,000,000 bytes. I would have a half dozen more int and small varchar for parsing information. So each row in the table will represent all information related to this waveform.

I am a complete noob to working with databases. I just set this one up and created my insert queries. I have no idea how using such a large type may affect responsiveness, speed, or anything else.

I can say that I will never want to use this varbinary column to do any filtering or searching. This table is mostly for record storage.


texts: Downgrade working postgres cluster 10 to 9.x

texts: Is there anyway to downgrade working postgres cluster 10 to 9.6? There are major changes (i.e, pg_wal / pg_xlog, etc.,) been done in 10. So far, I decided to take dump and restore into new 9.x cluster. Is there any other feasible way to do that?


texts: Should I restart the instance after adjusting Memory Allocation and MAXDOP?

texts: As far as I know adjusting Maximum server memory and Max Degree of Parallelism does not require the instance to be restarted. Will new settings always come into effect without restart?


texts: Split database for security and privacy reasons

texts:
Currently I have a small business where I have a main product (written in PHP and MySql), several users, several companies, etc.

The software occupies around 20 tables in its operation. The problem is that I want to expand the business and create a different program, but I want to unify the data.

A new program (different function) will use about 30 tables, but in reality there is a "core" that are the same tables, let's say 10.

Here I have two options:

Create a larger database with
20 + 30 tables, (since some were repeated) = 40 tables.
Or create different and connected databases

DB_core = 10 tables
   DB_1 = 10 tables
   DB_2 = 20 tables

Why using different databases?
I already want to develop this second software with a different team, but I don't want to give them access to some sensitive or valuable information, tables of people, companies, etc. This data will keep it in the "core" database and only in the production server.

From what I understand, users can be created with permissions by tables, but I feel that it is much easier to keep us in separate databases. Are there any significant performance implications? Is the most extensive programming worth it?

PD: Sometimes, I need the programmers team manage the production server.


texts: Show processlist / Max concurrent connections seem to max out at 131

texts: When I put my database under a load test (basically just a basic write and basic read from our app), and run show processlist; in the middle of it, there is always 131 rows in the set and that's it. I'm running a master slave with 16GB of memory on each on a Joyent Percona instances which is based on Solaris 10. They are configured to each use up to 8GB memory and 5000 max concurrent connection. What could be limiting it to 131?


texts:
I work on something and I need to check some views. I use this code:

SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME LIKE 'TEMP_T%'

To check if it works I:

added a table,
created a view of "SELECT * FROM TEMP_T",
altered the table by adding a new column,
and check the status again.

From what I understand (did a bit of research) this should invalidate the view, but it didn't. I also removed the initial column of the table (after creating the view), but the view still appears to be valid. In the end I even droped the table, but the view still is in STATUS='N'.

Can you guys help me understand why is this happening? I assume it's a bug in DB2, but as I'm new to DB I'd like to hear some other opinions.

Here is the log:

CREATE TABLE DB2INST1.TEMP_T ( COL_A  CHAR(5) )
DB20000I  The SQL command completed successfully.

CREATE VIEW DB2INST1.TEMP_V AS   SELECT * FROM DB2INST1.TEMP_T
DB20000I  The SQL command completed successfully.

SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME LIKE 'TEMP_%'

TABLE                            SCHEMA                           STATUS TYPE
-------------------------------- -------------------------------- ------ ----
TEMP_T                           DB2INST1                         N      T
TEMP_V                           DB2INST1                         N      V

  2 record(s) selected.

ALTER TABLE DB2INST1.TEMP_T ADD   COLUMN COL_B VARCHAR(20)
DB20000I  The SQL command completed successfully.

ALTER TABLE DB2INST1.TEMP_T DROP  COLUMN COL_A
DB20000I  The SQL command completed successfully.

REORG TABLE DB2INST1.TEMP_T
DB20000I  The REORG command completed successfully.

SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME LIKE 'TEMP_%'

TABLE                            SCHEMA                           STATUS TYPE
-------------------------------- -------------------------------- ------ ----
TEMP_T                           DB2INST1                         N      T
TEMP_V                           DB2INST1                         N      V

  2 record(s) selected.

DROP TABLE DB2INST1.TEMP_T
DB20000I  The SQL command completed successfully.

SELECT SUBSTR(T.TABNAME,1,32) AS TABLE, SUBSTR(T.TABSCHEMA,1,32) AS SCHEMA, T.STATUS, T.TYPE FROM SYSCAT.TABLES T WHERE T.TABNAME LIKE 'TEMP_%'

TABLE                            SCHEMA                           STATUS TYPE
-------------------------------- -------------------------------- ------ ----
TEMP_V                           DB2INST1                         N      V

  1 record(s) selected.


texts: Create publication is not working in PostgreSQL

texts:
I am trying to create a publication (for logical replication) in PostgreSQL database but it throws an error:

indsolv_dev=# CREATE PUBLICATION my_publication;
ERROR:  syntax error at or near "PUBLICATION"
LINE 1: CREATE PUBLICATION my_publication;


texts: Transforming multi column table to different structure table

texts:
I am a software developer who got data in the following format:

TABLE [dbo].[SensorValues](
    [timestamp] [datetime] NOT NULL,
    [Sensor1] [float] NULL,
    [Sensor2] [float] NULL,
    [Sensor3] [float] NULL
)

I would like to transform it to another table, corresponds to the scheme my application expect:

TABLE [dbo].[SensorValuesNormalized](
    [Id] [smallint] NOT NULL,
    [TimeStamp] [datetime2](3) NOT NULL,
    [Value] [real] NOT NULL
)

Id is the number of the column in the original table, the X column will be transform with X as an id.

I can use C# / Python to go over each line on the source table, and generate rows for Sensor1, Sensor2 and Sensor3.

I wonder if there is an SQL Query I can use to transform data from the first table to the second while retaining the data ?

e.g.

[timestamp] [Sensor1] [Sensor2] [Sensor3]

2012-10-17 05:47:40.387 | 5.0 | 7.3 | NULL

I would like the output to be:

[Id] [TimeStamp] [Value]

1    2012-10-17 05:47:40.387  5.0
2    2012-10-17 05:47:40.387  7.3

EDIT: I have more than 200 sensors, timestamp is unique in the original table, and not unique in the destination.


texts:
I'm realising that this is the first time I've stored temperatures in a MySQL DB.

What do think the best mysql field type for temperatures (c) is?

Decimial (3,3) ?


texts: Converting columns from NVARCHAR to VARCHAR

texts:
I have several large tables with many NVARCHAR(MAX) columns. This database is only for US use and I don't think there are any foreign characters or letters in any of the columns, so I wanted to look into:

Changing many of these columns over from NVARCHAR to VARCHAR, and also
Resizing these columns to more appropriate sizes based on some data profiling I have done.  I know for example that the max length of the data in some of these columns are only around 30 characters so NVARCHAR(MAX) seems like overkill.

I am also trying to limit implicit conversions.

What is an easy way to determine before doing this if each column does indeed only have VARCHAR data in it and won't have any issues with being converted away from NVARCHAR?


texts: How can I choose the best autogrowth option for my new database?

texts:
I want to create a new database that I can use for testing, restoring it from a full backup from a production database. These backup file sizes are around 45,486KB, and the production database size is 3.7GB. When I try creating the new database, the default initial size (MB)says 45 for the Primary rows data and 3739 for the Log, Autogrowth by 5MB restricted to 50MB for the data and by 10 percent restricted to another large number!

Any advice on what is the ideal number to start with? When do I need to change them? I'm using SQL Server 2008. Thanks.


texts: Login permission on Availability group database

texts:
We have an availability group database. Just wanted to check on the below statement  whether it needs to be run on all replica (By failing over and making it primary)or it can be run on the primary and the permission will be replicated to the replicas.

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE, VIEW DEFINITION ON [DB_Lock] TO sqlogin

SQLOGIN is a sql login and has been mapped to the  availability group database user.

DB_Lock is a table on the Availability group database. Thanks for the help!


texts: Clustered Index Scan on 5+ billion rows instead of using non clusterd index for <1000 rows

texts:
We have a huge SQL 2017 database containing more than 5 billion internetpages. One of the table contains the info about the page: PageID is the primary clustered key and UriKey is the non clustered index so we can lookup based on the Uri of the page. There are a lot more columns in that table as well, related to that page.

If we join with other tables and we join on the UriKey, the query will use the non clustered index only if we only require the UriKey itself. Once we add another column, it does a clustered index scan, wich can take up to days to complete.

This uses the correct index and takes less than a second to complete
https://www.brentozar.com/pastetheplan/?id=rkk1vIb07

select kps.Page_ID, p.PageID, r.*, p2.UriKey
from LB_Keywords_Pages_Static kps
    inner join spidir2.dbo.se_pages p
    on kps.page_id=p.pageid
    inner join Temp_KeyboostRedirects r
    on p.UriKey like '%/'+ r.source +'/%'
    inner join Spidir2.dbo.SE_Pages p2
    on p2.UriKey='https://www.keyboost.nl'+ r.target
where Account_ID=-2147482063

This selects one column extra (p2.Domain_ID) and takes more than a day to complete https://www.brentozar.com/pastetheplan/?id=ByOpUIb0m

select kps.Page_ID, p.PageID, r.*, p2.UriKey, p2.Domain_ID
from LB_Keywords_Pages_Static kps
    inner join spidir2.dbo.se_pages p
    on kps.page_id=p.pageid
    inner join Temp_KeyboostRedirects r
    on p.UriKey like '%/'+ r.source +'/%'
    inner join Spidir2.dbo.SE_Pages p2
    on p2.UriKey='https://www.keyboost.nl'+ r.target
where Account_ID=-2147482063

When we specify the index to be used, it works like a charm https://www.brentozar.com/pastetheplan/?id=SkvkKIbAm

select kps.Page_ID, p.PageID, r.*, p2.UriKey, p2.Domain_ID
from LB_Keywords_Pages_Static kps
    inner join spidir2.dbo.se_pages p
    on kps.page_id=p.pageid
    inner join Temp_KeyboostRedirects r
    on p.UriKey like '%/'+ r.source +'/%'
    inner join Spidir2.dbo.SE_Pages p2 with (index(SE_Pages_UriKey))
    on p2.UriKey='https://www.keyboost.nl'+ r.target
where Account_ID=-2147482063

I noticed this strange behavior also with other keys and queries. I didn't notice this behavior before when we were using SQL 2016. This is causing our database to become very slow.

If we really have to specify every time the index to be used, wouldn't it be better to move to a nosql database?


texts:
We have a scenario where we need to restore a SQL Server 2012 backup to a SQL Server 2016 environment, drop a few files to make more room, take a backup, and restore it back to another SQL Server 2012 environment again. Using the "GENERATE SCRIPTS" task is not an option as the DB is extremely large and complicated. Basically, is there a way to retain the old compatibility mode without automatically upgrading it, while restoring, using, and backing up an older version database in a newer version server?

None of the process above is negotiable or will be elaborated upon, these are the constraints I have to work with. Thanks for any help!


texts: Loading image from file system into SQL query using openrowset

texts:
I have a directory with images and a table in my DB with the path of each file. The main application allow me to create reports where I can display an image, so I was thinking to use a query like:

SELECT [ID]
      ,[PT_CODE]
      ,[FILE_PATH],

      CASE WHEN [FILE_PATH] IS NOT NULL THEN
      (SELECT * FROM OPENROWSET(BULK [FILE_PATH], SINGLE_BLOB) TT)
      END
      AS IMAGE_LOADED
  FROM [DB].[dbo].[TABLE_MR_FILES]

But I keep getting the error:

  Incorrect syntax near 'FILE_PATH'.

I have try multiple combinations without luck to make the OPENROWSET read the path stored in the column [FILE_PATH]. What am I missing?

Note: I am using MSSQL 2012. I don't want to import the images into the DB just load them in the fly as needed by the report runned from the application. I have full access to the DB so if a store procedure is the solution I can go with it.


texts: SQL Server Management Studio - Debugger buttons greyed

texts:
I have for mission to enable Transact SQL from a remote computer. My database is on a server and i'm trying to use the debug from a computer on the same network. My firewalls are all open on both side. Both of my machines are in a domain, i'm trying to use the debugger with a user registered in the security connection and who gets the sysadmin rights. Everything looks OK, i can open the debugger but the buttons to step into the object are greyed.

When I try to debug on the machine itself it works without problem, but remotely I can't use F10 F11 etc .. You can see the pictures attached, where you can see the differences between local debugger screen and remote debugger screen:

When it works on local: https://gyazo.com/5a900c7f961f9f43af16be20843162d3
Doesnt work remotely: https://gyazo.com/7bd375164ca0ccf84dcb2fd8349fda04
User configuration: https://gyazo.com/81a9ff4cd00adedb07791595ec8984ce

Does anybody have an idea of what's going on ? Let me know if you need any information.


texts:
We have 2 SQL Servers at Production site and 1 SQL Server at DR site.

Please correct below things as I am not getting clear idea about these sync mode in AlwaysOn:

If I install SQL Server FCIs in Production environment (2 node) can I set one secondary to synchronous mode automatic failover and DR (Stand-Alone) server to Async mode?
Total 3 SQL Servers (Primary Sync, Secondary Sync and Secondary Async (Stand-Alone)) is it possible?
If SQL Server Cluster (2 Instances) is configured in Windows 2-node cluster, is synchronous mode possible?
Should all the SQL Servers be stand-alone to configure AlwaysOn synchronous mode and automatic failover?
Two SQL Servers in Production site, can I set secondary as Synchronous mode with automatic failover?


texts: Single query to get data from multiple tables in one database

texts:
I have 36 Microsoft SQL Server VM's, each with a specific database ending in _Sec as the DB name.  There are 2 tables in this database that I need to get data from in a single query: tblConnGroups and tblConnGroupsDet.

tblConnGroups has 3 columns that I need to include in the results

tblConnGroups.ID
tblConnGroups.GroupName
tblConnGroups.Provisional

tblConnGroupsDet has essentially one column that is needed in the results.

tblConnGroupsDet.GroupID
tblConnGroupsDet.DBName

tblConnGroups.ID is a key used in tblConnGroupsDet.GroupID, however in tblConnGroups the ID field has a one-to-many relationship to the GroupID field in tblConnGroupsDet.

The Connection Group Name only appears in the tblConnGroups with its ID, and other information, but the GroupID will be in each row of the tblConnGroupsDet with a member database name.

USE [Name_Sec];
GO
SELECT ID,Groupname,Provisional
from [tblConnGroups]
ORDER BY ID ASC

ID   GroupName       Provisional
514  Name_FY0910     0
844  Name_FY1011     0
1588 NameTest_FY1516 1

USE [Name_Sec];
GO
SELECT GroupID,DBName
FROM [tblConnGroupsDet]
ORDER BY GroupID ASC

GroupID  DBNAME
514      Name_Base
514      Name_FY0910
514      Name_State0910
514      Name_portal
514      Name_FY1011

I tried a couple of different join queries to match the tblConnGroups.ID and tblConnGroupsDet.GroupID and haven't quite figured out how to get the information I want out of these two tables.

Basically I need a query that will give me these results where GroupName will get repeated for each DBName where the ID/GroupID match:

GroupName,DBName,FileSystemPath

I'm hopeful that this is clearer than mud, I don't do a whole lot of DB Admin work, we've been trying to find a DBA, and this is a hat I don't put on very often.


texts: How do I read ERD Notation (crow's feet) to convert to Natural Language?

texts:
Background

I am attempting to insure I understand how to read ERD (Entity Relationship Diagram) notation so I can convert it to a natural language explanation.

I'm trying to insure I can explain a method of converting what I see in the diagram to natural language.

Let's use the following diagram:
(Please don't get too caught up on the correctness of the actual entities, they are just for our example.)

Natural Language Sentence Examples

Company to Address

Would this be a correct way of talking about the relationship from Company to Address.

A company has 1 to many addresses. (shipping, billing, etc).

This indicates that the company must have at least one address, right?

Does that match the notation I have in the diagram?

Company to Connection

Each company has zero to many Connections.

Is that correct?  Is that how I read that double-bar on the Company side?

How Are the Two Company-side Connectors Any Different?

Do the two company-side connectors indicate anything different at all?
Can you explain that?

General Help For Natural Language Translation?

Do you have a logical method with steps you could provide for examining each relationship and converting to natural language?

For example, with Company to Connection.

Where do I start reading?
How do I remember (or explain) that the crows foot on Connection indicates the number of Connections a Company has and not vice versa?

Thanks


texts: Moving primary key constraint from one index to another

texts:
I have a SQL Server database, and have run sp_blitz against it. This pulled out that there are a couple of heaps on moderate size tables (a few hundred thousand rows in one case).

Most of these tables have a primary key (one does not, but I'll deal with that later). The primary key itself has an unusual name - i.e. not the default one, when most of the tables have a default primary key name (PK_tablename).

There is an index with a name matching the primary key, which is unique and non-clustered.

I can rename the primary key, but I then think I should be creating a clustered index. If I do this, then I'll have duplicate indexes, so it would make sense to remove the non-clustered index. However, it's being used for the primary key.

If I move the primary key constraint from the old non-clustered index to the new clustered index:

Does this make sense?
Are there any things I should be aware of?
What is the best method to do this?


texts: Cannot Find Job Associated Active Email Notifications

texts: We have a email notification going via our SQL Server however I cannot find any job or setting associated with it. Its definitely the correct server and I am logged in as admin. The email is sent via the email operator as setup on the associated server. The email is definitely sent from the SQL server in question as when I query the msdb in the sysemail_mailitems the email the recipients received definitely exists as shown in the screenshot below. All we want to do is change the recipients for the notification but only if we knew where....


texts: CDC with SSIS: process all changes in order of occurrence

texts:
I have a SSIS package that I'm using to sync (in an on going basis) all changes from one table to another. Note that I need to sync ALL changes and not just NET changes.

My problem is that I cannot figure out how to process the changes in the same order that they occurred in on the source table.

As a starting place, I used CDC in SSIS for SQL Server 2012. The Control Flow for my setup looks like:

And the data flow looks like:

The above setup is working for syncing all changes, just not in the correct order. Reading Processing Modes for the CDC Source, two relevant points are made:

"The order that the operations took place can be determined by the
__$seqval field"
"You will (typically) not want to use the CDC Splitter when processing all changes, as sending your change rows down multiple paths will make it difficult to apply them in the correct order."

So that's definitely my issue. Does anyone know how I can process the changes from the CDC Source in the correct order? I have not been able to find any examples online and I'm somewhat new to SSIS.


texts: Building where clause based upon parameters

texts:
In PostgreSQL I am trying to build a where clause in a function that uses an inbound parameter to determine the contents of the IN

For example:

select fld1, count(fld1)
from xyz
where fld1 in (
    case $1
        when 1 then 'Value1'
        when 2 then 'Value2'
        when 3 then '''Value1'',''Value2'''
        when 4 then '''Value4'',''Value5'',''Value6'''
    else NULL
)
group by fld1

Value 1 works fine as does Value2, Values 3 and 4 fail not syntactically but no rows are returned.


texts: query for showing columns from various tables, grouping and showing calculated field in each row of the result

texts:
I am doing a stock management system.
In one table I store items:
ITEM

id_item

item_name

ORDER

id_order

date

ORDER_ITEM

id_ord_item

id_order : foreign key to ORDER id_order primary key

id_item : foreign key to ITEM table.

qty_ordered

STOCK_IN : this table reflects item entries for related item quantities for orders

id_entry

id_ord_item: foreign key to ORDER_ITEM table.

description: optional

in_qty : In of a specific quantity for a specific item in a specific order

in_date: the date where the item quantity arrives to warehouse

Having this structure: I wanted to show a report or query table like this containing the following fields:
item_name
,
date (order date)
,
description,

qty_ordered,

in_qty,

in_date.

qty_left (calculated field) qty_ordered-sum of in_qty to date

Not sure if I should store the sum of arrive quantities to date in one column and use that to calculate the quantity left then.
Next to those, I want to show a calculated field that in each row that is the pending qnantity left to arrive, which should be the aty_ordered - the sum of arrived item quantity to date (in_qty).
Also, my goal was to show only the items that have a quantity left to arrive for a selected order. Items can come in different shippings, not all quantity ordered at once, and I want to register the date each shipping comes and the quantity of arrival of each item and the date.
I was thinking of not storing calculated values in table fields but I'm not sure if I should.
Also I thought of showing all this in a single query, but I'm not sure if this is possible.
Maybe I should make an agregate query for showing the order and it's item with the item quantity ordered, it's total quantity arrived to date and then in a separate query list all records of the ins of that item and their dates.
I can't figure out a way to do this propery. I thogght I could show a list of items grouped by items inside those orders, and then below list all entries.
I hope someone can though some light on this to me. I'm not sure how to approach this problem.


texts:
We are using SQL Azure Premium P1 which comes with 8GB ram and 150 IOPS. For a couple of hours, we have been seeing timeouts on SQL queries (even fairly simple queries take time).

In logs for SQL Azure we have seen that our physical read IOPS has increased from a steady 5 IOPS to around 130 in a matter of minutes but our actual production in the system relying on SQL Azure has not increased.

The minute it jumped to 130 IOPS the log shows that the database memory usage decreased from a very steady 4.8GB to around 1GB

I think it makes sense that If memory usage drops, physical reads increase.

I have looked at queries being executed on the server but see nothing out of the ordinary except for the slower response.

My question is whether there any reasons why SQL Azure (or SQL server for that matter) would drop things from memory and favor disk IO, even if there is plenty memory available.


texts: Merge MySQL tables without data repetition

texts:
My situation is this: I have several DIFFERENT tables in my MySQL database with the same name columns. Such tables can contain (or not) the same data.

What I need to do is a kind of "merge" between these tables, creating a new table that will contain only the data repeated once.

In other words, I want to check for duplicate records in multiple tables, leaving one with the records without repetition.

A example to clarify:
I have 2 tables (in fact I have 10 tables, but 2 works for the example) with the columns id, name and city like below

+----+---------------------------------------------------------+-----------------------+
| id | name                                                    | city                  |
+----+---------------------------------------------------------+-----------------------+
| 6  | HOTEL PANAMBY GUARULHOS                                 | guarulhos             |
| 31 | PLAZA INN MASTER                                        | new york              |
| 40 | PLAZA INN AMERICAN LOFT SãO PAULO                       | sao paulo             |
| 41 | PLAZA INN PARIS                                         | paris                 |
+----+---------------------------------------------------------+-----------------------+

The id '41' of table 1 could be equal to id '80' of table 2, for example and so subsequently. My task is compare the tables and create a new one without repetition. Don't exist any FK between tables, they are isolated.

Until now, I discovered two approaches to deal with this.

1) Using SELECT UNION. Apparently is the best approach, but I couldn't see the correct joins and group by to use it, once I don't have FKs;

2) Using a Stored Procedure with cursors for two tables and compare row by row. I tried use this one, but SP it isn't my speciality, so i created a good one, but the tables has over 1000 rows each one and my SP always crashed the memory.

I found this question here, but is not my case, I want a new table without repetition. Other questions in this site with similar problem talk about SQL Server and don't work for me.

Currently, I'm doing the workaround with a PHP script, but I'd like to know for any MyQSL solutions, if any.
I found some articles about Dataclean, but I don't know if this is the case and never work with this in MySQL.

I hope I was clear.


texts:
I would like to SET 'IS NULL' as a variable and use it in a WHERE clause.
I will be unioning many tables together that carry the same WHERE clause and would like flexibility to use a variable for my yearCompleted column - see example below-

DECLARE @YEARCOMPLETED
SET @YEARCOMPLETED = NULL

SELECT * FROM TABLE1
WHERE yearCompleted = @YEARCOMPLETED
UNION ALL
SELECT * FROM TABLE2
WHERE yearCompleted = @YEARCOMPLETED

In this instance is it possible to find rows where yearCompleted IS NULL using a variable?


texts: Postgresql tables seem duplicated (with suffixes such us pkey, hashes, etc)

texts:
due to some problems I came to check the state of my current database in production and I found that some tables are "duplicated". By duplicated I mean that there appear to be some clones of the actual table, with the same name and a suffix. An example:

Running the following command once connected to my database:

SELECT relname, reltuples, relpages * 8 / 1024 AS "MB" FROM pg_class ORDER BY relpages DESC;

Returns this:

                            relname                       |  reltuples  |  MB
----------------------------------------------------------+-------------+-------
 ...                                                      |             |
 reversion_revision                                       |      806547 |    58
 ...                                                      |             |
 app_session                                              |       96803 |    40
 ...                                                      |             |
 reversion_revision_pkey                                  |      806547 |    17
 reversion_revision_manager_slug_7521cb9583783434_like    |      806547 |    17
 reversion_revision_c69e55a4                              |      806547 |    17
 reversion_revision_e8701ad4                              |      806547 |    17
 reversion_revision_b16b0f06                              |      806547 |    17
 ...                                                      |             |
 app_session_dadd2494                                     |       96803 |     2
 app_session_f8a3193a                                     |       96803 |     2
 app_session_3ed54a03                                     |       96803 |     2
 app_session_4437cfac                                     |       96803 |     2
 app_session_pkey                                         |       96803 |     2
 app_session_e160a0b9                                     |       96803 |     2
 app_session_975a0aa5                                     |       96803 |     2
 app_session_b5ac7ae1                                     |       96803 |     2
 app_session_c585221c                                     |       96803 |     2
 ...                                                      |             |

I think they are duplicated since the number of reltuples is the exact same and the names share the original name (plus the weird suffix)

I am not a DB expert, but this seems wrong to me. Is there a problem with this? The only one I see (since everything works fine) is the space increase. Is there a clear solution? Did I perform something wrong? Is it dangerous?

I imagine it should be enough with deleting this "duplicated" tables. Would there be a programmatic way of doing this? Any hints? I am completely lost.

I am running psql (PostgreSQL) 9.3.13 under Ubuntu 14.04


texts: How to identify fragmentation level of the table data itself not the table indexes, and then defrag

texts:
Every where I can find descriptions on how to identify the fragmentation level of the table indexes, but I cant find how to identify the same for the table data iself.
Also defragmentation solution is found for table indexes but for table data can not find.

I am not talking about physical fragmentation of the database files either, nor vertical/horizontal fragmentation of tables.

I have a database (SQLServer 2008 R2) with about 20 tables (among about other 20 tables) that are continuosly being inserted and deleted their records (which do not have indexes).  Those tables will cause fragmentation of themselfs and the other tables inside the same filegroup.

So even if I make the filegroup files big to hold the data and reduced physical fragmentation due to auto grow, the internal fragmentation eventually will be increased, generating performance drops.

I hope my question gets picked for an answer.


texts: Why doesn't simple recovery model support log backup or tail log backup?

texts: Lets say there is a crash then we can use the full backup and a tail log backup - because if there was checkpoint then data would already be written to the database data file and flushed from the transaction log. Where as if there was no checkpoint yet then logs will still be there in the transaction log file. So is recovery possible in this recovery model?


texts: How to extract the date from this varchar column

texts:
I need to remove records from this table that are older than 30 days.

We only have 2 columns in this table. The first date after 'Printed_%' is what I will be using to determine if the record should be deleted. Notice some of the dates are in MM/DD/YYYY format while some appear in M/D/YYYY format in the string. How can extract the date so I can determine which rows are older than 30 days?

ID      Command
123232  Printed_9/6/2017 2:36:09 PM W;03;2K;8/6/2013;8/6/2015;ODSI3498
123233  Printed_9/6/2017 2:53:20 PM W;07;2K;8/6/2013;8/6/2015;PWEOIR123
123234  Printed_9/6/2017 2:54:44 PM W;15;2K;11/9/2011;11/18/2005;PWEOIR123
123235  Printed_10/21/2016 10:21:10 AM W;15;2K;11/9/2011;11/18/2005;ODSI3498
123236  Printed_11/15/2015 10:30:13 AM W;02;2K;8/7/2013;8/7/2014;ODSI3498
123237  Printed_12/7/2014 10:30:26 AM W;21;2K;7/30/2013;7/30/2014;ODSI3498


texts: Is it possible to have something like Union-FS for Oracle databases?

texts:
I wonder if it is possible to have something like what Union-FS is for filesystems also for databases?
Usecase
You have a webapp (with Oracle as database in the backend) with several instances: One production app and several work areas in which developers can make their changes and push them to production.
The 'workarea-webapp' should "see" all data stored in the production DB but if one of the developers changes data or inserts/deletes data (stored in the db) this should not affect the production db.
For files we use Union-FS which works fine but I wonder how to provide something like this for Oracle databases (Standard Licence)?


texts: Will one SQL instance work whilst the other 2 are patched?

texts:
My change request to patch 3 SQL instances on the same server was denied because of one instance - they say a server reboot is fine, but not a few hours downtime.

Seeing as each instance is standalone, should it not be fine to patch 2 instances and leave the third?

I see there is a 'shared components' feature - what is this exactly, is this down for all instances during the patching, and does it matter?

How can I assuage the fears of my colleagues that the third instance will still be working - the business are not willing for it to go down even for a couple of hours in the evening.


texts: MyISAM table keeps crashing. What are my options?

texts:
My MYSQL table is very large (250GB, around 200M rows). Recently I've been putting it through a lot -- parallel processes that do a large number of reads.

It has started locking and crashing with alarming regularity. About once per week. The recovery is obviously time consuming.

Does this seem consistent with overloading, or should I suspect another cause (e.g., hardware failure)?

Next, what are the best steps for resolving it? I think switching to InnoDB will probably take a very long time. I can dial down the processes accessing it, but I'm unclear how low to go to stop being "abusive". Any advice?


texts: How to Enable Windows Authentication in Firebird 2.5

texts: I am trying to enable Windows Authentication on a remote Firebird 2.5 database.  I modified the Firebird config file and changed the authentication method to 'MIXED', restarted the Firebird services but so far have still not been able to use Windows credentials.  Is there additional steps to get this working?


texts: Analyze logs to tell if database was moved or copied

texts:
In SSMS when you right click on any database, under Tasks, there is option to Copy Database. One of the things the wizard asks you is whether you are copying or moving the database. It basically saves a package and runs it.

I'm on the server where the job was created, and which is also the destination server. I need to find out whether the database was copied, or moved, from the source server. How can I find that out (other than of course checking the source server)?


texts:
We have a large query that works fine locally but deadlocks on AWS RDS. According to CloudWatch, we are not resource-bound on CPU, disk or IOPS, and nothing else should be accessing these tables.
The gist of the query is a three step process.
Pivoting
The upstream data is millions of records of (site, key, value) with a dozen or so standard keys. The pivot (which works on AWS) creates a new table with (site, value1, value2, value3,...) records. There are over 800k rows in the pivot table.
A modest amount of processing is done at this stage, mostly COALESCE() to allow some key-value pairs to override other key-value pairs.
Indexes and Analyzing
We add the indexes required to optimize the following query and VACUUM ANALYZE the newly created tables.
Denormalization
The pivot table values are foreign keys into various other tables. We denormalize the references for subsequent processing with a Java application. For performance reasons we create a table although a materialized view would probably also work. The table creation is a straightforward CREATE TABLE table_name AS SELECT * FROM view_name.
The foreign tables have a modest amount of data, typically on the order of dozens to hundreds of entries although one may hit 10,000+ records. All have the necessary indexes to optimize the query (per EXPLAIN).
A modest amount of processing is done at this stage, mostly CASE statements which tweak data irregularities.
The denormalization takes a long time (~hour) but always succeeds on my desktop. It has consistently failed on AWS, even at 24+ hours. As I mentioned above it's not resource-limited and we have verified that the hangup is locks on the pivot table.
Does anyone have any idea what could be the cause or a fix? It's a straightforward view with a fair number of scalar subqueries on foreign keys but no other joins. The table is large, over 800k records, but it's one record in -> one record out.
Our next step is to check the behavior with a smaller dataset but success would only tell us that we could try breaking apart our query into smaller pieces, not why we're seeing the deadlocks and what we can do to prevent them.


texts: Does ALTER TABLE - ADD COLUMN lock the table?

texts:
I have a question about the ALTER TABLE ... ADD COLUMN DDL statement.

On a Amazon RDS instance with MariaDB v10.2, I've noticed that INSERT statements complete and the rows are correctly inserted in the table (as verified via SELECT) before an ALTER TABLE ... ADD COLUMN on the table finishes.

Shouldn't any DML statement that performs a write be queued until the ALTER TABLE operation finishes?

I'm posting this question because I've been asked to perform some test to verify whether it is possible to run ALTER TABLE ... ADD COLUMN on a live Production database in business hours, on a heavily-used database, on tables with several million rows -- which I find very ill-advised. Even if ALTER TABLE does not place a lock on the table, it will have to wait until any connection is not using the table anymore (due to the connection placing a metadata lock), which may happen much much later.
EDIT: Apparently this evaluation was too pessimistic. I've been doing several tests with mysqlslap performing heavy operations on the table (INSERT, UPDATE, DELETE statements, and SELECT statements with LIKE to avoid using indexes) on 150 simulated concurrent connections while ALTER TABLE ... ADD COLUMN runs; profiling shows metadata locks but with short waiting times (1 sec each), and table alteration completes in around 30 minutes, compared to 10 minutes with no SQL statements running. While this is satisfying, on the other hand I'd like to know whether it is safe to assume that DDL statements are non-blocking.

(It is probably worth of note that there is an Instant ADD COLUMN feature on InnoDB, which allows instant addition of a column to the table (under specific constraints), but it is not available before v10.3.2.)


texts: We converted a database table to be in-memory and the corresponding memory-optimized filegroup takes 1GB on the disk

texts:
SQL Server 2014 SP2.  As the titles says, we have converted one of our database tables to be in-memory.  After we did this, the corresponding memory-optimized filegroup takes up 1GB on the disk, but on a larger server it's up to 4GB).  I suspect it has to do with the number of CPUs.

The table is EMPTY!  The structure of the table is nothing special, something like this:

Id  int
StatusId    int
ReviewRequired  bit
SourceDate  datetime
UserID  int
InsertDate  datetime
UpdateDate  datetime

Why does it take up so much space on the disk?  I looked, but I couldn't find any way to control the size of the memory-optimized files created.

Here is T-SQL Script used for creating in-memory table:

    CREATE TABLE [dbo].[MyTable]
(      [Id] [INT] NOT NULL,
       [StatusId] [INT] NOT NULL,
       [Reviewed] [BIT] NOT NULL,
       [CreateDate] [DATETIME] NOT NULL,
       [RequestID] [INT] NULL,
       [InsertDate] [DATETIME] NULL,
       [UpdateDate] [DATETIME] NULL,
CONSTRAINT [PK_Id] PRIMARY KEY NONCLUSTERED HASH
(
       [Id]
)WITH ( BUCKET_COUNT = 16),
INDEX [IX_StatusId] NONCLUSTERED HASH
(
       [StatusId]
)WITH ( BUCKET_COUNT = 64)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )


texts: Restore Oracle database from cold backup files

texts: I have cold backup files for an oracle database. Can someone tell me a step by step procedure on how to restore the database on my machine.I am using Oracle 11g


texts: When to use sort_in_tempdb when rebuilding indexes?

texts:
We are debating whether to use the SORT_IN_TEMPDB option for our DW tables. My understanding is that there are more writes when using this option, although they are more sequential. We have a SAN (which has has been notoriously slow at times), so in our case we want to limit the number of writes as much as possible. I believe tempdb is on a separate LUN (set of disks).

We have plenty of disk space in our data file and on our tempdb file. In this case, would we benefit from using SORT_IN_TEMPDB?

One thing that struck me was this comment on this Answer

  When rebuilding an index you would need twice the space of the index +
  20% for the sorting. So in general to rebuild every index in your db
  you only need 120% of your biggest index in your DB. If you use
  SORT_IN_TEMPDB, you only win 20%, you still need an aditional 100% in
  your data file. Further more, using sort in tempdb increases your IO
  load drastically, since instead of Writing the index one time to the
  datafile, you now write it one time to the tempdb and then write it to
  the data file. So that is not always ideal.

We definitely don't want to increase our IO load with our slow/possibly misconfigured SAN.

What would be the best way to test this? By simply rebuilding the table with and without the option and log the times?

Edit: We have 8 tempdb files, each 15GB. We do have TF 1117/1118 flags set and IFI is enabled. We currently do a mixture of rebuilding with the sort_in_tempdb option and without it.

Thanks!

SQL Server 2012 Enterprise


texts: Count rows for each latest status of tickets

texts:
ticket_number |          datetime          | status
---------------+----------------------------+---------
             1 | 2020-08-03 03:52:58.048196 | replied
             1 | 2020-08-02 19:55:49.121455 | new
             2 | 2020-07-30 03:52:58.048196 | pending
             2 | 2020-07-28 20:15:41.213842 | replied
             2 | 2020-07-26 03:52:58.482911 | new
             3 | 2020-06-17 19:55:49.394628 | closed
             3 | 2020-06-14 03:52:58.513141 | replied
             3 | 2020-06-11 19:55:49.242859 | new
             4 | 2020-05-14 07:13:50.527481 | new
             5 | 2020-05-13 11:24:38.558921 | new

The above table ordered by datetime. After ordering I need to group by ticket_number so that each ticket appears only once, and pick the status for the latest datetime per ticket. Then I need to count rows for each status.
Ticket 1 would have a status of replied - replied_count = 1,
Ticket 2 would have a status of pending - pending_count = 1,
Ticket 3 would have a status of closed - closed_count = 1,
Ticket 4 would have a status of new - new_count = 1,
Ticket 5 would have a status of mew - new_count = 2
Expected Result:
replied_count  |  pending_count  |  closed_count  |  new_count
---------------+-----------------+----------------+------------
             1 |               1 |              1 |          2


texts:
I have a View in my database set up to display data for parts that my company uses for projects.

One of the columns in the View shows what our recorded price for the part is.  However, as time goes on, and prices change, there will be many such entries for part prices.

Is there a way within a View setup, to set it so that the value retrieved for a column is that last value in that table?

Or is this a situation where I'd need to include the date of the price log as part of the view data, and then do a TOP 1 ... ORDER BY DESC type query against my view?

EDIT:

For example right now if I query my View for specifically the part with Part_Number = '0010.05.20.20' I will get the following Table result:

Part_Number    Supplier     Manufacturer  Price  Discount  Currency
0010.05.20.20  A2A Systems  Vario         55.97  1.000     CAD
0010.05.20.20  A2A Systems  Vario         50.25  1.000     USD

I know that the 50.25 price value is the most recent one, because it is the one I just inserted into the database for testing purposes.  However, there is no way to know just from doing a SELECT * FROM PartsView query which price is the most current one.

This is the code for creating my View, to clarify, I am wondering if there is a way to modify this so that the Price column only shows the most recent entry for any criteria it is looking up.  If that is not possible, is my only discourse to add a 'Log Date' Column for when the price was logged?

CREATE VIEW PartsView AS
SELECT
    p.part_number          AS [Part_Number],
    s.supplier_name        AS [Supplier],
    m.manufacturer_name    AS [Manufacturer],
    pl.cost_per_unit       AS [Price],
    pl.discount_percentage AS [Discount],
    cu.currency_type       AS [Currency]
FROM
    dbo.parts      AS p     INNER JOIN
    dbo.suppliers  AS s  ON p.supplier_id = s.supplier_id INNER JOIN
    dbo.price_logs AS pl ON p.part_id = pl.part_id INNER JOIN
    dbo.currencies AS cu ON pl.currency_id = cu.currency_id
GO


texts: mysql import/drop very slow on pyshycal server

texts:
I have mysql installed on a physical server (name it Mysql A) and importing 1 MB .sql (from mysqldump) Magento file could take more than 5 minutes, and dropping database slow as well.

whereas importing the same .sql file into a mysql server that is installed on a VMware Virtual machine (name it Mysql B), it just takes 3 seconds.

the disk write/read test results on both physical and vm machine are equal.

Here is the settings on MySQL A (physical):
http://pastebin.com/3qAxCJxz

and MySQL B (vm):
http://pastebin.com/PqZePH8Z

both settings are default ubuntu mysql installations.

Any help on why it takes dramatically slower when importing/dropping database on my physical server? It's a HP Proliant ML-10 with 16GB of RAM, and  Intel(R) Xeon(R) CPU E3-1220 V2 @ 3.10GHz and the VM's host machine specs and performance has the same benchmark results as the server #A

Thanks


texts: SQL Server 2012 Availability Group issue

texts:
I need some assistance and some bandages for shooting myself in the foot. This is a DEV environment but I need to bring it up.

I am using SQL Server 2012 Enterprise on Windows 2008 R2. Somehow our AG listener group disappeared. I tried to recreate the AG listener and it was giving error regarding computer account not having permissions. The genius inside of me decided to delete AG group from Windows Cluster Admin and now I cannot recreate AG group (I should've done it via SQL Server instead of Windows).

I keep on getting the following error :

  Creating availability group resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)

  ADDITIONAL INFORMATION:

  Create failed for Availability Group 'SQLAG_D'. (Microsoft.SqlServer.Smo)

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

  The availability group 'SQLAG_D' already exists. This error could be caused by a previous failed CREATE AVAILABILITY GROUP or DROP AVAILABILITY GROUP operation. If the availability group name you specified is correct, try dropping the availability group and then retry CREATE AVAILABILITY GROUP operation.

  Failed to create availability group 'SQLAG_D'. The operation encountered SQL Server error 41042 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command. (Microsoft SQL Server, Error: 41042)

Does anyone have any suggestions?

The AG Group is already dropped in Windows Cluster and it is not showing in SQL Availability Group. The sys.availability_groups system catalog view has the "deleted" AG in it. There is no mention of SQLAG_D in it.


texts: Running EXPLAIN in a SQL client improve performances on production server

texts:
I have a MySQL 5.7.20, I use SQLYog Community as my MySQL client and I have a Tomcat Server running a Java application connecting with MySQL Connector 5.1.37.

My query is bad and is being rewritten, the question is not on how to improve that particular query. It loads the content of the table by chunck of 2000 lines, but does a JOIN on itself to also load multiple versions of a line, identified by a number, under the main version of the line.

SELECT t.*
FROM myTable AS t, myTable AS t2
WHERE t2.lineNumber = t.lineNumber
AND t2.version = 0
AND t.executionNumber='87710'
AND t2.executionNumber='87710'
AND t.lineNumber IN (24000,24001,24002,24003,[...],25996,25997,25998,25999)
AND t2.lineNumber IN (24000,24001,24002,24003,[...],25996,25997,25998,25999)
AND (t.version <> 0 OR ( 1  )) AND  1
ORDER BY t.lineNumber, t.version > 0, t.recordDate DESC;

In my case the total size of the table is a little over 160000 lines, so the load takes 81 executions of the query. This takes a random amount of time ranging from a few seconds to over an hour. What I notice is if I recreate the table from scratch and call the web server, each execution of the query takes about 43 secondes. If I run an EXPLAIN FOR CONNECTION using the MySQL client, I get a really bad execution plan.

1    SIMPLE    t    NULL    ref    executionNumber,lineNumber    executionNumber    4    const    80752    100.00    Using where; Using temporary; Using filesort
1    SIMPLE    t2    NULL    ALL    executionNumber,lineNumber    NULL    NULL    NULL    161504    5.00    Using where; Using join buffer (Block Nested Loop)

On the other hand, if I run a "regular" EXPLAIN and copy paste the query, I get a better execution plan.

1    SIMPLE    t2    NULL    range    executionNumber,lineNumber    lineNumber    4    NULL    2000    5.00    Using index condition; Using where; Using temporary; Using filesort
1    SIMPLE    t    NULL    ref    executionNumber,lineNumber    lineNumber    4    labo.t2.lineNumber    1    50.00    Using where

The weird part is, after running that second EXPLAIN, the performance on the web server improves and the Java call that was running for many minutes ends in seconds. I have reproduce this many times, waiting an arbitraty number of times between the start of the Java call and the run of the EXPLAIN. The query coming from the Java server always improve from the first query following my EXPLAIN.

So my question is, how can the execution of an EXPLAIN query changes the performances of another query ?

My best guess is that the MySQL query optimizer keeps offering a bad execution plan from its cache until I run the EXPLAIN, that forces it to compute a new execution plan, but I couldn't find any doc confirming that theory. And it wouldn't explain why the query optimizer offers a bad execution plan to a Java webapp executing a query and a better execution plan to a MySQL client running and EXPLAIN on that same query.


texts:
I have a table with many dates, ten years into the future and ten years history. (combined with customer id and more)

Most of the reads and writes from/to the database is for records with the current date.

How would sql server use an sorted unique index on the date field? Will it start from the top or bottom and scan half the index before it find the current date?

Or is there someway it wil start in the midle of the index and scan up or down based on the frist value in the index?


texts: Performance benefit of taking unused databases offline

texts:
I have several databases for legacy applications which run in VMs that are currently off until someone needs to look at some history. I have set each of the databases used by these applications to offline but I wanted to know:

Is there a performance benefit to having these unused databases in the offline state instead of sitting online but unused without any connections to them or queries run against them?


texts:
According to the PostgreSQL documentation:

  A host name specification that starts with a dot (.) matches a suffix
  of the actual host name. So .example.com would match foo.example.com
  (but not just example.com).

So I put this line in pg_hba.conf and restarted PostgreSQL:

hostssl market_data market_data_access .horsholm.dk md5

Then I attempt to connect to the database:

psql "postgresql://market_data_access@<server-IP>:5433/market_data?sslmode=require"

Instead of a password prompt, I get this:

psql: FATAL:  no pg_hba.conf entry for host "195.249.206.131", user "market_data_access", database "market_data", SSL on

Just to check, I make reverse DNS look-up of the IP address listed in the error message:

$ host 195.249.206.131
131.206.249.195.in-addr.arpa is an alias for 131.129-190.206.249.195.in-addr.arpa.
131.129-190.206.249.195.in-addr.arpa domain name pointer kommune.horsholm.dk.
$

What am I doing wrong?


texts: How do I merge and delete duplicated rows in SQL using UPDATE for SQL server 2014?

texts:
For example, I have a table of:
id | code | name | type | deviceType
---+------+------+------+-----------
1  | 23   | xyz  | 0    | web
2  | 23   | xyz  | 0    | mobile
3  | 24   | xyzc | 0    | web
4  | 25   | xyzc | 0    | web

I want the result to be:
id | code | name | type | deviceType
---+------+------+------+-----------
1  | 23   | xyz  | 0    | web&mobile
2  | 24   | xyzc | 0    | web
3  | 25   | xyzc | 0    | web

How do I do this in SQL Server using UPDATE and DELETE statements?
Note: Unfortunately I can't use the solutions given because SQL server 2014 does not have 'STRING_AGG' function.
Any help is greatly appreciated!