Improve performance on SQL Server Query I'm trying to increase the performance of a query and I'm out of ideas:

The query does this: gets a 'Process' with specific priorities on selection namely:

Picks up a process that has been waiting for over 15 minutes, AND has either no 'sleep until' value set, or the 'sleep until' is less than the time NOW AND ProcessState = 1.

Possible values for ProcessState are 1,2,3,4

The Query I'm trying to optimize is below: it runs very frequently:

SELECT TOP (1) [t0].[ProcessID],
 [t0].[MaterialID],
  [t0].[VideoVersionID],
   [t0].[AudioVersionID],
    [t0].[TXVersionID],
     [t0].[XMLVersionID],
      [t0].[SubtitleVersionID],
       [t0].[Progress],
        [t0].[ProcessStatusDescription],
         [t0].[WorkflowProcessState],
          [t0].[WorkflowProcessSubState],
           [t0].[ProcessState],
            [t0].[ProcessStateDateLastModified],
             [t0].[DateCreated],
              [t0].[DateLastChecked],
               [t0].[SleepUntil],
                [t0].[LongRunningProcessID]
FROM [dbo].[WF_Process] AS [t0]
WHERE ([t0].[ProcessStateDateLastModified] <= '2015-06-18 09:48:31.597')
AND (([t0].[SleepUntil] IS NULL) OR ([t0].[SleepUntil] <= '2015-06-18 10:03:31.597'))
AND ([t0].[ProcessState] = 1)
ORDER BY [t0].[ProcessStateDateLastModified]

The only things that change are the two date time fields, which operate a sliding window from DateTime.Now(). First paramter DateTime.Now()- 15 minutes, second date time paramater is DateTime.Now().

Execution plan here: https://gist.github.com/anonymous/fd56ead25d726d247518
Or execution plan diagram here:

The table looks like this:

CREATE TABLE [dbo].[WF_Process](
    [ProcessID] [bigint] IDENTITY(1,1) NOT NULL,
    [MaterialID] [bigint] NOT NULL,
    [Progress] [decimal](18, 2) NULL,
    [ProcessStatusDescription] [varchar](900) NOT NULL,
    [WorkflowProcessState] [int] NOT NULL,
    [WorkflowProcessSubState] [int] NOT NULL,
    [ProcessState] [int] NOT NULL,
    [ProcessStateDateLastModified] [datetime] NOT NULL,
    [DateCreated] [datetime] NOT NULL,
    [DateLastChecked] [datetime] NULL,
    [SleepUntil] [datetime] NULL,
    [LongRunningProcessID] [varchar](65) NULL,
 CONSTRAINT [PK_Process] PRIMARY KEY CLUSTERED
(
    [ProcessID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[WF_Process] SET (LOCK_ESCALATION = DISABLE)
GO

ALTER TABLE [dbo].[WF_Process]  WITH CHECK ADD  CONSTRAINT [FK_Process_GB_Material] FOREIGN KEY([MaterialID])
REFERENCES [dbo].[GB_Material] ([MaterialID])
GO

ALTER TABLE [dbo].[WF_Process] CHECK CONSTRAINT [FK_Process_GB_Material]
GO

I have a combined index on all the columns which are within the query: ( and I have other indexes on the table too )

There are 187,851 rows in the database with a data space of 50MB. It has a total Index space of 483MB.

The Query IO Statistics look like this:

(1 row(s) affected)
Table 'WF_Process'. Scan count 1, logical reads 27566, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

The client statistics looks like this:

The DB server is meaty. DL360 gen8 ( 32 GB ram, 30+ cores etc ).

This query is very heavily used, and so I'm trying to optimize it as much as I can, any help/information to speed up this query would be appreciated. Anything over 500 ms isn't really acceptable.

EDIT:

Did as Zohar said and I now have [ProcessStateDateLastModified] as Key index column, and the other two columns [SleepUntil] and [ProcessState] as included columns. This reduced the number of logical rads ( expected ), but did not reduce the overall timings of the query:

(1 row(s) affected)
Table 'WF_Process'. Scan count 1, logical reads 741, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Execution plan:

Client stats:

I would suggest to try an index like this:

CREATE NONCLUSTERED INDEX NCI_ProcessState_ProcessStateDateLastModified_SleepUntil
ON [TABLE](ProcessState,ProcessStateDateLastModified,SleepUntil)

This way you can filter sharp on the state as all others will result in bigger result sets due to the <= filter.

You can check the index usage easily with this query:

SELECT obj.*,usage.*
FROM sys.dm_db_index_usage_stats AS usage
INNER JOIN sys.indexes as obj
        ON usage.object_id = obj.object_id
WHERE database_id = db_id()

Just filter it on your index and take a look a the search and update columns.


Is 1.6 seconds a typical time to fetch nearly 3.7 million rows from a table? I am trying to work out if we need to upgrade our server hardware or need to configure the MySQL settings to make things faster.

A simple SELECT * From table took 1.6 seconds from the command line. However mysql-slow.log shows that it takes 7 seconds when one of our processes from another server executes it. This seems very slow to me. The table contains three INT columns only.

It's an Ubuntu Rackspace cloud server. We have 4GB RAM and only 1.2GB is used up.

So do the numbers look about right or is there something wrong?

Additional information

I have added the EXPLAIN EXTENDED SELECT * From table here:

Both 1.6s and 7s seem reasonable.  Some issues that lead to variations:

Caching (Was I/O needed, or was everything sitting in RAM -- a factor of 10 variation is possible)
Distance to other server (Ping time)
Bandwidth to other server (Mb/sec)
Client's ability to absorb that much data that fast
Whether client is receiving it in chunks or all at once

But my question to you is -- what can you do with 3.7M rows all at once?  That is, this does not seem like a reasonable benchmark or whatever.

Back to the "caching".  Try this:

restart mysqld
run your test
run your test again -- likely to run faster.

Main cache:  for a 4GB server and InnoDB tables, innodb_buffer_pool_size should be something like 2G.


Upgrade certification from MCSA sql server 2012/2014 to MCSE SQL 2016 All,

I would like to know what path should I chose in order to achieve MCSE for SQL 2016. I already completed MCSA certification for SQL 2012/2014 and would like to upgrade it to MCSE SQL 2016/2017. Kindly, let me know what is the best approach for this. Is there any direct path or do I need to take n number of exams to get that certification. Also, would like to know what resources or links you'll can share that can help me possibly get through to that certification...most likely in first attempt. I only would choose one exam but it should be more focused towards honing my existing DBA skills.

https://buildazure.com/2017/02/14/mcse-data-management-and-analytics-certification/

Thanks

In order to move from MCSA to MCSE you would need to take one of the following exams (As of Jan-2017) which one you take is entirely up to you - though if you want to use SQL on Azure I'd recommend taking one of the cloud-based exams:

Exam 70-473: Designing and Implementing Cloud Data Platform Solutions
Exam 70-475: Designing and Implementing Big Data Analytics Solutions
Exam 70-464: Developing Microsoft SQL Server Databases
Exam 70-465: Designing Database Solutions for Microsoft SQL Server
Exam 70-466: Implementing Data Models and Reports with Microsoft SQL Server
Exam 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server
Exam 70-762: Developing SQL Databases
Exam 70-767: Implementing a Data Warehouse using SQL
Exam 70-768: Developing SQL Data Models
Exam 70-773: Analyzing Big Data with Microsoft R
Exam 70-774: Perform Cloud Data Science with Azure Machine Learning
Exam 70-775: Perform Data Engineering on Microsoft Azure HDInsight

You can find more information on the Microsoft Learning page for that exam track which also has recommended learning material and optional courses.

Regardless of which exam or training method you decide to take, there is no substitute for experience and you will find it much easier to pass the exams if you have hands-on experience with the product and specialization that you choose.


sp_executesql changes non standard characters to? Am trying to use sp_executesql to run commands but I've come across an issue where in non-english characters (aka Chinese & Japanese) are changed to ??? when ran.

sql server 2008R2 & 2012

Example Code

DECLARE @text nvarchar(30) = N'select ''J こんにちは C 你好'''
SELECT @text
SELECT N'J こんにちは C 你好'
exec sp_executesql @text
exec sp_executesql  N'select ''J こんにちは C 你好'''

J= Japanese 'Hi' C = Chinese 'Hi' as processed by google translate for testing purposes

Output

------------------------------
select 'J こんにちは C 你好'

------------
J こんにちは C 你好

------------
J ????? C ??

------------
J ????? C ??

As you can see when ran naturally or the code is selected the output is returned as expected, however once it is passed through sp_executesql it returns as ? characters. Its worth noting if you attempt to send a varchar instead of an nvarchar it errors out so it defiantly uses unicode.

Have tried a few different collations for the input but that doesn't seem to change anything

You should pass your unicode characters within SELECT as UNICODE using N:

DECLARE @text nvarchar(30) = N'select N''J こんにちは C 你好'''
SELECT @text
SELECT N'J こんにちは C 你好'
exec sp_executesql @text
exec sp_executesql  N'select N''J こんにちは C 你好'''


Can you rename replication jobs? I have snapshot replication running on three different servers, two publishers and one subscriber.  The replication processes have associated jobs (either building the snap shot or grabbing the snap shot, I believe) that have ugly system-generated names.  Is it safe to rename these something that's readable and understandable?

Yes you can rename the jobs.  They will still function normally because they are identified by job_id for system purposes.

You can find more info about how it is uses in the MSdistribution_agents table.  More info here: http://msdn.microsoft.com/en-us/library/ms174399(v=sql.90).aspx


eCommerce orders table. Save prices, or use an audit/history table? Im designing my first eCommerce schema. I've been reading around the subject for a little while, and am a bit confused about the relationship between an order_line_item and a product

A product can been purchased. It has various details, but the most important is unit_price.

An order_line_item has a foreign key to the product_id purchased, the quantity purchased and the unit_price at the point in time the customer purchased the product.

Most of what I have read says that the unit_price on the order_line_item should be explicitly added (i.e. not referenced through the product_id). Makes sense, as the store could change the price in the future which would mess up order reports, tracking, integrity etc.

The thing I don't understand, is why directly save the unit_price value to the order_line_item?

Would it not be better to create an audit/history table that documents the unit_price change of a product?

When an order_line_item is created, the foreign key of the product_audit table is added and the price can be retrieved (by reference) from there.

There seem to me to be a lot of positives to using this approach (less duplication of data, price change history etc), so why isn't it more frequently used? I haven't come across an example of an eCommerce schema that uses this approach, am i missing something?

UDPATE: It seems like my question relates to Slowly Changing Dimension. I'm still confused though as Slowly Changing Dimension relates to data warehouse and OLAPs. So can Slowy Changing Dimension types be applied to my main business transaction process database (OLTP)? I wondering if I'm mixing a lot of concepts up, Would greatly appreciate some guidance.

As you've identified, storing the price on the order makes the technical implementation easier. There are a number of business reasons why this may be beneficial though.

In addition to web transactions, many businesses support sales through other channels, e.g.:

Over the phone
Sales agents "on the road"
At a physical location (e.g. shop, office)

In these cases the order may be entered into the system at some time after the transaction took place. In these circumstances it can be difficult to impossible to correctly identify which historical price record should be used - storing the unit price directly on the order is the only feasible option.

Multiple channels often bring another challenge - different prices for the same product.  Surcharges for phone orders are common - and some customers may negotiate themselves a discount. You may be able to represent all possible prices for all channels in your product schema, but incorporating this into your order tables can become (very) complex.

Anywhere that negotiation is allowed it becomes very difficult to link price history to the order price agreed (unlesss agents have very narrow negotiation limits). You need to store the price on the order itself.

Even if you only support web transactions and having a relatively simple pricing structure, there's still an interesting problem to overcome - how should price increases be handled for in flight transactions? Does the business insist that the customer must pay increases or do they honour the original price (when the product was added to the basket)? If it's the latter it the technical implementation is complicated - you need to find a way to ensure you're maintaining the price version in the session correctly.

Finally, many businesses are starting to use highly dynamic pricing. There may not be one fixed price for a given product - it is always calculated at runtime based on factors such as time of day, demand for the product and so on. In these cases the price may not be stored against the product in the first place!


When to create Indexes? I have a stored procedure that takes around 2 minutes to execute. The execution plan suggested me to create a non clustered index on a table (which is a high traffic table with millions of records in it, plus it gets a constant stream of data every single second).

Please help me decide on whether I should create an index on that table or not.

Note: I tried creating a non clustered index on that table (on development server) and the time was reduced to 40 seconds from 2 minutes.

You haven't really given enough information about the original execution plan, what it changed to or what columns you put in the new NCI.  It is possible that the query performance can be improved even further.  The cost of the new index is the initial build, the additional processing of entries on INSERT and UPDATE and potentially the fragmentation maintenance.  Also, it's possible it may require some statistics maintenance if the data profile is likely to change.

It is possible that the index could improve overall system performance by reducing read access to the clustered index, but with a table changing so rapidly, the cost of maintaining the index might be very high.  On the other hand, you have to look at the overall requirements.  If this query only runs once a month, perhaps it is too high to bear.  If it runs 10 times a hours, then running it in a few seconds, with inserts slightly slower might be a small price to pay.

A thing to look out for would be index fragmentation if there are a lot of updates because the NCI key is not static.


Question regarding database design I want to store data of customers and employees. What I am having trouble deciding is what is the correct thing to do:

a. Option a: Two tables - Customer and Employee
b. Option b: Three tables - Person, Customer, and Employee

So for Option b, what I had in mind was that common properties of customers and employees like FirstName, LastName, etc. are on Person then related to Customer or Employee if he/she is a customer or employee (or both). Then Customer and Employee table would only have fields specific to them. I know I would need to join to query with Option b but I don't think that is such a big deal.

I'm not sure if Option b is appropriate or I'm mixing OOP concepts with designing so I wanted to ask in the perspective of someone who thinks only for the database in the context of designing, not specifically for performance.

I'd go b.  A person is a person.  Record that then layer over what role they take in your organisation.

Consider that an employee might also be a customer.  A person might take the role of other participants that you have not yet thought of or chosen to model.

A customer can also be a business which would have a different shape.

Lots of potential reasons to keep them separate.


How to determine the sweet spot between pool size and database connections in PostgreSQL We are having trouble handling the traffic during peak hours to our database server. We're looking into improving the hardware (see this question about that side of things) but we also want to work on the pooling configuration and server tuning.

The application we
are working on is a turn-based multiplayer game for smartphones, where the backend consists of Rails with unicorn and PostgreSQL 9.1 as
the database. We currently have 600 000 registered users and since the game state is stored in the database several
thousands writes are made every couple of seconds. We have analyzed the
log files from PostgreSQL using PgBadger and during the critical hours we get a lot of

FATAL: remaining connection slots are reserved for non-replication superuser connections

The naive solution to counter this problem would be to increase max_connections (which currently is 100) in
postgresql.conf . I have read http://wiki.postgresql.org/wiki/Number_Of_Database_Connections which indicates
that this might not be the right thing to do. In the aforementioned article it's referred to finding the sweet spot between
max_connections and pool size.

What can be done in order to find this sweet spot? Are there any good tools to measure the I/O performance for different
values of max_connections and pool size?

Our current setup is 4 game servers, each having 16 unicorn workers and a pool size of 5.

Here are the non-default postgres-settings we are using:

version                      | PostgreSQL 9.1.5 on x86_64-unknown-linux-gnu,compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
checkpoint_completion_target | 0.9
checkpoint_segments          | 60
checkpoint_timeout           | 6min
client_encoding              | UTF8
effective_cache_size         | 2GB
lc_collate                   | en_US.UTF-8
lc_ctype                     | en_US.UTF-8
log_destination              | csvlog
log_directory                | pg_log
log_filename                 | postgresql-%Y-%m-%d_%H%M%S.log
log_line_prefix              | %t
log_min_duration_statement   | 200ms
log_rotation_age             | 1d
log_rotation_size            | 10MB
logging_collector            | on
max_connections              | 100
max_stack_depth              | 2MB
server_encoding              | UTF8
shared_buffers               | 1GB
ssl                          | on
TimeZone                     | localtime
wal_buffers                  | 16MB
work_mem                     | 8MB

The short answer here is "trial and error guided by monitoring and performance metrics".

There are some general rules of thumb that should help you find the vague area you should start in, but they're very general. The broad guidelines "number of CPUs plus number of independent has disks" is often cited, but it's only an incredibly coarse starting point.

What you really need to do is get robust performance metrics in place for your application. Start recording stats.

There isn't much in the way of integrated tooling for this. There are things like the nagios check_postgres script, Cacti system performance counter logging, the PostgreSQL statistics collector, etc ... but there isn't much that puts it all together. Sadly, you'll have to do that bit yourself. For the PostgreSQL side, see monitoring in the PostgreSQL manual. Some third party options exist, like EnterpriseDB's Postgres Enterprise Monitor.

For the application-level metrics mentioned here you will want to record them in shared data structures or in an external non-durable DB like Redis and aggregate them either as you record them or before you write them to your PostgreSQL DB. Trying to log directly to Pg will distort your measurements with the overhead created by recording the measurements and make the problem worse.

The simplest option is probably a singleton in each app server that you use to record application stats. You probably want to keep a constantly updating min, max, n, total and mean; that way you don't have to store each stat point, just the aggregates. This singleton can write its aggregate stats to Pg every x minutes, a low enough rate that the performance impact will be minimal.

Start with:

What's the request latency? In other words, how long does the app take from getting a request from the client until it responds to the client. Record this in aggregate over a time period, rather than as individual records. Group it by request type; say, by page.
What's the database access delay for each query or query type the app executes? How long does it take from asking the DB for information / storing information until it's done and can move on to the next task? Again, aggregate these stats in the application and only write the aggregate info to the DB.
What's your throughput like? In any given x minutes, how many queries of each major class your app executes get serviced by the DB?
For that same time range of x minutes, how many client request were there?
Sampling every few seconds and aggregating over the same x minute windows in the DB, how many DB connections were there? How many of them were idle? How many were active? In inserts? Updates? selects? deletes? How many transactions were there over that period? See the statistics collector documentation
Again sampling and aggregating over the same time interval, what were the host system's performance metrics like? How many read and how many write disk IOs/second? Megabytes per second of disk reads and writes? CPU utilisation? Load average? RAM use?

You can now start learning about your app's performance by correlating the data, graphing it, etc. You'll start to see patterns, start to find bottlenecks.

You might learn that your system is bottle-necked on INSERT and UPDATEs at high transaction rates, despite quite low disk I/O in megabytes per second. This would be a hint that you need to improve your disk flush performance with a battery backed write-back caching RAID controller or some high-quality power-protected SSDs. You could also use synchronous_commit = off if it's OK to lose a few transactions on server crash , and/or a commit_delay, to take some of the syncing load off.

When you graph your transactions per second against the number of concurrent connections and correct for the varying request rate the application is seeing, you'll be able to get a better idea of where your throughput sweet spot is.

If you don't have fast flushing storage (BBU RAID or fast durable SSDs) you won't want more than a fairly small number of actively writing connections, maybe at most 2x the number of disks you have, probably fewer depending on RAID arrangement, disk performance, etc. In this case it isn't even worth trial and error; just upgrade your storage subsystem to one with fast disk flushes.

See pg_test_fsync for a tool that'll help you determine if this might be a problem for you. Most PostgreSQL packages install this tool as part of contrib, so you shouldn't need to compile it. If you get less than a couple of thousand ops/second in pg_test_fsync you urgently need to upgrade your storage system. My SSD-equipped laptop gets 5000-7000. My workstation at work with a 4-disk RAID 10 array of 7200rpm SATA disks and write-through (non-write-caching) gets about 80 ops/second in f_datasync, down to 20 ops/second for fsync(); it's hundreds of times slower. Compare: laptop with ssd vs workstation with write-through (non-write-caching) RAID 10. This laptop's SSD is cheap and I don't necessarily trust it to flush its write cache on power-loss; I keep good backups and wouldn't use it for data I care about. Good quality SSDs perform just as well if not better and are write-durable.

In the case of your application, I strongly advise you to look into:

A good storage subsystem with fast flushes. I cannot stress this enough. Good quality power-fail-safe SSDs and/or a RAID controller with power-protected write-back cache.
Using UNLOGGED tables for data you can afford to lose. Periodically aggregate it into logged tables. For example, keep games-in-progress in unlogged tables, and write the scores to ordinary durable tables.
Using a commit_delay (less useful with fast-flushing storage - hint)
Turning off synchronous_commit for transactions you can afford to lose (less useful with fast-flushing storage - hint hint)
Partitioning tables, especially tables where data "ages out" and is cleaned up. Instead of deleting from a partitioned table, drop a partition.
Partial indexes
Reducing the number of indexes you create. Every index has a write cost.
Batching work into bigger transactions
Using read-only hot standby replicas to take the read load off the main DB
Using a caching layer like memcached or redis for data that changes less often or can afford to be stale. You can use LISTEN and NOTIFY to perform cache invalidation using triggers on PostgreSQL tables.

If in doubt: http://www.postgresql.org/support/professional_support/


What does "_localserver_view" mean after an MSDB Database table What is equal to the database table
[msdb]. [dbo]. [sysschedules_localserver_view], which contains the suffix _localserver_view?

I am having a permissions problem with a [msdb].[Dbo].[Sp_help_schedule], and I noticed the user I chose to run this stored procedure, select access in the table [msdb].[dbo].[sysbobschedules] more than when accessing the table [msdb]. [dbo].[sysschedules_localserver_view] (which is executed by an internal command from [msdb].[dbo].[sp_help_schedule]) is nothing.

Also note that this table does not exist in the msdb database, or in the table, view, or stored procedure folders.

Why would you want to know what this _localserver_view extension changes in the permissions or in the table view.

I'm not sure I understood your question but I'll try to explain the differences.

When you have full permissions in msdb
you'll get the same rows when

executing msdb.dbo.sp_help_schedule
selecting from msdb.dbo.sysschedules_localserver_view
selecting from msdb.dbo.sysschedules

This is because msdb.dbo.sp_help_schedule selects from msdb.dbo.sysschedules_localserver_view which selects from msdb.dbo.sysschedules.

You'll get less rows when select from msdb.dbo.sysjobschedules mentioned in your question, this is because msdb.dbo.sysjobschedules

  Contains schedule information for jobs to be executed by SQL Server
  Agent.

While msdb.dbo.sysschedules shows also schedules currently not related to any existing job, for example is contains schedules for Data Collector that may not be configured.

When the user has only select permission in msdb

he will not be able to execute msdb.dbo.sp_help_schedule, he'll get the full result when selecting from tables msdb.dbo.sysschedules and msdb.dbo.sysjobschedules, but he'll get no rows when selecting from the msdb.dbo.sysschedules_localserver_view view.

This is because this view contains the where clause:

WHERE (svr.master_server = 0)
  AND ( (sched.owner_sid = SUSER_SID())
        OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
      OR (ISNULL(IS_MEMBER(N'SQLAgentReaderRole'), 0) = 1)
      )

This means that to get any row from this view one of the following should be true for the current user:

user is owner of the job (and only the schedules related to owned jobs will
be returned)
corresponding login is a member of sysadmin fixed server role (all rows will be returned)
user is a member of SQLAgentReaderRole database role (all rows will be
returned)


using PERFORM to insert a string of SELECT statement into a temp table I am trying to insert data into a temp_table and then truncating the table after analyzing the result.

Here is my code:

CREATE OR REPLACE FUNCTION validation()
  RETURNS text AS $$
DECLARE counter INTEGER;
DECLARE minsid INTEGER;
DECLARE maxsid INTEGER;
DECLARE rec RECORD;
DECLARE stmt varchar;
BEGIN
  SELECT MIN(sid) INTO minsid FROM staging.validation;
  SELECT MAX(sid) INTO maxsid FROM staging.validation;

  CREATE TEMPORARY TABLE temp_table (col1 TEXT, col2 INTEGER, col3 BOOLEAN) ON COMMIT DROP;

  FOR counter IN minsid..maxsid LOOP
    RAISE NOTICE 'Counter: %', counter;
    SELECT sql INTO stmt FROM staging.validation WHERE sid = counter;

    RAISE NOTICE 'sql: %', stmt;

    PERFORM 'INSERT INTO temp_table (col1, col2, col3) ' || stmt;

    IF temp_table.col3 = false THEN
      RAISE NOTICE 'there is a false value';
    END IF;

  END LOOP;
END; $$
LANGUAGE plpgsql;

Whenever I run this function
SELECT * FROM validation();
I get an error:

ERROR: missing FROM-clause entry for table "temp_table" Where: PL/pgSQL function validation() line 21 at IF

Here is how my staging.validation table looks -

https://docs.google.com/spreadsheets/d/1bXO9gqFS-GtcC1qJtgNbFkR6ygOuPtR_RZoU7VNhgrI/edit?usp=sharing

The issue with your SQL is with the type (or return type).

In

SELECT * FROM validation();

You are using validation() as if the returned value from the function were a table. But you defined the function to return a single value

CREATE OR REPLACE FUNCTION validation()
  RETURNS text AS $$

So, the said function can't be used in the FROM clause.

If you are trying to use the side-effects of the function (PERFORM 'INSERT INTO ...), then you can use it in the SELECT list directly, e.g.

SELECT validation();

If you really intend to use the returned relation, then you need to make validation a Set Returning Function (SRF).

--EDIT--

In addition to the return type issue, another error in the IF statement is causing the actual error message (rather than the error in return type, which needs to be fixed as well).

If I understand what you are looking for (e.g. to test whether col3 = false for some rows in temp_table), one way that I think can achieve it, is to use a query, e.g.:

IF (SELECT 1 FROM temp_table WHERE  col3 = false) = 1 THEN
  RAISE NOTICE 'there is a false value';
END IF;

The subquery (SELECT 1 FROM temp_table WHERE  col3 = false) returns 1 if FALSE exists, and NULL otherwise.

I don't think it's valid to use temp_table.col3 directly.


Move an Oracle database by moving the raw data files I have a large Oracle database (~300 GB) and I need to move a copy of the database to a new environment in a short period of time. I was wondering if there is a way to just pick up the raw files Oracle manages and move them to the new box and bypass doing a dump and then another import?

I was looking at http://docs.oracle.com/cd/B28359_01/server.111/b31107/asmcon.htm but not sure if this is what I need. Where are the raw data files stored?

This is largely dependent on the differences is Oracle version, and OS version. IF both are at the same version, and Oracle is patched in the same manor then the answer becomes a factor of:

IF all of the database related files will reside in the same place THEN

...do a cold clean shutdown of the database

...copy the datafiles, temp, undo, redo logs, control files AND instance related files (initINST.ora, spfileINST.ora, orapwINST, etc) in $ORACLE_HOME/dbs

...setup the instance in the oratab file (or, if it is Windows, use ORADIM to create the instance related windows service)

...startup normally

IF the file structure will change, but the instance name will not THEN

...do a cold clean shutdown of the database

...copy the datafiles, undo, redo logs AND instance related files (initINST.ora, spfileINST.ora, orapwINST, etc) in $ORACLE_HOME/dbs

...do not copy controlfiles

...setup the instance in the oratab file (or, if it is Windows, use ORADIM to create the instance related windows service)

...startup nomount

...recreate the controlfiles using the "create controlfile reuse ~ noresetlogs" command

...add back in TEMP tablespace

IF the file structure will change AND the instance name will as well THEN

...do a cold clean shutdown of the database

...copy the datafiles, undo, redo logs AND instance related files (initINST.ora, spfileINST.ora, orapwINST, etc) in $ORACLE_HOME/dbs

...do not copy controlfiles, temp or redo logs

...setup the instance in the oratab file (or, if it is Windows, use ORADIM to create the instance related windows service)

...startup nomount

...recreate the controlfiles using the "create controlfile set ~ resetlogs" command

...add back in TEMP tablespace

This is the typical, old-style clone technique. Normally, I am a fan of the RMAN DUPLICATE, but if this is a one time thing, I probably wouldn't worry about it. If this isn't 11g, you would be taking a separate backup, transporting that for use by rman. If it is 11g, duplicate can use the "from active database" clause, so long as the instances can reach each other on the network.


Is it useful to index a column if it is already the first column in a composite index? Suppose I have this table (simplified from my project):

create table BillTax
(
    BillID int not null constraint FK_BillTax_Bill foreign key references Bill(BillID),
    TaxCode varchar(20) not null,
    constraint PK_BillTax primary key clustered (BillID, TaxCode),
    TaxRate decimal (10, 9) not null,
    TaxAmount decimal (12, 4) not null,
);

As you can see, the primary key is a composite of the BillID and TaxCode columns and uses a clustered index for speed. All queries against the table will seek on BillID, but some queries will seek on both BillID and TaxCode. Also, all queries will include other columns (e.g. TaxRate, TaxAmount) as result data. Given these queries, is it useful to create a separate (non-clustered) index on just the BillID column?

Cons:

Waste of space
Added complexity
Decreased performance on inserts

Pros:

Index on a single column should be pretty small
Query optimizer should be able to pick the best query either way (but we know the query optimizer is not always as smart as we'd want)

My understanding is that if I query this table on just the BillID column, the query optimizer will still use the clustered index because that index has the BillID column as its first column. And it will be quite fast because even though the index will be a little bigger as it includes the TaxCode column, being clustered will make up for that. On the other hand, one could argue that a single non-clustered index could be faster, if the data coming back is not that heavy. See Nonclustered index is faster than clustered index? for a related question.

I know, I know, try both and measure it yourself. I guess I'm just wondering if there is a major theoretical consideration I'm missing that renders one approach or the other moot.

Note: I found a possible duplicate here, but it deals specifically with PostgreSQL, and the accepted answer relies on some DBMS-specific implementation details like data type storage sizes:
Is a composite index also good for queries on the first field? (sic).

Given these queries, is it useful to create a separate (non-clustered) index on just the BillID column?

First, note that an index on BillId will include TaxCode.  The clustered index key is the row loacator for this table, so every index must contain it.  And for non-unique indexes, the clustered index key columns are added as index key columns to make the index physically unique.

It just won't include the rest of the columns on the leaf pages.  Such an index is sometimes useful, especially if you have a wide table and no other indexes.

For instance a COUNT(*) or paging query could use this narrow unique index instead of having to scan the whole clustered index.

But for the question as stated, no, maintaining a second copy of the key index without the two non-key columns is unlikely to be useful.


How long takes populating catalog in SQL Server 2008 R2 when update a row? I have installed SQL Server 2008 R2 with FullText search feature installed.

I created a catalog and fulltext index on a table (let's say Table1). I used the following commands (sql queries):

USE MyDatabase
EXEC sp_fulltext_database 'enable'

CREATE FULLTEXT CATALOG [MY_CATALOG_] WITH ACCENT_SENSITIVITY = ON

CREATE FULLTEXT INDEX ON Table1 (Column1)
    KEY INDEX PK_KEY_TABLE1_
    ON MY_CATALOG_ WITH CHANGE_TRACKING AUTO;

WITH CHANGE_TRACKING AUTO; means auto-populate, right ?

When I use a stored procedure to update a row from Table1 and I use search query like:

SELECT * FROM Table1 WHERE CONTAINS(Column1,'modifiedName')

but no results were found.

I've checked the populate status is 0 (idle). I have to go in Sql Management studio and choose rebuild catalog. And then works (mean I found results what I expected)

My question: how long takes populating catalog in sql server ? My table (Table1) has 2000 rows.

I think ther is no answer. IIRC the change gets logged and full text index processes them async one by one - so the time is "random" - i.e. depending on the amount of changes, the size of the queue.

If you ahve things properly configured it should show up "after some time", but that should - unless you do a TON of updates in one moment - be relatively fast.

This is in line with the business approach (text repositories) where a time delay can be accepted.


Error code is 0x80131500 when applying SP4 to SQL Server 2012 This is an open request for any thoughts, advice or guidance about an issue I have while attempting to apply SP4 to a SQL Server 2012 installation.

The server is currently the secondary node of a two-node AG and has been running well for several years. The SP file is on a local drive and I am running it from the RDP desktop. The error code is 0x80131500.

Though the things I have found so far about that error code all relate to Windows Store on Windows 10.

When I look in the setup logs for the SP I see a "Network Path Not found" error and it seems to be looking for the Primary node of this AG.

Any assistance is appreciated.

Mystery solved. I have found out that the network segment between the nodes of the AG had File and Printer Sharing disabled. Only Sql traffic was allowed.
Re-enabling that on both nodes of the AG for the time it takes to apply the patches on both nodes allowed the needed communication between them.

Just another reminder that you should always check the simple things first when you have a problem.

Have a great day
Richard


How to reference a table from another schema inside a package? I am using TOAD to create a package in a Schema that I have am owner. My package uses tables of another schema.

The package has a procedure that uses a cursor. When I create the package I get compilation errors:

  Body  509 30  PL/SQL: ORA-00904: : invalid identifier

The error points to a table that the select of the Cursor is using and that it is located in the other Schema.

If I run the query directly in TOAD it works perfectly, I am new in Oracle and I don't understand why I get this error. Why am I getting this error?

From section "6 Coding PL/SQL Subprograms and Packages" in the "Oracle Database Advanced Application Developer's Guide, 11g Release 2 (11.2), E41502-05"

  To create without errors (to compile the subprogram or package successfully) requires these additional privileges:
  - The owner of the subprogram or package must be explicitly granted the necessary object privileges for all objects referenced within the body of the code.
  - The owner cannot obtain required privileges through roles.

You must have granted select permission on this table to the schema that owns the package.
It is important that this permissions are granted directly to the package owner and not
indirect using a role. Using a role is sufficient to select the table directly by Toad
but it is not sufficient if you want to select the table in a package.


ERROR: Too many connectioons I'm new to mysql. So, sorry if you find it an easy/silly question.
I have a quick question since I encountered a strange scenario. I want to make sure does it actually happens  or did I do the right thing.
I was taking backup of a table from a prod replica and what I found that  after some time it locked production master database table. We started getting 'Too many connections' error on live. I checked mysql documentation for this and found: That you should stop replication before starting the dump from replica to ensure that the dump contains the consistent set of data.
https://dev.mysql.com/doc/mysql-replication-excerpt/5.7/en/replication-solutions-backups-mysqldump.html
But, One thing I'm unable to get that yeah..from documentation I can conclude it's good practice to stop. But, nowhere it's being mentioned in the way that 'you have to stop the replication otherwise - it can make your master unreachable. Did anyone face the same issue?
Also, one more thing - I couldn't get- dump operation always make schema level lock -not to lock the whole table. So, how could this happen.
Thanks for any advice/suggestion/reason in advance!

Official documentation will rarely list all the things that could happen if advice or practices are not followed as the list would very quickly become far too long, unwieldy, and outdated. Instead it is much easier for the group — or individual — to state the recommended practice and move on, leaving all the different possible situations, conditions, and failures to sites like this one. By sharing the issue you encountered, other people can learn from your experience and hopefully resolve their issue.
With regards to your second question:

I couldn't get dump operation always make schema level lock -not to lock the whole table. So, how could this happen?

I am not quite sure what you’re asking but, when a backup of a table is being made, the table must be locked to ensure consistency. If records are modified or deleted during a backup process, then the backup may be wrong. If it is wrong, then it is unreliable. If it is unreliable then it cannot be trusted. If one backup cannot be trusted then …
You get the picture.
Generally what I will do with MySQL backups is pause replication, wait for any in-progress transactions to complete, lock the entire database, make a snapshot, unlock the database, then resume replication. This ensures the entire backup is accurate to a specific point in time.


Fast query in MySQL with table of 1B+ rows Consider a MySQL table with the following columns:

source_id integer
timestep integer
position integer
value float

This table contains slightly more than 1,000,000,000 rows. The data is read-only and serves as source for certain analyses.

What can be done to speed up the queries?

I've created indices on all four columns. What else can be done?
The DB is currently on a MBP mid 2015, but it is going to be migrated to a workstation. Any suggestions on RAM-wise and / or SSD-wise?

Consider the following view on the previous table and a query on the view:

create view V(id, block, value) as
select source_id, round(timestep/25), avg(value)
from T
group by source_id, round(timestep/25);

select * from V where id=1;

The last query is taking ages to complete.

Version is 5.7.15

I would suggest that the round(timestep/25) is what is causing the slowdown in the group by part of the view.

Could you add a further field (timestep_aggregate) to the table containing the results of the round function? Using a trigger to populate the field on insert / update.

Or if using MySQL 5.7.5 or above, you could use a generated column which would do this for you - see Generated Columns in MySQL 5.7.5

If so, you could then create a combined index on source_id and timestep_aggregate, then use the new field instead of the function within your view. This would be much more performant.


pgAdmin 4: How to establish a connection through an SSH tunnel/interface in Windows I installed the latest pgAdmin 4 (v2.1) but I found out that (related thread), for the moment, the only way to establish a connection through an SSH tunneling is using another outside process, an example in the pgAdmin's forum says:

I setup a tunnel as follows:
ssh -f dpage(at)hostname(dot)enterprisedb(dot)com -L 5999: hostname.enterprisedb.com:5432 -N

(...) I was running on Mac, but the same procedure should work on Linux. On
Windows, there's a tunnelling app in Putty iirc, but I don't know how
to use it offhand.

I have found a simple PuTTY tutorial to establish a connection using an SSH Tunnel.
The connection is established but it seems, when I try to connect through pgAdmin it fails or is refused (using 127.0.0.1:3306 or 127.0.0.1:5432 as the tunnel's destination). Why?

So in this step of that PuTTY SSH tunnel tutorial:

I used instead 5432 as the Source port and my.tunnel.domain:5432 as the Destination.

Then in the connection details:

In the Host Name I placed the server to connect to (the one with the DB: my.db.domain), but the login credentials relate to the tunnel's my.tunnel.domain (e.g. Connection > Data has the tunnel's domain user and Connection > SSH > Auth has the tunnel's domain private key).

I opened this connection (it opens up a window with a terminal, if successful) and finally in the pgAdmin 4 (v2.1) Create Server... window, I just added localhost as the Host name/address and 5432 as the Port, with the credentials relating to the my.db.domain:

The DB connection is established and works normally if the PuTTY window with the terminal is active.

Another and more verbose tutorial on how to set a Putty SSH tunnel can be found here.


Column vs Field: have I been using these terms incorrectly? I feel kind of embarrassed here, I've always used the terms "column" and "field" completely interchangeably, which recently caused some confusion in a technical discussion.

I was told, though, that this wasn't correct, that it should be (translating each term into spreadsheet terminology, ignoring data types and all the other stuff that make databases useful):

Database Column: like a spreadsheet column
Database Record: like a spreadsheet row
Database Field: like a spreadsheet "cell" (a specific column of a specific row)

Is this right? I could have sworn that column and field are used more interchangeably than that. I certainly have been.

So we don't add fields to a table, we add columns to a table, and fields are only relevant when talking about data within a record?

Other thoughts on column vs field?

Edit: to clarify, the current context is MS SQL Server. My background before SQL server was MS Access, which might influence my use of these terms.

Relational database theory does not include the use of the word Field.  Dr. E.F. Codd, who wrote the series of papers that provide the theoretical basis for RDBMS's never used the term.  You can read his seminal 1970 paper A Relational Model of Data for Large Shared Data Banks if you want to check.

Terms like Domain, Table, Attribute, Key and Tuple are used.  One reason for this, is that his papers were largely concerned with relational algebra, and the way a particular implementation would define a table in a database wasn't considered by Codd to be important.  Vendors would flesh that out later.  People also have to understand that historically, RDBMS's evolved from existing hierarchical and network databases that predate them, AND the inner workings of an RDMBS still have to be concerned with data organization and storage.

In common use, and you can easily verify this by simply doing a bit of googling, Fields and columns are the same thing.

PC Databases like DBase, Access and Filemaker typically use "field" instead of "column".  "Attribute" is another term that can be used interchangeably.

For example, here's a link to the MS Access manual on adding a "field" to a table.  It's clear to see that in MS Access a "field" is equivalent to a "column".

The same holds for Dbase and Filemaker Pro.

Sometimes people will refer to a specific value in a specific row as being a "field" or more properly a "field value" but that does not make the use of "field" when referring to a column or column-equivalent-concept incorrect.  This does tend to cause a level of confusion because people have used "field" to mean different things for many years.  In relational theory -- a single atomic value is referred to as a "Datum".

If someone stated that a "field" is one value in a relational database and not the same as a column, that is their opinion, since "field" is not part of relational database vernacular.  They are neither right nor wrong, however, throughout the database world, field is more often used to mean column.

With that said, projects and teams often have to work out an understanding of how they want to use particular terminology within the project to avoid confusion.

You aren't wrong, but you also might decide to simply go along with the convention being used, or avoid using the word field altogether in favor of "column".  With relational databases, "Table" and "Column" are the building blocks that exist in DDL and it's best to just use those terms and avoid "field" which isn't used, nor clearly defined.


MongoDB service not starting on RedHat - missing 'service_uri' I'm on a Red Hat Enterprise Linux Client release 5.4 (Tikanga) machine

I created my /etc/yum.repos.d/10gen.repo like this:

[10gen]
name=10gen Repository
baseurl=http://downloads-distro.mongodb.org/repo/redhat/os/x86_64
gpgcheck=0
enabled=1

I installed MongoDB with: sudo yum install mongo-10gen mongo-10gen-server

but when I run: sudo service mongod start

I get: Missing SERVICE_URI environment variable

help! :)

That sounds more like a problem with the service command than with MongoDB or the install.  Try starting it from init.d instead:

sudo /etc/init.d/mongod start


Problem installing DB2Express I installed DB2 Express 10.1 on my Windows 7 (64bit) notebook. Now after the installation finished I cannot connect to the database. During the installation I specified "C:\Daten\db\db2" as the directory to be used for DB2.

When trying to connect, I get the following error:

  SQL1031N  The database directory cannot be found on the indicated file system.

However the database seems to be there:

db2 => connect to db2inst
SQL1031N  The database directory cannot be found on the indicated file system.
SQLSTATE=58031

db2 => list database directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = DB2INST
 Database name                        = DB2INST
 Local database directory             = C:\Daten\db\db2\IBM\DB2\DB2INST
 Database release level               = f.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

I verified that the file system privileges are correct for the directory (the user running the DB2 service has full control on that directory and I made him the owner as well).

The content of C:\Daten\db\db2\ looks like this:

\---IBM
    \---DB2
        \---DB2INST
            +---cfg
            +---cfgcache
            +---DB2
            |   +---ctrl
            |   +---hmonCache
            |   |   \---DB2
            |   |       \---0
            |   |           \---ossipcres
            |   +---log
            |   +---security
            |   |   \---auditdata
            |   +---spmlog
            |   |   \---SPMLOGSD
            |   +---SQLDBDIR
            |   +---TMP
            |   \---UIF
            +---DB2DAS00
            |   +---cfg
            |   +---dump
            |   +---log
            |   +---metadata
            |   \---tmp
            |       +---dastmp
            |       \---schedtmp
            +---db2dump
            +---function
            |   \---unfenced
            \---license

I then tried to re-catalog the database:

db2 => uncatalog database db2inst
DB20000I  The UNCATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.
db2 => catalog database db2inst on "c:\Daten\db\db2\IBM\DB2\DB2INST\"
DB20000I  The CATALOG DATABASE command completed successfully.
DB21056W  Directory changes may not be effective until the directory cache is
refreshed.

But that didn't help either.

For some reason it seems DB2 can't access the (existing) directory:

db2 => list db directory on "c:\Daten\db\db2\IBM\DB2\DB2INST\"
SQL1031N  The database directory cannot be found on the indicated file system.
SQLSTATE=58031
db2 => list database directory

I'm not experience with DB2 and the last time I installed Express everything went smoothly, so I'm a bit lost now.

If you built your database at C:\Daten\db\db2\IBM\DB2, then that should be your path for your database directory. (ie, it should be one higher than where your database resides).

Also, just wondering what command you used when you built your database? Where did you specify that it lives?


mysql 5.5 repartition With MySQL 5.5, I want to change the partitioning scheme of a very large InnoDB table from "hash" to "range". Can I do it while the table is accepting DML? If not, what is the best-practice work-around?

Thanks!

There probably is no way to change the partitioning without taking downtime.  If you can block just writes, then this would allow concurrent reads:

disable writes (not via SQL)
CREATE TABLE new ... PARTITION BY RANGE ...
INSERT INTO new
    SELECT * FROM real;   --  probably wise to do in chunks
test new table
RENAME TABLE real TO old, new TO real;  -- Fast and atomic
DROP TABLE old;
enable writes

But -- Before embarking on the rebuild, please show us SHOW CREATE TABLE (both by-hash and by-range) and the important SELECTs.  Also, explain the purpose of using Partitioning.  It may be that Partitioning, even by-range, is not worth doing.  (In my experience by-hash is useless.)

My comments on partitioning (perhaps relevant):  http://mysql.rjweb.org/doc.php/partitionmaint


Can I use a Synonym to point to an object in a schema using another schema? I don't have too much experience in Oracle, so I am sorry if this is a simple question.

I have to move many objects in the database from one schema to another.
I am trying to update all dependencies as well, but just to be in the safe side I would like to leave behind a link.

Example:

Right now I have in my Schema 1 (S1) a MyTable.
Users reference it like:

S1.MyTable
I would like to move MyTable to Schema 2 (S2)

S2.MyTable
I would like my users to be able to still use MyTable as they used to.

S1.MyTable /MyTable is really in S2, but users can access it with S1/

I have been reading about Synonyms and it seems you can create public ones so users can reference an object directly, like:

MyTable

But I need them to be able to write S1.MyTable

Can I use synonyms for this?

Yes, of course. You just need to create synonym in S1 schema (not public synonym).


Running query progress indication I have a query:

SELECT *
FROM OPENQUERY
(
    oracle,
    'SELECT *
    FROM [some_table]'
)

It runs for about 7 hours. I only need to run this query once to import data into a new system when we change system.

I want to know is there some table in SQL Server 2005 that tracks how much data has been imported or bytes sent and received? So I can try and monitor this or check how this is going?

Is there some table in MS SQL 2005 that tracks how much data has been imported or bytes sent and received? So I can try and monitor this or check how this is going?

The simplest way to do this is the query the management views associated with the currently-executing request, the session, or the connection:

sys.dm_exec_requests
sys.dm_exec_sessions
sys.dm_exec_connections

The first two expose a row_count attribute that contains the number of rows returned by the query so far. If you know the total number of rows that will be returned (even roughly) this can provide a simple progress indication.

The third view provides num_reads and num_writes indicating how many network packets have been sent and received by the connection. The size of the network packet in use for that connection is given in the net_packet_size attribute. The reads and writes include protocol overhead, and are not reset to zero at the start of each query or statement, so you would need to record the values before the query of interest starts executing.

All three views allow you to specify the session_id you are interested in, for example:

SELECT row_count
FROM sys.dm_exec_requests AS DER
WHERE session_id = 52;


execute() vs executeQuery() in MySQL Connector C++ With MySQL Connector C++, I can use the sql::Statement::execute() function if I am going to modify the database (INSERT, UPDATE, DELETE, MERGE), or the sql::Statement::executeQuery() function if I am going to do a simple read only query.

Supposing I do not know what is in the query, how can I choose what to do?

At the moment I just check if in the query string there are any of the INSERT, UPDATE, DELETE or MERGE tokens. But that is a far from perfect way to do it.

Is there another method to do it?

Should I just ignore the warning messages returned by MySQL when I use executeQuery() to modify the DB?

Thank you.

Each one of the execute calls is optimized in a different way depending based on their precise usage - for instance the executeUpdate method is optimized the way it is because it expects you to not return a result set from your call. You should choose the one that best suits your needs. The below is from the Connector C++ reference:

  If you don't know ahead of time whether the SQL statement will be a SELECT or an INSERT, UPDATE, or DELETE, use the execute method. execute() returns true if the SQL query was a SELECT, and returns false if the statement was an INSERT, UPDATE, or DELETE. If the statement was a SELECT query, you can retrieve the results by calling the getResultSet method on the Statement instance. If the statement was an INSERT, UPDATE, or DELETE statement, you can retrieve the count of affected rows by calling getUpdateCount().

The article I mentioned can be found here:

http://dev.mysql.com/tech-resources/articles/mysql-connector-cpp.html

I hope this helps you.


SQL 2012 insert data into a table from another table and custom columns I'm trying to insert data from one table into another table and I also have 4 columns that are not in the source table but need to be populated in the target table. Here is the Insert statement

SET IDENTITY_INSERT dbo.district ON

INSERT INTO Table1 (dim_district_key, workday_id,
district_code,district_abbr,district_name,state,region,dss_start_date,
dss_end_date,dss_current_flag,dss_version, dss_update_time, created_by,
created_on, last_updated_by, last_updated_on)

SELECT dim_district_key, workday_id, district_code, district_abbr,
district_name, state,region, dss_start_date, dss_end_date, dss_current_flag,
dss_version, dss_update_time, SYSTEM_USER, GETDATE(), SYSTEM_USER, GETDATE()
FROM Table2

These columns are not in table2: created_by, created_on, last_updated_by, last_updated_on

When I run the above insert statement I get
Msg 207, Level 16, State 1, Line 4
Invalid column name 'created_by'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'created_on'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'last_updated_by'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'last_updated_on'.

The obvious answer is that those columns aren't in Table1 either.  Otherwise I don't see an obvious reason why that would fail.  It looks like you're adding data to the table that could easily be captured by a default value.  See part B for an example of using SYSTEM_USER as a default value or just see the code shamelessly copied below:

CREATE TABLE Sales.Sales_Tracking
(
    Territory_id int IDENTITY(2000, 1) NOT NULL,
    Rep_id  int NOT NULL,
    Last_sale datetime NOT NULL DEFAULT GETDATE(), -- Your date info
    SRep_tracking_user varchar(30) NOT NULL DEFAULT SYSTEM_USER -- System user
);

By setting these as a default value on Table2 you might just be able to add those four columns to Table2 without having to bother with the insert.  Alternatively you can set the date and user columns to default to GETDATE() and SYSTEM_USER respectively on Table1.  That would eliminate the need to specify those values in your insert statement, and give you good defaults moving forward from the looks of things.


Identity Column Behaviour After Replication Say we have two Servers: Server A and Server B. Data is being replicated (vanilla Transactional Replication) from A to B.

For one of the Tables, we have data as follows:

Server A   - Server B
=========    =========

ID|Content   ID|Content
----------   ----------
1 | ...      1 | ...
2 | ...      2 | ...
  ...         ...
98| ...      98| ...
99| ...      99| ...

After a while, we delete old data:

Server A   - Server B
=========    =========

ID|Content   ID|Content
----------   ----------
50| ...      50| ...
51| ...      51| ...
  ...         ...
98| ...      98| ...
99| ...      99| ...

Now, we changed the order of replication, so now Server B is Replicating to server A.

The issue is:

I Would have expected that new data that is added to Server B (and then replicated to Server A) would continue using the ID column (It would go to 100, 101, 102, ...). Instead, I noticed it starts back at 1.

The question is:

How will the DB handle it when the ID finally hits 50? Will the DB just start throwing Primary Key violations, or will it realize what is going on, and jump from ID 49 to ID 100?

If it would start throwing Primary Key violations, is there a command we can use to tell Server B that it should start at ID 100? And if so, can this command be done while it is replicating data?

After switching to a different master you must reseed the identity value, otherwise you will get duplicates in the id column. Use the DBCC CHECKIDENT statement.

DBCC CHECKIDENT ( 'dbo.ITest', reseed, 1000 ) ;

Choosing the number could be automated by using a SELECT MAX( TID ) query and placing the returned number into a dynamic SQL string. This must be placed in a serializable transaction to make sure no other sessions insert new rows while this session is reseeding the value (thanks David). Also, the master must not be changed while reseeding is taking place.

One way to deal with this might be to create non-overlapping identity domains. On ServerA, create the identity column as identity ( 1, 2 ) and on ServerB as identity (2, 2 ). That way ServerA's inserts will be 1, 3, 5… and  ServerB's inserts will be 2, 4, 6… Of course, if you ever want to create a third server then you are hosed. :-)

Better still, don't use identity in a multi-master situation. It provides no prevention of duplicates. Use a uniqueidentifier or an application-generated identifier instead.


ON clause of JOIN specific syntax? I've been a bit curious about the syntax of the JOIN operation, especifically the ON clause where we set the condition. Let's say we are performing a join on an existing table and a table we have just created as a subquery in the JOIN, for instance:

SELECT DISTINCT ssn, job, identifier, startdate, enddate, salary, ceasing
FROM oldcontracts
JOIN (SELECT DISTINCT name, identifier FROM jobs)
ON oldcontracts.job = jobs.name
;

The thing here is, how would we go about setting an ON condition that would work? I say this because the syntax is of the following form:

table1.column <condition> table2.column

Where apparently, SQL 2005 is very strict about where we get those columns from. Meaning I get an error on jobs.name saying it is not a valid identifier. I assume SQL throws this error because jobs was not the table that is being joined in the operation.

I know that the way around this is to simply set jobs as the table2 to use in the JOIN, and adjust the SELECT as need be, then everything would work, but I'm curious to know if there's a way to address a column of a subquery, given that that specific subquery is the one being used as table2 in the JOIN operation?

Normally, you'd simply alias the subquery (which I thought was required in SQL Server)

SELECT DISTINCT ssn, job, identifier, startdate, enddate, salary, ceasing
  FROM oldcontracts
       JOIN (SELECT DISTINCT name, identifier
               FROM jobs) mysubquery
         ON oldcontracts.job = mysubquery.name

I would strongly suggest that you always alias any tables in your query and that you use those aliases for every column in the select list.  That makes it much easier to see where the different data elements are coming from and makes it easier when you add additional joins in the future.


How to structure an append-only database in PostgreSQL? Preface

We have been stuck for years using a monolithic program with terrible and inefficient database structure. Now we are finally moving away from it with our own program.

The only area we are having difficulties with is the database. Our data is financial and our existing program causes loss of data mostly through overwrites (UPDATE, DELETE). We have decided to move toward an append-only structure to keep all of the data we accumulate. We may look into using a warehouse if it grows past our initial estimates, but in the meantime, we expect the database to grow to a few millions rows over a few years for some of the larger tables; not massive in scale at this point.

We are using PostgreSQL and will be denying modification privileges such as UPDATE and DELETE for users to ensure historical consistency of data.

Our Ideas

We thought we might be able to contain all current and historic data into one table such that we query the last record (or newest timestamp) for each id as the following table suggests.

To clarify, we have dozens of tables; Below represents a simple version of one such tables.

╔═════╦════╦════════╦════════╦═════════════════════╗
║ key ║ id ║ field1 ║ field2 ║ timestamp           ║
╠═════╬════╬════════╬════════╬═════════════════════╣
║ 0   ║ 0  ║ a      ║ b      ║ 1970-01-01 00:00:01 ║
╠═════╬════╬════════╬════════╬═════════════════════╣
║ 1   ║ 1  ║ x      ║ y      ║ 1970-01-01 00:00:02 ║
╠═════╬════╬════════╬════════╬═════════════════════╣
║ 2   ║ 0  ║ a      ║ c      ║ 1970-01-01 00:05:01 ║ # new version of id=0 row
╚═════╩════╩════════╩════════╩═════════════════════╝

We have also considered NoSQL solutions, except we prefer to remain ACID compliant (and most NoSQL solutions cannot meet that expectation).

We have also looked into other products such as AWS QLDB, but we're limited by budget, so we've decided to go with PostgreSQL for the time being and will re-evaluate when necessary.

Questions

How can we best structure the database to ensure optimal performance, but also keep in mind developer usage and ease of use?
Does our existing idea for structuring the database allow for optimal performance and is it coherent?
What limitations and issues (apart from disk space) might we be creating for ourselves with this plan?

This sounds like a use-case for temporal tables. AFAIK PostgreSQL does not implement this natively. There are extensions for it. It can be written in user-code, usually with triggers.

The basic idea is that the data table has two date/time columns. These define the interval during which the value in a row is applicable - it is "the" value for that key during that interval. By key I mean natural key, not a surrogate row-labeling key i.e. the id column, not the key column in your example. It is best to make this a "closed-open" interval so the start time is included but the end time excluded. The resolution of the interval start and end columns has to be appropriate for the update frequency.

When a new value for that key arrives, the existing row is updated to set its end and a new row is written. The end date/time for the old row and the start date/time for the new row are equal, hence the closed-open formulation.

To use your example: the first row arrives

║ key ║ id ║ field1 ║ field2 ║ from                ║ to
║ 0   ║ 0  ║ a      ║ b      ║ 1970-01-01 00:00:01 ║ 9999-12-31 23:59:59 ║

Some time later the second row for this key arrives. The first row is updated, the second row is written

║ key ║ id ║ field1 ║ field2 ║ from                ║ to
║ 0   ║ 0  ║ a      ║ b      ║ 1970-01-01 00:00:01 ║ 1970-01-01 00:05:01 ║
║ 2   ║ 0  ║ a      ║ c      ║ 1970-01-01 00:05:01 ║ 9999-12-31 23:59:59 ║

To read a value

select <columns>
from <table>
where id = 0
and from <= <right now>
and to > <right now>

The place holder <right now> represents the current instant on the server. It can be local, UTC or whatever. It just has to be consistent with what was used when the row was written. Using UTC uniformly makes this easier.

A consistent set of values can be read from the database from any point in history by using that historical time in the predicate instead of <right now>.

To delete a value set to to the current time.

There are, of course, downsides. Each table must have extra columns. These must be indexed. Ensuring intervals for each key do not overlap using only constraints is non-trivial. Every query must have temporal predicates and they must all use exactly the same time value.

These complexities can be mitigated by adding an archive table. When an existing row's to values is set, that row is also deleted from the data table inserted into the corresponding archive table. So each id value will only have one row in the data table, and many retired rows in the archive table. Queries no longer need the additional temporal predicates. Indexes (on the data table) need not include the interval columns. Historical queries become more complex, and every new value stored will incur the cost of an additional delete and insert.


Split/explode comma delimited string field into SQL query I have field id_list='1234,23,56,576,1231,567,122,87876,57553,1216'

and I want to use it to search IN this field:

SELECT *
FROM table1
WHERE id IN (id_list)

id is integer
id_list is varchar/text

But in this way this doesn't work, so I need in some way to split id_list into select query.

What solution should I use here? I'm using the T-SQL Sybase ASA 9 database (SQL Anywhere).
But in this way this doesn't work, so I need in some way to split id_list into select query.

Way I see this, is to create own function with while loop through,
and each element extract based on split by delimiter position search,
then insert elements into temp table which function will return as result.

  COMMENTS TO Sebastian Meine ANSWER AND HIS SOLUTIONS :

  Sybase SQL Anywhere 9

  sa_split_list system procedure not exist here, so it doesn't work
  CAST works great

  Sybase SQL Anywhere 12

  sa_split_list system procedure exist and works great
  CAST works great

  For the Sybase SQL Anywhere 9 I have made sa_split_list system procedure replacement:

CREATE PROCEDURE str_split_list(in str long varchar, in delim char(10) default ',')
RESULT(
  line_num integer,
  row_value long varchar)
BEGIN
  DECLARE str2 long varchar;
  DECLARE position integer;

   CREATE TABLE #str_split_list (
   line_num integer DEFAULT AUTOINCREMENT,
   row_value long varchar null,
   primary key(line_num));

   SET str = TRIM(str)+delim;
   SET position = CHARINDEX(delim, str);

   separaterows:
   WHILE position > 0 loop
       SET str2 = TRIM(LEFT(str, position - 1));
       INSERT INTO #str_split_list (row_value)
       VALUES (str2);
       SET str = RIGHT(str, LENGTH(str) - position);
       SET position = CHARINDEX(delim, str);
    end loop separaterows;

   select * from #str_split_list order by line_num asc;

END

  Execute the same way as sa_split_list:

select * from str_split_list('1234,23,56,576,1231,567,122,87876,57553,1216')

I don't actually have a sybase system to test with, but according to http://dcx.sybase.com/1200/en/dbreference/sa-split-list-sysproc.html the following solution should work:

SELECT *
  FROM table AS T
  JOIN sa_split_list('1234,23,56,576,1231,567,122,87876,57553,1216') AS L
    ON T.id = L.row_value;

If the id_list is actually stored in another table (say list_table) you need to also use a CROSS APPLY operator (http://dcx.sybase.com/1101/en/dbusage_en11/apply-joins-joinsasp.html):

SELECT *
  FROM list_table AS LT
 CROSS APPLY sa_split_list(LT.id_list) L
  JOIN table T
    ON T.id = L.row_value
   AND <optional: some other condition that ties rows in list_table to rows in table, like a group membership or location>

This all requires that only numbers are part of these list as the split pieces will be automatically cast to the datatype of the id column. If that is not the case you can use this simpler, but slower technique:

SELECT *
  FROM table
 WHERE ','+@id_list+',' LIKE '%,'+CAST(id AS VARCHAR(20))+',%'

That last example assumed that the id list is in a variable @id_list. if it is in another table you can use the same CROSS APPLY technique from above. The additional commata make sure that the first or last entry can be matched.


Common database shared to other database I am having thoughts on what design should I take on my current application architecture. I am building multiple sites (different products) then requesting to a centralized API for data.

My initial thoughts of the API database would be:

common_db

This database likely contains common entities such as user (id, details, etc.)

first_site_db

This database contains table with data specific for the product site

second_site_db

This database contains table with data specific for the product site

... and so on.

Is this a bad database design practice? Considering this is fine, what particular technique should I implement in order for each product database to get the user details on the common database?

It is not a bad design at all, and it is not something new.

In MySQL any operation involving multiple tables can also involve multiple databases, so you won't have functional limitations.

The drawback is the complexity that you will need to add if you need to shard the databases across multiple servers/clusters. Because you will need a way to replicate the common_db, and nothing else, to all servers. But, even if you choice another model, you will hardly have an easy solution for this problem.


MySQL max_connection value Questions

I got a 12 GB machine with apache & myqsl, i have may apache max_client set to 150 will this cause some bottlenecks with the 50 max_connection in mysql?
What value should I set mysql max_connection or apache max_client to balance and still have optimize performance for my site?

Thank you.

Please see my mysql config and mysqltuner result.

[mysqld]
innodb_file_per_table
datadir=/var/lib/mysql
tmpdir=/var/lib/mysqltmp
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-name-resolve
table_cache=1024
table_definition_cache = 1024
thread_cache_size=16
back_log=100
max_connect_errors=10000
open-files-limit=20000
interactive_timeout=30
wait_timeout=60
max_connections=50
skip-name-resolve

# Slow Query Log Settings
log-slow-queries=/var/lib/mysqllogs/slow-log
long_query_time=5
log-queries-not-using-indexes

# Global, Non Engine-Specific Buffers
max_allowed_packet=16M
tmp_table_size=1768M
max_heap_table_size=1768M
query_cache_size=32M

# Per-Thread Buffers
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=8M
join_buffer_size=32M

# MyISAM
key_buffer_size=64M
myisam_sort_buffer_size=64M

# InnoDB
innodb_log_file_size=100M
innodb_buffer_pool_size=1512M

[mysql.server]
user=mysql
#basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open-files-limit=65535

 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.73-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated
+InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 48M (Tables: 124)
[--] Data in InnoDB tables: 2G (Tables: 179)
[!!] Total fragmented tables: 39

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 14h 27m 2s (20M q [41.828 qps], 1M conn, TX: 275B, RX:
5B)
[--] Reads / Writes: 75% / 25%
[--] Total buffers: 3.3G global + 42.2M per thread (50 max threads)
[OK] Maximum possible memory usage: 5.4G (46% of installed RAM)
[!!] Slow queries: 6% (1M/20M)
[OK] Highest usage of available connections: 74% (37/50)
[OK] Key buffer size / total MyISAM indexes: 64.0M/31.1M
[OK] Key buffer hit rate: 99.9% (11M cached / 7K reads)
[OK] Query cache efficiency: 68.1% (10M cached / 14M selects)
[!!] Query cache prunes per day: 42514
[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 1M sorts)
[!!] Joins performed without indexes: 73669
[OK] Temporary tables created on disk: 6% (136K on disk / 2M total)
[OK] Thread cache hit rate: 99% (155 created / 1M connections)
[OK] Table cache hit rate: 74% (974 open / 1K opened)
[OK] Open file limit used: 0% (453/65K)
[OK] Table locks acquired immediately: 99% (18M immediate / 18M locks)
[!!] InnoDB data size / buffer pool: 2.3G/1.5G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
Variables to adjust:
    query_cache_size (> 64M)
    join_buffer_size (> 32.0M, or always use indexes with joins)
    innodb_buffer_pool_size (>= 2G)

Max_connections is The total number of concurrent connections allowed by the MySQL server. If you are getting Too many connections' error, it means that you have reached this limit.

Also, You should observe threads_running variable value for a period of time to determine exact value for 'max_connections'.

You can consider max_connection value as

  MaxValue (threads_running)` + 10 = Max_connections


Can we create index for key/value of JSONB data type? Can we create an index for a key/value of a JSONB data type?

For example, for these schema:

CREATE TABLE x (
  id BIGSERIAL,
  data JSONB
);
CREATE TABLE y (
  id BIGSERIAL,
  data JSONB
);

The slow query:

SELECT *
FROM x
  LEFT JOIN y
    ON (y.data->>'x_id')::BIGINT = x.id

How to create an index for y.data->>'x_id' that can be used for that kind of query?

I suggest an expression index on the value of the key 'x_id', cast to bigint - plain (default) btree, not GIN. A specific difficulty here: the short notation for type casts would need multiple layers of parentheses to make the syntax work for index creation.

CREATE INDEX y_data_xid_idx ON y (((y.data->>'x_id')::bigint));

Rather use the explicit form (to the same effect):

CREATE INDEX y_data_xid_idx ON y (cast(y.data->>'x_id' AS bigint));


Constraint on Composite Type How can I create a constraint on a sub-field of composite type?

Pseudocode

create type axis(
    major_axis float,
    minor_axis float,
    angle float constraint angle_constraint check(angle between -90 and 90)
);

create table sample(
    axis1 axis,
    axis2 axis
);

Is this possible in PostgreSQL 9.2? It seems to be not possible in 9.1 as mentioned here.

Use CREATE DOMAIN with a CHECK constraint. This works in PostgreSQL 9.1. It's documented to work in at least 8.0+. "A partial workaround is to use domain types as members of composite types."

create domain angle as float check (value between -90 and 90);

create type axis as (
    major_axis float,
    minor_axis float,
    angle angle
);

create table sample(
    axis1 axis,
    axis2 axis
);

This INSERT statement should succeed.

insert into sample values
(row(0, 0, 35), row(0, 0, 35));

But this one should fail.

insert into sample values
(row(0, 0, 93), row(0, 0, 35));
ERROR: value for domain angle violates check constraint "angle_check"
SQL state: 23514


where varcharcol = 0 returns all rows Suppose I have a table like this:

create table mytable(cola int primary key, colb varchar(10));

Let's put some data in it:

insert into mytable values(1, 'abc');
insert into mytable values(2, 'def');

When I select using this query:

select * from mytable where colb = 0;

I get ALL rows.

Please note colb data type is varchar, but I use 0 as value. If I use 1 or other integers, I get no rows.

Why is that? Is there a setting to prevent this behavior?

BTW mysql 5.5.9 for windows.

This is caused MySQL's sloppy data type checking. As far as I know there is no workaround, except to write correct SQL.

In general it is highly recommended to never rely on implicit data type conversion. Make sure your literals match the datatype of the column.

So you should write:

select * from mytable where colb = '0';


Good layout of 3d point data for spatial queries in Postgres? Like shown in another question, I deal with a lot (>10,000,000) entries of points in a 3D space. These points are defined like this:

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

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

I oftentimes do spatial queries like this:

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

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

This is the table layout the structure is used in:

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

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

The composite type is clean design, but it does not help performance at all.
First of all, float translates to float8 a.k.a. double precision in Postgres. You are building on a misunderstanding.
The real data type occupies 4 byte (not 8). It has to be aligned at multiples of 4 bytes.
Measure actual sizes with pg_column_size().
SQL Fiddle demonstrating actual sizes.
The composite type real3d occupies 36 bytes. That's:
23 byte tuple header
1 byte padding
4 bytes real x
4 bytes real y
4 bytes real z
---
36 bytes

If you embed that into a table, padding may have to be added. On the other hand the header of the type can be 3 byte smaller on disk. Representation on disk is typically a bit smaller than in RAM. Doesn't make a lot of difference.
More:

Configuring PostgreSQL for read performance
Calculating and saving space in PostgreSQL

Table layout
Use this equivalent design to reduce row size substantially:
    Column     |           Type           |                       Modifiers
---------------+--------------------------+---------------------------------
 id            | bigint                   | not null default nextval(...
 creation_time | timestamp with time zone | not null default now()
 edition_time  | timestamp with time zone | not null default now()
 user_id       | integer                  | not null
 project_id    | integer                  | not null
 location_x    | real                     | not null
 location_y    | real                     | not null
 location_z    | real                     | not null
 radius        | real                     | not null default 0
 skeleton_id   | integer                  | not null
 confidence    | smallint                 | not null default 5
 parent_id     | bigint                   |
 editor_id     | integer                  |

Test before and after to verify my claim:
SELECT pg_relation_size('treenode') As table_size;

SELECT avg(pg_column_size(t) AS avg_row_size
FROM   treenode t;

More details:

Measure the size of a PostgreSQL table row


PostgreSQL procedural languages overhead (plpython / plsql / pllua...) I'm trying to find information about PostgreSQL user defined functions in procedural languages performance for real time tasks.

How does they compare to builtin functions?
Is there any difference (in overhead) how Postgres call / manage plpython vs plpgsql vs pllua functions (I'm interested in the Postgres integration / context / data transfer side, not the VM itself)?
Is the context a big overhead? Can I use it for realtime data mapping (let's say 1000 queries/s))
Is there any benefit of writing user defined functions in plpgsql then other pg/language? On the documentation they enumerate advantages, but I think they apply to all postgresql procedural languages.

Related findings:

A speed of PL languages for atypical usage
PostgreSQL Function Language Performance: C vs PL/PGSQL

UDFs in interpreted languages are pretty much always slower than UDFs written in C or built-in functions, all other things being the same.
Each language binding has different code to connect PostgreSQL to the language, with different degrees of optimisation, different ways of passing some data types, etc. So variation certainly exists. It shouldn't be huge unless you're passing a data type that gets very different handling by one language than another, e.g. one passes a hstore as a string, and another converts it to a dict.
Unclear what "the context" is. Can you use it for "real time data mapping" ... well, depends on what the function does and if it's fast enough on the server it's running on, for the clients it's taking to, and for your requirements. How long is a piece of string? Benchmark.
PL/PgSQL is simpler to write, and offers faster access to SQL. It's generally better when you need to wrap a little logic around a lot of SQL. It's very slow for mathematical operations and complex algorithms, so purely computational code in PL/PgSQL should be avoided whenever possible in favour of C, or a faster procedural language.

Speedups when re-implementing PL/PgSQL code in C can vary from neglible to over 1000 times. It all depends on what the code is actually doing.

(This kind of multi-question isn't well suited to Stack Exchange as it's harder to have a definitive answer)


What role is able to grant role permissions on MSSQL 2016? Unfortunately, we'll have to remove db_owner role from not admin users, because people were abusing it.

We'll split roles permissions in 3 groups:

1) readonly (SELECT) with specific write (INSERT,UPDATE,DELETE) on some tables

2) DDL (ALTER) without RW

3) "restricted admin" custom role, able to add other AD groups and users to existing roles

But I can't find what role is able to grant and revoke permissions without being a db_owner. I tried db_accessadmin and db_securityadmin, but when a user with these roles tries to add another user to db_datareader or db_datawriter it fails saying he doesn't have permission.

A restricted admin should only add users to roles through ALTER ROLE.  The ability to grant new permissions to a role or user should be reserved for a real admin.


Is there any manual for the visual explain in mysql? The output plan is unreadable Just like this example, I cannot even tell the join ordering from it. And what does "ALL" and "ref" in the table mean? Is there any manual about the visual explain?

ref evidently means a keyed lookup because the definition of ref in EXPLAIN is

  The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table.

As for ALL, my guess is that refers to a full table scan because the same URL says

  A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that enable row retrieval from the table based on constant values or column values from earlier tables.

In all honesty, I cannot tell what the ORDER BY is. You should try comparing that graph above to the text-based output from one of these:

pt-visual-explain (Documentation)
mk-visual-explain (Documentation)

and see if the JOIN ordering makes sense there.


Relationship with optional in-between table I am currently implementing a self-assessment tool. The requirements for its questionnaires are as follows:

  A questionnaire has one or more dimensions and every dimension has either one or more questions or one or more subdimensions (and every subdimension has one or more questions).

Sub dimensions are irrelevant for the evaluation (while dimensions are relevant) and are only used for separating questions of dimensions, providing introduction texts and categorization inside reports that will be generated after filling out a questionnaire.

When I directly translate those requirements into an ERD, it looks like this:

Or, written out (I left out most irrelevant columns for clarity):

CREATE TABLE IF NOT EXISTS questionnaire
(
    id BIGSERIAL NOT NULL PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS dimension
(
    id               BIGSERIAL NOT NULL PRIMARY KEY,
    order_index      SMALLINT  NOT NULL,
    questionnaire_id BIGINT    NOT NULL REFERENCES questionnaire ON DELETE CASCADE,
    UNIQUE (questionnaire_id, order_index)
);

CREATE TABLE IF NOT EXISTS subdimension
(
    id           BIGSERIAL NOT NULL PRIMARY KEY,
    order_index  SMALLINT  NOT NULL,
    dimension_id BIGINT    NOT NULL REFERENCES dimension ON DELETE CASCADE,
    UNIQUE (dimension_id, order_index)
);

CREATE TABLE IF NOT EXISTS question
(
    id              BIGSERIAL NOT NULL PRIMARY KEY,
    order_index     SMALLINT  NOT NULL,
    dimension_id    BIGINT REFERENCES dimension ON DELETE CASCADE,
    subdimension_id BIGINT REFERENCES subdimension ON DELETE CASCADE,
    CHECK ( dimension_id IS NOT NULL AND subdimension_id IS NULL
        OR subdimension_id IS NOT NULL AND dimension_id IS NULL ),
    UNIQUE (dimension_id, order_index),
    UNIQUE (subdimension_id, order_index)
);

While this works, I am not very happy with this solution. For example, if I would want to get all questions of a questionnaire, the best query that comes to my mind would be this:

SELECT q.id
FROM question q
         INNER JOIN dimension d ON q.dimension_id = d.id
WHERE questionnaire_id = 1
UNION
SELECT q.id
FROM question q
         INNER JOIN subdimension s ON q.subdimension_id = s.id
         INNER JOIN dimension d ON s.dimension_id = d.id
WHERE questionnaire_id = 1;

Alternatives that came to my mind:

always referencing the dimension in the question table (so it won't be an either/or anymore and the CHECK constraint would be obsolete).
always using a subdimension, even though there does not need to be one (if a dimension has only one subdimension it would be some kind of 'dummy' subdimension that would not provide an introduction text etc). This would mean there is no direct reference from a question to a dimension.

Does anyone have any better ideas? I feel like I am missing the obvious.

I think that the idea of a special “empty” subdimension that is used if there is no real subdimension is the best one. It makes the data model and the queries simple.


Tell SQL Server a query is ad hoc and not to optimize based on it Is there a way to run a one-off query against a production SQL Server database and tell it not to make any future judgments based on that query.  E.g. Store execution plans, recommend indexes, etc.

Or perhaps I shouldn't be so concerned.

You can add the query hint OPTION (RECOMPILE).

This effectively tells SQL Server, "Give me a plan to execute this query one time, but don't save it in the plan cache".

Take a peek at Kendra Little's article on RECOMPILE hints and execution plan caching.  She covers all the uses and potential effects.

RECOMPILE Hints and Execution Plan Caching

Also see:

Parameter Sniffing, Embedding, and the RECOMPILE Options

Regarding disabling the missing index feature, the only supported way to do that is to start SQL Server from the command line with the -x switch. That is not what you want, but it does set a limit on your options.


Update staging data with production data in Postgresql I'm using PostgreSQL (version 9.3) on my server and I want to update the data in my staging instance database with the data in my production instance database. Each database is controlled by a different role, and I want to keep their privileges separate in the transfer.

I tried following the documentation and ran these commands with my separate users (which have access to the corresponding PostgreSQL roles):

staging-user: pg_dump production_db > prod_db_file
production-user: psql --single-transaction staging_db < prod_db_file

But I got a couple of errors: must be owner of extension plpgsql and syntax error at or near. Besides, I'm not sure if this would have done what I wanted anyway. There are a lot of optional flags on the pg_dump and psql commands, and I don't know which ones I should use. (I've tried many combinations of flags and nothing has worked so far.)

Note: When I tried the above commands, my staging database already existed.

How do I move my production data to my staging database?

Update Oct. 30, 2015: This answer is simpler and more accurate than my old answer, which I removed for the sake of brevity. To see the old answer, take a look at the edit history.

As production user: pg_dump -Fc my_prod_db > prod_dump.db
Drop and create staging database
As staging user: pg_restore --no-owner --dbname my_staging_db path/to/prod_dump.db


Changed stored procedure causes VB runtime error ALTER  Procedure [dbo].[HRptForm3A]
@StrCond Varchar(1000),
@SelYear int,
@Type varchar(1)
as
Declare @Tbl varchar(30)
    if ( @Type = 'O')
        begin
            set @Tbl = 'TblResult'
        end
    else if( @Type = 'I')
        begin
            set @Tbl = 'TblGResult'
        end
Declare @FromPayPeriodId Int
Declare @ToPayPeriodId Int
    select @FromPayPeriodId=isnull(Min(PayPeriodId),0),@ToPayPeriodId=isnull(Max(PayPeriodId),0) from tblPayPeriodMst Where
    Month(FromDate) in (4,5,6,7,8,9,10,11,12) and Year(FromDate)=@SelYear or
    Month(FromDate) in (1,2,3)and Year(FromDate)=@SelYear+1
Declare @Qry2 Varchar(1000)
Set @Qry2='Select E.Empid,E.Empcode,E.Empname,E.EmpFhName,E.Condate,E.PfNo as AccountNo,E.VPF,E.Leftdate,E.ReasonForLeft,R.payperiodId,
R.Lop,R.LWF,R.PFGross,R.PF,R.EPFE,R.EPFR, **Month(P.FromDate)+1** as Monthnumber ,Year(P.FromDate) as YearNumber,
Case
When Month(P.FromDate) in(4,5,6,7,8,9,10,11,12) then  Month(P.FromDate)
when Month(P.FromDate) in(1,2,3) then  Month(P.FromDate)
end as Mnth
From
PayEmpMstView as E,' + @Tbl + ' as R,tblPayPeriodMst as P
where  E.Empid=R.Empid  and len(E.PFNo)>0
and R.PayPeriodId=P.PayPeriodid  and
P.PayPeriodid between ' + Convert(Varchar(10),@FromPayPeriodId) + ' and '+ Convert(Varchar(10),@ToPayPeriodId)+ @StrCond +
' Order by E.EmpCode,Year(P.FromDate),Month(P.ToDate)'
Exec(@Qry2)

Exec ('Select E.EmpCode,E.EmpId  from tblPayEmployee E Where  0=0  ' +  @StrCond )

GO

when i change the highlighted value Month(P.FromDate) to Month(P.FromDate)+1. its showing exception error in the coding part of vb.net as HRESULT:800340 error
this change is working in sql server 2000 but not in 2005.
how to overcome this

Karthik added this code in the comments. It appears to be VB.NET code. It's not super legible.

MNth(4) = "Apr-" & Year :
MNth(5) = "May-" & Year.ToString :
MNth(6) = "Jun-" & Year.ToString :
MNth(7) = "Jul-" & Year.ToString
MNth(8) = "Aug-" & Year :
MNth(9) = "Sep-" & Year.ToString :
MNth(10) = "Oct-" & Year.ToString :
MNth(11) = "Nov-" & Year.ToString
MNth(0) = "Dec-" & Year :
MNth(1) = "Jan-" & (Year + 1).ToString :
MNth(2) = "Feb-" & (Year + 1).ToString :
MNth(3) = "Mar-" & (Year + 1).ToString
Dim J As Integer J = 4 ' k = 1
  IdOfEmp = Val(DS.Tables(0).Rows(TotalCnt).Item("EmpId"))
  While J < 13
    Try
      While IdOfEmp = Val(DS.Tables(0).Rows(TotalCnt).Item("EmpId"))
        If J = Val(DS.Tables(0).Rows(TotalCnt).Item("MonthNumber"))
          Then .PrintString(" | | | | | | | | |") :
               .NewLine() :
               .Tab(.LeftMargin)
        If J = 12
          Then J = 0
        End If
       .PrintString(MNth(J), 9, DosPrint.Align.Left)

I'm not sure that the best approaches are being followed here, but ... I'm just trying to clean things up.

So many problems with this code.  The error is right here:

.PrintString(MNth(J), 9, DosPrint.Align.Left)

Since J is a number between 2 and 13 when it returns from sql and then in the set 0,2..11,13 after the if statement, when it is 13 MNth(13) will throw an array out of bounds.

The fix is in - add the following line (if statement for context.)

 J = J - 1  ' now J goes from 1-12
 If J = 12
      Then J = 0
 End If

Another note (I can't resist pointing this one out but there are many...)

This part of your query:

Case
When Month(P.FromDate) in(4,5,6,7,8,9,10,11,12) then  Month(P.FromDate)
when Month(P.FromDate) in(1,2,3) then  Month(P.FromDate)
end as Mnth

could be written as:

Month(P.FromDate) as Mnth

Since Month() is defined to return a value from 1..12