I stated in the above comments that this seems to be a case of a supertype-subtype relationship but, since changing your database structure may be a decision that lies outside of the scope of this question, I am going to focus on offering a solution for your current situation.

Then, after a couple of edits, I decided to include some DDL statements and a brief description of my assumptions about your database structure, hoping that in this way my answer and its involved queries will be more easily comprehended.

Understanding of the situation and hypothetical DDL

As I percive the scenario, type_a and type_b may be two different kinds of reading or measurement that you are collecting for each device.

This way, although I am not completely sure of the meaning of the type_a.type_a_id and type_b.type_b_id columns, I assume that they are some sort of sequential_number or row_number or record_identifier in each respective table. Likewise, the columns type_a.stats_time and type_b.stats_time are the exact points in time when a device posts some sort of reading.

I have also assumed that type_a.device_id and type_b.device_id are FOREIGN KEYS that make reference to the device table that, in turn, has the column device.device_id being used as some kind of sequential_number or row_number or record_identifier that you have defined as PRIMARY KEY.

Having described my understanding of the state of affairs, please note that I am not suggesting that this is the optimal structure (since, naturally, I am not familiar with the real scenario, it may lack normalization, integrity, etc.), I am just making some assumptions based on the data samples and queries that you have presented, in order to deliver a possible solution for your specific situation. So, here is the speculative DDL:

CREATE TABLE device
(
    device_id INT      NOT NULL AUTO_INCREMENT,
    name      CHAR(30) NOT NULL,
    type      CHAR(1)  NOT NULL,
    PRIMARY KEY (device_id),
    UNIQUE INDEX uix_name (name)
);

CREATE TABLE type_a
(
    type_a_id  INT      NOT NULL AUTO_INCREMENT,
    device_id  INT      NOT NULL ,
    stats_time DATETIME NOT NULL,
    `status`   CHAR(10) NOT NULL,
    noise      INT      NOT NULL,
   PRIMARY KEY (type_a_id),
   CONSTRAINT FOREIGN KEY fk_type_a_device (device_id)
   REFERENCES device(device_id),
   UNIQUE INDEX `uix_device_id_and_stats_time` (device_id, stats_time)
);

CREATE TABLE type_b
(
    type_b_id  INT      NOT NULL AUTO_INCREMENT,
    device_id  INT      NOT NULL,
    stats_time DATETIME NOT NULL,
    `status`   CHAR(10) NOT NULL,
    `signal`   INT      NOT NULL,
    PRIMARY KEY (type_b_id),
    CONSTRAINT FOREIGN KEY fk_type_b_device (device_id)
    REFERENCES device(device_id),
    UNIQUE INDEX `uix_device_id_and_stats_time` (device_id, stats_time)
);

-- Some ‘device’ INSERTS...

INSERT INTO device (name, type) VALUES ('First device', 'A');
INSERT INTO device (name, type) VALUES ('Second device', 'A');

-- ... And then, some ‘type_a’ and ‘type_b’ INSERTS
-- in order to have some sample data for retrieving.

INSERT INTO type_a (device_id, stats_time, `status`, noise)
VALUES (1, STR_TO_DATE('06/01/2015 08:10:01 AM', '%c/%e/%Y %r'), 'Foo', 123);

INSERT INTO type_a (device_id, stats_time, `status`, noise)
VALUES (1, STR_TO_DATE('04/04/2015 03:07:34 PM', '%c/%e/%Y %r'), 'Bar', 456);

INSERT INTO type_b (device_id, stats_time, `status`, `signal`)
VALUES (2, STR_TO_DATE('03/04/2015 02:08:15 PM', '%c/%e/%Y %r'), 'Boo', 789);

INSERT INTO type_b (device_id, stats_time, `status`, `signal`)
VALUES (2, STR_TO_DATE('05/07/2015 04:03:12 PM', '%c/%e/%Y %r'), 'Far', 852);

Initial proposal

And then, here is my first submited query which, while keeping the original idea, has been reformatted and adapted to the DDL structure that was added later:

SELECT DE.device_id,
       COALESCE(TA.type_a_id, TB.type_b_id)   AS type_id,
       DE.name,
       DE.type,
       COALESCE(TA.stats_time, TB.stats_time) AS stats_time,
       COALESCE(TA.status, TB.status)         AS `status`,
       COALESCE(TA.noise, 0)                  AS noise,
       COALESCE(TB.signal, 0)                 AS `signal`
  FROM device            DE
  LEFT OUTER JOIN type_a TA
    ON TA.device_id    = DE.device_id
  LEFT OUTER JOIN type_b TB
    ON TB.device_id    = DE.device_id
ORDER BY stats_time DESC;

As you can see, I am using the COALESCE() function that only if the column type_a.stats_time contains a NULL value, then ‘prints’ the value of the column type_b.stats_time, and works in a similar way for the type_a.status and type_b.status columns.

Subsequent proposal

First method

Now, having reread your question and after some comment interactions, I understand that what you actually want is to obtain a single result set containing only two rows —one row including columns corresponding to the latest type_a.stats_time value and one row holding columns related to the latest type_b.stats_time value—. Therefore I suggest you the following query that also retrieves data from the DDL proposed above:

(SELECT DE.device_id,
        TA.type_a_id  AS type_id,
        DE.name,
        DE.type,
        TA.stats_time AS stats_time,
        TA.status,
        TA.noise,
        NULL          AS `signal`
   FROM device DE
   JOIN type_a TA
     ON TA.device_id  = DE.device_id
  WHERE TA.stats_time = (SELECT MAX(stats_time)
                           FROM type_a))

UNION

(SELECT DE.device_id,
        TB.type_b_id  AS type_id,
        DE.name,
        DE.type,
        TB.stats_time AS stats_time,
        TB.status,
        NULL          AS noise,
        TB.signal
   FROM device DE
   JOIN type_b TB
     ON TB.device_id  = DE.device_id
  WHERE TB.stats_time = (SELECT MAX(stats_time)
                           FROM type_b))

ORDER BY stats_time DESC;

Note the use of the UNION operator, which in serves the purpose of combining in one single result set the latest row of type_a (based on the newest stats_time column value, obtained via the MAX() function within a subquery in the WHERE clause) with the latest row in type_b (likewise, based on the newest stats_time column value, also obtained through the MAX() function within a subquery in the WHERE clause).

Second method

You can also try with this alternate query, which sorts each one of the combined result sets based on the stats_time column in each respective SELECT statement using the ORDER BY and LIMIT clauses.

(SELECT DE.device_id,
        TA.type_a_id  AS type_id,
        DE.name,
        DE.type,
        TA.stats_time AS stats_time,
        TA.status,
        TA.noise,
        NULL          AS `signal`
   FROM device DE
   JOIN type_a TA
     ON TA.device_id = DE.device_id
  ORDER BY TA.stats_time DESC LIMIT 1)

UNION

(SELECT DE.device_id,
        TB.type_b_id  AS type_id,
        DE.name,
        DE.type,
        TB.stats_time AS stats_time,
        TB.status,
        NULL          AS noise,
        TB.signal
   FROM device DE
   JOIN type_b TB
     ON TB.device_id = DE.device_id
  ORDER BY TB.stats_time DESC LIMIT 1)

ORDER BY stats_time DESC;

Once you compare the performance of all the suggested methods, it will be easy to define which one is the best for your needs. Also, if one of them solves your problem, then you can set it up as a VIEW, this way future data retrieval will be easier to obtain.

About the speed performance aspect of your queries, you may start refining such matters by paying special attention to the indexes definition in the apropiate columns, e.g. type_a.stats_time and type_b.stats_time.


How can I configure PostgreSQL 9.0 to start automatically under Windows XP, Part II? Two weeks ago I asked "How do I configure Windows XP to start the postgresql demon after each boot/reboot?", and BenV helped me out with his answer:

Make sure the service is set to start automatically.

Start->Control Panel->Administrative Tools->Services
Find the service (should be named something like postgresql-9.0)
Make sure the Startup Type is set to Automatic

Additionally there was a problem with the password for the user postgres that I had to correct, and everything worked fine - until the 2nd reboot (I installed PostgreSQL on my developer PC, which I switch off every night). I had to correct the password again, and have to redo it ever since after every 2nd reboot.

What can I do?

There's also one more setting to be noted...
On the same window where we set Automatic/Manual start up, there's a "Recovery" tab in which we can force the service to restart when a failure occurs.

But this might not be a good idea to use, especially on development environments, since it may help slip some possible problems past the developer's and/or administrator's eyes.


How should I migrate a large MySQL database to RDS? I've already looked into this a little bit. I realize there are similar questions on Stack Overflow, and Amazon themselves have a helpful document giving advice here:

http://aws.amazon.com/articles/2933

My concerns are the following:

Amazon recommends using mysqldump only for "small amounts of data", which they define as less than 1GB. The database I intend to migrate is over 20GB.

One thing that's nice about mysqldump, however, is that it has the --single-transaction flag, which allows me to ensure a DB state that is consistent with a single point in time.

For larger amounts of data, Amazon's recommendation is to export the database into flat (e.g., CSV) files and then use mysqlimport to import those to RDS. The best way I know how to do this, however, is through the SELECT ... INTO OUTFILE command, which only operates one table at a time. The downside to this, of course, is that it doesn't provide the consistency guarantee of --single-transaction.

I suppose I could ensure consistency by taking the entire DB down temporarily; but I'd like to avoid that if at all possible.

What's the best way to get my large (> 20GB) database into flat files so that I can then use mysqlimport?
If it is indeed the SELECT ... INTO OUTFILE command, how do I export all of the tables in the database (preferably without having to do one at a time)?
Is there any good way to ensure consistency throughout all this?

I just recently spent a lot of time trying to figure out a 15GB transition to RDS. Ended up finding a script on one of the amazon forums that I modified to my own uses and seems to work well. I'm not sure if you can do single transaction, but the dump itself is very quick compared to the actual transfer. I think 15GB only took me 12 minutes to dump, so even if it doesn't have single transaction option I don't think you'd have a very long span of time for inconsistencies to occur. I'm not sure if that's good enough for you, but I found the solution a lot more graceful than the flat file method.

#!/bin/bash

declare -a dbs=(dbname1 dbname2 dbname3 dbname4);

j=0
while [ $j -lt 4 ];
#4 is the number of dbs
do

echo "Dumping ${dbs[$j]} DB"
time mysqldump --order-by-primary --host=sourcehost --user=sourceuser --password=sourcepass `echo ${dbs[$j]}` > /tmp/`echo ${dbs[$j]}`.sql
echo "Adding optimizations to ${dbs[$j]}"
awk 'NR==1{$0="SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;\n"$0}1' /tmp/`echo ${dbs[$j]}`.sql >> /tmp/`echo ${dbs[$j]}`X.sql
mv /tmp/`echo ${dbs[$j]}`X.sql /tmp/`echo ${dbs[$j]}`.sql
echo "SET unique_checks=1; SET foreign_key_checks=1; COMMIT;" >> /tmp/`echo ${dbs[$j]}`.sql
echo "Copy ${dbs[$j]} into RDS"
time mysql --host=yourrds.rds.amazonaws.com --user=rdsuser --password=rdspassword `echo ${dbs[$j]}` < /tmp/`echo ${dbs[$j]}`.sql &

j=$(($j+1))
done


MySQL user defined rollback procedure I'm attempting to write my own mini-rollback procedure. I have a table that tracks any updates or deletes to another table using a trigger. I am attempting to make it possible to restore one or more of these tracked changes through the use of a procedure. However, I'm receiving a syntax error with the following:

-- UNDO_CHANGES_PROCEDURE - This copies the values of the work log track table back into the relevant record in the work log table if an accidental edit or deletion is made.
DROP PROCEDURE IF EXISTS UNDO_CHANGES_PROCEDURE;

DELIMITER $$

CREATE PROCEDURE UNDO_CHANGES_PROCEDURE(ID INT(6))
BEGIN
DECLARE var_trig CHAR(8);

SET var_trig = (SELECT TRIGGER_TYPE FROM WORK_LOG_TRACK WHERE WORK_LOG_TRACK.WORK_LOG_EDIT_NUMBER = ID);

IF var_trig = 'Update' THEN
UPDATE WORK_LOG SET ENTRY_NUMBER = WORK_LOG_TRACK.ENTRY_NUMBER_FK, EMPLOYEE_ID_FK = WORK_LOG_TRACK.EMPLOYEE_ID_FK, WORK_ORDER_NUMBER_FK = WORK_LOG_TRACK.WORK_ORDER_NUMBER_FK, ENTRY_TIME = WORK_LOG_TRACK.ENTRY_TIME, WORK_DONE = WORK_LOG_TRACK.WORK_DONE WHERE WORK_LOG_TRACK.WORK_LOG_EDIT_NUMBER = ID AND WORK_LOG.ENTRY_NUMBER = WORK_LOG_TRACK.ENTRY_NUMBER_FK;
ELSE
INSERT INTO WORK_LOG(ENTRY_NUMBER, EMPLOYEE_ID_FK, WORK_ORDER_NUMBER_FK, ENTRY_TIME, WORK_DONE) VALUES (WORK_LOG_TRACK.ENTRY_NUMBER_FK, WORK_LOG_TRACK.EMPLOYEE_ID_FK, WORK_LOG_TRACK.WORK_ORDER_NUMBER_FK, WORK_LOG_TRACK.ENTRY_TIME, WORK_LOG_TRACK.WORK_DONE) WHERE WORK_LOG_TRACK.WORK_LOG_EDIT_NUMBER = ID;
END IF;
END;
$$

DELIMITER ;

The syntax error comes in in regards to my update statement, any help or suggestions would be appreciated.

The syntax error comes from the INSERT statement. The statement INSERT...VALUES does not support WHERE clause.

Possible you want this -

INSERT INTO WORK_LOG (
  ENTRY_NUMBER, EMPLOYEE_ID_FK, WORK_ORDER_NUMBER_FK, ENTRY_TIME, WORK_DONE
)
SELECT
  ENTRY_NUMBER_FK,
  EMPLOYEE_ID_FK,
  WORK_ORDER_NUMBER_FK,
  ENTRY_TIME,
  WORK_DONE
FROM
  WORK_LOG_TRACK
WHERE
    WORK_LOG_EDIT_NUMBER = ID;


How can I drop all triggers in a single database? I have a database with 104 triggers, is there a way to delete all the triggers with a single command from a single database called 'system_db_audits?

You can use Dynamic SQL and the sys.triggers DMV to build query that you can execute.

is_ms_shipped excludes any triggers that were shipped with SQL Server.
parent_class_desc filters for object level triggers, rather than database level.

Change the PRINT to an EXEC once you are happy with the output.

USE system_db_audits;
GO

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql +=
    N'DROP TRIGGER ' +
    QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + N'.' +
    QUOTENAME(t.name) + N'; ' + NCHAR(13)
FROM sys.triggers AS t
WHERE t.is_ms_shipped = 0
  AND t.parent_class_desc = N'OBJECT_OR_COLUMN';

PRINT @sql;


Is reading a single record always an atomic operation? Is reading a single record always an atomic operation?

I mean, when e.g. a read (select) and write (update) query are simultaneously carried out on the same record, is it possible for the read operation to read and inconsistent record? (some field values from the old version of that record and some new values resulting from the write operation be returned)

In an ACID compliant database it is atomic


Multiple instance on single SQL Server We have a server running single instance of SQL SERVER in production environment.

We are planning to create another Instance of SQL SERVER in same server.

My question do we have to buy a new license or the existing license is good enough ?

You can install as many instances of SQL Server as you like on a server, up to 50. The licensing is by the server, not by the instance (it used to be, once upon a time) so you have some freedom to do so.

The real question I would counter with is: Do you need another instance? Most people don't.


Query to Find Out How Many Days Until Database Is Full Using my central MDW database I am trying to make a query that will be able to calculate the number of days until all my databases free space will be full. Currently the query I have is overly complicated and takes long to run.

I am wondering if there is a simple solution using the following columns to calculate this. Columns I have are:

row_number
snapshot_time
instance_name
database_name
dbsize
current_usage

All tables are found in the MDW database.
Any suggestions are greatly appreciated!

You will need to start building a history of the dbsize vs current_usage metrics by day (or more incremental if needed).  Only then will you be able to make educated calculations for projected dates.

Otherwise you'll essentially just be setting a "hard-coded" linear curve that won't really be precise enough.

Basically, have a stored procedure that takes a snapshot of the current dbsize, current_usage and inserts it into a historical table.  Once you have enough data, you'll be able to use that data to make your projections.


How to filter out sales to find customers who have not purchased in the last 2 years What I am trying to accomplish is to find all the customers who have sales in the past but have not purchased from us in the last 2 years (SYSDATE - INTERVAL '2' YEAR)

Here is my code currently.

select invc.company
 , invc.customer_id
 , to_char(invc.invdate, 'mm/dd/yyyy') as date
 , invc.name
 , invc.address1
 , invc.address2
 , invc.city
 , invc.state
 , invc.zip_code
 , invc.country
 , invc.region_code
 , invc.site
 , sum(invc.sales) as SlsAmnt
from ifsinfo.hb_invoicing_all invc
where **THIS IS THE PART IM STUCK AT**
group by invc.company
           , invc.customer_id
           , invc.creation_date
           , invc.name
           , invc.address1
           , invc.address2
           , invc.city
           , invc.state
           , invc.zip_code
           , invc.country
           , invc.region_code
           , invc.site

I am basically looking for a query that will return a list of customers who haven't purchased from us in 2 years or more.

SUM(SALES) = 0 within last 2 years+

No sales in the last two years means:

having max(invc.invdate) < (SYSDATE - INTERVAL '2' YEAR)


ERROR - Function 'innodb' already exists I'm looking to start converting some MyISAM tables to INNODB. Running Ubuntu 14.04 Server with MySQL 5.6 ( apt-get upgraded from 5.5 ), 32G ram on 4 core/ 8 thread cpu. Initially had trouble with...

could not open single-table tablespace file .\mysql\innodb_index_stats.ibd after restart of MySQL

and

Error: Table "mysql"."innodb_table_stats" not found.

I followed Rolando's posted advice and the DB isn't crashing or creating a huge error.log anymore.
( the above issues are gone, just describing leading events )

Now I'm concerned about the following errors before I convert anything to INNODB. Federated can be ignored from what I read, the second line below ( also the subject of this post ) concerns me

2014-05-19 01:50:57 30950 [Note] Plugin 'FEDERATED' is disabled.
2014-05-19 01:50:57 30950 [ERROR] Function 'innodb' already exists
2014-05-19 01:50:57 30950 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2014-05-19 01:50:57 30950 [ERROR] Function 'federated' already exists
2014-05-19 01:50:57 30950 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2014-05-19 01:50:57 30950 [ERROR] Function 'blackhole' already exists
2014-05-19 01:50:57 30950 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2014-05-19 01:50:57 30950 [ERROR] Function 'archive' already exists
2014-05-19 01:50:57 30950 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.

added the following to my.cnf from Rolando's posted advice

innodb_buffer_pool_size=4G
innodb_buffer_pool_instances=2
innodb_read_io_threads=12
innodb_write_io_threads=12
innodb_io_capacity=300
innodb_log_file_size=128M

Wasn't sure if there should be anything else INNODB related. Based on what I could find also added

innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1

That didn't help. Not that I expected it to eliminate an "existing function"...lol

The plugins throwing errors in the error.log were not specifically specified in my.cnf. So I ran...

select * from mysql.plugin

for MySQL 5.6.17 I deleted the following rows in mysql.plugin

innodb ha_innodb.so
federated ha_federated.so
blackhole ha_blackhole.so
archive ha_archive.so

Do not delete anything you are not sure about. The above worked for me as...

I do not use Federated ( accesses data in tables of remote databases )
INNODB is built into 5.6, no plugin required
I do not use Blackhole ( accepts data but throws it away and does not store it )
I do not use Archive ( storing large amounts of data without indexes in a very small footprint )


PageIOLatch_Sh waits noticed after tuning an index Lately, a lot of queries to the database are timing out. While investigating I found that queries are in the suspended state with wait_type as PageIOLatch_Sh. I have not noticed so many queries being suspended before on the server. This has been happening for the since Saturday.

A couple of weeks ago, the cpu on the db server was at 100% constantly. While trying to fix that I changed a couple of indexes which were not really being used and were resulting in user_scans. After changing the indexes, the cpu usage on the server went down, the performance of the site was generally faster and looking at the index stats now a high number of user_seeks were happening, which I thought was a good sign.

However, now after a week or two, even though the cpu usage is not at 100, a large number of queries have started timing out and PageIoLatch_Sh waits are being observed. The interesting thing is that these timeouts were not happening at such a high frequency earlier even though the cpu was at 100%.

Now maintaining indexes is also something which slows down processing of sql server, so I was wondering whether the new indexes could have resulted in a larger number of these waits and timeouts?

From the background information you have provided it sounds like you have moved the contention point on your server from CPU to IO, as a result of implementing an indexing strategy targeted at specific troublesome queries.

For the most part (without getting into the specifics of what exactly your indexing strategy is) this is expected behaviour because with CPU no longer being a contention point, WAITS are naturally occurring elsewhere.

The performance issues you are seeing now are not necessarily caused by the index modifications you have applied. Further investigation into identifying the specific queries that are responsible for the IO waits/as well as the timeouts that you are seeing is required. You want to review the execution plans for each of these scenarios to identify possible areas for optimization (look for typically undesirable operations first such as implicit conversions, table scans etc.).

Not wanting to be presumptuous however I often encounter query timeouts on customer environments due to poor query plan choices as a result of parameter sniffing. This may also be something to look into.

For some handy queries to use during you investigations take a look at Glenn Berry's SQL Server Diagnostic Queries. There are a number of fantastic queries in there for looking at IO, for example which databases are responsible for the most IO, which database data files are seeing the most activity, which queries cause most reads/writes etc.


Protocol used by Oracle RMAN to communicate with auxiliary What protocol is used by RMAN to communicate with the server used for backing up/restoring data?

I searched everywhere but couldn't find any docs with this information.

Background: I am developing a proxy that backs up an oracle database to a different store

The thing you are looking for is called the SBT API. I can't find any free documentation on it, but the process is that a vendor of say a tape library implements the functions of SBT (e.g. sbtopen(), sbtwrite() and so on), compiles them as a shared library (a .so file) and this is placed in the ORACLE_HOME. When RMAN starts it looks for a file called libobk.so and dynamically links to it. RMAN then calls the functions, and in turn the protocol between the MML (media management layer) and the actual hardware e.g. a tape library, is proprietary.


Setup failed for Microsoft Management Studio I had a fault, the details of which I don't remember, on trying to start up SSMS. I thought I would just install it, but that turned into a real four day ordeal, with a different error on each install. Eventually I uninstalled all traces of all SQL servers from my machine (they're all just dev) and started afresh.

Everything went well, except installing SSMS. It says "A valid destination folder for the install could not be determined", and gives me a link to a huge log file, in which I can find nothing comprehensible about a folder not being determined. It ends with paths to two very similar, about as large log files.

Has anybody else experienced the same error, or could give me a clue what to look for in the log files, help will be gratefully accepted.

I ran into the exact situation where I uninstall various version of SQL and related tools and freshly reinstall 2016.

This resolve my problem: Setup failed when installing SQL Server Management Studio 2016. I do not understand why it works in my first installation but only after uninstalling and at my second reinstallation the registry points to (x86) instead - if someone can help explain this solution.


How to create a new DB with same data from another DB in oracle 10g I trying to copy entire data from old database to newly created database in oracle 10g.
please any one tell me the steps to do the process.

One way would be to user RMAN's Duplicate Database feature.


PostgreSQL pass table name and column name as arguments to function I have two almost similar tables: posts and comments.

Both of them has column id and votes.

Also i have two tables posts_votes and comments_votes that also has an almost similar structure: both of them has column value (can be -1 or +1), has user_id as FOREIGN KEY to user_account table, and field post_id or comment_id depending on which table it is, for posts_votes it will be post_id, and for comments_votes it will be comment_id.

What i need, is to write a trigger that will update votes counter for posts and comments table each time the row inserted/deleted/updated in posts_votes or comments_votes table.

It can be easily done by writing to different triggers, with difference only in table name for update query, and column name for WHERE statement.

This is example of such trigger for posts table (full DDL here):

CREATE OR REPLACE FUNCTION update_votes_counter() RETURNS TRIGGER AS
$$
  BEGIN;
    IF (TG_OP = 'INSERT') THEN
      UPDATE posts SET votes = votes + NEW.value WHERE id = NEW.post_id;
    ELSIF (TG_OP = 'DELETE') THEN
      UPDATE posts SET votes = votes - OLD.value WHERE id = OLD.post_id;
    ELSIF (TG_OP = 'UPDATE') THEN
      UPDATE posts SET votes = votes - OLD.value + NEW.VALUE WHERE id = NEW.post_id;
    END IF;
  END;
$$
LANGUAGE plpgsql;

What i want, is to write single function with arguments as table name and column with reference, so i can use this function in both cases for two tables.

It will be like this:

update_votes_counter('posts', 'post_id');
update_votes_counter('comments', 'comment_id');

and the function should build query like this:

UPDATE $table_name$ SET votes = votes + NEW.value WHERE id = NEW.$column_name$

Thanks for any advices!

Yo can create the functions dynamicaly to get a pair of static triggers doing this:

DO
$COMMAND$
declare
  v_sql text:=
$CREATES$
CREATE OR REPLACE FUNCTION update_votes_in_posts() RETURNS TRIGGER AS
$$
  BEGIN
    IF (TG_OP = 'INSERT') THEN
      UPDATE posts SET votes = votes + NEW.value WHERE id = NEW.post_id;
    ELSIF (TG_OP = 'DELETE') THEN
      UPDATE posts SET votes = votes - OLD.value WHERE id = OLD.post_id;
      return old;
    ELSIF (TG_OP = 'UPDATE') THEN
      UPDATE posts SET votes = votes - OLD.value + NEW.value WHERE id = NEW.post_id;
    END IF;
    return new;
  END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER update_votes_in_posts
  AFTER INSERT OR DELETE OR UPDATE ON posts_votes
  FOR EACH ROW
  EXECUTE PROCEDURE update_votes_in_posts();
$CREATES$;
begin
  execute v_sql;
  execute replace(v_sql, 'post', 'comment');
end;
$COMMAND$;

In one command (the DO) you set a variable (v_sql) that it contains the trigger for the posts table. You can execute the content of that variable directly. Then you can replace the inside the variable content all ocurrences of post (replacing with comment).

¡Voilá!

Look at a running complete example of this in: https://dbfiddle.uk/?rdbms=postgres_10&fiddle=f6c9931bc985f3ee7ebec7ee51c5017f


Determining if auto stats update is in progress In our production system, queries sometimes 'stall'. Whilst stalled, no incrementing resource use (CPU, Reads) is shown in sp_whoisactive, and there is no blocking.

In retrospective diagnosis, we can see that sys.dm_db_stats_properties shows last_updated around the time the query was 'stalled'.

What we would like to do is - when we see a stalled query - then determine what auto stats updates are in progress.

Because we want to do this ad-hoc, and also because we don't want to impact production performance, using profiler is probably not an option for us.

(If there is no way of doing an ad-hoc determination, then maybe we'll have to consider Extended Events, or some other lower-impact pre-emptive tracking).

Our version is 2014, however answers for later versions would also be useful.

SQL Server 2019 has introduced a wait stat to track this. On earlier versions, you're left to traces/XE. See my posts about the new wait stat:

SQL Server 2019: WAIT_ON_SYNC_STATISTICS_REFRESH
SQL Server 2019: WAIT_ON_SYNC_STATISTICS_REFRESH Redux

To use XE on prior versions, the event you want to look for is auto_stats. A bare minimum session to get you started would look like this:

CREATE EVENT SESSION auto_stats
    ON SERVER
    ADD EVENT sqlserver.auto_stats
    ADD TARGET package0.event_file
    ( SET filename = N'auto_stats' );

Though you'd probably want to configure it with some specificity to databases or tables that you care about, and I'd definitely want to filter on duration (WHERE [duration] > 1000000 would be one second) or something, because it'd be pretty noisy otherwise.


Verify SSAS Backup returning arguments instead of expected output Following the link http://geekswithblogs.net/darrengosbell/archive/2008/09/13/ssas-verifying-backups.aspx I tried to verify ssas backup and executed below mentioned command in specified path as in link.

msmdsrv -j -b "\\path\ssasfile.abf"

But all it outputs is the arguments I passed in:

 -j -b \\path\ssasfile.abf

However, I was expecting output as mentioned in the post How to check the health of a SSAS database? And of its backups?. Any suggestion is much appreciated.

That's what happens when you pass in a backup file that either doesn't exist or on which you don't have access.

For example if I run the command on an existing backup file I get this:

msmdsrv -j -b "c:\temp\AdventureWorksDW2012Multidimensional-EE.abf"

Outputs the arguments as well as the test results:

  -j -b c:\temp\AdventureWorksDW2012Multidimensional-EE.abf

  Backup file \?\c:\temp\AdventureWorksDW2012Multidimensional-EE.abf
  contains database with the Name of
  AdventureWorksDW2012Multidimensional-EE and Id of
  AdventureWorksDW2012Multidimensional-EE consisting of 7927 files with the
  total size of 5554858464 bytes
  All files were successfully tested

You can see the output starts with the arguments and then shows the test results.

If you point msmdsrv.exe to a non existing backup file it just returns the arguments and no results (unfortunately no error message either)

msmdsrv -j -b "c:\temp\NOTEXISTING.abf"

simply returns

  -j -b c:\temp\NOTEXISTING.abf


Restore Database SET SINGLE_USER I am in the process of writing a restore database script. I want to be able to connect as sa and run the restore script.

When I run SET SINGLE_USER does this mean that my login is the only user access to the database? Or how is this determined? I want to use this as I get an error saying other connections are active.

Below is my restore script to date. Are there any improvements I can make to this?

ALTER DATABASE AdventureWorks SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE AdventureWorks SET READ_ONLY;
GO

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH
MOVE N'AdventureWorks_dat' to N'H:\Data\AdventureWorks.mdf',
MOVE N'AdventureWorks_log' to N'E:\Logs\AdventureWorks.ldf',
STATS = 5;

GO

ALTER DATABASE AdventureWorks SET MULTI_USER;
GO

ALTER DATABASE AdventureWorks SET READ_WRITE WITH NO_WAIT;
GO

SET SINGLE_USER means only one user can connect to the database at a time. If someone or some service (SSIS, SSRS, SQL Agent, etc) connects before you than you will have to wait for them to disconnect


DSE Cassandra decimal type I generated solrschema with dsetool on a table name seriesdata (its key is composed by seriesmetadata_id, initialtime; two decimal field).
These fields are defined as DecimalStrField but they are numeric valued (I have to execute range selection). I tried to change schema defining these fields as:

<fieldType class="org.apache.solr.schema.TrieLongField" name="TrieLongField"/>

but I receive this error:

  [cassandra@bigdatalin-03 ~]$ dsetool reload_core timeseriesks.seriesdata schema=schema_data.xml solrconfig=solr_config_data.xml reindex=true -l cassandra -p cassandra
      org.apache.solr.client.solrj.impl.HttpSolrServer$RemoteSolrException: com.datastax.bdp.search.solr.CassandraIndexSchema$ValidationException: Mismatch between Solr key field [seriesmetadata_id] with type {TrieLongField{class=org.apache.solr.schema.TrieLongField,analyzer=org.apache.solr.schema.FieldType$DefaultAnalyzer,args={class=org.apache.solr.schema.TrieLongField}}] and Cassandra key alias [seriesmetadata_id] with type [decimal]
              at org.apache.solr.client.solrj.impl.HttpSolrServer.executeMethod(HttpSolrServer.java:665)
              at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:303)
              at org.apache.solr.client.solrj.impl.HttpSolrServer.request(HttpSolrServer.java:294)
              at com.datastax.bdp.tools.SearchDseToolCommands.createOrReloadCore(SearchDseToolCommands.java:559)
              at com.datastax.bdp.tools.SearchDseToolCommands.access$200(SearchDseToolCommands.java:59)
              at com.datastax.bdp.tools.SearchDseToolCommands$ReloadCore.execute(SearchDseToolCommands.java:209)
              at com.datastax.bdp.tools.DseTool.run(DseTool.java:126)
              at com.datastax.bdp.tools.DseTool.run(DseTool.java:51)
              at com.datastax.bdp.tools.DseTool.main(DseTool.java:186)

Why?
Thanks a lot!

Since you're using Datastax Enterprise search nodes, solr is integrated with cassandra. What has happened here is that when you changed your solr schema, you did not change the associated cassandra schema. The error message is telling you that there is a mismatch between your new solr schema and the cassandra schema for the same table. Check the cassandra schema for seriesdata and make it consistent with the solr schema.


SSRS 2016 report manager using m/d/y date parameter format in Chrome I've got a 2016 SSRS server, set up with UK regional settings.  When using Chrome, date parameter fields are using the US mm/dd/yyyy format instead of the expected UK dd/mm/yyyy:

Edge and IE are fine:

The date picker itself works fine, and the reports run normally in all browsers.  All reports are set to use en-GB language, and this happens on all client systems I've tried it on.  As far as I can tell all the server region and local settings are correct.

Any thoughts on how I can get SSRS to use the correct format in Chrome?

I'm not sure if this will work for you but Chrome uses it own language configuration.

Go to:

Settings
  Show Advanced Settings
    Languages
      Language and Input Settings

And then select the desired language. (English UK)


Execute pgScript in pgAdmin 4 while skipping errors In pgAdmin3, via the SQL Editor, there is an Execute pgScript button which (unlike Execute Query) would run through the entire script, skipping errors. The SQL editor in pgAdmin4 doesn't appear have this, is there an alternative that I haven't been able to find, or is there a parameter/command I can add to my script in order to skip errors at runtime?

In this instance the script is auto-generated and only renames tables/columns which may not exist so the error output is not required.

I can still run the script in pgAdmin 3 but connecting to a version 9 or above database with pgAdmin3 raises several warnings which don't seem to cause any problems but I'd rather find a solution than a workaround

Using: PostGreSQL 9.5, pgAdmin4 1.6

UPDATE: according to this:

    How do we execute pgScripts in pgAdmin4?

  You cannot I'm afraid. They've been more or less unsupported for years
  as they really became obsolete when Postgres introduced the DO command
  for executing anonymous blocks of code in v9.0.

...it looks like I might be able to use DO to force it to behave like pSQL?

On psql, it's the default, but you can (un)set it with \set ON_ERROR_STOP 0|1.

So I suggest trying \set ON_ERROR_STOP 0 at the beginning of the script.

From documentation:

  ON_ERROR_STOP

  By default, command processing continues after an error. When this variable is set to on, processing will instead stop immediately. In interactive mode, psql will return to the command prompt; otherwise, psql will exit, returning error code 3 to distinguish this case from fatal error conditions, which are reported using error code 1. In either case, any currently running scripts (the top-level script, if any, and any other scripts which it may have in invoked) will be terminated immediately. If the top-level command string contained multiple SQL commands, processing will stop with the current command.


Duplicate fails with no copy of datafile nnn found to restore I've duplicated our production to our UAT environment several times in the past but due to various reason, this time has been a serious PITA.
Oracle 11.2.0.3.
We have a recovery catalog setup.  This is the script thats run on the UAT box:

connect catalog rman/cat@rc
connect auxiliary /
run {
ALLOCATE AUXILIARY CHANNEL aux1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux2 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux3 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL aux4 DEVICE TYPE DISK;

duplicate database RACDB01 to GVPUAT skip tablespace
TS_ADMIN2,TS_ERROR_ERRORLOG,...
<big list of tablespaces I don't care about>...,RADSS_WG_INDEX_LARGE
NOFILENAMECHECK until time "to_date('2016-03-02 00:00:00','YYYY-MM-DD HH24:MI:SS')";

The problem is it errors with RMAN-06023: no backup or copy of datafile 352 found to restore.  This is because on 03/01/2016 I added a datafile to production.  A backup was started on production at 12pm, I added a datafile at 6 pm, and the backup finished at roughly 11pm.  How can I use that backup?

Try just backing up the tablespace for the new datafile. Then as long as you have all of the archive logs that were generated since the last backup, your clone should work.


Backups with Split Availability Groups I have been a DBA for about 2 years now, and there are some subtleties that I still do not understand about AlwaysOn availability groups.  First off, they have been nothing but trouble as far as I can tell, mainly because of a situation that occurs frequently in our environment.

We do Windows patches once per month, with the expected server reboots.  Roughly once per month, we encounter a situation where clusters with multiple availability groups divide themselves up between the nodes of a cluster.

If I am scheduling backup jobs, I usually administrate the jobs via multi-server administration from a master server.  I do this at the resolution of the listener, since it connects to the primary node of an availability group.  The problem is, if I specify "all databases," the entire node attempts to backup, no matter the primary/secondary status of a given availability group.

As a result, a lot of noise is generated in our monitoring solutions, as any cluster with diffuse availability groups returns a failed status for the backup, as the failure occurs on the attempt to take a normal backup (not copy-only) on a secondary AG.

Do I have to script jobs to run by availability group in these cases?

Is there a combination of settings that only backup an availability group which is primary for that node?

I have made the argument for consolidating the groups so that we can administrate by SERVER instead of by AG.  My bosses argue that we should be using AG's for load balancing across clusters so that we are not paying a SQL license for a node we only use for HADR.  I have put forth everything that can go wrong with these diffuse AG's, but maybe there is something I am not aware of.

For clarity, I get that we can point listeners to the different AG's and that they will connect to the primary of its respective node.  I just cannot seem to be able to manage any sort of generic backup plan that doesn't produce monitoring and write capability conflicts against these split AG situations.  Any clarity that anyone can offer would be greatly appreciated.

Availability Group aware backup scripts should exist on all nodes that can possibly host the primary copy of each database.  This allows the active node to perform the backup, avoiding error reports from nodes where the database is in secondary or read-only mode.

There are several excellent Availability Group-aware backup solutions, such as the ones from Ola Hallengren or Midnight DBA's MinionWare Backup


Cluster IP's under Access Point for Administering the Cluster When adding the cluster IPs under Access Point for Administering the Cluster, do we only include IPs for the public subnets, or do we also need to include an IP for the private subnet(s)?  This cluster will be used for a three-node multi-subnet AlwaysOn Availability Group.  As for the listener, I know it needs to include all of the public  subnets, but I'm not sure how the cluster core resources should be configured.

Please see No such thing as a Heartbeat Network and Everything You Know About Clustering is Wrong.  These don't really answer your question, but perhaps will change the question to, "Do we need a heartbeat network?"

With Windows 2008 and later, a heartbeat network is not required, and it's really only going to benefit if your "public" network gets so saturated that cluster communications are delayed, in which case the "heartbeat" network may stop a failover from occurring (ref. Shanky's comment below).  This was a nice safeguard that made more sense in the days of 10/100 Mbs networks, and although it's certainly possible to saturate a 10 Gps link, it's far less likely.  I.e., if you anticipate having major issues with your public network, then a private network might ease some of the pain, but you'll still need to address the public network issues to cure the disease.

Additionally, the use of file witness and majority node quorum features additionally reduces the need for a private network for cluster stability.  Many clusters for availability groups now span geographic regions where it doesn't make sense to bridge your network just for the purpose of putting the DR node on the same subnet.  Again--if it added a huge amount of value, it might make sense, but the benefit just isn't there.

The monitoring built-in to availability groups can initiate failover even if the cluster doesn't detect any problem, and I don't think it would care about a private network as it would only care about the route it is using for replication.  Hopefully someone can post a reference to how AGs might benefit, or not, from a private network in different scenarios, but I'm guessing that it doesn't exist because the private network has not been required for 10 years now.

If you think that the replication of the database will put significant load on the public network, you can use a private network for the replication by setting the endpoints to the private addresses.  See Configuring a Dedicated Network for SQL Server Always On Availability Groups Data Replication Traffic.  Yes, I know there is a link in the article to a previous article advocating a private network.  My point is that if your public network is so bad that you need a private network, you are probably going to have a lot of failovers and other issues.  It's like buying insurance for a leaky boat--great, you've got insurance, but the boat is still going to sink.


Optimize query that only returns when the number of rows to be returned is less than an amount I have a query that first counts the number of rows to be returned and, if it's below a limit, it returns those rows, else it returns nothing.

For context: I have a map that displays some points only after a certain zoom level. However, my users now want that the map displays the points as long as there is space in the screen, instead of having a predefined minimum zoom level. This means that now whenever they move across the map, I'll have two queries, instead of one: one to know how many items there are, and another one to actually return them.   The load will potentially double. I thought about doing it this way:

SELECT p.ACOLUMN, p.ANOTHERCOLUMN, p.LATITUDE, p.LONGITUDE
FROM table p
where p.LATITUDE < @latMax)
and p.LATITUDE > @latMin)
and p.LONGITUDE < @lngMax)
and p.LONGITUDE > @lngMin)
and (SELECT count(*)
FROM table
where LATITUDE < @latMax
and LATITUDE > @latMin
and LONGITUDE < @lngMax
and LONGITUDE > @lngMin)) < 200 -- example limit value

Looking at the actual execution plan, I see that SQL Server 2017 is seeking on the same index twice:

It's also of course reading the same amount of rows in both seeks. Is there a way to remove those duplicate seeks? Or is there maybe another, more optimal way to do what I'm trying?

I didn't really mean round trip all the way to the application. I consider a stored procedure one form of "application" in this context.

DECLARE @c int;

SELECT @c = COUNT(*) FROM dbo.table WHERE ...;

IF @c < 200
BEGIN
    SELECT cols FROM dbo.table WHERE ...;
END

If the application always expects either an empty or non-empty resultset, you can do this:

DECLARE @c int;

SELECT @c = COUNT(*) FROM dbo.table WHERE ...;

SELECT TOP (CASE WHEN @c <= 200 THEN @c ELSE 0 END) cols
  FROM dbo.table WHERE ...;

Yes, you have to read the table twice (in the case where there are fewer than 200 rows; TOP (0) should short-circuit), but I don't see a way to avoid that, unless you maybe dump rows into a temporary structure first (this will be useful I suppose in cases where your I/O is way faster than your network, but you'd still only be writing 201 rows at most, which is probably still way better than moving a lot more rows across the network):

SELECT TOP (201) cols INTO #f FROM dbo.table WHERE ...;

IF @@ROWCOUNT <= 200
BEGIN
  SELECT cols FROM #f;
END

DROP TABLE #f;

You could also try this, but I'm not sure the results will be any better than your original, it all depends if SQL Server sees that it doesn't have to materialize the CTE twice:

;WITH c AS
(
   SELECT cols, rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
   FROM dbo.table WHERE ...
)
SELECT cols FROM c WHERE NOT EXISTS (SELECT 1 FROM c WHERE rn > 200);


Deleting Billion records in a range vs exact ID lookup MYSQL I have a database table which is around 700GBs with 1 Billion rows, the data is approx 500 GB and index is 200GB,
I am trying to delete all the data that's before 2021,
Roughly around 298,970,576 rows in 2021 and remaining before this are 708,337,583 rows.
To delete this I am running a non-stop query in my python shell
DELETE from table_name where id < 1762163840 limit 1000000;
id -> 1762163840 represent data from 2021. Deleting 1Mil row taking almost 1200-1800sec.
Is there any way I can speed up this because the current way is running for more than 15 days and there is not much data delete so far and it's going to do more days.
I thought that if I make a table with just ids of all the records that I want to delete and then do an exact map like
DELETE from table_name where id in (select id from _tmp_table_name);
will that be fast? and will it going to be faster than first making a new table with all the records and then deleting it?
The database is setup on RDS and instance class is db.r3.large 2 vCPU and 15.25 GB RAM, only 4-5 connections running

I would use much smaller batches, and delete in specific ranges:
DELETE from table_name where id BETWEEN ? AND ?

Start with the first number equal to the minimum id of the table, and the second number 1000 higher than that. Doing batches of 1000000 is too large, it'll take too long and make the rollback segment grow too large.
Then as you loop, increase the start and end of the range by 1000, terminating when it gets to 1762163840.
It will take a long time to do this no matter what.
One alternative strategy some people use instead of deleting a great majority of data is to make a new table, copy only the small number of rows you want to keep, and then swap the tables. That will take less time, but it's complicated to manage concurrent updates. It's easier if your table is insert-only.


PostgreSQL's PGP_SYM_ENCRYPT log security In one of our project, I tried to use PostgreSQL's security extension pgcrypto. I found that is function, PGP_SYM_ENCRYPT is quite handy. Using this function enable me to query encrypted data with LIKE %keyword%, BETWEEN etc, which I can not achieve if I use programming language such as Java or PHP to encrypt the data then insert it into the database.

However, I found an issue. Once I execute queries with PGP_SYM_ENCRYPT new line in the log file is written. Interestingly, the log is including the password in PLAIN!

Of course, IMO, this is unacceptable. I want to secure my password, in the same time I also want to log any change (INSERT, UPDATE, DELETE) into my database.

Is there any way to hide this password information from the log file?

You can change your configuration settings to make this less likely to occur (What setting was triggering the log message in the first place?)

But in general, if you send your password to the database, then the database has your password. There is simply no way around this, other than not sending your password to the database and dealing with the consequences of that.  If you do send your password to the database, then you must guard the database log files with the same vigilance as you guard the rest of the database.


Performance of Non Clustered Indexes on Heaps vs Clustered Indexes This 2007 White Paper compares the performance for individual select/insert/delete/update and range select statements on a table organized as a clustered index vs that on a table organized as a heap with a non clustered index on the same key columns as the CI table.

Generally the clustered index option performed better in the tests as there is only one structure to maintain and because there is no need for bookmark lookups.

One potentially interesting case not covered by the paper would have been a comparison between a non clustered index on a heap vs a non clustered index on a clustered index. In that instance I would have expected the heap might even perform better as once at the NCI leaf level SQL Server has a RID to follow directly rather than needing to traverse the clustered index.

Is anyone aware of similar formal testing that has been carried out in this area and if so what were the results?

To check your request I created 2 tables following this scheme:

7.9 million records representing balance information.
an identity field counting from 1 to 7.9 million
a number field grouping the records in about 500k groups.

The first table called heap got a non clustered index on the field group. The second table called clust got a clustered index on the sequential field called key and a nonclustered index on the field group

The tests were run on an I5 M540 processor with 2 hyperthreaded cores, 4Gb memory and 64-bit windows 7.

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr  2 2010 15:48:46
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

  Update on 9 Mar 2011: I did a second more extensive benchmark by running the following .net code and logging Duration, CPU, Reads, Writes and RowCounts in Sql Server Profiler. (The CommandText used will be mentioned in the results.)

  NOTE: CPU and Duration are expressed in milliseconds

  1000 queries
  zero CPU queries are eliminated from the results
  0 rows affected are eliminated from the results

int[] idList = new int[] { 6816588, 7086702, 6498815 ... }; // 1000 values here.
using (var conn = new SqlConnection(@"Data Source=myserver;Initial Catalog=mydb;Integrated Security=SSPI;"))
            {
                conn.Open();
                using (var cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select * from heap where common_key between @id and @id+1000";
                    cmd.Parameters.Add("@id", SqlDbType.Int);
                    cmd.Prepare();
                    foreach (int id in idList)
                    {
                        cmd.Parameters[0].Value = id;

                        using (var reader = cmd.ExecuteReader())
                        {
                            int count = 0;
                            while (reader.Read())
                            {
                                count++;
                            }
                            Console.WriteLine(String.Format("key: {0} => {1} rows", id, count));
                        }
                    }
                }
            }

  End of Update on 9 Mar 2011.

SELECT performance

To check performanc numbers I performed the following queries once on the heap table and once on the clust table:

select * from heap/clust where group between 5678910 and 5679410
select * from heap/clust where group between 6234567 and 6234967
select * from heap/clust where group between 6455429 and 6455729
select * from heap/clust where group between 6655429 and 6655729
select * from heap/clust where group between 6955429 and 6955729
select * from heap/clust where group between 7195542 and 7155729

The results of this benchmark are for the heap:

rows  reads CPU   Elapsed
----- ----- ----- --------
1503  1510  31ms  309ms
401   405   15ms  283ms
2700  2709  0ms   472ms
0     3     0ms   30ms
2953  2962  32ms  257ms
0     0     0ms   0ms

  Update on 9 Mar 2011:
   cmd.CommandText = "select * from heap where group between @id and @id+1000";

  721 Rows have > 0 CPU and affect more than 0 rows

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1001      69788    6368         -
Cpu            15        374      37   0.00754
Reads        1069      91459    7682   1.20155
Writes          0          0       0   0.00000
Duration   0.3716   282.4850 10.3672   0.00180

  End of Update on 9 Mar 2011.

for the table clust the results are:

rows  reads CPU   Elapsed
----- ----- ----- --------
1503  4827  31ms  327ms
401   1241  0ms   242ms
2700  8372  0ms   410ms
0     3     0ms   0ms
2953  9060  47ms  213ms
0     0     0ms   0ms

  Update on 9 Mar 2011:
   cmd.CommandText = "select * from clust where group between @id and @id+1000";

  721 Rows have > 0 CPU and affect more than 0 rows

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1001      69788    6056         -
Cpu            15        468      38   0.00782
Reads        3194     227018   20457   3.37618
Writes          0          0       0       0.0
Duration   0.3949   159.6223 11.5699   0.00214

  End of Update on 9 Mar 2011.

SELECT WITH JOIN performance

cmd.CommandText = "select * from heap/clust h join keys k on h.group = k.group where h.group between @id and @id+1000";

The results of this benchmark are for the heap:

873 Rows have > 0 CPU and affect more than 0 rows

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1009       4170    1683         -
Cpu            15         47      18   0.01175
Reads        2145       5518    2867   1.79246
Writes          0          0       0   0.00000
Duration   0.8215   131.9583  1.9095   0.00123

The results of this benchmark are for the clust:

865 Rows have > 0 CPU and affect more than 0 rows

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1000       4143    1685         -
Cpu            15         47      18   0.01193
Reads        5320      18690    8237   4.97813
Writes          0          0       0   0.00000
Duration   0.9699    20.3217  1.7934   0.00109

UPDATE performance

The second batch of queries are update statements:

update heap/clust set amount = amount + 0 where group between 5678910 and 5679410
update heap/clust set amount = amount + 0 where group between 6234567 and 6234967
update heap/clust set amount = amount + 0 where group between 6455429 and 6455729
update heap/clust set amount = amount + 0 where group between 6655429 and 6655729
update heap/clust set amount = amount + 0 where group between 6955429 and 6955729
update heap/clust set amount = amount + 0 where group between 7195542 and 7155729

the results of this benchmark for the heap:

rows  reads CPU   Elapsed
----- ----- ----- --------
1503  3013  31ms  175ms
401   806   0ms   22ms
2700  5409  47ms  100ms
0     3     0ms   0ms
2953  5915  31ms  88ms
0     0     0ms   0ms

  Update on 9 Mar 2011:
   cmd.CommandText = "update heap set amount = amount + @id where group between @id and @id+1000";

  811 Rows have > 0 CPU and affect more than 0 rows

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1001      69788    5598       811
Cpu            15        873      56   0.01199
Reads        2080     167593   11809   2.11217
Writes          0       1687     121   0.02170
Duration   0.6705   514.5347 17.2041   0.00344

  End of Update on 9 Mar 2011.

the results of this benchmark for the clust:

rows  reads CPU   Elapsed
----- ----- ----- --------
1503  9126  16ms  35ms
401   2444  0ms   4ms
2700  16385 31ms  54ms
0     3     0ms   0ms
2953  17919 31ms  35ms
0     0     0ms   0ms

  Update on 9 Mar 2011:
   cmd.CommandText = "update clust set amount = amount + @id where group between @id and @id+1000";

  853 Rows have > 0 CPU and affect more than 0 rows

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1001      69788    5420         -
Cpu            15        594      50   0.01073
Reads        6226     432237   33597   6.20450
Writes          0       1730     110   0.01971
Duration   0.9134   193.7685  8.2919   0.00155

  End of Update on 9 Mar 2011.

DELETE benchmarks

the third batch of queries I ran are delete statements

delete heap/clust where group between 5678910 and 5679410
delete heap/clust where group between 6234567 and 6234967
delete heap/clust where group between 6455429 and 6455729
delete heap/clust where group between 6655429 and 6655729
delete heap/clust where group between 6955429 and 6955729
delete heap/clust where group between 7195542 and 7155729

The result of this benchmark for the heap:

rows  reads CPU   Elapsed
----- ----- ----- --------
1503  10630 62ms  179ms
401   2838  0ms   26ms
2700  19077 47ms  87ms
0     4     0ms   0ms
2953  20865 62ms  196ms
0     4     0ms   9ms

  Update on 9 Mar 2011:
   cmd.CommandText = "delete heap where group between @id and @id+1000";

  724 Rows have > 0 CPU and affect more than 0 rows

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts     192      69788    4781         -
Cpu            15        499      45   0.01247
Reads         841     307958   20987   4.37880
Writes          2       1819     127   0.02648
Duration   0.3775  1534.3383 17.2412   0.00349

  End of Update on 9 Mar 2011.

the result of this benchmark for the clust:

rows  reads CPU   Elapsed
----- ----- ----- --------
1503  9228  16ms  55ms
401   3681  0ms   50ms
2700  24644 46ms  79ms
0     3     0ms   0ms
2953  26955 47ms  92ms
0     3     0ms   0ms

  Update on 9 Mar 2011:

  cmd.CommandText = "delete clust where group between @id and @id+1000";

  751 Rows have > 0 CPU and affect more than 0 rows

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts     144      69788    4648         -
Cpu            15        764      56   0.01538
Reads         989     458467   30207   6.48490
Writes          2       1830     127   0.02694
Duration   0.2938  2512.1968 24.3714   0.00555

  End of Update on 9 Mar 2011.

INSERT benchmarks

The last part of the benchmark is the execution of insert statements.

insert into heap/clust (...)
 values (...),
        (...),
        (...),
        (...),
        (...),
        (...)

The result of this benchmark for the heap:

rows  reads CPU   Elapsed
----- ----- ----- --------
6     38    0ms   31ms

  Update on 9 Mar 2011:

string str = @"insert into heap (group, currency, year, period, domain_id, mtdAmount, mtdAmount, ytdAmount, amount, ytd_restated, restated, auditDate, auditUser)
                    values";

                    for (int x = 0; x < 999; x++)
                    {
                        str += string.Format(@"(@id + {0}, 'EUR', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test'),  ", x);
                    }
                    str += string.Format(@"(@id, 'CAD', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test') ", 1000);

                    cmd.CommandText = str;

  912 statements have > 0 CPU

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1000       1000    1000         -
Cpu            15       2138      25   0.02500
Reads        5212       7069    6328   6.32837
Writes         16         34      22   0.02222
Duration   1.6336   293.2132  4.4009   0.00440

  End of Update on 9 Mar 2011.

The result of this benchmark for the clust:

rows  reads CPU   Elapsed
----- ----- ----- --------
6     50    0ms   18ms

  Update on 9 Mar 2011:

string str = @"insert into clust (group, currency, year, period, domain_id, mtdAmount, mtdAmount, ytdAmount, amount, ytd_restated, restated, auditDate, auditUser)
                    values";

                    for (int x = 0; x < 999; x++)
                    {
                        str += string.Format(@"(@id + {0}, 'EUR', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test'),  ", x);
                    }
                    str += string.Format(@"(@id, 'CAD', 2012, 2, 0, 100, 100, 1000 + @id,1000, 1000,1000, current_timestamp, 'test') ", 1000);

                    cmd.CommandText = str;

  946 statements have > 0 CPU

Counter   Minimum    Maximum Average  Weighted
--------- ------- ---------- ------- ---------
RowCounts    1000       1000    1000         -
Cpu            15       2403      21   0.02157
Reads        6810       8997    8412   8.41223
Writes         16         25      19   0.01942
Duration   1.5375   268.2571  6.1463   0.00614

  End of Update on 9 Mar 2011.

Conclusions

Although there are more logical reads going on when accessing the table with the clustered & the nonclustered index (while using the nonclustered index) the performance results are:

SELECT statements are comparable
UPDATE statements are faster with a clustered index in place
DELETE statements are faster with a clustered index in place
INSERT statements are faster with a clustered index in place

Of course my benchmark was very limited on a specific kind of table and with a very limited set of queries, but I think that based on this information we can already start saying that it is virtually always better to create a clustered index on your table.

  Update on 9 Mar 2011:

As we can see from the added results, the conclusions on the limited tests were not correct in every case.

The results now indicate that the only statements which benefit from the clustered index are the update statements. The other statements are about 30% slower on the table with clustered index.

Some additional charts where I plotted the weighted duration per query for heap vs clust.

As you can see the performance profile for the insert statements is quite interesting. The spikes are caused by a few data points which take a lot longer to complete.

  End of Update on 9 Mar 2011.


How would you make this function work on any table? I'm trying to break all the rules of databasing using the stuff function. I want to smush every applicable row into just one, for science, you know?

Problem is, I have to write a new function every time. They look like this:

    ALTER function [dbo].[stuffMeds](@VisitID varchar(55))
    returns varchar(max)

    as
    begin
    declare @string varchar(max)

    set @string = (select stuff( (
    select  distinct top 500 ', ' + DataItemID  from
    EmrAcctPha_Medication med
    where
    VisitID = @VisitID
    order by ', ' +  DataItemID desc
    for xml path ('')),1,2,'')
     )

    return @string

    end

used in a query like this:

    select AdmitDateTime, RoomID, dbo.stuffMeds(VisitID)
    from AbstractData

It turns a list like this:

Albuterol Sulfate
Amlodipine Besylate
Aspirin
Benztropine Mesylate
Bisacodyl
Ciprofloxacin
Collagenase
Divalproex Sodium
...

into a string like this:

Divalproex Sodium, Collagenase, Ciprofloxacin, Bisacodyl, Benztropine Mesylate, Aspirin, Amlodipine Besylate, Albuterol Sulfate

I can write it as a dynamic stored procedure, but you can't call sp_executesql from a function (or at least, I don't know how).

Question How would you write this function in a way that it could be used on any table?

In another answer, @SQLRaptor makes the suggestion of using STRING_AGG. Working with that, it seems like the following should do the trick:

SELECT  AdmitDateTime, RoomID, med.stuffMeds
FROM    AbstractData AD
OUTER APPLY (
    SELECT  STRING_AGG(DataItemID, ', ') WITHIN GROUP (ORDER BY DataItemID ASC) stuffMeds
    FROM    EmrAcctPha_Medication med
    WHERE   AD.VisitID = med.VisitID
) med


Sql Server merge replication primary key violations Using Sql Server 2008.  Pretend I have a publisher and 2 subscribers A and B.  The replicated table has a guid on it and also a primary key that is not the guid.  Frequently, updates happen at both subscribers near simultaneously generating 2 different guids but the same primary key.  Then when they go to syncronize I get primary key violation errors.

Thinking of ways to resolve this - having the guid be generated based on the primary key or having replication use the primary key instead of guid (that seems hard/silly) or as a last resort have replication just continue on and ignore those violations.

Which route should I take?

I'd change the IDENTITY so one is odd, one even. Or negative/positive.

Probably the simplest way to do it...

Edit: I assumed identity PK columns.

I'd suggest you update the question with the relevant info contained in the comment. It's incomplete otherwise.

You said "The desired behaviour is that after the merge all rows are identical. The first one to the publisher wins..."

This sounds like conflict resolution, which is how you'd deal with PK collisions.


How to design mongodb collection? We have a service will collect data and call api to save to a mongodb's collection name alldata

And the problem is ,it keeps write data frequently,then the mongo will shutdown (I found on internet said the collection limit is 16mb)

So we have to delete the old data by hour
But I want to keep the old data at least one week
How to design this ??

should we use many collections to save??  If we use many collections,how to query many of them??
Or should we back up ?

I heard that mongodb is appropriate to bigdata,but I don't know how people usually use it ?
Please give me some advice    Thank you.

The limit is not on the collection size, but on the document size. There are ways that you can store the document of a higher size.

Having very large Documents will surely affect the performance of the MongoDB. You can surely split these documents into multiple documents in the same Collection.

For example,
You can divide the Documents to be stored Date wise, I mean, if you were writing all the data related to some X constraint into a document, then split it into X Constraint, Date 1 as one document, X Constraint, Date 2 into a different document. You can split in any logical way you feel is right for your Database design.

Or if you Document is very granular in nature and can't be split, then you have 3 options
1. Use Mongo's GRID FS
2. a relational approach to store the part of the data in a different Collection
3. Depending on the number of reads, number of writes and the size of the Document, choose the best possible NoSql Solution.

If you need a more specific answer, please update the question with relevant details.


Simultaneous money withdrawls from a distributed/replicated database Lets say I have $10 in my bank account. The database is replicated in North America and Asia. At the exact same time I do a withdrawl of $10 in N. America and in Asia, meaning that my withdraw would do work on two different copies of the data. How can I be prevented from ending up with a balance of -$10 and $20 in my pocket?

Is it possible to produce the proper result with NoSQL?

(I'm not a database expert, just curious)

Thanks!

Take a look at the views of Micheal Stonebraker here - pages 21 and 22. Stonebraker is both an academic and a serious commercial player in the database world (Postgres, Vertica, Informix, VoltDB inter alia). For your particular question, note his views on banking.

Stonebraker is an advocate of "NewSQL".

NewSQL is a two-pronged approach which addresses both fundamental facets of database processing: OLTP and OLAP.

For OLTP (banking, shopping) he proposes in memory, sharded, shared nothing architectures with none of the transactional/buffering overhead of "OldSQL" (that's Oracle, SQL Server, MySQL... &c. to you and me). His offering in this area is VoltDB. Oracle and Microsoft are playing catch-up (with In-Memory and Hekaton)
For OLAP (DW) applications he is a fan of column oriented stores - i.e. Vertica (a commerical success he sold to HP).

He says two main things:

that ACID is essential for a database system (see the .pdf), and
that there's nothing wrong with SQL - high level langauages are good and that NoSQL is basically really a great leap 30 years backwards.

He makes the excellent point that back in the 1970's, there was a debate in the CS/IT world about C versus assembler. Some thought that hand-crafted assembler (think Map-Reduce programmes) would be more efficient than a C compiler's output (think database query optimiser).

As you may have guessed, I'm a big Stonebraker fan.


get result of search in database postgresql and work with it every time I have a query who spend alot of time because there is thousand of ligne in the database, so I need if it's exist a method to put the result of the query some where and work with them the time i want. thanks

From PostgreSQL's point of view you can use Materialized Views or Unlogged Tables

For other solutions you can check different caching mechanisms


Is it possible to output multidimensional data from linear table without iteration? I have the following table on a cycling club's website:

CREATE TABLE `mileage` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `rideID` mediumint(9) NOT NULL,
 `rideDate` date NOT NULL,
 `isWeekly` tinyint(1) NOT NULL,
 `riderUid` smallint(6) NOT NULL,
 `leaderUid` smallint(6) NOT NULL,
 `mileage` smallint(6) NOT NULL,
 `days` smallint(6) NOT NULL DEFAULT '1',
 `eBike` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8081 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Each entry represents one rider/trip, where the rider is the person who rode and the trip is the rider's part of a club ride.

When the site was built, the club wanted the ability to get mileage between dates (defaulting to current month or YTD).

The following query works well for that application (variables are from PHP):

SELECT m.riderUid, w.firstName, w.lastName, w.isMale,
SUM(mileage) as miles,
COUNT(*) as cnt
FROM `mileage` m
    LEFT JOIN `members` w ON (w.uid = m.riderUid)
WHERE (m.rideDate >= '$startDate' AND m.rideDate < '$endDate')
GROUP BY m.riderUid
ORDER BY miles DESC

Now they've requested to get the data month-by-month for any given pair of dates. Meaning they want to see columns output for, say, January, February, March ... etc.

I suppose I can do this iteratively, using PHP to feed successive monthly start and end dates and then glomming them all together on the page, but that feels kind of clunky. I wonder if there's an easier way using a MySQL query.

So, is this kind of thing possible using a query or do I have to brute-force it using PHP or JavaScript? I should add that I'm not a database guru by any stretch, just a journeyman coder.

I think you just need include the month in the GROUP BY:

SELECT m.riderUid, w.firstName, w.lastName, w.isMale,
  MONTH(m.rideDate) as monthNumber,
  SUM(mileage) as miles,
  COUNT(*) as cnt
FROM `mileage` m
    INNER JOIN `members` w ON (w.uid = m.riderUid)
WHERE (m.rideDate >= '$startDate' AND m.rideDate < '$endDate')
GROUP BY m.riderUid, w.firstName, w.lastName, w.isMale, MONTH(m.rideDate)
ORDER BY MONTH(m.rideDate), m.riderUid

If the date range is longer than a year, then months from different years will be folded together, so replace MONTH(m.rideDate) with LEFT(m.rideDate, 7) as suggested by @RickJames.

If you want the months as columns, then you need to "pivot" the months, see e.g. this answer on Stackoverflow. So that might give you a query with a subquery similar to the above, something like:

SELECT riderUID, firstName, lastName, isMale,
CASE WHEN monthNumber = 1 THEN miles END AS JanuaryMiles,
CASE WHEN monthNumber = 1 THEN cnt END AS JanuaryCnt,
CASE WHEN monthNumber = 2 THEN miles END AS FebruaryMiles,
CASE WHEN monthNumber = 2 THEN cnt END AS FebruaryCnt,
CASE WHEN monthNumber = 3 THEN miles END AS MarchMiles,
CASE WHEN monthNumber = 3 THEN cnt END AS MarchCnt,
CASE WHEN monthNumber = 4 THEN miles END AS AprilMiles,
CASE WHEN monthNumber = 4 THEN cnt END AS AprilCnt,
CASE WHEN monthNumber = 5 THEN miles END AS MayMiles,
CASE WHEN monthNumber = 5 THEN cnt END AS MayCnt,
CASE WHEN monthNumber = 6 THEN miles END AS JuneMiles,
CASE WHEN monthNumber = 6 THEN cnt END AS JuneCnt,
CASE WHEN monthNumber = 7 THEN miles END AS JulyMiles,
CASE WHEN monthNumber = 7 THEN cnt END AS JulyCnt,
CASE WHEN monthNumber = 8 THEN miles END AS AugustMiles,
CASE WHEN monthNumber = 8 THEN cnt END AS AugustCnt,
CASE WHEN monthNumber = 9 THEN miles END AS SeptemberMiles,
CASE WHEN monthNumber = 9 THEN cnt END AS SeptemberCnt,
CASE WHEN monthNumber = 10 THEN miles END AS OctoberMiles,
CASE WHEN monthNumber = 10 THEN cnt END AS OctoberCnt,
CASE WHEN monthNumber = 11 THEN miles END NovemberMiles,
CASE WHEN monthNumber = 11 THEN cnt END NovemberCnt,
CASE WHEN monthNumber = 12 THEN miles END AS DecemberMiles,
CASE WHEN monthNumber = 12 THEN cnt END AS DecemberCnt
FROM (
    SELECT m.riderUid, w.firstName, w.lastName, w.isMale,
      MONTH(m.rideDate) as monthNumber,
      SUM(mileage) as miles,
      COUNT(*) as cnt
    FROM `mileage` m
      INNER JOIN `members` w ON (w.uid = m.riderUid)
    WHERE (m.rideDate >= '$startDate' AND m.rideDate < '$endDate')
    GROUP BY m.riderUid, w.firstName, w.lastName, w.isMale, MONTH(m.rideDate)
) q1
ORDER BY riderUid

See here for db-fiddle example. Note: If running this query through phpMyAdmin, you may have to embrace each instance of CASE WHEN ... END in parentheses in order to workaround a bug in phpMyAdmin.

(I suspect the above can also be re-written more elegantly without the subquey using window functions, though that requires MySQL 8.0+ or MariaDB 10.2+.)

A couple of other tips:

Include all the fields you SELECT in your GROUP BY - this is required by default in newer MySQL versions.
Also, surely you can use INNER JOIN instead of OUTER JOIN in this case? Or do you have mileage for members who are no longer in the members table? If so, you may want to keep the LEFT JOIN.
Also, InnoDB is a better choice than MyISAM in most use-cases now, and MyISAM is discouraged in MySQL 8.0+.


Normalised database with one-to-one relationship Is it bad practice to use a 1-1 relationship between two logically distinct tables: the user details and the user configuration settings? Each configuration record would have a single unique user ID, but they seemed separate enough to warrant two tables rather than one big one. I ask because the WikiBook for my school course said that I should only use 1-Many relationships.

In the User table I will be storing details such as their username, id and authorisation token for an external system. In the Config table I will be storing settings such as whether to show a welcome message, which are changeable through the settings page.

The attributes should be split in 2 tables because the configuration details are not depending on the user but on the configuration of an external system. If a user can have only 1 configuration then you must use an 1-1 relation. If a user can have multiple configurations then you must use the 1-M relation.

This is the theory. If you want (in case of a 1-1 relation) put all in 1 table than this is also 'allowed'. Remember well that if one day the relation becomes 1-M that you are in problems. This is also why it is preferred to split the information into 2 tables.


Merging two tables by minimal interval values I have two tables with two columns keeping tabs of categorical values, such as:

Table 1
+----+-------+-----+-----------+
| ID | Begin | End | Condition |
+----+-------+-----+-----------+
|  1 |     1 |   8 |    Normal |
|  2 |     8 |  23 |  Critical |
|  3 |    23 |  30 |    Normal |
+----+-------+-----+-----------+

Table 2
+----+-------+-----+------------+
| ID | Begin | End | Supervisor |
+----+-------+-----+------------+
|  1 |     1 |  14 |       John |
|  2 |    14 |  30 |     Janice |
+----+-------+-----+------------+

These Begin and End columns represent a continuous interval in which the value is valid. In the above example, the interval would be days in a month, so according to Table 1 I'd have a normal condition from day 1 to day 8, critical from day 8 to day 23, and normal again from the 23rd to the 30th. And I know from Table 2 that John was supervising from the 1st to the 14th, and Janice took over from the 14th to the 30th.

What I want is to merge these two tables, to have both Condition and Supervisor values in the same table, with the minimal interval for each pairing of values. So, this:

Merged Table
+----+-------+-----+-----------+------------+
| ID | Begin | End | Condition | Supervisor |
+----+-------+-----+-----------+------------+
|  1 |     1 |   8 |    Normal |       John |
|  2 |     8 |  14 |  Critical |       John |
|  3 |    14 |  23 |  Critical |     Janice |
|  4 |    23 |  30 |    Normal |     Janice |
+----+-------+-----+-----------+------------+

What can be guaranteed of the tables is that:

each will have its respective "Begin" and "End" fields.
those will have the same span (the "Begin" value of the first row and the "End" value of the last row are the same for every table).
that every interval is sequential (the "End" value of row n is always equal to the "Begin" value of row n+1).
that the value of "End" will always be higher than "Begin" for a given row.

I had this worked out programatically in python, but I'm trying to scrap that script from my workflow and do everything directly in the DB. Ultimately I could replicate my python function in plpgsql, but I wonder if there is a more SQL-esque way of achieving this?

I'd approach this in three logical stages:

Expand the rows in both tables for all the days in the month and join them together.
Assign a 'group' to each series of rows where condition and supervisor don't change for a period (a "gaps and islands" problem).
Group the results.

So the solution looks like this:

create table t1(
  id serial primary key
, begin_on integer
, end_on integer
, condition text
);

insert into t1(begin_on,end_on,condition)
values (1,8,'Normal')
     , (8,23,'Critical')
     , (23,30,'Normal');

create table t2(
  id serial primary key
, begin_on integer
, end_on integer
, supervisor text
);

insert into t2(begin_on,end_on,supervisor)
values (1,14,'John')
     , (14,30,'Janice');

select min(g) begin_on, max(g)+1 end_on, condition, supervisor
from( select g
           , condition
           , supervisor
           , row_number() over (order by g)
             - row_number() over (partition by t1.id, t2.id order by g) grp
      from generate_series(1,30) g
           join t1 on g>=t1.begin_on and g<t1.end_on
           join t2 on g>=t2.begin_on and g<t2.end_on ) z
group by condition, supervisor, grp
order by begin_on;

begin_on | end_on | condition | supervisor
-------: | -----: | :-------- | :---------
       1 |      8 | Normal    | John
       8 |     14 | Critical  | John
      14 |     23 | Critical  | Janice
      23 |     30 | Normal    | Janice

dbfiddle here

I am assuming this is a cut-down example of your real-world problem or I would also suggest changing the way you store the data in the first place, perhaps using date ranges instead of integers.


At which point in a log backup does SQL Server truncate the log file? I am investigating an issue where on a somewhat predictable schedule where our third party backup software begins a full backup of some of our databases during the workday. It starts a log backup, detects a broken chain in the LSN's and then converts it to a full backup.

I checked and there are no other log backups being run against the SQL servers we are experiencing this with.

What happens is that a log backup begins on schedule, but on some days the connection with the backup server is lost during the backup. The job sits in limbo and when it restarts with the same job id there is a broken chain detected and it begins a full backup of affected databases.

Scouring the logs it I can see the backup failures and the backup restarting with the same LSN which is no longer there.

So my question is, when there is a log backup with the truncate option does SQL Server truncate the log as it is backed up, or at the successful completion of the job? In my case it seems like a range of LSN's is being backed up, marked as backed up and when the job fails it looks for those same LSN's again when the job restarts.

Technically, the virtual log files (VLFs) are only attempted to be marked as inactive (can be reused) when they have been successfully backed up and are no longer needed by internal (and sometimes external) processes - such as replication or availability groups.

  What happens is that a log backup begins on schedule, but on some days the connection with the backup server is lost during the backup.

This shouldn't cause any issues with SQL Server. SQL Server will see that the connection was broken and kill the session plus whatever was executing. This means that backup wasn't successful so the next log backup should start at the same place because it hasn't yet finished successfully.

  The job sits in limbo and when it restarts with the same job id there is a broken chain detected and it begins a full backup of affected databases.

Sounds like the application thinks it ran a backup successfully even though it didn't and "detects" there is a problem... but there isn't. It really sounds like the application logic in the backup program is either flawed or running into a config issue... or it might be their standard logic (by design).

Now if it takes a full backup and walks away... that's not going to affect the log reuse.

  But on a certain day of the week, at a certain time of day half of the jobs hang because the backup server goes down for a few minutes and when they restart again they begin a full backup on some databases. And from what I can tell it looks for the LSN, runs a backup, job fails, restarts looking for the same LSN.

Sounds like you have other infrastructure issues that may be contributing. Assuming they are not, though, it sounds like the backup application is confused. If the job fails to backup the log successfully it should start at the same place because it hasn't yet been successfully backed up. If the backup application chooses to see that as an issue and take a full backup to reset itself (the backup application) internally, that's on the application vendor to fix/decide/working as intended/whatever after you tell them about it.

However, since this only happens when the backup application server goes down... you may want to also have a stern talking to your infrastructure team and get that issue situated - or at the very least, if it's downtime because of patching or something, that all jobs be held until the patching (or whatever) is completed.


Prioritizing values in select statement in ORACLE Due to the JOIN for the communication method (Phone, email etc) we get alot of duplicates because a company can have multiple communication methods (CICM.CLIENT_VALUE).

My question is...
Is there a way to incorporate in the select statement to where if out of the client_Value field, if the value = EMAIL & Phone then only pull EMAIL for that CUSTOMER_ID, if they dont have have email and only phone, then pull phone

Right now I can have CUSTOMER_ID = 1 and have 2 records with the same data but the client_value will be different because they have an email, and phone, so I am getting 2 returned records instead of prioritizing 1 over the other.

Here is an image to show what I am referencing...you can see all the blacked out fields have the same value on both records but the contact_type and value are different. I would like to prioritize and choose PHONE over EMAIL if available, and not get BOTH to reduce record return count...

If this needs clarification please advise.

SELECT DISTINCT INV.CUSTOMER_ID
     , INV.ADDRESS_ID
     , INV.NAME
     , INV.ADDRESS1
     , INV.ADDRESS2
     , INV.CITY
     , INV.STATE
     , INV.ZIP_CODE
     , INV.COUNTRY
     , CICM.CLIENT_VALUE as CONTACT_TYPE
     , CICM.VALUE
     , CICM.DESCRIPTION
     , INV.REGION_CODE
     , INV.SITE
     , LD.LSTINVDATE
     , CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-13') AND TO_DATE('30-Sep-15') THEN SUM(INV.SALES) ELSE 0 END AS TWO_YEARS
     , CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-15') AND SYSDATE THEN SUM(INV.SALES) ELSE 0 END AS SALESPROGRESS
     , SUM(INV.SALES) AS TOTALSALES
FROM IFSINFO.HB_INVOICING_ALL INV
LEFT JOIN IFSINFO.HB_LAST_INVOICE_DATE LD
    ON (INV.COMPANY = LD.COMPANY)
    AND (INV.CUSTOMER_ID = LD.IDENTITY)
LEFT JOIN IFSAPP.CUSTOMER_INFO_COMM_METHOD CICM
    ON (INV.ADDRESS_ID = CICM.ADDRESS_ID)
    AND (INV.CUSTOMER_ID = CICM.CUSTOMER_ID)
WHERE INV.CUST_GRP = '4'
AND INV.COMPANY = '20'
AND INV.STATE = '&State'
GROUP BY INV.COMPANY
       , INV.CUSTOMER_ID
       , INV.ADDRESS_ID
       , INV.NAME
       , INV.ADDRESS1
       , INV.ADDRESS2
       , INV.CITY
       , INV.STATE
       , INV.ZIP_CODE
       , INV.COUNTRY
       , CICM.CLIENT_VALUE
       , CICM.VALUE
       , CICM.DESCRIPTION
       , INV.REGION_CODE
       , INV.SITE
       , LD.LSTINVDATE
       , INV.INVDATE

HAVING SUM(CASE WHEN INV.INVDATE >= TO_DATE('01-Oct-13')
                 AND INV.INVDATE < TO_DATE('30-Sep-15')
             THEN INV.SALES ELSE 0
           END) = 0

If I have well understood your question, you should use the DECODE function in your query in the SELECT and GROUP BY sections.

NB: for performance purpose, you might create an index using that function.


Which RDBMS best fits my need? I realise this may be come a subjective mess, but I'm hoping for some objective information on the subject. I also don't want to go shopping, I just want to know 'what to shop for'.

I'm very new to database management. As in I started SQL 4 weeks ago, and using MS Access last week. I'm coming to the end of out perrenial database project using MS Access wherein I've hit up against a lot of the limiations to Access, and I've mentioned that we need something different.

Next time round I'd like full access to the 'database capabilities' such as Union Corresponding, Intersect etc. I'd also like to be able to cursor through the rows if need be and be able to run checks row by row.

It's likely we'll have a handful of tables, but a few hundred thousand entries in the biggest table. I also need to be able to report the data. I also need to be able to import from Agresso.

One did catch my eye, which was the JPQL, in fact I could imagine the example on wikipedia fitting my needs, and I am able to code in Java. But I don't know if it fits the rest of my needs or if the project was handed over to someone not proficient in Java if it would be easily transferred. A GUI is essential.

I'm a little lost having never seen or even heard of RDBMS until last week, and now I need to decide what would work best going forward.

What RDBMS fits my needs to closest? How do I know what to pick?

My recommendation is to start out with Postgres.  The advantage is that it has a full feature set and it teaches you good habits from the start.  Once you are familiar with it, it is easier to move to up to the Oracle/DB2 approach or down to the MySQL approach easily enough.  On the other hand it is an advanced database and you can take it extremely far if you want.  And it is open source to boot.


Are there any MySQL Benchmarking tools? I've heard a long time ago that there is this tool that helps you tweek mysql settings for better performance but i can't seam to find it. I am aware that I can use ab for apache to simulate high traffic and it will generate me a slow log. However, if it crashes (already happened and it was in production mode), I don't know why it crashed and if it can be tweeked from the config.

Benchmarking or Tuning tool?

IMHO, there's no tool that will be specific to the latter unless you have a super generic usage. You need to identify your usage pattern and tune your database hosts to accommodate. If you're write-heavy, you will have a different configuration than a read-heavy scenario. Bottom line, your tuning follows your applications usage.

For benchmarking, I use Sysbench. Here's an example from my blog.

Added:
Here's my beef with config tools: With the changes in versions 4.x vs 5.x vs 5.5.x there are a lot of tweaks that don't jive between them. So you really need a savvy DBA to evaluate what's going on for each host. Load, storage, traffic, application specific requirements, there's a lot that can go into an optimal configuration. A tool may get you part of the way, but may leave out something or include something that may cause a failure. From buffers to flushing to plugins to threading, a config tool may provide you with false confidence that you are implementing the correct config.


Query to find optimal filegroups for objects I have been assigned a task to create four file groups (one per volume) locate tables so that they are located in a manner that during an ETL process no filegroup is being read and written to at the same time.

I have stepped through all the packages, data flow tasks and procedures and views and noted which tables are being read from and to and in what sequence.

I now need write a query to determine the optimal positioning of the tables.

In terms of logic I have the following, broken it down into the following rules.

For each sequence source table and destination table can not be on the same volume.
Tables can not exist on more than one volume.

I am interested to hear peoples opinion on whether this process is worthwhile but regardless I would appreciate some help resolving the query.

Below you can find the scripts to create the table and insert some test data.

Thanks in advance for any suggestions.

-- Create table
CREATE TABLE [dbo].[TestData]
(
    [TestDataID] [smallint] IDENTITY(1,1) NOT NULL,
    [Src] [nvarchar](50) NOT NULL,
    [Dest] [nvarchar](50) NOT NULL,
    [Seq] [int] NOT NULL,
CONSTRAINT PK_TestData PRIMARY KEY CLUSTERED (TestDataID)
)
GO

-- Insert data
INSERT INTO TestData(Src, Dest, seq)
 SELECT 'A', 'B', 1
 UNION ALL
 SELECT 'A', 'C', 1
 UNION ALL
 SELECT 'C', 'D', 2
 UNION ALL
 SELECT 'A', 'D', 3
 UNION ALL
 SELECT 'B', 'D', 3
GO

Bad news. You said ETL process - that means heavy data loads, and inserts/updates/deletes have a single disk bottleneck: the transaction log file. You can add multiple transaction log files, but they're used sequentially, not load-balanced (except in some really edge-case scenarios.)

Instead, what you need is multiple databases. In data warehouses, you typically use a different source database than a destination database, like a staging database. That way, you can put its databases on different drives (and even different classes of drives). For example, some staging databases live on cheap local SSDs.

If you do find yourself storage-bottlenecked on data file reads or writes, you're better off solving that problem by adding memory (to cache the data and avoid reads altogether) or by striping the data across more drives, not less. By splitting the data files into smaller pieces, and giving them each fewer drives, you're usually making the problem worse.


SQL*Plus save current COLUMN settings In SQL*Plus, is there a way to save current COLUMN settings similar to STORE which saves SET commands in a script?

COLUMN can be used to list the settings, but they are not in script format.

You can edit $ORACLE_HOME/sqlplus/admin/glogin.sql file and add inside your col format settings :)

[ora11g@botor ~]$ cat /u01/app/ora11g/product/11.2.0/db_1/sqlplus/admin/glogin.sql
define _editor=vi
set lines 450
set pages 100
set time on
set timi on
set serveroutput on
......


Cannot generate reports from SQL Management Data Warehouse I'm running SQL Server 2008 R2 and have installed the MDW on one server and have a Data Collector collecting and uploading the server activity, query results, and Disk activity data to the MDW. When I select any of the reports from the MDW with Data Collection > Reports > Management Data Warehouse I receive the error:

  Exception has been thrown by the target of an invocation - > Object reference not set to an instance of an object.

This occurs for all 3 reports and after I've waiting some time and data has been uploaded from the data collector. I do not have SSRS running, but read that isn't necessary.

Any suggestions?

Just ran into and resolved this issue today.  Select the View Logs context menu option in the Data Collection Sets--the data upload could have failed, which causes the reports to generate this issue.