Does XP_API deprecation announcement mean that sp_executesql will not be supported in the future? I have setup an Extended Events session in SQL 2008 R2 to track the deprecation_final_support and deprecation_announcement events.  I noticed some "deprecation_announcement" events in my log with "feature" of "XP_API", which appear to all be SQL statements that use SP_EXECUTESQL.

The event message says:

  Extended stored procedure API will be removed in a future version of
  SQL Server. Avoid using this feature in new development work, and plan
  to modify applications that currently use it.

One example of the sql_text action from an event is:

exec sp_executesql N'DECLARE @c varbinary(128);SET @c=cast(@userName as varbinary(128));SET CONTEXT_INFO @c;', N'@userName varchar(128)', @userName = 'MYDOMAIN\myuser';

The master.sys.SP_EXECUTESQL proc is listed under "Databases > System Databases > master > Programmability > Extended Stored Procedures > System Extended Stored Procedures" in Object Explorer.

Does this mean that sp_executesql will not be supported in a future version of SQL Server?

The Deprecated Database Engine Feature List for SQL Server 2017 shows XP_API as the "feature name" for a list of now-unsupported features.  The list of deprecated features consists of:

srv_alloc
srv_convert
srv_describe
srv_getbindtoken
srv_got_attention
srv_message_handler
srv_paramdata
srv_paraminfo
srv_paramlen
srv_parammaxlen
srv_paramname
srv_paramnumber
srv_paramset
srv_paramsetoutput
srv_paramstatus
srv_paramtype
srv_pfield
srv_pfieldex
srv_rpcdb
srv_rpcname
srv_rpcnumber
srv_rpcoptions
srv_rpcowner
srv_rpcparams
srv_senddone
srv_sendmsg
srv_sendrow
srv_setcoldata
srv_setcollen
srv_setutype
srv_willconvert
srv_wsendmsg

These items are all part of the Extended Stored Procedure API, which is deprecated.

The documentation for sp_executesql makes no mention of it being deprecated.  Although that might be an error of ommission, it seems very unlikely that a core piece of functionality will be deprecated.  Imagine all the code world-wide that would need to be retrofitted.

The deprecation-related Extended Events items are likely flagging some internal use by sp_executesql of one or more of the xp_api items listed above.  Since sp_executesql is an extended stored procedure implemented in the Engine, Microsoft will either re-factor the sp_executesql code to avoid calls to those deprecated events, or will not deprecate them for internal use.

This question is tagged with SQL Server 2008 R2; if you run an Extended Events session in SQL Server 2017 looking at the deprecation_announcement and deprecation_final_support events, you don't see sp_executesql firing events.  To test that, I used this session definition:

CREATE EVENT SESSION [deprecated events] ON SERVER
ADD EVENT sqlserver.deprecation_announcement
(
    ACTION (
        package0.event_sequence
        ,sqlserver.client_app_name
        ,sqlserver.database_name
        ,sqlserver.is_system,sqlserver.sql_text
        )
)
, ADD EVENT sqlserver.deprecation_final_support
    (
        ACTION
        (
            package0.event_sequence
            ,sqlserver.client_app_name
            ,sqlserver.database_name
            ,sqlserver.is_system
            ,sqlserver.sql_text
        )
    )
ADD TARGET package0.ring_buffer (SET max_memory=(10240))
WITH (STARTUP_STATE=OFF);
ALTER EVENT SESSION [deprecated events] ON SERVER STATE=START;
GO

Then ran this code, which uses sp_executesql:

DECLARE @cmd nvarchar(max);
SET @cmd = 'SELECT CurrentDate = GETDATE();';
EXEC sys.sp_executesql @cmd;

The ring buffer output for the [deprecated events] session shows no messages as a result of executing the code.  However, if you run this code, which contains sp_helpremotelogin, which is on the deprecated list, you do see items in the ring-buffer output:

EXEC sp_helpremotelogin @remoteserver = 'test', @remotename = NULL;


Why does dropping a MVIEW trigger a full refresh? I just created a materialized view in Oracle but realized I used the wrong script, so I wanted to drop the MVIEW again.

The MVIEW was created with a statement like this:

create materialized view foobar
  refresh start with trunc(sysdate) + 1/8  next sysdate + interval '8' hour
as
select ...
from ...;

The creation took some time.

Now after realizing it was the wrong script I ran:

drop materialized view foobar;

Which was running for some time as well. I first thought it might be due to some locks or other sessions blocking this, but no: when I queried v$session I was seeing that my drop statement actually triggered a full refresh of the mview:

USERNAME  | SID  | SERIAL# | PROGRAM                  | STATUS | SQL_ID        | SQL_TEXT
----------+------+---------+--------------------------+--------+---------------+-------------------------------------------------------------------------
MY_USER   |  294 |   29131 | oracle@hostname (J002)   | ACTIVE | 6snjgrzbtynyc | INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "MY_USER"."FOOBAR" select .....

And indeed the drop materialized view ran for about the same time as the create.

So my question: why would Oracle trigger a full refresh when I drop a materialized view?

Environment: Oracle 11.2.0.3 64bit

P.S.: I think the alias indexed-view for materialized-view is wrong. Although both reference the same underlying concept they are distinct names used by Microsoft and Oracle. Someone interested in materialized views in Oracle isn't probably interested in indexed views in SQL Server. But I don't know how this alias can be removed

After running some tests in an isolated environment it seems that Raj is right: the refresh background job that I saw while waiting for the DROP wasn't triggered by the DROP. As far as I can tell this was caused by the refresh settings provided during the creation of the mview.

It seems that

refresh start with trunc(sysdate) + 1/8  next sysdate + interval '8' hour

together with the implicit build immediate causes the mview to be refreshed during the creation and then again immediately after the creation has finished.

Apparently start with trunc(sysdate) + 1/8 causes this. When running

create materialized view foobar
   BUILD DEFERRED
   refresh start with trunc(sysdate) + 1/8  next sysdate + interval '8' hour
as
select ...
from ...;
The create statement finishes quickly and when I then look into the active sessions I do see one background session that does the refresh.


bash: sqlplus: command not found I have installed oracle correctly and also set ORACLE_HOME correctly on server,still sqlplus command is not working in my PC while it is working in others PC. I think I missed out some important step can you tell me what should I do ?

 $ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/dbhome_1

This following is error message:

$ sqlplus
bash: sqlplus: command not found

Export the PATH variable. You have missed that.

PATH=$ORACLE_HOME/bin:$PATH
export PATH


MySQL GRANT requiring additional permissions I have a MySQL user (rundeck) that is being used as part of a deployment process. Its purpose is to create a new database, create a new user, and grant permissions to the new user for the new database. The problem is that I can't seem to find a granular permission for the user that allows granting permissions without also exposing the data in the database. If I run with any permissions other than GRANT and ALL, I get the following output:

mysql> GRANT ALL ON `mydatabase`.* TO ''@'192.168.101.%';
ERROR 1044 (42000): Access denied for user 'rundeck'@'hostname.com' to database 'mydatabase'

I really would like to restrict the rundeck user from accessing the data, but can't get GRANT to work without also using ALL. If I use both together, it works perfectly, unsurprisingly. We suspect that the mydatabase.* may be the element requiring additional permissions. Can anyone suggest a group of permissions that will allow a user to grant permissions to a database that they cannot access the data to?

SHOW GRANTS output from working configuration:

GRANT ALL PRIVILEGES ON . TO 'rundeck'@'hostname.com' IDENTIFIED BY PASSWORD 'passhash' WITH GRANT OPTION

SHOW GRANTS output from non-working configuration:

GRANT CREATE, DROP, RELOAD, SHOW DATABASES, CREATE USER ON . TO 'rundeck'@'hostname.com' IDENTIFIED BY PASSWORD 'passhash' WITH GRANT OPTION

The fundamental problem you have is this:

You cannot grant another user a privilege which you yourself do not have; the GRANT OPTION privilege enables you to assign only those privileges which you yourself possess.
— http://dev.mysql.com/doc/refman/5.6/en/grant.html

This makes sense, since if it were otherwise, then you could just grant privileges to yourself.
There is one way around this, depending on how much flexibility you have with the calling code -- stored procedures can run with the credentials of the defining user (as opposed to the invoking user).  If the calling system could call a procedure instead of using GRANT ..., then you can create a procedure that is executable by the restricted user, granting other privileges as appropriate.


How to predict the growth of a PostgreSQL database I'm looking for a way to predict growth of the database given its fields, indexes, constraints (as foreign key), number of lines, and so.

I searched on Google, but I found only means to determine the actual space occupied by the database, and can not predict their growth.

For example, how can I determine how many bytes would be occupied on disk for the following tables, in the following cases

1) 100,000 rows in the account table, 10 rows in the role table and 100,000 lines in account_role table.

2) 500,000 lines per account table, 20 lines on paper table and 600,000 rows in the table account_role?

CREATE TABLE account(
    user_id serial PRIMARY KEY,
    username VARCHAR (50) UNIQUE NOT NULL,
    password VARCHAR (50) NOT NULL,
    email VARCHAR (355) UNIQUE NOT NULL,
    created_on TIMESTAMP NOT NULL,
    last_login TIMESTAMP
);

CREATE INDEX username_index ON COMPANY (username);

CREATE TABLE role(
    role_id serial PRIMARY KEY,
    role_name VARCHAR (255) UNIQUE NOT NULL
);

CREATE TABLE account_role
(
  user_id integer NOT NULL,
  role_id integer NOT NULL,
  grant_date timestamp without time zone,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT account_role_role_id_fkey FOREIGN KEY (role_id)
      REFERENCES role (role_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT account_role_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES account (user_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

Well, for this part of your question:

  I'm looking for a way to predict growth of the database given its fields, indexes,  constraints (as foreign key), number of lines, and so.

  I searched on Google, but I found only means to determine the actual space occupied by the database, and can not predict their growth.

It's not clear why monitoring pg_database_size(your_database_name) doesn't give you the information you are after. Plot the size of all your databases daily, fit a curve to the plot, and you should be able to come up with an estimate of when you need to buy more disk space. What else do you need to know, and why?

For this part of your question:

  For example, how can I determine how many bytes would be occupied on disk for the following tables, in the following cases

  1) 100,000 rows in the account table, 10 rows in the role table and 100,000 lines in account_role table.

  2) 500,000 lines per account table, 20 lines on paper table and 600,000 rows in the table account_role?

You don't really need to concern yourself with how many rows are in each table or the precise structure of the table. Just query SELECT pg_total_relation_size('account'::regclass); and you have the answer, in bytes.


Can comments in stored procedures cause problems We're running a webserver that calls stored procedures to compute complex queries. Using sp_who2, we've discovered a peculiar issue where an abnormal number of processes would be stuck on SELECT state for a long duration. I've ran this sp to track down what query is being ran by the processes and to my surprise, the following is what we discovered:

  --EXECUTE SP_POS_SU01 'SU_INV_LIST_XML', '20110501', '0001031082', '104301775', '021', '2', '', '', '', '', '', '', '', '', '', ''
  --EXECUTE SP_POS_SU01 'IN_LIST_XML', '0001031086', '20110701', '20110725',
  ... Several more executes that are commented out

Here's what our SP looks like:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 --EXECUTE SP_POS_SU01 'SU_INV_LIST_XML', '20110501', '0001031082', '104301775', '021', '2', '', '', '', '', '', '', '', '', '', ''
  --EXECUTE SP_POS_SU01 'IN_LIST_XML', '0001031086', '20110701', '20110725',
ALTER              PROCEDURE [SomeDB].[SP_POS_SU012_XML] @CRDATE        VARCHAR(08) ,
                    @CUS_CD     VARCHAR(10) ,
                    @PRODUCT_CD     VARCHAR(13) ,
                    @ORDER_GB   VARCHAR(03) ,
                    @CANCEL_YN      VARCHAR(01)
                            ...

We weren't sure if the commented execute codes we defined for testing purposes in SP were actually being ran with the SP calls. From our internal testing, we couldn't reproduce the same behavior, but removing those commented lines of execute seemed to do the trick as sp_who2 no longed showed hundreds of processes stuck on SELECT.

But since I have absolutely no way to tell if this was the real cause or not, I have to ask: could such comments actually produce side-effects described here? Or worse, could this have happened from a deliberate attempt to attack our server/database?

I've never heard of comments causing a problem.

The code you captured from the running server and the posted source are for different SPs.  It makes me think that maybe the ..SU01 version is still in your database and being executed (from a job perhaps?) whereas you are trying to debug the ..SU012_XML code.

For testing you could add another INSERT to the SP, writing as single value to a dummy table.  Then if the commented-out lines run there will be more rows in the dummy table than expected.


MySQL Hardware Bottleneck I have a web application that makes simple queries to a MySQL database. The database is quite large (8.4GB) and the time to lookup an entry needs to be as fast as possible.

The database represents a graph theory problem and I'm running a shortest path algorithm that queries it possibly millions of times to find a path. I'm looking at server options to host my app. I expect very light traffic to the site. (Database is well indexed and optimized.)

I know that an SSD server is a must. (Average time with SSD 10-20 seconds on Dell laptop clocked at 1.6Ghz Intel i5, compared to several minute wait on HDD with same hardware)

I consider a 10-20 second wait acceptable and want to ensure that the server can achieve similar results.

My question:

What kinds of CPU speeds/cores should I be looking at? What is the processor clock/cores that will ensure that the SSD is the bottleneck.

I expect that this is varies depending on the nature of the program. If so, how might I go about testing speeds at different clocks, considering I have only one PC?

MyISAM or InnoDB?  Depending on which, you need to see the appropriate cache buffer.  If you had 16GB of RAM, you could set that so that the table will (eventually) be entirely in RAM.  That would move it from being I/O bound (as it probably is now) to being CPU bound.  At that point, an old, slow, cheap, spinning drive will be almost as good as SSDs.

Can you parallelize the algorithm?  If so, split it into as many programs (connections) as you have cores.  That will (assuming CPU-bound) give you a nice speed-up.

Let's see the main query.  And the SHOW CREATE TABLE for any table(s) involved.  There may be simple ways to speed it up.

(I estimate that a table of 8.4GB is about the 96th percentile.)


index creation - Slow Query - PostgreSQL 9.2 I've got the following query:

SELECT
* FROM ( SELECT split_part(full_path, '/', 4)::INT AS account_id,
       split_part(full_path, '/', 6)::INT AS note_id,
       split_part(full_path, '/', 9)::TEXT AS variation,
       st_size,
       segment_index,
       reverse(split_part(reverse(full_path), '/', 1)) as file_name,
       s.segment_data,
       i.st_ino,
       full_path,
       (i.st_size / 1000000::FLOAT)::NUMERIC(5,2) || 'MB' AS size_mb
FROM gorfs.inodes i
JOIN gorfs.inode_segments s
  ON i.st_ino = s.st_ino_target
WHERE i.checksum_md5 IS NOT NULL
  AND s.full_path ~ '/userfiles/account/[0-9]+/[a-z]+/[0-9]+'
  AND i.st_size > 0) as test WHERE account_id = 12115;

Explain analyze for the query above: http://explain.depesz.com/s/rHOU

It seems the query is not using the Index I've created: ix_full_path

Table inodes_segments:

CREATE TABLE gorfs.inode_segments
(
  st_ino "gorfs"."ino_t" NOT NULL,
  segment_index "gorfs"."pathname_component" NOT NULL,
  st_ino_target "gorfs"."ino_t",
  full_path "gorfs"."absolute_pathname",
  segment_data "bytea",
  CONSTRAINT pk_inode_segments PRIMARY KEY ("st_ino", "segment_index"),
  CONSTRAINT fk_host_inode_must_exist FOREIGN KEY (st_ino)
      REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_target_inode_must_exist FOREIGN KEY (st_ino_target)
      REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT uc_no_duplicate_full_paths UNIQUE ("full_path"),
  CONSTRAINT cc_only_root_can_be_its_own_parent CHECK ("st_ino_target" IS NULL OR "st_ino"::bigint <> "st_ino_target"::bigint OR "st_ino"::bigint = 2)
)

Table inodes:

CREATE TABLE gorfs.inodes
(
  st_dev "gorfs"."dev_t" DEFAULT NULL::bigint,
  st_ino "gorfs"."ino_t" NOT NULL DEFAULT "nextval"('"gorfs"."inodes_st_ino_idseq"'::"regclass"),
  st_mode "gorfs"."mode_t" NOT NULL,
  st_nlink "gorfs"."nlink_t" NOT NULL,
  st_uid "gorfs"."uid_t" NOT NULL,
  st_gid "gorfs"."gid_t" NOT NULL,
  st_rdev "gorfs"."dev_t",
  st_size "gorfs"."off_t",
  st_blksize "gorfs"."blksize_t",
  st_blocks "gorfs"."blkcnt_t",
  st_atime "gorfs"."time_t" NOT NULL,
  st_mtime "gorfs"."time_t" NOT NULL,
  st_ctime "gorfs"."time_t" NOT NULL,
  checksum_md5 "md5_hash",
  media_subtype_id integer,
  external_size "gorfs"."off_t",
  CONSTRAINT pk_inodes PRIMARY KEY ("st_ino"),
  CONSTRAINT fk_media_subtype_must_exist FOREIGN KEY (media_subtype_id)
      REFERENCES public.media_subtypes (media_subtype_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT cc_mount_devices_not_supported CHECK ("st_dev" IS NULL)
)

I've already created the following indexes:

CREATE INDEX CONCURRENTLY ix_full_path ON gorfs.inode_segments USING btree (("full_path")::"text");
CREATE INDEX CONCURRENTLY ix_checksum_st_size ON gorfs.inodes USING btree ("checksum_md5","st_size") WHERE checksum_md5 IS NOT NULL;

But it didn't work.

Question:

What can I do to improve the Query?

PostgreSQL can only make use of a function index when the comparison is against the results of the function, e.g.:
AND (s.full_path)::text ~ '/userfiles/account/[0-9]+/[a-z]+/[0-9]+'

Alternatively, create the index without typecasting:
CREATE INDEX CONCURRENTLY ix_full_path ON gorfs.inode_segments USING btree (full_path);

Note also that the character / in regexp has a special meaning, and although ~ can deal with it unescaped (because it is implied), other regexp functions (such as regexp_replace or regexp_matches) will likely fail, so you may want to always escape them, as a general practice:
AND (s.full_path)::text ~ '\/userfiles\/account\/[0-9]+\/[a-z]+\/[0-9]+'

In any case, regex expressions may not benefit from a present BTREE index, unless they're anchored:
AND (s.full_path)::text ~ '^\/userfiles\/account\/[0-9]+\/[a-z]+\/[0-9]+'

The query may still not use the index if the regex comparison is anchored, when it considers that the results would not actually filter much (from collected statistics) and a sequential scan would perform better, e.g. when most of the rows in that table actually start with /userfiles/account/.
Edit - optimising the query
Your query is not optimised for large sets. Firstly, most of you filtering is based on a non-indexed comparison (split_part(full_path, '/', 4)::INT = 12115). Secondly, you apply the filtering condition to the results of the join; PostgreSQL is often smart enough to translate the filter to the subquery, but it is best to construct your queries in a way that will ensure that PostgreSQL will the do the right thing.
For example, filter by account_id directly from full_path:
CREATE INDEX CONCURRENTLY ix_full_path ON gorfs.inode_segments USING btree (full_path); -- ensure your index is not typecasted

SELECT split_part(full_path, '/', 4)::INT AS account_id,
       split_part(full_path, '/', 6)::INT AS note_id,
       split_part(full_path, '/', 9)::TEXT AS variation,
       st_size,
       segment_index,
       reverse(split_part(reverse(full_path), '/', 1)) as file_name,
       s.segment_data,
       i.st_ino,
       full_path,
       (i.st_size / 1000000::FLOAT)::NUMERIC(5,2) || 'MB' AS size_mb
FROM gorfs.inodes i
JOIN gorfs.inode_segments s
  ON i.st_ino = s.st_ino_target
WHERE i.checksum_md5 IS NOT NULL
  AND s.full_path ~ '^/userfiles/account/12115/[a-z]+/[0-9]+' -- filter account directly into string comparison
  AND i.st_size > 0;

Alternatively, you can create a function index by the account_id part of full_path:
-- This index is both useful to filter any rows that do not conform to the below full_path formatting
-- and to search within them for a numeric account_id
-- (note: the index would only be used if the exact same regex comparison is performed on full_path when searching for an account_id)
CREATE INDEX CONCURRENTLY ix_account_id_from_full_path ON gorfs.inode_segments USING btree ((split_part(full_path, '/', 4)::INT))
WHERE (full_path ~ '^/userfiles/account/[0-9]+/[a-z]+/[0-9]+');

SELECT split_part(full_path, '/', 4)::INT AS account_id,
       split_part(full_path, '/', 6)::INT AS note_id,
       split_part(full_path, '/', 9)::TEXT AS variation,
       st_size,
       segment_index,
       reverse(split_part(reverse(full_path), '/', 1)) as file_name,
       s.segment_data,
       i.st_ino,
       full_path,
       (i.st_size / 1000000::FLOAT)::NUMERIC(5,2) || 'MB' AS size_mb
FROM gorfs.inodes i
JOIN gorfs.inode_segments s
  ON i.st_ino = s.st_ino_target
WHERE split_part(s.full_path, '/', 4)::INT = 12115
  AND i.checksum_md5 IS NOT NULL
  AND s.full_path ~ '^/userfiles/account/[0-9]+/[a-z]+/[0-9]+'
  AND i.st_size > 0;


Changing compatibility level of database causes performance improvement of stored procedure We have a Web Application in a Windows Server 2012 Datacenter (IIS 8), running under .NET Framework 4.0, which connects to another Windows Server 2012 Datacenter with SQL Server 2012. My Database has a Stored Procedure that obtains 3 parameters UserID, StartDate and EndDate. This Stored Procedure runs very well but at the end of the month (when all the +40.000 clients uses it) starts to decrease its performance gradually.

The weird thing is that when we Changed the compatibility level to Sql Server 2008 It automatically "refreshed" and stared to work very fast, and then again gradually decreased its performance until we (again) took the compatibility lever back to 2005 and stared to work properly.

Why is it that changing the Compatibility Level the Main Stored Procedure improves a lot after decreasing its performance gradually?

This is because of the changes affected when you modify the compatibility level of any database within SQL Server. This was an affect seen starting at SQL Server 2008 I believe, at least it shows up in documentation since then.

As stated here on MSDN for the ALTER DATABASE SET COMPATIBILITY_LEVEL:

  Compatibility Levels and Stored Procedures

  When a stored procedure executes, it uses the current compatibility
  level of the database in which it is defined. When the compatibility
  setting of a database is changed, all of its stored procedures are
  automatically recompiled accordingly.

So you basically will see every query plan currently in cache get marked for recompile, this would include stored procedures as noted above. So your initial performance improvement is only being seen because the plan for that stored procedure was recompiled. Then after it ran for some time the plan was not sufficient.

Instead of going back and forth between compatibility levels you should try optimizing the procedure so the query plan generated is more optimal for the range of parameter values being passed. Quick alternative, if code changes cannot be done immediately, might be to just schedule a job during the heavy use to initially mark the procedure for recompile.


The Distributor has not been installed correctly. Could not enable database for publishing while in the process of creating a publication in one of my servers I continuously get the following error message

  The Distributor has not been installed correctly. Could not enable
  database for publishing.

Although I have dropped and re-created the distributor several times.

--==============================================================
-- replication - create publication - complete
-- marcelo miorelli
-- 06-Oct-2015
--==============================================================

select @@servername
select @@version
select @@spid
select @@servicename

--==============================================================
-- step 00 --  configuring the distributor
-- if there is already a distributor AND it is not healthy,
-- you can have a look at the jobs related to this distributor and
-- MAYBE, if you need to get rid of it, run this step
-- generally you need to run this when adding a publication it says there is a problem with the distributor
--==============================================================

use master
go
sp_dropdistributor
-- Could not drop the Distributor 'QG-V-SQL-TS\AIFS_DEVELOPMENT'. This Distributor has associated distribution databases.

EXEC sp_dropdistributor
     @no_checks = 1
    ,@ignore_distributor = 1
GO

--==============================================================
-- step 01 --  configuring the distributor
-- tell this server who is the distributor and the admin password to connect there

-- create the distributor database
--==============================================================

use master
exec sp_adddistributor
 @distributor = N'the_same_server'
,@heartbeat_interval=10
,@password='#J4g4nn4th4_the_password#'

USE master
EXEC sp_adddistributiondb
    @database = 'dist1',
    @security_mode = 1;
GO

--==============================================================
-- check thing out before going ahead and create the publications
--==============================================================

USE master;
go

--Is the current server a Distributor?
--Is the distribution database installed?
--Are there other Publishers using this Distributor?
EXEC sp_get_distributor

--Is the current server a Distributor?
SELECT is_distributor FROM sys.servers WHERE name='repl_distributor' AND data_source=@@servername;

--Which databases on the Distributor are distribution databases?
SELECT name FROM sys.databases WHERE is_distributor = 1

--What are the Distributor and distribution database properties?
EXEC sp_helpdistributor;
EXEC sp_helpdistributiondb;
EXEC sp_helpdistpublisher;

--==============================================================
-- here you need to have a distributor in place

-- Enabling the replication database
-- the name of the database we want to replicate is COLAFinance
--==============================================================
use master
exec sp_get_distributor

use master
exec sp_replicationdboption @dbname = N'the_database_to_publish',
                            @optname = N'publish',
                            @value = N'true'
GO

is there anything missing?
any ideas?

I think I have nailed it,
what I did is very simple

A call to the procedure sp_adddistpublisher was missing.

  Configures a Publisher to use a specified distribution database. This
  stored procedure is executed at the Distributor on any database. Note
  that the stored procedures sp_adddistributor (Transact-SQL) and
  sp_adddistributiondb (Transact-SQL) must have been run prior to using
  this stored procedure.

I have run added the last command on the script below step01:

--==============================================================
-- step 01 --  configuring the distributor
-- tell this server who is the distributor and the admin password to connect there

-- create the distributor database
--==============================================================

use master
exec sp_adddistributor
 @distributor = N'the_same_server'
,@heartbeat_interval=10
,@password='#J4g4nn4th4_the_password#'

USE master
EXEC sp_adddistributiondb
    @database = 'dist1',
    @security_mode = 1;
GO

exec sp_adddistpublisher @publisher = N'the_same_server',
                         @distribution_db = N'dist1';
GO

I noticed that now when I call the following proc:

EXEC sp_get_distributor

I see the distribution db installed  column equals 1, I should have noticed this indication before.

If it works I will leave this answer as it is, otherwise I will add to it accordingly.


SQLNET.ALLOWED_LOGON_VERSION=12 and JDBC thin client To fix password security issues we need to set SQLNET.ALLOWED_LOGON_VERSION=12.  According to the Oracle Database Net Services Reference:

  Note the following implications of setting the value to 12:
  ...
  Releases of OCI clients before Oracle Database 10g and all versions of JDBC thin clients cannot authenticate to the Oracle database using password-based authentication.

That statement looks really bad to me.  I don't want to choose between decent password security and the recommended JDBC driver.

The good news is that the manual might be wrong.  I can connect withSQLNET.ALLOWED_LOGON_VERSION=12 and a JDBC thin client.  I did have to upgrade ojdbc.jar from 11.2.0.1.0 to Oracle 11.2.0.3.0, but that is expected and also proves that the setting is truly taking affect on my server.

What's going on here:

Is the manual wrong?
Am I reading the manual wrong?
Is it only working in my simple test case as some undocumented feature, but will fail with other configurations?

We have thousands of users with many different configurations, I cannot rely on just my simple test.  Does anyone else have experience with both SQLNET.ALLOWED_LOGON_VERSION=12 and JDBC thin clients?

I just tested it too, and found that I can connect with 11.2.0.3/11.2.0.4 JDBC thin clients without error, but when using older thin clients like 10.2.0.4, then I get the expected ORA-28040: No matching authentication protocol.

In my opinion, the most logical explanation is that Oracle updated this document many times since 2011 and the particular line you quoted is in fact incorrect today.  It might have been correct pre-11.0.2.3.

Oracle doesn't maintain a change log (such as you would find on FOSS projects, a Wikipedia article, or even the StackExchange questions). However, you can get lucky sometimes if the page was crawled by the Internet Archive Wayback Machine. Turns out this Oracle doc was, so we're in luck, and they have snapshots of it dating back to 2011, preceding the CPUOct2012 by a year.
https://web.archive.org/web/20110715000000*/http://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm

Curiously, if you look at the Dec 2011 version of the doc above, the paragraph you cited actually said (back then):

  ... following implications of setting the value to 11 [not 12] ...

It would seem to me that someone at Oracle updated the number from 11 to 12 without researching the implications.

I vote for answer #1, the manual is wrong.

ADDENDUM: The above criticism aside, you probably want to either submit a "Reader Comment" on the bottom of the Oracle doc, or submit an SR, or both.  If you have an Oracle customer relationship, you're probably paying a lot of money, so might as well make use of it and file the SR. Either way Oracle responds your a$$ is covered. i would very much like to see Oracle's response to this question.


inserting multiple values into SQL with % This works:

cursor = conn.execute("""Select FAMILY,F_NAME from birthday where B_MONTH = '%s' """ % (currentMonth))

I cannot seem to expand it to work using an AND clause to also include day, I get a syntax error:

cursor = conn.execute("""Select FAMILY,F_NAME from birthday where B_MONTH = '%s' AND B_DAY = '%s' """ % (currentMonth),%(currentDay))

The % operator takes a string on its left side, and a list of values on its right side:

execute("SELECT ... WHERE B_MONTH = '%s' AND B_DAY = '%s'" % (currentMonth, currentDay))

Please note that using % introduces the risk of SQL injections when used with strings, so you should always use SQL parameters instead:

execute("SELECT ... WHERE B_MONTH = ? AND B_DAY = ?", (currentMonth, currentDay))


Want to output post title with meta-tags at same time on MySQL of WordPress I want to edit meta-tags of a blog page of my WordPress blog using SQL editting tool such as MySQL workbench.

I want SQL output such as below. Can not?

# post_title, metatag
'metadev', 'hoge'
'metadev', 'fuga'

SELECT * FROM yuis_yuisorgblog.wp_posts;

Output of above SQL is below.

# ID, post_author, post_date, post_date_gmt, post_content, post_title, post_excerpt, post_status, comment_status, ping_status, post_password, post_name, to_ping, pinged, post_modified, post_modified_gmt, post_content_filtered, post_parent, guid, menu_order, post_type, post_mime_type, comment_count
'164', '1', '2018-08-20 15:49:53', '2018-08-20 06:49:53', '
metatagdev
\n', 'metatagdev', '', 'publish', 'closed', 'open', '', 'metatagdev', '', '', '2018-08-20 15:49:53', '2018-08-20 06:49:53', 'metatagdev', '0', 'http://yuis.org/blog/?p=164', '0', 'post', '', '0'

SELECT * FROM yuis_yuisorgblog.wp_term_relationships;

Output of above SQL is below.

# object_id, term_taxonomy_id, term_order
'164', '5', '0'
'164', '6', '0'

SELECT * FROM yuis_yuisorgblog.wp_term_taxonomy;

Output of above SQL is below.

# term_taxonomy_id, term_id, taxonomy, description, parent, count
'5', '5', 'post_tag', '', '0', '1'
'6', '6', 'post_tag', '', '0', '1'

SELECT * FROM yuis_yuisorgblog.wp_terms;

Output of above SQL is below.

# term_id, name, slug, term_group
'5', 'hoge', 'hoge', '0'
'6', 'fuga', 'fuga', '0'

SELECT post_title,name FROM wp_posts
JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_posts.ID
JOIN wp_terms ON wp_terms.term_id = wp_term_relationships.term_taxonomy_id
WHERE post_type = 'post'


Calculations over UNION, merge query repetitive code I am struggling with this query a lot mainly because of the repetition which possibly could be reduced - hopefully.

The general idea is to display sums of each table for a given month and display it as a pivot table.

Current code is in my opinion very messy and repetitive but the main thing is it doesn't allow me to make the calculations, due the UNION joining.

For each month as following ( I assume this has to be a derivative table that takes values of the other and calculates this formula for each month ):

Calculation Formula = (
    employees_salaries +
    employees_salaries_insurence_costs +
    employees_agreement +
    employees_agreement_insurence_cost +
    employees_extras +
    employees_to_hand +
    employees_delegations ) - employees_deductions

Current query is working displays everything properly but the questions and desired output :

Question : Is there any possible query minification
         - Possible query structure to enable the calculation formula

Desired output : Current layout is what needs to stay

I am trying since few days but I am unable to accomplish my desired result, any tips will be really helpful.

Output

+------------------------------------+---------------------+------+-------+-------+-----+------+-------+----------+-----------+---------+----------+----------+
|         employees_salaries         |       January       | Febr | March | April | May | June | July  |  August  | September | October | November | December |
+------------------------------------+---------------------+------+-------+-------+-----+------+-------+----------+-----------+---------+----------+----------+
| employees_salaries                 | 0                   |    0 |     0 |     0 |   0 |    0 |     0 | 15826.12 |         0 |       0 |        0 |        0 |
| Calculations                       | Calculation Formula |      |       |       |     |      |       |          |           |         |          |          |
| employees_salaries_insurence_costs | 0                   |    0 |     0 |     0 |   0 |    0 |     0 |  5044.44 |         0 |       0 |        0 |        0 |
| employees_agreement                | 0                   |    0 |     0 |     0 |   0 |    0 |   250 |        0 |         0 |       0 |        0 |     3550 |
| employees_agreement_insurence_cost | 0                   |    0 |     0 |     0 |   0 |    0 | 71.05 |        0 |         0 |       0 |        0 |   178.65 |
| employees_extras                   | 0                   |    0 |     0 |     0 |   0 |    0 |     0 |        0 |         0 |    1.00 |        0 |        0 |
| employees_deductions               | 0                   |    0 |     0 |     0 |   0 |    0 |     0 |    45.60 |         0 |    1.00 |        0 |        0 |
| employees_to_hand                  | 0                   |    0 |     0 |     0 |   0 |    0 |     0 |        0 |         0 |  111.00 |   555.00 |        0 |
| employees_delegations              | 0                   |    0 |     0 |     0 |   0 |    0 |     0 |        0 |      2520 |     540 |        0 |        0 |
+------------------------------------+---------------------+------+-------+-------+-----+------+-------+----------+-----------+---------+----------+----------+

MCVE Example + Schema : https://www.db-fiddle.com/f/8RvfD3uA4cSSVgmhoP2co6/9

Setting aside the odd ball row (Calculations/Calculation Formula) for the moment, I opted for doing a union all of the raw data (in a derived table), and then grouped/pivoted the derived table, eg:

select ctype                                                       As employees_salaries,
       COALESCE(SUM(case when month(cdate)=1  then camount end),0) As January,
       COALESCE(SUM(case when month(cdate)=2  then camount end),0) As Febr,
       COALESCE(SUM(case when month(cdate)=3  then camount end),0) As March,
       ...
from   (select 'employees_salaries'                  as ctype,
               mmonth                                as cdate,
               gross                                 as camount
        from   employees_salaries
        where  year(mmonth) = year(curdate())

        union all

        select 'employees_salaries_insurence_costs',
               mmonth,
               insurence_cost1+insurence_cost2
        from   employees_salaries
        where  year(mmonth) = year(curdate())

        union all
        ... ) as c
group by ctype, year(cdate)

order by 1

Here's a fiddle for the complete query.


How can I drop a view only if it exists in Oracle? I would like to perform a DROP VIEW IF EXISTS... command on an Oracle DB as it can be done on a MySQL database. How can this be achieved?

An alternate method:

  DECLARE
     dne_942    EXCEPTION;
     PRAGMA EXCEPTION_INIT(dne_942, -942);
  BEGIN
     EXECUTE IMMEDIATE 'DROP VIEW my_view';
  EXCEPTION
     WHEN dne_942 THEN
        NULL; -- if it doesn't exist, do nothing .. no error, nothing .. ignore.
  END;
  /

This has the added benefit of not needing to query the data dictionary .. just try to fire the drop .. and if it doesn't exist .. that's fine .. no real issue.


Importing via sqlcmd errors out PS C:\Users\administrator.INTERNAL\Desktop> sqlcmd -S SQLMAIN\MSSQLSERVER -i rdmdb.sql
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : SQL Server Network Interfaces: Connection string is not valid [
87]. .
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A network-related or instance-specific error has occurred while
 establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and i
f SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

I opened the port 1433 to allow remote connections in the firewall. I also disabled the firewall with no success.

I double checked server name and instance and they all check out.

If you are really sure that your Server name is correct you can still try to specify the port explicitly like that:

sqlcmd -S SQLMAIN,1433 -i rdmdb.sql

If you run your SQL Server database as a Contained Database it might be necessary to login to the database direclty using -d:

sqlcmd -S SQLMAIN,1433 -d yourDatabase -i rdmdb.sql

Make sure that you really can access your SQL Server on the specified port. You can try this using telnet.

telnet SQLMAIN 1433

If you don't use a windows login you might need to provide your username and passowrd using -U (Username) and -P (Password).

sqlcmd -S SQLMAIN,1433 -d yourDatabase -U User -P Password -i rdmdb.sql

If you use the integrated authentication, you can also try the Trusted Connection using -E

sqlcmd -S SQLMAIN,1433 -d yourDatabase -E -i rdmdb.sql

Please be also be sure that the specified input file can be accessed and found at your specified location.

Hopefully this will answer your question and your getting your connection to work. :-)


Sql clustering sql server account Does both the sql cluster  nodes needs to use the same service account ? In my environment I saw one one was using a service account and another was using a local account although the sql agent was using the same acccount and once the failover went to the node which was using the local account i was not able to failback to the original node and also the back up were failing. Is this due to the mismatch of the sql server service account ? please help

All nodes in a cluster should be using the same service account for SQL Server. This is true for both an FCI instance and an Availability Group setup.

For security sake, consider using Group Managed Service Accounts.https://blogs.technet.microsoft.com/askpfeplat/2012/12/16/windows-server-2012-group-managed-service-accounts/

This will allow you to have a service account that will have an strong, automatically changing password for your SQL Servers. Auditors love this. Your risk is minimum since Active Directory is handling the password change and downtime is not required to do this.


Postgres - Insert performance with indexes We have a table with about 20 columns in a Postgres 9.2.10 DB. For better performance on some SELECT queries, we plan to add an index on one column with data type timestamp. As the index also can slow down the performance on inserts, we did the following performance test:

We inserted 5 million records into the table. That's the max. number of records we expect in production. Then we measured the time to insert 10000 records with and without index on the timestamp column. This is the maximum number of inserts we expect per day, In peaks not more than 5 inserts per second.

Here the results:

With index - 84 441 ms
Without index - 78 000 ms

At least for this test, the index slowed down the performance only slightly. For our requirements, I do not see an issue to add the index.

But this was only one test in a lab environment, are there any other pitfalls when running this on a production database? Can we get into a situation, where an INSERT suddenly takes more than 5 seconds under specific circumstances?

No, a basic btree index on a small column is typically cheap to maintain. Of course it gets slightly more expensive when the table has accumulated some bloat from dead rows, but the difference should be small. And you have to consider additional storage on disk for the index.

One thing seems worth mentioning: Updates on columns involved in an index in any way cannot use H.O.T. updates ("heap-only tuples"), so this might add some cost for updates changing the indexed column. But still, this would just prevent one internal optimization, no big deal. And only relevant if you involve the column in updates.

Is a composite index also good for queries on the first field?
Redundant data in update statements


Set method to change StartMode property using SqlServer.Management.Smo.Wmi.Service I'm trying to change SQL server Configuration manager settings using Microsoft.SqlServer.Management.Smo.Wmi.Service. Microsoft provided with a list of methods & properties here. I used the following code to change the Service account of a particular service:

$MyServerObject.SetServiceAccount($UName, $PWord)

because there exists a method called SetServiceAccount. But I do not understand/know how to set StartMode which a property. I know that currently my service says it is automatic,coz I can verify it using this line of code

$MyServerObject.StartMode

and the result is: Auto

Anybody knows how to set this property?

If someone wants to see what I'm talking about run these lines in PowerShell (Run as Administrator) on a computer where SQL Server is installed:

#Load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null

#Create Server Object
$SMOWmiserver = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer')

#Query Server object
$SMOWmiserver.Services |
    select name, type, ServiceAccount, DisplayName, Properties, StartMode, StartupParameters |
        Format-List

#Select a service
$MyServerObject=$SMOWmiserver.Services | where {$_.name -eq "MSSQLSERVER"}
$MyServerObject.StartMode

Hopefully I can answer your question and explain how I did so.

To alter the service start mode using your code above you run

# Check Start Mode
$MyServerObject.StartMode
# Set Start Mode
$MyServerObject.StartMode ='Manual'
# Alter Start Mode
$MyServerObject.Alter()
# Check Start Mode again
$MyServerObject.StartMode

You can establish by using Get-Member as shown below

$MyServerObject|Get-Member

In the list of results you will see

StartMode                Property   Microsoft.SqlServer.Management.Smo.Wmi.ServiceStartMode StartMode {get;set;}

The {get;set;} shows that you can alter the property

You will also see

Alter                    Method     void Alter(), void IAlterable.Alter()

This is the method that you will use to apply the change to the object.

You can use the same principle throughout your PowerShell use.

It is also possible to do this remotely, if you have the correct privileges for your account, to do the following

# Get Service
$Service = Get-WmiObject win32_service -ComputerName COMPUTERNAME -Filter "Name = 'MSSQLSERVER'"
# Get Start Mode
$Service.StartMode
# Change Start Mode
$Service.ChangeStartMode('Manual')
#Check Start Mode
$Service.StartMode

to achieve the same result


Delete log to increase DB space? I'm running SQL Server 2008 Express and hitting the 4GB size wall.  I'd like to make some space to give me time before i migrate to a larger DB.

I don't need my transaction log.  So, can i delete my log to increase space?

Otherwise, i'll have to create a duplicate DB structure, and then copy the data into the new DB and then delete everything from the original DB so i can start over.

In this case, is there an "easy way" to copy the data from the old tables into new tables?

I don't need my transaction log.

Yes you do.

  can i delete my log to increase space?

No you can't.

  In this case, is there an "easy way" to copy the data from the old tables into new tables?

Before you go throwing the towel in with your current database, you should step back and analyze why you think your transaction log is too large in your current database.  Unless you logged workload changes, the same behavior will occur in the new database.

Find out first how large your transaction log file is and how much space is actually being used inside the file:

use YourDatabaseName;
go

select
    name,
    physical_name,
    type_desc,
    size_mb =
        convert(decimal(10, 2), size * 8.0 / 1024),
    used_space_mb =
        convert(decimal(10, 2), fileproperty(name, 'spaceused') * 8.0 / 1024)
from sys.database_files;

Note: you'll need to change "YourDatabaseName" to the appropriate database name.

Likewise, there are certain database settings (recovery model) and log reuse descriptions that can also aid in the troubleshooting of a transaction log that is just too large:

select
    name,
    recovery_model_desc,
    log_reuse_wait_desc
from sys.databases
where name = 'YourDatabaseName';

The above queries should be a decent starting point with troubleshooting and/or discovery regarding your problem.  Feel free to comment below with your findings and we can take this a step further.

Another thing, are you completely confined to SQL Server 2008 for your Express Edition?  In SQL Server 2008 R2 and above, the maximum database size is 10 GB.  Can you not use a newer version of SQL Server?


What are SHOWPLAN permission security risks? (MS SQL Server) I'm having some friction when it comes to showplan permission security.

Say there are 5 databases on a server. And each one has a test/ production split (so 10 total).

There is one sysadmin.

There is another person (non-sysadmin) who is responsible for the data, queries, tables, views, design, structure, indices, etc on DB Number #1. And that alone.

Do you grant SHOWPLAN/ View Server State permissions on DB Number #1 to the person responsible for its design, query optimization, etc? Or does that present an unacceptable security risks to a non sys-admin?

Perhaps it's a bit subjective or a gray area, but I wasn't sure. Are there specific patches/ procedures that can be put in place to mitigate these risks?

Are there any alternatives to SHOWPLAN when optimizing queries and query run time? What about sys.sql_expression_dependencies, also a related View Server State permission, correct?

Some may argue that you have a a DB_Development, DB_Test, and DB_Production split. Perhaps ideally, but when you only have the resources to implement a Test/ Production split, maybe access on Test alone is sufficient risk mitigation?

You can read more on the security risk in: SHOWPLAN Permission and Transact-SQL Batches:

  Security Note
  Users who have SHOWPLAN, ALTER TRACE, or VIEW SERVER STATE permission can view queries that are captured in Showplan output. These queries may contain sensitive information such as passwords. Therefore, we recommend that you only grant these permissions to users who are authorized to view sensitive information, such as members of the db_owner fixed database role, or members of the sysadmin fixed server role. We also recommend that you only save Showplan files or trace files that contain Showplan-related events to a location that uses the NTFS file system, and that you restrict access to users who are authorized to view sensitive information.


SELECT from slave and INSERT into master in MySQL I'm using PHP/Laravel and MySQL databases.
Briefly, I want to use slave databases for reads and master database for writes.
What Laravel config, Laravel package, or database tool do you suggest to handle my issue?

Dangerous. Read about "Critical Read". If you blindly send all SELECTs to a Slave, you could screw up transactions on the master. Or you could miss data when replication is "behind".

Instead, modify your application to connect to either the Master or to some Slave, based on whether it is safe to get the data just from a Slave.


Looking for good, but quick references for underlying OS concepts related to SQLOS (Memory Access, Scheduling, etc...)? Looking for good, but quick references for underlying OS concepts related to SQLOS (Memory Access, Scheduling, etc...)?

I am researching SQLOS and how threads and scheduling are handled, but I would like to review some OS concepts and how SQLOS interacts with the environment. I am aware of the Operating Systems Concepts textbook, but I was wondering if there was a good resource available online that is a little more brief and to the point.

This is a very broad quesiton and will be eventually closed.

Below page from BOL will be helpful to you :

Thread and Task Architecture
Platform layer for SQL Server - A new platform layer in SQL Server 2005 to exploit new hardware capabilities and their trends by Slava Oks
Architectural Overview of SQL Server 2014’s In-Memory OLTP Technology
SQL Server Threading Model - This is to Paul Randal's online course about wait stats on Pluralsight.


Postgres Installed in Linux (RHEL) and trying to access / connect using pgadmin (Windows) I've installed and configured postgresql in Linux successfully,

1. postgres user created
2. new db created successfully
3. I'm trying to connect using pgadmin III (Windows)
4. Following details are adding in pgadmin window

Host: DEMO
Host: XXXX
Port: 5432
Maintenance DB: postgres
Username: postgres
password: G0!mf17.

5. Clicked on OK

It's tried to connect and displayed message connecting to database.... Failed.

I'm not sure why it's failed and what needs to configure in linux system.

I'm new to linux, If anyone able to guide me solve this issue it will be greatful.

Please let me know if you need more details.

First try to be more specific:

Which version of RHEL are you using?
Which version of Windows are you using?
How are these computers connected?

Are they in the same network segment?
Are there any firewalls in between

Next, try the following:

Check firewall configurations

Windows firewall configuration (or any Antivirus/Firewall software that may be blocking the pgAdmin)
SELinux configuration and/or any antivirus/firewall that may be preventing connections to the linux host
If the host is a 'hostname' then check that the network services is translating the name correctly. Try a ping to the IP and then ping to the hostname. Or issue a dnslookup.

In the linux system try netstat -a | grep LISTEN |grep "5432" to verify that there is a process actively LISTENING on port 5432

Hope this helps you to start solving your problem.


Embedded select with rank I am having the hardest time following the tutorials on this site regarding ranking with MySQL.  I have a larger problem than others seem to show.
My table is constantly updating with single answers (ans) corresponding to a matching word (word).  I also have to check whether the row has an answer at all (ans IS NOT NULL).

I've devised the following:

SELECT userid, floor(sum(ans = word) / count(id) * 100) as score
FROM fb_asl.quiz
WHERE DATE(`time`) BETWEEN CURDATE() + INTERVAL (-3) DAY AND CURDATE() + INTERVAL (-3) DAY
AND ans IS NOT NULL
GROUP BY userid
ORDER BY score DESC;

This returns the following

1504    93
3567    79
8225    66

How can I go about getting a rank corresponding to a specific userid?  Say returning something like:

2   3567    79

Based on the above query, I have a solution for you

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT score,userid,rank FROM
(
SELECT AA.*,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    (@curscore:=score) newscore
FROM
(
    SELECT
        userid,
        floor(sum(ans = word) / count(id) * 100) as score
    FROM fb_asl.quiz
    WHERE
        DATE(`time`) BETWEEN
        CURDATE() + INTERVAL (-3) DAY AND
        AND CURDATE() + INTERVAL (-3) DAY
        AND ans IS NOT NULL
    GROUP BY userid
) AA ORDER BY score DESC) A;

Let me demonstrate it with some sample data

SAMPLE DATA

use test
DROP TABLE IF EXISTS scores;
CREATE TABLE scores
(
    id int not null auto_increment,
    score int not null,
    primary key (id),
    key score (score)
);
INSERT INTO scores (score) VALUES
(50),(40),(75),(80),(55),
(40),(30),(80),(70),(45),
(40),(30),(65),(70),(45),
(55),(45),(83),(85),(60);

SAMPLE DATA LOADED

mysql> DROP TABLE IF EXISTS scores;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE scores
    -> (
    ->     id int not null auto_increment,
    ->     score int not null,
    ->     primary key (id),
    ->     key score (score)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO scores (score) VALUES
    -> (50),(40),(75),(80),(55),
    -> (40),(30),(80),(70),(45),
    -> (40),(30),(65),(70),(45),
    -> (55),(45),(83),(85),(60);
Query OK, 20 rows affected (0.02 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql>

WHAT SAMPLE DATA LOOKS LIKE

mysql> SELECT * FROM scores;
+----+-------+
| id | score |
+----+-------+
|  1 |    50 |
|  2 |    40 |
|  3 |    75 |
|  4 |    80 |
|  5 |    55 |
|  6 |    40 |
|  7 |    30 |
|  8 |    80 |
|  9 |    70 |
| 10 |    45 |
| 11 |    40 |
| 12 |    30 |
| 13 |    65 |
| 14 |    70 |
| 15 |    45 |
| 16 |    55 |
| 17 |    45 |
| 18 |    83 |
| 19 |    85 |
| 20 |    60 |
+----+-------+
20 rows in set (0.00 sec)

mysql>

MY SOLUTION AGAINST THE SAMPLE DATA

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT score,ID,rank FROM
(
    SELECT AA.*,BB.ID,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    (@curscore:=score) newscore
    FROM
    (
        SELECT * FROM
        (SELECT COUNT(1) scorecount,score
        FROM scores GROUP BY score
    ) AAA
    ORDER BY score DESC
) AA LEFT JOIN scores BB USING (score)) A;

MY SOLUTION EXECUTED AGAINST THE SAMPLE DATA

mysql> SELECT score,ID,rank FROM
    -> (
    ->     SELECT AA.*,BB.ID,
    ->     (@rnk:=@rnk+1) rnk,
    ->     (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    ->     (@curscore:=score) newscore
    ->     FROM
    ->     (
    ->         SELECT * FROM
    ->         (SELECT COUNT(1) scorecount,score
    ->         FROM scores GROUP BY score
    ->     ) AAA
    ->     ORDER BY score DESC
    -> ) AA LEFT JOIN scores BB USING (score)) A;
+-------+------+------+
| score | ID   | rank |
+-------+------+------+
|    85 |   19 |    1 |
|    83 |   18 |    2 |
|    80 |    4 |    3 |
|    80 |    8 |    3 |
|    75 |    3 |    5 |
|    70 |    9 |    6 |
|    70 |   14 |    6 |
|    65 |   13 |    8 |
|    60 |   20 |    9 |
|    55 |    5 |   10 |
|    55 |   16 |   10 |
|    50 |    1 |   12 |
|    45 |   10 |   13 |
|    45 |   15 |   13 |
|    45 |   17 |   13 |
|    40 |    2 |   16 |
|    40 |    6 |   16 |
|    40 |   11 |   16 |
|    30 |    7 |   19 |
|    30 |   12 |   19 |
+-------+------+------+
20 rows in set (0.00 sec)

Give it a Try !!!


Why would execution plan decide to sort 500k rows instead of 4k? Consider the following query:

select top 50 p.value, p.pointid from point p
    where exists (select c.collectionid from collection c
        where c.parentid = 1
        and c.somebool = 0 and c.someotherbool = 1
        and p.collectionid = c.collectionid)
    order by p.value desc

In a given schema where the collection table has points and a parent is referenced from the collection table (part of this query).
Something about this query and the indexes I have on the more complex version of this results in Sql Server thinking its a good idea to execute the "order by" FIRST on a table of ~500k rows instead of executing the where and working on 4k rows instead.
This results in a spill (btw would a spill kill the cache?) which just destroys performance seemingly across the board.

I created this a smaller example of this but couldn't reproduce the problem making me believe there is some sort of issue with another index or materialized view or something that is confusing the execution plan.

On the more complex version of this I can get the desired result by forcing it to use an index that is on the equivalent of point.collectionid. Sadly I'm constrained by an ORM that doesn't output nice SQL otherwise I'd use the hint or temp table.
Amusingly enough top(3250) causes the 500k sort but top(3500) doesn't.

Any ideas about the rationale? Could this be a bug in Sql Server?

EDIT: An image showing the original execution plan and my attempt to recreate the problem.
We can see from this that in the "madness" it begins the query off the PK clustered of the 500k row table, in the recreation (there are a million rows in that one) it correctly executes the WHERE part of the clause first starting with the PK of the other table to reduce its subset to 4k as opposed to sorting the whole thing.

Sorry I can't post the lot but there is a large amount of noise in this db and I don't want to post internals.

The general answer here is: Because it does not know.

The Query Optimizer makes assumptions based on expense based on statistics. Missing indices, outdated statistics and some other things (too complex query -> running out of time to find the optimal plan, more rare) can lead to a bad execution plan.

Missing indices and outdated statistics are the most likely culprits.

For a detailed analysis you need to look at the plan - not a picture of the plan. Of an actual execution (so you can compare initial estimated rows with actual rows, which gives an idea how far off the query optimizer's assumptions were).

If you have a top clause, then sql server really gets a little into murky water. It knows the distribution of foreign keys in the whole data set via statistics (histogram), but how far are those represented in your top results? This can leave sql server on a bad path because statistical mathematics may break down. Which results in a non optimal query path.

Query optimizers are also under constant development ;) Not saying bug - more like "edge cases better handled in newer versions".


Extrapolate values and sum I have data in the form:

|Date      |Overdrawn|
|20/09/2018|1        |
|21/09/2018|1        |
|24/09/2018|0        |
|25/09/2018|1        |
|26/09/2018|1        |

I need to sum the number of overdrawn days with the following rule:

Missing dates (weekends) should be treated the same as the last date available.

i.e. If overdrawn on Friday, Saturday and Sunday should be counted as 2 days overdrawn. If Friday is not overdrawn, Saturday and Sunday should also be treated as not overdrawn.

In the example above date 20, 21, 22, 23, 25, 26 should be treated as overdrawn and the sum should return 6.

Can someone explain how to extrapolate the overdrawn value for the missing dates before summing?

Thanks

Try this one:

create table #test (
    date date,
    overdrawn tinyint
)

insert into #test values
    ('2018-09-20', 1),
    ('2018-09-21', 1),
    ('2018-09-24', 0),
    ('2018-09-25', 1),
    ('2018-09-26', 1)

select sum(
        case datepart(dw, date)
            when 6 then overdrawn * 3 --Friday
            else overdrawn
        end
    )
from #test

This solution is based off the fact that the overdrawn field can either be 0 or 1 only. You'll have to include additional conditions if that's not the case.


Creating plan guide for query called through sp_executesql To make a long story short, I have a view called vwRelatives which uses CTE recursion to build family trees. It's meant to be queried for a single person at a time.

This runs in about a quarter second:

SELECT * FROM vwRelatives WHERE person_id = 5

This (the way the query is executed from the application) takes more like 4.5 seconds:

exec sp_executesql N'SELECT * FROM vwRelatives WHERE person_id = @P1',N'@P1 int',5

(Note that I've simplified the query a bit. The real thing has an explicit column list and an ORDER BY, but the WHERE semantics are the same. I get the same symptoms with either version.)

Most likely, SQL Server is able to take person_id = 5 into account when creating an execution plan for the first query, but parameterizing it is causing the whole view to be run and then filtered by person_id.

So I figured I'd create a plan guide. And now I have two problems.

These are the steps I'm taking, which appear to have no effect.

First, run the 'good' query to get it into the plan cache...

SELECT * FROM vwRelatives WHERE person_id = 5

...then perform the standard steps to turn it into a plan guide...

--Get the 'good' plan
SET @xml_showplan = (
    SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
        CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
        WHERE st.text LIKE N'SELECT * FROM vwRelatives WHERE person_id = 5'
)

--Apply a plan guide to the meat of the sp_executesql query
EXEC sp_create_plan_guide
    @name = N'vwRelatives_Test_Plan_Guide',
    @stmt = N'SELECT * FROM vwRelatives WHERE person_id = @P1',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 int',
    @hints = @xml_showplan;

This completes successfully, but when I run the original sp_executesql statement again, it still takes 4.5 seconds. I have Profiler running, and both the Plan Guide Successful and Plan Guide Unsuccessful events are selected. Neither of those events ever shows up in the trace.

What am I doing wrong that's preventing SQL Server from seeing this plan guide as a match for the sp_executesql query?

For those stumbling upon this question trying to create a plan guide, the syntax I had originally is correct. The reason it wasn't working (I suspect - I can't find any confirmation in the documentation) is that the view uses a CTE for recursion. Evidently this disqualifies it from plan guide use.

My original problem was that the recursive view performed poorly when a SELECT statement was issued via sp_executesql with parameters (the client is an Access database).

I finally came across this older question on Stack Overflow where somebody is having basically the same problem:

https://stackoverflow.com/questions/4226035/why-does-a-query-slow-down-drastically-if-in-the-where-clause-a-constant-is-repl

I was kind of beginning to wonder if I would need to trick/help the query optimizer by pushing the recursion out to a user-defined function, and this confirmed the suspicion. I moved all the CTEs from the view into an inline UDF that uses the @person_id parameter directly in the anchor of the recursion, and now it's pleasantly fast even with sp_executesql.

So, not the solution I originally thought I was going to need, but I'll take it. (It's probably more straight-forward this way, too. I don't have to worry about attaching a plan guide to every subtle variation of the query that Access might construct.)


Improve query performance of subquery with MAX() I am looking to migrate our support database from MS SQL Server to Postgres and most of it has gone OK. However, some of the queries seem to be slower on PG than on the SQL Server.

We have a ticket table which has a related table assignments. In the assignment table (linked by ticket_id) we can make comments and assign the ticket to another group to work on the ticket. So to find out who currently has the ticket we need to get the last update for the ticket

Select tbltickets.ticketid, tbltickets.ticketsummary, ta2.*
from tblTickets
Join (
   SELECT tblassignment.ticketid,
          max(tblassignment.allocationid) AS maxallocationid
   FROM tblassignment
   join tbltickets on tblassignment.ticketid = tbltickets.ticketid
   GROUP BY tblassignment.ticketid) as ta on tbltickets.ticketid = ta.ticketid
join tblassignment ta2 on ta.maxallocationid = ta2.allocationid
where tbltickets.closed is NULL;

So it works but it slower than we would like circa 2seconds and will only get slower.

it is looking up the maxallocationid subquery that is taking the time at 1.5s as it is doing the calculation for all the assignments, not just the ones for the tickets we are interested in (e.g closed is NULL)

I can improve like this:

Select tbltickets.ticketid, tbltickets.ticketsummary, ta2.*
from tblTickets
Join (
   SELECT tblassignment.ticketid,
          max(tblassignment.allocationid) AS maxallocationid
   FROM tblassignment
   join tbltickets on tblassignment.ticketid = tbltickets.ticketid
   where tbltickets.closed is null
   GROUP BY tblassignment.ticketid) as ta on tbltickets.ticketid = ta.ticketid
join tblassignment ta2 on ta.maxallocationid = ta2.allocationid
where tbltickets.closed is NULL;

It now runs in 170 msec which is fine, but I cannot then use in a view to make it reusable with different criteria (or at least I don't know how).

Is there a way to allow for filtering the tickets, then look up maxassignment only on returned tickets, preferable without reorting to functions/stored procedures!

I have considered using a trigger to update the ticket table with info from the assignment, but do not want to go there if I can avoid it as I am not familiar with this!

Looks like you want a (LEFT) JOIN LATERAL - the standard SQL equivalent of (OUTER) APPLY in SQL Server, as a_horse called to mind:

SELECT t.ticketid, t.ticketsummary, ta2.*
FROM   tblTickets t
LEFT   JOIN LATERAL (
   SELECT ta.allocationid
   FROM   tblassignment ta
   WHERE  ta.ticketid = t.ticketid
   ORDER  BY ta.allocationid DESC NULLS LAST  -- see below!
   LIMIT  1
   ) ta2 ON true
WHERE  t.closed is NULL;

The LEFT JOIN preserves rows from tblTickets without related rows in tblassignment. To eliminate those, use JOIN instead.

Be sure to have an index on tblassignment (ticketid, allocationid DESC NULLS LAST).
Drop NULLS LAST in query and index if allocationid is defined NOT NULL. (Index and query must agree on this for the index to be applicable.)

You can add any predicates to filter rows from tblTickets before the greatest allocationid is looked up, only for qualifying rows. (Maybe add index-support for common predicates on tblTickets ...)

This query should be very fast.

Related:

What is the difference between LATERAL and a subquery in PostgreSQL?
SQL Server - conditional aggregation with correlation
Postgres analogue to CROSS APPLY in SQL Server
Optimize GROUP BY query to retrieve latest row per user
PostgreSQL sort by datetime asc, null first?
MySQL: Get most recent record


How to find which edition the SQL Server installation media belongs to Without installing SQL Server media 2008 and 2012, how can we know that the media belongs to a certain edition (like Developer, Standard or Enterprise)?

Per this thread on MSDN:

  SQL 2008 onwards SQL server setup allows you to chose the option which edition you want to install. That means one media has the files of all edition.

and

  ..., the FULL media is good to install all editions, it depends on the PID you provide during setup.

The media layout is found in \MediaInfo.xml, and the default product key is in \<platform>\DefaultSetup.ini. Note that the product key is the same, regardless of platform.

Unfortunately, I haven't found a way to determine the available editions without running Setup and looking at the list after entering the product key. (I assume this is by design for security reasons.)

If you have a lot of these to validate, it's probably faster to grab a list of the product keys and check against the keys available in your MSDN subscription. The corollary to that is to always replace the default product key during Setup with one of the keys on MSDN.


Create trigger to insert rows from one table to another with sum operation (group by) I have two tables in my database traffic_shift and traffic_daily. With following columns.

Table traffic_shift :-

day     month    year    toll_id    shift    car_single    car_return
 1       9        2016    1          1        10            5
 1       9        2016    1          2        100           50
 1       9        2016    1          3        200           100

 1       9        2016    2          1        15            20
 1       9        2016    2          2        30            40
 1       9        2016    2          3        45            60

 2       9        2016    1          1        12            6
 2       9        2016    1          2        24            12
 2       9        2016    1          3        36            18

 2       9        2016    2          1        18            9
 2       9        2016    2          2        36            18
 2       9        2016    2          3        54            27

traffic_daily table will be like this,

Table traffic_daily

day     month    year    toll_id    car_single    car_return
 1       9       2016     1          310          155
 1       9       2016     2          90           120
 2       9       2016     1          60           30
 2       9       2016     2          108          54

As you can see there are 3 shift in a day for any toll. So the functioning of the trigger should be :

it should sum up the car_single and car_return, date wise for each toll and should insert into the table traffic_daily as shown in above table.

Please help me out with this. I am just stuck at this problem.

Thanks in advance.

EDIT

This is what i could come up with.

DROP TRIGGER after_insert_shift;
DELIMITER $$

CREATE TRIGGER `test`.`after_insert_shift`
AFTER INSERT ON `test`.`shift`
FOR EACH ROW
BEGIN
INSERT INTO daily
SET
`day` = new.day,
`month` = new.month,
`year`= new.year,
toll_id = new.toll_id,
car_single = SELECT SUM(car_single)
    WHERE daily.day = new.day , daily.month = new.month, daily.year = new.month ,
    daily.toll_id = new.toll_id FROM shift GROUP BY
    shift.day,shift.month,shift.year,shift.toll_id;

car_return = SELECT SUM(car_return) WHERE daily.day = new.day ,
    daily.month = new.month, daily.year = new.month ,
    daily.toll_id = new.toll_id FROM shift GROUP BY
    shift.day,shift.month,shift.year,shift.toll_id;

END$$

DELIMITER ;

First observation, FROM is usually before WHERE, i.e.

car_single = SELECT SUM(car_single) FROM shift WHERE ...

Second observation, INSERTnormally have the form:

INSERT INTO daily (day, month, ... )
VALUES (...)

These are the syntactical errors I find in your trigger. Logically I think there is more to do. Your trigger will insert a new row into daily every time a new row is inserted into shift, from your description that does not sound right. Here's what I would try to do (only a sketch).

CREATE TRIGGER `test`.`after_insert_shift`
AFTER INSERT ON `test`.`shift`
FOR EACH ROW
BEGIN
    UPDATE daily SET (car_single, car_return) = (
        SELECT SUM(car_single), SUM(car_return)
        FROM shift
        WHERE day = new.day
          AND daily.month = new.month
          AND daily.year = new.year
          AND ...
    )
    WHERE daily.month = new.month
      AND daily.year = new.year
      AND ...

    -- Check if number of rows updated = 0, if so insert a new row
    SELECT ROW_COUNT() INTO N;
    IF N = 0 THEN
        INSERT INTO daily (c1, c2, c3, ...)
        SELECT ...

Don't know if that's your problem, but I would start there.


Can We Perform an "Up-To-The-Minute Restore" After Taking Transaction Log Backups? The Setup:

Say we have a Database in Full Recovery Mode that does a nightly Database Backup, Transaction Log Backup, and Transaction Log Shrink.

Now the Transaction Log grows so quickly during the day, so we need to perform a Transaction Log Backup + Transaction Log Shrink every 6 hours to keep it small.

The Question:

Given these sequence of events (specifically the every-six-hours Transaction Log Backup/Shrink), will we still be able to perform an "Up-to-the-Minute" Restore? (That is, using the last DB Backup + Transaction Log to get the most recent data).

Shrinking your transaction log should not be part of your routine. A transaction log backup clears the log file automatically; shrinking it afterward just causes it to have to grow again afterward. Read this for more information.

Yes, you should be able to perform point-in-time restores using your latest full backup and any subsequent transaction log backups. Anything more recent than the latest transaction log backup will require that the server still be operational enough to take a tail-log backup

The critical point is to test it. Backups are not reliable until you know from experience that you can restore the data successfully. This article and the links at the bottom of that article have some good information about SQL backup strategies.


Is it possible to run MongoDB javascript scripts in DBeaver DBeaver's wiki shows a simple query against a Mongo database but what about admin scripts?  It would be great to develop these in DBeaver but I've tried a few permutations to no avail.

Here's an example of a maintenance script:

var cursor = db.album.find({}, {"k": 1 })
var albumKeys = []

while( cursor.hasNext() ){
    albumKeys.push( cursor.next()['k']);
}

cursor = db.song.find({ "alb" : { $not : { $in : albumKeys } } } )
var songsWithMissingAlbum = []

while( cursor.hasNext() ) {
    songsWithMissingAlbum.push(cursor.next())
}

printjson(songsWithMissingAlbum)

subtle differences to the script allows this to run in DBeaver, and (with a small adaptation) as a script for mongo <script.js>

conn = new Mongo();
db = conn.getDB("myDB");

cursor = db.album.find({}, {'k': 1 });

albumKeys = [];

while( cursor.hasNext() ){
    albumKeys.push( cursor.next()['k']);
}

cursor = db.song.find({ 'alb' : { $not : { $in : albumKeys } } } );
songsWithMissingAlbum = [];

while( cursor.hasNext() ) {
    songsWithMissingAlbum.push(cursor.next());
}

return songsWithMissingAlbum; /* printjson(songsWithMissingAlbum) */

Pertinent changes

semicolon line terminations
final return statement (for dbeaver) which must be changed to printjson(..) call in command line script for mongo.
added db and connection setup which works in both.


Errors using expdp with `flashback_time=systimestamp` Oracle 11.2.0.1.0 on Windows 2012 Std:

expdp '/ as sysdba' full=y directory=exp dumpfile=... logfile=... flashback_time=systimestamp

Fails with:

ORA-39001: invalid argument value
ORA-39150: bad flashback time
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-01877: string is too long for internal buffer

Replacing flashback_time=systimestamp with consistent=y which AFAIK is a backwards compatible way to say the same exact thing also fails with the same errors. Removing those options completely, with no other changes, makes it work.

Still, I would like to take a consistent backup in addition to RMAN, which yes I do have configured and working. There are various workarounds suggested on the web. For example using to_timestamp but none of them seem to be working.

My questions are:

Is this a known bug?
Are there any settings that could cause this behavior like system locale?
I am using a slightly modified version of this script and I swear just yesterday it worked fine. I don't recall changing anything, today it doesn't work. Any ideas?
Is there a reliable workaround? I am thinking of using flashback_scn instead but that will require a separate query beforehand.

Edit:

Doubling down on the weirdness here. Each day:

00:00-11:59 - errors as above
12:00-23:59 - everything works fine

I ran the script repeatedly yesterday evening using flashback_time=systimestamp - no errors. Today 7:45am getting errors again from the same script, without modifications.

Just had a look at the documentation.

It says:

  You can specify the time in any format that the
  DBMS_FLASHBACK.ENABLE_AT_TIME procedure accepts

So SYSTIMESTAMP should actually work... Though the docs suggest you need to enclose the parameter in quotation marks. Try:

FLASHBACK_TIME="SYSTIMESTAMP"


command or query to ping SQL server I have 2 windows 7 machines on a very high security domain one acting as a server, the other as a client.

Each box has a SQL Server Express 2005 database and application "A", however I'm only using one of them to store data (the server), the client machine just uses application "A" and a system data source to connect to the server machines database.

My problem is that applciation "A" is acting funny, I'd like to see how long its taking for the data to come from the Server machines database, to the client machine.

I'd normally just ping the server from the client, but due to the restrictive GPO on this high security domain, I cant...

There are obviously firewall exceptions to allow the connectivity to travel from Server -> Client.  Is there a query I can run from the client machine to give me a response time/any other useful data?

I think you could run a query from SQL Server Management Studio from the machine that you can connect from and run the query with client statistics. That won't be exactly the same as a ping but you can see the server execution time (time from request sent from client to time server starts to send response back) and the client processing time (time from when the server starts to send to when the client receives it). That is why it isn't a ping because client execution time includes that network time and the time on the client to process.

I'd run a fairly simple query with a handful or so of rows returned. And maybe a slightly more complex query with more rows. You should also run each of these a few times in a row to eliminate any other slowdowns with the query.

Again, this won't really be a "ping" but it can at least show you some information and you can use this to trend information over time as well by running the same queries and looking at the info.

To look at client statistics - from within the query window of SSMS, go to Query and then select Include Client Statistics. You'll see the details in the Client Statistics tab of the results.

If you could also run SSMS queries on the actual server itself and run the same queries you can look at the difference in these times for the same queries and that should give a rough approximation of the impact of network latency perhaps.


Data usage increases initially when reorganize partition mysql cluster In my MySQL cluster, the data usage of 4 data nodes is approximately 77%. I added a new data node online, and attempted to reorganize partition to redistribute data among all data nodes.

But before the query alter table customer_tbl reorganize partition; is successfully executed, the data usage of existing data nodes started increasing, and reached up to 95% before ERROR customer_tbl is FULL is encountered.

How can I bring a data node online and reorganize table successfully? My table has ~100 million rows.

Why the data usage of existing data nodes increased in the first place and what is the internal working of mysql cluster when it reorganizes partitions?

You're correct that the on-line repartitioning of data temporarily uses extra memory on the data nodes. In most cases the application data is split over many tables and so relatively little memory is needed when repartitioning each table. If the data is in one big table and you don't have the available RAM then there are a number of options...

Perform the data migration yourself in an iterative fashion. e.g.

create cdr2 (....) partition by ...;

loop
{
   insert into cdr2 select * from cdr order by .... LIMIT 1000;
   delete from cdr order by .... LIMIT 1000; } until cdr empty.

Some extra memory will be used, but shouldn't be double.  Best effect would be if the order by .... could follow insert-order in some way.
Backup the data and restore it on another Cluster (with more memory), repartition and then revers the process.
Use mysqldump, empty the table, repartition it and then load the data back in
Convert the table to be disk-based (on-line operation), repartition and then convert it back into an in-memory table
(Temporarily) add extra RAM to the data nodes (this is an on-line operation).

Regards, Andrew.


Is there a way to group often used tables and queries in SSMS? I am not a DBA by training, but due to role shifts I am having to modify and maintain tables and stored procedures fairly often. I would like to visually group tables and stored procedures together in a sort of shortcuts folder. So that when I need to open several related views I don't have to scroll all over the database. Is something like this possible?

Sorry if this is a dumb question. I am just trying to make this part of my job a little less tedious.

I have my db objects in source control with one file per table, view, SP etc. When working on a project I create a working folder and paste into it a shortcut to the relevant objects. Note this is a shortcut, not a copy, so the real files remain in the place source control expects to see them. I can drag into SSMS from the shortcut.


How to apply a theme to SQL Server Management Studio 2008? Has anyone had any success with, for example, applying a 'dark' them to the Text Editor component of of SSMS 2008?

There was an old trick for 2005 which is no longer operational and has not been upgraded.  http://winterdom.com/2007/10/colorschemesinsql2005managementstudio

My eyes will thank you very much!  Cheers.

I used this approach and theme provided here, it worked for me in SSMS 2008:

Beautify Your Management Studio 2005/2008

  To install, simply backup your [HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\FontAndColors{A27B4E24-A735-4D1D-B8E7-9716E1E3D8E0}] registry key, and load in this new set (standard disclaimer about being careful with your registry, make sure you know what you’re doing, don’t blame me, etc). You can then either restart SSMS, or go to the Tools, Options, Environements, Fonts and Colors and simply click OK.

There is also a comment which provides the proper registry location for changing the theme in SSMS 2012, which only seems to differ by the version number.


Left Join 3 tables into ONE in Mysql I have 3 tables: Employees, attendanceIn table, attendanceOut

And My query is

SELECT  employees.eno,employees.ename,employees.dept,attendanceIn.attIn AS attIn,
       attendanceOut.attOutAS attout
    FROM  employees
    LEFT JOIN  attendanceIn  ON employees.eno=attendanceIn.eno
    LEFT JOIN  attendanceOut  ON employees.eno=attendanceOut.eno
    WHERE  DATE(attendanceIn.puchtime) LIKE '2016-07-01%'
      AND  attendanceOut.puchtime LIKE '2016-07-01%'
    GROUP BY  eno
    ORDER BY  eno ASC

Is there any better way to write the query?

Do not use functions on your columns you are comparing against, as that prevents the query from using any indexes you may have.

Start with this:

SELECT employees.eno,employees.ename,employees.dept,attendanceIn.attIn AS attIn,attendanceOut.attOutAS attout
FROM employees
LEFT JOIN attendanceIn ON employees.eno=attendanceIn.eno
LEFT JOIN attendanceOut ON employees.eno=attendanceOut.eno
WHERE attendanceIn.puchtime >= '2016-07-01 00:00:00'
AND attendanceIn.puchtime < '2016-07-02 00:00:00'
AND attendanceOut.puchtime >= '2016-07-01 00:00:00'
AND attendanceOut.puchtime < '2016-07-02 00:00:00'
GROUP BY eno
ORDER BY eno ASC

If you still need performance improvements, provide SHOW CREATE TABLE tablename statements for all your tables in this query.


Select maximum of a count in a grouped clause I have the following tables:
Vehicles(v͟i͟n͟, model,category)
Sales(s͟a͟l͟e͟I͟D͟, staffID,customerID,date)
vehicleSold(saleID,v͟i͟n͟,salePrice)

When I join these tables using:
select YEAR(Sales.saleDate)
     , Vehicles.model
     , count(Vehicles.model) 'Sold'
     , Vehicles.category
  from Vehicles
  JOIN vehicleSold
    on Vehicles.vin = vehicleSold.vin
  JOIN Sales
    on Sales.saleID = vehicleSold.saleID
 group
    by YEAR(Sales.saleDate)
     , Vehicles.model
     , Vehicles.category;

Result is:
+----------------------+-------------+------+----------------+
| YEAR(Sales.saleDate) | model       | Sold | category       |
+----------------------+-------------+------+----------------+
|                 2020 | Altima      |    1 | car            |
|                 2020 | Flying Spur |    2 | car            |
|                 2020 | Lifan E3    |    3 | Electric Moped |
|                 2020 | Ridgeline   |    2 | truck          |
|                 2020 | Shiver      |    4 | motorbike      |
+----------------------+-------------+------+----------------+

Out of this table I want to get the model that was most sold in a category. So, in this case I only want to return a 2020, Flying Spur, car as the only row in category car because it was the most sold in 2020 in its category. I tried using a subquery is MAX(COUNT(*)) but I guess that is not supported in mysql. If anyone could point out my mistake and has any idea how to do this then that would be big help!

You can use a window function like ROW_NUMBER() to generate a unique ID for each record within a grouping (PARTITION) of each value of category like so:
SELECT SalesDateYear, model, Sold, category
FROM
(
    select YEAR(Sales.saleDate) SalesDateYear
         , Vehicles.model
         , count(Vehicles.model) 'Sold'
         , Vehicles.category
         , ROW_NUMBER() OVER (PARTITION BY Vehicles.category ORDER BY COUNT(Vehicles.model) DESC) SortId
      from Vehicles
      JOIN vehicleSold
        on Vehicles.vin = vehicleSold.vin
      JOIN Sales
        on Sales.saleID = vehicleSold.saleID
     group
        by YEAR(Sales.saleDate)
         , Vehicles.model
         , Vehicles.category
) Results
WHERE SortId = 1

(I normally like to use a CTE instead of subquery for these kinds of things, but I don't think your version of MySQL has CTEs, so the above uses a subquery.)


Data Analytics SQL Query method I need every 13th row as new column. I am not sure how this can be done using Pivot or transpose. Number of rows are not fixed.

Please guide me the way.
Thanks

Data is as shown below where every 4 row gets repeated

ID    Value
AA     A
BB     B
CC     C
AA     D
BB     E
CC     F
AA     G
BB     H
CC     I

 it should be as below

ID    Value1  Value2     Value3
AA     A           D           G
BB     B           E           H
CC     C            F              I

You can try PIVOT, something like this:

DECLARE @t TABLE ( ID CHAR(2), Value CHAR(1) )

INSERT INTO @t
SELECT *
FROM (
VALUES
( 'AA', 'A' ),
( 'BB', 'B' ),
( 'CC', 'C' ),
( 'AA', 'D' ),
( 'BB', 'E' ),
( 'CC', 'F' ),
( 'AA', 'G' ),
( 'BB', 'H' ),
( 'CC', 'I' )
)   x ( y, z )

SELECT *
FROM
    (
    SELECT *, ROW_NUMBER() OVER( PARTITION BY ID ORDER BY [VALUE] ) rowId
    FROM @t
    ) x
PIVOT ( MAX( Value ) FOR rowId In ( [1], [2], [3] ) ) pvt

My results:


mongoDB : Restoring space after db.repairDatabase This is my first post in this forum and also I am very new to mongoDB. Before posting I searched for existing posts relating to repairDatabase but everything looks in advanced stage. My query is very basic.

If you look below series of simple operations i.e. insert documents and deleting documents and finding the occupied size of collection.

I am logged into mongo and this is the start of my screen:

2016-12-16T12:17:17.521+0000 I CONTROL  [main] Hotfix KB2731284 or later update is not installed, will zero-out data files
MongoDB shell version: 3.2.9
connecting to: 127.0.0.1:27017/test

> show dbs
local  0.000GB

> db
test

> show collections

>// No collection at this moment (I entered this text here and not from shell screen)

> use mc
switched to db mc

> db.mc.storageSize()
> // Nothing returned

> db.mc.totalSize()
> // Nothing returned

> db.mc.insert({x:1})
WriteResult({ "nInserted" : 1 })

> db.mc.storageSize()
4096

> db.mc.totalSize()
8192

> db.mc.insert({x:2})
WriteResult({ "nInserted" : 1 })

> db.mc.storageSize()
4096 // Same as above

> db.mc.totalSize()
8192 // Same as above

> for(i=1;i<100;i++){db.mc.insert({y:i})} ;
WriteResult({ "nInserted" : 1 })

> db.mc.count()
101

> db.mc.storageSize()
16384 // Size increased

> db.mc.totalSize()
32768 // Size increased

> db.mc.remove({})
WriteResult({ "nRemoved" : 101 })

> db.mc.count()
0

> db.mc.storageSize()
20480 // Size further increased

> db.mc.totalSize()
40960 // Size further increased

> db.repairDatabase()
{ "ok" : 1 }

> db.mc.storageSize()
20480 // Same after repairDatabase

> db.mc.totalSize()
24576 // Here size decreased after repairDatabase

> show dbs
local  0.000GB
mc     0.000GB

If I observe the numbers produced after above operations, I was under assumption that size should go back to empty, though show dbs shows mc as 0.000GB.

I am bit confused about the size before and after the repairDatabase. I was thinking space should get release. I haven't tried compact option

It would be great if anyone monitoring this forum could give their thoughts.

I am bit confused about the size before and after the repairDatabase. I was thinking space should get release. I haven't tried compact option

In general you should consider the  repairDatabase  command a last resort to be used when you actually need to salvage or repair a database and don't have a better data source to copy from (eg. a copy of the data from another replica set member of backup). Historically the repair command was often (ab)used to forcibly rebuild databases using the older MMAP storage engine, but this is no longer recommended practice. The repair operation obtains a global write lock and will block all other operations on your MongoDB server until complete.

With MongoDB 3.2.3+ using WiredTiger (the default storage engine), you can use the compact command to release unused disk space to the operating system for a specific collection. Typically this isn't required unless you find space is not being reused effectively over time.

NOTE: while compacting a collection other operations on the same database will be blocked so this is an activity you would only want to perform during scheduled maintainance periods. Depending on the layout of data on disk, compaction may also not result in significant storage space savings. A better operational approach to reclaim all unused space while avoiding downtime would be to resync a mongod as a member of a replica set.

A few points that should help clarify your observations on size numbers with WiredTiger:

Data in WiredTiger is compressed by default and the representation on disk is different from the representation in memory.
There are several potentially interesting measures of size including data size, storageSize, totalIndexSize, and totalSize (which adds storageSize + totalIndexSize). You can inspect these metrics (and more) via db.collection.stats().
Data written to files on disk is grouped into block allocations (in multiples of 4KB) which is why your single document insertion resulted in a 4KB storageSize and 8KB totalSize (the extra 4KB is the initial allocation for the _id index file). Block allocations are variable sized and managed by the WiredTiger storage engine.
The WiredTiger storage engine does not perform in-place updates on data; it uses an MVCC (MultiVersion Concurrency Control) approach with Snapshots and Checkpoints. This can lead to some apparently contradictory observations where storage space may need to grow to accommodate multiple document versions even though the data size is not increasing. Allocations from older document versions will be marked available for reuse as checkpoints complete, and WiredTiger will try to minimize storage fragmentation with a best-fit allocation from existing storage where possible.

  If I observe the numbers produced after above operations, I was under assumption that size should go back to empty, though show dbs shows mc as 0.000GB.

The show dbs command indicates the actual data size (eg. db.mc.stats().size), which may be much larger than the storageSize (due to compression) or smaller than storageSize (due to preallocated/unused space). Your example is the latter: there is storage space available for reuse.
When you deleted documents you used db.collection.remove({}). This syntax deletes matching documents without removing collection metadata (eg. index definitions) or compacting storage space. If you want to more immediately drop an entire collection and free storage space you should instead use db.collection.drop().

For more information, see:

WiredTiger Storage Engine in the MongoDB documentation
A Technical Introduction to WiredTiger presentation from MongoDB World 2015.