How to Return XML Node Ordinal, or delete node based on element value? I have an XML document similar to this:

<Root>
  <Sub>
    <Record>
      <Guid>aslkjflaksjflkasjfkljsd</Guid>
    </Record>
    <Record>
       <Guid>opqiwuerl;kasdlfkjawop</Guid>
    </Record>
  </Sub>
</Root>

I am replacing an entire <Record> node based on some criteria.  The <Record> nodes contain a <Guid> that I can use to identify them (pretend those are valid GUIDs please!).

Knowing the GUID, I am returning that node's XML into a variable for further processing.  Downstream, I need to be able to delete that particular node so I that I can insert the modified version of the node back into the original document.

Is there a way to determine the ordinal, or another way to use a delete/replace method  to remove a node based on an element's value?

If you have the value in a variable you can use sql:variable() in the predicate for the delete.

declare @XML xml = '
<Root>

    <Record>
      <Guid>aslkjflaksjflkasjfkljsd</Guid>
    </Record>
    <Record>
       <Guid>opqiwuerl;kasdlfkjawop</Guid>
    </Record>

</Root>'

declare @Guid varchar(30) = 'aslkjflaksjflkasjfkljsd'

set @XML.modify('delete /Root/Sub/Record[Guid = sql:variable("@Guid")]')

Replacing a record with a new record would look something like this.

declare @XML xml = '
<Root>

    <Record>
      <Guid>aslkjflaksjflkasjfkljsd</Guid>
      <Value>some value</Value>
    </Record>
    <Record>
       <Guid>opqiwuerl;kasdlfkjawop</Guid>
    </Record>

</Root>'

declare @Guid varchar(30) = 'aslkjflaksjflkasjfkljsd'
declare @NewRecord xml = '
<Record>
  <Guid>aslkjflaksjflkasjfkljsd</Guid>
  <Value>some new value</Value>
</Record>'

set @XML.modify('insert sql:variable("@NewRecord") after (/Root/Sub/Record[Guid = sql:variable("@Guid")])[1]')
set @XML.modify('delete (/Root/Sub/Record[Guid = sql:variable("@Guid")])[1]')

First add the new record after the existing one and the delete the first occurrence where Guid is a match. Without [1] in the delete you will delete all occurrences, not only the first one.


SQL Server : If a query is cancelled, where to get data on it? Suppose am tracking a query using sys.dm_exec_query_stats, if the query ends fine, then we can find the relevant statistics in sys.dm_exec_query_stats. But if a query is cancelled, the statistics of that query (i.e. how long was it executing before being cancelled by the user, its I/O usage, and so on) is not there in sys.dm_exec_query_stats.

Is there any other place to get this info?

You'd have to be polling sys.dm_exec_requests rather frequently, or using trace / extended events to target queries that ended with abort. With the disclaimer that I work for SQL Sentry, Performance Advisor - among many other things - tracks all aborted queries for you, as long as they exceed the Top SQL threshold (5 seconds by default, but that is configurable). It's possible other monitoring tools might do something similar.


Will a mysql db import be interrupted if my ssh session times out? I have a large-ish (several GB) mysql DB that I'm planning to import via

mysql -u root -p mydb < mydb.sql

in a SSH session.

What would happen if my SSH connection drops?

Is it safer to run something like

nohup mysql -u root -p mydb < mydb.sql

Or does mysql not care?

Using the following

MySQL 5.1.45
CentOS 5.x

If the SSH drops without putting the mysql session into the background (along with nohup), then the DB connection is at the SSH session's mercy. So, yes, mysql would care about the lifecycle of the SSH session.

You should launch it with & at the end as follows:

nohup mysql -u root -p mydb < mydb.sql &

Then, you can walk away feeling OK (you could actually logout on purpose at this point) because process 1 (init) will pick up the process ID that is running the mysql session upon the SSH session termination (voluntary or involuntary) because it was suddenly orphaned (What a great dad !!!).


Should I use begin tran to do an alter table column that increases the column length? I want to execute this statement in a production database to alter the length of a varchar column from 50 to 72:

ALTER TABLE Users ALTER COLUMN LoginID varchar(72) NOT NULL

The column is not null so I'm NOT altering column nullability from NULL to NOT NULL.

Database server is SQL Server 2008 R2; table has about 60,000 records.

Do I have to put the alter statment within begin tran commit tran?

The statement is atomic and is its own transaction, so no, there is no value in surrounding it with BEGIN TRANSACTION; / COMMIT TRANSACTION;. It will either succeed in whole or fail in whole.


MySQL get values from the other table by using inner joins In mysql I have 3 tables called as

ps_product_lang

ps_order_detail

and ps_image

here are the tables sample data

=== ps_product_lang ===

CREATE TABLE IF NOT EXISTS `ps_product_lang` (
  `id_product` int(10) unsigned NOT NULL,
  `id_shop` int(11) unsigned NOT NULL DEFAULT '1',
  `id_lang` int(10) unsigned NOT NULL,
  `description` text,
  `description_short` text,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`id_product`,`id_shop`,`id_lang`),
  KEY `id_lang` (`id_lang`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `ps_product_lang`
--

INSERT INTO `ps_product_lang` (`id_product`, `id_shop`, `id_lang`, `description`, `description_short`,  `name`) VALUES
(1, 1, 1, '
this is dummy
', '
dummy
', 'dummy product')
);

===== ps_order_detail===
    CREATE TABLE IF NOT EXISTS `ps_order_detail` (
      `id_order_detail` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `id_order` int(10) unsigned NOT NULL,
      `product_id` int(10) unsigned NOT NULL,
      PRIMARY KEY (`id_order_detail`),
      KEY `product_id` (`product_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ;

    --
    -- Dumping data for table `ps_order_detail`
    --

    INSERT INTO `ps_order_detail` (`id_order_detail`, `id_order`,  `product_id`) VALUES
    (1, 1, 2);

========= ps_image =========
CREATE TABLE IF NOT EXISTS `ps_image` (
  `id_image` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_product` int(10) unsigned NOT NULL,
  `position` smallint(2) unsigned NOT NULL DEFAULT '0',
  `cover` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id_image`),
  UNIQUE KEY `idx_product_image` (`id_image`,`id_product`,`cover`),
  KEY `image_product` (`id_product`),
  KEY `id_product_cover` (`id_product`,`cover`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=24 ;

--
-- Dumping data for table `ps_image`
--

INSERT INTO `ps_image` (`id_image`, `id_product`, `position`, `cover`) VALUES
(1, 1, 1, 1),
(2, 1, 2, 0),
(3, 1, 3, 0),
(4, 1, 4, 0),
(5, 2, 1, 0),
(6, 2, 2, 0),
(7, 2, 3, 1),
(8, 3, 1, 1),
(9, 3, 2, 0),
(10, 4, 1, 1),
(11, 4, 2, 0),
(12, 5, 1, 1),
(13, 5, 2, 0),
(14, 5, 3, 0),
(15, 5, 4, 0),
(16, 6, 1, 1),
(17, 6, 2, 0),
(18, 6, 3, 0),
(19, 6, 4, 0),
(20, 7, 1, 1),
(21, 7, 2, 0),
(22, 7, 3, 0),
(23, 7, 4, 0);

So here I want to get all the field like

id_order_detail id_product name id_image(from ps_image table where cover = 1)

To get id_order_detail id_product name I am using this mysql inner join

SELECT DISTINCT (a.id_order_detail),(b.id_product),(b.name)
        FROM ps_order_detail a join
        ps_product_lang b on a.product_id=b.id_product GROUP BY(name)

This one is giving me id_order_detail id_product name

Now I want to get id_image from the ps_image table where the cover value is 1 for the product_id

So can someone tell me how to do this one? Any help and suggestions will be really appreciable. Thanks

Try this =>

SELECT DISTINCT (a.id_order_detail),(b.id_product),(b.name), img.id_image
        FROM ps_order_detail a join
        ps_product_lang b on a.product_id=b.id_product
        join ps_image img on img.id_product = b.id_product
where img.cover = 1
GROUP BY(name)


For each category, find the count of foreign-key items in all child categories using a PostgreSQL Recursive CTE I have a typical tree structure stored as an adjacency list in PostgreSQL 9.4:

gear_category (
   id INTEGER PRIMARY KEY,
   name TEXT,
   parent_id INTEGER
);

As well as a list of items attached to the categories:

gear_item (
   id INTEGER PRIMARY KEY,
   name TEXT,
   category_id INTEGER REFERENCES gear_category
);

Any category can have attached gear items, not just the leaves.

For speed reasons, I want to pre-calculate some data about each category, which I'll use to generate a Materialized View.

Desired output:

speedy_materialized_view (
   gear_category_id INTEGER,
   count_direct_child_items INTEGER,
   count_recursive_child_items INTEGER
);

count_recursive_child_items is the cumulative number of GearItems attached to the current category or any child categories. There should be one row for each category, with a 0 for any counts that are 0.

In order to calculate this, we need to use a recursive CTE to traverse the tree:

WITH RECURSIVE children(id, parent_id) AS (
    --base case
    SELECT gear_category.id AS id, gear_category.parent_id AS parent_id
    FROM gear_category
    WHERE gear_category.id = 37  -- setting this to id includes current object
                             -- setting to parent_id includes only children
    --combine with recursive part
    UNION ALL
    SELECT gear_category.id AS gear_category_id
         , gear_category.parent_id AS gear_category_parent_id
    FROM gear_category, children
    WHERE children.id = gear_category.parent_id
)
TABLE children;

It's simple to count the child gear items attached to this list of child categories:

--Subselect variant
SELECT count(gear_item.id) AS count_recursive_child_items_for_single_cat
FROM gear_item
WHERE gear_item.category_id IN (
SELECT children.id AS children_id
FROM children);

-- JOIN variant
SELECT count(gear_item.id) AS count_recursive_child_items_for_single_cat
FROM gear_item, children
WHERE gear_item.category_id = children.id;

But if you look at the CTE, I've hardcoded the starting category ID of '37'. I can't figure out how to combine these queries to generate the count_recursive_child_items for all categories, not just a single one.

How do I combine these?

Also, currently for each category, I calculate all the child categories, which creates a lot of duplicated work, and I'm not sure how to remove that. For example, say I have Grandparent > Parent > Leaf. Currently I separately calculate child categories for Grandparent and Parent, which means I twice calculate the Parent > Leaf relationship.

And since I'm already returning count_direct_child_items for each category, it might be faster to just use those when calculating count_recursive_child_items rather than starting from scratch in my counts like I currently do.

Separately, each of these concepts makes sense to me. I just can't figure out how to combine them into a single elegant/optimized query.

This does the job:
CREATE MATERIALIZED VIEW speedy_materialized_view AS
WITH RECURSIVE tree AS (
   SELECT id, parent_id, ARRAY[id] AS path
   FROM   gear_category
   WHERE  parent_id IS NULL

   UNION ALL
   SELECT c.id, c.parent_id, path || c.id
   FROM   tree t
   JOIN   gear_category c ON c.parent_id = t.id
   )
, tree_ct AS (
   SELECT t.id, t.path, COALESCE(i.item_ct, 0) AS item_ct
   FROM   tree t
   LEFT   JOIN  (
      SELECT category_id AS id, count(*) AS item_ct
      FROM   gear_item
      GROUP  BY 1
      ) i USING (id)
   )
SELECT t.id
     , t.item_ct       AS count_direct_child_items
     , sum(t1.item_ct) AS count_recursive_child_items
FROM   tree_ct t
LEFT   JOIN tree_ct t1 ON t1.path[1:array_upper(t.path, 1)] = t.path
GROUP  BY t.id, t.item_ct;

count_recursive_child_items is counted separately for each category, so I am not convinced it's the fastest possible way for deep trees.
However, aggregate functions are not allowed in recursive CTEs.
Recursive Function
Strictly speaking, it's really iterative - but so is a "recursive" CTE.
You could build a function working with temp tables. You need to know your way around plpgsql or there is too much to explain.
CREATE OR REPLACE FUNCTION f_tree_ct()
  RETURNS TABLE (id int, count_direct_child_items int, count_recursive_child_items int) AS
$func$
DECLARE
   _lvl int;
BEGIN
   -- basic table with added path and count
   CREATE TEMP TABLE t1 AS
   WITH RECURSIVE tree AS (
      SELECT c.id, c.parent_id, '{}'::int[] AS path, 0 AS lvl
      FROM   gear_category c
      WHERE  c.parent_id IS NULL

      UNION ALL
      SELECT c.id, c.parent_id, path || c.parent_id, lvl + 1
      FROM   tree t
      JOIN   gear_category c ON c.parent_id = t.id
      )
   , tree_ct AS (
      SELECT t.id, t.parent_id, t.path, t.lvl, COALESCE(i.item_ct, 0) AS item_ct
      FROM   tree t
      LEFT   JOIN  (
         SELECT i.category_id AS id, count(*)::int AS item_ct
         FROM   gear_item i
         GROUP  BY 1
         ) i USING (id)
      )
   TABLE tree_ct;

   -- CREATE INDEX ON t1 (lvl); -- only for very deep trees

   SELECT INTO _lvl max(lvl) FROM t1;  -- identify max lvl to start bottom up

   -- recursively aggregate each level in 2nd temp table
   CREATE TEMP TABLE t2 AS
   SELECT t1.id, t1.parent_id, t1.lvl
        , t1.item_ct
        , t1.item_ct AS sum_ct
   FROM   t1
   WHERE  t1.lvl = _lvl;

   IF _lvl > 0 THEN
      FOR i IN REVERSE _lvl .. 1 LOOP
         INSERT INTO t2
         SELECT t1.id, t1.parent_id, t1.lvl, t1.item_ct
              , CASE WHEN t2.sum_ct IS NULL THEN t1.item_ct ELSE t1.item_ct + t2.sum_ct END
         FROM   t1
         LEFT   JOIN (
            SELECT t2.parent_id AS id, sum(t2.sum_ct) AS sum_ct
            FROM   t2
            WHERE  t2.lvl = i
            GROUP  BY 1
            ) t2 USING (id)
         WHERE  t1.lvl = i - 1;
      END LOOP;
   END IF;

   RETURN QUERY  -- only requested columns, unsorted
   SELECT t2.id, t2.item_ct, t2.sum_ct FROM t2;

   DROP TABLE t1, t2; -- to allow repeated execution in one transaction
   RETURN;
END
$func$  LANGUAGE plpgsql;

This cannot be included in a CREATE MATERIALIZED VIEW statement because of the use of temporary tables. You could just create another (temp) table with it, acting as a manually maintained "materialized view":
CREATE TABLE speedy_materialized_view AS
SELECT * FROM f_tree_ct();

Alternatively you could TRUNCATE speedy_materialized_view in the function and write to it directly. The function would RETURNS void instead or you could return some meta-information like a row count ...
SQL Fiddle.
Aside:
Column aliases in the recursive term of a CTE are just for documentation since output column names are determined by the non-recursive term only.


How to create a role that cannot create or alter a table? I want to create a role named manager in postgresql . This role can update, select, delete and insert data in all tables but users that belong to this role cannot create a table nor modify the schema.

I tried the following sql statement:

CREATE ROLE manager
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

How can I achieve this condition?.

The privilege to create tables is granted to new roles automatically. You need to REVOKE the role's CREATE privilege on the schema (not the database):

REVOKE CREATE ON SCHEMA myschema FROM manager;

Per documentation on GRANT:

  CREATE

  For databases, allows new schemas to be created within the database.

  For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this
  privilege for the containing schema.

  For tablespaces, allows tables, indexes, and temporary files to be created within the tablespace, and allows databases to be created that
  have the tablespace as their default tablespace. (Note that revoking
  this privilege will not alter the placement of existing objects.)

And REVOKE all direct privileges any role might have in respective schemas. By default, plain roles only have privileges for the schema public and schemas they create themselves.


Is there a way to see the code that creates a view in SQL server management studio? I want to see the underlying code for how a view is created in SQL server management studio. Is there a way to do this?

I want to see any date filters, etc, applied to this view in hard code.

Right click the view, select Script View as, CREATE To, New query editor window.


How to transfer DDL changes between different stages? What technical solutions are available to track DDL changes and transfer them between different stages (e.g. DEV -> QA -> PROD) on postgreSQL databases? Is there a common mechanism to do that?

There are some.  The most reliable I've seen so far (at least when the development process is also have to be considered) is having diffs.

Basically, all DB objects (tables, types, functions, etc.) are defined one by one in files, which are then under version control.  When you want to change something, you write a diff (ALTER TABLE, for example) and change the affected definition files, too.  The changed files and the diff itself have to be checked in, of course. Then you can roll out these diffs on the different stages, each time registering the diff with its unique name (which might be a ticket number with something else).

All this does not really need special software, your favourite DB client, the usual version control system and some small mechanism of registering the diffs is enough.  For the latter, you can use depesz's versioning schema.


SQL Server Change Tracking and Replication My colleagues and I are working on a solution revolving around change tracking…

Our goal is to use SQL Server change tracking to track changes to be pushed to another non-SQL system.

Our database is a subscriber (transactional replication). Numerous publishers are pushing transactions into numerous tables.

During our testing, we observed that the entries recorded by change tracking has records grouped into ‘chunks’. I.E. There are many records with the same value of SYS_CHANGE_VERSION.

This is based on the query of select * from changetable(CHANGES my_table, 0)

Observation: When we execute the above query, some of the rows are ‘interleaved’.

For example, we may have 50 row with SYS_CHANGE_VERSION = 100, 1 row with SYS_CHANGE_VERSION = 101 and then more rows with SYS_CHANGE_VERSION = 100.

Our question:

When rows are inserted into the change tracking table/mechanism, can rows be inserted with a lower value of SYS_CHANGE_VERSION than the current maximum value?

We are trying to determine if all we need to do is have a sort order on SYS_CHANGE_VERSION and simple ignore records for the current max value of SYS_CHANGE_VERSION.

Furthermore, once data is read from the change tracking table, can subsequent reads contain entries which have the same ‘maximum’ SYS_CHANGE_VERSION which was read previously.

We are worried about whether or not we can continue/resume the process of pushing transactions to the non-sql system.

As an alternative, we have considered using the replication stored procedures on my subscriber to push changes to my non-SQL system.

Any answers greatly appreciated.

My understanding of Change Tracking is that it is per database transaction. This means that all changes that are part of the same transaction get the same SYS_CHANGE_VERSION.

I also understood that the SYS_CHANGE_VERSION is set during the commit of the transaction, so numbers are a) always ascending and b) may be interleaved. You should always process the records in transaction completion order.

See Obtaining Changes by Using the Change Tracking Functions for confirmation that VERSION is ever increasing.


Get maximum number with combination of columns I have table with columns like AutoID, Number, Name, City, State, Country.

What I wanted is the maximum number entered in the “Number” column with the combination of Name, City, State and Country.

Example:

Name  City State Country
----- ---- ----- -------
Smith NY   NY    USA
John  NY   NY    USA
John  NJ   NY    USA

Now smith should get “Number” 1, John 2, and again John(in NJ) 1 as he is the first from NJ.

I can simply put a where clause in query and get the max number + 1. But the problem is that when I have huge amount of data and the number of users increases, my query will be really slow. I am also inserting data in the same table so it will keep on piling.

I hope I have made my self clear.

If I'm understanding you correctly then the following should give you what you want.

  create table xt (name NVARCHAR(50) NULL,
  city nvarchar(2) NULL,
  state nvarchar(2) NULL,
  country nvarchar(3) NULL)

  INSERT into xt (name, city, state, country)
  values(N'smith', N'ny', N'ny', N'usa'),
  (N'john', N'ny', N'ny', N'usa'),
  (N'john', N'nj', N'ny', N'usa')

SELECT name,
       city,
       state,
       ROW_NUMBER() OVER (PARTITION BY name ORDER BY (SELECT 0)) AS RN
  FROM xt
   GROUP BY name, city, state
   order by rn

drop table xt

This should provide the following output

name    city    state   RN
john    nj  ny  1
smith   ny  ny  1
john    ny  ny  2


Sql Server Service Pack Update Notifications Is there any option in Sql Server to enable notifications from Microsoft in a way that it sends us emails every time a new service pack is released?

I have multiple servers with different versions of Sql Server installed and I want the sql server to send me an email every time a new service pack is available

I read this article and it says there is no such option.

Sql Server Service Pack Updates Article I found online

But I would like to ask the question anyways in case if it changed during the latest releases.

You could subscribe to the RSS feed on Brent Ozar's new site sqlserverupdates.com

He keeps an up to date list of the latest Service Packs and Cumulative Updates required for each version of SQL Server since 2005.


How do I create a table best suited for storing address information I have to create an address table in Oracle. Table is expected to hold millions of row. I created a table looking like this

 Table Address
    ID | Address Line1 | Address Line2 | Address Line3| City | Zip | ..|

The queries that I expect from my client may contain AddressLine1 and Zip or AddressLine1, Zip and City in which case the above table structure would not the most ideal. I don't want to have to scan the entire table for every query. I know for sure that the client queries will at least contain zip or city, am thinking that I have to create and indices on Zip and City. Should I consider anything else in my design?

I considered partitioning the table, but am going to start with a blank database and rows get added over a period of time, am not sure if I can dynamically create those partitions based on City or zip.

Also my expectations is that the database has to be optimized for reads more than writes

Any ideas?

I would analyze the data and seriously consider scalability.

-Do you need to retain an old address for prior billing?
-Do you need to know when a client changed addresses?
-Are other countries currently involved now?
-Will other countries be involved?

3 address lines may not be enough.

You will likely need a total of:

-5 address lines
-City
-State
-Zip
-country
-Province ?
-CreateDate
-Expiredate
-Region  (I would consider this a way to group Zip codes 90000 series is Southern California, etc).  You could adjust later as needed.
-AcctType (billing, sales mailings, main office)


Speed up AWS PostgreSQL insertion via Python script I have 120,000 csv inside my AWS EC2 instance, each containing 120,000 rows. I can't insert each of them as is into my AWS RDS postgresql DB, that will be 120,000^2 = 14,400,000,000 records. Each csv is about 2 MB.

My approach is:

Python script that converts 120,000 records into just 1 record (list of dictionary) for each csv (now 5 MB after condensing the data) with the help of pandas library
The python script then insert each csv's 1 record into AWS postgresql database via pandas.to_sql (which uses sqlalchemy
I use python multiprocessing module to fully utilize my AWS EC2 to speed up data insertion
I did not create additional indexes in order to speed up my data insertion
I use AWS EC2 instance with up to 25GB of network connection

My observation is:

At the beginning, my code will insert 50 csv per min, which is decent speed
However, right now, with 50k csv being inserted, it only insert 1k csv in 5 hours, about 3.33 csv per min
I tried using psql \copy and realized that it takes between 30-50 sec to insert 1 csv, that's slower than my script that converts the data and insert into the DB directly

I am not sure how I can speed up things up.

t2 classes are unsuitable for high work loads, by design.  Once you have used up all your CPU credits, performance is abysmal.  Again, by design.

Don't use t2 (or any t*) instances if you care about performance.

As far as I can tell, there is no way to monitor the CPU credits on RDS (the way you can on EC2)


Msg 39011 SQL Server was unable to communicate with the LaunchPad service I was playing with the new "R" scripting in SQL Server 2016 CTP 30. Finally got it working, thanks to this site.

I've had a problem that I'm sure others will run into as well.

All of a sudden the "R" scripts no longer worked, they returned this error:

  Msg 39011, Level 16, State 1, Line 1
  SQL Server was unable to communicate with the LaunchPad service. Please verify the configuration of the service.

In order to move TempDB to a disk with more space, I had to restart the server.
This stopped the SQL Server as well as SQL Agent, and the new "SQL Server Launchpad".

It was easy to restart SQL Server and SQL Agent.
To restart LaunchPad, I needed to go to

Administrative Tools -> Services
and find "SQL Server LaunchPad (MSSQLSERVER)"

right-click it and select "Start"
and my scripts run again.


Replicated Database Log File Maintenance I have a database on the publisher that is involved in replication (publication configured for merge and transaction). Trying to regain control of the log file for this particular database (VLF count, size, etc.).

Is there anything I need to do (or be cautious of) with the replication setup before trying to perform any maintenance on the log file? I am not an expert in the area of replication and cannot find anything solid that provides guidance as to what measures should be taken.

Edit: This would include working on the distribution database as well, data retention was not configured at all for some reason.

I would first concentrate on the Autogrowth settings for the database as too frequent and incorrect autogrowths can cause massive internal log fragmentation affecting any processes that reads transaction log file (e.g. replication, CDC, backups, etc) and there-by soaring the number of VLF's. Remember that the log file has to grow as it cannot be ZERO initialized and hence if the autogrowth settings are not appropriate, then SQL Server has to wait till the auto growth event finishes.

Now, if you are using T-Rep, the log reader agent will read the log when replicating changes from the publisher to distributor and then the distributor agent will distribute the changes to the subscribers which are called "pending transactions". Once the changes are replicated, the log entry is marked as "replicated".

The problem arises when the log reader becomes slow or it gets delayed leading to accumulating "pending replication" commands for long periods and they will remain part of active log and hence VLF cannot be truncated.

You can check the log_reuse_wait_desc column in sys.databases which will be showing "Replication" as the problem.

Note: Merge replication and snapshot replication do not affect transaction log size. If a database includes one or more transactional publications, the log is not truncated until all transactions relevant to the publications have been delivered to the distribution database.

If the log file grows out of control, a common technique for T-Rep is to use sp_repldone command to mark all the log records currently waiting on the log reader as replicated and then you have to re-initialize the subscribers.

Note: Even if you try to switch to simple recovery model thinking that it will truncate the log, it wont work as replication is supported in SIMPLE recover as well, and the log wont be able to get truncated as the log reader agent will need them to process.

This brings us to doing Proper Log Management - size the log file to support the volume of data changes occuring in the database and take log backups frequently so as to allow rapid reuse of space within the log file.

Best is to monitor your Transaction log along with other monitoring that you might be normally doing using PERFMON and DMV's.

Just and FYI, sys.dm_db_log_space_usage is a new DMV in SQL Server 2012 that will get basic log size and space info.

References :

Manage the Size of the Transaction Log File
Frequently Asked Questions for Replication Administrators
Stop and checked your Virtual Log Files (VLFs)
Free e-book : SQL Server Transaction Log Management by Tony Davis and Gail Shaw


Estimated hardware requirement for a planned "mostly-write" database I was asked to create a server application that needs to receive and store relatively simply messages (about 7 fields, uints, strings and a timestamp), but a lot of them and at a high frequency. The messages fly in at an average rate of about 100 messages per second, with peaks going up to about 400 per second. The database will mainly consist of one large archive table and some reference tables with station names etc.

As I do not have experience with Postgres, I'm having a hard time defining the required hardware for the use case, is there some rule of thumb or some lookup table that you know of? Of course, bigger and faster is always "better", but I would like to get a feeling for what will do the job with a good performance and reserve without going directly to the top of the shelf.

Here are some more details:

The data need to be accessible for at least 5 years before it is acceptable to export and remove them from the DB
the data need to be filtered by time ranges and some additional fields
Search and filtering results should be reasonably fast (from a GUI user's point of view who knows nothing about the amount of data), so maybe some milliseconds or seconds
From what I already know, new messages do not need to be written to the DB instantly, it is ok to collect them in intervals of 1 to 2 seconds and bulk put them into the database
99% of the time, nobody will look at the data, so there's usually just the server application pumping messages into the archive, from time to time a user starts a client application to look at some values, so there are very few concurrent connections to the DB

Here's a message definition from my current server application written in Go:

type Message struct {
    Sender     string   `json:"sender"`
    MessageID  int      `json:"message_id"`
    Text       string   `json:"text"`
    Priority   int      `json:"priority"`
    Parameters []string `json:"parameters"`
}

MessageID references a Messages table, for the Parameters string array I would like to use a Postgres string array field.

What do you think is a good hardware platform in terms of CPU & cores, disk space, memory? Thanks in advance for your estimation.

How many connections are there going to be pumping in the data?  Is every insert naturally its own transaction or are they naturally bundled up somehow?

  From what I already know, new messages do not need to be written to the DB instantly, it is ok to collect them in intervals of 1 to 2 seconds and bulk put them into the database

Where are they going to be stored for 1 to 2 seconds? What happens if the system crashes/restarts and those 1 to 2 seconds get forgotten about, is that acceptable?  If a small amount of data loss is acceptable on rare occasions, then simply setting synchronous_commit to off is probably a better solution then coming up with some complicated way to re-arrange small transactions so they are bundled into larger transactions.

All the above is mostly to answer the question of how fast does the fdatasync on your storage need to be.

  the data need to be filtered by time ranges and some additional fields

How wide will the queries time ranges be?  If they are narrow, then you can have an index just on the timestamp.  Then read every row in the time range and apply the additional field filters to each row.  This would make index maintenance pretty efficient, as the rows should be inserted roughly in timestamp order, meaning the index leaf pages that need to be updated will have just recently been used and so still in memory.  If you need more indexes than this, then index maintenance will likely become your bottleneck, especially once the total size of your indexes becomes a substantial fraction of your RAM.

Also, will the time range in those queries be mostly for recent times, or for any time in the last 5 years?

  The data need to be accessible for at least 5 years before it is acceptable to export and remove them from the DB

Do you envision this working by removing data from 5 years + 1 hour ago, every hour?  Or just archive and drop an entire year at a time once a year?  If the latter, then partitioning by year would be a good idea.  But at 3 billion records per year, you might want to partition by month instead.

I think it is hard to do much capacity planning without knowing which indexes will need to exist.

I don't think a large number of CPUs will help you.  Spend the money on fast storage, or maybe RAM, instead.


Could not continue scan with NOLOCK due to data movement We run SQL Server 2000 and we get a few of these errors every night.

Could not continue scan with NOLOCK due to data movement

The query that throws this error is a large complex query that joins over a dozen tables.  Our underlying data can be updated frequently.

The cultural 'best-practice' is that, in the past, introduction of NOLOCK hints increased performance and improved concurrency. This query doesn't need to be 100% accurate, i.e. we will tolerate dirty reads etc. However, we are struggling to understand why the database is throwing this error, even though we have all these locking hints.

Can anyone shed some light on this - be gentle, I'm actually a programmer, not a DBA :)

PS: We have applied the fix mentioned below previously: http://support.microsoft.com/kb/815008

This is a reasonably well-known issue with SQL Server 2000 - essentially, what happens is if a row gets deleted by process A while process B is doing a scan (either at READ UNCOMMITTED or WITH (NOLOCK)), then process B goes "huh what happened to this data" when it tries to read it. More precisely, the row has to be deleted after process B reads the index, but before it attempts to read the data row.

Craig Freedman gives a good write up here

Fortunately, the fix is relatively simple: http://support.microsoft.com/kb/815008

If that doesn't work, you have the slightly more painful option of removing all your WITH (NOLOCK) hints and setting your transaction isolation level to something above READ UNCOMMITTED.


Oracle 11g R2 - same query sometimes fast, sometimes slow I have a simple update query on a table with about 100 rows in it:

UPDATE RESULTS_DATA
     SET IMAGE_BLOB=:myBlob
WHERE MEASUREMENT_INDEX=:mIndex
  AND BATCH_ID=:batchId
  AND SYSTEM_ID=:sysId

There are indexes on all 3 columns I am searching on. The BLOB is 1.2MB in size. Sometimes the query takes 0.5 seconds to complete, but other times it takes around 3-4 seconds. Looking in the V$SQL table shows that almost everything is the same between multiple runs of the same query, but the following fields are very different:

IO_INTERCONNECT_BYTES (1277952 on fast query, 2818048 on slow query)
    PHYSICAL_READ_REQUESTS (1 on fast query, 27 on slow query)
    PHYSICAL_READ_BYTES (8192 on fast query, 393216 on slow query)
    PHYSICAL_WRITE_REQUESTS (155 on fast query, 164 on slow query)
    PHYSICAL_WRITE_BYTES (1269760 on fast query, 2424832 on slow query)

This is from running exactly the same query sending exactly the same BLOB. Could anyone tell me why this would happen?

Thanks!

The query is sometimes fast because it is in the database's cache.

Make a test:

alter system flush shared_pool; (as sysdba - NOT IN PRODUCTION !!)
execute the query and display execution plan;
execute the query and display execution plan (should be faster because the query is not parsed but only executed );
alter system flush shared_pool; (as sysdba)
execute the query and display execution plan (should be slower the query is first parsed then executed)

In a ideal world, a query is parsed once and bound/executed over and over.


query for showing columns from various tables, grouping and showing calculated field in each row of the result I am doing a stock management system.
In one table I store items:
ITEM

id_item

item_name

ORDER

id_order

date

ORDER_ITEM

id_ord_item

id_order : foreign key to ORDER id_order primary key

id_item : foreign key to ITEM table.

qty_ordered

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

id_entry

id_ord_item: foreign key to ORDER_ITEM table.

description: optional

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

in_date: the date where the item quantity arrives to warehouse

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

qty_ordered,

in_qty,

in_date.

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

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

Basic is that you have all Tables set and also defined the relationship.
Then you add a new query.
Then you pull in all Table that you need, if your relationships are correctly set, all tables should ha a connection.
Now you pull all needed columns into the part below
and add a column that has the difference betwenn qty ordered and inqty

Now you have a corect query to run.
As SQL it looks like
SELECT ITEM.item_name, STOCK_IN.description, ORDER.date_order, ORDER_ITEM.[qty_ordered]
, STOCK_IN.in_qty, STOCK_IN.in_date, [qty_ordered]-[in_qty] AS qty_left
FROM ([ORDER]
      INNER JOIN (ITEM
       INNER JOIN ORDER_ITEM ON ITEM.id_item = ORDER_ITEM.id_item) ON ORDER.id_order = ORDER_ITEM.id_order)
       INNER JOIN STOCK_IN ON ORDER_ITEM.id_ord_item = STOCK_IN.id_ord_item;

For your query to have the complete quantity for all orders
SELECT ITEM.item_name, STOCK_IN.description, ORDER.date_order, ORDER_ITEM.[qty_ordered], STOCK_IN.in_qty, STOCK_IN.in_date, [Sum1] AS qty_left
FROM
    ([ORDER] INNER JOIN (
                                           ( ITEM INNER JOIN (SELECT ORDER_ITEM.id_item
                                                                       ,SUM( [qty_ordered]-[in_qty]) AS Sum1
                                                                       FROM ([ORDER] INNER JOIN ORDER_ITEM ON ORDER.id_order = ORDER_ITEM.id_order) INNER JOIN STOCK_IN ON ORDER_ITEM.id_ord_item = STOCK_IN.id_ord_item
                                                                       GROUP BY ORDER_ITEM.id_item) qtysum ON qtysum.id_item = ITEM.id_item
                                           )
INNER JOIN ORDER_ITEM ON ITEM.id_item = ORDER_ITEM.id_item
) ON ORDER.id_order = ORDER_ITEM.id_order)
INNER JOIN STOCK_IN ON ORDER_ITEM.id_ord_item = STOCK_IN.id_ord_item;

Result:


Inherit audit columns and triggers Background

I have a database with dozens of tables, some of which should have audit columns.

Problem

I'd like to avoid the drudgery of creating audit columns manually, and write the update and delete triggers once.

Code

There are several different pieces of code that must come together.

Audit Table

The following code creates an audit table and city table:

CREATE TABLE audit (
  created timestamp with time zone NOT NULL DEFAULT ('now'::text)::timestamp with time zone,
  deleted timestamp with time zone,
  updated timestamp with time zone
);

Example Table

A simple table to serve as an example of the problem:

CREATE TABLE city (
  name       text,
  population real,
  altitude   int
);

Parent Table Trigger

The following code creates a database trigger and a delete prevention rule on the audit table:

CREATE FUNCTION audit_delete() RETURNS trigger AS
$audit_delete$
BEGIN
  OLD.deleted := current_timestamp;
  RETURN NULL;
END;
$audit_delete$
LANGUAGE plpgsql;

CREATE TRIGGER audit_delete BEFORE DELETE ON audit
    FOR EACH ROW EXECUTE PROCEDURE audit_delete();

Adding Columns

Inheritance requires columns in the child table to match the parent table and must be created manually. Such as:

for $name in (SELECT list of tables); do
  ALTER TABLE $name ADD COLUMN
    created timestamp with time zone NOT NULL DEFAULT ('now'::text)::timestamp with time zone;
  ALTER TABLE $name ADD COLUMN
    deleted timestamp with time zone;
  ALTER TABLE $name ADD COLUMN
    updated timestamp with time zone;
done

There's probably a way to detect and auto-add the missing columns using the information schema.

Establish Inheritance Hierarchy

The following code applies the inheritance to the given table:

ALTER TABLE city INHERIT audit;

Insert Data

Add some data into the database:

INSERT INTO city ('Vancouver', 603502, 10 );
INSERT INTO city ('Seattle', 620887, 10 );
INSERT INTO city ('La Rinconada', 30000, 5100 );
INSERT INTO city ('Jericho', 20300, -260 );

Delete Data

Remove some data:

DELETE FROM city WHERE name = 'Jericho';

At this point, I was expecting the deleted column to contain the date for the value of "Jericho". Instead, the row as actually removed from the database.

The trigger for audit_delete() didn't fire.

Questions

What is the optimal (or usual) way to create a trigger that sets the deleted date on delete queries for all tables that inherit from the audit table (using PostgreSQL 9.2)?
(Optional) Is this a good use for triggers?

Thank you!

Related Links

http://projects.nocternity.net/index.py/en/psql-inheritance
http://www.postgresql.org/docs/current/static/tutorial-inheritance.html
https://stackoverflow.com/questions/8469116/how-can-i-make-a-trigger-to-use-inheritance-with-that-tables
http://tapoueh.org/blog/2010/11/24-dynamic-triggers-in-plpgsql.html
http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
http://archives.postgresql.org/pgsql-general/2009-08/msg00026.php
http://andreas.scherbaum.la/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html

From http://postgresql.1045698.n5.nabble.com/DELETE-and-UPDATE-triggers-on-parent-table-of-partioned-table-not-firing-td5683717.html:

  The DELETE and UPDATE triggers need to be on the child tables.  An operation on a child doesn't fire the triggers of the parent.

As such, the following code must also be executed:

CREATE TRIGGER ${name}_delete BEFORE DELETE ON $name
    FOR EACH ROW EXECUTE PROCEDURE audit_delete();

The audit_delete() function won't work as written in the question: setting the deleted date is not possible using OLD.deleted = current_timestamp. Instead, assuming that the table has a primary key field of id:

CREATE OR REPLACE FUNCTION audit_delete()
  RETURNS trigger AS
$BODY$
BEGIN
  EXECUTE
    'UPDATE ' || TG_TABLE_NAME || ' SET deleted = current_timestamp WHERE id = ' || OLD.id;
  RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1;


Master updates Slave but not Master itself I have a setup master/slave in which applications are pointed only to master. Yesterday Master had got crashed due to "multi bit error on dimm detected" in Front indication panel in orange color.

After reboot I see the master went for recovery then it came up and application started to use the Master normally but still the err msg exist in Front indication panel. But now the slave had stuck out due to primary key constraint for a table.

The problem I face is master has a table data until what exist in binlog of master. But slave has the table data what is not in master binlog.
Below is the table details when compared to Master table and Slave table.

Last info in binlog timestamp is 2013-04-29 02:13:11
System shut down timestamp 2013-04-29 02:54

            mysql> select * from audit where id=11298907;
            ------------------------------------------------------------
            | id     | ipaddress      | dated        | msisdn  |
            +----------+----------------+-------------------------------
            |11298907 | 82.25.226.183  | 2013-04-29 02:13:11 | 998282821|
            -----------------------------------------------------------
            mysql> select * from audit where id > 11298907 limit 1;

            -----------------------------------------------------------
            | id     | ipaddress      | dated        |msisdn  |
            +----------+----------------+------------------------------
            |11298908 | 82.25.226.183 | 2013-04-29 04:31:13 | 992828111|
            ------------------------------------------------------------

            On Slave
            ==========

            mysql> select * from audit where id=11298907;
            ------------------------------------------------------------
            | id     | ipaddress      | dated        | msisdn  |
            +----------+----------------+-------------------------------
            |11298907 | 82.25.226.183  | 2013-04-29 02:13:11 | 998282821|
            -----------------------------------------------------------

            mysql> select * from audit where id > 11298907 limit 1;

            -----------------------------------------------------------
            | id     | ipaddress      | dated        |msisdn  |
            +--------+----------------+---------------------------------
            |11298908 | 82.25.226.183 | 2013-04-29 02:13:12 | 762616173|
            ------------------------------------------------------------

Both the servers are ntp synched with GST.
And they remain same after Master reboot too.
It took almost 2 hrs to bring up Master Server.
How come only slave can have the data that Master is not aware of? Please have someone come accross such a thing?

The reason for this is pretty straightforward:

  if you are using InnoDB tables and the MySQL server processes a COMMIT statement, it writes the whole transaction to the binary log and then commits this transaction into InnoDB

http://dev.mysql.com/doc/refman/5.5/en/binary-log.html

In periods of moderate to low traffic, it's easy for the "last thing that happened" to make it into the binary log and get handed over to the slave, where it's executed, but not persist on the master.  If you're not using sync_binlog = 1 to sync the binary log to disk after every write and innodb_flush_log_at_trx_commit = 1 to flush every InnoDB commit immediately to disk, then you don't have an ACID-compliant installation ... and if the machine itself crashes, then MySQL has no means of realizing that InnoDB and the binary log aren't consistent on crash recovery.

Update: the original question suggests that you reviewed the master's binlogs and found the data that made it to the slave to be missing from there, too.  To clarify, there are two possible explanations, if this is the case:

the event was "written" to the master binary log and sent to the slave, but "writing" to a file and the operating system flushing that file to the hard drive are not the same thing; a file that has been written to does not have to be flushed in order for the unflushed data to be read, since the "written" data exists in the OS cache even if it hasn't been persisted to disk.
there is some language at the link above that suggests that even if the binlog did get persisted to disk, crash recovery could have trimmed those transactions when InnoDB discovered they hadn't been committed:

  after a crash, after doing a rollback of transactions, the MySQL server cuts rolled back InnoDB transactions from the binary log

The justification for this action is stated as:

  This ensures that the binary log reflects the exact data of InnoDB tables, and so, that the slave remains in synchrony with the master (not receiving a statement which has been rolled back).

This statement is not fully true, since there's a small window of time that the event could have gotten sent to the slave.  On the other hand, if this trimming does happen, the binlog would agree with the data that got persisted on the master, which is arguably better than it not agreeing with the data that got persisted to the master.

I'm not wholly convinced that the example, below, from the source code, represents the process described in the documentation, but if you have a message in your error log that looks like this, then that explanation seems to address your question.

sql/binlog.cc:
  sql_print_information("Crashed binlog file %s size is %llu, "
                        "but recovered up to %llu. Binlog trimmed to %llu bytes.",
                        log_name, binlog_size, valid_pos, valid_pos);


Unix Epoch timestamp for now() in postgresql I am trying to update one field as epoch from now() but I am getting as '15872897243629.2188' fraction format. What I need is the exact epoch from now() without fraction values like '15872897243629'

here the query I used

UPDATE tablename SET
column1= extract(epoch from now()),
column2= extract(epoch from now())
where column3= 'SUCCESS';

Add an appropriate type cast:

CAST (EXTRACT (epoch from current_timestamp) AS bigint)


How to get a list/result of best selling items I'm trying to get the results of my best selling products using SQL.

My table is order_items which has the following columns.

id|product_id|qty

As there is a QTY field I have to somehow count or group each by product ID then sum the qty, then only return the top 5 selling items.

So the data looks kind of like below.

id|product_id|qty
-------------------------
 1|   1      |   1
 2|   1      |   3
 3|   2      |   3
 4|   2      |   1
 5|   1      |   7

... and so on

Not to sure about how to go about this, any help is much appreciated.

Database system used is MySQL

Assuming SQL Server, I might use:

SELECT TOP(5) ProductID, SUM(Quantity) AS TotalQuantity
FROM order_items
GROUP BY ProductID
ORDER BY SUM(Quantity) DESC;

This returns the top 5 best-selling Products.

Since SUM(Quantity) is also included in the SELECT clause, you can reference its alias in ORDER BY instead of repeating the expression:

...
ORDER BY TotalQuantity DESC;


SQL substract values from same column different rows I have above mysql stock table where both inward and outward data are stored and identified each type by type column (i and o). I want to build a query to retrieve balance quantity(qty) per each batchno or drugid.
for example, batch no 2001 has 1000 inward and 200 outward. So the balance should be 800.
if there are many outwards for same batchno, they all should be included.

some help in this regard is highly appreciated.

retrieve balance quantity(qty) per each batchno or drugid

SELECT batchno, -- or drugid
       SUM(CASE type WHEN 'i' THEN qty  -- inward
                     WHEN 'o' THEN -qty -- outward
                     ELSE 0             -- unknown
           END) balance
FROM stock_table
GROUP BY batchno -- or drugid


Very slow IndexOnlyScan on partial ~16MiB indexes I have partitioned table by months and recently added partial, covering index to get certain data really fast. It works on one pair of servers (master+slave) that hold portion of data (including whole 2016 year), but it does not work as expected on second pair that holds 2017 and 2015.

What I would like to know is why it is so slow and how could I fix this.

I have to use pg_hint extension in order to force IndexOnlyScan for the slowest plan, otherwise it decides that doing Bitmap Heap Scan will be faster (it is not wrong). I also did try with set enable_bitmapscan = off; for the same effect.

Those test are made on live servers and clearing cache/buffers might be tricky, but possible if necessary. Both master and slave behave in similar way.

Most notable difference in config is that faster pair has hot_standby_feedback turned off and is 9.6.5, while slower is 9.6.2. But AFAIK that would only mean more bloat on slower pair. I did not notice any mention of btree IndexOnlyScan fixes between 9.6.2 and 9.6.5, but I could be barking at wrong tree here?

My coworker mentioned that maybe it is checking visibility map to see if tuples in index are still live or not, but would that explain queries to take 30-70 minutes on a 17MiB index? About 10-15% are dead tuples on those partitions and partial index covers about 1% of total rows.

Index looks like this:

CREATE INDEX "partition_201701_covering_idx"
  ON "partition_201701"
  USING btree
  (col1, col2, col3)
  WHERE orpr > 0::numeric;

Stats:

         tablename          |                      indexname                       |  num_rows   | table_size | index_size | unique | number_of_scans | tuples_read | tuples_fetched | indexprs |        indpred         | indam
----------------------------+------------------------------------------------------+-------------+------------+------------+--------+-----------------+-------------+----------------+----------+------------------------+-------
 "partition_201701"         | "partition_201701_covering_idx"                      | 5.64915e+07 | 12 GB      | 17 MB      | N      |               2 |      352450 |         352446 |          | (orpr > (0)::numeric)  | btree
 "partition_201506"         | "partition_201506_covering_idx"                      | 4.0186e+07  | 8851 MB    | 16 MB      | N      |               1 |      347222 |          11370 |          | (orpr > (0)::numeric)  | btree
 "partition_201601"         | "partition_201601_covering_idx"                      | 4.81767e+07 | 11 GB      | 15 MB      | N      |               1 |      318277 |            347 |          | (orpr > (0)::numeric)  | btree

EXPLAIN (analyze, buffers):

QUERY PLAN
Index Only Scan using "partition_201701_covering_idx" on "partition_201701" v  (cost=0.42..1309538.47 rows=359624 width=16) (actual time=14.866..4535824.075 rows=352133 loops=1)
  Index Cond: ((timestmp >= '2017-01-01 00:00:00'::timestamp without time zone) AND (timestmp <= '2017-01-31 00:00:00'::timestamp without time zone))
  Heap Fetches: 352137
  Buffers: shared hit=64485 read=239413 written=12
Planning time: 0.181 ms
Execution time: 4536208.657 ms

QUERY PLAN
Index Only Scan using "partition_201506_covering_idx" on "partition_201506" v  (cost=0.42..105239.14 rows=316903 width=16) (actual time=26.633..25690.807 rows=347219 loops=1)
  Index Cond: ((timestmp >= '2015-06-01 00:00:00'::timestamp without time zone) AND (timestmp <= '2015-06-30 00:00:00'::timestamp without time zone))
  Heap Fetches: 11373
  Buffers: shared hit=61890 read=9222 dirtied=6178
Planning time: 27.566 ms
Execution time: 25909.181 ms

QUERY PLAN
Index Only Scan using "partition_201601_covering_idx" on "partition_201601" v  (cost=0.42..14977.06 rows=403037 width=16) (actual time=29.858..203.586 rows=318277 loops=1)
  Index Cond: ((timestmp >= '2016-01-01 00:00:00'::timestamp without time zone) AND (timestmp <= '2016-01-31 00:00:00'::timestamp without time zone))
  Heap Fetches: 347
  Buffers: shared hit=101701 read=1966
Planning time: 147.848 ms
Execution time: 216.116 ms

If 15% of the table rows are dead, and those dead rows are concentrated in the rows which meet the partial index conditions, then that could be up to 1500% bloat in the index itself.  (For this reason, tables with partial indexes often need to be vacuumed much more aggressively than the default settings).  But, it might not be necessary to use this high bloat to explain the slowness, because the lack of visibility map setting might be sufficient to explain it.

  My coworker mentioned that maybe it is checking visibility map to see if tuples in index are still live or not, but would that explain queries to take 30-70 minutes on a 17MiB index?

Yes.  17MiB of index can reference gigabytes of table, and since it has to check that table for visibility, that means it can be thrashing through gigabytes of table data in random order being driven off of 17MiB of index.

Looking at your slowest plan, if all 239413 buffer misses had to be read from disk and you have 7200 rpm disks, that alone would explain half your execution time.  If there were several such queries running at the same time competing for disk access, it could explain all of the execution time.

While it is technically using an index only scan, you are getting basically zero benefit from this because of the visibility map issue.

Either the table isn't being vacuumed enough, or the effects of hot_standby_feedback are rendering the vacuuming largely ineffective.  (Or possibly the table has such a high churn rate that trying to keep the visibility map topped up is a hopeless task, and you need to give up on index-only-scans being a useful tool for you on that partition)


Separating tables vs having one table At the moment I have a table setup that looks somewhat like this:

create table tbl_locationcollections
(
   id int(11) PRIMARY KEY IDENTITY, --(Primary Key),
   name varchar(100) not null,
   cleanname varchar(100) not null,
   typeid int(11) not null (foreign key)
)

create tbl_locationcollectiontypes
(
   id int(11) PRIMARY KEY IDENTITY,  --(Primary Key)
   type varchar(100)
)

a type would be something like: country, state, city etc etc.

I would then join to the various type tables like this:

create tbl_states2locationcollection
(
   id int(11) PRIMARY KEY IDENTITY,  --(Primary Key)
   stateid int(11) not null, --(foreing key)
   locationcollectionid int(11) not null --(foreign key)
)

with other tables like tbl_cities2locationcollection etc etc.

Is this style of seperation better for speed and readability than having a table such as:

create tbl_locations2collection
(
   id int(11) PRIMARY KEY IDENTITY, --(Primary key)
   locationid int(11) not null, --(foreign key to the type of: state, country, city)
   typeid int(11) not null --(foreign key)
)

where all the different types are mixed in together.

the only downside i can see for not having a mixed table, is having to create a specific table for each type that is created in the future.

First of all, mysql (and other sql databases) are RDMS meaning that they are based in the relational model. This means that the design should be about entities and their relations. In your case:

Entities: locations, types.

Relation: one location can be of one type (if I have understand you correctly). This is a one-to-one relation.

The best way to store this relation is as you propose at the begining:

create table tbl_locationcollections
(
   id int(11) PRIMARY KEY IDENTITY, --(Primary Key),
   name varchar(100) not null,
   cleanname varchar(100) not null,
   typeid int(11) not null (foreign key)
)

create tbl_locationcollectiontypes
(
   id int(11) PRIMARY KEY IDENTITY,  --(Primary Key)
   type varchar(100)
)

but then you propose something strange, some tables to relate each type with their locations. There is no need for this table. The relation is already set by the foreign key constraint. If you want to get all the locations of the same type, for example country, you will do it in a query:

SELECT *
FROM tbl_locationcollections
INNER JOIN tbl_locationcollectiontypes
ON typeid.tbl_locationcollections = id.tbl_locationcollectiontypes
WHERE type.tbl_locationcollectiontypes = 'country'

Benefits of this aproach:

-Readability

-Scalable: you can add more types with out changing the database design

-Logic: you work with relations between entities, wich is the same abstraction our brain makes.

Hope it helps ;)
If you want to know how to store one-to-many or many-to-many relations that is another topic, feel free to ask!


A short way to execute a dynamic SQL string I have a table, and a column with a query in it. And I want to update this table, converting the value to the result of it's execution.

This is a well known example of such table actually: all_tab_partitions. It has a column HIGH_VALUE with values like:

TO_DATE('2016-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS').

One could extract the date via substrings or regexps, but may be Oracle-guys think of exactly converting it via dynamic execution.

I've found a rather straight way to do this:

CREATE OR REPLACE FUNCTION PARSE_(STR VARCHAR2)
RETURN DATE
IS
    c NUMBER;
    r INTEGER;
    val DATE;
BEGIN
    c := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(c, 'SELECT ' || str || ' FROM DUAL', DBMS_SQL.NATIVE);
    DBMS_SQL.DEFINE_COLUMN(c, 1, val);

    r := DBMS_SQL.EXECUTE(c);
    LOOP
        IF DBMS_SQL.FETCH_ROWS(c) > 0 THEN
            -- get column values of the row
            DBMS_SQL.COLUMN_VALUE(c, 1, val);
        ELSE
            EXIT;
        END IF;
    END LOOP;

    DBMS_SQL.CLOSE_CURSOR(c);

    RETURN val;

EXCEPTION WHEN OTHERS THEN
    IF DBMS_SQL.IS_OPEN(c) THEN
        DBMS_SQL.CLOSE_CURSOR(c);
    END IF;

    RETURN NULL;
END;
/

SELECT PARSE_('TO_DATE(''2016-01-01 00:00:00'', ''YYYY-MM-DD HH24:MI:SS'')') AS d FROM DUAL;
-- 01.01.2016

And if you want to use it with an all_tab_partitions.HIGH_VALUE, the HIGH_VALUE field has to be converted previously. I've made this in 2 step:

create temp table with TO_LOB(HIGH_VALUE) AS HV field
select from this temp table with PARSE_(TO_CHAR(HV)).


Oracle autotask results of space advisor When querying the dba_autotask_client view on a Oracle 12c database, I can see that the auto space advisor is enabled. Oracle docs says the findings are now implemented automatically. How can I view the results of this autotask?

CLIENT_NAME                      STATUS   CONSUMER_GROUP       MEAN_JOB_DURATION
-------------------------------- -------- -------------------- --------------------------------------------------
sql tuning advisor               ENABLED  ORA$AUTOTASK         +000000000 00:00:00.700000000
auto optimizer stats collection  ENABLED  ORA$AUTOTASK         +000000000 00:00:06.841993315
auto space advisor               ENABLED  ORA$AUTOTASK         +000000000 00:00:01.273776967

This auto task identifies segments that have space available for reclamation and makes recommendations on how to defragment those segments.

You can use DBMS_SPACE.ASA_RECOMMENDATIONS procedure to see the results.

Or you can see the same results in the Oracle Enterprise Manager.

For example:
Go to Advisor Central and Select the Advisory Type as shown in the following screenshot(OEM 11g).


auto-incrementing key in self-referencing table, performance My design contains a table where almost every entry has a parent it has to point to, within the same table (differently put, self references).

    /* scenario executions */
    CREATE TABLE `boteval`.`scenario_executions` (
      `scenario_id` BIGINT NOT NULL COMMENT 'connects to a scenario id',
      `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'execution id',
      `parent_id` BIGINT NULL COMMENT 'execution id of parent executor (null would mean no parent, i.e. a top level execution)',
      `started` DATETIME(6) NOT NULL,
      `ended` DATETIME(6) NULL COMMENT 'can be null while the execution has not yet ended',
      PRIMARY KEY (`id`),
      UNIQUE INDEX `id_UNIQUE` (`id` DESC));

The use case being one where tree structures need to be incrementally recorded into the table, first the parent, than children, children of children, etc, as the program unfolds.

Using an auto-incrementing key for this table, I have reason to believe it would be extruciatingly slow if every insert needed to happen only after its predecessor had already returned its database-assigned auto incremented key.

On the other hand, I think that batching this workload would be way beyond naive, as a sql that strings together the returned ids would become extremely nested for each buffered batch, not to mention the dependencies between batches.

I have several solutions to this, but I wonder if there are very specific design patterns you would recommend. Maybe one of my design constraints would need to be relaxed: maybe the auto-incrementing, if I can safely supply a unique key from my code. In a way, this might not even really be a good fit for a relational database like MySQL.

Thanks in advance for any comments on good patterns for this kind of scenario!

In indexes, DESC is ignored.  (This will be rectified in version 8.0.)
MySQL is happy to use an ASC index (that is, any index) but scan it in descending order.
So DROP that redundant UNIQUE index.

That said, I don't see that anything you have described so far could be cause "excruciatingly slow" inserts.

To further elaborate.  You have 2 BTrees:

One with all the columns, and ordered (ASC) by ID
One with just ID and ordered (ASC) by it.

When inserting a new row (without explicitly specifying ID), the server is optimized to insert it into the "last" block of the BTree and bumping the A_I value.

Meanwhile, the UNIQUE index is acting much the same.  But it is doubling the amount of work to do, with no benefit.

On the other hand, if you have multiple connections doing the inserts, this may be worth noting -- How the inset works:

Get a new A_I value (well optimized)
Check all UNIQUE indexes for duplicates.  This may be slow, since another connection may be locking the adjacent ID.
Insert the row
Throw info into the "Change buffer" for later updating of non-unique indexes.  (This does not apply.)

Also worth noting in InnoDB:

Each transaction has some overhead -- writing to the undo log, writing the "double write" buffer, etc.
Hence, batched insert run much faster because they spread out this overhead over multiple rows.  Can you insert, say, 100 rows in a single INSERT?  That may literally run 10 times as fast.

Do you really need BIGINT?  It takes 8 bytes.  INT UNSIGNED takes only 4 bytes, and allows 4 billion values.


sum of double precision gives weird results I've been making a sum of a column containing about 500.000 records of double precision numbers. All numbers in the database should normally be of two ciphers behind the period. However when making the sum I get 6 numbers after the period: 123123123.549977

Either I have bad data in my database where there are records with more numbers after the period or there is something I'm missing about the sum function.

So my questions are:

Does the sum function have any rounding properties which may cause this?
Is there a way to select all records containing more than two numbers after the period?

This is a common problem with floating point numbers everywhere.

Floating point numbers stored in computer systems should only ever be considered approximations because there are numbers easy to represent in decimal that come out longer than the available precision (sometimes they are in fact never ending) when converted to binary. See ypercube's links and https://stackoverflow.com/questions/588004/is-floating-point-math-broken amongst many other references, for more detail.

The most common example given (as seen in that StackOverflow link) is 0.1+0.2 not resulting in exactly 0.3. You have to apply extra rounding or flexible bounds checking (in both cases reducing the effective precision) to get the behaviour you are expecting.

As your data has a fixed number of decimal places (or a fixed maximum) at 2, you would be much better of using the fixed place decimal/numeric types or similar. These are actually stored and processed as scaled integers avoiding the need for any floating point representation internally so avoiding the approximation problem (all integers can be represented accurately in both base 10 and base 2, assuming you have enough digits/bits). For instance 0.1 and 0.2 might be stored as 1000 and 2000, so the addition results in 3000 which when converted to a string for display becomes 0.3 not the 0.3000000004 you might get out of a floating point calculation, and of course compares accurately to 3 in the same type which would be scaled to 3000.

  Is there a way to select all records containing more than two numbers after the period?

You could convert to string and count the characters after the decimal point (or comma depending on your system local). In TSQL this would find values that are have ended up with more than two decimal places after being stored in a binary float format then translated back to decimal:

SELECT *
FROM   myTable
WHERE  CHARINDEX('.', REVERSE(CAST(floatValue AS NVARCHAR(MAX)))>3

(you may need to tweak function names and related syntax for postgres).


Where is SQLCMD.EXE in SQL Server 2014 Express? Using "SQLCMD.EXE" to back up my SQL Server Express databases for years I just discovered that after installing the 2014 version, I found no SQLCMD.EXE anymore.

In previous versions it was located at

  C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE

But in my 2014 installation, no SQLCMD.EXE exists in

  C:\Program Files\Microsoft SQL Server\120\Tools\Binn

My question:

Any chance to get SQLCMD.EXE into SQL Server Express 2014?

Found this on Microsoft Connect:

  According to this page on MSDN
  http://msdn.microsoft.com/en-us/library/ms143547(v=sql.120).aspx ,
  sqlcmd.exe and bcp.exe are now in < Install Directory >\Client
  SDK\ODBC\110\Tools\Binn

Indeed, sqlcmd.exe and bcp.exe are in that folder in my machine.

Make sure you installed Client SDK Tools.


Best way to backup PostgreSQL cluster before upgrade I noticed that PGDATA folder changes during an upgrade (which is normal as I use the pg_upgrade --links command), but I don't know the best way to backup up my cluster before the upgrade in order to restore it in case of failure.

AFAIK, the several ways to backup my cluster are:

pg_dumpall
pg_basebackupp
tar

I tested pg_dumpall and pg_basebackup, but I've seen that after doing an upgrade and restore, the size and files of PGDATA changed (like the WAL folder for example).

Is this normal and which method is recommended to backup/restore cluster ?

AFAIK, the several ways to backup my cluster is : pg_dumpall, pg_basebackupp or tar.

Also, filesystem instantaneous snapshots if your filesystem/OS has this feature.

For small/medium databases it's probably OK to use any of these methods.

Choose the method which you feel most comfortable with.

With tar/cp backups remember to shutdown cluster before doing the backup.

Logical backups (pg_dump + pg_dumpall) have extra advantage of being portable (to other Pg versions). But the restore is a multi-step process, with Fs/directory it's just a plain replacement of contents.

  I tested pg_dumpall and pg_basebackup but I've seen that after doing
  an upgrade and restore, the size and files of PGDATA did changed (like
  WAL folder for example). Is it normal

Yes, completely normal. I mean - if the difference is 100-fold then probably something fishy is going on, but it's normal that after dump/restore the on-disk size is smaller.


Reporting on a normalized Key-Value pivot ERD & SQL Fiddle provided. We're trying to design a reporting tool for our stakeholders around a normalized Key-Value Pair model. We've tried to find a solution using Pivot Tables but can't seem to find a way to accomplish a query in SQL Server.

http://sqlfiddle.com/#!2/6ed2f (SQL Fiddle's server is having some issues.)

Metaphor for the business problem: we have a collection of surveys that collect responses from users. 1 survey has many questions, 1 question has many responses, each of the responses is from a user, and the user has many responses.

A survey can also generate an output of the surveying experience. Each survey has different output keys. 1 survey has many output keys, 1 output key has many values, and each output value is associated with a user.

The input/output values and other data is tied together to the user through a "Survey Submission".

(Random fact: output values and response/input values are both stored as JSON strings to preserve a dynamic data type.)

The stakeholder would like to see the aggregated values for a given survey.

A solution for just the Inputs would be fine, although a solution that works for Inputs and/or Outputs would be great.

A friend offered me this solution, although he doesn't have an account for SE.

Note from 2 years later: This provides the number of times each answer occurs per question. Which is kind of helpful, but doesn't tell us, "what are all of the questions and answers given on this one survey submission?" Maybe I should have emphasized that 'ResponseValue' can be free-form text.

SELECT SIK.Name AS [QuestionKey], SIV.JsonValue AS [ResponseValue], COUNT(SIV.Id) AS [Frequency]
FROM [Surveys] S
JOIN [SurveyInputKeys] SIK ON SIK.SurveyId = S.Id
JOIN [SurveyInputValues] SIV ON SIV.SurveyInputKeyId = SIK.Id
WHERE S.Id = @surveyId
GROUP BY SIK.Id, SIV.JsonValue, SIK.Name

Psuedo-output...

   SurveyId    |   QuestionKey   |   ResposeValue   |  Frequency
      1        |   Best Singer   |     "Adele"      |     5
      1        |   Best Singer   |   "John Doe"     |     2
      1        |   Best Singer   | "Free Form Text" |     1


Create a DELETE statement that deletes Models that haven’t had a purchase since December 31st 2014 We are given 5 tables.
Orders
    Order number (Primary Key)

    Date of order

    Customer ID (Primary Key for Customers table)

    Serial Number (Primary Key for Machines table)

Customers
    ID (Primary Key)

    Name

    Phone Number

    Street (ie. 505 Ramapo Valley Road)

    City (ie. Mahwah)

    State

    Zip

Machines
    Serial Number (Primary Key)

    Model Number (Primary Key for Models table)

Model
    ID (Primary key)

    Speed

    RAM

    HD

    Price

    Manufacturer ID (Primary key for Manufacturer table)

Manufacturer
    ID (Primary Key)

    Name

    Phone Number

    Email Address

Our task is to create an SQL statement which will delete tuples from the Models table which haven't had a purchase since December 31st 2014.
The following is what I have managed to come up with thus far:
DELETE FROM Model

WHERE ID NOT IN

(

    SELECT Model.ID

    FROM Model

    INNER JOIN Machines

        ON Model.ID = Machines.ModelNo

    INNER JOIN Orders

        ON Machines.SerialNo = Orders.SerialNo

    WHERE OrderDate > 2015

    GROUP BY Model.ID

);

Please let me know how I can fix this SQL statement, I believe the problem is either in the first or second line or possibly both.

Your main issue is you're using an INNER JOIN between Machines and Orders which means only return the rows that match (in other words, only the rows where Orders exist). Rather you should use an OUTER JOIN, in this case a LEFT OUTER JOIN (which can simply be written as LEFT JOIN) so you get all the rows of Models and Machines, and the ones that don't have Orders will show a NULL value for the columns in the Orders table. Then you can use the WHERE clause to filter out anything but the rows without Orders by saying WHERE Orders.SerialNo IS NULL like so:
DELETE FROM Model
WHERE ID IN
(
    SELECT Model.ID
    FROM Model
    INNER JOIN Machines
        ON Model.ID = Machines.ModelNo
    LEFT JOIN Orders
        ON Machines.SerialNo = Orders.SerialNo
        AND Orders.OrderDate >= '2015-01-01'
    WHERE Orders.SerialNo IS NULL
    GROUP BY Model.ID
);

The other issue you had was your OrderDate field is likely storing more than just the year (I'm assuming), so you need to specify the whole date (not just the year) when you do a direct comparison. I fixed that in the above query as well.
Please read the resource I linked above on LEFT JOINS along with the other resources I provided you on your last post, specifically understanding the different join types and when to use them are very important concepts.


Generating SQL with SQL I'm trying to generate SQL statements using SQL to create SQL code that will be highlighted and run by an admin to process the statement. My issue is with the code I have created. It's not efficient at the moment as it is using fixed values.

Is there a way to get the code to auto generate a script that produces several statements for users, by looping through my table of users and extracting the usernames, placing these values into the declared variable U_USERNAME?

Would I have to use a SELECT INTO the U_USERNAME variable? How would that look syntax wise in this instance?

DECLARE
u_perm VARCHAR2(25);
u_table VARCHAR2(25);
u_username VARCHAR2(25);
u_code VARCHAR2(2000);

BEGIN
u_perm := 'INSERT';
u_username := 'J123';
u_table := 'TABLE_NAME';
u_code :='';
dbms_output.put_line('Grant ' || u_perm || ' on ' || u_table || ' to ' ||
u_username || ';' );

u_code := ('Grant ' || u_perm || ' on ' || u_table || ' to ' || u_username ||
';') ;
dbms_output.put_line ('u_code = ' || u_code|| '');

END;

---The user table that will store the username I will being trying to extract from.

CREATE TABLE  tblss_user(
user_id       INTEGER NOT NULL,
user_name     VARCHAR(15),
reg_date      DATE

You should be GRANT-ing to a ROLE, not the individuals.
One exception to this:  one Schema is creating code that reference another schema's table.

Grant to Multiples schemas/roles at once

This is legitimate code:
GRANT INSERT ON SCOTT.EMP TO ADAMS,ALLEN;

If you are on 11g or higher, you can use LISTAGG() to generate the comma separated list

declare
  local_variable  varchar2(2000);
begin
  select listagg( e.ename,',' ) within group (order by e.empno)
    into local_variable
  from scott.emp e
  where e.ename like 'A%';
end;
/

But, you'll need to limit the number of rows.  LISTAGG will error if it is over a certain length (eg 4000 bytes for 11g)

LOOPing

You'll want to LOOP over your table:

begin
  for curr in ( select e.ename from scott.emp e order by e.ename)
  loop
    dbms_output.put_line( 'grant for ' || curr.ename );
  end loop;
end;
/

Master's Technique

OK, ok.  This is my technique.  (maybe others)

Use one of the many freely available template based code generators.
This is probably only useful if you are going to do this on a repetitive cycle as new tables are created.

Again, you should be granting to a ROLE, not individual users.


Scheduling index rebuild jobs I have a MS-SQL 2014 SP1 - two node AlwaysOn AG (One Primary and an read-only secondary)  with about 10 databases sizing 20-30GB in total. I have some queries regarding scheduling index rebuild and stats gathering.

I have planned to schedule it on weekly basis through maintenance plans. Usually on sunday nights where there will be no load at all on the primary.

Should I run the job in primary or in replica? If I run it only on secondary then what benefits do I get? or vice versa? (Meaning should I run on both the nodes)

Is corruption likely to happen if I use SP1 and index rebuild via maintenance plans?
As mentioned here

You should use smart index maintenance scripts as opposed to maintenance task.

  Should I run the job in primary or in replica?

Yes, as they get replicated on secondary replicas.

  If I run it only on secondary then what benefits do I get?

you cannot run on secondary replica.

Index maintenance generate a lot of transaction log activity that affects your RTO and RPO. Also, it increases the redo backlog on secondary replicas.

You should follow Recommendations for Index Maintenance with AlwaysOn Availability Groups :

Find a quiet time or during planned maintenance time run your index maintenance.
If possible switch from Synchronous mode to Async mode. Once index maintenance is done, switch it back to what it was.
Take frequent log backups so that the log file does not grow out of control.
Since you are on sql server 2014, you should use WAIT_AT_LOW_PRIORITY, MAX_DURATION, and ABORT_AFTER_WAIT
If you are using online index rebuilds, then use MAXDOP = 1

Also, note that a Partitioned Table May Limit the Runtime MAX DOP of Create/Alter Index

  Is corruption likely to happen if I use SP1 and index rebuild via maintenance plans?

Since you are on SP1 (12.0.2370 or greater) , you should be fine.


Creating the same unique ID in multiple tables I have two tables of data; one table is the Employee Master table containing a list of unique employee names, and the other table contains transactional data with multiple records for each employee.

There is no unique number that ties the two tables together. I would like to assign the same unique ID to the employee names within each table.

For the Employee Master table, I was able to do this with this code:

select employee_name, row_number() Over(order by employee_name asc) as employee_id
from HREmployeeMaster
group by employee_name

However, no matter what I try, for the transaction table, I cannot assign one ID to the distinct employee names. Instead, I keep getting an ID for each record in the table.

Any advice would be appreciated.

Add EmployeeId  to columns to both tables.  Populate the master EmployeeId  with the query you have, and join the transaction details table to the employee master on EmployeeName  and lookup the EmployeeId .

eg

with q as
(
   select e.EmployeeId master_EmployeeId, t.EmployeeId
   from HREmployeeMaster e
   join EmployeeTransactions t
     on e.EmployeeName = t.EmployeeName
)
update q set EmployeeId  = master_EmployeeId;


How can I minimize impact of expensive queries in Database A on Databases B, C, D How can I configure SQL Server so that activity in 'Database A" has  minimal impact on users in 'Database B'

The Context

Our (very large) production SQL Servers have a lot of databases on them running various "legacy apps", each database for a particular customer's instance of an application.

The Problem

If a "rogue user" runs a very expensive query--sometimes from a report, other times from the application--it will cause "the other databases" to slow to a crawl.

[I understand that the 'root problem' is the expensive queries; programmers should not allow them to run. Intensive reports should run off a separate reporting databases. Programmers should test on large data set, etc. My question pertains to 'isolating' impact of the databases on each other.]

I suspect, but I have not confirmed that the problem lies in the 'tempdb', the temporary area/database SQL Server uses for sorts it can't fit in memory.

I've seen posts pointing to spreading tempdb over multiple files. Would this help?

https://stackoverflow.com/questions/719869/how-to-spread-tempdb-over-multiple-files
http://logicalread.solarwinds.com/sql-server-tempdb-best-practices-multiple-files-w01/#.VdzGxrNv4Uw

I would recommend taking a look at the resource governor...
In an environment where multiple distinct workloads are present on the same server, Resource Governor enables you to differentiate these workloads and allocate shared resources as they are requested, based on the limits that you specify. These resources are CPU, physical IO, and memory.
By using Resource Governor, you can:

Provide multitenancy and resource isolation on single instances of
SQL Server that serve multiple client workloads. That is, you can
divide the available resources on a server among the workloads and
minimize the problems that can occur when workloads compete for
resources.
Provide predictable performance and support SLAs for workload
tenants in a multi-workload and multi-user environment.
Isolate and limit runaway queries or throttle IO resources for
operations such as DBCC CHECKDB that can saturate the IO subsystem
and negatively impact other workloads.
Add fine-grained resource tracking for resource usage chargebacks
and provide predictable billing to the consumers of the server
resources.

https://msdn.microsoft.com/en-us/library/bb933866.aspx


Are there advantages to using temporary tables over derived tables in SQL Server? I read the derived tables have better performance than temporary tables, but anyway many SQL Server developers prefer the second ones. Why?
I must do queries with large data (millions records) and I want to be sure I am using the best choice.

CREATE TABLE A(
    id BIGINT IDENTITY(1,1) NOT NULL,
    field1 INT NOT NULL,
    field2 VARCHAR(50) NULL,
);

CREATE TABLE B(
    id INT IDENTITY(1,1) NOT NULL,
    field1 VARCHAR(10) NULL,
    field2 INT NULL
);

INSERT INTO A
    (field1,field2)
VALUES
    (1,'a'),(2,'b'),(3,'c'),(2,'d'),(5,'e'),
    (6,'f'),(7,'g'),(8,'h'),(9,'i'),(2,'j');

INSERT INTO B
    (field1,field2)
VALUES
    ('a',1),('b',2),('c',3),('d',4),('e',5),
    ('f',6),('g',7),('h',8),('i',9),('j',2),('k',3);

DECLARE @begin INT=0,@end INT=200;

Derived tables

/*derived tables*/
SELECT
    C.id,C.field1,C.field2,C.field3
FROM
(
    SELECT
        A.id,A.field1,A.field2,B.field2 AS field3,
        ROW_NUMBER() OVER (ORDER BY A.id) AS iRow
    FROM
        A INNER JOIN B ON A.field1=B.id
) C
WHERE iRow BETWEEN @begin AND @end;

Temporary tables

/*temporary tables*/
CREATE TABLE #C (
    iRow INT IDENTITY(1,1),
    id bigint,
    field1 INT,
    field2 VARCHAR(50),
    field3 INT );

INSERT INTO #C
    (id,field1,field2,field3)
SELECT TOP 1000
    A.id,A.field1,A.field2,B.field2
FROM
    A INNER JOIN B ON A.field1=B.id
ORDER BY
    A.id;

SELECT id,field1,field2,field3
FROM #C
WHERE iRow BETWEEN @begin AND @end;

DROP TABLE #C;

@user16484 already directed you to Which one have better performance : Derived Tables or Temporary Tables in the comment.

Also see Temp Table 'vs' Table Variable 'vs' CTE. which also covers derived tables.

A quick summary: #temp tables can be indexed, can have UNIQUE indexes/constraints, can be references more than one time in the same query, can be referenced (FROM or JOIN) by more than one query.  Derived tables can be referenced (FROM or JOIN) once in one query.

Performance-wise, pull out Profiler for SQL:BatchCompleted and RPC:Completed, watch the Read, Write, CPU, and Duration columns, and see what a few runs of derived tables vs. #temp tables vs. indexed #temp tables does for each particular query.

In general - if you're going to use it more than once, #temp table wins.  If you're joining a lot of tables, #temp table probably wins.  If you're joining only a few tables, derived table has a reasonable chance of winning.  Benchmark it!


How to find historical sql_id executed by users I'm trying to find historical sql_id executed by users within interval between snapshot_id from AWR. I'm using the view DBA_HIST_ACTIVE_SESS_HISTORY,DBA_USERS and DBA_HIST_SNAPSHOTS to try find the username,sql_id,sample_time etc.. but after some tests neither all sql executed was listed in output of the following query

col username for a30;
col BEGIN_INTERVAL_TIME for a30;
col END_INTERVAL_TIME for a30;
COL SAMPLE_TIME FOR A30;
set linesize 2000;
select * from (
select b.BEGIN_INTERVAL_TIME,b.END_INTERVAL_TIME,u.username,a.SESSION_ID,a.SESSION_SERIAL#,
a.INSTANCE_NUMBER,a.sql_id,A.SAMPLE_TIME from DBA_HIST_ACTIVE_SESS_HISTORY a
inner join dba_users u
on u.user_id=a.user_id
inner join dba_hist_snapshot b
on b.snap_id=a.snap_id
where b.snap_id >= 40 and b.snap_id <= 46
order by BEGIN_INTERVAL_TIME desc) where rownum <= 50;

Why is this happening? Is there some other way to do it?

AWR captures only the top N sql for each category (Elapsed time, CPU time, I/O time, Gets, Reads, etc.).

If you set this limit to MAXIMUM with DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS, even that does not guarantee capturing all SQLs as they may age out from the shared pool between taking AWR snapshots.

If you want all SQLs to be logged, you can use SQL tracing (Monitoring and Tracing SQL) or Auditing (Monitoring Database Activity with Auditing) with the settings that fulfill your requirements.


Sql statement to compare two table I have two table with the same structure, i want an sql statement to compare them and see any modified line, deleted or added

Table Product1 :

 ________________
|Product  | Price|
|Product1 | 10   |
|Product2 | 8    |
|Product3 | 9    |
|________________|

Table Product2 :

 ________________
|Product  | Price|
|Product1 | 11   |
|Product2 | 8    |
|Product3 | 9    |
|Product4 | 13   |
|________________|

Table result :

 ________________
|Product  | Price|
|Product1 | 11   |
|Product4 | 13   |
|________________|

You want something like this (see below for DDL and DML). This sort of query would be easier with the likes of EXCEPT and MINUS, but we'll have to wait for those...

-- first query gets price difference between tables
SELECT p1.* FROM product1 p1, product2 p2
WHERE p1.pname = p2.pname
AND p1.price <> p2.price
-- second query finds extra products in product2 table - unions both of them.
UNION
SELECT p2.* FROM product2 p2, product1 p1
WHERE p2.pname NOT IN(SELECT pname FROM product1);

+----------+-------+
| pname    | price |
+----------+-------+
| Product1 |    10 |
| Product4 |    13 |
+----------+-------+

Or - for generality, note where I've added 'Product5', 17 to the product1 table? To get the differences both ways you need to perform the UNION of 3 queries

SELECT p1.* FROM product1 p1, product2 p2
WHERE (p1.pname = p2.pname
AND p1.price <> p2.price)
UNION
SELECT p1.* FROM product1 p1, product2 p2
WHERE p1.pname NOT IN (SELECT pname FROM product2)
UNION
SELECT p2.* FROM product2 p2, product1 p1
WHERE p2.pname NOT IN(SELECT pname FROM product1);

which gives

+----------+-------+
| pname    | price |
+----------+-------+
| Product1 |    10 |
| Product5 |    17 |
| Product4 |    13 |
+----------+-------+

CREATE TABLE product1
(
  pname VARCHAR(15),
  price int,
  PRIMARY KEY (pname)
);

INSERT INTO product1 VALUES('Product1', 10);
INSERT INTO product1 VALUES('Product2', 8);
INSERT INTO product1 VALUES('Product3', 9);
INSERT INTO product1 VALUES('Product5', 17);

CREATE TABLE product2

(
  pname VARCHAR(15),
  price int,
  PRIMARY KEY (pname)
);

INSERT INTO product2 VALUES('Product1', 11);
INSERT INTO product2 VALUES('Product2', 8);
INSERT INTO product2 VALUES('Product3', 9);
INSERT INTO product2 VALUES('Product4', 13);


What is the best practice for treating fields with only two possible values? A column/field will only have 2 possible values, either "Transplanted" or "Direct-seeding". Is it better to create a table for these options and give them unique IDs or are they better off put as raw string values in the table?

I would start with a check constraint that limits the column to those two values.

If the table containing those values is expected to get really big (tens or hundreds of millions of rows), then using a lookup table and only storing a foreign key reference as a number might save some space and might increase performance if you don't need that value all the time.

Plus it will make adding new values easier because you don't need to re-create the check constraint.

If the end-user is expected to maintain the list of allowed values, then a check constraint is not an option at all.

Another thing to consider is localization (translating those labels). If you have to translate those display values into other languages, then using a lookup table is the better way to go.


How to drop a DB2 instance when the instance owner was removed This is a real sticky situation. I was handed over a machine (running an AIX 7.1), and my first task was to re-install DB2 server on it. But someone before me had conveniently removed an instance owner account, and probably recreated it. Now, the problem is this:

1) When I try to uninstall DB2, it says the instance is active and has to be dropped first.

2) When I try to drop this instance, DB2 says there is no such instance.

I am quite new to DB2 administration. Not sure how to proceed here. Any help is appreciated

Thanks

If you dropped the instance from the file system (perhaps while DB2 was down with db2stop), then, DB2 still "knows" about the instance because its directories contain a reference to it.

I would perhaps try to uncatalog the instance first (this would remove it from the directories).

Try the following:

db2 uncatalog node <instance name>;
db2 terminate;

And see if that doens't help. You may also need to remove the instance definition from the DB2 registry:

# make sure there is a space after the equals sign to unassign this variable.
db2set DB2INSTDEF= -g
db2stop
db2start

As with anything, try to take a backup of everything before you mess around so you don't make it worse.


How do I list all available encoding types available in postgreSQL? SELECT datname, pg_encoding_to_char(encoding)
FROM pg_database;

…lists all the databases, each with its encoding type.

However, I am trying to find out what are all the encoding types available in the PostgreSQL server. Can I query for all available encoding types?

Or are the only encoding types ever available listed in Chapter 23.3 Character Set Support?

Or are the only encoding types ever available listed in Chapter 23.3 Character Set Support?

Yes. You can see the full list in the source code, if you insist

That said, it's highly abnormal to use any of these encoding except UTF-8 as an internal storage format. Unicode should support all of the useful characters in all of the other encodings. If you find UTF-8 acceptable, the only purpose these other encoding serve is to eliminate the need to create an ETL script for the COPY statement.


Attacks on Postgresql listening for requests on 'localhost' I am using jboss server for my servlets and the postgresql as the database server, listening on port 5432 on localhost. My jboss server is bound to a public IP. I want to know that since my postgesql is bound only to the localhost, is there any chance of an attack at all. Will any one be able to see the all the table data even if they have the user name and password for the database?

Starting by securing PostgreSQL according to Erwins answer is a good start. However, there are other ways to attack and gain access to data. Even if you are after data as an attacker, one wouldn't assume that the database is directly accessible from the internet.

But data might be accessible indirectly due to flaws in the application server, in this case JBoss, and its configuration or in the application code (sql injection).

JBoss had a seriuos security vulnerability about a year ago that allowed you to gain root access - and as a consequence full access to any PostgreSQL server on localhost.
http://www.darknet.org.uk/2011/02/jboss-autopwn-jsp-hacking-tool-for-jboss-as-server/

UPDATE:

Yes, Ashwin, using prepared statements or stored procedures/functions is a good way to minimize the risks of sql injections attacks. OWASP has a check list for prevention of SQL injections.
https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Defense_Option_1:_Prepared_Statements_.28Parameterized_Queries.29

One advantage of stored procedures/functions is the granular control of permissions. For example you can grant access for the application user to only be able to run specific procedures/functions, whilst access is denied to the underlying tables and other objects. The procedure/function is instead run by the permission of the owner of the procedure/function (a user also with restrictive permissions, just able to operate on the specific tables). This setting can be controlled with SECURITY DEFINER, as in this example function.

CREATE OR REPLACE FUNCTION myschema.myfunction() RETURNS void AS
$BODY$
BEGIN
    INSERT INTO myschema.mytable(msg) VALUES('value goes here');
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
ALTER FUNCTION myschema.myfunction() OWNER TO myinsertupdateuser;
GRANT EXECUTE ON FUNCTION myschema.myfunction() TO myappuser;

This function can be executed by myappuser, but myappuser can not access myschema.mytable directly. In other words myappuser can only run the code you specify in the function. The function is run as myinsertupdateuser, which is assumed to have insert permission on the myschema.mytable table.


MySQL 5 on Mac 10.5: "problem connecting to the database" I have worked with MySQL for years but I am stuck on a seemingly trivial problem.

I installed MySQL 5.0.96 on Mac 10.5, created a database, created a user with privileges on everything, and problem:
Apache/PHP (osCommerce) installed on the same machine says:

There was a problem connecting to the database server. The following error had occured:
Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2)
Please verify the connection parameters and try again.

It is my first time installing MySQL on Mac, and there might be a bug with %, so I added for all addresses I could imagine (%, localhost, 127.0.0.1, my IP) but no luck:

mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'monty'@'127.0.0.1' IDENTIFIED BY 'some_pass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'127.0.0.1' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER 'monty'@'192.168.43.152' IDENTIFIED BY 'some_pass';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'192.168.43.152' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

Default install, I did not change any parameter.
skip_networking is OFF.
The Mac firewall is set to accept anything.

Am I doing something wrong?

I have found the problem:
Somehow, MySQL decided to create mysql.sock in /private/tmp rather than in /var/mysql.

So, my solution was to create a symlink:

cd /var
sudo mkdir mysql
sudo ln -s /private/tmp/mysql.sock mysql.sock

Now everything works fine.