texts: Very slow IndexOnlyScan on partial ~16MiB indexes

texts:
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


texts: Application unable to connect after doing a failover ,Mirroring

texts:
After doing a failover to mirror database, the application is throwing below error
Login failed for user 'NT Authority\anonymous logon'. reason: could not find a login matching the name provided

In error log it is logging
Login failed for user 'xxx'.Reason: failed to open the explicitly specified database ' xxx'. Error 18456,severity:14,state:38

I know this question is duplicated and i found many threads, but i don't find the solution in them or may be i'm unable to understand that.

Please help me with the steps to resolve.


texts:
I'm using postgresql and I have a large database.  A simplified version of the datastructure for my table looks like this:

id (primary unique key), data, userID
1, foo, 4
2, foobar, 4
3, lala, 5
4, boo, 6
etc...

I want to find all the rows where the userID value is unique in the entire table structure.  So in the above example, it would return the rows for id's 3 and 4.  If the userID is listed in two or more rows, I just want to discard it.

Is this possible in one query?

Thanks in advance.


texts: Inside stored procedure, set column names of returned query according to variable

texts:
I have a query which I'd like to reuse, instead of having many different copies of it just for the sake of changing the column name.

The report (for example) uses the same query, but there are either dogs or cats in
the table, and if there are cats then it returns 'Nr of Cats', and if there are dogs then 'Nr of Dogs' as heading for the returned table.

declare @Animals table (
  AnimalType varchar(20), Name varchar(20)
  )

insert into @Animals(Type, Name) values('Cat', 'Garfield')

declare @AnimalType varchar(20)

set @AnimalType = (Select Top 1  AnimalType from @Animals )

select
    Case when @AnimalType = 'Cat'
           then 'Nr of Cats'
           else 'Nr of Dogs' = count(*)  from @Animals


texts: Which MySQL server parameters should be adjusted to improve INSERT performance for a large MyISAM table?

texts:
I have a MyISAM table with roughly 20M rows for which inserting 1000 rows takes about 40 seconds on the production server (Ubuntu 12.04, MySQL 5.6.35). On the test machine (Windows 8.1, MySQL 5.6.22), the same insert takes less than 1 second which seems a more reasonable timing for me. On the production server inserts of ~1000 rows happens every 5 minutes. Which my.cnf configuration values should I try to change to improve performance on the production server?

Note: I've already increased key_buffer_size=256M (and even to key_buffer_size=1024M) which didn't change anything.

This is how the table definition looks like:

CREATE TABLE `mytable` (
  `id1` varchar(40) NOT NULL,
  `t` datetime NOT NULL,
  `id2` varchar(12) NOT NULL,
  `a` varchar(8) NOT NULL,
  `b` int(11) NOT NULL,
  `f1` varchar(4) DEFAULT NULL,
  `f2` varchar(5) DEFAULT NULL,
  `f3` varchar(16) DEFAULT NULL,
  `f4` varchar(16) DEFAULT NULL,
  `f5` tinyint(4) DEFAULT NULL,
  `f6` tinyint(4) DEFAULT NULL,
  `f7` tinyint(4) DEFAULT NULL,
  `f8` tinyint(4) DEFAULT NULL,
  `f9` tinyint(4) DEFAULT NULL,
  `f10` tinyint(4) DEFAULT NULL,
  `f11` tinyint(4) DEFAULT NULL,
  `f12` tinyint(4) DEFAULT NULL,
  `f13` varchar(8) DEFAULT NULL,
  `f14` int(11) DEFAULT NULL,
  UNIQUE KEY `id1` (`id1`,`t`,`id2`),
  KEY `t` (`t`),
  KEY `id2` (`id2`),
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

id1 and id2 are random values, t is a timestamp representing web user local times, so from a coarse perspective it's constantly increasing, but within a bunch of 1000 rows times may vary +/- 1 day.

Update 1

Rows will only be inserted into the table, never updated. Hence, there is no fragmentation, as expected:

mysql> select ENGINE, TABLE_SCHEMA, TABLE_NAME, DATA_LENGTH, INDEX_LENGTH, DATA_FREE from information_schema.tables;
+--------+--------------+----------------+-------------+--------------+-----------+
| ENGINE | TABLE_SCHEMA | TABLE_NAME     | DATA_LENGTH | INDEX_LENGTH | DATA_FREE |
+--------+--------------+----------------+-------------+--------------+-----------+
| ...                                                                             |
| MyISAM | products     | mytable        |  2301965084 |   2030800896 |         0 |
| ...                                                                             |
+--------+--------------+----------------+-------------+--------------+-----------+

Update 2

I've 'cloned' mytable to mytable2 for testing purposes and running test INSERTs into mytable2. In general I'm seeing better performance here: roughly 8 seconds for inserting 1000 rows (after some initial startup time). So, (1) looks like I'm encountering OS level caching issues for my production table. (2) When changing the random id1 to an increasing value, inserting 1000 rows takes roughly 400ms.

Update 3

After changing indexes to:

  UNIQUE KEY `t` (`t`,`id1`,`id2`),
  KEY `id1` (`id1`),
  KEY `id2` (`id2`)

inserting 1000 rows with random t, id1 and id2 keys, this takes roughly 500ms after some initial startup time: inserting first batch took 40000ms, second batch took 18000ms, ... 10th batch took 500ms. Hence, the initial question comes up again: is there any my.cnf setting to improve this startup delay?


texts: Given a table containing class and students, need to find the diff in students

texts:
I have class_students table where given 2 class ids I need to find the diff in students.
This is sql fiddle that I used,
http://sqlfiddle.com/#!9/dba48e/11
Any help here.


texts: Differential backup successfully completes without taking the backup of few databases

texts:
I have differential backups for multiple databases configured to run through a stored procedure.  Sometimes one or more databases have not been backed up, even though the job was "successful."

I'm calling the backup through a stored procedure that runs scheduled in a job.

I have created the output file in the job, but it is not saying anything specific.

I've checked the SQL Server logs, event logs, changed the timings of the job, however I'm still not able to get the desired result nor any specific reason.

No recovery model change has happened.

The log backup job is running fine without any issues.

SQL Server version is SQL Server 2008 R2 + SP1 on Windows Server 2008 R2.


texts: De-duplicate rows in place vs de-duplicate on transfer

texts:
Doing what: Loading data from many flat table CSV files into their final destination, a "Target" table.

Using: TSQL / SQL Server 2008

Goals:
Don't include "duplicate" rows
What are duplicates in this context? Two scenarios:

For some tables, if two rows have the same value in all columns. ex  "*"
For other tables, if two rows have the same value in
some columns  ex. "COL_A, COL_B, COL_C"

I know which tables fall into which scenario, and in scenario 2, which columns are the some.

Current Strategy:

Load "raw" data in batches (i.e. 1-5.csv, 5-10.csv, etc) into separate "Staging"
database
Dynamically create a TSQL script that transfers from
staging to target removing duplicates both...

Within the staged data
itself
And between the staged data, and the target database where
it's being transferred

Execute the transfer TSQL script (in batches corresponding to each Staging Database) to move to a single Target database

For each of the two scenarios (i.e. whether a Table's duplicates is based on all columns (1) or some columns (2)), I have a different transfer query. Here is a pattern of each, respectively:

INSERT INTO TARGET.TABLE1 SELECT * FROM STAGING.TABLE1 EXCEPT SELECT * FROM TARGET.TABLE1
INSERT INTO TARGET.TABLE2
SELECT * FROM (
SELECT STAGING.*,
ROW_NUMBER() OVER (PARTITION BY STAGING.COL_A, STAGING.COL_B, STAGING.COL_C) AS ROW_NUMBER
FROM STAGING.TABLE2 as STAGING
LEFT OUTER JOIN TARGET.TABLE2 TARGET
ON ISNULL(STAGING.COL_A, TARGET.COL_A) IS NULL OR STAGING.COL_A = TARGET.COL_A
{... repeat for COL_B and COL_C }
WHERE TARGET.COL_A IS NULL)
as T WHERE ROW_NUMBER = 1

I can add unique constraints if that would help.

My questions:

Is there anything wrong with overall approach of using staging databases to store "raw data", and transferring batches of that stage into a target, eliminating duplicates within stage and between stage+target during the transfer.

I was thinking it might be more efficient to just load all raw data into its final destination, and "de-duplicate" "in-place" (though I don't know what this looks like)

If the general approach is sound, are the above queries OK? Is "EXISTS" a safe clause for scenario 1? Is the ROW_NUMBER PARTITION BY and JOIN BY the some columns a fast way to avoid transferring duplicates in scenario 2?


texts: SQL database backups methods-tools-procedures

texts:
Currently, I am investigating some way to more efficiently backup 4 Production SQL Databases (3 different servers/instances) and would like to know if anyone else has had the problems we are having and if anyone has any recommendations.

Database 1: 80GB
Database 2: 74GB
Database 3: 5GB
Database 4: 30GB

Database 1 & 2 are still currently SQL Server 2005 with no plans on upgrading them right now

Database 3 & 4 are for new systems and will be growing rapidly over the next several months. Currently Mirrored.

The problem we are running into is that there are maintenance tasks on the servers that run for these databases for backups that take roughly 30 min for Database 1 & 2. Now these all run at the same time as several other environment backups that the Sys Eng have running.

After the backup, a schedules task using 7-zip is run to compress the backups. All of this is killing the CPU during that time frame and if for whatever reason a job/task gets locked up, it will affect the entire system.

Now due to the business we are in, we have a very limited timeframe we can run these backup jobs, and these backups need to be done. We also need to start encrypting the backups after the compression to further our HIPPA compliance... yep...

Another part of the problem that we are are aware of is we have 20 other databases doing the same thing at the same time, which is killing the SAN, all of which we know and believe don't need these backup tasks.

So aside from that which we are working on, we are trying new methods of backing up databases to try to take the CPU load off the database servers as much as possible and hopefully do the backups for the databases incrementally.

Any suggestions? Tools, methods? We've tested Red-Gates SQL backup pro tool, SQL backupandftp freeware and using SSIS but none have seemed to accomplish what we were hoping for (ability to quickly accomplish the task and/or run on another server taking the CPU off the DB server).


texts: If at least a negative number, sum; otherwise just show

texts:
I have a need to sum values together only if there is a negative value present in a partition.  If there are no negative values in a partition, it should just output the row.

Here's what I have right now.  Initial data is provided as a CTE.

DML

;with ledger as (
    select accountId, type, amount
    from (
        values
         (1, 'R', -10)
        ,(1, 'V', 10)
        ,(1, 'R', 30)
        ,(2, 'R', 20)
        ,(2, 'R', -5)
        ,(2, 'V', 5)
        ,(3, 'R', 20)
        ,(3, 'R', 30)
    )   x (accountId, type, amount)
)
,b as ( --identifies accountid, type with negatives
    select
        accountid
        ,type
    from ledger
    group by accountid, type
    having min(amount) < 0
)
,onlyPositives as (
    select
         l.accountid
        ,l.type
        ,l.amount
    from ledger l
    left join b on b.accountid = l.accountid
                and b.type = l.type
    where b.accountid is null
)
,aggregatedNegatives as (
    select
         l.accountid
        ,l.type
        ,amount = sum(l.amount)
    from ledger l
    inner join b on b.accountid = l.accountid
                and b.type = l.type
    group by l.accountid, l.type
)
select  accountid, type, amount
from    onlyPositives

union all

select  accountid, type, amount
from    aggregatedNegatives

I'm expecting an output like this, and the query above outputs correctly.

1, R, 20  (summed because -10+30=20)
1, V, 10  (left alone)
2, R, 15  (summed because 20-5=15)
2, V, 5   (left alone)
3, R, 20  (left alone)
3, R, 30  (left alone)

The query so far looks beastly and feels unnecessarily complex.  Is there a simpler query I could write that I have overlooked?

This is a slight variation on the issue: If positive, sum all items. If negative, return each one

rextester -> https://rextester.com/EZRT33825


texts: What steps are required to move data from a custom schema in one database to the default schema in new database

texts:
Lets assume for the sake of argument that I have the following tables in a single SQL Server database.

mySchema.Users
mySchema.Products
--
secondSchema.Users
secondSchema.Contracts
secondSchema.ContractItems
--
oldSchema.People
oldSchema.Table1
oldSchema.Table2
oldSchema.Table3
oldSchema.Table4
--
and so on and so forth

Each "schema" is fully independent of the others. The only reason they are in a single database, is due to hosting and cost limitations. In a perfect world these would have been in separate databases to begin with.

What things will I need to do when (and if ever) I need to move secondSchema into a new database, all by itself because its usage goes up and it now requires being housed in its own database.

I have reviewed Decision criteria on when to use a non-dbo schema vs a new Database, and as I've stated above, there is no relational or transactional justification for them being in the same database. They are in there for cost reasons.


texts: I would like to find some materials and real life practice about performance tuning queries. Can anyone recommend some resources to read and study?


texts:
What are ways in which teams handle database change management? I have a team of 8 developers making database changes concurrently.

Each developer is responsible for updating their changes in what we consider a "poor man's database change log" which is essentially a shared file referencing a script. Sometimes the scripts are database alters. Other times the script re-creates a view due to a change (which may have also been changed by another developer later on).

It always happens that scripts are placed out-of-order, listed in duplicate, etc.

My gut tells me we are doing this wrong. How can we do this better?

If it matters, we are using Postgres + git.


texts: Reducing execution time for this gnarly SQL

texts:
I'm creating an order system of some kind.  There is a function that I use to get orders (in PHP) and retrieve the required information about the order.  However, it seems that I am pulling a lot of information, including several left joins that could be the victim of executing for a period of more than 3 seconds.

The amount of rows in each table that is used.

Orders - 2,485
Order items - 27,321
Order payments - 3
Customers - 68
Customers' addresses - 153 (joins twice for invoice and delivery address)
Order statuses - 2,277
Status types - 5
Recurring orders data - 46

I know that you will need to use EXPLAIN <query> to debug the query.  It seems that it does not use any of the indexes that I have placed on the tables (basically there is an index on every column that I am joining, apart from the PRIMARY KEY columns)

This is the debug:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY     <derived2>  ALL     NULL    NULL    NULL    NULL    2302    Using filesort
2   DERIVED     <derived3>  ALL     NULL    NULL    NULL    NULL    2302    Using temporary; Using filesort
3   DERIVED     <derived4>  ALL     NULL    NULL    NULL    NULL    2484
3   DERIVED     o   eq_ref  PRIMARY     PRIMARY     4   oi.order_id     1   Using where
3   DERIVED     <derived6>  ALL     NULL    NULL    NULL    NULL    3
3   DERIVED     c   eq_ref  PRIMARY     PRIMARY     4   orders_system.o.customer_id     1
3   DERIVED     <derived7>  ALL     NULL    NULL    NULL    NULL    153
3   DERIVED     <derived8>  ALL     NULL    NULL    NULL    NULL    153
3   DERIVED     <derived9>  ALL     NULL    NULL    NULL    NULL    13
3   DERIVED     <derived10>     ALL     NULL    NULL    NULL    NULL    2277
10  DERIVED     os  ALL     NULL    NULL    NULL    NULL    2277    Using temporary; Using filesort
10  DERIVED     st  index   PRIMARY     PRIMARY     1   NULL    5   Using where; Using index; Using join buffer
9   DERIVED     recurring_orders    ALL     NULL    NULL    NULL    NULL    46  Using where; Using filesort
8   DERIVED     customer_addresses  ALL     NULL    NULL    NULL    NULL    153
7   DERIVED     customer_addresses  ALL     NULL    NULL    NULL    NULL    153
6   DERIVED     order_payments  ALL     NULL    NULL    NULL    NULL    3   Using where; Using temporary; Using filesort
4   DERIVED     <derived5>  ALL     NULL    NULL    NULL    NULL    2484    Using temporary; Using filesort
5   DERIVED     oi  ALL     order_id    NULL    NULL    NULL    27321   Using where; Using temporary; Using filesort
5   DERIVED     o   eq_ref  PRIMARY     PRIMARY     4   orders_system.oi.order_id   1

Visual:

I am not sure why the query is executing a full table scan, this is problematic if it is taking that long to run, especially if orders are about to increase daily.

This is the query that is used to execute, it's too big to view here.

What can I do to improve this query?


texts:
Right now I am using ts_rank (with ts_vector and ts_query) to search strings for relevance. While this is doing a great job for me so far. I was hoping to work in a bit of fuzzieness into the searching.

As it stands the search is done by a user entering their terms as a string, that I then parse into tokens. These tokens are then compared against a string. What I would like to do is use something similar to a levenshtein in these queries to allow for minor misspellings to be matched as well.

For example if the user typed in rubico instead of rubicon I would still get a match on it (because rubico only has a distance of 1 from rubicon).

Is this possible to do with Postgres' ts_rank functionality, or are there other options to allow for text searches to work with levenstheins?


texts: Optimize a postgresql database view query

texts:
Given the following database schema (using rails-like associations, as this is a ruby-on-rails project, also please forgive me for how I phrase this for I am new to this forum):

Client has_many Deals (0 to many)
Deal has_many DealStages (1 to many)
DealStage belongs_to Stage

where Stage has an attribute position:integer
and DealStage has an attribute current:boolean and only 1 DealStage can be marked as current = true

I am trying to get each Client's highest stage by position, with the ultimate goal of filtering a set of Clients by Stage ID. The current stage of each deal is defined by whichever DealStage is marked as current. This is what I have done to achieve this result:

  CREATE VIEW client_max_stages AS
    WITH client_max_stage_positions AS
    (
      SELECT DISTINCT clients.id AS client_id, max(stages.position) AS position
      FROM clients
      INNER JOIN deals ON deals.client_id = clients.id
      INNER JOIN deal_stages ON deal_stages.deal_id = deals.id
      INNER JOIN stages ON stages.id = deal_stages.stage_id
      WHERE deal_stages.current = true AND deals.is_active = true
      GROUP BY clients.id
    )
    SELECT DISTINCT client_max_stage_positions.client_id, stages.id AS stage_id
    FROM client_max_stage_positions
    INNER JOIN deals ON deals.client_id = client_max_stage_positions.client_id
    INNER JOIN deal_stages ON deal_stages.deal_id = deals.id
    INNER JOIN stages ON stages.id = deal_stages.stage_id
    WHERE stages.position = client_max_stage_positions.position;

I strongly believe this can be optimized in some way, but this gets me all the clients grouped by maximum stage, then each client with the ID of it's highest stage.


texts: Using `min_active_rowversion` for global temporary tables

texts:
I use global temporary tables for easy integration testing of my SQL Server backed application.

But the function min_active_rowversion does not seem to include global temporary tables, like seen here:

using (var connection = new SqlConnection("data source=.;Integrated Security=True"))
{
    connection.Open();

    connection.Execute("create table ##mytable ( Id int, rv rowversion )");

    var a = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001

    var x = ToUInt64(connection.Query<byte[]>("insert into ##mytable (Id) output Inserted.rv values (1)").Single()); // => 22647

    var b = ToUInt64(connection.Query<byte[]>("select min_active_rowversion()").Single()); // => 20001
}

I can't see this limitation documented anywhere. Can this be made to work? And if so, how?


texts: Find the IDs and names of the employees whose salaries are at least 10% higher than the average salary of all employees

texts:
I need to find the IDs and names of the employees whose salaries are at least 10% higher than the average salary of all employees. I can't get the code to work with the 10% higher than the average. I know how to get the average, but when I run my particular code, I get all names and know that it's not running correctly.

Here is my query:

Select employees.eid,
       CONCAT(efirstName,
       ' ', eLastName) AS Name
from employees
GROUP BY employees.salary
HAVING employees.salary > avg(salary * .10);

I have also tried:

Select employees.eid,
       CONCAT(eFirstName,
       ' ',
       eLastName AS Name
FROM employees
GROUP by eLastName
HAVING avg(salary) > avg(salary * .10);


texts: Database space doesn't match ibdata1 size

texts:
I'm using InnoDB database with a single file configuration (in /var), so no innodb_file_per_table.

In the MySql workbench, when I query for the databases used space, with this query

SELECT table_schema "Database", sum( data_length + index_length ) / 1024 / 1024 "Data     Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema;

I get 47 GB.
However, when I check the space on the /var directory with df -h, it returns 129 GB of used space. Last week, this was at 127 GB, so I know that the whole 129 GB is used because new space is allocated by MySql.

I was under the impression that when deleting rows from a table, the space was freed, but the ibdata1 does not skrink and re-use the space latter.

So my question is, why does ibdata1 keep growing to 129 GB and beyond when, supposedly, my databases only uses 47 GB of space?


texts: Good layout of 3d point data for spatial queries in Postgres?

texts:
Like shown in another question, I deal with a lot (>10,000,000) entries of points in a 3D space. These points are defined like this:

CREATE TYPE float3d AS (
  x real,
  y real,
  z real);

If I am not mistaken 3*8 bytes + 8 bytes padding are needed (MAXALIGN is 8) to store one of these points. Is there a better way to store this kind of data? In the afore mentioned question it was stated that composite types involve quite some overhead.

I oftentimes do spatial queries like this:

  SELECT t1.id, t1.parent_id, (t1.location).x, (t1.location).y, (t1.location).z,
         t1.confidence, t1.radius, t1.skeleton_id, t1.user_id,
         t2.id, t2.parent_id, (t2.location).x, (t2.location).y, (t2.location).z,
         t2.confidence, t2.radius, t2.skeleton_id, t2.user_id
  FROM treenode t1
       INNER JOIN treenode t2 ON
         (   (t1.id = t2.parent_id OR t1.parent_id = t2.id)
          OR (t1.parent_id IS NULL AND t1.id = t2.id))
        WHERE (t1.LOCATION).z = 41000.0
          AND (t1.LOCATION).x > 2822.6
          AND (t1.LOCATION).x < 62680.2
          AND (t1.LOCATION).y > 33629.8
          AND (t1.LOCATION).y < 65458.6
          AND t1.project_id = 1 LIMIT 5000;

A query like this takes about 160 ms, but I wonder if this could be reduced.

This is the table layout the structure is used in:

    Column     |           Type           |                       Modifiers
---------------+--------------------------+-------------------------------------------------------
 id            | bigint                   | not null default nextval('location_id_seq'::regclass)
 user_id       | integer                  | not null
 creation_time | timestamp with time zone | not null default now()
 edition_time  | timestamp with time zone | not null default now()
 project_id    | integer                  | not null
 location      | float3d                  | not null
 editor_id     | integer                  |
 parent_id     | bigint                   |
 radius        | real                     | not null default 0
 confidence    | smallint                 | not null default 5
 skeleton_id   | integer                  | not null

Indexes:
    "treenode_pkey" PRIMARY KEY, btree (id)
    "treenode_parent_id" btree (parent_id)
    "treenode_project_id_location_x_index" btree (project_id, ((location).x))
    "treenode_project_id_location_y_index" btree (project_id, ((location).y))
    "treenode_project_id_location_z_index" btree (project_id, ((location).z))
    "treenode_project_id_skeleton_id_index" btree (project_id, skeleton_id)
    "treenode_project_id_user_id_index" btree (project_id, user_id)
    "treenode_skeleton_id_index" btree (skeleton_id)


texts: SQL Server Database Diagram in SQL Management Studio creating multiple schemas

texts:
I'm using a multiple schema approach (Multi-Tenant arhitecture) in the same DB and it's working great. I can use EF bounded contexts and split domains appropriately.

  According to MSDN, multi-tenant architecture can be a shared server
  (separate dbs), shared database (separate schema), or shared schema
  architecture.

Currently i'm writing TSQL to get the DB design i want but I would like to know if there is a way to choose the schema before creating a new table when using Database Diagram tool in SQL Management Studio?


texts:
I'm very new to databases, so I apologize if my question is very basic...

Anyways, I'm creating a table with what seems like alot of data (500 million rows right now, and potentially twice as much in the future).  Now, I need to have a way to access the data in this table quickly, so I'm researching partitions and indexes.  However, I'm getting confused on when I'd want to create a partition vs. an index.  I have three columns that seem like reasonable candidates for partitioning or indexing:

Time (day or week, data spans a 4 month period)
Customer "Token": The last two digits of a user's ID
Client ID (typically will have 5-20 clients in the dataset)

When running future selects against this table, it's likely that I'll be filtering on client id as well as wanting to do some sampling (which I would like to do by the "Token" variable).  I may occasionally be filtering by the time variable as well.

So, my question is this: how should I organize my table?  Should I partition by Client and Token, and then create an index on time?  Or just partition on client and create indices on time and token?  And, more importantly, what is the logic behind the strategy you recommend?

Also, after I've created the table, will the indexes break if I add more data into it (particularly new clients for the same date/token range)?  Is recreating an index relatively simple?

Thanks so much for your help, and please let me know if you need any more information from me.


texts: Alternatives to log shipping / Replicate a local SQL Server database on a remote DB accesible only by IP

texts:
I'm trying to replicate a local SQL Server 2008R2 DB to a remote DB instance accessible only by IP (specifically, a RackSpace cloud site DB). I believe log shipping is ideal, since the remote only needs to be kept up to date occasionally, and data transfer is one-way. However, I cannot get this configured as attempting to use the RackSpace as the secondary in the config generates an error that I must be a member of the sysadmin fixed role on that instance. So I have two questions:

Is the sysadmin role fixable?
If not, what are my options for replicating data from a local SQL Server instance to a remote instance?


texts: Store a formula in a table and use the formula in a function

texts:
I have a PostgreSQL 9.1 database where part of it handles agent commissions. Each agent has his/her own formula of calculation how much commission they get. I have a function to generate the amount of commission each agent should get, but it's becoming impossible to use as the number of agents grow. Am forced to do some extremely long case statements and repeating code, which has made my function very big.

All the formulas have constant variables:

d .. days worked that month
r .. new nodes accuired
l .. loyalty score
s .. subagent commission
b .. base rate
i .. revenue gained

The formula can be something like:

d*b+(l*4+r)+(i/d)+s

Each agent negotiates the payment formula with the HR dept. So can I store the formula in the agents table then have like a small function that just gets the formula from the table and translates it with values and computes the amount?


texts: Using oracle easy connect does it really connect to the remote DB?

texts:
I tried connecting to remote host db using :

 sqlplus abc/abc@hostname1:1000/ABC

And tried printing

!hostname

it still shows me local hostname from which I am running and not HOSTNAME1.

So if I want statistics or run a script on that remote host .. Would it not do that..
i.e. Do I actually get data from remote host?

Is it bcoz !hostname is not a linux command and not a DB command?


texts:
I have configured a log shipping for our company's primary database to restore on a separate server, with the restored database serving as report database.

The problem is that every few days, the log shipping breaks and unfortunately, more often than not, a full backup happens after the break which prevents me from doing a differential restore to "un-break" the LSN chain.  Here are the results of the LSN chain for your reference (primary server and database):

I can usually restart the LSN chain if I can do differential restore before the full backup happens but I should not really depend on that.  The main issue here is I am getting a "NUL" message in the following query, and I am 100% sure that the LSN breaks where the "NUL" happens, as the last restored log is the one with the green highlight.  I would really appreciate anyone's input.  Thanks.

DECLARE @db_name VARCHAR(100)
SELECT @db_name = 'BESIDE'

SELECT TOP (30) s.database_name
,m.physical_device_name
,CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB'  AS bkSize
,CAST(DATEDIFF(second, s.backup_start_date, s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken
,s.backup_start_date
,CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn
,CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn
,CASE s.[type] WHEN 'D'
  THEN 'Full'
  WHEN 'I'
  THEN 'Differential'
  WHEN 'L'
  THEN 'Transaction Log'
END AS BackupType
,s.server_name
,s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date DESC
,backup_finish_date


texts:
Hopefully this has a simple answer and I've missed something obvious. I have a table where the number of rows * the row size is much smaller then the actual data space used by the table.

If I run the standard report "Disk Usage by Top Tables", the numbers I get are:

# Records   Reserved (KB)      Data (KB)      Indexes (KB)      Unused (KB)
33,245        32,962,192       31,070,264       144               1,891,784

which (unless I'm missing something) implies each row is taking up just under 1MB!

The table schema (which I didn't design) is:

CREATE TABLE [MySchema].[MyTable]
(
    [Code]     [varchar](4) NOT NULL,
    [SomeID]   [smallint] NOT NULL,
    [SomeID1]  [smallint] NOT NULL,
    [Updated]  [datetime] NOT NULL,
    [SomeId2]  [int] NULL,
    [SomeId3]  [int] NULL,
    [Somekey]  [real] NULL,
    [desc1]    [char](12) NULL,
    [colA]     [real] NULL,
    [someid4]  [char](20) NULL,
    [starttime] [real] NULL,
    [endtime]  [real] NULL,
    [duration] [real] NULL,
    [reason]   [real] NULL,
    [status]   [real] NULL,
    [category] [real] NULL,
    [comment]  [char](30) NULL,
    [ColB]     [real] NULL,
    [ColC]     [real] NULL
)

It has no indexes or keys.

A bit of research lead me to the idea that perhaps in the past the table had variable length columns that had been deleted, so I ran DBCC CLEANTABLE with no change.

SQL Server 2005 x64 Service Pack 3 (as far as I can work out from the following version string:

[Microsoft SQL Server 2005 - 9.00.4053.00 (X64)   May 26 2009 14:13:01   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)]

Update: I'm fairly sure this is due to a pathologically fragmented HEAP ....
I rebuilt by applying a clustered index (an index is required, according to missing index reports), and the size discrepancy has gone.


texts: How can I change the properties which appear in the SSMS Filter Settings? For example: how can I remove 'Durability Type' and add 'Filegroup'?


texts: db.serverStatus() got not authorized on admin to execute command

texts:
My mongo version : 2.6.11
I want to  check db.serverStatus() in my robomomgo
But got errmsg:

{
    "ok" : 0,
    "errmsg" : "not authorized on admin to execute command { serverStatus:   1.0 }",
    "code" : 13
}

How can I add authorization  ??

I find a command can create user with authorization :

db.createUser(
    {
      user: "tester",
      pwd: "password",
      roles: [
         { role: "readWrite", db: "test" }
      ]
    }
);

But I don't know which is db.serverStatus() 's database name??
How can I authorize it??


texts: Questions for 'Tips on Converting to Galera' on MariaDB KB

texts:
Some questions about the excellent writing Tips on Converting to Galera.

Is "cross-colo writing" cross connect datacenters write?

  Also, you should use "START TRANSACTION READONLY" wherever
  appropriate.

this means the transaction will be handled only by one node (no replication latency)?. Are there cases when a SELECT without START TRANSACTION READONLY involving more than one node? SELECT marks it's a read?

  Transaction "size"

  (This section assumes you have Galera nodes in multiple colos.)
  Because of some of the issues discussed, it is wise to group your
  write statements into moderate sized BEGIN...COMMIT transactions.
  There is one latency hit per COMMIT or autocommit. So, combining
  statements will decrease those hits. On the other hand, it is unwise
  (for other reasons) to make huge transactions, such as
  inserting/modifying millions of rows in a single transaction.

Is this the max transaction size? Is it recommend to use close to max transaction size? Can you give a value what number you mean with moderate sized BEGIN...COMMIT transactions

MariaDB [(none)]> select @@wsrep_max_ws_size;
+---------------------+
| @@wsrep_max_ws_size |
+---------------------+
|          1073741824 |
+---------------------+
1 row in set (0.00 sec)

  Critical reads

  Here is a 'simple' (but not 'free') way to assure that a
  read-after-write, even from a different connection, will see the
  updated data.

when an app needs to use a read-after-write? What's the use case for read-after-write? But anyway then this read-after-write should be in the same transaction? Please a little more info about read-after-write and when to use it.

  Variables that may need to be different (...)
      innodb_doublewrite - ON: When an IST occurs, want there to be no torn pages? (With FusionIO or other drives that guarantee atomicity,
  OFF is better.)

what are torn pages? I translated torn in my native language and now even more confused.

  Miscellany (...) DROP USER may not replicate?

Any more details and infos? We use automated DROP USER since a few years and never noticed issues. Did we just didn't saw the problems?


texts: Is it necessary to delete a database before restoring the backup in MySQL?

texts:
As a rule, I always drop the database, and then create a new one and only after that restore a dump file. But I'm wondering, is it necessary to do?

For example, if I lose some data and begin to restore my database, why I cannot just to restore it, like in MS SQL, without deleting it first?


texts: Is it a good idea to create an index for each column in a new table?

texts:
I will short my question. (SQL-Server 2008)

Is it a good idea , when crating a new table, to create an index for each column?

Why i'm saying this?

Today I was creating a database, and my boss was behind me, and he said:

"Oh, it's good to create an index, for every column" (and then he showed me the graphic way to do this).

I'm studying indexes now and I'm not sure if this is a good idea, since every index will be updated everytime time there will be an insert\update on the table.

Some advices?


texts: UPDATE with join condition on matching words in columns of another table

texts:
I have 2 tables that looks like this:

Table A:

CREATE TEMP TABLE table_a (
  Column_1 text,
  ID_number int
);
INSERT INTO table_a VALUES
  ('foo,bar,baz', 123),
  ('qux,quux,quuz',456),
  ('corge,grault,garply',789),
  ('qux,bar,grault', 101);

Table B:

CREATE TEMP TABLE table_b (
  Column_1 text,
  Column_2 text,
  ID_number int
);
INSERT INTO table_b VALUES
  ('foo','baz',null),
  ('qux','quzz',null),
  ('corge','garply',null);

I'm trying to copy across values from the ID_number column in Table A, where the values in Column 1 & 2 of table B can be found in the same row of Column 1 in Table A.

This is the kind of thing I was thinking of:

UPDATE table_b AS B
SET id_number = A.id_number
FROM table_a AS A
WHERE A.column_1 LIKE B.column_1
  AND A.column_1 LIKE B.column_2

.. but obviously this doesn't work.

How can I translate this into a proper query?

Additional info

table_a.Column_1 contains UK addresses, for example:

'47 BOWERS PLACE, GREAT YARMOUTH, NORFOLK, NR20 4AN'

In table_b I have the first line of the address in Column_1 (so, '47 BOWERS PLACE') and the postcode ('NR20 4AN') in Column_2.

I thought it would be best to simplify things, but maybe the actual data has some relevance in this situation.

table_a has about 30 million addresses. table_b has around 60k rows.

Performance is relevant, the faster this runs the better, and it will likely be repeated in the future.


texts: pgAgent Job's "last result" is continuously "running"

texts:
I am using PostgreSQL-9.3 on Ubuntu-14.04 and using "/usr/share/pgadmin3/pgagent.sql" I have configured my pgAdmin3 to use pgAgent. postgres is my maintenance Db and I have run the pgagent.sql content into it's SQL editor to bring the "Jobs" node in pgAdmin3-gui.
In order to check how does this pgAgent works, I made a sample Db and then a test table under it. I have properly configure the "Steps" and "Schedule" parameters. But after starting this newly created job from the Job node using "right-click and run now", my step is running just once and then it's not terminating after the execution. I am sure that my SQL and other parameters are right since I have seen the table modification after the very first run of the step but then the step is not terminating. It's a simple insert SQL command.
Any help regarding why the step is not terminating?


texts: Regarding configuration of kernel parameter

texts:
Before installation of ORACLE, I have to set some kernel parameters such as

kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

I've searched what they are, but it's quite hard to understand.

For example, I can get the information for kernel.shmmni, which is max number of segments. This was easy, but I can't understand what kernel.sem and net.core mean and why they are required.

Can someone please explain the meaning of these parameters?


texts: Moving from dynamic disk allocation to direct space disk via storage pool in SQL server

texts:
Pardon me if I am not clear in asking the question since I have very less understanding from storage terms used or how it's used with Sql server.

Currently our storage team is testing to move disk storage allocation from dynamic disk to storage pools via direct spaces as what I understood from them.

From what I understand we currently are provided storage in a manner where can we create mount points for each lun provided. We use those luns/ mount points for SQl server like data drives log file drives and tempdb as per best practices.

Now what storage team is moving using direct spaces where they will replace dynamic disk with storage pool which will give us one entire pool of storage say data drive of 20 TB where we can place all data files rather than splitting separately as in mount points. Same holds true for log and temp db as well.

They want us to do storage performance testing as they believe this to be a performance booster for our storage issues. But I have a different opinion based on how SQL might work.

Can someone please share the experience or any knowledge on above implementation?

Storage is the regular storage spaces not spaces to direct


texts: to copy column from one table to another in psql

texts:
I want to copy column User_id from public."firstapp_offer_Vendor" to table firstapp_bid on the basis of oid, i.e. if oid in firstapp_offer_Vendor is equal to firstapp_bid.offer_id then user_id should be copied to vendor_id.
Here are my two tables:
Table firstapp_bid:
id   Bid  offer_id vendor_id
1   "123"   1       null
2   "123"   6       null
3   "123"   13      null
4   "120"   13      null
5   "140"   14      null
6   "130"   14      null
7   "140"   14      null
8   "150"   14      null
9   "123"   15      null
10  "123"   16      null
11  "120"   16      null

Table first_app_offer_vendor:
id oid user_id
1   1   3
12  6   2
26  13  3
27  13  4
28  14  2
29  14  4
30  14  6
31  14  7
32  15  7
33  16  6
34  16  7

The output table should be:
id   Bid  offer_id vendor_id
1   "123"   1       3
2   "123"   6       2
3   "123"   13      3
4   "120"   13      4
5   "140"   14      2
6   "130"   14      4
7   "140"   14      6
8   "150"   14      7
9   "123"   15      7
10  "123"   16      6
11  "120"   16      7

I tried implementing that using
INSERT INTO firstapp_bid
(
   vendor_id
)
SELECT
   user_id
FROM
   public."firstapp_offer_Vendor"
WHERE
   public."firstapp_offer_Vendor".oid = public."firstapp_bid.offer_id

but it gives me the wrong output.


texts:
I am trying to run:

CREATE TABLE `members` (
`id` int(11) NOT NULL auto_increment,
`users` text NOT NULL,
`password` text NOT NULL,
`ip` text NOT NULL,
`status`text NOT NULL,
UNIQUE KEY `id` (`id`)
) TYPE=MyISAM;

I just can't seem to find the error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TYPE=MyISAM' at line 8


texts: SQL Server Rebuild/Reorganize Fragmented Indexes causes CRC error or consistency errors

texts:
In our test lab I've been experimenting with different jobs to keep our critical indexes from becoming too fragmented.

I'm currently using the approach described here: sys.dm_db_index_physical_stats (under the Examples -> D section: Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes).

Basically every hour I query the dm_db_index_physical_stats dynamic management view and if an index is between 5% and 30% fragmented I reorganize it, if it's greater than 30% fragmented I rebuild it. It seems to work fine during most of our testing, however, twice I've run into a problem where the scheduled job fails with an error:

  The operating system returned error 23(Data error (cyclic redundancy
  check).) to SQL Server during a read at offset 0x00000eae3b2000 in
  file 'C:\Program Files\Microsoft SQL
  Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\database.mdf'.
  Additional messages in the SQL Server error log and system event log may provide more detail.
  This is a severe system-level error condition that threatens database integrity and must be corrected immediately.
  Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online. [SQLSTATE HY000] (Error 823)

When I run DBCC CHECKDB a problem is reported in one of my indexes, the only way I'm able to fix this problem is by using

DBCC CHECKDB ('dbname', REPAIR_ALLOW_DATA_LOSS)

I'm not positive, but I suspect this error is caused by rebuilding or reorganizing indexes while my load tests are running in the test lab.

I've searched around and found nobody else reporting this consistency error related to rebuilding indexes. You can see more information about my problem on my blog post: SQL Server Index Corruption: CRC Error & DBCC CHECKDB

Is my approach to tuning indexes flawed? Should I not be trying to rebuild indexes on a "live" database (while traffic is hitting it)? Should I be using SQL Server Enterprise Edition's feature of rebuilding an index with (online=on)? Any help is appreciated.

I'm running SQL Server 2008 R2 Standard.


texts:
I am trying to remove filestream from my database, so I have to do the following

I would like to know how to remove the following:

ALTER TABLE AMGR_Documents_Tbl ADD UId uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT( NEWID() );
ALTER TABLE AMGR_Documents_Tbl ADD CONSTRAINT AMGR_Documents_Tbl_UId_Unique UNIQUE( UId );`

Then I need to Add a column with no filestream which probably is the following code:

ALTER TABLE AMGR_Letters_Tbl ADD TextCol1 varbinary( max );
Update [AMGR_Letters_Tbl]
SET TextCol1 = TextCol;
ALTER TABLE [AMGR_Letters_Tbl] DROP COLUMN TextCol;
/*Now i just rename the column*/

I also need to fix the FTS index (Which i do not understand) after which is the following:

    IF 0 != INDEXPROPERTY( OBJECT_ID( 'AMGR_Letters_Tbl' ), 'Letters_Record_Id', 'IsFulltextKey' ) BEGIN
                EXEC sp_fulltext_table AMGR_Letters_Tbl, 'drop'
            END

/****** I need to fix filestream on both tables:  ******/
/****** This is what the whole thing looks like (for 2 tables):  ******/
IF @@VERSION NOT LIKE 'Microsoft SQL Server 2005%' AND ISNULL(SERVERPROPERTY ('FilestreamEffectiveLevel'),0) > 0 BEGIN
    if not exists   ( select name from syscolumns where id IN
                ( select Id from sysobjects where id = object_id(N'[dbo].[AMGR_Letters_Tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
                ) AND name = 'UId' )
    BEGIN
        -- add unique ROWGUIDCOL column
        ALTER TABLE AMGR_Letters_Tbl ADD UId uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT( NEWID() );
        ALTER TABLE AMGR_Letters_Tbl ADD CONSTRAINT AMGR_Letters_Tbl_UId_Unique UNIQUE( UId );
        EXEC( 'ALTER TABLE AMGR_Letters_Tbl ADD TextCol1 varbinary( max ) FILESTREAM NULL' );
        -- will need to re-create FTS index after
        IF 0 != INDEXPROPERTY( OBJECT_ID( 'AMGR_Letters_Tbl' ), 'Letters_Record_Id', 'IsFulltextKey' ) BEGIN
            EXEC sp_fulltext_table AMGR_Letters_Tbl, 'drop'
        END
        EXEC( 'UPDATE AMGR_Letters_Tbl SET TextCol1 = TextCol;' );
        ALTER TABLE AMGR_Letters_Tbl DROP COLUMN TextCol;
        EXEC sp_rename 'AMGR_Letters_Tbl.TextCol1', 'TextCol', 'COLUMN';
        EXEC ('DBCC CLEANTABLE (0,''AMGR_Letters_Tbl'',1000) WITH NO_INFOMSGS;');
    END
END
GO
------------------------------------------------------------

IF @@VERSION NOT LIKE 'Microsoft SQL Server 2005%' AND ISNULL(SERVERPROPERTY ('FilestreamEffectiveLevel'),0) > 0 BEGIN
    if not exists   ( select name from syscolumns where id IN
                ( select Id from sysobjects where id = object_id(N'[dbo].[AMGR_Documents_Tbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
                ) AND name = 'UId' )
    BEGIN
        -- add unique ROWGUIDCOL column
        ALTER TABLE AMGR_Documents_Tbl ADD UId uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT( NEWID() );
        ALTER TABLE AMGR_Documents_Tbl ADD CONSTRAINT AMGR_Documents_Tbl_UId_Unique UNIQUE( UId );
        EXEC( 'ALTER TABLE AMGR_Documents_Tbl ADD Data1 varbinary( max ) FILESTREAM NULL' );
        -- will need to re-create FTS index after
        IF 0 != INDEXPROPERTY( OBJECT_ID( 'AMGR_Documents_Tbl' ), 'AMGR_Documents_Record_Id', 'IsFulltextKey' ) BEGIN
            EXEC sp_fulltext_table AMGR_Documents_Tbl, 'drop'
        END
        EXEC( 'UPDATE AMGR_Documents_Tbl SET Data1 = Data;' );
        ALTER TABLE AMGR_Documents_Tbl DROP COLUMN Data;
        EXEC sp_rename 'AMGR_Documents_Tbl.Data1', 'Data', 'COLUMN';
        EXEC ('DBCC CLEANTABLE (0,''AMGR_Documents_Tbl'',1000) WITH NO_INFOMSGS;');
    END
END
GO


texts: Two-column index (pretty unequal columns) and database optimisation

texts:
I'm thinking about creating an index on a table like this:

primary | type | id
      1   a      10001
      2   a      10002
      3   b      10001
      n   b      1....

type will contain four values, ~ 95 % will be "a" (or 1, please don't care about the exact value/normalization). Also, 95 % of all queries will be looking for "a", too, in combination with a list of ids (~ one to five or so), like:

SELECT id FROM table WHERE type = 'a' AND id IN (...)

Basic task is to check a list of given id's for existence, so a "covering index" is the thing I basically want.

Now, as far as I understood, it's better to have the column first which reduces the result set most, which would be id. But on the other hand, an intelligent database engine could detect that most of the queries are about "a" and could keep only this branch of the index tree in memory, or, enough memory provided, all three indices as a "different" index each, as it would be better not to bother with all the "b" values.

Is a MySQL/MariaDB (just InnoDB backend) doing this kind of optimizations? Or any other thoughts? I'm not so interested in this table, more a general view on this case.

Thx in advance for all your comments ;)


texts: Is it possible to validate more than one parameter simultaneously into a Stored Procedure in sql?

texts:
On Microsoft SQL Server 2008 I realized a stored procedure which contains more than 20 parameters, in this SP I update or insert data into table which does not accept null values.

I need to evaluate if one of these parameters contain a null value before performing the insert or update into table.

Example:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Example]
  @parameter1 char (8),
  @parameter2 char (20),
  @parameter3 char (20),
  @parameter4 char (20),
  @parameter5 char (20),
  @parameter6 char (20),
  @parameter7 char (20),
  @parameter8 char (40),
  @parameter9 char (15),
  @parameter10 char (15) ,
  @parameter11 char (15)...

I have a possible solution for this, which is performing evaluation parameter by parameter and validate if is null add some value, but I'm looking for a better solution for this.


texts:
I am trying to optimize the rollup code for my company and ran into a very peculiar issue. I converted many Scalar functions to be TVFs and they all seem to run more quickly than the original, which is great. However, in the queries that call them, they end up running significantly slower than the original. Here is a basic outline of my update:

SELECT col1, ..., colx,
    (CASE WHEN x <= 0 OR y <= 0 OR z <= 0 OR z = x
          THEN output
        WHEN valX <= 0
          THEN output
        WHEN minimum.min < 1.0 THEN 1.0
        ELSE minimum.min
        END) AS Q,
FROM Tbl1...tblx (series of inner joins)
CROSS APPLY dbo.inlinemin(val1, val2) AS minimum

This is a basic outline of the original:

SELECT col1, ..., colx,
    (CASE WHEN x <= 0 OR y <= 0 OR z <= 0 OR z = x
          THEN output
        WHEN valX <= 0
          THEN output
        ELSE maximum(minimum(val1,val2),1.0)
        END) AS Q,
FROM Tbl1...tblx (series of inner joins)

The numbers are more or less the same, as is the logic. The only difference is my function 'inlineMin' is a TVF as opposed to 'maximum' and 'minimum' the original Scalar functions. These functions are exceedingly simple and just return the max or min between the two passed parameters. Even the execution plan is more or less the same. There is a change from merge join to hash match at one point, however, the cost of this difference is minimal and could not account for the drastic change in elapsed time and cpu time.

When I run the functions outside of the rollup query my function is faster than the original for large sets of data. This makes sense given how TVFs work in comparison to scalar UDFs. However, when I call them in the query, my updated version runs roughly 6x slower. The cross apply is (seemingly) not the issue since leaving the cross apply and simply using the old functions

SELECT ...
    ELSE maximum(minimum(val1,val2),1.0)
    END) AS Q,
FROM Tbl1...tblx (series of inner joins)
CROSS APPLY inlinemin(val1, val2) AS NotUsedHere

Is roughly as efficient as the original code. It is only when I include the output of my function in the select that the query becomes significantly slower.

As I understand, the function is called and runs at the cross apply, meaning that it should be calculating a value even if it is not in the select, so why would it be faster not to include it in the select? Further, if the above is false, why would my function itself be faster but run significantly slower when used inside of a query?

Edit:

Here is the Inline TVF that I have written to replace the original

CREATE FUNCTION [dbo].[InlineMin](@val1 FLOAT, @val2 FLOAT)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
    SELECT minVal =
    CASE    WHEN @val1 < @val2
            THEN @val1
    ELSE
            ISNULL(@val2,@val1)
END

Here is the anonymized query plan for my rewrite:
https://www.brentozar.com/pastetheplan/?id=ryKR_Q0Em

and the anonymized query plan for the original:
https://www.brentozar.com/pastetheplan/?id=SJsS1-A4X


texts: Creating Postgresql database with multiple usertypes

texts:
I will be building an application where a company can sign up and sell their products, a user (customer) can also sign up and purchase said products.

A customer cannot be a company and a company cannot be a customer.

So one or the other. Not both.

Was wondering if my setup is good practice.


texts:
I have a tables ENV_CONFIG and ENV_CONFIG_HIST where the latter table should get a new record on any insertion to the former. The trigger seems pretty straight forward but I'm getting compilation errors:

line 5  : SQL Statement ignored (Insert ...)
line 13 : Column not allowed here (,new.PARAM_VALUE)

What am I missing?

create or replace TRIGGER TRG_ENV_CONFIG_INSERT
  AFTER INSERT
  ON ENV_CONFIG
  FOR EACH ROW
BEGIN
  INSERT INTO ENV_CONFIG_HIST (
    PARAM_NAME
    ,PARAM_DESCR
    ,PARAM_VALUE
  ) VALUES (
    new.PARAM_NAME
   ,new.PARAM_DESCR
   ,new.PARAM_VALUE
 );
END;


texts: How to get the relationship between backup set?

texts:
We use command backup database to perform a full backup, serval backup sets are generated during the backup operation.
Also, we use list backup to show the backup information, the output as followed:

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7       136M       DISK        00:00:20     04-NOV-06
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20071104T200759
        Piece Name: /d2/RDBMS/backupset/2007_11_04/o1_mf_annnn_TAG20071104T200759_ztjxx3k8_.bkp

  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    1       173832     21-OCT-06 174750     21-OCT-06
  1    2       174750     21-OCT-06 174755     21-OCT-06
  1    3       174755     21-OCT-06 174758     21-OCT-06

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    2M         DISK        00:00:01     04-NOV-06
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20071104T200829
        Piece Name: /disk1/oracle/dbs/c-774627068-20071104-01
  Controlfile Included: Ckp SCN: 631510       Ckp time: 04-NOV-06
  SPFILE Included: Modification time: 21-OCT-06

From above output, I could not figure out whether such backup sets are generated by the same command, backup database. Is there any available command to satisfy my requirement?


texts: Connecting to SQL Server from R which is Running on Remote UNIX Machine

texts:
We have a UNIX machine which includes R engine on it and installed SQL Server ODBC Driver 11.

We need to connect R to SQL Server. SQL Server currently works on another machine, so we tried to connect R to SQL Server with the code below.

library(RODBC)

dbhandle <- odbcDriverConnect('driver=ODBC Driver 11 for SQL Server;server=10.XXX.XX.XX:1663;')

But getting that error:

  Warning messages: 1: In odbcDriverConnect("driver=ODBC Driver 11 for
  SQL Server;server=10.XXX.XX.XX:1663;") :

  [RODBC] ERROR: state HYT00, code 0, message [unixODBC][Microsoft][ODBC
  Driver 11 for SQL Server]Login timeout expired 2: In
  odbcDriverConnect("driver=ODBC Driver 11 for SQL
  Server;server=10.XXX.XX.XX:1663;") :

  [RODBC] ERROR: state 08001, code 87, message
  [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]A network-related
  or instance-specific error has occurred while establishing a
  connection to SQL Server. Server is not found or not accessible. Check
  if instance name is correct and if SQL Server is configured to allow
  remote connections. For more information see SQL Server Books Online.
  3: In odbcDriverConnect("driver=ODBC Driver 11 for SQL
  Server;server=10.XXX.XX.XX:1663;") :

  [RODBC] ERROR: state 08001, code 87, message
  [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network
  Interfaces: Connection string is not valid [87]. 4: In
  odbcDriverConnect("driver=ODBC Driver 11 for SQL
  Server;server=10.XXX.XX.XX:1663;") : ODBC connection failed

If I run telnet to SQL Server from UNIX machine, it works perfectly. I mean there is a healthy connection between these two machine.

How can we connect R to SQL Server without using DSN?


texts: Portable Method of Deploying Oracle Express on Windows (Ideally without Installer)

texts:
I'm looking for a portable way of deploying Oracle Express alongside a C# application on a continuous integration machine running CruiseControl.

Ideally I would not have to use the installer - this machine is being used for building a lot of other projects, and we are concerned that running the installer could break something. Ideally, the database is wholly contained within the build folder, and this build environment would be completely separate from any other builds or programs.  We don't like things that are global to the whole machine!

From what I can see though, this appears to be impossible.  Oracle Express runs as a service, which puts it at a global scope.  Is there a way that I make it local? ie. I run an executable to start and stop it, and it has no dependencies outside the build folder?

If not, what would the effect of running the installer be?


texts:
I'm storing a large amount of time series data/value (varchar) with timestamp for a set of parameters (varchar) in PostgreSQL.

I'm looking for a way to avoid storing duplicate if the value is the same, which 90% of the time it is. What's important for me is to be able to know when the value first appeared for the particular parameter and when it changes and also when it disappear. I'll be representing the data in a graph.

Example of the schema and data, this is an oversimplified version of what I'm actually storing
http://sqlfiddle.com/#!17/cd02b

Is there a general pattern to solving this kind of problem?


texts: How do I write a query that inserts values while AND gets values from another table?

texts:
I have a table (Tbl2) that needs to be populated with values from another table (Tbl1).

Tbl1 has columns: id (uuid), name, address, gender.

Tbl2 has columns: id (uuid), org, name, address, gender, createdAt, updatedAt.

I want to copy id, name, address, gender from tbl1 into tbl2. How Do I write a query that inserts those values into Tbl2 while also providing new ids, org, createdAt, and updatedAt values?


texts: Anchoring values for a rating or likert scale

texts:
I am designing a schema for an interview system as per the suggestion in this answer.  I need to store several different types of questions:

Questions with a free-text response
Multiple choice text-response questions
Likert (rating) scale questions.  This is essentially a multiple-choice question with numeric options.

I am keeping all questions in a q_question table.  Possible answers for multiple choice questions are kept in a q_choice table, with a foreign key to q_question.

I also need to store responses to questions.  I store responses to free-text questions in an q_answer_text table, and responses for multiple choice questions in a q_answer_choice table (with a foreign key into the q_choice table).

The question is, should I represent my Likert questions as free-text questions, multiple-choice questions, or something else entirely?

At the moment, the question-choice relation is one-to-one.  So if I stored Likert responses as multiple-choice, I'd have to either relax this constraint, or duplicate the choices for each new Likert question (since they all use the same scale).  If I just keep the numeric choice as a free-text response, I will have no way to anchor the numeric choice to a qualitative description (e.g. "strongly agree") in my database.

Clarification regarding @MDCCL's notes:

The relation between q_question and q_answer_* is ternary, in the context of a specific interview (this part of the design is very similar to the one outlined in this question.  An interview maps a respondent (not shown) to a questionnaire (which is simply a set of questions).
Yes, a question can have multiple choices for the likert response.  With the model I've come up with below, it would actually be a many-to-many relationship.  Then the same five choices could be reused across all likert questions (which are always on the same 1-5 scale).
I am thinking about creating a ternary relation table among questionnaires, sections, and questions.  This would allow reuse of questions and sections across different "questionnaires".  This would be especially useful, for instance, in versioning.
The more I think about it, maybe storing different types of answers in different tables is overkill.  Choices are immutable anyway (modifying the survey in any way should necessitate creating a new revision of a questionnaire).  So, perhaps just storing the value of the choice directly, in the same way as a free-text response, would be more sensible.


texts: How to install HANA DB on Oracle Linux 7?

texts: I want to know how to install SAP HANA DB 2.0 on Oracle Linux 7 including downloading, installing and the first connection establishing to the system from network. I've spent a few hours to find out how to do it, but haven't found all required information in one place.


texts: PostgreSQL Multi Master Single Slave Replication

texts:
I have multiple transaction PostgresQL 9.4 databases and a single publication read-only database. Is there any replication mechanism (asynchronous) to push every updated transaction from every database to the one publication database. Is there any work around Slony to accomplish this?

Thanks in advance.


texts: DBeaver's Auto Increment field max is 2147483647 - why?

texts:
In DBeaver, when right-clicking the 'Foreign Keys' to select 'View Foreign Keys', the following informational page is displayed:

I would think that the 'Auto Increment' field value would read 1 (or some other small number) to indicate the step value by which that foreign key is auto-incremented.

Instead, I see a value of MAX INT 32 bit (2147483647).

What does that value mean?


texts:
I'm in the DB classes, and I have to understand and show that it is possible to get the results of HAVING and GROUP BY without using themselves. I searched for alternatives for those 2 commands, but I didn't understand anything.

Can someone give me examples?


texts: Best Database for range queries on a single column

texts:
here's my usecase: I have these fields: id(int), frequency(int), created(timestamp).
I need a database that can optimize range queries on created field. Specifically for queries like: SELECT * FROM my_table WHERE created >= val1 AND created <= val2
This db needs to handle millions of queries and also scale properly.
I've looked at Cassandra and understood that it doesn't work well for range splice queries like this since it needs to query all partitions. I suppose I could simply create an index on the created column and query it in a SQL type db but the reason I'm not looking at SQL db is that it won't be able to scale well (like there's no auto-sharding available in SQL dbs for example).
Please let me know of any db suggestions for this usecase. Thank you!


texts:
We need to have db server (shared hosting, vps ..etc) mainly as storage (saving data)
The database (PostgreSQL) is going to have <= 10 tables with data types:
1 small int, 2 bigint, 1 bool and 3 float32
The database will have insertion queries 1 million rows per hour approx
We will need moderate RAM, enough to query a database that could grow in order of 20-200 GB weekly
Scalability priority is for disk space.
We dont mind if its slow as long as it works as the budget is limited.
I've looked into google postgresql cloud and other options, the problem is we can't decide how much resources we need to start with? (CPU, RAM)


texts:
I have a general RBAC model design: user table, role table and resource table. The user_role talbe is the joined table of user and role, and resource_role table is the joined table of resource and resource_role.
The structure is:
User table
User_ID|Email             |Name     |
-------|------------------|---------|
      1|bobtest@gmail.com |Bob      |
      2|marytest@gmail.com|Mary     |
      3|samtest@gmail.com |Sam      |

Role table
Role_ID|Role_Name  |
-------|-----------|
      1|admin      |
      2|manager    |
      3|editor     |
      4|author     |

Resource
Resource_Id|Resource_Name  |
-----------|---------------|
          1|create_post    |
          2|update_post    |
          3|view_post      |
          4|delete_post    |

User_role table will define what roles one user has and each user can have multiple roles
User_Id|Role_Id  |
-------|---------|
      1|        1|
      1|        3|
      2|        2|
      3|        4|

Role_resource table will define what resources one user will have:
Role_Id  |  Resource_Id|
---------|-------------|
        1|            1|
        1|            2|
        1|            3|
        1|            4|
        2|            1|
        2|            2|
        2|            3|
        2|            4|
        3|            3|
        3|            2|
        4|            3|
        4|            2|

I also have a post talbe to store post information and Author_Id is the foreign key refer to User table User_ID
post table
Post_ID|Name          |Author_Id |
-------|--------------|----------|
      1|Post_A        |         1|
      2|Post_B        |         2|
      3|Post_C        |         2|
      4|Post_D        |         3|

My question:

How can I set one user with the particular role can only edit the particular post rather than all posts? For example, one user with author role can create any new posts, but he should only edit the posts he created, not all posts.
I want to set one editor can only edit particular author posts

How should I design the structure?


texts: SQL Server 2016 - Configuration Manager WMI Problem

texts:
Background:

Windows Server 2016 Core
SQL Server 2016 Enterprise (obviously 64-bit)
Windows Failover Cluster
SQL Failover Cluster

I am attempting to connect to SQL Server Configuration Manager via my local Computer Management connecting to the database server. I expand Services and Applications and click on SQL Server Configuration Manager. However, this is the error message I see:

  Connection failed.
  Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid namespace [0x8004100e]

I have tried this in the command prompt:

mofcomp "%programfiles(x86)%\Microsoft SQL Server\130\Shared\sqlmgmproviderxpsp2up.mof"

Which then says:

  MOF file has been successfully parsed
  Storing data in the repository...
  Done!

Then I restarted WMI service, closed Computer Management, and reopened it. And I got the same error.

Does anyone know how to resolve this?

Thank you!

P.S. - I have posted this on another forum a few days ago and no one could help me resolve this issue

Update 1: I have turned off the firewalls on both client and server and attempted to connect to Configuration Manager which gave me the same error message

Update 2: My user account has domain admin privileges

Update 3: I ran the Get-WMIObject command and got SystemDirectory, BuildNumber, RegisteredUser, SerialNumber, and Version information so I'm guessing that I am allowed to access WMI remotely?

Update 4: I have SQLServerManager13.msc (located in the server's C:\Windows\SysWOW64) and attempted to open it on my local machine which gave me the same WMI error.

Update 5: I ran SQL Server repair on both SQL nodes (still same message)

Update 6: I ran winmgmt /verifyrepository and winmgmt /salvagerepository and both stated that the WMI repository is consistent

My solution: You need to have SQL Server 2016 installed on your local machine. Then you can use Computer Management to connect to your remote SQL Server Configuration Manager stuff.


texts: Pivoting rows into columns dynamically in Oracle

texts:
I have data like the following in an Oracle database

select * from TABLE_1

ID       Field      Value
----     -----   -----
  1      name    Bob
  1      age     30
  1      gender  male
  2      name    Susan
  2      status  married

I need the result in below format

ID       NAME    AGE    GENDER  STATUS
----     -----   -----  ------  --------
  1      Bob      30     male
  2      Susan                   married

I wrote a query

select
   ID,

    coalesce(sum(case when Field = 'name' then value end), 0) as "Name",
    coalesce(sum(case when Field = 'age' then value end), 0) as "Age",
    coalesce(sum(case when Field = 'gender' then value end), 0) as "Gender"
 from "TABLE_1"
group by ID;

I am getting a conversion error:

  ORA-00932: inconsistent datatypes: expected CHAR got NUMBER.

What am I doing wrong?


texts:
I have a task to design database and problem is described as below:

I worked the problem and come up with my own design. sorry relations are overlapping I tried to make them explicit as I can

My Questions:

Are my tables in at least in 3NF?
I am using same address table for both client and employees, is this a good practice?
In two FKs of the clientaddresses and employeeadresses tables I am using different multiplicities is this a problem?
Any other comments


texts: Does a query with a primary key and foreign keys run faster than a query with just primary keys?

texts:
SELECT something FROM table WHERE primary_key = ?

vs.

SELECT something FROM table WHERE primary_key = ? AND other_key = ?

Say that this is a scenario where the inclusion of other_key does NOT change the resultset. Is the second query faster in practice? Or do databases just use a single best key if several are provided?


texts: bcp command Incorrect syntax near '�'. Character is actually: "ä"

texts:
I have  mssql-server and mssql-tools installed on Ubuntu (Linux). When I try to export data with the bcp command using the following command-line:

bcp DBname.dbo.Täble_Name out Täble_Name -c -k  -S127.0.0.1 -Usa -PpassWord -r ~

I get this error:

  SQLState = 37000, NativeError = 102
  Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '�'.

The � is ä.

If I surround the Täble_Name with square-brackets:

 bcp DBname.dbo.[Täble_Name] out Täble_Name -c -k  -S127.0.0.1 -Usa -PpassWord -r ~

I get this error on the object name:

  SQLState = S0002, NativeError = 208
  Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name 'DBname.dbo.Täble_Name'.

I went further and added single-quotes '' along with the -q option (which enables Quoted Identifiers):

bcp 'DBname.dbo.[Täble_Name]' out Täble_Name -c -k  -S127.0.0.1 -Usa -PpassWord -r ~ -q

The error becomes :

  SQLState = S0002, NativeError = 208
  Error = [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Invalid object name 'DBname.dbo.T�ble_Name'.

NB: the command works perfectly with table names without this special character ä .


texts: Geospatial join using ST_Contains or ST_Within seems slow, with costly query and bad estimate

texts:
Problem
I have two tables, one which which includes a point column and the other which includes a boundary (table definitions below). I want to select rows from the first column where the point is within the boundary in the latter column. Writing a SELECT query using what seems like a pretty straight forward join like so ends up being quite slow.
SELECT temp_locations.id
FROM temp_locations
INNER JOIN temp_boundaries
ON st_within(temp_locations.point, temp_boundaries.mpoly)
WHERE temp_boundaries.id = 11;

From a table with just over 120k rows, this takes almost 12s to return fewer than 70k rows (the exact timing isn't important here, but this is running on a new, amply powered developer machine).
The point gemoetry field is indexed, as is the mpoly geometry.
The boundaries are not trivial, they are US state boundaries, but even still this seems... very surprising to me.
I've tried both ST_Within and ST_Contains, expecting no difference but curious none the less (no difference!). The existing documentation and posts I've found about this all seem to point to doing exactly what this query does.
Am I missing something obvious here? Is this a function of using multipolygon geometry, and for filtering this should be simplified? Or is the expected and reasonable performance for this kind of query?
Tables and indexes
CREATE TABLE temp_boundaries (
    id integer,
    uuid uuid,
    mpoly geometry(MultiPolygon,4326),
    name character varying(255)
);
CREATE TABLE temp_locations (
    id integer,
    point geometry(Point,4326),
    street_address character varying(255),
    street_address_2 character varying(255),
    city character varying(255),
    state character varying(2),
    zip_code character varying(10)
);

CREATE UNIQUE INDEX temp_location_pkey ON temp_locations(id int4_ops);
CREATE UNIQUE INDEX temp_boundary_pkey ON temp_boundaries(id int4_ops);

CREATE INDEX temp_location_point_id ON temp_locations USING GIST (point gist_geometry_ops_2d);
CREATE INDEX temp_boundary_mpoly_id ON temp_boundaries USING GIST (mpoly gist_geometry_ops_2d);

EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
QUERY PLAN
Nested Loop  (cost=5.49..3530.86 rows=3080 width=4) (actual time=18.877..11298.389 rows=67498 loops=1)
  Output: temp_locations.id
  Buffers: shared hit=6157153
  ->  Index Scan using temp_boundary_pkey on public.temp_boundaries  (cost=0.29..8.30 rows=1 width=51649) (actual time=0.019..0.021 rows=1 loops=1)
        Output: temp_boundaries.mpoly
        Index Cond: (temp_boundaries.id = 11)
        Buffers: shared hit=3
  ->  Bitmap Heap Scan on public.temp_locations  (cost=5.21..3522.44 rows=12 width=36) (actual time=18.853..11277.169 rows=67498 loops=1)
        Output: temp_locations.id, temp_locations.point
        Filter: st_within(temp_locations.point, temp_boundaries.mpoly)
        Rows Removed by Filter: 4884
        Heap Blocks: exact=4072
        Buffers: shared hit=6157150
        ->  Bitmap Index Scan on temp_location_point_id  (cost=0.00..5.20 rows=123 width=0) (actual time=16.476..16.476 rows=72382 loops=1)
              Index Cond: (temp_locations.point @ temp_boundaries.mpoly)
              Buffers: shared hit=523
Planning Time: 2.262 ms
Execution Time: 11309.728 ms


texts:
I am making a blog cms system and the release date and time of the posts should be shown on my site.

Should I save the Datetime in the php code with a function and save it in a varchar in the database or should I use the default value of the database by using DATETIME.

Which is the more professional way to do this / The more efficient way


texts: PGAdmin4 Browser: Autocomplete not working

texts:
I am using PGAdmin4 on my Firefox Browser(Mac). I am looking for auto-complete feature but could not get it work.

The documentation says that the shortcut for auto complete is "Ctrl+Space" but it does not work.

Has anyone figured out how to make it work?


texts: MySQL Cluster - Management Node Redundancy

texts:
I am trying to design a fully redundant MySQL Cluster implementation for a service provider's network. Nodes within this cluster will be geographically separated between towns in the state that the service provider resides. Though I understand that using Geographic Replication for MySQL Cluster requires an asynchronous replication configuration, since the two data centers that the cluster nodes will reside in are connected via a high bandwidth (10Gbps), low latency pipe (~2ms), my goal is to have geographic diversity and synchronous replication across these data centers.

The nodes per data center break down like this:

Data Center A:

Management Node 1
SQL(API) Nodes 1 and 2
Data Node 1 (Node Group 1) and Data Node 2 (Node Group 2)

Data Center B:

Management Node 2
SQL Node 3
Data Node 3 (Node Group 1) and Data Node 4 (Node Group 2)

In this scenario, data node redundancy is strong (I believe), considering that a node from each node group will reside at each data center and a given data node in node group 1 (in either data center A or B) will have half of the data while the data node from the other node group in this same data center will have the other half of the data. In the instance of an isolation event or other type of failure that breaks apart the data centers, each location will have at least a full copy of the data.

My concern now becomes having a management node located in each data center. I am not sure if redundancy for the management nodes is best implemented this way or not. From what I understand about MySQL Cluster, if an isolation event occurs at one of the data centers (let's say data center A) and the connection between these two locations is lost, data nodes in data center B would start looking at management node 2 in this data center as the arbitrator, instead of the management node 1 in data center A, which would have originally been the arbitrator.

In my mind, this situation creates a split brain operation, where each data center is looking at its own arbitrator and, as a result, you still have all data nodes turned up in the cluster, yet only half of the storage locations are being written to. You break your synchronous environment.

Should I instead have the redundant management node also located in data center A on a separate physical resource, or does this change nothing about the logic of the cluster? Should I go ahead and instead create an asynchronous replication environment for the database and have each data center house its own cluster instead of breaking a cluster out between two geographic locations?

Warning: This is my first database project ever, so I am quite new to this stuff. I may be completely butchering this design implementation.


texts: Is it ever good practice to use a distinct database account for each user of an application?

texts:
The applications I'm used to are server based and use one database account for many users, with the application code controlling what the user can do, or single-user.

Are there any successful complex business applications where each person needs their own database account, and the database server is relied on to enforce policy rules about what each user should be allowed and not allowed to do?

I'm thinking about applications where multiple people contribute to information in one database, and can access information stored by others - e.g. colleagues in an organisation who all need to access customer records.

Also is there a name for this type of set up?


texts: In a unidirectional Master-Slave setup, should the slave be manually stopped in cases where the Master is offline due to Maintenance, Crash etc. ? Or should one just leave it so that it keeps trying to reconnect until the Master is up and running again ?


texts: Is it possible to tell the source of a backup?

texts:
I was wondering if it is possible to tell the difference between backups created manually and backups created through a maintenance plan.

There does not seem to be any way to discriminate them in the msdb backup related tables apart from the account that the Sql Agent runs under is placed in the user_name column in the msdb..backupset table

I would like to verify that backups done through a maintenance plan exist on disk but don't really care about any manually created ones. Part of the plan compresses and copies the archives to another server. These will be compared to make sure everything is ok with the plan.


texts: Independent aggregation of related entities, while also sorting by related (in a single statement)

texts:
I have a model with books and authors, with a many-to-many relationship between them (because a book can have multiple authors, and an author can write multiple books) through a table which I've named authorships.
My goal is to query for some subset of the books, and also get the set of related authorships, and the set (i.e. without duplicates) of related authors, each in some particular order. In essence I want to retain the normalized/separated structure of the records, I don't want to denormalize in any way (just sort).
Typically I think you'd do this using multiple statements, using a function or external code to provide the IDs to IN expressions or similar. However I've been able to use the following pattern in PostgreSQL to do it in a single statement:
WITH matched_books AS (
  SELECT id, title FROM books
  -- Could be any criteria:
  WHERE title LIKE 'The %'
),
related_authorships AS (
  SELECT authorships.id, book_id, author_id
  FROM authorships
  JOIN matched_books ON book_id = matched_books.id
),
related_authors AS (
  SELECT id, name
  FROM authors
  -- Could also use DISTINCT and do a join here, but I understand EXISTS is typically better for performance:
  WHERE EXISTS (SELECT 1 FROM related_authorships WHERE author_id = authors.id)
)
SELECT
  -- Scalar subqueries that each return a single JSON array of objects:
  -- JSON is completely fine for my purposes, but could also use array_agg.
  (SELECT json_agg(matched_books.* ORDER BY title) FROM matched_books) books,
  (SELECT json_agg(related_authorships.* ORDER BY id) FROM related_authorships) authorships,
  (SELECT json_agg(related_authors.* ORDER BY name) FROM related_authors) authors;

(Side note: In previous attempts I had used LEFT JOINs at the top level and json_agg(DISTINCT ...), but that left me unable to use ORDER BY meaningfully, and seemed messier/worse for performance.)
While this approach almost works great, I now want to order the books by information stored in their related authors and/or authorships. As an obvious example, let's say I want them to be sorted by the name of their author, or if any have multiple authors, use a column from authorships (it can just be the lowest integer id in this case) to determine which author should be used.
I can't think of an approach that will allow this while still returning the sets independently, at least not without some duplication of operations. How would you solve this?


texts: Index on Persisted Computed column needs key lookup to get columns in the computed expression

texts:
I have a persisted computed column on a table which is simply made up concatenated columns, e.g.

CREATE TABLE dbo.T
(
    ID INT IDENTITY(1, 1) NOT NULL CONSTRAINT PK_T_ID PRIMARY KEY,
    A VARCHAR(20) NOT NULL,
    B VARCHAR(20) NOT NULL,
    C VARCHAR(20) NOT NULL,
    D DATE NULL,
    E VARCHAR(20) NULL,
    Comp AS A + '-' + B + '-' + C PERSISTED NOT NULL
);

In this Comp is not unique, and D is the valid from date of each combination of A, B, C, therefore I use the following query to get the end date for each A, B, C (basically the next start date for the same value of Comp):

SELECT  t1.ID,
        t1.Comp,
        t1.D,
        D2 = (  SELECT  TOP 1 t2.D
                FROM    dbo.T t2
                WHERE   t2.Comp = t1.Comp
                AND     t2.D > t1.D
                ORDER BY t2.D
            )
FROM    dbo.T t1
WHERE   t1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY t1.Comp;

I then added an index to the computed column to assist in this query (and also others):

CREATE NONCLUSTERED INDEX IX_T_Comp_D ON dbo.T (Comp, D) WHERE D IS NOT NULL;

The query plan however surprised me. I would have thought that since I have a where clause stating that D IS NOT NULL and I am sorting by Comp, and not referencing any column outside of the index that the index on the computed column could be used to scan t1 and t2, but I saw a clustered index scan.

So I forced the use of this index to see if it yielded a better plan:

SELECT  t1.ID,
        t1.Comp,
        t1.D,
        D2 = (  SELECT  TOP 1 t2.D
                FROM    dbo.T t2
                WHERE   t2.Comp = t1.Comp
                AND     t2.D > t1.D
                ORDER BY t2.D
            )
FROM    dbo.T t1 WITH (INDEX (IX_T_Comp_D))
WHERE   t1.D IS NOT NULL
ORDER BY t1.Comp;

Which gave this plan

This shows that a Key lookup is being used, the details of which are:

Now, according to the SQL-Server documentation:

  You can create an index on a computed column that is defined with a deterministic, but imprecise, expression if the column is marked PERSISTED in the CREATE TABLE or ALTER TABLE statement. This means that the Database Engine stores the computed values in the table, and updates them when any other columns on which the computed column depends are updated. The Database Engine uses these persisted values when it creates an index on the column, and when the index is referenced in a query. This option enables you to create an index on a computed column when Database Engine cannot prove with accuracy whether a function that returns computed column expressions, particularly a CLR function that is created in the .NET Framework, is both deterministic and precise.

So if, as the docs say "the Database Engine stores the computed values in the table", and the value is also being stored in my index, why is a Key Lookup required to get A, B and C when they are not referenced in the query at all? I assume they are being used to calculate Comp, but why? Also, why can the query use the index on t2, but not on t1?

Queries and DDL on SQL Fiddle

N.B. I have tagged SQL Server 2008 because this is the version that my main problem is on, but I also get the same behaviour in 2012.


texts:
Our compagny has a central Sql Server Database which is connected to many applications (Web, Backend/Frontend services, backoffice, ..) This database is hosted in a small country (for taxing advantages) where bandwidth is expensive.

Is there any way to have a detailed view of bandwidth usage ? such as how bytes are sent per application, per stored procedure, per login...

I want to monitor this at Sql Server because I can not do this at client side (too many clients and it is not suitable to alter client code for that)


texts: Error 1366: Incorrect string value: '\xF0\x9F\x98\x8A\x0AC…' for column 'text_body' at row 1

texts:
I'm trying to store email content in mysql.

But i'm facing errors while trying to insert the row.

I have done some research and changed the column type to utf8mb4. But I'm still facing the problem.

Can someone tell me what i'm missing?

This is the error I'm getting

Error 1366: Incorrect string value: '\xF0\x9F\x98\x8A\x0AC...' for column 'text_body' at row 1

This is the character set of my table DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci


texts: Suggestion for enforcing unique constraint in a table where soft delete is implemented

texts:
I am using SQL Server 2014 and in my database, I have a Position table which is structured like this:

ID         int           PK
Name       nvarchar(50)  Unique Index
IsDeleted  bit

A user can't enter positions with similar names; IsDeleted is a flag for soft delete as I want to avoid deleting records from this table. However it got me thinking in a scenario like this:

1. User enters position 'President'.
2. User deletes position 'President'.
3. User changes mind and again enters a position 'President'.

This would result in an error due to unique constraint. Even if I use Name and IsDeleted combination for unique constraint, there is no stopping the user from deleting again and adding several times. One thing I can think of is having Name and TimeStamp combination as unique constraint, but it doesn't sound correct.

I'd like to enforce this if possible in the database, and not the application logic. Can you guys give some suggestions?


texts:
I have two table question and answer. The structure is given below

CREATE TABLE IF NOT EXISTS `question` (
`id` int(10) NOT NULL,
  `question` varchar(100) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

INSERT INTO `question` (`id`, `question`) VALUES
(1, 'Who is Mammotty'),
(2, 'Who Rahul Ghandi');

CREATE TABLE IF NOT EXISTS `answers` (
`id` int(10) NOT NULL,
  `q_id` int(10) NOT NULL,
  `answer` varchar(100) NOT NULL,
  `status` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

INSERT INTO `answers` (`id`, `q_id`, `answer`, `status`) VALUES
(1, 1, 'Actor', 'Y'),
(2, 1, 'Farmer', 'N'),
(3, 1, 'Engineer', 'N'),
(4, 1, 'Driver', 'N'),
(5, 2, 'Politician', 'Y'),
(6, 2, 'Teacher', 'N'),
(7, 2, 'Doctor', 'N'),
(8, 2, 'Pilot', 'N');

I want to display question and answer in this format

question         | ans1       | status1 | ans2   | status2 | ans3     | status3 | ans4   | status3
---------------------------------------------------------------------------------------------------
Who is Mammotty  | Actor      | Y       | Farmer | N       | Engineer | N       | Driver | N
Who Rahul Ghandi | Politician | Y       | Teacher| N       | Doctor   | N       | Pilot  | N

Can any one Help me ?


texts: How does this derived table with cross apply work?

texts:
I borrowed some code on how to compact and uncompact a string of diagnosis codes in order to build a bridge table for my data warehouse. It works well. However, I just do not understand how it is doing the uncompacting. Here Is the SQL Fiddle for the below code

create table dimDiagnosisGroup (dxGroupKey int, dxCodeList nvarchar(1024))
insert into dimDiagnosisGroup
values  (1,'042, 070.70, 722.10'),
        (2,'042, 070.70, 780.52, 496, 716.90, 581.9'),
        (3,'042, 070.70, 782.1, V58.69'),
        (4,'042, 070.70, 782.3, V58.69')

 WITH XMLTaggedList AS (
     SELECT dxGroupKey,
        CAST('' + REPLACE(dxCodeList, ', ', '') + '' AS XML)
            AS Diagnosis_Code_List
     FROM dimDiagnosisGroup
 )
     SELECT dxGroupKey,
            ExtractedDiagnosisList.X.value('.', 'VARCHAR(MAX)') AS dxCodeList2
          FROM XMLTaggedList
        CROSS APPLY Diagnosis_Code_List.nodes('//I') AS ExtractedDiagnosisList(X)

I understand the XMLTaggedList part fine. What I am not understanding is the Cross Apply to the ExtractedDiagnosisList(X), then the ExtractedDiagnosisList.X.value('.', 'VARCHAR(MAX)').

When I hover over ExtractedDiagnosisList in the select statement, SSMS says it is a derived table. However, it kind of looks like a function to me. I am not understanding how Diagnosis_Code.List gets the .nodes('//I') function. Finally, the ExtractedDiagnosisList.X.value section just looks foreign to me, in SQL. It looks like syntax from a language like C#.


texts:
MySql database at the moment has:

Character Set: utf8
Default Collation Name: utf8_unicode_ci

I want to change it to:

utf8mb4
utf8mb4_unicode_ci

I would also do the same thing for all tables in the database.

My question is the following:

By doing that change is there a possibility that the content of my columns gets messed up in any way? (Cause I am really scared to do it!!!)
I have millions of rows in the tables, is there ANY way to do a check after I make the switch that the content of all tables is the OK?

Some crazy ideas I am thinking of include:

Running some scripts to compare original DB to changed DB
Running some scripts to calculate column sizes of original DB to changed DB

Any help or ideas which would guide me to the right direction would be appreciated.

THANK YOU


texts: Given the same input, is the HASHBYTES function guaranteed to return the same output?

texts:
If I use the SQL SERVER HASHBYTES() function in a script on my machine, is it guaranteed to produce the same output if run on another machine/instance?

For example, will

SELECT  HASHBYTES('SHA1', 'Hello World')

generate the same output regardless of SQL version, processor architecture etc?

and will

SELECT  HASHBYTES('SHA1', column1) from table1

generate the same output if the column definition is slightly different - varchar(50) vs varchar(100) - for example?

Is there anything that needs to be considered here?


texts: Create second instance of mysqld on windows xp

texts:
I am trying to install the new version of the mysql on the windows7 and windows xp . In few of the computers already a new version of mysql are installed. Here I just want to create a second instance of the service of mysqld with my my_XX.ini file.

I have successfully configured the instance on the windows 7 pc . But can not create instance on the windows xp for the same .

I am trying this code on the command prompt which run as administrator. but it does not start the service

sc create mysqld2 binPath= c:\mysql\bin\mysqld.exe --defaults-file=c:\mysql\my_XX.ini

Even I have tried with the mysqld command to start the mysql as per other my_xx.ini with

mysqld --defaults-file=c:\mysql\my_XX.ini

I am not able to create a second instance on the windows xp . Please help me to start the second instance of mysql on windows xp with windows service