Is it normal to take days to compile PostgreSQL from source code? I am trying to install PostgreSQL 10 beta from source code by following their documentation. Is it normal that make and make install take more than 12 hours to complete? Would using extra CPU cores and memory speed things up? Is there a binary available for Debian 8?

Thank you!

How long it takes to compile will depend on a big number of factors, mostly the hardware (or resources of your virtual machine) on which you're compiling, plus your OS and environment settings, plus what else is your computer doing while you compile.

To have a reference of times, you can check PostgreSQL BuildFarm.

For instance, for a certain system called gull, which runs Debian 8.3, the gull PostgreSQL BuildFarm for date 2017-07-06 shows a Total run time
01:05:48.

Your system might actually be an order of magnitude slower and take 12 hours. It seems a bit "overkill", but possible. Although I don't know the specifics of compiling PostgreSQL for Debian (specially, how parallel it can be made), I'm sure that more memory and more (and faster) cores will decrease compilation time. It is also very likely that, at this phase of development, some settings to ease debugging might be different from releases on production, and (maybe) take longer to compile.

As for packages already compiled, check

Search PostgreSQL at debian.org
Package postgresql-10 (10~beta1-1)


MySQL backup full instance I am new to MySQL after using Postgres for long time, I wonder if there's an equivalent way to backup the data like pg_basebackup in MySQL or the only option is dump?

The equivalent backup tool for MySQL is Percona XtraBackup.
This does not do a dump like mysqldump, it makes a physical copy of the tablespace files without blocking clients using the database. The documentation is pretty good quality.


How to create oracle user for more than one version I have  server under RHEL 6.3 installed on that server Oracle 11gr2 and 12c, I want to create user lets say Ahmad but I want this user to be sysdba on both servers, for single server I edit .bach_profile to be like the following:

11g:

ORACLE_HOSTNAME=oracledev; export ORACLE_HOSTNAME
ORACLE_UNQNAME=DB11G; export ORACLE_UNQNAME
ORACLE_BASE=/oracle11gr2/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=DB11G; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

PATH=$PATH:$HOME/bin
unset USERNAME
cd $ORACLE_BASE
export PATH

and if I want to make him sysdba for 12c I use the following:

export TMP=/tmp
export TMPDIR=$TMP

export ORACLE_HOSTNAME=oracledev
export ORACLE_UNQNAME=cdb1
export ORACLE_BASE=/oracle12c/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/db_1
export ORACLE_SID=cdb1

export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

My question is it possible to marge those parameters together without making a conflict, if yes  please advise

For a start you can use oraenv script to set PATH, LD_LIBRARY_PATH, ORACLE_BASE and ORACLE_HOME:

export ORACLE_SID=ORCL

ORAENV_ASK=NO
. /usr/local/bin/oraenv

ORACLE_SID can be parsed from /etc/oratab file. ORACLE_HOSTNAME maybe from HOSTNAME environment variable.


CXPACKET Waits performance tune for SQL Server 2008 I have a SQL Server 2008 query that is working on millions of records.  The query is w/in a proc that is run nightly by a job.  The query can take a full day to run when I first put it on the server, but within a week or so it will drop down to less than one hour--without any intervention from me.  It FIXES ITSELF somehow.

The query runs in tempdb and, before it fixes itself, when I examine performance stats on it I find the following:
CXPACKET: 20,700sec or 66% of wait time.
PAGEIOLATCH:_SH 2,500 or 8% of wait time.
LOGBUFFER: 1500sec or 5% of wait time
IO_COMPLETION: 1500sec or 5% of wait time

I tried to tune indexes, etc. and the stats above are somewhat of an improvement over my first run when CXPACKET was 77% of wait time.  I read trouble shooting tips that said I should split my tempdb into one file for each CPU.  I have a dual cpu 32 bit W2K8 system and so I split tempdb into 2 files and greatly increased the size of each to 150 GB each 10% autogrow, but they aren't growing so I think the size is sufficient.

When I looked at the server while the query was running I could see that the CPUs were NOT pinned and were hovering down around <10% of their capacity.  What was pinned was DISK IO.  The machine has a single disk.

Without further ado, here are the two queries causing the trouble (the first query used to be a subquery of the latter-see explanation below):

insert into #ttcbm(tradeId1, tradeId2)
select distinct tp.tradeid tradeId1, tp1.tradeid tradeId2
from #tradeP tp
join #tradeP tp1
on tp.cmbId = tp1.cmbId
and tp.qs_plyrid = tp1.qs_plyrid
and tp.tradeId > tp1.tradeId
OPTION (MAXDOP 1)

insert into #mergeT(tradeId1, tradeId2)
select distinct tp.tradeid tradeId1, tp1.tradeid tradeId2
from #tradep tp
join #tradep tp1
on tp.cmbId = tp1.cmbId
and tp.tradeid > tp1.tradeId
left join #ttcbm x
on tp.tradeId = x.tradeId1
and tp1.tradeId = x.tradeId2
where 1 = 1
and x.tradeId1 is null
and x.tradeId2 is null
OPTION (MAXDOP 1);

I added MAXDOP 1 per a troubleshooting tip I read that said CXPACKET was caused by parallelism, and perhaps it did help drive down my waits a bit, but not like the improvement that happens when the query fixes itself, i.e., from 24 hours to  lest than 1 hr.

the #ttcbm table has a PK of tradeid1, tradeid2 and #tradep has a pk of (cmbId, qs_plyrid, tradeid) and both tables have record counts on the order of 100K to 500k.  #ttcbm used to be a subquery of the latter 'insert into #mergeT' query, but I separated it out when I read that separating out complicated queries can improve performance when parallelism is a problem.

There is a lot of misunderstanding about CXPACKET.  CXPACKET isn't the cause of your problems, it is a side effect.  What CXPACKET means when you see it is that that thread of a parallel query is waiting for another thread of that query to do something.  Just because you see a lot of CXPACKET waits doesn't mean there's a problem with the query, it means that there is a problem somewhere else.  When you see CXPACKET waits you need to look at the other threads of the SPID and see what other waits are besides CXPACKET.  That other wait is the problem.

To your specific issue, the reason that the run times are so crazy is probably because the SQL Server is generating a different plan on some days because the statistics are out of date (making the job run long).  Then you either manually update statistics (or via a job) or auto stats kicks in and then the plan gets better and the job runs quickly again.

Once you've solved the stats problem you can start looking at other reasons why the job is running slow.  The fact that you only have a single disk is not helping any for sure.


Database planning... avoiding parallel fields this is my first post here.

I'm a programmer and lately I've had an interest in databases.  I'm working on a side project and would like some tips as how to best construct my database.

Here's the scenario:
I am attempting to see how the '6 degrees of seperation' theory holds up on social media.
I will have a scripted social media account which will act in much the same way as a web crawler... It will visit a social media account and catalog every UserID and UserName into a database file, UserID acting as the primary key.  I would like to have a table (or collection of tables) which will keep track of which UserID's were linked to which account... i.e. UserID 12345 had links to/from UserID's 44444 and 55555 on their page.

The problem is, in my database classes I'm told it is bad practice to have parallel fields or columns that hold more than one attribute.  I would like to avoid this.

From what I described UserID 12345 would have either two entries in one column(44444 & 55555), or two parallel fields holding one userID each.  What is the best way to work around this?

One row in a relational DB table holds one fact only. That user 12345 is connected to user 44444 and to user 55555 is two facts. Consequently it should be stored as two separate rows.

Columns capture meaning from the real world. Depending on the table, there can be an implicit semantics between the columns. A good example is a departmental reporting hierarchy. Typically this would be modelled:

Employees
   EmployeeID
   Name
   HiredDate
   ManagerID Foreign Key references Employees.EmployeeID

Although each manager is also an employee (and will have a row in the table for her data), for a given row it does not make sense to swap the values in the EmployeeID column and the ManagerID column. The two are not symmetric and there is meaning in the columns.

For your example, however, if A is a friend of B then B is a friend of A. The pairing is completely symmetric. Modelling this in RDBMS can be problematic as it breaks the implicit asymmetry mentioned earlier. There are techniques to do this in RDBMS. One way is to store each pair twice i.e. 12345/44444 and 44444/12345. Another is to query the table twice and swap the meanings assigned to columns between queries.

This can be tricky to understand and is likely to be quite challenging as a first journey into relational database design.


Slow max, min query in Postgresql View I have a view and it's a left join between two tables (table_a has hundreds of millions of rows, table_b has ~2 millions). The query

SELECT MIN("id") AS "min_id",MAX("id") AS "max_id" FROM "the_view" LIMIT 1

is very slow on the view (return in 30~60 seconds), but runs fast on both table.

Both table are using id as primary key. They joins together on id as well. I have the same structure in mysql, and it runs well (return in ms).

Is there anything I should set in Postgres to make this work?

Here's the table schema:

create table table_a (
id int primary key,
name varchar(40)
);

create table table_b(
id int primary key,
age_group varchar(5)
);

create view the_view as
select a.id, a.name, b.age_group
from table_a as a
left join table_b as b using(id);

Any ideas where to look into? Thank you.

Here's the explain analyze on the slow query:

psql=# explain analyze
       SELECT MIN("id") AS "min_id",MAX("id") AS "max_id" FROM "the_view" LIMIT 1;
  Limit  (cost=3296039.61..3296039.62 rows=1 width=4) (actual time=265398.380..265398.381 rows=1 loops=1)
   ->  Aggregate  (cost=3296039.61..3296039.62 rows=1 width=4) (actual time=265398.374..265398.374 rows=1 loops=1)
    ->  Index Only Scan using table_a_pkey on table_a a  (cost=0.57..2763865.85 rows=106434752 width=4) (actual time=0.158..133179.832 rows=106434713 loops=1)
        Heap Fetches: 0
Total runtime: 265398.506 ms

Bound to query
If you can only change the view, not the query: this is 100 % equivalent using a correlated subquery instead of the LEFT JOIN:
CREATE VIEW the_view_new AS
SELECT a.id, a.name
    , (SELECT age_group FROM table_b WHERE id = a.id) AS age_group
FROM   table_a a;

Your query as is just reads top and bottom row from the index now, IOW blazingly fast. This is a workaround, gets more complicated with more columns and may exhibit weak spots with other queries.
Better query
Your query:
SELECT MIN("id") AS "min_id",MAX("id") AS "max_id" FROM "the_view" LIMIT 1;

Only needs table_a. In the view, table_b is joined with a LEFT JOIN. Obviously, the query planner does realize that table_b is not needed for the result of min() and max() after a LEFT JOIN (contrary to what I assumed at first). The query plan does not mention table_b.

It can also only return a single row, so LIMIT 1 is only complicating matters for the query planner further, to no effect. (Seems not to be the tipping point here.)

The obfuscation confuses the planner enough to read the whole 100 million rows from the index (rows=106434752), while it would only need to look up first and last row. That's a whole lot of pointless work.

This is simpler, cleaner and faster, while returning the same:
SELECT MIN(id) AS min_id, MAX(id) AS max_id FROM table_a;

As you can see in the EXPLAIN ANALYZE output of this SQL Fiddle with 100k rows in table_a:
Simple query
(actual time=0.010..0.010 rows=1 loops=1)
Your query:
(actual time=0.012..400.498 rows=100000 loops=1)
This looks like a weakness of the query planner in any case. We should repeat the test with pg 9.4 and possibly file a bug report ...
Bound to use the view
If you are bound to use the view (as commented), there is a workaround to convince the query planner (id must be NOT NULL - true in your case since PK):
SELECT (SELECT id AS min_id FROM the_view ORDER BY id ASC  LIMIT 1) AS min_id
     , (SELECT id AS max_id FROM the_view ORDER BY id DESC LIMIT 1) AS max_id;

Check the query plans in the fiddle: two times rows=1.
Aside: I suggest you take a look at the chapter Identifiers and Key Words in the manual.


How can I add a user and specify a password for a in IBM Data Studio? In Data Studio I can connect to a sever/database, go down to Users And Groups, right-click on Users and add a new one. But the program never asks me for a password. How can I specify it?
Also, no matter how many times I tried to "Save changes" they are never saved. I even tried to "generate change" and run it, but it always fails.
I am connecting as the only user that exists on the server: db2admin. If db2admin has no authority to manage users, then who has?

Eventually I found that DB2 UDB does not have its own repository for users, unlike other database servers. DB2 relies on the OS for user management. In Windows, for instance, after creating a UDB user, one has to go into Windows Control Panel/User Accounts and add that same user. Then UDB will allow it to log in with the password, supplied on the OS level. I presume that in Linux the procedure is similar: add a user in UDB, then run CLI commands to add a user and to change its password.


What will happen in the background, when we update a record in a table in Postgres? I have a table company like,

id  |  name  | age | address   | salary
----+--------+-----+-----------+--------
 1  | Ganesh |  32 | Chennai   |  20000
 2  | Allen  |  25 | Chennai   |  15000
 3  | Teddy  |  23 | Chennai   |  20000
 4  | Mark   |  25 | Chennai   |  65000
 5  | Paul   |  32 | Chennai   |  20000
 6  | Allen  |  25 | Chennai   |  15000
 7  | Teddy  |  23 | Chennai   |  20000
 8  | Paul   |  32 | Chennai   |  20000
 9  | Allen  |  25 | Chennai   |  15000
 10 | Teddy  |  23 | Chennai   |  20000

Now, what will happen in the background, when I update any one (or more)  record(s) in this table?

update company set address = 'California' where id = 5;

My real doubt is, whether it will delete the whole old record and insert a record newly with  given value or it just replace the given value in the specified column?

If the record is deleted for update, then the memory remain there?

In general, a new row version will be created by an update to support MVCC. This might not happen under certain conditions e.g. if the update does not change any indexed column.

The space  occupied by the old row version will be freed by the auto-vacuum process and re-used for new data once there are no more transactions that would see that version.


The most efficient way to join huge tables I have a table with 20M rows, and each row has 3 columns: time, id, and value. For each id and time, there is a value for the status. I want to know the values of the last and the next periods for a specific time and id, and have the following query to get the values:

update a1
set  a1.value_last = b1.value,
     a1.value_next = c1.value
from tab1 a1
left join tab1 b1
on a1.id = b1.id
and a1.period = b1.period + 1
left join tab1 c1
on a1.id = c1.id
and a1.period = c1.period - 1

It seems that the query takes forever and the log file increased by more than 10 GB. I'm wondering what's the most efficient way to write this query? I know using index will speed up the joining process, but how can I reduce the logging?

I'm using SQL Server 2016 on Win10 64bit.

update a1
set  a1.value_last = LAG(value, 1,0)  OVER (partition by id ORDER BY period)
  ,  a1.value_next = LEAD(value, 1,0) OVER (partition by id ORDER BY period)
from tab1 a1

index on id, period

or just use a view - that might surprise you

CREATE VIEW tab1LastNext
AS
select a1.id, a1.period, a1.value
     , LAG(value, 1,0)  OVER (partition by id ORDER BY period) as value_last
     , LEAD(value, 1,0) OVER (partition by id ORDER BY period) as value_next
from tab1 a1;

for logging
need to hope value is not null or this gets messy

select 1;
while @@rowcount > 0
begin
  update top (10000) a1
  set a1.value_last = LAG(value, 1,0) OVER (partition by id ORDER BY period)
  from tab1 a1
  where LAG(value, 1,0) OVER (partition by id ORDER BY period) is not null
    and LAG(value, 1,0) OVER (partition by id ORDER BY period) != a1.value_last
end
select 1;
while @@rowcount > 0
begin
  update top (10000) a1
  set a1.value_next = LEAD(value, 1,0) OVER (partition by id ORDER BY period)
  from tab1 a1
  where LEAD(value, 1,0) OVER (partition by id ORDER BY period) is not null
    and LEAD(value, 1,0) OVER (partition by id ORDER BY period) != a1.value_next
end


MySQL's "slow query" log: strange "Query_time" I need some help understanding what's going on with my "slow query" log in MySQL. I have set

 set global long_query_time=3

and yet this is what I see in the slow query log:

# Time: 120723  9:04:38
# User@Host: root[root] @  [111.111.111.205]
# Query_time: 0.017508  Lock_time: 0.000111 Rows_sent: 2884  Rows_examined: 9132
SET timestamp=1343027078;
SELECT [...]
# User@Host: root[root] @  [111.111.111.202]
# Query_time: 0.030282  Lock_time: 0.000102 Rows_sent: 0  Rows_examined: 12323
SET timestamp=1343027078;
SELECT [...]
# Time: 120723  9:04:41
# User@Host: root[root] @  [111.111.111.202]
# Query_time: 0.004416  Lock_time: 0.000104 Rows_sent: 18  Rows_examined: 6266
SET timestamp=1343027081;
SELECT [...]
# User@Host: root[root] @ hostname [111.111.111.199]
# Query_time: 0.029399  Lock_time: 0.000116 Rows_sent: 0  Rows_examined: 12323
SET timestamp=1343027088;
SELECT [...]
# Time: 120723  9:04:53
# User@Host: root[root] @  [111.111.111.201]
# Query_time: 0.004470  Lock_time: 0.000080 Rows_sent: 0  Rows_examined: 6248
SET timestamp=1343027093;
SELECT [...]
# Time: 120723  9:04:55
# User@Host: root[root] @  [111.111.111.198]
# Query_time: 0.042627  Lock_time: 0.000087 Rows_sent: 0  Rows_examined: 0
use [...];
SET timestamp=1343027095;
UPDATE [...]
# User@Host: root[root] @  [111.111.111.198]
# Query_time: 0.029115  Lock_time: 0.000124 Rows_sent: 0  Rows_examined: 0

where the time in Query_time is significantly lower than my prevously specified 3 seconds. Why's that? Where is the missing time?

MySQL 5.1.41, Ubuntu 10.04

Ah, I should have checked that first:

log_queries_not_using_indexes has been turned on, so a whole number of queries has been logged because they were not using indexes.

Apparently, a query "not using indexes" is not marked in any way in the slow query log, so one has to guess whether an entry has been logged because of an execution time exceeding the long_query_time threshold or because of indexes.


Reporting Services in SharePoint Integrated Mode with SSPI/Windows Authentication Architecture I have a confusion about the place of Reporting Services' installation in my current architecture. I have two servers, one where SharePoint is on, and another where my data resides. Both servers have SQL Server 2016 installed. I need to install Reporting Services in one of the places, I don't know where. Here are my requirements:

The users MUST be represented in the database. This means that if
a user, who has access to a report, does not have access to its
underlying tables, then the user must not be able to see the data.
This means, I'll be managing the security using the DB Roles, and
thus I need pass through authentication. I'll also be using
Row-Level Security feature, which again, mandates the reporting
services to tell the database what is the user that's querying the
data.
I need to use all of the features SharePoint has to offer (and so
does the Reporting Services). This means that I need to install
Reporting Services in Integrated mode.

My Questions:

1- where should the reporting services be installed Server 1 or Server 2 (where SharePoint is located).

2- More importantly: how could I get Reporting Services to communicate the users who are accessing the data. I guess how could I see pass through authentication, to ensure security is not managed by just who has access to the reports and who doesn't. Assume everyone has access to all reports, and DB has to be smart enough NOT to provide unauthorized users anything.

Reporting Services in SharePoint Integrated Mode  can only be installed on a SharePoint Server. That is, you can only have the Reporting Services on the same server because Report Server database is no longer utilized when you install in SHP Integrated mode. Instead, SharePoint databases are used, there are a number of sharepoint databases used by SHP and one of them will host your meta data and report definitions. This makes centrally managing reports possible. So there is only one answer to your question, Server 2 or Sharepoint server.


DB2 Express C 10.5.4 using up all memory for package cache The testing environment (Win64) uses a express c - which does pretty well. Now when testing a java batch it runs out of private memory exhausting all available memory (14.8 GB) to package cache. As a first action I put on STMM because I thought this would help. Now, because it did not work out, I disabled the STMM and still something is eating up the memory. The production environment is on DB2 10.1 WSE with 64GB available. I am afraid that one day our applications will run into the same situation on this machine.

In db2diag.log everything started with sqlcode -101 "statement too long".
The output of db2mtrk is:

Speicher wird überwacht am 2015/10/30 um 06:59:18.

Speicher für Instanz

   other       fcmbp       monh
   57,0M       832,0K      1,8M

Speicher für Datenbank: EASY

   utilh       pckcacheh   other       catcacheh   bph (5)     bph (4)
   64,0K       14,8G       192,0K      2,7M        64,9M       64,9M

   bph (3)     bph (1)     bph (S32K)  bph (S16K)  bph (S8K)   bph (S4K)
   82,1M       80,7M       1,8M        1,5M        1,4M        1,3M

   shsorth     lockh       dbh         apph (1149) apph (1148) apph (1147)
   2,3M        46,6M       107,4M      128,0K      128,0K      128,0K

   apph (1146) apph (1145) apph (1144) apph (1143) apph (1142) apph (1141)
   128,0K      128,0K      128,0K      128,0K      128,0K      128,0K

My setting from  get db cfg:

 Speicher mit automatischer Leistungsoptimierung                       (SELF_TUNING_MEM)    = OFF
 Größe des gemeinsamen Datenbankspeichers (4 KB)                       (DATABASE_MEMORY)    = AUTOMATIC(180832)
 Schwellenwert für Datenbankspeicher                                   (DB_MEM_THRESH)      = 100
 Max. Speicher für Sperrenliste (4 KB)                                 (LOCKLIST)           = AUTOMATIC(11200)
 Anzahl der Sperrenlisten pro Anwend. (in )                           (MAXLOCKS)           = AUTOMATIC(97)
 Größe des Paketcache  (4 KB)                                          (PCKCACHESZ)         = 16384
 Sortierspeicherschwelle für gemeinsame Sortierungen (4 KB)            (SHEAPTHRES_SHR)     = AUTOMATIC(5025)
 Zwischenspeicher für Sortierlisten (4 KB)                             (SORTHEAP)           = AUTOMATIC(1005)

If someone has a clue, an idea or hint for me - I would be very glad to hear from you.

Many applications or persistence frameworks generate new statements for each interaction with the database. The DB2 statement concentrator can help save system resources by mapping similar statements to one and reusing access plans and thereby share resources. Usually this help to cut query compilation time and reduce required system resources.

The package cache size can either be automatically set (self-tuning memory manager) or manually specified. In your case it seemed manually setting made more sense as the cache was hoping to reuse statements. If it is set to a fixed value, cache entries would need to be removed for new statements. However, the rest of your system memory could have been assigned to a bigger bufferpool.


AWS DMS: 2 million CDCIncomingChanges resolved without target throughput We're attempting to use AWS DMS to replicate MS-SQL to a customer's on-prem DB, and running into a confusing situation.  At 13:00 UTC there are over 2 million CDCIncomingChanges, with CDCTargetLatency of 60k.  Somehow, by 14:50 UTC these changes are processed and latency is resolved down to zero while CDCThroughputRowsTarget stays at zero.  It would seem that somehow there are ~2 million incoming changes from the source DB, which are essentially stuck for 60,000 seconds (16 hours), yet they all amount to no rows being changed on the target.

We've increased the capacity of our replication instance and have tried increasing various constraints in our Task Settings.  I've read all the AWS DMS docs including their three-part "Debugging DMS Migrations" series and I've looked through the logs with maximum debug level, yet I still don't get what is happening here.

In our task settings, we have BatchApplyEnabled:true and BatchApplyPreserveTransaction:true, so my only idea is that this could be caused by a long-running transaction.  In general though, I don't know how to go about debugging this, or determining the root cause.  Any help is appreciated.

Task Status Dashboard:

Replication Instance Dashboard:

It would seem that every row in a DB was effectively being deleted and recreated without any changes, and that batchApply was enabled.  Batch apply would effectively cancel out large numbers of changes, which is why the number of CDCIncomingChanges was reduced without throughput.


Attempting to tune a Sharepoint site using SQL Server Waits and Queues I'm pretty new to this performance tuning with Waits and Queues thing - fascinating, but also not always all that intuitive...

Right now, a customer of mine has a SQL Server 2008 64-bit Enterprise Edition, currently with 16 GB of RAM assigned to it, running on a physical server with 64 GB of RAM (there are other and changing SQL Server instances on the same machine, too).

This app he's running is a Sharepoint 2010 solution, which performs - well - OK for a Sharepoint site - most of the time. Except for searches, which are terribly slow.

Now from a SQL Server perspective, I've been watching the wait statistics for a few days, and the top three wait types are:

1) CXPACKET at around 49%
2) SOS_SCHEDULER_YIELD at around 10.5%
3) OLEDB at around 9.5%

This seems to be quite consistent - no big changes over time.

PAGEIOLATCH_SH comes in as seventh - 2.5% of total wait times, average resource wait time is 14ms.
ASYNC_IO_COMPLETION comes in eight - less than 2% of total wait times, but high average resource wait time of 38 seconds (yes, seconds - not milliseconds!)

The signal wait time is extremely low, so that doesn't seem to indicate CPU pressure. So what's causing this pattern - and what can we do to (a) find more relevant info, or (b) find a solution to speed things up?

Any thoughts? Insights? Ideas? I'm pretty much open to anything - what we cannot change is the basic architecture (separate SQL Server instances, being moved between physical servers - and a Unix-based SAN which cannot guarantee separation of data and log onto separate, physical disks)

Again: this is a SharePoint site - so as far as I know, trying to solve things with indexing or restructuring the database is somewhere between hard and impossible.

As this is SharePoint your hands are tied.  You can't create indexes or statistics without loosing support from Microsoft.

CXPACKET just means that one thread of a parallized query is waiting for another thread to finish waiting for something else.  SOS_SCHEDULER_YIELD can mean a few things, typically it means that you need to fix your indexes are you have queries that aren't tuned correctly so the SQL Server is pausing those queries (yielding them) so that it can work on other stuff (as this is SharePoint you can't really do anything about this).  OLEDB means that the SQL Server is waiting on the OLEDB driver so probably there are linked server queries or something along those lines that are calling out to another database server.  There's nothing you can do about that either.

From what it sounds like there's about nothing you can do without throwing more hardware at the problem.  Use Profiler, Server Side Tracing or Extended Events and find the queries which are taking to long to run and check their execution plans and see how bad that are.  Odds are those search queries are doing large scan operations.

How big are the content databases?  Microsoft recommends keeping the content databases under 100 Gigs so that scanning is kept to a minimum.


Return master and master details record(s) in a single row in postgresql I am trying to formulate a PostgreSQL query where I wanted to return all the payment table data in an array and customer data as a header in a single row.
I am writing the following query to get data in a single row, but somehow I am getting several rows with the same repetitive data. I want a single data row to be returned for the requested id. I don't want to use the "limit=1" keyword to return a single row.

WITH t as (select row_to_json(rr)::jsonb as record
from (
       select (
                select row_to_json(row)
                from (
                       select
                             c.customer_id,
                             c.first_name,
                             c.last_name,
                             c.email,
                             count(*) total,
                             sum(p2.amount) total_amount
                       from payment p2
                       where p2.customer_id = c.customer_id
                     ) row
              ) as header,
              (select array_to_json(array_agg(row_to_json(row)))
                from (
                       select
                             amount,
                             payment_date
                       from payment p1
                       where p1.customer_id = c.customer_id
                     ) row
              ) as details
       from customer c
       join payment p on p.customer_id = c.customer_id
     ) rr)

    SELECT record FROM t
    where record->'header'->>'customer_id'= '2'

It's really hard to tell what exactly you want, but maybe something like this:

select jsonb_build_object('customer', to_jsonb(c),
                          'total_amount', pd.total_amount,
                          'payment_details', pd.payments)
from customer c
  join lateral (
     select jsonb_agg(jsonb_build_object('amount', amount, 'date', payment_date)) as payments,
            sum(amount) as total_amount
     from payment p
     where p.customer_id = c.customer_id
  ) pd on true


Query Index question I am trying to figure out how I can index this query below. The table is 155 columns. It has a clustered index on the column which is not in the query. The query is dynamically created from out of the box application and no way I cant tune it except adding index. I could not find an index that will optimize the query. The table has 511919 rows.Now, it is clustered index scan OR non clustered index Scan (with covered index)

SQL Server version : 2016 SP2, We have 22 core and 24 gbs on the server. I can post a query plan.

    Declare
        @P0 VARCHAR(8000)
        ,@P1 VARCHAR(8000)
        ,@P2 VARCHAR(8000)
        ,@P3 VARCHAR(8000)
        ,@P4 INT
        ,@P5 VARCHAR(8000)
        ,@P6 VARCHAR(8000)
        ,@P7 VARCHAR(8000)
        ,@P8 INT
        ,@P9 VARCHAR(8000)

Set @P0 = 'Cancelled'
Set @P1 ='20190321'
Set @P2 ='20190321'
Set @P3 ='20190321'
Set @P5 ='20190321'
Set @P6 ='20190321'
Set @P7 ='20190321'

Set @P4 =1
Set @P8 =0

Set @P9='Service'

SELECT  Col1,
    Col2,
    Col3,
    Col4,
    Col5,
    Col6,
    Col7,
    Col8,
    Col9,
    Col10,
    Col11,
    Col12,
    Col13,
    Col14,
    Col15,
    Col16,
    Col17,
    Col18,
    Col19,
    Col20,
    Col21,
    Col22,
    Col23,
    Col24,
    Col25,
    Col26,
    Col27,
    Col28,
    Col29,
    Col30,
    Col31,
    Col32,
    Col33,
    Col34,
    Col35,
    Col36,
    Col37
FROM Table1
WHERE (
        Col18 IS NOT NULL
        AND Col75 <> @P0
        AND DATEDIFF(dd, Col146, CAST(SUBSTRING(@P1, 1, 4) + '-' + SUBSTRING(@P2, 5, 2) + '-' + SUBSTRING(@P3, 7, 2) + 'T00:00:00.000' AS DATETIME)) <= @P4
        AND DATEDIFF(dd, Col146, CAST(SUBSTRING(@P5, 1, 4) + '-' + SUBSTRING(@P6, 5, 2) + '-' + SUBSTRING(@P7, 7, 2) + 'T00:00:00.000' AS DATETIME)) >= @P8
        )
    AND Col39 = @P9
ORDER BY 18 ASC
    ,31 DESC
    ,30 ASC

This is a covered index. It is used as non clustered index Scan.

/****** Object:  Index [NC_OBJ_DID_SQ_CoveredIndex2]    Script Date: 3/21/2019 1:30:31 PM ******/
CREATE NONCLUSTERED INDEX NC1 ON Table1
(
    TableCol39  ASC,
    TableCol6 ASC,
    TableCol75  ASC,
    TableCol18  ASC
)
INCLUDE (   TableCol1,
    TableCol2,
    TableCol3,
    TableCol4,
    TableCol5,
    TableCol6,
    TableCol7,
    TableCol8,
    TableCol9,
    TableCol10,
    TableCol11,
    TableCol12,
    TableCol13,
    TableCol14,
    TableCol15,
    TableCol17,
    TableCol19,
    TableCol20,
    TableCol21,
    TableCol22,
    TableCol23,
    TableCol24,
    TableCol25,
    TableCol26,
    TableCol27,
    TableCol28,
    TableCol29,
    TableCol30,
    TableCol31,
    TableCol32,
    TableCol33,
    TableCol34,
    TableCol35,
    TableCol36,
    TableCol37,
    TablCol146 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Thanks a bunch!

If you are unable to adapt the queries, some of these columns in the where clause will remain not sargable.

To make your query sargable, You would need to try and remove the DATEADD() functions in the where clause, around Col146.
This makes the (possible) index on Col146 not sargable.

Consider this as a possible change:

AND Col146 <= DATEADD(dd,-@P4,CAST(SUBSTRING(@P1, 1, 4) + '-' + SUBSTRING(@P2, 5, 2) + '-' + SUBSTRING(@P3, 7, 2) + 'T00:00:00.000' AS DATETIME))
AND Col146 >= DATEADD(dd,-@P8,CAST(SUBSTRING(@P1, 1, 4) + '-' + SUBSTRING(@P2, 5, 2) + '-' + SUBSTRING(@P3, 7, 2) + 'T00:00:00.000' AS DATETIME))

Making sure that all functions are applied to the variables. You should not take my word for it and double check this.

Index changes.

If you could make your query sargable & the sort is not expensive.

CREATE INDEX IX_Table1_Col39_Col146_Col75
ON Table1(Col39,Col146,Col75)
INCLUDE(Col18
,Col31
,Col30 )
WHERE Col18  IS NOT NULL

However, If a lot of rows are returned after the search predicates, you might have to index your SORT (Order by) As well, since that one will use a lot of CPU depending on the  amount of rows passing through. We would have to see an actual query plan to determine this.

We are not able to change the query, no matter how much we beg and plead for mercy

You could try and index that what is sargable and try to remove the SORT operator.

CREATE INDEX IX_Table1_Col39_Col75_Col18_Col31_Col30
ON Table1(Col39 ASC ,Col75 ASC ,Col18 ASC,Col31 DESC, Col30 ASC)
INCLUDE(  Col146 )
WHERE Col18  IS NOT NULL;

Note that the <> operator is not that sargable, you could probably try and change it to;

CREATE INDEX IX_Table1_Col39_Col146_Col75
ON Table1(Col39 ASC ,Col18 ASC,Col31 DESC, Col30 ASC)
INCLUDE(  Col146,Col75 )
WHERE Col18  IS NOT NULL;

What about the other columns?

The include list is something I will leave to you, depending on the expected resultset's, the key lookup could be something to live with or not.

The optimizer might not even use the index if it thinks that the key lookup would be too expensive.

Testing the theories

To see if what I am saying holds any truth, I conducted a test, ofcourse, YMMV. It is simply to see if it is even possible to remove  the SORT operator and that we are able to seek on certain filters.

DDL + DML Below.

Index #1

CREATE INDEX IX_Table1_Col39_Col75_Col18_Col31_Col30
ON Table1(Col39 ASC ,Col75 ASC ,Col18 ASC,Col31 DESC, Col30 ASC)
INCLUDE(  Col146 )
WHERE Col18  IS NOT NULL;

While we are able to seek on col75 and col39, the sort + residual predicate remains:

And that is not what was expected unfortunately.

There is light at the end of the Tunnel (Index #2)

CREATE INDEX IX_Table1_Col39_Col18_Col31_Col30
ON Table1(Col39 ASC  ,Col18 ASC,Col31 DESC, Col30 ASC)
INCLUDE(  Col146,Col75)
WHERE Col18  IS NOT NULL;

I can rerun my modified test query (without all the selected columns)

         Declare
        @P0 VARCHAR(8000)
        ,@P1 VARCHAR(8000)
        ,@P2 VARCHAR(8000)
        ,@P3 VARCHAR(8000)
        ,@P4 INT
        ,@P5 VARCHAR(8000)
        ,@P6 VARCHAR(8000)
        ,@P7 VARCHAR(8000)
        ,@P8 INT
        ,@P9 VARCHAR(8000)

Set @P0 = '1blabla'
Set @P1 ='20190315'
Set @P2 ='20190315'
Set @P3 ='20190315'
Set @P5 ='20190315'
Set @P6 ='20190315'
Set @P7 ='20190315'

SET @P9 = '0bla'
Set @P4 =1
Set @P8 =1
SELECT *
FROM table1 Where
(
 Col18 IS NOT NULL
        AND Col75 <> @P0
           AND DATEDIFF(dd, Col146, CAST(SUBSTRING(@P1, 1, 4) + '-' + SUBSTRING(@P2, 5, 2) + '-' + SUBSTRING(@P3, 7, 2) + 'T00:00:00.000' AS DATETIME)) <= @P4
        AND DATEDIFF(dd, Col146, CAST(SUBSTRING(@P5, 1, 4) + '-' + SUBSTRING(@P6, 5, 2) + '-' + SUBSTRING(@P7, 7, 2) + 'T00:00:00.000' AS DATETIME)) >= @P8

     )
    AND Col39 = @P9
ORDER BY Col18 ASC
    ,Col31 DESC
    ,Col30 ASC;

To get a pretty seek with no sort or other operators (And a pesky residual predicate.)

Your result could look more like this:

Conclusion

Based on these tests, which may very well be 100% different for you, this is the index that I would initially go for:

CREATE INDEX IX_Table1_Col39_Col18_Col31_Col30
ON Table1(Col39 ASC  ,Col18 ASC,Col31 DESC, Col30 ASC)
INCLUDE(  Col146,Col75)
WHERE Col18  IS NOT NULL;

DDL + DML Used in this example

        SET NOCOUNT ON;
        DECLARE @i int = 0
        WHILE @i < 100
        BEGIN
        INSERT INTO Table1(Col39,   Col75,Col146,Col18,Col31,Col30)
        VALUES(cast(@i as varchar(3))+'bla',
               cast(@i as varchar(3))+'blabla',
               DATEADD(DD,-@i,GETDATE()),
               cast(@i as varchar(3))+'blablabla',
               cast(@i as varchar(3))+'blablablabla',
               cast(@i as varchar(3))+'blablablablabla');
        SET @i += 1;
        END
        SET NOCOUNT OFF ;
        INSERT INTO Table1
        SELECT * From Table1;
        GO 7
        Beginning execution loop

   (6500 rows)

    GO


Performance impact of NOT using a foreign key (many dedicated 1:many key-key tables vs non-fk generic key-key table) We're using a lot of association tables to manage 1:many relationships among a variety of different objects in our system.

To illustrate the question, two examples would be:

users, events, ass_users_events.  The ass_users_events would contain only the User_ID and Event_ID columns, both with foreign key relationships.
projects, tasks, ass_projects_tasks.  The ass_projects_tasks would contain only the Project_ID and Task_ID columns, both with foreign key relationships.

NB1: Every object table actually uses a combination of an Auto-Incrementing Integer Primary Key, plus a UUID column with a unique index which is the actual record ID. For the purpose of this question, we're using only UUID's so there is no chance of collision.

NB2: The reason we use this format rather than a straight Foreign Key column/index is that the reality is much more complex than this example, there are many different joins to many different tables and we don't want the ORM doing a lot of unnecessary work every time a record is loaded.

The problem is we're starting to create these associational tables for pretty much every new object type in the system for many of the other existing objects in the system and in the long run this doesn't seem sustainable, we'll end up with hundreds of association types.

A potential solution we're considering is getting rid of ALL of the current associational tables and instead creating one table with the following structure: obj_1_id, obj_1_type, obj_2_id, obj_2_type.  Every column would be indexed, likely as composite indexes (i.e. INDEX object_1 (obj_1_type,obj_1_id) and INDEX object_2 (obj_2_type,obj_2_id)).

The examples above in the ass tables would become: abc,user,123,event, and def,project,456,task.

This solution gives us the flexibility to build as many relationship types as we want between different objects, and there's ample indexing on the ass table to remain performant.  My question is is there a downside to using indexing only during joins, vs having defined foreign key relationships in smaller tables, but potentially hundreds of them?

EDIT:  I think there are some misconceptions below so this might clear up a couple things:

I use polymorphic object structures, but every object is stored in it's own table, i.e. user, product, category, event, etc.
As far as the proposed ass table, it will only have 4 functional fields, with very straightforward datatypes (plus one ai_col as primary).  The datatypes would be varchar(10) for the type cols and CHAR(32) / BINARY(16) for the id's.  SELECT's will only ever have one join, to the ass table, i.e.

SELECT event.*  FROM event INNER JOIN ass ON ass.obj_1_type = 'event' AND ass.Object_ID_1 = event.Event_ID AND ass.obj_2_type='location'

there's no reason I would ever ask for two of these objects at once, I would only be filtering results from one table by existence of ass links.

UUID's are the real Unique ID's here, but every table uses ai_col which is an INNODB construct for increasing the performance of their clustered index.
This is a read heavy environment, I'm not near as concerned with insert/delete performance.
We use handlersocket for simple read AND write queries removing the SQL overhead.
The integrity of this table is acceptable at Eventually Consistent in this specific use case.

EDIT 2: To make this crystal clear, here's the why...  if I have 200 objects each in their own tables, that I want to be able to link in all possible ways, I would either have to have 199 foreign key relationships in each object table, or I would have to create 40,000 tables with all the possible combinations and then all the business logic to go with that. Using a the method we're considering, there's one table and I have RI taken care of at the application level, which I indicated is ok at eventually consistent.

It looks to me that you are considering the EAV (Entity Attribute Value) model, i.e. wedging every object into a single table, rather than having different tables for different objects. EAV is generally considered an antipattern.

It can be known as an Open and/or flexible Schema. It is also known (sarcastically) as the OTLT (One True Lookup Table), (even more sarcastically) as MUCK (Massively Unified Code Key - Joe Celko) and perhaps best of all (I only just found this) as DEMONS (Diabolically Enticing Method Of iNformation Storage - adapted from Mike Smithers).

It is interesting to note that Celko (who was a member of the ANSI/ISO SQL committee which determines/ed the SQL standard) said (see reference) about the programmer who had written such a system that the questioner should "Find the moron that did this to you and kill him".

See the Smithers' post (and and Celko's writings here and here) about the difficulties of writing queries against EAV schemas. It's no coincidence that Celko titles his first article "Avoiding the EAV of Destruction". Take a look here for an absolutely hilarious visual analogy of the EAV system from Bill Karwin - a highly respected SQL guru and author of a book "SQL Antipatterns".

You mention "having defined foreign key relationships in smaller tables, but potentially hundreds of them". Modern RDBMSs can cope with hundreds of tables and FK relationships between them and unless you have VERY strong reasons (with proven testing behind your argument), then stick with traditional relational principles.

To directly answer your question, in all likelihood, the complexity of the queries that you will need to use to obtain data results from an EAV system will have FAR more of a negative performance impact than a system with many tables with clear, properly defined FK relationships using INT(11) foreign keys or even UUIDs.

Another point about performance - use of an EAV system will force you to use inappropriate datatypes and this will seriously interfere with your RDBMSs query optimiser's ability to (err...) optimise the queries. Under these EAV systems, many values are (again inappropriately) stored as strings and converted on-the-fly, which is both time-consuming and, again, confusing for the optimiser - not recommended.

From my accepted answer here and for completeness and honesty, just one further remark. There is only one significant system out there that makes use of the EAV model - and that's Magento (1, 2). Its principal niche is the fashion industry where the EAV model may be suitable for sparse tables (fashion items tend to be available in a myriad of colours, syles, sizes...). It is popular (1, 2), but then so is MySQL which is inferior in many ways to PostgreSQL, Firebird and (multi-user capability aside) SQLite.

I also noticed this in the comments

  but I never rely entirely on the DB's RI, I generally do it at the
  application layer falling back on RI as a backup. I've considered
  using a maint task that runs occasionally to remove orphaned records
  to shore that up as well.

This is nothing short of madness.

The RI (Referential Integrity) capabilities of MySQL (and other RDBMSs) have been tested by literally MILLIONS of users. If you set up a proper PK-FK relationship between two of your tables, then the chances of this failing are virtually NIL! You'd be far better off using your time and effort to focus on other areas of your application than ensuring proper RI - unless you've committed the unpardonable sin of implementing RI in the app rather than using the capabilities of the RDBMS. See Jonathan Lewis' disaster no.2 in my answer here. Lewis wrote this book, so he knows a thing or two about databases.


T-SQL query to find backup folder for specific maintenance plan I want to check check if backup plans are set up correctly and need to query different servers, mostly SQL Server 2008, but also 2012.

Is there any way to query the maintenance plans for databases, backupfolder, starttime and other information?

So far I have only have this:

select name, subplan_name from msdb.dbo.sysmaintplan_subplans sp join
msdb.dbo.sysmaintplan_plans p on p.id = sp.plan_id

Mostly this is regarding new servers so backup have not had a chance to run yet, rendering logs useless.

You can use the available views in msdb to find the information regarding backups :

dbo.backupset:  provides information concerning the most-granular details of the backup process
dbo.backupmediafamily:  provides metadata for the physical backup files as they relate to backup sets.
dbo.backupfile: this system view provides the most-granular information for the physical backup files

You can use below T-SQL code to list down all required info:

    SELECT
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
    msdb.dbo.backupset.database_name,
    msdb.dbo.backupset.backup_start_date,
    msdb.dbo.backupset.backup_finish_date,
    msdb.dbo.backupset.expiration_date,
    CASE msdb..backupset.type
    WHEN 'D' THEN 'Database'
    WHEN 'L' THEN 'Log'
    END AS backup_type,
    msdb.dbo.backupset.backup_size,
    msdb.dbo.backupmediafamily.logical_device_name,
    msdb.dbo.backupmediafamily.physical_device_name,
    msdb.dbo.backupset.name AS backupset_name,
    msdb.dbo.backupset.description
    FROM   msdb.dbo.backupmediafamily
    INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id =    msdb.dbo.backupset.media_set_id
    WHERE  (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1)
    ORDER BY
    msdb.dbo.backupset.database_name,
    msdb.dbo.backupset.backup_finish_date


How can I output a new line with `FORMATMESSAGE` in transact sql? The following code stats that, how many users that has created at least one post on a specific SE site.

DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(DISTINCT OwnerUserId)
FROM Posts where OwnerUserId is not null)
PRINT FORMATMESSAGE('%s has \r\n %d users', DB_NAME(), @RowCount+1)

which basically works though, the FORMATMESSAGE part does not work as expected.

Usually, \n (in lots of systems and languages) and \r\n (mainly in MS systems) represent new line. I tried both, and even /r/n, none of them worked.

How can I output a new line with FORMATMESSAGE in transact sql?

The problem is the interpretation of \r\n as carrriage return and line feed. SQL Server does interpret \r\n like this in some places, but the FORMATMESSAGE function is not one of them, so instead you need to use CHAR(13) and CHAR(10) which represents the the ASCII codes for Carriage Return (13) and Line Feed (10):

DECLARE @RowCount INT
SET @RowCount = (SELECT 10)
PRINT FORMATMESSAGE('%s has ' + CHAR(13) + CHAR(10) + ' %d users', DB_NAME(), @RowCount+1)

Or use a variable:

DECLARE @RowCount INT,
    @NewLine CHAR(2) = CHAR(13) + CHAR(10)
SET @RowCount = (SELECT 10)
PRINT FORMATMESSAGE('%s has ' + @NewLine + ' %d users', DB_NAME(), @RowCount+1)
-- or
DECLARE @RowCount INT,
    @NewLine CHAR(2) = CHAR(13) + CHAR(10)
SET @RowCount = (SELECT 10)
PRINT FORMATMESSAGE('%s has %s %d users', DB_NAME(), @NewLine, @RowCount+1)


Joining two tables on multiple criteria I have two tables products and positions.

products:

id BIGINT PRIMARY KEY,
product VARCHAR(100),
criterium1 VARCHAR(100),
criterium2 VARCHAR(100)

positions:

id BIGINT PRIMARY KEY,
position VARCHAR(100),
criterium1 VARCHAR(100),
criterium2 VARCHAR(100)

Each table has criterium1 and criterium2.  I would like to have a result set containing joins between positions and products.  For each position I would like the corresponding product that matches either on criterium1 or criterium2; when criterium1 cannot be found or is NULL, it needs to match on criterium2.

What would the SQL statement look like?

So far I have tried:

SELECT * FROM positions pos
INNER JOIN products pro
ON COALESCE(pos.criterium1, pos.criterium2,'')
    = COALESCE( pro.criterium1, pro.criterium2,'')

However, this does not match pro.criterium1 to pos.criterium1 or pro.criterium2 to pos.criterium2.

Addition:
Sorry my requirements are a little murky. Let me try to complete paint the picture.

We are using SQL Server 2005.

products:

id   product   criterium1   criterium2
 1    pro1       AAA1         910
 2    pro2       106          BB2
 3    pro3       AB1          XXY

positions:

id  position  criterium1  criterium2
 1    pos1       NULL         910
 2    pos2       106          CCC
 3    pos3       XXX          BB2
 4    pos4       AA1          XXY
 5    pos5       NULL         123

What I am looking for is table joining using conditional matching, where I would like to match positions to products.  First I want to match on criterium1.  If I cannot find a match on criterium1 or the position criterium1 is NULL, I need to match on criterium2.

In the above example the expected result set would be:

resultset
positionId  productId
 1            1 (no match on criterium1 since NULL, match on criterium2 )
 2            2 (match on criterium1)
 3            2 (no match on criterium1, match on criterium2 )
 4            1 (match on criterium1, criterium2 match is disregarded)

I only want to match positions.criterium1 with products.criterium1 and positions.criterium2 with products.criterium2.

Assuming pro1, criterium1 should be AA1, this produces the results you are looking for:

SELECT pos.id, pro.id, pos.criterium1, pos.criterium2
   , pro.criterium1, pro.criterium2
FROM Positions pos
JOIN Products pro ON pos.criterium1 = pro.criterium1
   OR (pos.criterium2 = pro.criterium2
   AND pos.id NOT IN (
      SELECT posx.id FROM Positions posx
      JOIN Products prox ON posx.criterium1 = prox.criterium1
   ))
ORDER BY 1;

Test query (only tested in Oracle):

WITH Products AS (
   SELECT 1 id, 'AA1' criterium1, '910' criterium2 FROM dual UNION ALL
   SELECT 2 id, '106'  criterium1, 'BB2' criterium2 FROM dual UNION ALL
   SELECT 3 id, 'AB1'  criterium1, 'XXY' criterium2 FROM dual
   ),
   Positions AS (
   SELECT 1 id, NULL  criterium1, '910' criterium2 FROM dual UNION ALL
   SELECT 2 id, '106' criterium1, 'CCC' criterium2 FROM dual UNION ALL
   SELECT 3 id, 'XXX' criterium1, 'BB2' criterium2 FROM dual UNION ALL
   SELECT 4 id, 'AA1' criterium1, 'XXY' criterium2 FROM dual UNION ALL
   SELECT 5 id, NULL  criterium1, '123' criterium2 FROM dual
   )
SELECT pos.id, pro.id, pos.criterium1, pos.criterium2
   , pro.criterium1, pro.criterium2
FROM Positions pos
JOIN Products pro ON pos.criterium1 = pro.criterium1
   OR (pos.criterium2 = pro.criterium2
   AND pos.id NOT IN (
      SELECT posx.id FROM Positions posx
      JOIN Products prox ON posx.criterium1 = prox.criterium1
   ))
ORDER BY 1;


Create new database with large initial size I want to create a new database with a 200GB data file (.mdf) and 50GB log file (.ldf).

But it is very slow (it's about 15 minutes and still hasn't created). and it is very time consuming. Is that normal? if yes what does it do that takes time? can I enhance its creating speed?

I am using SQL Server 2008 R2, Windows Server 2008R2, 16GB RAM (which I limited that to 12GB in ssms) and Core i7 Cpu

Unless you're using Instant File Initialization, SQL Server must zero out all sectors for the files.  This is a very time consuming process, especially for 250 GB (data+log).  In order to speed things up, enable this feature and then SQL Server will not zero out the space for the data files.

Please note, SQL Server will always zero out the log file, so you will still be limited by that. Another reference for log file initialization


Merging tables, but ignoring duplicate columns I have two tables I want to merge:

city1(id, city, state, zip, lat, lon, county)

city2(id, city, state)

The tables contain overlapping city and state, but not primary keys. I can't figure out how to do this. UNION won't work, nor INSERT IGNORE due to the different column numbers and primary keys. So, I guess that leaves joins(left?), but I can't figure out the syntax to get it it excluding duplicates.

A NOTE

UNION will still display duplicate city/state combinations. From what I've read, UNION does prevent duplicates if the tables have different sizes.

Table Create Statements

Column and table names have changed

CREATE TABLE `cities` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `state_code` char(2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_state_code` (`state_code`)
) ENGINE=InnoDB AUTO_INCREMENT=29739 DEFAULT CHARSET=latin1

CREATE TABLE `cities_extended` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `state_code` char(2) NOT NULL,
  `zip` int(5) unsigned zerofill NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `county` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=41756 DEFAULT CHARSET=latin1

Proposed Solution #1

ALTER TABLE cities          ADD UNIQUE INDEX idx_city_state (city,state_code);
ALTER TABLE cities_extended ADD UNIQUE INDEX idx_city_state (city,state_code);
CREATE TABLE cities_new
    SELECT A.city,A.state_code
    FROM cities A LEFT JOIN cities_extended B USING (city,state_code)
    WHERE B.city IS NULL
;
INSERT INTO cities_extended (city,state_code)
SELECT city,state_code FROM cities_new;
DROP TABLE cities_new;

Explanation for Proposed Solution #1

Create Unique Indexes on (city,state_code) for both tables

ALTER TABLE cities          ADD UNIQUE INDEX idx_city_state (city,state_code);
ALTER TABLE cities_extended ADD UNIQUE INDEX idx_city_state (city,state_code);

Create Temp Table with rows from cities that's not in cities_extended

CREATE TABLE cities_new
    SELECT A.city,A.state_code
    FROM cities A LEFT JOIN cities_extended B USING (city,state_code)
    WHERE B.city IS NULL
;

Load the Temp Table into cities

INSERT INTO cities_extended (city,state_code)
SELECT city,state_code FROM cities_new;

Discard the Temp Table

DROP TABLE cities_new;

Proposed Solution #2

ALTER TABLE cities_extended ADD UNIQUE INDEX idx_city_state (city,state_code);
INSERT IGNORE INTO cities_extended
(city,state_code,zip,latitude,longitude,county)
SELECT city,state_code,0,0.0,0.0,'' FROM cities;

Explanation for Proposed Solution #2

INSERT IGNORE only works with unique indexes and primary keys. In your case, a unique index on city,state_code was needed for cities_extended.

After making such an index, you can insert all columns except id.

You could also include id like this

ALTER TABLE cities_extended ADD UNIQUE INDEX idx_city_state (city,state_code);
INSERT IGNORE INTO cities_extended
(id,city,state_code,zip,latitude,longitude,county)
SELECT NULL,city,state_code,0,0.0,0.0,'' FROM cities;

GIVE IT A TRY !!!


Setting max server memory in SQL Server 2014 I have a SQL Server 2014 Standard Edition server running Windows 2012 R2, with 128GB memory.

I'm attempting to configure SQL Server to use max memory of 96GB, but it appears to not be working.

It is defaulting to 128 GB.  On the first attempt I did not catch it defaulting to 128 which resulted in  errors 233, 33086 and 17300 and "Wait on the database engine recovery handle failed".

I've since uninstalled and reinstalled SQL Server, however I still cannot set max memory to less than 128...

I am using a service account created for running the database engine. No other features are installed.

The exact statement I'm using is:

EXEC sys.sp_configure N'max server memory (MB)', N'96' GO RECONFIGURE WITH OVERRIDE GO

Right after I added the statement to my question I saw my mistake.

Aye aye. You are confusing megabytes and gigabytes (MB is even in the configuration name!).

EXEC sys.sp_configure N'max server memory (MB)', N'96' --<-- this is 96 MB
GO

EXEC sys.sp_configure N'max server memory (MB)', N'98304' --<-- this is ~96 GB
GO


Oracle 11g - Create Database fails? I've just installed Oracle 11g and linked it to SQL Developer.

I want to create a database for my django install.

CREATE database 'django';

Error starting at line 1 in command:
create database 'django'
Error at Command Line:1 Column:0
Error report:
SQL Error: ORA-01501: CREATE DATABASE failed
ORA-01100: database already mounted
01501. 00000 -  "CREATE DATABASE failed"
*Cause:    An error occurred during create database
*Action:   See accompanying errors.

(I have also tried without the '')

Why isn't this working?

Is there another way I'm meant to create databases?

One of the key differences between these platforms is that a database in MySQL is a schema in Oracle.  Since a schema in Oracle is also a user, the command you are looking for is CREATE USER.  I highly recommend that you read the Oracle Concepts Guide before continuing.  You may also find this documentation on MySQL>>Oracle migrations using SQL Developer useful.  Here is the part relevant to your situation:

  When migrating MySQL databases to Oracle, SQL Developer maps each
  MySQL database to a tablespace in Oracle. Database objects, such as
  tables, indexes and views are stored in the respective tablespaces and
  are referenced from the Oracle schema for the user that owns them.

From the error message perspective, the salient part to pull out is "ORA-01100: database already mounted".  This indicates that a database has already been created and mounted.  It may also be open.  Here is an example reproducing your issue, opening the database (which may not be necessary if it is already open) and creating a django user.

SQL> create database django;
create database django
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-01100: database already mounted

SQL> SELECT status FROM v$instance;

STATUS
------------
MOUNTED

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT status FROM v$instance;

STATUS
------------
OPEN

SQL> create database django;
create database django
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-01100: database already mounted

SQL> CREATE USER django IDENTIFIED BY "No More ORA-001100.";

User created.

You should also assign permissions and create a django tablespace setting it as the default tablespace for django.


Depending on Foreign Key Constraints to avoid deletion In PostgreSQL, I have a 2 tables with father/parent relationship. I wish to delete the father record if no records of child are associated with it (using after triggers)

Currently, I have been doing something like

Get the count of records (having the same parent id as the current child)
If the count is 1, delete current child record and delete the parent record of it
If the count is greater than 1, just delete the child record (since more child's depend on the parent record)

I was wondering if I directly fire the delete statement without getting the count of parent record, like

Delete Child record
Delete Parent Record (shall throw a error if there are dependencies)

In this case if any child is associated with the parent (via a foreign key), it shall throw a error and not be deleted

Is the 2nd way right, to get this done, are such errors (foreign key constraints dependancies) logged in PostgreSQL

It seems like your intention is to delete the parent record when the last child is deleted.

If so, while you can issue an unconditional DELETE to remove the parent and let foreign key relationships with existing children prevent it, this may not be the best strategy. To make it work at all you must do the DELETE of the parent in a different transaction (or sub-transaction) to the DELETE of the child, otherwise the whole transaction's work will be undone when the DELETE of the parent fails and the transaction aborts.

Doing it in an entirely separate transaction is not very safe as it'll leave a window where there's a parent with no children - and if you ever re-use keys it's totally unacceptable.

Deleting the child then deleting the parent in a subtransaction using SAVEPOINT and ROLLBACK TO SAVEPOINT is OK, albeit awkward. You need to think about what happens when two concurrent transactions delete the last two children of the same parent, though; you'd land up with an orphan parent record because each would see the "remaining" child the other one is in the process of deleting. To prevent that you'll need to SELECT ... FOR UPDATE the parent before deleting the child.

BEGIN;
SELECT 1 FROM parent WHERE parent_id = 1 FOR UPDATE;
DELETE FROM child WHERE child_id = 11 and parent_id = 1;
SAVEPOINT delete_parent;
DELETE FROM parent WHERE parent_id = 1;
-- In the application see if the DELETE was successful. If it was, COMMIT.
-- if it failed, run:
ROLLBACK TO SAVEPOINT delete_parent;
-- before committing the deletion of the child.
COMMIT;

Alternately, because you're forcing transactions that remove children from a parent to occur serially, you can just replace the SAVEPOINT and everything below with:

DELETE FROM parent
WHERE parent_id = 1 AND NOT EXISTS (
  SELECT 1 FROM child c WHERE c.parent_id = parent.parent_id
);

An alternative strategy is to use a SERIALIZABLE isolation transaction to optimistically delete the parent instead of using the initial SELECT ... FOR UPDATE. If somebody else creates a new child or does something else that'll conflict, serializable isolation in PostgreSQL 9.1 or above will abort one of the conflicting transactions. Your application must be prepared to deal with errors and re-run aborted transactions.


Query Exceeding Query Timeout on Linked Server and Working Currently we have a SQL Server Agent job that executes a stored procedure that reads data over a linked server.

Initially on our test server this was timing out at 10m:17s (617 seconds).  We changed the Query Timeout in Linked Server properties->Server Options to 3600 (1 hour) and the SQL Server agent job now works fine with the Agent job taking about 30 minutes.

When this was made live on Production, the Agent job continued to succeed however with the difference in data quantity the job now takes 1h:15m.  This is beyond the timeout setting.  My theory is that the stored procedure is 'finished' with the linked server within 3600 seconds and the extra time is spent processing locally.

My concern is why is this working? Or more to the point preventing it from ceasing to work if the data changes much.

I have researched a number of topics on this, but I haven't encountered any similar posts.

Query Timeout setting affects only when the query is kept in waiting state for resources. This option doesn't affect the maximum duration a query can run and as per my understanding without custom logic, there is no setting which times out a query if it exceeds specified value.

MSDN also states the same. If the value is ZERO, SQL Server takes the value from
Global query wait Option setting which can be configured through Sp_configure

Below is an excerpt from MSDN:

  In Microsoft SQL Server, memory-intensive queries (such as those involving sorting and hashing) are queued when there is not enough memory available to run the query. The query times out after a set time calculated by SQL Server (25 times the estimated cost of the query) or the time specified by the nonnegative value of the query wait


Shrinking a SQL Server Transaction Log I’m currently having to deal with a SQL Server transaction log that has got out of control. Disclaimer: I’m not a dba and this isn’t my area of expertise so please bear with me.

Currently I have a 115GB Transaction log file for a 500MB database which has (obviously) been poorly managed for some time for it to get in this state.

The top priority is to reclaim the space on the disk taken up by this file before we run out! I’ve been told increasing the size of the drive is not an option, even temporarily, and based on past growth, we need to act pretty soon.

As I understand it, the best approach is to keep the db in full recovery mode but take regular backups of the log file, monitor this over a period of time and adjust initial size and increment to suit. All okay.

Seeing as we take regular full db backups at midnight, would it be safe for me to temporarily put the database into Simple Recovery Mode (after one of these backups has run), shrink the log file to reclaim (virtually all of) the space and then put it back in to Full Recovery with the backup strategy mentioned above?

My thinking is that if something happened around this time, we could simply restore the full backup without using the logs.

UPDATE

A few extra details in reply to some of the answers and comments:

We do want to retain the ability to do a point-in-time restore so the
database should remain in full recovery mode.
The reason that the t-log file has grown so large is that it has never been
backed up. Verified as log_reuse_wait_desc returns ‘LOG_BACKUP’.

The transaction log for that database contains all transactions since the last transaction log backup, or the last time it was switched from simple recovery mode. Execute the following to get the definitive answer as to why SQL Server can not truncate the log and subsequently why the log is growing.

SELECT  d.Name
        ,d.log_reuse_wait_desc
FROM    sys.databases d
ORDER BY
        d.name

If you want point in time recovery then leave the DB in the full recovery model and take frequent log backups. Each log backup will contain all transactions since the last log backup. The log backup process is also responsible for clearing the log and marking the space for reuse i.e. the next transaction made in the DB will be written to the start of the truncated log in a circular fashion. This backup and reuse of the log is what prevents the log file from growing.

If you are not interested in point in time recovery and want to simplify the administration of the database. Then set the database to the simple recovery model and do not take t-log backups. SQL Server will automatically truncate the transaction log after each transaction is committed. Meaning that once the transaction has been committed to the log the record is overwritten by the next transaction etc.

Either way, once you've made one of these two decisions you can then shrink the log file down to a more reasonable size. Note ideally you want to make it large enough so it doesn't grow but not so large that you'll need to shrink it again. Also note that you can not shrink the active part of the log.

Download and deploy https://ola.hallengren.com/ database administration solution to cover backups, index fragmentation, statistics and CHECKDB.

You might also find the 'disk usage' report returned by right clicking the DB in Object Explorer > Reports > Standard reports > 'disk usage' useful for returning the free space in the t-log.

I also recommend that you Google why it's so important to keep the log chain intact from a DR point of view, and how switching from full to simple  breaks the chain leaving you exposed to data loss.


PostgreSQL 10 - single big query in UDFs or multiple smaller ones? Many times, I'm able to write a PL/PGSQL UDF in two ways:

As a big query usually using WITH clauses and UNION (i.e. more declarative but also more complex to write/read)
As multiple smaller queries (i.e. more procedural but also easier to write/read)

I'm aware that PG query planner will plan each smaller query in UDF individually, and I'm afraid that might have an impact on overall performance. What do you guys prefer?

Thank you.

If you're able to write it as a single query Q, the best alternative is to avoid writing the UDF in the first place, and embed Q as a subquery in the calling query. That's probably the best if performance is your main concern.

If code reuse, modularity, readability are more important than performance, IMO you can choose option 2.


What's the most effective way to back-up / save data on Oracle 11g large data sets? We have a database(actually more databases split by primary access path, modulo on a key.) with millions of records (about 400 millions ). Right now I need to save a specific table userData(which has  specific data such username, address, city, state, zipcode, country) remotely(on a different server). I'm wondering which would be the best solution given the following requirements :

The data transfer between the host server (database server) and the
remote server must be as small as possible.

The operation should not be CPU / resource intensive. It should be silent (should not move needle too much on the monitoring dashboard) due the fact we are using a shared  On Demand(as a service) service provider which seems to place a "hold" on our instance when we make too much noise.

The operation must be  fast.
I was thinking if it would be possible to do some "compression" on the fly (e.g. using 7z) and I'm wondering what kind of query should I use to make it "silent " to do not disturb the network / CPU too much .
11g database is used on Solaris . On the remote server I'm planning to use redhat but that can be different of course .

You could use Oracle Data Pump Export and unload data compressing the resulting dump file(s). Below is an example of unloading the tables EMPLOYEES and DEPARTMENTS owned by the user HR:

expdp hr parfile=hrexp.par

The parameter file hrexp.par contains the following parameters:

content=data_only
compression=all
include=table:"IN ('EMPLOYEES', 'DEPARTMENTS')"
directory=dmpdir
dumpfile=hrdata.dmp
reuse_dumpfiles=yes
nologfile=yes

The dump file is created in the directory specified by the directory parameter. You will probably want this directory to be located on the NFS so that it's available both to source database host and the remote host. The Oracle database should have appropriate privileges to be able to read from/write to this directory, and the user which performs the export operation should have read/write access to the directory object:

SQL> create directory DMPDIR as '/nfs/oraexp';
Directory created.

SQL> grant read, write on directory DMPDIR to hr;
Grant succeeded.

In the example, the value of parameter content instructs Data Pump Export to unload data only. The compression parameter here specifies that the exported data should be compressed.

And now the most important part concerning performance. Oracle Data Pump can use different methods during load and unload operations, and in your case Direct Path method may be preferable because of your CPU resource usage requirement. In order for Data Pump to be able to use this method, you should satisfy some requirements listed in the section "Situations in Which Direct Path Unload Is Not Used" of the Database Utilities Guide:

  If any of the following conditions exist for a table, then Data Pump
  uses the external table method to unload data, rather than the direct
  path method:

  Fine-grained access control for SELECT is enabled.
  The table is a queue table.
  The table contains one or more columns of type BFILE or opaque, or an object type containing opaque columns.
  The table contains encrypted columns.
  The table contains a column of an evolved type that needs upgrading.
  The table contains a column of type LONG or LONG RAW that is not last.
  The Data Pump command for the specified table used the QUERY, SAMPLE, or REMAP_DATA parameter.


Question about cost optimization formula for SELECT I am a student in computer science and I have a question, which refers to cost optimization. I have to read the book (Fundamentals of Database Systems from Elmasry and Navathe) and try to present the topic to class. But I have some issues understanding one cost formula for B-tree-index:

  Using a secondary (B+-tree) index: [...]If the comparison condition is
  (> >=, <, <=) and half the file records are assumed to satisfy the condition, then(very roughly) half of the first level index blocks are
  accessed, plus half the file records via the index. The cost estimate
  for this case, approximately, is C=x+(b_l1/2)+(r/2).[...]

  Using Linear search: [...] C=b[...]

where

x = number of levels of multilevel index
r = number of records
b = number of blocks
b_li = number of first-level index blocks
C = cost function

I don't understand the formula for the secondary index. I think the formula should look like this:

  C=x+(b_l1/2)+(b/2)

Can somebody explain me, why the formula uses the number of entries instead of number of blocks?

Thank you very much

Assuming "first level" means the leaf level of a nonclustered (secondary) index, the b_li/2 part accounts for the cost of scanning (using sequential I/O) through half those index blocks.

Assuming "file records" means the base table, the r/2 component represents the cost of looking up additional data not present in the secondary index. Since this is a per-row cost that is expected to result in a random I/O pattern, it makes sense to express this in terms of r.


Converting spatial data from Oracle to MySQL This is a followup to this question: Problem getting zips in raius via MySQL

I have some spatial stuff in an Oracle table.

MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-71.88455,42.27993,-71.88467,42.27977,-71.88271,42.27823,-71.88269,42.2780.........

How can I convert this geometry to a MySQL geometry type? I can write a script to do it if someone can give me the basics.

I took an entire 8 hour shift of reading boring Oracle and MySQL docs to figure it out but here it is.. in PHP..

(as a Github Gist)

/**
 * Convert an Oracle SYS.SDO_GEOMETRY definition (perhaps extracted from an insert
 * statement) to a MySQL Geometry column. This function only handles Polygons and
 * Multipolygons.
 *
 * Example:
 * $definition = "MDSYS.SDO_GEOMETRY(2003,4326,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(-75.01703,41.79308,-75.02978,41.7941,-75.02735,41.772,-75.02716,41.77193,-75.02697,41.77187,-75.01354,41.79051,-75.01337,41.79061,-75.0132,41.79072,-75.00949,41.79234,-75.00946,41.79254,-75.00943,41.79274,-75.00518,41.7943,-74.9995,41.79178,-74.99365,41.79788,-74.99383,41.79798,-74.99876,41.80318,-74.9988,41.80337,-74.99883,41.80349,-75.00425,41.80479,-75.00431,41.80459,-75.01239,41.79415,-75.01245,41.79412,-75.01688,41.79345,-75.01696,41.79327,-75.01703,41.79308))";
 * $sql = "INSERT INTO mytable (geom) VALUES (".OraclePolygonToMysql($definition).")";
 */
function OraclePolygonToMysql($definition){
    // Get the geometry type, our data should only be polygons and multipolygons
    $SDO_GTYPE = substr($definition, strpos($definition, "(") + 3, 2);
    switch($SDO_GTYPE){
        case "03": $type = "POLYGON"; break;
        case "07": $type = "MULTIPOLYGON"; break;
        default: die("hol up i thought we only had polygons in this bitch");
    }
    // MDSYS.SDO_ELEM_INFO_ARRAY contains info about how to split up the coordinates..
    // So the info array is some dumb ass 1-indexed triplet model where we only need every third index..
    // https://docs.oracle.com/cd/B12037_01/appdev.101/b10826/sdo_objrelschema.htm#i1006226
    $start = strpos($definition, "MDSYS.SDO_ELEM_INFO_ARRAY")+strlen("MDSYS.SDO_ELEM_INFO_ARRAY(");
    $infoArray = explode(",", substr($definition, $start, strpos($definition, ")", $start) - $start));
    // MDSYS.SDO_ORDINATE_ARRAY contains the actual ordinates that need to be grouped properly for
    // MySQL to understand them
    $start = strpos($definition, "MDSYS.SDO_ORDINATE_ARRAY") + strlen("MDSYS.SDO_ORDINATE_ARRAY");
    $ordsArray = explode(",",trim(substr($definition, $start), " ()"));
    // Get the starting index fro each group
    $groupStarts = array();
    while(count($infoArray) > 0){
        $startIndex = intval(array_shift($infoArray)) - 1; // compensate for the dumb ass 1-index
        array_shift($infoArray); array_shift($infoArray); // we don't need these... i don't think
        $groupStarts[] = $startIndex;
    }
    // Get the ending index for each group
    $groups = array();
    for($i=0; $i<count($groupStarts); $i++){
        $start = $groupStarts[$i];
        $end = isset($groupStarts[$i+1]) ? $groupStarts[$i+1] - 1 : count($ordsArray)-1;
        $groups[] = array("first"=>$start, "len"=>$end-$start+1);
    }
    // Group the indexes into their own arrays
    for($i=0; $i<count($groups); $i++)
        $groups[$i]['group'] = array_slice($ordsArray, $groups[$i]['first'], $groups[$i]['len']);
    // Create the string from the groups array
    $chunks = array();
    foreach($groups as $group){
        $chunked = array_chunk($group['group'], 2);
        foreach($chunked as $k=>$chnk) $chunked[$k] = implode(" ", $chnk);
        $c = "(".implode(",", $chunked).")";
        $chunks[] = $type === "MULTIPOLYGON" ? "($c)" : $c;
    }
    return "GeomFromText('$type(".implode(",",$chunks).")')";
}


Cannot start SQL Server 2014 service. Memory Error (resource pool 'internal') Under Windows 2012 R2 Standard (Fully Updated): When trying to start my SQL Server 2014 Service I get in Application event viewer:

"there is insufficient system memory in resource pool 'internal' to run this query"

Here is the complete ERRORLOG in pastebin.

Significant errors I can see:

2017-07-04 15:32:56.59 spid8s       Failed allocate pages: FAIL_PAGE_ALLOCATION 1
...
2017-07-04 15:32:56.62 spid8s      Error: 701, Severity: 17, State: 123.
2017-07-04 15:32:56.62 spid8s      There is insufficient system memory in resource pool 'internal' to run this query.
2017-07-04 15:32:56.65 spid8s      SQL Server shutdown has been initiated
2017-07-04 15:32:56.68 spid8s      Error: 701, Severity: 17, State: 123.
2017-07-04 15:32:56.68 spid8s      There is insufficient system memory in resource pool 'internal' to run this query.
2017-07-04 15:32:56.77 spid8s      SQL Server shutdown has been initiated

This is a RAM Map screnshot of my server.

This machine is hosted in a VPS, and technical support says the "Driver locked" RAM is assigned on demand, so I can't find why SQL Server throws this memory error. Any ideas??

This machine is hosted in a VPS, and technical support says the
  "Driver locked" RAM is assigned on demand, so I can't find why SQL
  Server throws this memory error. Any ideas??

Yes.  Your hoster has "borrowed" your VMs memory and given it to another VM.  The "Driver Locked" memory is a "Baloon Driver" that both VMWare and Hyper-V use to enable dynamic memory allocation to VMs.  That memory appears to belong to the VM, but is actually held by the hypervisor, and may be currently being used by other VMs.

Not all operating systems support actually changing the amount of memory they use without a reboot.  In Windows this capability only appeared in Windows 10/Windows Server 2016.  So the way hypervisors handle this is they install something inside the VM to allocate memory.  Then this memory is actually removed from the VM and is available for other VMs.  Typically they use a driver (as opposed to a user process), commonly called a "Baloon Driver".

So the almost 12 GB are "Driver Locked" by the VPS manager is not actually available.  You need to ask your hoster to give your VM more memory.

It may be the case that the hypervisor is supposed to respond to memory pressure inside the VM adding memory to the VM and reducing the amount locked away in the Baloon Driver.  But if so, this is just not working well enough to enable your SQL Server to start, and they need to bump up the startup memory for your VM a bit.

On the other hand, if you paid for 16GB of ram, you might want to look for a new hoster who doesn't engage in such shenanagans.


Exporting results of a query in SQL developer without having to execute the query twice Is there anyway to export the results of a query in Oracle SQL Developer without having to execute the query twice? Running a SQL query in worksheet, then Right click on Result Set window -> Export Data -> Text will run the query a second time.

There are some SQL Developer specific comments/"hints". For example if you run the below as a script (F5) and not a statement (Ctrl-Enter):

select /*csv*/ * from table;

You will get the results in CSV format the first time already. You can even spool the output just as in SQL*Plus. So you could just run the below block of code as a script (select lines and F5) and get a CSV directly in one pass:

spool C:\Users\XYZ\Desktop\my.csv
select /*csv*/ * from table;
spool off

Starting with version 4.1, you do not even need to use the above comment/"hint". You can just:

SET SQLFORMAT csv

Then run your query as a script.

Further options here:
http://www.thatjeffsmith.com/archive/2012/05/formatting-query-results-to-csv-in-oracle-sql-developer/


Groups, Users, and Inbox We're working on a PM system where a user can send an email to other users or groups.

We currently have the following tables:

Users
Groups

Users can be part of many groups. A user doesn't have to be part of any group.

The problem I am having is with managing recipients and inboxes. Groups don't have inboxes - users do. An inbox stores information like whether or not the recipient has read the message, marked it for follow up, deleted it, etc.

So I made two tables:

recipients

recipients_inbox

recipients just stores the thread id, recipient id, and whether or not the recipient is a group or not.

recipients_inbox has the users who this message was sent to - whether as part of a group or individually. It stores: thread id, recipient id, when the thread was last read, flagged, etc.

My question is: Does it make sense to keep the recipients and recipients_inbox table separate? Should I just combine them and where the recipient is a group leave user-fields such as lastRead and flagged as null?

Thank you

Here's how I think I would approach the issue:

TABLE message
    id number,
    message varchar2(8000),
    sent_by_user number,
    etc...

TABLE inbox
    id number,
    user_id number,
    message_id number,
    etc...

Then, when sending to a group, I would go ahead and add an entry to each user's inbox with a pointer at the message. This way you aren't duplicating the message content, but you simplify the system by allowing users to perform their own actions on group messages without affecting the group message for the group as a whole.

Not sure it is a complete answer to your issue, but I would go this route since it means that message management is always a user-level issue; only during send operations do groups need to enter the equation.


How to efficiently create a new PRIMARY KEY CLUSTERED definition online Given a table with a PRIMARY KEY definition (acting as the clustered index), I want to efficiently change the definition of the PRIMARY KEY (or at a minimum, just the clustered index) online.

I would love to simply do the following:

CREATE UNIQUE CLUSTERED INDEX PK_MyTable ON dbo.MyTable
(
    [Col2] ASC
) WITH (DROP_EXISTING = ON, ONLINE = ON);

However this results in an error: Cannot recreate index 'PK_MyTable'. The new index definition does not match the constraint being enforced by the existing index.. And I understand why.

Is there another way to accomplish this?

For background: we have a table that is only queried via a nonclustered index. The characteristics of the that index are quite good to be the clustered index of the table (i.e. unique and unchanging narrow key, inserts are primarily at the "end"). The current PRIMARY KEY (the clustered index) is simply a surrogate key. The table is not the target of any FK relationships, and rows are never queried by the current PRIMARY KEY. We would like to efficiently change the clustered index online. We are running Enterprise edition.

If terms of "online" operations; at a minimum we want to continue inserting new rows, but we can temporarily suspend queries until the operations complete.

If it's not possible to do this with the existing table online, is there an approach (maybe involving a new table and copying the data) that could work with as little interruption as possible?

If you're running Enterprise edition, you can add and remove indices, including clustered indices, with ONLINE.

Since your existing PK isn't being used, you can start by dropping it.  Then create your new clustered index, and finally drop your old non-clustered index which is now obsolete.

 ALTER TABLE MyTable DROP CONSTRAINT PK_MyTbl_SrgKey WITH (ONLINE=ON)
 ALTER TABLE MyTable ADD CONSTRAINT PK_MyTbl_BizKey PRIMARY KEY (X) WITH (ONLINE=ON)
 DROP INDEX IX_MyTbl_BizKey ON MyTable WITH (ONLINE=ON)

At no point will the biz key field be un-indexed, and there should be minimal blocking.

I'd guess you're already familiar with this syntax and you're hoping for something simpler or more atomic.  Hopefully someone can offer this!


Disabling certain steps in a job I have a project coming this weekend where all the jobs has to be stopped but only 1 step in a particular job has to be run.
for eg: i have a a 2 step job and the second step must run and the first step has to be disabled or stopped along with the other jobs.

First of all i thought of stopping the sql server agent, but that will stop all the jobs. Is there any way to do it.

You can't disable a particular job step.

You have two options, the way I see it:

Change the job to have the "start step" as your second step, and have the second step's "on success action" to quit the job (if there are subsequent steps)
Create a separate job with the step's logic and then only use the new job that does the work


MySQL on insert in table update field in another table? I'm creating one PHP script and I need to update table Topics when someone insert something in table TopicAnswers.

Is there any SQL query to do something like this, for example something as FOREIGN KEY?

UPDATE:

mysql> SHOW CREATE TABLE MintForum.Topics\G
*************************** 1. row ***************************
       Table: Topics
Create Table: CREATE TABLE `topics` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `CategoryID` int(10) unsigned NOT NULL,
  `UserID` int(10) unsigned NOT NULL,
  `TopicTitle` varchar(255) NOT NULL,
  `TopicQuestion` longtext NOT NULL,
  `Visits` varchar(255) NOT NULL,
  `Answers` varchar(255) NOT NULL,
  `CreateDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TI
MESTAMP,
  PRIMARY KEY (`ID`),
  KEY `CategoryID` (`CategoryID`),
  KEY `UserID` (`UserID`),
  CONSTRAINT `topics_ibfk_1` FOREIGN KEY (`CategoryID`) REFERENCES `categories`
(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_2` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON
 UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_3` FOREIGN KEY (`CategoryID`) REFERENCES `categories`
(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_4` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON
 UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_5` FOREIGN KEY (`CategoryID`) REFERENCES `categories`
(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_6` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON
 UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_7` FOREIGN KEY (`CategoryID`) REFERENCES `categories`
(`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topics_ibfk_8` FOREIGN KEY (`UserID`) REFERENCES `users` (`ID`) ON
 UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

mysql> SHOW CREATE TABLE MintForum.TopicAnswers\G
*************************** 1. row ***************************
       Table: TopicAnswers
Create Table: CREATE TABLE `topicanswers` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `TopicID` int(10) unsigned NOT NULL,
  `UserID` int(10) unsigned NOT NULL,
  `TopicAnswer` longtext NOT NULL,
  `PostDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIME
STAMP,
  PRIMARY KEY (`ID`),
  KEY `TopicID` (`TopicID`),
  KEY `UserID` (`UserID`),
  CONSTRAINT `topicanswers_ibfk_1` FOREIGN KEY (`TopicID`) REFERENCES `topics` (
`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_2` FOREIGN KEY (`UserID`) REFERENCES `users` (`I
D`) ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_3` FOREIGN KEY (`TopicID`) REFERENCES `topics` (
`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_4` FOREIGN KEY (`UserID`) REFERENCES `users` (`I
D`) ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_5` FOREIGN KEY (`TopicID`) REFERENCES `topics` (
`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_6` FOREIGN KEY (`UserID`) REFERENCES `users` (`I
D`) ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_7` FOREIGN KEY (`TopicID`) REFERENCES `topics` (
`ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `topicanswers_ibfk_8` FOREIGN KEY (`UserID`) REFERENCES `users` (`I
D`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Insert Query:

INSERT INTO TopicAnswers (TopicID, UserID, TopicAnswer) VALUES (:TopicID, :UserID, :TopicAnswer);

Update Topics Query:

UPDATE Topics SET Answers = Answers + 1;

New Problem:
How I can implement this query:

UPDATE Topics SET Answers = Answers + 1 WHERE ID = TopicID;

instead of:

UPDATE Topics SET Answers = Answers + 1

Where TopicID must be TopicID of just inserted row?

SOLVED!

I used :

UPDATE Topics SET Answers = Answers + 1 WHERE ID = NEW.TopicID;

Something like this should do the trick.

DELIMITER $$
CREATE TRIGGER My_Trigger -- name is arbitrary
AFTER INSERT ON TopicAnswers
FOR EACH ROW
BEGIN
  UPDATE Topics
    SET Answers = Answers + 1
    WHERE ID = NEW.TopicID;  -- The OP figured this line out for himself.
END;
$$
DELIMITER ;

I don't have your data, but it should get you started.

Take the trouble to go to the Help Centre, take the tour
and check out the "help us to help you" blog. This forum
is a great resource and you will get all the more out of
it if you take the trouble to read the guidelines (not that
there's anything wrong with your post). p.s. welcome to the
forum.


SQL Server - backup checksum default vs VSS A (hopefully) quick question - does enabling backup checksum default option affect VSS backups in any way?

This gives you a little bit of extra protection for your database backups, since the backup operation verifies each page for checksum and torn page, and generates a checksum for the entire backup. This is not a substitute for actually restoring you database backups on a regular basis, but it is a useful extra step to help ensure that your backups are actually good.
CHECKSUMs does not add too much CPU load but doing a VERIFYONLY does. There is no impact on vss snapshot creation.


bcp in of datetime type data results in an "Invalid date / time format" I am familiarizing myself with the bcp utility, and I have a large table containing datetime data awhich I bcp out in several ways: native, character, delimited.

I then bcp in to a truncated table with the same columns/format as the original table, however, I obtain errors like:

----------------
Starting copy...
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid date format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid time format
SQLState = 22008, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Invalid date format

4 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 15     Average : (266.67 rows per sec.)
----------------

And for the rows that do get copied, the other columns contain garbage data.

If I do the bcp out/in with data in other datatypes, everything seems okay?

How can I make this to work?

I have seen this before.  Ensure your datetimes are formatted like this:

yyyy-mm-ddThh:mm:ss

Example:

2011-01-18T17:30:59 would represent January 18th 2011 at 5:30:59PM

It has been a while since I ran into an issue similar to this, but I remember this being a step I took to resolve my problem.


For MySQL, how to INSERT the result of a SELECT statement? For MySQL, how to INSERT the result of a SELECT statement? I tried

SET @input1 = 1;
SET @input2 = 2;
INSERT INTO
    my_table (col1, col2)
SELECT
    @input1 AS col1,
    @input2 AS col2
WHERE
    @input1 > 0
        AND
    @input2 > 0;

which gives a message:

  [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE @input1 > 0 AND @input2 > 0' at line 6

Any idea how to fix it?

Here is a related article I found on this INSERT-SELECT approach to do conditional insert:

https://timmurphy.org/2013/10/23/conditional-insert-in-sql/

But it seems it does not work for MySQL which requires a FROM clause for SELECT?

The basic syntax for this generally involves inserting into one table by selecting from another, which would look something like:

INSERT INTO t1 (a1, b1)
     SELECT a2, b2
       FROM t2
      WHERE a2 > 0
            AND
            b2 > 0
;

There is some flexibility in that, although perhaps not as much as you are looking for. It might be worth looking at the mysql docs for insert-select for more information.