`cube` query performance issues with ordering I have a cube column named embedding in a documents table storing a vectorized (TF-IDF) representation of some text in the field which I converted into dense format. I created a GIST index on the
But I am having trouble with query performance. It takes ~20 seconds on this query (~5MM rows on a 32GB machine):

select id
from documents
where embedding <-> cube('(0.08470847,...,0.06106149)') < 0.25
order by embedding <-> cube('(0.08470847,...,0.06106149)') asc
limit 25

The same query without the order by performs within milliseconds.

I am not sure how to improve the ordering performance.

I ran explain analyze on the query and this is the result:

Limit  (cost=0.54..323.63 rows=25 width=12) (actual time=18032.104..18704.827 rows=25 loops=1)
  ->  Index Scan using ix_100 on documents  (cost=0.54..22895274.16 rows=1771566 width=12) (actual time=18032.101..18704.797 rows=25 loops=1)
        Order By: (embedding <-> '(0.084708469999999994,... , 0.061061490000000003)'::cube)
        Filter: ((embedding <-> '(0.084708469999999994,... , 0.061061490000000003)'::cube) < '0.25'::double precision)
Planning Time: 1.575 ms
Execution Time: 18728.073 ms

I am at a loss how to proceed from here, I wish to avoid sorting after fetching the results in the application layer and ideally should work within the database.

Any ideas?

Edit: adding the explain(analyze,buffers) for the query with limit

query:

explain (analyze, buffers)
select id
from documents
where embedding <-> cube('(0.08470847,..,0.06106149)') < 0.25
limit 10;

with this output:

Limit  (cost=0.00..7.73 rows=10 width=4) (actual time=0.036..0.076 rows=10 loops=1)
  Buffers: shared hit=5
  ->  Seq Scan on documents  (cost=0.00..1370989.16 rows=1772915 width=4) (actual time=0.034..0.072 rows=10 loops=1)
        Filter: ((embedding <-> '(0.084708469999999994..., 0.061061490000000003)'::cube) < '0.25'::double precision)
        Rows Removed by Filter: 10
        Buffers: shared hit=5
Planning Time: 0.107 ms
Execution Time: 0.098 ms

Edit -2 :

modified query per last update and results are back to ~20 secs per query

Limit  (cost=0.54..323.56 rows=25 width=12) (actual time=727.488..21603.571 rows=25 loops=1)
  Buffers: shared read=1352076
  ->  Index Scan using ix_100 on documents  (cost=0.54..22910761.65 rows=1773159 width=12) (actual time=727.485..21603.535 rows=25 loops=1)
        Order By: (embedding <-> '(0.0665496899999999947, ... 0.063358020000000001)'::cube)
        Filter: ((embedding <-> '(0.0665496899999999947, ... 0.063358020000000001)'::cube) < '0.25'::double precision)
        Buffers: shared read=1352076
Planning Time: 0.164 ms
Execution Time: 21644.516 ms

Sorting works on values the query returns.

Here you have an index on embedding column but you are sorting on the result of embedding <-> cube('(0.08470847,...,0.06106149)'), which is not indexed.

So first retrieve the required result with the help of sub-query, then perform sorting.

select id,EDistance
from
(
select id, embedding <-> cube('(0.08470847,...,0.06106149)') EDistance
from documents
where embedding <-> cube('(0.08470847,...,0.06106149)') < 0.25
limit 25
) t
order by EDistance ASC

Thanks!


Minimal logging for bulk inserts? I have many scripts running that truncate entire tables and rewrite data daily, however this  fills my transaction logs very quickly.  All these scripts use INSERT INTO .. SELECT. So recently I've changed my database to the Bulk-Logged recovery model, and I added the with (TABLOCK) to all my insert statements.  This doesn't seem to be doing anything, because according to https://www.mssqltips.com/sqlservertip/1185/minimally-logging-bulk-load-inserts-into-sql-server/ if the table has a clustered or non-clustered index, it still gets fully logged.  Should I then drop all the indexes before inserting, and recreate them after the table is populated?  Or is there a better method to do this?

Paul White has a really detailed post about a way to get minimal logging for INSERT...SELECT statements that target clustered tables with secondary indexes.  Check out the full article here:
Minimal Logging with INSERT…SELECT and Fast Load Context
The main thing you're probably missing is that you need to enable trace flag 610.
Read the whole thing to see make sure you get past all the conditions and edge cases - especially around estimated number of rows to be inserted, existing number of pages in indexes, and the estimated data size of rows being inserted.


Default value for CHARSET when creating a table I came across this query in a book i'm learning from.
  CREATE TABLE `articles` (
      `title` varchar(1000),
      `content` text,
      `links` text,
      `image` varchar(500),
      `url` varchar(500),
      UNIQUE KEY `url` (`url`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

I want to understand the last line of this query, in particular "DEFAULT CHARSET=utf8"
I looked up the documentation on table options

[DEFAULT] CHARACTER SET
Specifies a default character set for the table. CHARSET is a synonym for CHARACTER SET. If the character set name is DEFAULT, the database character set is used.

From the first line of this explanation, "[DEFAULT] CHARACTER SET" it looks to me as if this actually setting "CHARACTER SET" to default for all subsequent table creations, as well as this current one?
in the case of the code from the book, database character set is different to utf8, which would be used? If it's utf8 still, why have they put DEFAULT there anyway?
Is the last line of the documentation above talking about this?
CREATE TABLE `articles` (
      `title` varchar(1000),
      `content` text,
      `links` text,
      `image` varchar(500),
      `url` varchar(500),
      UNIQUE KEY `url` (`url`)
    ) ENGINE=InnoDB DEFAULT CHARSET=DEFAULT

thanks.

You can remove Default, it is not mandatory. If you look att the grammar it says:
| [DEFAULT] CHARACTER Set =

Meaning that the table gets this CHARACTER SET. It does not affect other tables.
If no CHARACTER Set is declared it is "inherited" from the database definition. This is the same as:
CHARACTER Set = DEFAULT

I dont know if it is part of the confusion, but there are two different DEFAULT keywords here, with different meaning. The first says what the default CHARACTER Set for this table is, the second one that the default character set for the database should be used.


MySql query with OR parameter SELECT task_name,task_id,
       SUBSTR(task_text,1,20) AS task_text,
       task_project_id,task_status
    FROM projectboard_tasks
    WHERE task_status IN  ('0','3')
     AND  task_assigner = 31 OR  task_assigned_to = 31

Here in query i return the rows with task status 0 and 3 but due to OR the query returns rows which are not matching the task_status condition

I get the rows which are not with status 0 or 3 because it match the task_assigner with other rows and shows the result.

Is there any workaround for this ?

You need to apply proper parenthesis ( ). You need to apply parenthesis for the condition (task_assigner = 31 OR task_assigned_to = 31), so the task_status IN ('0', '3') and the task_assigner both conditions will satisfy.

The working query will be:

SELECT task_name, task_id, SUBSTR(task_text, 1, 20) AS task_text,
       task_project_id, task_status
FROM projectboard_tasks
WHERE task_status IN ('0', '3')
    AND (task_assigner = 31 OR task_assigned_to = 31)


Understanding “bitmap heap scan” and “seq scan” I'm trying to understand that which scan is efficient, Here is the explain for the query,

EXPLAIN SELECT *
FROM 'myTable_v' --view
WHERE cast(TIMESTAMP AS VARCHAR(100)) > CAST(1586415955169 AS VARCHAR(100))
AND cast(TIMESTAMP AS VARCHAR(100)) <= CAST(1586416090443 AS VARCHAR(100))

above view created as below,

CREATE VIEW 'myTable_v' AS
SELECT
msg ->> 'customerType'::text AS customertype,
msg ->> 'trackingId'::text AS "trackingId",
msg ->> 'conversationId'::text AS "conversationId",
msg ->> 'authState'::text AS authstate,
msg ->> 'tmoId'::text AS id,
msg ->> 'msisdn'::text AS sdn,
msg ->> 'ban'::text AS ban,
FROM myTable -- main table
WHERE (msg ->> 'type'::text) = 'CTYPE'::text;

Planner output is,
Without index

"Seq Scan on 'myTable' (cost=0.00..472877.98 rows=7300 width=904)"
"  Filter: (((msg ->> 'type'::text) = 'EdsConversationData'::text) AND ((("timestamp")::character varying(100))::text > '1586415955169'::text) AND ((("timestamp")::character varying(100))::text <= '1586416090443'::text))"

and after adding index like below,

CREATE INDEX idx_timestmp_n ON 'myTable' ( (msg ->> 'type'),((('timestamp')::character varying(100))))

Query planner out is,

"Bitmap Heap Scan on 'myTable'  (cost=505.45..49649.59 rows=7300 width=904)"
"  Recheck Cond: ((msg ->> 'type'::text) = 'EdsConversationData'::text)"
"  Filter: (((("timestamp")::character varying(100))::text > '1586415955169'::text) AND ((("timestamp")::character varying(100))::text <= '1586416090443'::text))"
"  ->  Bitmap Index Scan on idx_timestmp_n  (cost=0.00..505.43 rows=14517 width=0)"
"        Index Cond: ((msg ->> 'type'::text) = 'EdsConversationData'::text)"

So, here which one is suggestible to execute the query with index or without?

Appreciated for the responses.

Thank you

The planner thinks the bitmap scan is faster.  If you want to know which is actually faster, run them and see.  To get the best info, turn on track_io_timing and run the query with EXPLAIN (ANALYZE, BUFFERS).  If your system has slow clock access, you might want to add TIMING OFF.

There is no magic source of truth we can reveal to you just by looking at your post.  If there were, we would make the planner use it; and then it would never be wrong.


How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql I have legacy tables similar to the following:

employee

------------------------------------
|  employee_id  |   name
------------------------------------
|  1            |   David
|  2            |  Mathew
------------------------------------

payroll

-------------------------------------
|  employee_id  | salary
-------------------------------------
|   2           |  200000
|   3           |  90000
-------------------------------------

I want to get the following data, after joins and filters:

-----------------------------------------------------------
|  address_id  | employee_id  |  address
-----------------------------------------------------------
|   1          |  2           |  street 1, NY
|   2          |  2           |  street 2, DC
------------------------------------------------------------

I have the following query:

SELECT employee_id, salary, address_arr
FROM employee
LEFT JOIN payroll on payroll.employee_id = employee.employee_id
INNER JOIN
     (
       SELECT employee_id, ARRAY_AGG(address) as address_arr
       FROM addresses
       GROUP BY employee_id
     ) table_address ON table_address.employee_id = employee.employee_id
WHERE employee.employee_id < 1000000
LIMIT 100
OFFSET 0

Above query gives the desired output but is highly unoptimized as the GROUP BY operation occurs over the complete addresses table before being used for JOIN operation the outer query.

Kindly answer:

How can we avoid the GROUP BY operation to occurs over the complete
addresses table by using LIMIT OFFSET of the outer query?
Will the condition WHERE employee.employee_id < 1000000 be applied on subquery before or after the GROUP BY operation in the inner query. If the condition is applied after the GROUP BY, how can we avoid that?

Note: There are multiple JOINs and subqueries in the actual query being used.

I am not sure if this is really more efficient, but you could try to join to a derived table that applies the limit.

select emp.employee_id, emp.salary, adr.address_arr
from (
  SELECT employee_id, salary, address_arr
  FROM employee
    LEFT JOIN payroll on payroll.employee_id = employee.employee_id
  WHERE employee.employee_id < 1000000
  LIMIT 100
  OFFSET 0
) as emp
  JOIN (
     SELECT a.employee_id, ARRAY_AGG(a.address) as address_arr
     FROM addresses a
     GROUP BY employee_id
  ) as adr ON adr.employee_id = emp.employee_id;

The first derived table only selects 100 rows, and the join/group by should then only be done for those 100 employees.

If the optimizer doesn't push that down, you could try a lateral join instead to "force" a push down:

select emp.employee_id, emp.salary, adr.address_arr
from (
  SELECT employee_id, salary, address_arr
  FROM employee
    LEFT JOIN payroll on payroll.employee_id = employee.employee_id
  WHERE employee.employee_id < 1000000
  LIMIT 100
  OFFSET 0
) as emp
  LATERAL JOIN (
     SELECT a.employee_id, ARRAY_AGG(a.address) as address_arr
     FROM addresses a
     WHERE a.employee_id = emp.employee_id
     GROUP BY employee_id
  ) as adr ON adr.employee_id = emp.employee_id;

The join condition isn't really needed, but it dosn't hurt either


Mongodb - Will Bulk.find.remove() lock the collection I'm planning to run Bulk.find.remove() on a very big collection. (1.2TB).

Will it block inserting or updating other records (which are not a part of the above find)?

The short answer is: No, you will not be blocked.

When performing multi-document write operations, whether through a single write operation or multiple write operations, other operations may interleave.

The long explains are:

Collection is granular level (or the resource).

Whenever there is a write operation, Exclusive (X) lock mode must be applied on the resource.

As Collection is the resource in this scenario, Exclusive (X) lock mode must be applied on the Collection.

For all outer levels : Database, Global; Intent Exclusive (IX) must be applied.

When a write operation is triggered for a Collection, the control must do the following series of locks :

The MongoD instance must be locked in Intent Exclusive (IX) lock mode, with an intent to apply write operation in a granular level (Database / Collection / Document).
The Database must be locked in Intent Exclusive (IX) lock mode, with an intent to apply write operation in a granular level (Collection / Document).
The Collection must be locked in Exclusive (X) lock mode, to apply write operation on the Collection.

You can find more info in the following links:

https://docs.mongodb.com/manual/core/write-operations-atomicity/
https://docs.mongodb.com/manual/faq/concurrency/
https://docs.mongodb.com/manual/reference/method/Bulk.find.remove/
https://www.tutorialkart.com/mongodb/mongodb-locks/


Mongos version 3.0.1 Split Chunk Error with Sharding I have 3 config server and two shards. Each shard has a primary, secondary and an arbiter.

This is running with the following mongod settings:

storageEngine=wiredTiger

wiredTigerJournalCompressor=zlib

wiredTigerCollectionBlockCompressor=zlib

The mongos instance created the following log entries below about not being able to get a lock during the sharding balancing. All the data can be queried fine however.

Also all seems fine with sh.status() command:

shards:

{  "_id" : "ib3_prod_rs0",  "host" : "ib3_prod_rs0/ip-10-0-13-119.ec2.internal:27017,ip-10-0-21-50:27017" }

{  "_id" : "ib3_prod_rs1",  "host" : "ib3_prod_rs1/ip-10-0-6-138:27017,ip-10-0-8-202.ec2.internal:27017" }

balancer:

Currently enabled:  yes

Currently running:  yes

    Balancer lock taken at Wed Apr 01 2015 00:00:49 GMT+0000 (UTC) by 54.163.248.60:27017:1427843620:1804289383:Balancer:1681692777

Failed balancer rounds in last 5 attempts:  0

Migration Results for the last 24 hours:

    No recent migrations

How can I debug what is going on?

  2015-03-31T23:41:06.542+0000 I SHARDING [Balancer] moveChunk result: {
  ok: 0.0, errmsg: "could not acquire collection lock for
  cubes_prod.pushqueues to migrate chunk [{ : MinKey },{ : MaxKey }) ::
  caused by :: could not get status from ser...", $gleStats: {
  lastOpTime: Timestamp 0|0, electionId:
  ObjectId('551b29f564e83f84e725241e') } } 2015-03-31T23:41:06.543+0000
  I SHARDING [Balancer] balancer move failed: { ok: 0.0, errmsg: "could
  not acquire collection lock for cubes_prod.pushqueues to migrate chunk
  [{ : MinKey },{ : MaxKey }) :: caused by :: could not get status from
  ser...", $gleStats: { lastOpTime: Timestamp 0|0, electionId:
  ObjectId('551b29f564e83f84e725241e') } } from: ib3_prod_rs1 to:
  ib3_prod_rs0 chunk:  min: { UserKey: MinKey } max: { UserKey: 0 }

According to what you found out yourself, I think I can provide a detailed explanation.

How chunk migration works (simplified)

Here is how chunk migration works (simplified for the sake of shortness):

When chunk exceeds the configured chunkSize (64MB by default), the mongos which caused the size increase will split the chunk.
That chunk is split on the shard the original chunk resided on.  Since the config servers do not form a replica set, it has to update all three config servers.
If the difference in the number of chunks reaches the chunk migration threshold, the cluster balancer will initiate the migration process.
The balancer will try to acquire a balancing lock. Note that because of this there can be only one chunk migration at any given point in time. (This will become important in the explanation what happened.)
In order to acquire the balancing lock, the balancer needs to update the config on all three config servers.
If the balancing lock is acquired, the data will be copied over to the shard with the least chunks.
After the data is successfully copied to the destination, the balancer updates the key range to shard mapping on all three config servers
As a last step, the balancer notifies the source shard to delete the migrated chunk.

What happened in your case

Your data was increasing to a point where chunks had to be split. At this point, all three of your config servers were available and the metadata was updated accordingly. Chunks splitting is a relatively cheap operation in comparison to chunk migration and may happen often, so under load, you usually have a lot more chunk splits than migrations. As said before, only one chunk migration can happen at any given point in time.

Due to whatever problems, one or more of your config servers became unreachable after the chunks were split, but before enough chunks were migrated to balance them out below the chunk migration threshold, since there can only be one running chunk migration at any given point in time. Bottom line: Some time before every chunk that needed to be migrated was actually migrated, one or more of your config servers became unavailable.

Now the balancer wanted to migrate a chunk, but could not reach all config servers to acquire the global migration lock. Hence the error message.

How to deal with such situation

It is very likely that your config server were out of sync. In order to deal with this situation, please read Adam Comerfords answer to mongodb config servers not in sync. Follow it to the letter.

How to prevent

Plain and simple: use MMS. It is free, gives a lot of information about health and performance and using the automation agent, administration of a MongoDB cluster can be done a lot faster.

I tend to suggest to install at least 3 monitoring agents, so you can have a scheduled downtime on one while maintaining redundancy with the others.

MMS has alerting capabilities, so you will be notified if one of your config servers becomes unavailable, which is a serious situation.


When you switch Table A to Table B, does the index data get switched as well? I currently have a rather large table (5-7 million rows). This table is rebuild regularly by a procedure which builds the data in a staging table, then switches the data into the production table using the ALTER TABLE .. SWITCH TO .. statement.

Example:

BEGIN TRAN;

-- Rebuild indexes
ALTER INDEX IX_NC_GroupEvent_staging_GroupName on [dbo].[GroupEvent_staging]
   REBUILD;

ALTER INDEX IX_NC_GroupEvent_staging_Created ON [dbo].[GroupEvent_staging]
   REBUILD;

-- Empty production table
TRUNCATE TABLE [dbo].[GroupEvent];

-- Switch data from staging-table into production table
ALTER TABLE [dbo].[GroupEvent_staging] SWITCH TO [dbo].[GroupEvent]

COMMIT;

When this operation is performed, does the current state of the indexes (or index data if you will) get switched as well? I am asking because of 2 reasons:

1) In order to perform a SWITCH TO statement, a requirement is that both the source and target table must contain identical indexes. This leads me to believe the index-data might be switched as well, but I don't know how to verify this.
2) The main benefit of building the table in this manner is to avoid performing excessive work on the production table while in use. Naturally, it would make me very happy if I were able to rebuild indexes on the staging-table and have the rebuilt indexes being switched over to the production indexes along with the table.

does the index data get switched as well?

Yes. It would be bizarre if it didn't because then queries would return the wrong results or we would have to manually rebuild the indexes after switching.

  I don't know how to verify this

One way would be to just try it

CREATE TABLE [dbo].[GroupEvent]
  (
     GroupName VARCHAR(100) INDEX IX_NC_GroupEvent_staging_GroupName,
     Created   DATETIME INDEX IX_NC_GroupEvent_staging_Created
  );

CREATE TABLE [dbo].[GroupEvent_staging]
  (
     GroupName VARCHAR(100) INDEX IX_NC_GroupEvent_staging_GroupName,
     Created   DATETIME INDEX IX_NC_GroupEvent_staging_Created
  );

INSERT INTO [dbo].[GroupEvent_staging]
VALUES      ('Group1',GETDATE()),
            ('Group2',GETDATE());

ALTER INDEX IX_NC_GroupEvent_staging_GroupName ON [dbo].[GroupEvent_staging] REBUILD;

ALTER INDEX IX_NC_GroupEvent_staging_Created ON [dbo].[GroupEvent_staging] REBUILD;

SELECT index_id,
       allocated_page_file_id,
       allocated_page_page_id
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('[dbo].[GroupEvent_staging]'), NULL, NULL, 'DETAILED')
WHERE  is_allocated = 1;

-- Empty production table
TRUNCATE TABLE [dbo].[GroupEvent];

-- Switch data from staging-table into production table
ALTER TABLE [dbo].[GroupEvent_staging] SWITCH TO [dbo].[GroupEvent];

SELECT index_id,
       allocated_page_file_id,
       allocated_page_page_id
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('[dbo].[GroupEvent]'), NULL, NULL, 'DETAILED')
WHERE  is_allocated = 1;

SELECT GroupName
FROM   [dbo].[GroupEvent];

DROP TABLE [dbo].[GroupEvent], [dbo].[GroupEvent_staging];

The only object accessed in the process of returning the two rows was the index showing that the data must have been switched.

The above also compares the result from sys.dm_db_database_page_allocations for GroupEvent_Staging before the switch with a similar query for GroupEvent after the switch to see that the pages remain the same for both the heap itself (index_id=0) and the two nonclustered indexes (ids 2 & 3). This shows the switch was metadata only with ownership of the allocated pages transferred.


DB Design. Tracking history without losing referential integrity To make it simple, let’s say we have these 2 tables:

CREATE TABLE `tbl_companies` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `tbl_workers` (
  `id` int(11) NOT NULL,
  `name` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);

And another 2 tables to store the relationship between companies and workers

CREATE TABLE `tbl_companies_workers` (
  `company_id` int(11) NOT NULL,
  `worker_id` int(11) NOT NULL,
  PRIMARY KEY (`company_id `, `worker_id `)
);

CREATE TABLE `tbl_companies_workers_history` (
  `company_id` int(11) NOT NULL,
  `worker_id` int(11) NOT NULL,
  `start` date NOT NULL,
  `end` date DEFAULT NULL,
  PRIMARY KEY (`company_id `, `worker_id `)
)

With this schema it’s easy to display something like: “John Smith worked at MyComp from 2010 to 2012”. But if MyComp is deleted from de DB, then that information is deleted too. I’d like to be able to display the same info even though the company is deleted.

Is it possible to achieve this without losing referential integrity? What would be the best approach? I've been looking for some info and I found this and this, but still it’s not clear to me.

Thanks in advance.

This is a long-standing problem with lots of awkward solutions like the ones you referenced. The best ones still leave the problem of referential integrity. How do you reference an entity that changes over time and you want to maintain all the versions?

The solution actually turns out to be very simple: normalization. When we normalize an entity, there is a master entity table. This contains the PK of the entity and all other attributes. All FKs from other tables refer to this table as there is one and only one entry for any given entity.

After normalization, this table still exists but there is (at least) one other table that contains some of the attributes. There could be zero, one or several entries for any unique entity but that is fine. In fact, the PK of this table is the PK of the entity (which is also a FK back to the master entity table) and some other attribute.

So now you implement Version Normal Form. Take all the attributes of the main entity and separate out those attributes that change over time and you want to track those changes. What you have is the master entity table with the one and only PK of the entity (referential integrity works as normal) and all attributes that either don't change over time or the changes aren't tracked. Then the "version" table contains all the attributes that change and are tracked. The PK of this table is the entity PK and the change date (the date and time the version took affect).

A partial list of advantages of this scheme are

It uses normalization, a process we all know and work with daily.
Referential integrity is not affected.
Current and past versions are in the same table. In fact, the query to access the current state of the data and the query to look back into the past is the same query.
The change date can be set to the future for those instances when a state change is known ahead of time. Such planned changes do not appear in queries until the change time is reached.

There are two methods of "soft delete" that can be used. A "deleted" datetime attribute can be added to the master record with the default being NULL or (as I prefer) the magic date 9999-12-31. This is what I call a "firm" delete because it should not be undo-able. The other method is to add an "isDeleted" attribute to the version record. A delete operation would insert a version with "isDeleted" set to TRUE (or 'Y' or whatever). The deletion would be just another version. So to undelete, a new version with "isDeleted" turned off would bring the entity back into existence -- with the times it was deleted and then undeleted maintained like any other state change. Of course, both methods may be used if needed.

Here are the slides of a presentation I have made a few times at tech fairs. It covers how all the above can be done including the queries. And here is a document that goes into a lot more detail.


How to handle too many inserts? I have a database which is accessed from remote locations in the world with very long delays and poor transfer speeds. I currently transfer the data using a transaction, since either all or no data should be inserted. Currently the values are inserted one at a time. It takes a total of about 37 seconds for 200 inserts before the commit.

The problem is that there may be 10 sites continuously trying to insert data within a 2 minute interval. If they fail (due to concurrency errors) they will retry until they succeed. This adds up to an impossible situation as they will start growing a backlog.

I understand I can use a snapshot isolation level to allow reads in parallel, but how should I handle the parallel insets? How do banks and other systems do it since they should also get a backlog if they do it in a queue?

I use SQL Server and on the remote client I use C# to talk to the SQL Server.

The inserts are of 10 different types and are they are linked in pairs of two. The inserts are connected in pairs. The first insert is a generic one which is the same for all and are inserted into the same table - let's call it table_A.
Then the second insert is specific depending on the type of insert data. The second insert is made to 1 of 10 tables and the data varies greatly but that table also store the row index of the first insert in table_A. This is how they are linked.

The transfer speed from a good connection is about 1 second and that is no problem. The problem is the remote part that takes about 40 seconds. Table_A contains about 10 million rows and is well indexed. All other tables are somewhat smaller and well indexed.

I've seen similar problems addressed by decoupling the app from the central server:

Remote sites install a local SQL Server Express along with the app
Apps talk to the local Express instance. Low latency, good availability.
SQL Server Express uses Service Broker to deliver the updates to the central server
Service Broker handles the network availability, latency etc.
The central server uses Service Broker activation to modify the destination tables based on the updates received from the remote sites.

This way you can handle transparently pretty big backlogs when the sites experience poor network or no network. SSB will take care of the delivery (retry etc) while the remote apps continue to experience local availability times.

SSB isolates your central server data (transactions) from a slow network. There won't be any data locks while the SSB messages are being exchanged, no matter how slow the transfer.

In one incident I know a remote site that was disconnected for 42 days, kept working, and then resumed delivery and pushed all the updated in about 30 minutes once it was reconnected.

See also this presentation for a similar case.


multi-condition left join conditions in MS access Is there another way to write the following poorly performing in Access (Jet) SQL?

SELECT *
FROM
  Det left join
  (select * from Inv inner join Dep on Inv.SN=Dep.SN) as mass ON
    (Det.RecSN = mass.SelfID) and (Det.DDTime between mass.Start and mass.End)

eliminating the sub-select and doing a direct join does not work, giving a JOIN expression not supported Error in the following snippet

SELECT *
FROM
  Det left join
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    (Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End)

The following produces a result, but suppresses Det records that don't match up to one or the other of the inner tables

SELECT *
FROM
  Det inner join
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    (Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End)

adding another set of parentheses doesn't help other than helping Access highlight the join clause that's problematic

SELECT *
FROM
  Det left join
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    ((Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End))

dropping the between clause to a where condition results in no left join characteristics (DDTime isn't between NULL and NULL) and can lead to too many entries being held in memory while other joins are being performed to this whole mess, and can make it more logically difficult to add other where conditionals.

SELECT *
FROM
  Det left join
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    Det.RecSN = Inv.SelfID
WHERE
  Det.DDTime between Dep.Start and Dep.End

Adding a clause to make left join behavior re-emerge (but only for the portion in the ON clause) makes the world slow to a crawl, I think because Access doesn't know how to handle OR clauses without essentially running a union all in the background for each of the sides of the OR clause with the rest of the query.

SELECT *
FROM
  Det left join
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    Det.RecSN = Inv.SelfID
WHERE
  Det.DDTime between Dep.Start and Dep.End
  OR Dep.Start is NULL

My current work-around is to use the inner join method, then have another query that queries this one ("qCombo") and compares against the Det table for entries in Det, but not in qCombo using either a left join or where not exists. But this solution is just as inefficient (if not more so) than the sub-select method at top.

SELECT 'qCombo' as QueryName, *
FROM
  Det inner join
  (Inv inner join Dep on Inv.SN=Dep.SN) ON
    (Det.RecSN = Inv.SelfID) and (Det.DDTime between Dep.Start and Dep.End)

Select Det.*
FROM
  Det LEFT JOIN
  qCombo ON
    (Det.DDTime=qCombo.DDTime) and
    (Det.OtherFieldsInUniqueIdx=qCombo.OtherFieldsInUniqueIdx)
WHERE
  qCombo.QueryName IS NULL

Other possibilities?

Are the tables you are querying indexed?

If not, I would add an index to the "Inv.SN=Dep.SN"columns ,
" Det.RecSN = Inv.SelfID" and lastly the dates "Det.DDTime, Dep.Start".

NOTE: Be careful when adding indexes. Specially if you are working in your production database. Do your research first on creating indexes on transactional tables, to avoid locks.

If you already have these indexed, then how many records do you have on each table?

Suggestion: I would Separate the OR conditions in different queries and save into a temporary table, so that I can analyze the data sets and results. Ultimately, querying the temporary tables for the final join.

Rule of thumb:

query just one record Id, and see the performance.
Always check if index exists.

There may be various issues, and multiple approaches.


How does indexes on a date field work? I have a table with many dates, ten years into the future and ten years history. (combined with customer id and more)

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

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

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

SQL Server non-clustered indexes are sorted in ascending order by default, unless you specify that they must be in descending order, when you define them.

(Also worth noting that if a table is a clustered index, that clustered index will be included in all non-clustered indexes as well.)

When it uses that index, it will usually do a seek into the index at the correct spot, using the index's B-tree structure, otherwise it will scan the entire index if the index doesn't satisfy the query.


Can I improve the way pg_dump exports inherited tables? Given a postgresql 12 database with some table inheritance, and the child table adds no additional columns:

CREATE TABLE parent (
    id integer NOT NULL PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE child () INHERITS (parent);

If I run pg_dump (with pg_dump foo), the tables look like this in the export:

CREATE TABLE public.parent (
    id integer NOT NULL,
    name text NOT NULL
);

CREATE TABLE public.child (
)
INHERITS (public.parent);

If I then detach and reattach the child:

$ psql foo
psql (12.1 (Debian 12.1-2))
Type "help" for help.

foo=# ALTER TABLE child NO INHERIT parent;
ALTER TABLE

foo=# ALTER TABLE child INHERIT parent;
ALTER TABLE

... and try another pg_dump, the export has changed. The child table now explicitly lists the columns that are inherited from the parent:

CREATE TABLE public.parent (
    id integer NOT NULL,
    name text NOT NULL
);

CREATE TABLE public.child (
    id integer,
    name text
)
INHERITS (public.parent);

Is there anything I can do to get pg_dump to output the child table with no explicit columns?

It sounds like a contrived example, but I have a multi-Tb database that with child tables that have been detached and reattached back in the mists of time, and I'd love the schema exports to go back to being as simple (and readable) as possible.

In particular, it'd be great for everyone to see at a glance that the child table adds no additional columns.

You could mess with the pg_attribute catalog table and set attislocal to FALSE, but modifying the system catalogs is unsupported and dangerous.

In my opinion, that is not warranted for a mere esthetic improvement. The dump works just as fine as it is.


Delete duplicate records by column 1 but having differ values in column 2 and column 3 I have a table like:

SSN FName   LName
1   a   b
1   a   b
1   b   b -------------> to delete
2   c   d
2   c   d

as above example I want to delete such records where SSN has different values in FName and LName columns.

Kindly help.

It looks like you want to keep the rows where the combination of SSN, FName and LName are the most common.

You can do that with an updateable CTE that counts the number of occurrences for each combination using count() over() and then enumerates the counts using rank().

declare @T table
(
  SSN int,
  FName char(1),
  LName char(1)
);

insert into @T values
(1, 'a', 'b'),
(1, 'a', 'b'),
(1, 'b', 'b'), -------------> to delete
(2, 'c', 'd'),
(2, 'c', 'd'),
(3, 'a', 'b'),
(3, 'c', 'd');

with C1 as
(
  select T.SSN,
         count(*) over(partition by T.SSN, T.FName, T.LName) as C
  from @T as T
), C2 as
(
  select rank() over(partition by C1.SSN order by C1.C desc) as RN
  from C1
)
delete from C2
where C2.RN > 1;

select *
from @T;

Result:

SSN         FName LName
----------- ----- -----
1           a     b
1           a     b
2           c     d
2           c     d
3           a     b
3           c     d

In the case where there is a tie for most common combination (SSN = 3) the above query will keep all the rows for the tie.


Greenplum : Inserting non-duplicate rows on unique constraint violation I have a stored procedure that does the following :

INSERT INTO schema.my_unique_values
     SELECT DISTINCT id, value
       FROM schema.a_huge_table
      WHERE NOT EXISTS (SELECT 1
                          FROM schema.my_unique_values)

In summary, this query will insert a unique rows into the my_unique_values table from the a_huge_table.

The problem I have is that when I run this query concurrently in a multi-threaded script for a reason, it can insert duplicate rows. I have tried using serializable to avoid this 'phantom reads', but still no luck. My idea is to allow Postgres to only insert non-duplicate rows on unique constraint violation. But is this possible ?
My current experience is that when a unique constraint violation is hit, it will cancel the whole transaction, so it will not insert the non-duplicate rows. How can I achieve my goal ?

Note: I am using Greenplum 4.3.11 that is using Postgres 8.2, therefore there are limitation on the query that I can use.

Thanks..

First of all, I think you need to change slightly your query, because the WHERE NOT EXISTS, as it is now written, would be false as soon as your table has some row. You need to specify a WHERE clause:

INSERT INTO schema.my_unique_values
     SELECT DISTINCT id, value
       FROM schema.a_huge_table a
      WHERE NOT EXISTS
             (SELECT 1
                FROM schema.my_unique_values m
               WHERE m.id = a.id AND m.value = a.value)

INSERT, either within a transaction, or as a single-statement-transaction, will be atomic. So, if one row fails, everything will fail. In any case, you cannot have phantom reads in SERIALIZABLE transaction isolation levels. If you're under that isolation level, make sure your NOT EXISTS (...) really has the properly written WHERE clause.

As of PostgreSQL version 9.5, there is a new clause that can be part of an INSERT and that looks exactly for your use-case. You could modify your querty to make use of ON CONFLICT DO NOTHING:

INSERT INTO schema.my_unique_values
     SELECT DISTINCT id, value
       FROM schema.a_huge_table a
ON CONFLICT DO NOTHING ;

NOTE: The ON CONFLICT clause applies to each row, not to the INSERT as a whole.


Drop all databases based on age How can we drop all databases based on age.

If i go with today's date with 2 months retention the list will come with below query , now i want to drop all the databases with query output.

SELECT name as [database] , database_id, create_date
FROM sys.databases where create_date  <= '20190726' AND name NOT IN ('Master','Model','MSDB','tempdb')

Error

The below script not working with errors being:

  DROP DATABASE VM_P
  Msg 2812, Level 16, State 62, Line 15
  Could not find stored procedure 'DROP DATABASE VM_P.

Script

DECLARE @DBName VARCHAR (64)
DECLARE @SQL VARCHAR (255)
DECLARE DROPDB CURSOR FOR
SELECT name FROM sys.databases
WHERE create_date  <= '20190726'
AND name NOT IN ('master','model','msdb','tempdb','distribution','ReportServer','ReportServerTempDB')

OPEN DROPDB
FETCH next FROM DROPDB INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'DROP DATABASE ' + @DBName
            PRINT @SQL
    EXEC @SQL
    FETCH next FROM DROPDB INTO @DBName
END
CLOSE DROPDB
DEALLOCATE DROPDB

Your code is very, very close to working.

It's not explicitly clear from the docs but the examples do show this.

When using EXEC (or EXECUTE) with a stored procedure, you write the object name straight after the statement (example 1 below), however when calling a T-SQL string you must include brackets (example 2 below)

Example 1

EXEC dbo.Procedure

Example 2

EXEC ('SELECT * FROM dbo.SomeTable')

As such, your solution should look like this (Note the replacement of the static date with GETDATE() to satisfy your original condition of dropping based on the age of the database):

--For internal objects, always use NVARCHAR - this is what the engine uses internally in most places
DECLARE @DBName NVARCHAR (64)
DECLARE @SQL NVARCHAR (255)
DECLARE DROPDB CURSOR FOR
SELECT name FROM sys.databases
--Use GETDATE() with DATEADD for -60 days to get the threshold for deletion
WHERE create_date  <= DATEADD(DD,-60,GETDATE())
AND name NOT IN ('master','model','msdb','tempdb','distribution','ReportServer','ReportServerTempDB')

OPEN DROPDB
FETCH next FROM DROPDB INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'DROP DATABASE ' + QUOTENAME(@DBName)
            PRINT @SQL
    EXEC (@SQL)
    FETCH next FROM DROPDB INTO @DBName
END
CLOSE DROPDB
DEALLOCATE DROPDB

As an aside, given that this is such a potentially damaging script, I'd drop the EXEC and simply copy the printed statements into a new query to run them. This would avoid any unforeseen accidents as it gives you an opportunity to audit the script before you run it.


MySQL profile on query "Creating Sort Index" using 75% of the total time We are trying to figure how to optimize a query (taking around 100ms), and running profile we see Creating Sort Index using 75% of the total time. First, what exactly effects creating the sort index? Is it disk/io?

Second, is there any optimization we can make to the query itself?

SELECT r.`id`,
       r.name,
       r.public_uri,
       rv.version,
       rv.interpreter,
       rv.notes,
       rv.content,
       r.added,
       r.added_by,
       r.modified,
       r.modified_by,
       r.public,
       r.public_by
  FROM recipe_heads rh,
       recipes r,
       recipe_versions rv
 WHERE rh.recipe = r.`id`
   AND rh.recipe_version = rv.`id`
   AND r.`id` = rv.recipe
ORDER BY r.added DESC

Explain:

We had similar problem for huge queries. Often queries ran for hours (upto 7-8) depending upon the load on DB for 400M rows. However, our goal was to achieve group results, such as select col1, col2, col3, count(1), count(distinct col4) from table group by 1,2,3.

Underlying problem is same as yours though, as in both case DB sorts(orders) the results internally.

How creating sort index work.
On mysql website it says "The thread is processing a SELECT that is resolved using an internal temporary table." Per my algorithm understanding, system is most likely splitting data into chunks, read from disk this chunks one by one,  sorting individual chunks, putting back into some temporary disk space and so on. System does this for all chunks and eventually performs merge sort. This involves extensive read/writes.

A possible solution can be to increase your memory for DB (so that it can create bigger chunks that can stay in memory) or if you have a bigger memory somewhere else, you can program the solution by streaming from DB. This can be achieved in nlogn time.

Programmatically, I could reduce time from on an average of 2 hrs to a consistent 7.5 minutes.


Large RAM use after import database - ubuntu server My new VPS has 4GB of RAM. Before importing database system use about 1.2 GB of RAM. When I import into the database, using 250 MB SQL file, 25000 tables use of RAM goes to 97%, htop showing 3302 mb in cache.
Questions

Is this normal ?
If not, how to reduce RAM use?

Yes, this is normal. Linux will use any free memory if needed for cache and buffers - it will be reallocated if needed for anything else (i.e. stating a new program, an existing program needing more memory for something, caching more recently accessed information, or the hypervisor requesting some memory back by inflating the local balloon if you VM is setup that way, and so on).

This is all managed automatically, but if you really want to fully clear it yourself you can by running sync; echo 3 > /proc/sys/vm/drop_caches as root. The only reason I can think of for doing that is if you are running performance tests and want a repeatable "from cold" starting point without completely restarting.

If mysql is using memory mapped files for accessing its data files (which is likely as there can be performance benefits to this method) then any block currently loaded from them will show up as cached data, so as well as the 250Mb input file your ~3G of cache will include things already in memory counted in the "cached" figure before you started, the data and index blocks created as a result of the import, and any related blocks that needed to be read as part of the process.


How can I handle multiple users for an online CRM? First timer here so please bear with me. I'm not a database expert so I'm here to learn.

My client asked me to build an online CRM that is an online payroll and rostering system where they can manage projects and employees. I'm almost done with developing the platform but I need to solve a problem. The database has 115 tables. Now to my understanding, it should be one database per CRM user to avoid conflicts. Am I right?

I'd like to know how I should handle this on the production side. On the development side it's pretty simple one database to test but on the production server they can't all possibly use one database? If there are 1000 companies using the CRM, does it mean 1000 different databases? I need help understanding how to handle this situation.

From a database design perspective, it's possible to design a database where an "unlimited" number of different organizations could share the same database, but would not be able to access any data except their own.

However, that's a step that one would need to take very early on in the process.

NOTE: If the individual companies require access to the database itself for direct querying, then separate databases for each company are the best practical answer. It would be difficult (if not impossible) to allow true ad hoc querying of the underlying data while preventing Company A from viewing any other company's data in the database.

To implement a multi-organization database, you'd start with what we'll call the [Organization] table. This table would uniquely identify every organization using the database. Let's call the primary key on this table Org_ID.

All other database tables (except very basic tables that would hold values common to all organizations) would include an Org_ID column, and all queries would reference the column (in fact, if possible, I would make this column a part of each table's primary key). Users who could log into the application would be tied to one (and only one) organization, and would only be able to access the data for that organization. NOTE: in practice, few if any tables are likely to be common to all organizations. Even tables like US state postal abbreviations might not truly be safe to apply to all organizations, as some might only want the "50 states plus District of Columbia", others might want to include abbreviations for US territories, and still others might want to include Canadian province abbreviations.

Since this would impact almost every table in the database, and every query in the application, it's unlikely to be a practical change now, if you're "almost done with developing the platform."

If the organizations using the application are fairly large, or the server involved is not terribly large, then the solution may simply be to spin up additional servers as you get additional organizations, with (say) 1-10 organizations per server.

Background: I was peripherally involved in work on a time entry system much like you've described, that was implemented as a multi-organization system much like I've described.

Limiting to one organization per database is more secure, as it avoids the possibility of a bug in the application allowing one organization to see another organization's data (especially if any of the organizations in question are competitors; getting access to a competitor's customer data would be a very bad glitch, and would possibly result in lawsuits), and allows each organization to access their database directly for ad hoc querying or reporting.

A final note: I'm providing this from a general database design perspective. I haven't used MySQL in particular enough to know (for example) how many separate databases a server can support, either due to explicit limits on MySQL itself, or to practical limits.


Setting up SQL Server 2008 R2 I need to set up a SQL Server for a development environment for a client. First I installed SQL Server 2008, I was able to connect to it from SQL Server Management Studio fine but when I tried to restore the .bak it said

  the database was backed up on a server running version 10.50.2789.
  That version is incompatible with this server, which is running
  version 10.00.5500.

After googleing I found I needed SQL Server 2008 R2 to match that version number, so I downloaded that from the Microsoft website and ran it. But I keep trying to set up a new server instance so I can restore the .bak but it's still creating another instance in SQL Server 2008 when I install it with version number 10.0.1600.

Can anyone suggest how I can set up a server running version 10.50.2789?

Thanks

Follow the link given.  You will get details that which versions are compatible with the different edition of SQL Server. Hope it will help you.


TLS 1.2 breaks SQL Server linked servers As part of a recent security blitz we set up TLS 1.2 on all of our servers. Last night I flipped the "Force Encryption" flag on all of our SQL Servers and since then I've had a weird issue with linked servers.

I have two servers;

Server A running SQL Server 2016 SP2 CU3
Server B running SQL Server 2012 SP4

Both have the correct encryption certificates on them, both have have the necessary registry edits to disable SSL and TLS 1.0 & 1.1, leaving just TLS 1.2 enabled. Both have the certificate set in SQL Server Config Mgr and "Force Encryption" set.

The linked server from Server A to B (2016 to 2012) works fine.
The linked server from Server B to A (2012 to 2016) shows the server and lists the databases, but any attempt to query the tables over that linked server generates the error:

  TCP Provider: The specified network name is no longer available. (Microsoft SQL Server, Error: 64)

I can however start a SQL Server Management Studio session on Server B and connect to Server A just fine. So its only the linked server that is having the problem.

I checked the extended events on Server A and I can see the trace event for the SSL handshake coming from Server B using TLS 1.2, so the connection request is clearly coming in with the correct encryption set.

Googling the error seems to indicate a name resolution issue, so I tried setting up other linked servers using both the FQDN and the IP address, but neither resolved the issue.

Any help at this point would save what remaining hair I have left.

To eliminate network issues, I went to one of my 2016 servers that has a 2012 instance on it for backward compatibility. The same issue. the 2016 instance can query a linked server to the 2012 instance. The 2012 instance cannot query a linked server to the 2016 instance. A second test between two 2016 instances had the same issue too.

The SQL Server Client Version is 11.4.7462.6.

Registry Settings:

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0]

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client]
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server]
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1]

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client]
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server]
"DisabledByDefault"=dword:00000001
"Enabled"=dword:00000000

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2]

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server]
"DisabledByDefault"=dword:00000000
"Enabled"=dword:00000001

Are you familiar with using any tools such as netmon, wireshark, or netsh?  You might want to do a capture as you replicate this issue.  Something that sticks out in my mind is Server B might be trying to hit something external to the mix (i.e. Certificate Revocation List CRL check) and that call is failing.  A CRL check takes place on the client side of the equation - in this case Server B is the client.  If you are able to run a trace you probably want to filter for tcp resets where the client (Server B) is involved.  In wireshark a filter for this would look like tcp.flags.reset == 1 and ip.addr == 10.X.X.X where 10.X.X.X would be the ip address of Server B.


Uninstalling SQL Server 2012, No option to select instance Hi I am currently trying to completly remove sql server 2012 from my machine, when I click uninstall from the control panel then > remove, I have no option to select and instance to remove and i've no idea why.

When you deinstall SQL Server you have to select the correct program in the "Progams & features" windows. (appwiz.cpl)

  Hint: It should be the program with no size.

Start the program and you should be guided through the de-installation routine.

If you don't see any option to de-install an instance, then you might not be running the correct program version, or you might not be on the server you are trying to de-install the SQL Server instance from.


What is the date mentioned in @@version SQL Server I wanted to know what the date that is result of select @@vesion in SQL server refers to.

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)   May 14 2014 18:34:29   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2  (Build 9200: )

Its the SQL Server build date for the installed version. Refer to @@version on MSDN.


How to create a Hash Computed Column for Many Columns? Does anyone know how to create a Hash Computed Column Persisted? I keep receiving an error below. Otherwise, I will have to utilize an update or trigger statement everytime. I know how to conduct for single column, however this includes multiple columns now.

CREATE TABLE [dbo].[CustomerTransactiont]
(
    CustomerTransactionId int primary key identity(1,1),
    CustomerName varchar(255),
    Price decimal(10,2),
    Quantity int,

    RowHash as hashbytes('SHA2_512', (select CustomerName
    ,Price
    ,Quantity for xml raw)) persisted
)

Msg 1046, Level 15, State 1, Line 7
Subqueries are not allowed in this context. Only scalar expressions are allowed.

Just concat your columns together rather than trying to convert them into xml and then a hash.

CREATE TABLE [dbo].[CustomerTransactiont]
(
    CustomerTransactionId int primary key identity(1,1),
    CustomerName varchar(255),
    Price decimal(10,2),
    Quantity int,

    RowHash as hashbytes('SHA2_512', CONCAT(    UPPER(COALESCE(CustomerName, '')), '|'
                                                , COALESCE(CONVERT(VARCHAR(50), Price), ''), '|'
                                                , COALESCE(CONVERT(VARCHAR(20), Quantity), ''), '|'
                                                )) persisted
)

Note: The coalesce and the pipe characters were just for examples. The Upper is optional if you either want hash to be case sensitive or not.


Does the definition of functional dependency permit derivative values? Sorry if the question in the title seemed vague. The question is rather broad and this is the best I could summarize.

I have read about functional dependency, 1nf, 2nf and 3nf. From what I understand, if A -> B then B is functionally dependent on A. In other words, if there is only a unique A for each B then B is functionally dependent on A. (Please correct me if I'm wrong)

Now consider the following table.

CREATE TABLE sales (
   product_id int,
   product_name varchar,
   year int,
   indirect_sales int,
   direct_sales int,
   total_sales int
);

The candidate key for each row is clearly (product_id, year). This table seems to be 2nf but I suspect this table is still not 3nf. The reason is that we can determine total_sales from direct_sales and indirect_sales because direct_sales + indirect_sales = total_sales. Could we call this a functional dependency then? Because for each direct and indirect sales there's a unique total sales. You can also see that this could potentially cause updating errors. If you update direct_sales you must also update the total_sales simultaneously.

So how would you deal with this in the context of normalization? Normally you would create a separate table with attributes but creating a table with direct, indirect and total sales seems artificial. Also how would you deal with this in a real situation in which 3nf was strictly required.

I have provided a simplified example so you can grasp the essence of the problem but the real problem that I'm battling is with these tables.

CREATE TABLE user (
   user_id int,
   user_name varchar,
   user_login varchar,
   user_password varchar,
   balance int
);

CREATE TABLE transaction (
   transaction_id int,
   user_id int REFERENCES user.user_id,
   amount int
);

This is a banking database that stores user information and transaction information for each user. I suspect this won't be considered 3nf because balance is always the sum of user transaction amounts. This will cause updation anomaly; if we update a transaction the balance must reflect it.

Can you help me make sense of this? Thanks.

I found an excellent blog post by Ken Downs that clarifies everything:

Database Skills: Third Normal Form and Calculated Values

Basically, the tables above aren't 3NF on account of the derived attributes; 3NF doesn't permit derived attributes. And, yes, it follows from it that derived attributes are functionally dependent on their source attributes.


How to find similar records using multidimensional array? I have table that describes a movie release. This table consists of columns:

movie_id int
release_day int
showtime_count int
ticket_sold_count int

e.g. (1, 1, 100, 200) tuple describes movie ID 1, on the first day of the movie release when it had 100 showtimes and sold 200 tickets.

I have a movie that is 5 days into the release (e.g. (2,1,100,200), (2,2,150,300), (2,3,100,250), (2,4,150,300), (2,5,100,250)) and I want to find previous releases that have performed similarly up to this day into their release.

I know that I can calculate a distance between two points using <->. But how would I index and search the entire database for a movie where distance for days 1, 2, 3, 4 and 5 is the lowest?

Just focusing on the database and ignoring the statistics issues, and assuming the 5 days you use is a design decision and not just ephemeral example, and that we can ignore anything with an incomplete record, then you can do it like this:

Set up with random data:

create table movie as select x as movie_id, y as release_day, floor(random()*1000)::int as showtime_count, floor(random()*100000)::int as ticket_sold_count
from generate_series(1,1000000) f(x), generate_series(1,6) g(y);

denormalize and index:

create materialized view release as
    select movie_id,
           cube(
              array_agg(showtime_count order by release_day)||
              array_agg(ticket_sold_count order by release_day)
            ) as datavector
    from movie where
        release_day <=5
    group by movie_id
    having count(release_day)=5 and
           count(distinct release_day)=5;

create index on release using gist (datavector);

And use:

explain (analyze,buffers) select * from release order by datavector <-> '(230, 16, 178, 507, 366, 8798, 4724, 1736, 5765, 808)' limit 1;
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..0.59 rows=1 width=44) (actual time=1.800..1.802 rows=1 loops=1)
   Buffers: shared hit=95
   ->  Index Scan using release_datavector_idx on release  (cost=0.41..176884.39 rows=999999 width=44) (actual time=1.798..1.798 rows=1 loops=1)
         Order By: (datavector <-> '(230, 16, 178, 507, 366, 8798, 4724, 1736, 5765, 808)'::cube)
         Buffers: shared hit=95
 Planning Time: 0.091 ms
 Execution Time: 1.841 ms


Set boolean field to true if query match otherwise set to false I have bunch of ids someIds (20 - 100 thousands) and table with more than 12 millions rows like that:

spaceShips(
    id BIGINT,
    shipType TEXT,
    shipName TEXT,
    hasArtificialIntelligence boolean
)

And I need for all rows where shipType='warship' (about 2 millions) set field hasArtificialIntelligence true if spaceShips.id in someIds otherwise set false. Is there a better way than two update queries?

Yes, one update is sufficient:

UPDATE spaceShips
    SET hasArtificialIntelligence = spaceShips.id in ( ...,someIds,... )
WHERE shipType='warship'

spaceShips.id in ( ...,someIds,... ) will evaluate to either true or false (assuming someid is not null)


The problem might be a result of how you are defining your users.  I don't think the wildcards are working as you're expecting them to. The best way to figure this out, in my opinion, is to create a new user and password for the 18.61.10.64 host on the yoda.er.com MySQL Server.

Keep in mind that when logging in locally, the MySQL client on your server might be using the unix socket to authenticate.

mysql> show variables like '%socket%';
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| socket        | /var/run/mysqld/mysqld.sock |
+---------------+-----------------------------+

Connecting to MySQL

If this is the case then a password isn't necessary when logging in locally onto the MySQL server.

Could you try this from the machine you are trying to connect to. Use a new user and password combination because we are not sure how many different entries you might have for your power user.

GRANT ALL PRIVILEGES ON *.* TO 'someuser'@'18.61.10.64' IDENTIFIED BY PASSWORD 'somepassword';

FLUSH PRIVILEGES;

Then from the shell on 18.61.10.64

mysql -u someuser -h ip.address.for.r2d2.er.com -psomepassword


oracle failsafe - how to start instance in nomount Using Oracle Failsafe, how can a instance be started up in nomount mode?

If the instance is shutdown via sqlplus, the windows cluster manager just starts it up again. If the instance is shutdown via windows cluster manager, I'm unable to connect to it via sqlplus.

Shutdown instance in Cluster Manager (Right click, "Take Offline" or however you choose to do it)
Bring up Windows Services (Start->Run->Services.MSC) on the clustered server
Start the Oracle service for the instance you want
Now you can do whatever you like in SQL*Plus (Cluster Manager will still show it as down no matter what you do at this point)

So, if step 3 brings the Oracle instance up to "Open" you can do a shutdown immediate etc and then open it nomount.

HTH


Access denied when trying to run an agent job Can someone please point me in the right direction. I keep getting the below error message when I try to run a job that executes a package I created and saved on the SQL Server.

12/03/2012 17:32:15,New_App_R,Error,1,SQLAAG01,New_App_R,New_App_R,,
Executed as user: NT Service\SQLSERVERAGENT.
Started:  5:32:15 PM
Could not load package "\MSDB\New_App_R" because of error 0xC00160AE.
Description: Connecting to the Integration Services service on the computer "SQLAAG01"
failed with the following error: "Access is denied." By default only administrators
have access to the Integration Services service. On Windows Vista and later the process
must be running with administrative privileges in order to connect to the Integration
Services service.

I also tried creating the same package and saved it on the file system (desktop) and when I try to execute the package using the below code in a job or in a query window

EXEC @returncode = xp_cmdshell 'dtexec /f "C:\Users\jvelez\Desktop\New_App.dtsx"'

I get another access denied error that says:

Description: Failed to open package file "C:\Users\jvelez\Desktop\New_App.dtsx"
due to error 0x80070005 "Access is denied.".  This occurs when loading a package
and the file cannot be opened or loaded correctly into the XML document. This can
be the result of specifying an incorrect file name when calling LoadPackage or the
specified XML file has an incorrect format.

If I double click the package on my desktop it runs and works perfectly, but what I need to do is schedule the package to run. I tried creating jobs in different ways and adding myself to the administrators group but nothing seems to work. Any advice would be greatly appreciated.

I'm running SQL Server 2012 on Windows Server 2008

The 'Service\SQLSERVERAGENT' service account needs access to the file system to run that package.

You can also setup a proxy for the SSIS package and run the job using that proxy account.


Checking records consistency after mongorestore --noobjcheck I had an HDD malfunction on a Mongo node.  After running fsck and mongod --repair I was able to dump the needed collection with mongodump and move it to another server.

However, some records appear to be malformed and mongorestore fails with:

  assertion: 10331 EOO Before end of object

I restored the collection with mongorestore --noobjcheck and now I want to remove the malformed records from the collection.

How do I do that?  Is there a way to force Mongo to check itself or must I do it manually?

You can run a --repairDatabase for that node:

mongod --repair --repairpath <repair_path>

Keep in mind that the repair path should have at least as your data size free disk space. (if you have 100GB, the repair path should have at least 100GB free also.
That should take care of problems of that sort.

Also, in the future if anything of some sort happen again and you need to run mongodump, you can run the mongodump --repair.
This will ensure that any corrupted data will not be dumped.

Hope this helps


Planning Azure SQL Server I've been tasked to prepare Azure SQL database deployment plan and I am struggling with it...

I've been given following information:

2 million transactions will arrive to database daily throughout the day (~278 tr/sec on average).
Transactions will arrive from 1000 different locations (onprem SQL Servers generating 2000 transactions daily each)
All transactions will have a fixed size of 12KB. Therefore I am expecting to get about about 22-23 GB of data daily.
All data will be regularly aggregated and accessed from 100 different locations for BI purposes.
Updated data (configurations) will be sent back to aforementioned 1000 locations

I've tried to use Azure SQL DTU calculator, but it requires to run PowerShell script on existing server and I don't have one... This a new system, that is currently in development and it is planned to run on Azure SQL.

I also tried to find the way to convert transactions to DTU or Vcore, but failed to find any.

Can anyone please help me with that? Or point me in to right direction to choose correct plan to suffice my needs based on info provided.

Thank you in advance.
Mike

Easy!
Use the Azure SQL Database DTU Calculator:

Download the PowerShell script on your server
Run powershell -ExecutionPolicy Bypass -File .\sql-perfmon.ps1 and let it run for a few minutes
On the web page enter the number of cores that your server is using
Stop the PowerShell script and upload the .CSV file that is now on the C:\ root


How to remove diacritics in computed persisted columns? COLLATE is non-deterministic and cannot be used I have a function that includes:

SELECT @pString = CAST(@pString AS VARCHAR(255)) COLLATE SQL_Latin1_General_Cp1251_CS_AS

This is useful, for example, to remove accents in French; for example:

UPPER(CAST('Éléctricité' AS VARCHAR(255)) COLLATE SQL_Latin1_General_Cp1251_CS_AS)

gives ELECTRICITE.

But using COLLATE makes the function non-deterministic and therefore I cannot use it as a computed persisted value in a column.

Q1. Is there another (quick and easy) way to remove accents like this, with a deterministic function?

Q2. (Bonus Question) The reason I do this computed persisted column is 1)to search 2)to detect duplicates. For example the user may enter the customer's last name as either 'Gagne' or 'Gagné' or 'GAGNE' or 'GAGNÉ' and the app will find it using the persisted computed column (and the update/insert will balk at the duplicate). Is there a better way to do this?

EDIT: Using SQL Server 2012 and SQL-Azure.

Why not just create the table column with a case-insensitive, accent-insensitive collation? This prevents duplicates according to the collation rules, and allows the sort of searches you seem to require:

CREATE TABLE Test
(
    col1 varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AI PRIMARY KEY
);

-- Success
INSERT dbo.Test VALUES ('Gagné');

-- Failed, duplicate key
INSERT dbo.Test VALUES ('Gagne');

-- Success
SELECT *
FROM dbo.Test
WHERE col1 = 'gaGNE';

As an important side note, I should mention that T-SQL scalar and multi-statement functions have very poor performance characteristics. Using them in a computed column definition is even worse. Avoid.


Combining 2 temp tables to aggregate into a single table I'm working on a SP that takes a lot of data, separates it into two temp tables and unions those two together.  It works, but right now it's giving me a different line for each of temp tables, but I need it to grab it into one line.

USE CallDB
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[LDS_ALL_CALLS_120s_PLUS]
AS
BEGIN
end end

--Pulls all unique calls from CallDB into temp table #tmpTotCalls

IF OBJECT_ID('tempdb..#tmpTotCalls') IS NOT NULL DROP TABLE #tmpTotCalls

SELECT DISTINCT [user],count(*) as [TotCall]
  into #tmpTotCalls
  FROM CallDB
  WHERE status <> 'ALTNUM' --and length_in_sec >= '120'
  GROUP by [user]

--Pulls all unique calls OVER 120 seconds from
CallDB into temp table #tmp120s

IF OBJECT_ID('tempdb..#tmp120s') IS NOT NULL DROP TABLE #tmp120s

SELECT DISTINCT [user], count(*) as [120calls]
  into #tmp120s
  FROM CallDB
  WHERE status <> 'ALTNUM' and length_in_sec >= '120'
  GROUP by [user]

delete CallDB.[dbo].[120s_calls]
insert into CallDB.[dbo].[120s_calls]

Select [user], TotCall, '0' as [120calls] from #tmpTotCalls

UNION ALL

select [USER], '0' as [Totcall], [120calls] from #tmp120s

Order by [user]

DROP TABLE #tmp120s
DROP TABLE #tmpTotCalls

select * from CallDB.[dbo].[120s_calls]

END

What I get, as a solution is:

User | totCalls | 120Calls
fernandezv | 0 | 23
fernandezv | 2147 | 0

Seems to me like you need a JOIN instead of a UNION:

SELECT  ISNULL(T1.[user],T2.[user]) [user],
        ISNULL(T1.TotCall,0) TotCall,
        ISNULL(T2.[120Calls],0) [120Calls]
FROM #tmpTotCalls T1
FULL JOIN #tmp120s T2
      ON T1.[user] = T2.[user]
;


SQL Server Express in a shared hosting environment I have created an ASP.NET project in Visual Studio. The database is a SQL Server Express database TestDB.mdf. I uploaded this with .ldf file to the standard App_Data directory but the connection string doesn't seem to work. How can I use this SQL Server Express database in a shared hosting environment?

My Connection String is:

Data Source=.\\SQLEXPRESS;
Initial Catalog=C:\\inetpub\\dir1\\dir2\\docs\\App_Data\\Test.mdf;
Integrated Security=SSPI;
Connect Timeout=120";

It gives always the same error:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Most hosting companies are going to want you to connect to a database server. Having the database on the web server is more for development ease, not for production.


MongoDB only three of four shards being used Using MongoDB 2.6, I've setup four replication sets: a, b, c & d:

>rs.conf()

a:PRIMARY> rs.conf()
{
    "_id" : "name_of_replset",
    "version" : 1,
    "members" : [
        {
            "_id" : 0,
            "host" : "fedora_cje:port_number"
        },
        {
            "_id" : 1,
            "host" : "fedora_cje:port_number"
        },
        {
            "_id" : 2,
            "host" : "fedora_cje:port_number"
        }
    ]
}

Those replication sets have been added to shards

>sh.status()

 shards:
    {  "_id" : "a",  "host" : "a/fedora_cje:27000,fedora_cje:27001,fedora_cje:27002" }
    {  "_id" : "b",  "host" : "b/fedora_cje:27100,fedora_cje:27101,fedora_cje:27102" }
    {  "_id" : "c",  "host" : "c/fedora_cje:27200,fedora_cje:27201,fedora_cje:27202" }
    {  "_id" : "d",  "host" : "d/fedora_cje:27300,fedora_cje:27301,fedora_cje:27302" }

At the bottom of sh.status(), I am seeing this message

{  "_id" : "test",  "partitioned" : false,  "primary" : "d" }
{  "_id" : "test_collection",  "partitioned" : false,  "primary" : "d" }

but test and test_collection don't exist in the test_cluster database.

Inserting records, the fourth replication set and fourth shard are apparently ignored.

test_cluster.test_cluster_collection.stats()

"nchunks" : 3,
"shards" : {
    "a" : {...
    "b" : {...
    "c" : {

Notice also that the number of chunks remains at 3.

I am using the default /etc/mongod.conf file.

This is the insert statement being used:

>use test_cluster
>t=db.test_cluster.test_sharded_collection
>for( var i=0; i < 20000; i++ ){t.insert( { x:i,y:3,z:"test                       test" } )}

Question: Why is the fourth replication set and consequently the fourth shard being ignored during inserts?

There are a few confusing pieces here, first you list this piece of sh.status():

{  "_id" : "test",  "partitioned" : false,  "primary" : "d" }
{  "_id" : "test_collection",  "partitioned" : false,  "primary" : "d" }

That means that there is a test database, a test_collection database, not collections.  It also indicates that neither of them are sharded - that is what "partitioned" : false means.

You then show some output from test_cluster.test_cluster_collection.stats() but that is not one of the databases from the sh.status command.  It is a collection from the test_cluster database, and there is no indication of how that is sharded, though it would be listed in sh.status().  Basically the two pieces of information have nothing to do with each other.

Your for loop will also be inserting into the test_sharded_collection as a sub-collection of test_cluster.  In other words, the full namespace you are inserting into is test_cluster.test_cluster.test_sharded_collection, and I suspect that is not sharded which is why you are likely not seeing the chunks increase.  What I suspect you intended to do was insert into test_cluster.test_sharded_collection.  Try this for your inserts instead:

use test_cluster;
for( var i=0; i < 20000; i++ ){db.test_sharded_collection.insert( { x:i,y:3,z:"test                       test" } )};

The full output of sh.status(), and specifically the section about the test_sharded_collection would help me confirm my suspicions.


GZipStream/DeflateStream compressions alternatives I have created a simple CLR functions for compressing/decompressing NVARCHAR columns:

[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
public static SqlBinary Compress( string str ){
    if( str == null ){return new SqlBinary();}

    if( String.IsNullOrEmpty( str ) ){str = " ";}

    byte[] bytes = Encoding.Unicode.GetBytes( str );
    using( MemoryStream msi = new MemoryStream( bytes ) ){
        using( MemoryStream mso = new MemoryStream() ){
            using( GZipStream gs = new GZipStream( mso, CompressionMode.Compress ) ){
                msi.CopyTo( gs );
            }
            return new SqlBinary( mso.ToArray() );
        }
    }
}

The compression ratio I get is about 4 or if I have 1024 KB of not compressed data I will get 256 KB of compressed data. I am aware that the ratio depends on the data itself and its size, but I want to get a better ratio.

As I am using SQL Server 2012 and .net 4.0, is there a change that the compressions are not giving the expected ratio because of issues like this?

And is there an alternative class I can use in the SQL CLR function? There are such alternatives but are not supported currently.

Here are some thoughts on this:

Do you know that you should get better compression on the strings that you have tested with? Have you tested those same strings by gzipping outside of .NET? Such as on Linux / CygWin -- DOS port of UNIX utilities / PHP / etc?
If you have updated your system with .NET 4.5, then you are using the updated GZipStream. This is due to it being in System.dll which is a supported library. You can test this out by using the new constructor that allows for a CompressionLevel. Just change CompressionMode.Compress to be CompressionLevel.Optimal. SQL Server is bound to a specific version of the CLR, not to a specific version of the .NET Framework. Meaning, any new functionality in any of the supported libraries is usable, so long as any server you deploy your code to has had its .NET updated.

This does not mean you will get better compression. I tested this code and it came up with the same 31 bytes for "Hello World" as PHP and Fiddler generated, as noted in one of the questions you linked to: https://stackoverflow.com/questions/11435200/why-does-my-c-sharp-gzip-produce-a-larger-file-than-fiddler-or-php .

I just tested again with a string of 3405 random characters (i.e. "fsdkjf skdj f..."). I declared the variable as NVARCHAR(4000) and ran it through your code, after making the changes that I suggest here. The length of the compressed binary was 211 bytes. I then copied and pasted that same string into Notepad++, made sure that the encoding was set to "UCS-2 Little Endian" and saved. I checked the file in Windows Explorer and it was 6812 bytes (6810 in data, as also reported by DATALENGTH of the variable, plus 2 for the byte order mark). I ftped that to a Linux server in binary mode. File size was still 6812 on the Linux server. I then ran gzip -9 on it (i.e. max compression; default is -6). Compressed size? 231 bytes. So the .NET GZipStream actually did slightly better.
CompressionMode.Compress and CompressionLevel.Optimal are functionally equivalent. Each one is the assumed default when specifying the other.
Don't use string for the input param; use SqlString.
Get rid of the byte[] bytes line
Change new MemoryStream( bytes ) in the first using block to be:
new MemoryStream(str.GetUnicodeBytes())
You can get rid of the if( str == null ){return new SqlBinary();} line. Instead of handling this in the .NET code, just add WITH RETURNS NULL ON NULL INPUT to the CREATE FUNCTION. This way SQL Server won't even invoke your code if the input is NULL :). Just keep in mind for when you have multiple input parameters, this option will return NULL if any of them are NULL. If naturally at least one of them should be able to pass in a NULL, then you do have to handle this situation in your code.
Replace this line if( String.IsNullOrEmpty( str ) ){str = " ";} -- which actually returns a compressed space which is not an empty string -- with:

if (str.Value.Length == 0)
{
    return SqlBinary.Null;
}

I haven't tried "zlib" yet, but unfortunately both "SharpZipLib" and "DotNotZip" are buggy and have not been updated in several years and no indication that they will be updated. However, the bugs in "DotNetZip" seem to be mostly around zip file archives and not the GZip functionality (which has been working quite well in SQL# :-) ).


How to properly monitor the number of PostgreSQL database connections? I tried to use a Nagios script for monitoring the number of database connections on a Postgres database and I reached this problem: these are counted as currently open-connections and measured every 5 minutes.

SELECT sum(numbackends) FROM pg_stat_database;

Still, this seems to miss a huge number of short-lived connections, so the statistics are far from the reality.

I tried to run the script manually and I observed big changes even between two connections made few seconds away one from another.

How could I get this information in a reliable way? like max(connectios) happened during a time interval.

Its better to use Workload monitoring tools like pgbadger for checking database connections and overall load. It will help you understand which users are connecting for how much time and what queries are being fired by them. For information on installing and configuring pgbadger, refer this page.

If you just want to check the number of active connections, you can use
 select count(*) from pg_stat_activity where state='active'


SQL Server Agent - Do not show job step details and column headers in output file I have a SQL Server Agent job which runs a select statement on a schedule and appends results to a CSV file

(Right click the job --> properties --> advanced and then adding a file path to the output file location)

This is great except the output it generates displays

Job 'MyJobName' : Step1, 'Step Name' : Began executing YYYY-MM-DD HH-MM-SS

as well as the column headers throughout the file (it adds this with each append)

Is there a way to not output the Job name / step details and column headers?

You can do this with SQLCMD

Query

remember to add nocount so it does not add the rows affected to the file

sqlcmd -Q"set nocount on; select * from sys.databases;"  -S localhost -D master -o  "c:\temp\test3.csv"

Your job step needs to be SQLCMD however

The file

Part of the output


How do I select items from a table where a single column must contain two (or more) values? I have a MySQL database table that references different words and their locations in documents. I want to return the IDs of documents that contain all of the words.

Here is an example table.

docid     wordid
1         4
2         4
1         2
1         5

Ok, now say that someone queried the database for the words that had WORDIDs 4, 2, and 5.

My erroneous SQL SELECT statement would be something like:

Select docid from table where wordid = 4 and wordid = 2 and wordid = 5

This is giving me 0 results.

I have seen in other places where the where in clause has been suggested:

If I understand correctly, this is another way to write an OR clause. I have tried this:

select docid from table where wordid in (4,2,5)

But, this is giving me all the results. It should exclude docid 2 as that does not contain the other words. I'm expecting to just get docid 1.

However, I could be using the where in clause incorrectly as I have very little db experience.

How can I return docids that contain all of the words?

Please note as well, my where clause will be dynamically generated in a FOR loop.  The query could be as simple as one or two words, or it could be 10 or 12 words. I'm looking for a query structure that takes speed into consideration. Please let me know if you need anymore information.

For reference, I am trying to convert this code into PHP / MYSQL, but I don't understand the sql statement here or its equivalent in MYSQL:

http://my.safaribooksonline.com/book/web-development/9780596529321/4dot-searching-and-ranking/querying

This is the relational division problem and there is a question about it at SO, with a lot of ways to write this query, plus performance analysis for PostgreSQL: How to filter SQL results in a has-many-through relation

Shamelessly copying code form there and removing/changing code for answers that have features lacking from MySQL, like CTEs, EXCEPT, INTERSECT, etc, here are a few ways to do this.

Assumptions:

the table is called factors
there is a UNIQUE constraint on (wordid, docid)
there is a documents and a words table:

Easy to write, medium efficiency:

-- Query 1 -- by Martin
SELECT d.docid, d.docname
FROM   document d
JOIN   factors f USING (docid)
WHERE  f.wordid IN (2, 4, 5)
GROUP  BY d.docid
HAVING COUNT(*) = 3 ;           -- number of words

Easy to write, medium efficiency:

-- Query 2 -- by Erwin
SELECT d.docid, d.docname
FROM   documents d
JOIN   (
   SELECT docid
   FROM   factors
   WHERE  wordid IN (2, 4, 5)
   GROUP  BY docid
   HAVING COUNT(*) = 3
   ) f USING (docid) ;

More complex to write, very good efficiency in Postgres - probably lousy in MySQL:

-- Query 4 -- by Derek
SELECT d.docid, d.docname
FROM   documents d
WHERE  d.docid IN (SELECT docid FROM factors WHERE wordid = 2)
AND    d.docid IN (SELECT docid FROM factors WHERE wordid = 4);
AND    d.docid IN (SELECT docid FROM factors WHERE wordid = 5);

More complex to write, very good efficiency in Postgres - and probably the same in MySQL:

-- Query 5 -- by Erwin
SELECT d.docid, d.docname
FROM   documents d
WHERE  EXISTS (SELECT * FROM factors
               WHERE  docid = d.docid AND wordid = 2)
AND    EXISTS (SELECT * FROM factors
               WHERE  docid = d.docid AND wordid = 4)
AND    EXISTS (SELECT * FROM factors
               WHERE  docid = d.docid AND wordid = 5) ;

More complex to write, very good efficiency in Postgres - and probably the same in MySQL:

-- Query 6 -- by Sean
SELECT d.docid, d.docname
FROM   documents d
JOIN   factors x ON d.docid = x.docid
JOIN   factors y ON d.docid = y.docid
JOIN   factors z ON d.docid = z.docid
WHERE  x.wordid = 2
AND    y.wordid = 4
AND    z.wordid = 5 ;

Easy to write and extend to an arbitrary set of words but not as efficient as the JOIN and EXISTS solutions:

-- Query 7 -- by ypercube
SELECT d.docid, d.docname
FROM   documents d
WHERE  NOT EXISTS (
   SELECT *
   FROM   words AS w
   WHERE  w.wordid IN (2, 4, 5)
   AND    NOT EXISTS (
      SELECT *
      FROM   factors AS f
      WHERE  f.docid = d.docid
      AND    f.wordid = w.wordid
      )
   );

Easy to write, not good efficiency:

-- Query 8 -- by ypercube
SELECT d.docid, d.docname
FROM   documents d
WHERE  NOT EXISTS (
   SELECT *
   FROM  (
      SELECT 2 AS wordid UNION  ALL
      SELECT 4 UNION ALL
      SELECT 5
      ) AS w
   WHERE NOT EXISTS (
      SELECT *
      FROM   factors AS f
      WHERE  f.docid = d.docid
      AND    f.wordid = w.wordid
      )
   );

Enjoy testing them :)


Scalability of concurrent log restore for different databases on SQL Server Are there any locks or resource blocking/contention (beyond IO capacity) between two sessions, which restore logs for two different databases with norecovery? Same about restore logs with recovery?

a. My answer by now is “not much”, asking for validation.
Are there any locks or resource blocking/contention (beyond IO capacity) between two sessions, which restore two different databases with recovery from norecovery state?

a. My answer by now is “not much”, asking for validation.
What portions of concurrent log restore process (read backup/write to data/write to log, other) contribute into each of the major identified wait types BACKUPIO, IO_COMPLETION, LCK_M_S, BACKUPTHREAD, PREEMPTIVE_OS_FLUSHFILEBUFFERS, PAGEIOLATCH_EX

a. Most puzzling is LCK_M_S

I thought the question was very clear, but clarifying. The scenario is planning of log shipping of 2000-6000 DBs per server. There are two concurrency scenario - first is regular scheduled application of logs and second is DR scenario when all DBs need to be brought online using RESTORE DATABASE WITH RECOVERY.
Third question is a capture of actual top waits during test run of concurrent log restore. I do know what each wait mean, I don't know per SQL internals what portion of restore process is reflects and as such how can be optimized.

For 1 & 2 - You need to take into account VLFs as well. Too many VLFs will slow down the restore of Transaction log.

Also, all the restores are logged in msdb--> restorefile, restorefilegroup and
restorehistory tables, so many concurrent sessions will have to write to the same table and if those tables are not maintained (purged) properly, there will be contention/blocking.

As a side note, I have seen blocking especially when you logship 50+ databases with the same restore frequency.

For 3, I will just refer to the most authoritative wait stats repository.


Which file in "MySQL-5.0.27-win32" folder do i need to backup? Ok, I am using a pretty old MYSQL "mysql-5.0.27-win32" and the file and the structure maybe different from the latest Mysql (mysql 7).

Here is the current structure of of mysql-5.0.27-win32:

In "mysql-5.0.27-win32" folder:
bin
data
Docs
Embedded
examples
include
lib
mysql-test
scripts
share
sql-bench
my-huge.ini
my-innodb-heavy-4G.ini
my-large.ini
my-medium.ini
my-small.ini

In "data" folder:
mysql
mydatabase

...some other databases that I don't touch....

my-PC.err
my-PC.pid
ib_logfile0 ---> this is a file, not a folder
ib_logfile1 ---> this is a file, not a folder
ibdata1 ---> this is a file, not a folder

the "mydatabase" folder has file such as .frm, .MYD, .MYI for each of its table,
"mydatabase" also has "db.opt"

Ok, here is the scenarios. I finished developing my WebApp using mysql-5.0.27-win32in my Local Desktop PC. Now it is a time to deploy my app.

To deploy, I copy the whole mysql-5.0.27-win32 folder (which contains the current DB structure + some basic data) into Virtual Server.

So, there will be 2 same folders the original mysql-5.0.27-win32 in the Local PC & the copy  mysql-5.0.27-win32 in the Virtual Server. At the beginning, these 2 folders are exactly the same. They have the same DB structures (ie same number of tables & columns in the tables) & same data stored in the tables.

From now on, my website will store user data directly to the copy mysql-5.0.27-win32 folder in Virtual Server (VPS). But if the VPS got shutdown then I will lose all data.

Thus I want to back up daily the "mydatabase" from the copy mysql-5.0.27-win32.

So which file in the "MySQL-5.0.27-win32" folder do i need to backup so that when I put them into the original mysql-5.0.27-win32, then I can have exactly the same info as I have in the copy mysql-5.0.27-win32?

http://dev.mysql.com/doc/refman/5.1/en/windows-installation-layout.html

It would be the data folder that you'd want to back up for the data, but you might want to back up other folders depending on your setup.


SQL Server has encountered 1 occurrence(s) of cachestore flush On one of our production server log files, the following message is being observed on daily basis. What does it mean? Is it a serious issue?

  SQL Server has encountered 1 occurrence(s) of cachestore flush for the
  'SQL Plans' cachestore (part of plan cache) due to some database
  maintenance or reconfigure operations.

I also saw this in my own environment along with a few other messages:

  SQL Server has encountered 1 occurrence(s) of cachestore flush for
  the 'Bound Trees' cachestore (part of plan cache) due to some
  database maintenance or reconfigure operations.
  SQL Server has encountered 1 occurrence(s) of cachestore flush for
  the 'SQL Plans' cachestore (part of plan cache) due to some database
  maintenance or reconfigure operations.
  SQL Server has encountered 1 occurrence(s) of cachestore flush for
  the 'Object Plans' cachestore (part of plan cache) due to some
  database maintenance or reconfigure operations.

It turns out there were multiple databases with the AutoClose setting turned on.

You can check your own database with this command:

SELECT DATABASEPROPERTYEX('DatabaseName','IsAutoClose')

Once we turned the setting off, the messages went away and performance improved. For further reading as to why auto close is a bad idea:

Worst Practice: Allowing AutoClose on SQL Server
SQL Server Best Practices: AutoClose should be off


Is PostgreSQL appropriate for processing this large but simple dataset? I have a dataset I'm not sure how to store. The structure is simple: 30 numeric attributes identified by about 15 billion x, y, and t values. We're expecting ~17k t values and maybe 90k x/y combinations, though it could be some combination that gives us 20 million records in the end.

The processing involves retrieving 1-10 columns for each x and y pair and storing various calculated numeric values. Are we nearing/passing the limit of fast response times for Postgres with this many rows?

The processing is all done in-house by one person and shouldn't need to happen more than a couple dozen times as we settle on what summaries we want. So we're not worried about a high number of writes or connections, strict security, making updates to existing records, table relations, losing data because of network connection issues. Basically, we're not concerned about the kinds of things I understand the ACID part of a RDBMS like Postgres brings to the table. But we also don't need to replicate or distribute the data, high availability, change the schema on the fly, or manage an unusual number of writes (say a dozen for each of the 90k x/y pairs)- the kinds of things I understand NoSQL DBs offer.

So I guess the real issue is read-speed out of Postgres for a table of this size and the real question is whether there's a better storage mechanism for what we need to accomplish. Does the answer change if we have 40 billion records? 60?

Seems like your x,y is a fixed pair. So you can combine them to form a single key. If all your access is based on a key, then the NoSQL dbs can fit your bill. Some NoSQL dbs offer range queries too but that is not their biggest strength.

The limitation with the basic/standard RDBMS systems are that they are monolithic. You need a powerful machine to store your 15/40/60 billion objects on a single machine. You will also end up paying cost in terms of performance for the full ACID capabilities of the regular RDBMS systems even though you know that you dont need them.

NoSQL dbs which are inherently designed keeping distributed systems in mind can help greatly here. When you want to grow from 15->40->60 billion objects, probably you just have throw more machines into the cluster. When the data gets sharded, the amount of data stored on each machine is less (making the index sizes etc smaller). This can give you better read performance.


Does disconnecting the network stop a query? I recently executed an update query against 100,000 records. I realized I'd made a mistake while the query was running and quickly unplugged the network cable.

Does the update query

stop processing and completely rollback?
continue processing to completion and commit?
stop processing and leave only part of the target rows updated?

As mentioned by Nick and Martin, the eventual status of your query depends on whether SQL Server knows about your network cable pull before the query completed. From Books Online (though I find it interesting that there are equivalent topics for this in 2000, 2005, 2008, and 2008 R2, but not 2012 or 2014):

  If an error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to an instance of the Database Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and the instance of the Database Engine rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.

(As an aside, that word connections in the 2nd last sentence was probably meant to be transactions. I don't know how one rolls back a connection.)

In a similar way, SQL Server may undo or redo transactions during recovery after the server is shut down unexpectedly, and this will depend on the state of the transaction at the moment of shutdown. I have seen people use this tactic to achieve what you were trying to do (cancel the transaction(s)) and when the server started up again much of the work was simply redone (so net effect of their knee-jerk reaction was much closer to zero than they expected).

So rather than be subject to this, instead of doing drastic things in a panic, like yanking a network cable or shutting off the machine, I suggest in the future you have better discipline about running ad hoc queries against important systems. For example, instead of:

UPDATE dbo.sometable
-- where *oops* I forgot this part

Have this:

BEGIN TRANSACTION;

UPDATE dbo.sometable
-- where *oops* I forgot this part

-- COMMIT TRANSACTION;
-- ROLLBACK TRANSACTION;

Then, if the update was indeed correct, you can highlight the COMMIT part and run it. If it wasn't, you can calmly highlight the ROLLBACK part and run that. You can even use add-ins like SSMS Tools Pack to edit your New Query template to include that boilerplate.

Now it could still get you in trouble in the event that you run the query and then don't either commit or rollback, because now your transaction is blocking other users. But this is better then irrevocably modifying data.

And of course, as always, have a backup you can rely on.