Indexed range comparison of array key's value in row inside JSONB This is for tests with results. The order and if the test was done can be vary per row.

column jdata
{"name": "Somedata",
 "array": [ {"name":"test0","value":4.6},
            {"name":"test2", "value": 6.7},
            {"name":"test3", "value": 7.5},
            {"name":"test1","value":4.6}
           ],
"otherstuff": "stuff"
}

I want to get all occurrences of test1 where value is gt 4.5. test1 can be in any position in the array.

The best I can come up with is doing an indexed scan for @> {"name": "test1"} to get the IDs then on that to somehow construct a path getting (jdata->'array'->(gettest1indexsubqueryoffsorts)->'value')::float > 4.5

An index could maybe be constructed that summarizes all the tests into {"test1": 4.6, "test2": xx.xx} but again range checking cannot be done on that or the data structure can be changed. Pointers would be great.

You want to test a given key for a range of numeric values (not just for equality), but existing jsonb operators do not provide such functionality, even less with index support.

Makes the task difficult to speed up, but there are still options. The best solution depends on data distribution, value frequencies, typical queries, avg. column size and other undisclosed details.

Assuming the general case that you want to test for arbitrary names and values and return the whole row.

SELECT *
FROM   tbl t
WHERE  EXISTS (
   SELECT FROM jsonb_array_elements(t.jdata->'array') j
   WHERE  j->>'name' = 'test1'                           -- !
   AND   (j->>'value')::numeric > 4.5
   )
AND t.jdata->'array' @> '[{"name": "test1"}]';

The outer predicate AND t.jdata->'array' @> '[{"name": "test1"}]' is logically redundant and only makes sense if you expect a small percentage of rows to even have 'test1', in which case the query can make good use of this jsonb_path_ops index:

CREATE INDEX tbl_jdata_arr_test1_idx ON tbl USING gin ((jdata->'array') jsonb_path_ops);

Don't index the whole jsonb column for this purpose, would just makes the index bigger and slower. Only put the nested array into the index and make sure the WHERE condition of the query matches.

If you're only ever interested in 'test1', make it a partial index instead:

CREATE INDEX ...
WHERE  jdata->'array' @> '[{"name": "test1"}]';

We must repeat the test j->>'name' = 'test1' in the EXISTS expression to assert the object with "name":"test1" provides the value > 4.5 - and not just any object in the array.

Also, I would rather cast to numeric, not float. The implicit rounding of floating point numbers might make your test fail in extreme cases.

Related:

Speed up range test for key values nested in jsonb array of objects

More specialized, smaller and faster indexes are possible, for instance by extracting a Postgres array of names and only indexing that. Related:

Search for nested values in jsonb array with greater operator
Find rows containing a key in a JSONB array of records (my answer to your previous related question)
Index for finding an element in a JSON array
Index for finding combinations of attributes in JSON array


exist (select * from my table) My colleague suggests that Query 1 should run faster than Query 2. If it's true, please anyone explain me the reason. Thanks.

Query (1):

IF ( SELECT COUNT(UnitTrustCounterId)
     FROM   UnitTrustCounter
     WHERE  PeriodId = 0
   ) > 0
    SELECT  1
ELSE
    SELECT  0

Query (2):

IF EXISTS ( SELECT  *
            FROM    UnitTrustCounter
            WHERE   PeriodId = 0 )
    SELECT  1
ELSE
    SELECT  0

Well I would like to comment on further aspect of using if-else with exists.

It's best to avoid using if-else with exists. Why?

You need to make sure at least one record exists in your table
This method does not allow for concurrency (specially in insert, updates transactions, it could end up creating deadlock)

Instead you may use WHERE EXISTS

Reference materials that explains well: avoid if else with exists in sql

And to prove your colleagues wrong ;) check this article out too. I am not going to re-write Author's data here. But the tag line is worth re-writing again and again.

" The other benefit of EXISTS is that once it finds a single record that matches it stops processing. This doesn't have a huge impact if you're checking on a primary key. It does have a big impact if you're checking for existance based on another field. "

e.g. from article

if exists (select *
    from authors
    where state = 'ca')
  Print 'Record exits'
ELSE
  Print 'Record doesn''t exist'

if (select count(*)
    from authors
    where state = '172-32-1176') > 0
  Print 'Record exits'
ELSE
  Print 'Record doesn''t exist'

In the pubs database there are only 23 records in the authors table. Even with that small number of records, the IF EXISTS version runs 4 times faster than selecting a count. This is because it stops as soon as it finds a single record that matches the criteria. The second statement must process all the rows that match. --<<--taboo :D

Reference : using if exists vs if count(*)


disable constraints before using pg_restore.exe When I try to execute pg_restore.exe of a dump file from a database, it throws dozens of errors, all the same:

ERROR: insert or update on table "someTable" violates foreign key constraint "aConstraintName"

This is obviously due to the fact that I've emptied the data base before restoring it from the dump file (this file comes from a production database)... then of course no foreign keys constraint can be ok if one referenced table is empty...

Is there a way to disable the constraints and all foreign keys, for all tables, before I call pg_restore.exe, and afterwards, re-enable the constraints and foreign keys.

In SO I found something interesting: deferring constraint checking to commit time.
But I don't think I can call pg_restore.exe from inside psql.exe after deferring the constraints.

There is also this post, dating back 10 years ago, suggesting to drop then re-add the constraints. Or to change the value of pg_class reltriggers to 0 and that would also be possible for constraints... but I'm afraid it is more hacking than good practice...

What do you advise, what is the best practice in this case ? Is using pg_dump.exe with the -clean flag creates a dump that bypasses the constraints checking when restoring the database ?

Have you tried the --disable-triggers option to pg_restore?

Per the documentation: Use this if you have referential integrity checks or other triggers on the tables that you do not want to invoke during data reload.

Please note that this only is valid for a --data-only restore and requires the --superuser=username option to be passed, as well.


INDEX REBUILD IMPACT ON sys.dm_db_index_usage_stats In which case (or sql versions) are the index statistics reset by index rebuild?
There are a lot of articles which makes me think that perhaps this behavior wasn’t always the case.

Kendra Little breaks this down in detail on her blog here: Index Usage Stats Bug Fixed in SQL Server 2012 SP2 CU12 / SP3 CU3
I've quoted the table from that blog post below:

SQL Server Version
sys.dm_db_index_usage_stats
Missing Index DMVs

SQL Server 2005 & SQL Server 2008R2
Reset on database offline/restart.
Reset on any index drop/disable/create on that table. Reset on database offline/restart.

SQL Server 2012
Reset on ALTER INDEX REBUILD of that index until SP2+CU12 or SP3+CU3 . Reset on database offline/restart.
Reset on any index drop/disable/create on that table. Reset on database offline/restart. Reset on ALTER INDEX REBUILD of any index on the table.

SQL Server 2014
Reset on ALTER INDEX REBUILD of that index until SP2. Reset on database offline/restart.
Reset on any index drop/disable/create on that table. Reset on database offline/restart. Reset on ALTER INDEX REBUILD of any index on the table.

SQL Server 2016
Reset on database offline/restart.
Reset on any index drop/disable/create on that table. Reset on database offline/restart. Reset on ALTER INDEX REBUILD of any index on the table.

The gist of it is that these stats got reset on index rebuilds in certain builds of SQL Server 2012 and 2014.  You should update to the latest SP / CU, or to SQL Server 2016 or higher, to avoid the problem.


Use min max value from a CTE to define interval range on a next CTE I have one table on Redshift and in a query I'm trying to have the list of ids and the list of ids from a sequence generated.

WITH

missing_rows AS (
  SELECT id, created_at
  FROM logs
  WHERE created_at > GETDATE() - INTERVAL '2 hours'
),

range AS (
  SELECT min(id) AS min_id, max(id) AS max_id
  FROM missing_rows
),

series AS (
  SELECT (row_number() over (ORDER BY 1)) + (range.min_id - 1) AS id,
  FROM logs
  FULL OUTER JOIN range ON ???? -- here is my issue
  WHERE active_connections.id
BETWEEN range.min_id -- how to use min_id from range
    AND range.max_id -- how to use max_id from range
)

SELECT *
FROM series

How to use range values inside the next query series?

Range returns exactly 1 row, so you can use a cartesian product (cross join):

, series AS (
    SELECT (row_number() over (ORDER BY 1)) + (range.min_id - 1) AS id,
    FROM logs
    CROSS JOIN range
    WHERE active_connections.id BETWEEN range.min_id
                                    AND range.max_id
)

I'm not familiar with redshift so if cross join is not supported you can use a join where the join predicate always evaluates to true:

    FROM logs
    JOIN range ON 1=1
    WHERE active_connections.id BETWEEN range.min_id
                                    AND range.max_id

or an old style "," join;

    FROM logs, range
    WHERE active_connections.id BETWEEN range.min_id
                                    AND range.max_id

You can also use a normal join and use the where predicate in the on clause:

    FROM logs
    JOIN range
        ON active_connections.id BETWEEN range.min_id
                                     AND range.max_id


"Use Database" command inside a stored procedure I would like to dynamically use a database inside a stored procedure but the use database_name  command is not supported inside procedures.

What alternatives are there?

From MySQL Documentation on Stored Routine Syntax  When the routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates).
USE statements within stored routines are not permitted
If you need to query tables from other databases you can do it like DBName.TableName. and you can change DBName dynamically.


PreparedStatement caching size on Oracle and SQL Server I understand that PreparedStatements are complied and cached by database servers.

I would like to know if all the preparedStatements are complied and cached on the server, in which case what is the cache size on database?

There is no difference in treatment in Oracle between a java PreparedStatement and a standard Statement: both are treated as cursors. They are both parsed (="compiled"), executed and (for queries only) fetched.

The difference between the two kinds of statement is that PreparedStatement:

can be executed multiple times without re-parsing
can use binds

Both actions (binding and re-executing) are client behaviours. To Oracle a client that uses a cursor only once or another client that uses a cursor a thousand times are treated exactly the same way:

The statement is initially parsed.
If the statement has already been parsed by the database before (and is still in the share pool), the previous execution plan will be re-used. This is called a soft parse.
If the statement is new or too much dissimilar to previous statements, a new plan is computed. This is called a hard parse.
The statement is then executed (once the client asks for it).
A fetchable result set is returned for queries.
The client can re-use the cursor to run the same query, possibly with different variable values (jump to 4.)

Note that standard statements, even though they are destined to be used only once, will occupy a space in the shared pool (until they are aged away according to an LRU algorithm).

Prepared statements are therefore a client optimization. They allow the client to limit the number of hard parses by using bind variables and reduce further the number of soft parses by reusing the same cursor multiple times when possible.

Not using prepared statements usually leads to a shared pool full of slightly different statements that are used only once each. The number of parses increases dramatically, which can be a source of contention and therefore bad database performance.

You shouldn't worry too much about the size of the shared pool: use prepared statements and Oracle will keep the statements that are used the most at the top, they shouldn't age away and their plans should be available at all time.


Ola Hallengren maintenance script - how can I tell a backup file is still being written to? My question is whether there is a way to identify a backup file that is still being written to. I want to copy the completed backup files to another place. By reading the backup extension, I can't differentiate completed backup files from backup files that are still being written to. Any suggestions?

You could use POWERSHELL and the COPY-ITEM cmdlet.

It will skip over files that are currently in use.

$ErrorActionPreference = 'SilentlyContinue' prevents errors from being displayed due to locked files.

powershell.exe "$ErrorActionPreference = 'SilentlyContinue';Copy-Item 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\*.bak' c:\TestOut


Calculate total attendance for each workshop I want to get overall attendance in each workshop. NumberOFWeeks column holds the number of weeks the workshop runs. for example Workshop 101 runs for 6 weeks meaning 6 days.

So far I have done this which gives me the total number of entries but how can I only include results for selective days. For example only count attendance on 01/04/2015, 02/04/2015, 05/04/2015 only

  SELECT
  COUNT(attendance.AttendanceID) as 'Total Attendance'
  FROM
  student
  INNER JOIN attendance ON attendance.StudentID = student.StudentID
  WHERE
  student.WorkshopID = '101'

Something like this would give you the total across all three of those days:

SELECT COUNT(attendance.AttendanceID) as 'Total Attendance'
FROM student
INNER JOIN attendance ON attendance.StudentID = student.StudentID
  AND attendance.Date IN ('01/04/2015', '02/04/2015', '05/04/2015')
WHERE student.WorkshopID = '101'

If you wanted the total separately for each of those dates, use this:

SELECT attendance.Date, COUNT(attendance.AttendanceID) as 'Total Attendance'
FROM student
INNER JOIN attendance ON attendance.StudentID = student.StudentID
  AND attendance.Date IN ('01/04/2015', '02/04/2015', '05/04/2015')
WHERE student.WorkshopID = '101'
GROUP BY attendance.Date

Try this for overall attendance. Keep in mind I didn't have time to setup the schema, so this is untested:

SELECT wsdata.WorkshopID
  , wsdata.TotalAttendanceDaysPossible
  , adata.TotalAttendanceDaysActual
  , ((adata.TotalAttendanceDaysActual / wsdata.TotalAttendanceDaysPossible) * 100) AS OverallAttendance
FROM (
  SELECT ws1.WorkshopID
    , ws1.NumberOfWeeks AS TotalDays
    , COUNT(s1.StudentID) AS TotalStudents
    , ws1.NumberOfWeeks * COUNT(s1.StudentID) AS TotalAttendanceDaysPossible
  FROM workshop ws1
  INNER JOIN student s1 on ws1.WorkshopID = s1.WorkshopID
  GROUP BY ws1.WorkshopID
    , ws1.NumberOfWeeks
) wsdata
INNER JOIN (
  SELECT s2.WorkshopID
    , COUNT(a.AttendanceID) AS TotalAttendanceDaysActual
  FROM attendance a
  INNER JOIN student s2 ON a.StudentID = s2.StudentID
  GROUP BY s2.WorkshopID
) adata ON wsdata.WorkshopID = adata.WorkshopID
WHERE wsdata.WorkshopID = '101'


Which database could handle storage of billions/trillions of records? We are looking at developing a tool to capture and analyze netflow data, of which we gather tremendous amounts of.  Each day we capture about ~1.4 billion flow records which would look like this in json format:

{
   "tcp_flags": "0",
   "src_as": "54321",
   "nexthop": "1.2.3.4",
   "unix_secs": "1352234521",
   "src_mask": "23",
   "tos": "0",
   "prot": "6",
   "input": "105",
   "doctets": "186",
   "engine_type": "0",
   "exaddr": "2.3.4.5",
   "engine_id": "2",
   "srcaddr": "9.8.7.6",
   "dst_as": "12345",
   "unix_nsecs": "752265174",
   "sysuptime": "2943529544",
   "dst_mask": "24",
   "dstport": "80",
   "last": "2943523241",
   "srcport": "52672",
   "dpkts": "4",
   "output": "111",
   "dstaddr": "6.5.4.3",
   "first": "2943517993"
}

We would like to be able to do fast searches (less than 10 seconds) on the data set, most likely over narrow slices of time (10 - 30 mintes intervals).  We also want to index the majority of the data points so we can do searches on each of them quickly.  We would also like to have an up to date view of the data when searches are executed.  It would be great to stay in the open source world, but we are not opposed to looking at proprietary solutions for this project.

The idea is to keep approximately one month of data, which would be ~43.2 billion records.  A rough estimate that each record would contain about 480 bytes of data, would equate to ~18.7 terabytes of data in a month, and maybe three times that with indexes.  Eventually we would like to grow the capacity of this system to store trillions of records.

We have (very basically) evaluated couchbase, cassandra, and mongodb so far as possible candidates for this project, however each proposes their own challenges.  With couchbase the indexing is done at intervals and not during insertion of the data so the views are not up to date, cassandra's secondary indexes are not very efficient at returning results as they typically require scanning the entire cluster for results, and mongodb looks promising but appears to be far more difficult to scale as it is master/slave/sharded.  Some other candidates we plan to evaluate are elasticsearch, mysql (not sure if this is even applicable), and a few column oriented relational databases.  Any suggestions or real world experience would be appreciated.

In a company I work for we are dealing with similar amount of data (around 10 TBs of realtime searchable data). We solve this with Cassandra and I would like to mention couple of ideas that will allow you to do O(1) search on a multi TBs database. This is not specific to Cassandra db though, you can use it with other db as well.
Theory

Shard your data. There is no way a single server will reliably and realistically hold such volume of data.
Be ready for hardware faults and whole node failures, duplicate the data.
Start using many back-end servers from the beginning.
Use many cheaper commodity servers, compared to top-end high performance ones.
Make sure data is equally distributed across shards.
Spend a lot of time planning your queries. Derive API from the queries and then carefully design tables. This is the most important and prolonged task.
In Cassandra, you can design a composite column key and get access to that key in O(1). Spend time working on them. This will be used to access searchable records instead secondary index.
Make use of wide rows. They are useful for storing time-stamped events.
Never perform full-scan or in fact any operation more than O(Log N) on such volume. If you require anything more than O(Log N), offload such operations to Map-Reduce algorithms.

Practice

Don't spend time building OS images or installing servers on physical machines. Use cloud based providers for quick prototyping. I worked with Amazon EC2 and can highly recommend it for its simplicity, reliability and speed of prototyping.
Windows machines tend to be slower during boot time and take considerably more resources being in Idle state. Consider using Unix-based OS. Personally, I found Ubuntu server to be a reliable OS, but moreover there is a pretty good community at askubuntu
Think about networking, nodes shall ideally be close to each other to allow fast gossiping and meta-data exchange.
Do not go into extreme cases: really wide column rows or exceptionally long column families (tables). Best performance is achieved in the sane boundaries - if db supports that many N rows by design, it doesn't mean it performs well.
Our search takes about 3-5 seconds, much is due to the intermediate nodes between UI and the database. Consider how to bring requests closer to the database.
Use a network load balancer. Choose an established one. We use HAProxy, which is simple, but dead fast. Never had problems with it.
Prefer simplicity to complex solutions.
Look for free open-source solutions, unless you are backed up by a corporation's size budget. Once you go more than several servers, the costs of infrastructure might go sky high.

I do not work for Amazon and have no relations with HAProxy and Ubuntu teams. This is a personal opinion rather than any sort of promotion.


How to transfer Right-to-Left text from Excel to SQL Server? I have a collection of data in my Excel which the language is right-to-Left. My data collection is very large. I want to transfer this to my SQL Server database and insert the values into a table.

For example it would be like this:

Insert City (Id, City, State_Id)
values (1, N'right-to-left text', 5)

Right-to-Left sample :    (2, N'تهران')

As you might be aware the prefix N is used to describe the character.

My question is how would I be able to transfer all this data without having to overwrite them again?

The main problem is the right-to-left characters

There are two ways to perform this:

Right click on Database -> Task -> Import Data -> Select Excel as your data source ->     continue until the wizard is finished .
The longgest way:

In Excel add the following Columns before your data as follow:

Copy and paste your data set in a new query on SQL Server.

Use Ctrl + H to replace the blank spots like follow:

format it as you like and you're good to go!


How can lots of ram (128 GB) be used to improve MySQL 5.5 index creation? This is similar to another post of mine, but now I have a different table structure, and it's still unclear what MySQL parameters should be changed to take advantage of the extra ram my machine has---so if it seems like a duplicate, let me know how best to rephrase what I'm asking.

I have the following table: create table mytable (id1 int, id2 int, score float) engine MyISAM, with 50 billion records.

I plan to add an index on the first column (alter table mytable add index myindex (id1)), and I am wondering what MySQL parameters could be changed to take advantage of this extra memory (e.g. buffers?)?

Note: the engine type does not need to be MyISAM if that makes a difference.

Your first concern is the data loading. Since you don't intend to have a primary key, but just an index, you should order your data before loading by the index column id1, in any UNIX flavor:

sort -n mytable.txt > mytable.sort

For loading the data, use either mysqlimport of LOAD DATA from the MySQL prompt. To speed up the loading, MYISAM tables benefit from increasing *bulk_insert_buffer_size*, *myisam_sort_buffer_size* or *key_buffer_size*, for INNODB tables, increase *innodb_buffer_pool_size* and *innodb_log_file_size*.

These increases might not be sufficient, and the loading still might slow down as the memory fills up. In that case, it is more efficient to load the data in chunks. You can monitor the speed by executing an hourly du in the mysql root directory:

#!/bin/bash
while [ 1 ]
do
    du -hs database/.
    sleep 3600
done

Just for completeness (not relevant for this specific case): Disabling indexes also helps speedup the loading process.

Now to querying:
This is a bit more complex, as it depends on your data, and the way you want to query your table. Biggest factors that influence performance are:

buffers: set by variables. Execute your typical queries and compare
the "show variables" and "show status" output to identify
bottlenecks.
indexes: you already covered that
joins: Depending on the way you join with other tables, it might be
better to denormalize your table(s) in a data warehouse style,
especially if you want to join two large tables.

Great articles on data loading and large table query optimization can be found at:
http://www.mysqlperformanceblog.com/2007/05/24/predicting-how-long-data-load-would-take/
http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-could-be-slow-with-large-tables/

Review the default and allowed values for these and other variables at:
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html


Is select * still a big no-no on SQL Server 2012? Back in the days of yesteryear, it was considered a big no-no to do select * from table or select count(*) from table because of the performance hit.

Is this still the case in later versions of SQL Server (I'm using 2012, but I guess the question would apply to 2008 - 2014)?

Edit: Since people seem to be slating me slightly here, I'm looking at this from a benchmark/academical point of view, not whether it's the "right" thing to do (which of course it's not)

If you SELECT COUNT(*) FROM TABLE that only returns one row (the count), is relatively light, and is the way to get that datum.

And SELECT * is not a physical no-no, in that it is legal and allowed.

However, the problem with SELECT * is that you can cause a lot more data movement.  You operate on every column in the table.  If your SELECT only includes a few columns, you might be able to get your answer from an index or indexes, which reduces the I/O and also the impact on the server cache.

So, Yes it is recommended against as a general practice because it is wasteful of your resources.

The only real benefit of SELECT * is not typing all the column names.  But from SSMS you can use drag and drop to get the column names in your query and delete those that you do not need.

An analogy: If someone uses SELECT * when they do not need every column, would they also use SELECT without a WHERE (or some other limiting clause) when they do not need every row?


Where is it best to store a reference of the user-selected default? Consider a user can have multiple addresses. There is a users table, an addresses table, and a user_addresses table that links addresses and users with a many to many relationship.
Users can define what their default address is.
Is it best to have the users table have a default_shipping_id column linking to the appropriate entry on user_addresses, or should the user_addresses table have a is_default column?
Users table

ID
name
default_user_address_id

--------
--------------
--------------

v.s.
User_addresses table

ID
user_id
address_id
is_default

--------
--------------
--------------
--------------

What's the better approach here?

It doesn't matter.
As you have to get the message from the second table, you always have to inner join it , and add a where clause, to get the right one.
So the only thing would be size.
A boolean needs 1 byte.
Your default_user_address_id would be bigint which needs 8 Bytes, so as most people have only 1 address or maybe two, you would save space with Booleans.
So my vote would be for number two, as it would save size.


Job to check the status of another job I am looking to create a job which would check a status of another job every 5 minutes.

select
    CASE jh.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Success'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Cancelled'
    END AS Run_Status
from
    msdb.dbo.sysjobs j
inner join
    msdb.dbo.sysjobhistory jh on j.job_id = jh.job_id
where
    j.name = 'DW Job'
group by
    j.name, jh.run_status

How would I go about creating a step to check the status of a job every 5 minutes and if it completes sucessfully, go to the next step, other wise keep checking if the job is running and if the previous job fails, then report a job failure.

You can create a sql job with first step to check for the runnnnig job using below code:

      SELECT
    job.Name, job.job_ID
   ,job.Originating_Server
   ,activity.run_requested_Date
   ,datediff(minute, activity.run_requested_Date, getdate()) AS Elapsed
    FROM
    msdb.dbo.sysjobs_view job
    INNER JOIN msdb.dbo.sysjobactivity activity
    ON (job.job_id = activity.job_id)
   WHERE
 run_Requested_date is not null
 AND stop_execution_date is null
 AND job.name like 'Your Job Prefix%'

Then you can create two more steps for success and failure notifications using sp_send_dbmail feature. Make sure Advanced options on step 1 is configured properly so that on completion of step 1 it would go to say step 2 which sends you the success notification else on failure step 3 for failure notification.

However, you can use a better way of doing this by using sql reporting services.

Use the codes from below and test to check the status and see if that works for you!

http://www.codeproject.com/Articles/431307/View-SQL-Server-Job-Status-using-SQL-Reporting-Ser


MySql include partition key in index? Please look at the following Mysql table: http://pastebin.com/b0NDSbdz

I've partitioned the table by sent_at and I'm making sure that most of the queries pass sent_at. do I need to include sent_at in all of the indexes?

do you spot any potential redundant indexes?

when I do include the sent_at in the indexes, does it have to be the first field in the index declaration?

Multiple questions, multiple answers:

No need to "spot" potential redundant indexes: use either common_schema or Percona Toolkit to find such keys. But I've checked -- and no, there are no redundant keys.
You do not have to use sent_at in the indexes
If you do, it does not matter (partition-wise) where in the index.

But the one thing that is not told is how you query your table. Do you do an equality check on most fields and range-check on sent_at? In such case better have sent_at indexed last. If there is an equality check on sent_in or range check on same field, with little or no filtering on other columns, then have sent_in indexed by itself, or first in sequence. Right now you have 1 month of data in each partition; getting it indexed by time is not such a bad idea.


Usage of COPY FROM STDIN in postgres I just started using Postgres and I'm trying to create a sample db to understand its functions, looking around, I found some scripts in pgfoundry.org.
I understand the commands, since I previously used both Oracle and MS-SQL, but all the scripts I'm running return errors when they reach the "COPY FROM" instruction. More precisely, the error is thrown at the first element that should be inserted in the given table.

I've tried running the scripts both as queries and as pgScripts, but in both ways I'm getting an error at the first row after the COPY FROM.

I'm using pgAdminIII and I used StackBuilder to install PostgreSQL 9.2.4.1 as a DB Driver. May I be missing some basic configuration that's preventing me from running this command, or I just did not understand they way it works?

EDIT:
The error is:

ERROR:  syntax error at or near "7"
LINE 5600: 7 4 13 37 2012-03-10 16:41:43.797787 2012-03-10 16:41:43.797...
           ^

********** Error **********

ERROR: syntax error at or near "7"
SQL status: 42601
Char: 140891`

where the text is:

COPY action_abilitations (id, group_action_id, partecipation_role_id, group_id, created_at, updated_at) FROM stdin;
7   4   13  37  2012-03-10 16:41:43.797787  2012-03-10 16:41:43.797787`

pgScript is a local script extension of pgAdmin, which you most probably do not want here.

pgAdmin is a GUI, not a console application - there is no stdin you could easily use. If you need stdin to stream your content, use psql, which is a console application - with the \copy meta-command of psql.

If you have a file (which you obviously do), just use SQL COPY from pgAdmin:

COPY action_abilitations (id, group_action_id, ...)
FROM 'C:\Users\usernexus\Desktop\database05-12-2012.sql';

The file needs to be readable to the postgres system user.

More info in this closely related request to the pgAdmin support list.


Mysql performance related question for very large database Let's say I have database "DB" and it has many tables.

Question 1.
If table A has 100 million rows of data and table B has 100000 rows, will database size as a whole impact performance if I do a select query on table B?

Question 2.
Table A` has 100 million rows of data for around 8000 products. Searching data for particular product on this table is very slow. Will performance increase if I instead create 8000 tables, 1 for each product and put respective data on respective table and make a search on that particular table? Does this have any caveats in a long run?

More information:

It's an InnoDB  Table
Ram size is 27GB
Table A size (the largest table)

Rows=100.43 Million
Data Size= 13.62GB
Index Size= 7.21GB
Total Size = 20.83GB

Table B size

Table B could be various other tables but their size does not exceed 500MB

Table A is indexed and it has three indices on id (Pk), ProductCode, and AddedDate columns.

UPDATE

Table A

CREATE TABLE `ProductDetails` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `DepartmentID` INT(10) DEFAULT NULL,
  `SellerID` INT(10) DEFAULT NULL,
  `ProductCode` VARCHAR(20) DEFAULT NULL,
  `WholesalerType` VARCHAR(5) DEFAULT NULL,
  `Remarks` TEXT DEFAULT NULL,
  `AddedDate` DATETIME DEFAULT NULL,
  `InsertedTimestamp` INT(10) UNSIGNED NOT NULL,
  `WholesalerID` TINYINT(3) UNSIGNED DEFAULT NULL,
  `ProductType` TINYINT(4) DEFAULT NULL,
  `ArrivalDate` DATETIME DEFAULT NULL,
  `SerialNumber` VARCHAR(4) DEFAULT NULL,
  `MessageID` INT(11) DEFAULT NULL,
  `Model` VARCHAR(10) NOT NULL DEFAULT 'NA'
  PRIMARY KEY (`id`),
  KEY `ProductCode` (`ProductCode`),
  KEY `AddedDate` (`AddedDate`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='Engine should be InnoDB for performance'

Table B

CREATE TABLE `Products` (
  `ID` INT(11) NOT NULL AUTO_INCREMENT,
  `ProductCode` VARCHAR(20) NOT NULL DEFAULT '00A',
  `Created` DATETIME DEFAULT NULL,
  `LastEdited` DATETIME DEFAULT NULL,
  `Model` VARCHAR(15) CHARACTER SET utf8 DEFAULT NULL,
  `ProductType` VARCHAR(15) CHARACTER SET utf8 DEFAULT NULL,
  `StockItem` INT(11) NOT NULL DEFAULT '0',
  `StockThreshold` TINYINT(2) NOT NULL DEFAULT '10',
  `CostPrice` DOUBLE DEFAULT NULL,
  `SellingPrice` DOUBLE DEFAULT NULL,
  `ShortDescription` MEDIUMTEXT CHARACTER SET utf8,
  `Specification` TEXT CHARACTER SET utf8,
  `Description` TEXT CHARACTER SET utf8,
  `BrandID` INT(11) NOT NULL DEFAULT '0',
  `ManufacturerID` INT(11) NOT NULL DEFAULT '0',
  `ProductStatus` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
  PRIMARY KEY (`ID`),
  KEY `ProductCode` (`ProductCode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

Example Query

SELECT * FROM ProductDetails WHERE (ProductCode = 'PR1920A'
AND AddedDate > FROM_UNIXTIME(1519841700)
AND AddedDate < FROM_UNIXTIME(1523934207))
AND ((ProductType in('14','17')
AND MessageID = 28
AND Model LIKE 'MBA-1201')
OR (WholesalerType in('PARAM1','PARAM2','PARAM3','PARAM4','PARAM5')
AND UPPER(Model) IN('RAGL21','BVRK10','TPL001','LTV2960','DEN7YZ','6YU90Q','47GER8','GB3049')))
AND DepartmentID != 0 AND SellerID != 0
ORDER BY AddedDate;

UPDATE 2

Here are the result of the EXPLAIN

id   = 1
select_type =  SIMPLE
table = ProductDetails
type = ref
possible_keys = ProductCode, AddedDate
key = ProductCode
key_len = 63
ref = const
rows = 104456
Extra = Using index condition; Using where; Using filesort

And here is the SHOW INDEX results for ProductDetails table

Q1:  A "database" is a collection of "tables".  The performance of a query on one table (or a few tables JOINed together) is entirely on the table(s) in the query.  Not other tables; not other databases.

Q2:  Do not make a bunch of tables.  If you have an index starting with ProductCode, then queries with WHERE ProductCode = 123 AND ... will be efficient.  (This question has been asked and answered many times - always the same way.)

innodb_buffer_pool_size should be set to about 20G.

If you usually fetch by ProductCode, then change

PRIMARY KEY (`id`),
KEY `ProductCode` (`ProductCode`),
KEY `AddedDate` (`AddedDate`)

to

PRIMARY KEY(ProductCode, AddedDate, id),
INDEX(id),
KEY `AddedDate` (`AddedDate`)   -- (no change here)

That will 'cluster' all 12K (avg) rows for a given product together, making many SELECTs run faster.

Does Table B have 8K rows?  Or 100K rows?  If there is one row per product, then get rid of id and change

PRIMARY KEY (`ID`),
KEY `ProductCode` (`ProductCode`)

to

PRIMARY KEY(ProductCode)

BUG! You have inconsistent CHARACTER SETs between the tables.  This can cause comparisons on ProductCode to not use an index!  (And hence be sloooow.)  (See ALTER TABLE .. CONVERT TO ..)

UPPER(Model) -- Don't use UPPER.  You have a case-folding COLLATION, so you will get the desired test without need for UPPER().

When I suggested PRIMARY KEY(ProductCode, AddedDate, id) above, including AddedDate was not important.  But looking at the SELECT, it is important, both in the WHERE and in the ORDER BY.


How to update SQL Server 2012 from Evalutation to Developer edition? During the installation of a new development infrastructure, I had to install many SQL Server instances on different machines. Some were of course dedicated for production and some other for development. As we did not had licence at the moment, I installed everything with the Evaluation edition as it is written in the MSDN documentation that the Evaluation edition can be upgraded to any other version : SQL Server Edition Upgrades

I have no problem upgrading to the Business Intelligence edition as we bought the licence keys from Microsoft, but the issue is that I have no idea how I can upgrade the remaining instance to Developer edition as there is no way to get a licence key. Even on the MSDN Subscription website it is written that it does not require any licence key to be installed. The documentation does give any clue either about this issue.

So where can I get a valid product key to upgrade from Evaluation to Developer edition as stated in the MSDN documentation?

Thanks.

You will need to re-download from your MSDN subscription.  The license keys are built into the ISO files.  When you do the installation you can selecte the edition and it will auto-populate the license key.


Which is faster join Which is faster

SELECT * FROM X INNER JOIN Y ON x.Record_ID = y.ForignKey_NotIndexed_NotUnique

or

SELECT * FROM X INNER JOIN Y ON y.ForignKey_NotIndexed_NotUnique = x.Record_ID

There will be no difference - the query engine in mssql (and in most database engines) will build the same query plan for both.  You can verify this by viewing the estimated and actual query plan in SQL Workbench.


MySQL - Dumping 1TB database with SED to replace definer Im going to dump a large database more than 1TB using MySQLDUMP (unfortunately I have to stick with mysqldump, I can't use mydumper or any other backup tools)
And I want to replace the definer on the fly during the backup.
So if I use sed with dump command, will it take more time than the normal mysqldump?
mysqldump db name | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' > dump.sql

First, dump only the routines, not any of the data.  That way it should be quite fast -- drop a routine, rebuild it.  Rinse, repeat.
As for the regexp, the format should be very regular, so [ ]* (or \s* can probably be simplified to  .
[^*]* -->  .*\*

With some guts, simply
mysqldump ... | set -e '...' | mysql ...

I'm not clear that the regexp does the right thing; please provide a sample.


Postgres: Efficient schema for querying with one exact string match and two range matches The table I need to query:
CREATE TABLE regions (
    state text NOT NULL,
    zip_begin text NOT NULL,  -- 9-digit zip code
    zip_end text NOT NULL,
    date_begin date NOT NULL,
    date_end date,

    data ...,
)

There are ~50 states and between 0-5M rows per state.  The zip and date ranges might overlap.
The following will be one of the most common queries in my OLTP application:
SELECT data
FROM regions
WHERE state = {state}
  AND {date} BETWEEN date_begin AND date_end
  AND {zip} BETWEEN zip_begin AND zip_end

This query usually yields one row, but may sometimes yield more.
From the Postgres docs, it sounds like a GiST index might do what I need, but I don't really understand how those work.

Don't dismiss the simple btree index until you try it.  It surely won't scale well to a trillion rows, but you surely don't have a trillion rows.  It might be good enough.
To use an gist index, you would need to treat the ranges explicitly as ranges, not as end points.  It would probably be best to reformat your table in that format, but you can instead use expression indexes to reformat on the fly.  But then your query will have to be written to match.
Some other complexities are that simple scalars don't have built in gist operators, so to put "state" into a gist index requires you to use the btree_gist extension; and there is not a built-in text range.  Rather than creating a text-range, you can just cast the zip to int (which would probably be better done in the table in the first place), so something like this:
create index on regions using gist(
  state,
  daterange(date_begin,date_end,'[]'),
  int4range(zip_begin::int,zip_end::int,'[]')
);

And then the query would look like:
SELECT data
FROM regions
WHERE state = {state}
  AND {date}::date <@ daterange(date_begin,date_end,'[]')
  AND {zip}::int <@ int4range(zip_begin::int,zip_end::int,'[]')

Now I haven't tested this query on the gist index, because gist indexes are so ridiculously slow to build that I don't have one available yet to try it on. It has finished building and I have tested it, and it runs and gives the correct answer with '0' left-padded 5 digit zip codes.  With the range sizes I used, it is sometimes faster and some times slower than the btree index on a 10e6 row table, but was never markedly different.


Window function not working on non selected rows I'm creating auto switching partitions procedure.
I'm creating table that looks like this:

CREATE TABLE #MaxPartitionRows
    (MaxRows BIGINT NOT NULL,
    PNumber INT NOT NULL,
    PFName SYSNAME NOT NULL,
    Value VARCHAR(30) NOT NULL)

I'm using it to decide whether to merge partitions or not.
The columns are: max rows in tables per partition function, partition number, partition function and boundary value.
The logic is to merge partition only if the next partition is also empty.
So I tried this query:

 SELECT @vcPartitionFunction    = PFName,
    @vcBoundaryValue        = Value,
    @NextPartitionRowNum    = LEAD(MaxRows) OVER (ORDER BY PNumber)
FROM #MaxPartitionRows
WHERE PNumber = @iMinPartitionNumberToSwitchOut

The problem that I've faced is that @NextPartitionRowNum gets NULL as a value, even if there are rows in the table with bigger partition number.
My guess is that engine don't need to scan next row so it returns NULL.
Is that the correct assumption?
In this case the workaround is simple, I just use

SELECT @NextPartitionRowNum = MaxRows
    FROM #MaxPartitionRows
    WHERE PNumber = @iMinPartitionNumberToSwitchOut + 1

But what if I couldn't do that?

LEAD() is calculated after the WHERE clause has limited the result to one row (I'm assuming that (PNumber) is unique). That's why you always get NULL for @NextPartitionRowNum.

You could use your select, only if you are sure that there are no gaps in the PNumber values:

SELECT @NextPartitionRowNum = MaxRows
    FROM #MaxPartitionRows
    WHERE PNumber = @iMinPartitionNumberToSwitchOut + 1 ;

Alternatively, you could use this (gaps or not, doesn't affect it):

SELECT @vcPartitionFunction    = p.PFName,
    @vcBoundaryValue        = p.Value,
    @NextPartitionRowNum    = n.MaxRows
FROM #MaxPartitionRows AS p
  OUTER APPLY
    ( SELECT TOP (1) n.MaxRows
      FROM #MaxPartitionRows AS n
      WHERE n.PNumber > p.PNumber
      ORDER BY n.PNumber
    ) AS n
WHERE p.PNumber = @iMinPartitionNumberToSwitchOut ;

If you prefer window functions, this would work, too. First calculate LEAD() over the entire table, then restrict with WHERE:

WITH cte AS
  ( SELECT PFName, Value, PNumber,
           NextMaxRows = LEAD(MaxRows) OVER (ORDER BY PNumber)
    FROM #MaxPartitionRows
  )
SELECT @vcPartitionFunction    = PFName,
       @vcBoundaryValue        = Value,
       @NextPartitionRowNum    = NextMaxRows
FROM cte
WHERE PNumber = @iMinPartitionNumberToSwitchOut ;


Must data be evenly distributed among Mongo shards? According to this article: https://www.mongodb.com/blog/post/on-selecting-a-shard-key-for-mongodb. Assuming I have my schema as:

{
    time_posted: ...,
    userid: ...,
    content: ...
}

The author mentions that if I frequently query the latest 10 articles from a certain user, I should set a compound key of time_posted and user_id.

But in my case, I only need to query the latest 10 articles from all users. Therefore I think I only need to set a single key time_posted. I observed that by doing so the data are mostly inserted into 1 shard, and so Read Locality is improved, which is the desired effect.

However, according to https://docs.mongodb.org/v3.0/core/sharding-balancing/, Mongo will try to balance the data evenly among all the shards.

  Balancing is the process MongoDB uses to distribute data of a sharded
  collection evenly across a sharded cluster. When a shard has too many
  of a sharded collection’s chunks compared to other shards, MongoDB
  automatically balances the chunks across the shards.

So my question are: must the data be evenly distributed among Mongo shards? and can I turn off the rebalancer since I don't need my data to be evenly distributed?

So even if you have 1M users, only 10 articles must be kept? I have a strong feeling that your data model has this or that flaw.

Does sharding a collection with 10 documents (give and take) make sense?

If you only need 10 documents in a collection, you don't need to shard it, since it is not going to be balanced anyway, unless the documents are exceeding around 4.8Mb in size. This size computes like this:

maxChunkSize/number of your documents is the size pre document when you chunk gets split latest, equalling 6.4Mb per document
In order to cross the migration threshold, which is only 2 for collections with less than 20 documents, we need to have your chunk split at least once to trigger the migration
Since the chunk split may be already triggered when the chunk reaches half of it's max size resulting in a split when all documents are slightly over 3.2Mb, we add the median of the difference between half of the chunks max size and and it's max size (silently assuming that a chunk is guaranteed to be split at it's max size)

But sharding this collection would not make sense the first place, as , assuming the 10 documents are a hard limit, the collection's max size can only be 160Mb as per MongoDB's BSON size limit

Do I need to have my data balanced?

Let us find out wether it is a good idea to have a bad shard key and a disabled balancer. First, if you disable the cluster balancer, this affects all sharded collections. Let us take the user collection as an example:

We have 1M users
Their user id is stored in _id, starts with 1 and each new user's _id is a simple increment
We have two shards
We disable the balancer.

Now, you shard the collection from the start. What happens internally is that there are two chunks created. In the first chunk, created on the first shard, the _id from -∞ to _id < 0 are stored. In the second chunk, created on the second shard, the _ids from 0 to +∞ are stored. Now here comes the thing: since our _id increments from 1 for each user, there is never a single user stored in the first chunk and subsequently not on the first shard. No disk space of the first chunks utilized and – with more immediate importance – no RAM, too. Since indices are (tried to be) kept in RAM along with the recently used data (called working set) amongst other things to speed operations up, sooner or later we will have the situation that the RAM on the first shard is rather empty, while the second shard will start to evict data set items or even indices out of RAM. Bad idea, huh? Now things get worse: Since we have disabled the balancer, the cluster can do nothing to mitigate the situation.

Now let's assume we were slightly smarter and have pre-split our chunks so that the _id for our users collection are distributed in a way where the _ids ranging from -∞ to 500,000 are stored on the first shard and the rest on the second. It is obvious that this is only a temporary solution, since when we exceed 1M users, the whole problem starts again. And without the balancer running, the cluster still can not mitigate this situation.

Taking this a step further: We have found out that we can use a hash sum of our _id as our shard key. Jay! Problem solved! Except it isn't.
In theory, the hash algorithm should cause our users to be evenly distributed among our shards. But there is a little thing called variance.

It can be easily demonstrated like this (heavily simplified for the sake of shortness): When tossing a coin 20 times, in theory you should get an equal number of heads and tails, since the probability of either of them is 50% right? Try it. Now. The odds are very low that you will have an equal amount of heads and tails.

How does this translate to our problem? Well, chances are high that either the first or the second shard get's slightly more documents than the other over time. And this sooner or later will add up to a point where it becomes a problem – RAM and disk space is underutilized on one shard and over utilized on the other. Again. And again, the balancer can not help us to mitigate the problem.

So this should have made it crytsal clear that you should have your data balanced and that you should never have the balancer disabled by default (there are some administrative tasks during which you should or must have the balancer disabled).

Conclusion

For some 10 documents, no matter how big they are, you don't even need to shard the collection.

Disabling the balancer might cause severe problems with your cluster. Unless you absolutely have to or you are absolutely positively sure that you can live with the consequences, do not disable it.

Please note that I left out some more complicated topics, like hard drive IO bandwidth bottlenecks, network bandwidth distribution and alike for the sake of readability and – as funny as this might sound – shortness.


Index dive/sampling row count very inaccurate in MySQL The table emp_address_phone is filled with many random values.

CREATE TABLE `emp_address_phone` (
  `address_no` int(11) NOT NULL,
  `emp_no` int(11) DEFAULT NULL,
  `street1` varchar(255),
  `street2` varchar(255),
  `landmark` varchar(255),
  `city` varchar(50),
  `state` varchar(50),
  `country` varchar(50),
  `phone` varchar(20),
  PRIMARY KEY (`address_no`),
  UNIQUE KEY `emp_no` (`emp_no`),
  UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=21001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The query is on address_no column.

select * from emp_address_phone where address_no > 90;

There are really 6427 rows totally and all of them satisfy the criteria also. But the estimate was 3118.

From optimizer trace:

{
    "index": "PRIMARY",
    "ranges": [
      "90 < address_no"
    ],
    "index_dives_for_eq_ranges": true,
    "rowid_ordered": true,
    "using_mrr": false,
    "index_only": false,
    "rows": 3118,
    "cost": 317.29,
    "chosen": true
}

I changed the number of pages that index dive algorithm samples to 300 from 20 in the cnf file. So server startup with innodb_stats_persistent_sample_pages value 300. I run Analyze table. But the estimate is still way off.

Trace when innodb_stats_persistent_sample_pages = 300

{
    "index": "PRIMARY",
    "ranges": [
      "90 < address_no"
    ],
    "index_dives_for_eq_ranges": true,
    "rowid_ordered": true,
    "using_mrr": false,
    "index_only": false,
    "rows": 3213,
    "cost": 322.81,
    "chosen": true
}

The total number of pages in clustered pk index was 289 pages. So all the 289 must have been used for estimation right? Why is it still way off?

Is the innodb_stats_persistent_sample_pages variable's purpose different? If so, is the number of pages that is read while index dive not controllable?

Also, innodb_buffer_pool_load_at_startup and innodb_buffer_pool_dump_at_shutdown are off.

Yes, the samples are inaccurate.  A factor of 2 is actually somewhat common.

It could be that the "random" probe is not very random, and may repeat some pages.

OPTIMIZE TABLE, which is generally useless, would rearrange the data, probably enough to change the statistics.

Consider writing a bug report at bugs.mysql.com .

A side comment:  A table with 3 unique indexes is extremely rare; it usually indicates a flaw in the schema design.  Perhaps nepotism is not allowed in the company?


MySQL - Deduct from two columns with a remainder I have the following table:

Id,  balance, otherBalance
=============================
1,     100,      50

I would like to create an update query that receives a parameter 'amount' and does the following:
deduct from both balances columns the entire amount. Starting with the 'balance' column first, and only when it is depleted, deduct the remainder from the 'otherBalance'.

Examples

Executing update(98), will result in:

Id,  balance, otherBalance
=============================
1,     2,      50

Executing update(102), will result in:

Id,  balance, otherBalance
=============================
1,     0,      48

Executing update(151), will result in no change since both balances are less than the amount to be deducted.

You can use the variables to do the trick:

UPDATE table AS w
  JOIN amounts AS z ON z.w_Id = w.ID
   SET w.balance = IF( (@remainder := w.balance - z.amount) >= 0, @remainder, 0 )
     , w.otherBalance = IF( @remainder >= 0, w.otherBalance, w.otherBalance + @remainder )
;

Here I suppose that amount is fetched from the another table amounts and perform the mass update for all the rows at once.

If the amount is bigger than balance, then @remainder becomes negative therefore I've added it to the otherBalance instead of subtracting it.


Impact of changing char(10) to varchar(12) on massive table I have a table in a database in Microsoft SQL Server 2005 server that is 16gb in size and has 58 million rows.

It has a column called 'balance_forward' that is char(10)   (Unusual choice for a numerical column but I didn't design the table)  I need to increase its size to accommodate balances that are more than ten characters in size (xxxxxxxxx.xx)

I tried changing it to char(12) (risky,  I make no excuses) but I think* this caused the database's log file to grow many gigabytes larger (and fill up the log drive) and the operation failed anyway (data type is still char(10))

I later realized that it would make more sense to change to at least varchar(12),  that way the column won't forcibly take up more space, but it will have more room to fit larger data.

My question is - will this also cause the log file to grow again (I managed to free up some space by moving other files off the log drive)

And am I correct to assume that using varchar instead of char will prevent the existing data from taking up more space?

(Ideally the data type should be changed to the most appropriate type for financial balances,  but I figure that will be a more drastic change on 58 million rows)

*I can't be sure.  I thought I saw plenty of space on the log drive before the operation, but I might have seen 'mb' and mistook it for 'gb'. So it may be that the log had already filled the drive. Apparently logs can grow until they've filled their allocated drive/space

Based off this and this post I would say, yes, the change from char(10) to varchar(12) will touch every row and hence grow the log.  My reasoning being that the column has moved from the fixed-length to the variable-length part of the row structure and the storage engine will have to persist this information straight away.

Variable length columns will have a two byte overhead to track the actual length (see above).  If the average length of your data is 8 bytes or fewer you are on to a winner with this chage.  Otherwise you will be taking up more space with a varchar.

The digits you require could be stored in 9 bytes with a numeric type.  This would be too much of a change to your application, I suspect, even if it is the proper way to hold this data.

I'd suggest you add a new nullable column of the right type and length.  From the above post this will be a meta data only operation with minimal logging.  Put triggers in place to keep the new column in sync with the existing one.  Then your application can keep working while the following steps happen.  Copy the existing data across in chunks.  Test on your development box to find what chunk size gives the right balance of elapsed time and log size for your system.  In my experience 10,000 is about right.  Once everything's in place, drop the old column and triggers, and rename the new column.  An index rebuild will be necessary to reclaim space.  Here are some SO posts which deal with this subject.


Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server I'm trying to establish a linked server from SQL Server 2008 R2 to an Access database. The Access database is not password protected. As I have seen in other posts (specifically this one), this is usually due to the current user not having access to the Temp folder inside the NetworkService folder. Since we are running this in Windows Server 2008, the directory structure is quite different than what most are alluding to. I have allowed access to every temp folder in the "root:\Documents and Settings\" directory, and to no avail.

This is the procedure I am using to add the linked server:

EXEC sp_addlinkedserver
@server = N'OS_Access',
@provider = N'Microsoft.ACE.OLEDB.12.0',
@srvproduct = N'',
@datasrc = N'C:\RTBData\Data\OS.mdb';
GO

It creates the linked server with no problem, but I am not able to view the tables/views of the database. Likewise, my ASP.NET application cannot access it either.

I have tried both ACE and JET(64-bit)(by installing the Data Connectivity Components for Office), and they both do not work. I have also tried configuring the ACE provider with "Dynamic Parameter" and "Allow InProcess" to true.

Additionally, I tried upping the memory usage by the MSSQLSERVER services by adding "-g512;" in front of "startup parameters" string in SQL configuration manager to rule out memory issues.

If anyone could shed some light on this that would be fantastic! Thanks!

I fixed the problem by disabling UAC within the control panel. I am unsure what changes this made, but it might help others!


running many CREATE VIEW and GRANT SELECT statements in one execution Is there a way to run multiple CREATE VIEW commands and/or multiple GRANT SELECT commands by hitting F5 in SQL Server Management Studio?  If so, how can I do this?

Multiple CREATE VIEW statements can be executed together if you separate them with keyword 'GO'

CREATE VIEW vw_test1 AS SELECT name FROM dbo.test1;
GO
CREATE VIEW vw)test2 AS SELECT name FROM dbo.test2;
GO
GRANT SELECT ON vw_test....


Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant? I have a big table (30 millions rows) defined like this:

CREATE TABLE `drawing` (
`id` int unsigned NOT NULL,
`artist` int unsigned NOT NULL,
`imageUrl` text NOT NULL,
`uploadDate` timestamp NOT NULL,
PRIMARY KEY (`id`,`uploadDate`),
KEY `uploadDate` (`uploadDate`),
KEY `artist` (`artist`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED /*!50100
PARTITION BY RANGE ( UNIX_TIMESTAMP(uploadDate)) (
    PARTITION up2009 VALUES LESS THAN (1262304000) ENGINE = InnoDB,
    PARTITION up2010 VALUES LESS THAN (1293840000) ENGINE = InnoDB,
    PARTITION up2011 VALUES LESS THAN (1325376000) ENGINE = InnoDB,
    PARTITION up2012 VALUES LESS THAN (1356998400) ENGINE = InnoDB,
    PARTITION up2013 VALUES LESS THAN (1388534400) ENGINE = InnoDB,
    PARTITION up2014 VALUES LESS THAN (1420070400) ENGINE = InnoDB,
    PARTITION up2015 VALUES LESS THAN (1451606400) ENGINE = InnoDB,
    PARTITION up2016 VALUES LESS THAN (1483228800) ENGINE = InnoDB,
    PARTITION up2017 VALUES LESS THAN (1514764800) ENGINE = InnoDB,
    PARTITION up2018 VALUES LESS THAN (1546300800) ENGINE = InnoDB,
    PARTITION up2019 VALUES LESS THAN (1577836800) ENGINE = InnoDB,
    PARTITION up2020 VALUES LESS THAN (1609459200) ENGINE = InnoDB,
    PARTITION up2021 VALUES LESS THAN (1640995200) ENGINE = InnoDB,
    PARTITION upNew VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

As you see:

Table is partitioned based on the value of uploadDate, per year. Since there are lots of rows, I've found it useful when filtering by date (that is a typical operation in our DB). Speed of filtering increased significantly.
Since the partition is based on uploadDate, and we have also the identifier id, the primary key must be composed by both id + uploadDate

Sometimes we also perform filtering by artist, then sorting on uploadDate. No performances issues here.

Instead, we've found INSERT operations very slow.

Question: is it still useful to have an index on just (uploadDate)?

PARTITIONing is not a performance panacea.

For

WHERE artist = 'constant'
  AND uploadDate BETWEEN ...

INDEX(artist, uploadDate) on a non-partitioned table is as good (or better) than using partitioning.

Do you have other queries?  So far you have not presented a convincing case for partitioning.

Will you be purging 'old' data?  Being able to use DROP PARTITION instead of DELETE is one of the few benefits of PARTITION BY RANGE.

More Discussion


The process could not execute 'sp_replcmds' on 'S15415940'. Cannot execute as the database principal because the principal "dbo" does not exist Can anyone show me the way to fix these error messages:

  The process could not execute 'sp_replcmds' on 'S15415940'. (Source:
  MSSQL_REPL, Error number: MSSQL_REPL20011)

  Cannot execute as the database principal because the principal "dbo"
  does not exist, this type of principal cannot be impersonated, or you
  do not have permission. (Source: MSSQLServer, Error number: 15517)

  The process could not execute 'sp_replcmds' on 'S15415940'. (Source:
  MSSQL_REPL, Error number: MSSQL_REPL22037)

Thanks you!

In my experience I had this message

  The process could not execute 'sp_replcmds'

when two log reader processes where accidently trying to run.

The fix I had was to:
 connect at the publisher, look in master..sysprocesses for a a leftover process from the distributor
 kill it, and relaunch the log reader on the distributor.


Creating a stored Proc that creates new user and password I'm new to MySQL. I was trying to create a procedure with two parameters:

user_name
password

...that will be generated and granted full rights:

DELIMITER $$

DROP PROCEDURE IF EXISTS `gen_user`$$

CREATE PROCEDURE `gen_user`(`user_name` VARCHAR(10), `keyword` VARCHAR(255))
BEGIN
   CREATE USER `user_name`@'localhost' IDENTIFIED BY `keyword`
   GRANT ALL PRIVILEGES ON * . * TO `user_name`@'localhost'
   FLUSH PRIVILEGES
END$$

DELIMITER ;

The error I get is:

  Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`keyword`    GRANT ALL PRIVILEGES ON * . * TO `user_name`@'localhost'    FLUSH P' at line 3

One mistake for sure:  Each of the 3 statements needs a ; at the end.

Possibly the syntax calls for literals, not variables for user_name and password.  So use CONCAT(), prepare, execute, and deallocate prepare to build and execute the CREATE and GRANT statements.


Formatting Text Field As HH:MM:SS I am having to Cast() a Text datatype to a TIME datatype.  THe issue I'm running into is when the results display i'm loosing the hh:mm:ss format.  The syntax I am using is Select SUM(CAST(salestime As Time)) From testData; How do I display my query results in hh:mm:ss format?  I also tried to use this syntax SUM(Cast(Time_Format(salesTime, "%h, %i, %s") As Time)) but that is returning a whole number
EDIT
A few sample of the times in the TEXT column are:
0:05:58
0:00:00
0:31:54
0:23:26

You can use this
Schema (MySQL v8.0)
CREATE TABLE Table1
    (`salesTime` varchar(7))
;

INSERT INTO Table1
    (`salesTime`)
VALUES
    ('0:05:58'),
    ('0:00:00'),
    ('0:31:54'),
    ('0:23:26')
;

Query #1
SELECT SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(`salesTime`, "%H:%i:%s"))))  FROM Table1;

| SEC_TO_TIME( SUM( TIME_TO_SEC(TIME_FORMAT(`salesTime`, "%H:%i:%s")))) |
| --------------------------------------------------------------------- |
| 01:01:18                                                              |

View on DB Fiddle
But TIME can only have the maximum of 838:59:59, if your SUM get bigger, you need to convert the seconds also into dd.hh:mm:ss


Working out Duplicated Data in a Customer Data Base I have a database in SQL2008 of customers, There is an ID number for the customer. What im trying to do is to create a NEWID field.

We have duplicated customers that have different ID fields, I am combining the Surname and Postcode to find the duplicated customers.

As you can see in the image with an example, i need to do the following.

1.Check if PostSur field have a match,

2.Check if there is more than 1 IDNO,

3.Check the next IDNO against the First IDNO and if there is a unique entry in the Name field. Then populate that record with the First IDNO in a new field called NewCode.

4.Otherwise the Next IDNO populate the NewCode with 'Dupe'

5.Populate all other IDNO in the NewCode also.

So i can then hide the Dupes and i should then have unique customers using NewCode.

I have the following SQL which uses ROW_NUMBER but cant figure out how to do the above with it?

   select *
 from (
  select *,
      row_number() over (partition by PostSur  order by Postsur) as rowID
 from Aron_Reporting.dbo.Customer_NewSort
   ) as rowID
 order by PostSUr

any help would be great

Here are your duplicates, then everything else is your non-duplicate.

with c as (
    select *,
    row_number() over (partition by PostSur, Name  order by ID) as rowID
    from Aron_Reporting.dbo.Customer_NewSort
)
select c.*
    from c
    inner join c c2 on c.PostSur = c2.PostSur AND c.rowID > 1 AND c2.rowID = 1 as rowID
     order by PostSur

This code partitions the row numbers by PostSur but also name, that way every person with a different name, or PostSur starts the numbering over. I joined the results back to the anchor, but you really don't need that necessarily.

I think I understood your requirements, hopefully this at least gets you close.


SQL and Multiple Indexes Consider the following table:

i | a | b | c
--------------
0 | 1 | 2 | 3
1 | 4 | 5 | 6
2 | 7 | 8 | 9
3 | x | y | z

The table, in the end, will have several thousand rows, and even more columns then described here (20+).  I plan on adding indexes to the table, but cannot figure out which indexes to make.  In the end, I'll be doing SELECT statements with different combinations of these, and sometimes only on 1 column.  Is the most efficient way to make the indexes:

(A) Index each column separately, eg. aIndex(a), bIndex(b), cIndex(c), etc..

(B) An Index for each type of select statement, eg. aIndex(a), abIndex(a,b), acIndex(a,c), etc..

(C) Just index them all together into one, eg. tIndex(a,b,c).

Indexes are one of those things I'm still getting a grasp on, so I apologize if this seems like a silly or simplistic question.  What I'm trying to avoid is unnecessary indexes as they will increase disk-space and INSERT times dramatically.

Just having the table definition and a few rough examples is probably not enough information to get you the answer you want. You need to know which queries are going to be the most heavily used.

For example, you only get 1 clustered index, and that's the most powerful index, so you only have one option for that.

Are all/most of your queries likely to have a particular column (i?) involved? Do you join to other tables? On a common column? That would lead me to think i should be the first column in the clustered index. The subsequent columns in that index, should be the next most-commonly used columns (taking cardinality into consideration). You can put all the columns in the clustered index if you like, as that's effectively the order the data is stored in. So the order of the columns is NB.

Then, single column indexes can reference the clustered index, so that helps. But your choice of those non-clustered indexes and the number/order of columns also depends on the expected usage.

Also, is this a data entry or reporting table? Data entry = more indexes make it slower to enter data. Reporting = index the hell out of it, if needed!


Can SQL Server be configured to backup to a network share through a separate NIC? We're in the process having our SQL Server instances backup to a network share, but our network admins want to try and divert the bandwidth from the backups through a different network segment. I was curious if it were possible to configure SQL Server to run all backups through a secondary NIC?

Most of our servers are virtualized (so presenting the NIC isn't a problem) and we're running on SQL Server 2008 R2, 2012, & 2014 throughout our infrastructure.

Yes, see the Microsoft whitepaper A Technical Case Study: Fast and Reliable Backup and Restore of Multi-Terabyte Database Over the Network (DOC). On page 15, they explain how to separate out the backup targets onto multiple subnets and volumes.


How often should we use Update Statistcs.(More frequent or Not) We usually use Ola Hallengren's solution that takes care of Update Statistics which is inside the Index optimize job that we have. We have it scheduled for every Sunday midnight in all servers (1500+) as a standard practice.

Now, there are a few servers where there are a lot of performance troubles during the middle of the week and we run sp_updatestats for that particular database when an incident is raised for performance issues. We have a huge environment with all versions in use, 2000 to 2016. It is the 2012 & 2014 versions where we have been running the update stats manually for specific database mid-week.

Recently, we also had to schedule sp_updatestats for every day for some very active databases due to recurring troubles.

My questions :

How often should we have it scheduled?
What are the drawbacks for scheduling it too often?
Is there a way of evading the drawbacks with the regular updates running so frequently as I heard it takes more time to compile and might degrade performance for sometime?

Please help me with the experience you had with this.

How often should we have it scheduled?

You will need to decide this for yourself.

It will depend on the modification (insert update delete)of your
columns that are related to that statistics in question. You can use my script TSQL to Find Status of SQL Server Statistics to find the amount of rows modified since last updated statistics.
Is your auto update statistics turned on?
Size of your table because of the threshold when auto update statistics get triggered.  Understanding When Statistics Will Automatically Update by Erin Stellato explains in details.
If you find that your auto update statistics is not good enough you should consider manually updating statistics.
If you have large tables for pre 2014 version consider Trace Flag 2371. Be mindful that it is default in SQL 2016. Erik Darling talks about it in Changes to auto update stats thresholds in SQL Server 2016.

What are the drawbacks for scheduling it too often?

Kendra Little explains in UPDATE STATISTICS: the Secret IO Explosion with some tips how to do the same more efficiently.

Is there a way of evading the drawbacks with the regular updates running in parallel?

If you meant running update statistics in parallel I suggest 2 things to be aware of.

Do not run statistics from same table in parallel, it will cause blocking.  There has been improvement on this since SQL 2014. Read more details in Improved Support for Parallel Statistics Rebuilds by Jonathan Kehayias and a in Boosting Update Statistics performance with SQL 2014 & SQL 2016 by Parikshit Savjani of the Microsoft Tiger Team.
Scan phase of update statistics go parallel and has been improved in SQL 2016. Be careful with number of sessions running in parallel, you might cause other sessions waiting for available schedulers. See the related Q & A Parallel Statistics Update.

Based on the edit of your number 3 question I suggest you read Auto Update Stats Async Enabled.

Additional resources:

When To Update Statistics?
SQL Server Statistics Questions We Were Too Shy to Ask by Grant Fritchey
Another Reason to Avoid sp_updatestats by Erin Stellato


Train Fare Database Normalisation I have a database to normalize. The database has details of train ticket fares.

The trains have different fares depending on the distance and I have to store these fares in the database

It is not an option to calculate the fare based on distance because the fare is followed strictly by the fare chart. The only way I can think of to store the values is like this is in the following table. However using this half the table is going to have NULL values.

+--------------+-----------+-----------+-----------+-----------+
| Station Name | Distance  | Station A | Station B | Station C |
+--------------+-----------+-----------+-----------+-----------+
| Station A    |         0 | 0         | 10        |        25 |
| Station B    |         1 | NULL      | 0         |        10 |
| Station C    |         2 | NULL      | NULL      |         0 |
+--------------+-----------+-----------+-----------+-----------+

Can you suggest a better way to normalize the database.

You want to convert the pivot table into a normalized table.  Store each cell in your chart as a row in a FARE table.  The business key of this table is Origin + Destination and the non-key columns are Distance and Amount.


How can I make UPDATE to return an error if no rows are affected in Postgresql? In my application code, I have an ordinary UPDATE statement to update some row's properties. However, I've encountered a bug where this statement was provided with an incorrect ID and no row was affected.
This bug was silent because UPDATE statement that affects no rows still finishes successfully. I want to prevent such bugs in the future, and to do so, I want to instantly and loudly fail if this happens. One option that I have is to include a RETURNING statement and check it in the application code, but before going with it (and updating the whole application codebase where this pattern occurs — which is a massive change), I wanted to check the alternatives.
Is there a way to write an UPDATE statement in such a way that it produces an error if now rows are affected?

It would be possible to put the UPDATE into a CTE and then do something with the number of results:
WITH cte AS (
  UPDATE ...
  RETURNING ...
)
SELECT 1 / COUNT(*) FROM cte;

If the division by zero is too obscure for you, write a function that raises a proper error.
Personally, I would use a plain UPDATE, and then in my application, wrap all such SQL calls in a function that checks the number of rows. (PG returns that number from every UPDATE, but not every database driver supports this.)


How to ensure a trigger is fired after variable number of inserts? I have a 1:N relationship between two tables - let's call them "foo" (the parent table) and "bar". I have some triggers on both "foo" and "bar" tables. I insert data in a single transaction, first to "foo" and then to "bar". I need to check whether percentages saved in "bar" sum up to 100% ("check_percentage" trigger) after execution of all the "check_bar" triggers (they check whether "bar" meets some criteria and discards it, including the percentage, if not) - there is one per "bar" table.

First I though of "check_percentage" as a trigger on "foo", but it is not a constraint trigger, so DEFERRABLE INITIALLY DEFERRED shouldn't work according to the manual, and I didn't find any other way how to postpone its execution. The only solution I already found was to trigger "check_percentage" on "bar" instead, after "check_bar". If I check the percentage after each insert and "check_bar" execution, I can easily find cases where there are more than 100%, but for less than 100% (this is more likely to cause the problem), I would need to ensure that this instance of "bar" is not the last. I can theoretically get this information, but it would require a lot of extra logic. Triggering "check_percentage" in the end of the transaction would be ideal, but I don't know how to achieve it.

You could implement all your inserts on those tables via stored procedures. That would allow you to execute your check_percentage process at the end of said stored procedure. It would also allow you to impose additional conditions on said execution.


The difference between sys/ and sys@xe (Oracle) Can anyone tell me in detail,what is the difference between sqlplus sys/ as sysdba and sqlplus sys@xe as sysdba?For the records the database is installed on the same machine as the client.

sqlplus sys/ as sysdba connects directly to the database without using networking (TNS. This type of connection is called a bequeath connection.

sqlplus sys@xe as sysdba connects through the listener.


MongoDB: Database too big to resync? we have a mongodb cluster which takes about 3.7TB on the filesystem. One of our secondary nodes died, so we replaced it and started the resync.

The resync was started on 12 Jul 2017 and took until yesterday (6 Aug 2017) I can see the index rebuilding and immediately after that, I got the magical message (there are dbs before the test one):

[rsSync] initial sync cloning db: test
[rsSync] build index on: test.t1 properties: { v: 1, key: { _id: 1 }, name: "_id_", ns: "test.t1" }
[rsSync]         building index using bulk method
[rsSync] build index done.  scanned 0 total records. 0 secs
[rsSync] initial sync data copy, starting syncup
[rsSync] oplog sync 1 of 3
[ReplicationExecutor] syncing from: fr-mongo-c3-p3:27017
[rsBackgroundSync] we are too stale to use fr-mongo-c3-p3:27017 as a sync source
[ReplicationExecutor] syncing from: de-mongo-c3-b1:27017
[ReplicationExecutor] syncing from: de-mongo-c3-p1:27017
[ReplicationExecutor] syncing from: de-mongo-c3-p2:27017
[ReplicationExecutor] could not find member to sync from
[rsBackgroundSync] too stale to catch up -- entering maintenance mode
[rsBackgroundSync] our last optime : (term: 10, timestamp: Jul 12 05:29:26:14)
[rsBackgroundSync] oldest available is (term: 10, timestamp: Aug  3 06:24:29:9f0)

Now what can I do with this? First thing that comes to mind is to increase the oplog size and repeat the process. Second thing that comes to mind is to take the backup data, copy them to this new node and if this process takes less then 4 days, I might be able to make it.

Any other ideas? Or is my assumption completely wrong? Thanks for answers!

(Mongo version is 3.2.10, OS is Ubuntu Trusty)

Yes, you are correct in both cases! Actually, it would be best to do both!

First, increase the opLog size (at least 4x, 10x if you have space) and after that use backup method to copy all data to that node (lvm snapshot is best).

During that time what copy takes, your new opLog (what is this time much bigger) will collect all changes and still keep that starting point.


Help on deciding how to pick Correct Index I would like to ask for some help to create correct indexes for this query:

SELECT DISTINCT D.TypeID
FROM dbo.Sizes AS S
WHERE EXISTS (
        SELECT 1
        FROM #PermissionsTable AS PT
        WHERE PT.ProductID = S.ProductID
            AND PT.CountryID = S.CountryID
        );

This query is used in procedure and #PermissionsTable is created in there based on passed in criteria.

I've tried creating various indexes on my table, but I'm always getting only Index Scans, I'd like to get Seeks of course. For instance:

CREATE NONCLUSTERED INDEX idx_Sizes_ProductID_CountryID_TypeID
    ON dbo.Sizes (ProductID, CountryID, TypeID);

CREATE NONCLUSTERED INDEX idx_Sizes_CountryID_ProductID_TypeID
    ON dbo.Sizes (CountryID, ProductID, TypeID);

-- I've added TypeID into INCLUDE part, because I'm not using it in any clause except SELECT statement.
CREATE NONCLUSTERED INDEX idx_Sizes_ProductID_CountryID
    ON dbo.Sizes (ProductID, CountryID) INCLUDE(TypeID);

CREATE NONCLUSTERED INDEX idx_Sizes_CountryID_ProductID
    ON dbo.Sizes (CountryID, ProductID) INCLUDE(TypeID);

And on #PermissionTable I've tried creating both Clustered and Non Clustered indexes on ProductID, CountryID or CountryID, ProductID.

But I'm always ending up with Scans.

Sizes table has hundreds of millions of rows. Permissions table has around 400.000.

At the moment I've added WITH (FORCESEEK) hint next to FROM dbo.Sizes AS S, which forces seek, but I'd like this to be done by SQL Server engine.

Any literatur, tips, anything would be helpful.

Thanks!

Update: added execution plan

First of all, I have to ask: Why are you doing this? If you have a performance issue, pursue it. However, you only mention that you are seeing scans instead of seeks. Scans are not always bad - they can be the most efficient method of pulling large amounts of data since sequential file access is less costly in I/O terms than random access.

The commenters are correct - the query as written will always produce a scan. The outer query will always produce a scan since the query has neither a join nor a sargable condition in the WHERE clause to limit it. The inner query is likely using a scan because of the number of times it is executed and the probability of returning more than 30% of the rows over the course of the query. (30% is roughly the threshold for choosing a scan over a seek.) A join may produce a better plan, but it really depends on the distribution of values in the temporary table.

I can think of a couple of things you might try:

If you're running this query repeatedly to test it, make sure you've added OPTION(RECOMPILE) to the end while you're testing. This will force it to re-evaluate the query instead of using a cached plan. If you don't, the optimizer may not see that an option other than a scan is available.
Try using a CROSS APPLY instead of a join. You can use a subquery as the target of the APPLY; I've used that technique before with stubborn queries with good results. Your query would then resemble SELECT DISTINCT D.TypeID FROM dbo.Sizes S CROSS APPLY (SELECT TOP(1) 1 FROM #PermissionsTable PT WHERE PT.ProductID = S.ProductID AND PT.CountryID = S.CountryID) P. Keep in mind that the APPLY will still be run as many times as you have rows in Sizes, so even though you're using a seek, it might still produce a poor plan.

If neither of those has an effect, you may be able to improve performance by changing the way you think about the query. How many distinct ProductID/CountryID combinations are there in #PermissionsTable? Instead of searching on all 400,000 rows, can you select only those distinct combinations into another temp table, then join that to Sizes? Can you select only the distinct combinations of ProductID/CountryID/TypeID from Sizes? Can you reverse the query so that Sizes is in the inner query? The goal of all of these would be to reduce the number of rows that the query must retrieve.


Results of SAMPLE clause using a DBlink between 2 Oracle databases not randomised I'm using the SAMPLE clause to retrieve random rows from a table. It works fine except when the table is from another database. I'm using a database link between 2 Oracle 11g databases (11.2).

This query returns different random rows everytime I run it:

select * from myTable sample(10);

But this one always returns the same rows and in the same order:

select * from myTable@myDbLink sample(10);

I expected at least the same behavior as the tables are on the same version of Oracle. Why isn't it working? Is there a specific way to write this query so it can work with the link?

According to the Oracle documentation for the SELECT ... SAMPLE clause, the sample is random; it is just a coincidence that you are always getting the same rows on the local machine.  You could try adding the SEED(x) clause, where x is a number from 0 to 4294967295; but that may not help going across a database link.

select * from myTable@myDbLink sample(10) seed(42);

Per the Oracle documentation:

  SEED seed_value Specify this clause to instruct the database to attempt to return the
  same sample from one execution to the next. The seed_value must be an integer
  between 0 and 4294967295. If you omit this clause, then the resulting sample will
  change from one execution to the next.

Notice it is still not guaranteed!


How to disable Oracle OCI client transparent reconnect I have an application I wrote that uses OCI to connect to an Oracle database.  The database has recently been upgraded to Oracle 18, and since then database searches in the application randomly stop working.

I have narrowed the problem down to this.  When the app connects to the DB it runs some ALTER SESSION commands to disable case-sensitivity in LIKE conditions (so that I can search for "smith" and still find "Smith").  After some hours, the network drops the TCP connection.  The next morning when someone tries to access the system, the Oracle client transparently reconnects and executes the query.

The problem is that this reconnection happens without my code knowing about it, apparently by the Oracle client itself, and it does not re-run the original ALTER SESSION commands after reconnecting.  Our DBAs have supplied me with an audit log that shows the session ID changing, a new LOGON attempt and a single ALTER SESSION running to set the timezone (which does not come from my code, and also appeared in the original connection).

This means that my application issues queries to the database as per normal, unaware that there has been any interruption, but suddenly they become case-sensitive and the users start complaining that the search isn't returning any results.

With the previous Oracle server version my code would receive an error if it had been disconnected, and the code would try to reconnect and as part of the reconnection, it would run the ALTER SESSION commands again so that there was no change to the behaviour of the system.

I can't find anything about this auto-reconnect feature or how to adjust it, so is there some way to either preserve the ALTER SESSION changes so they are re-applied after the reconnection, or to disable this behaviour and return an error to the code if the connection is lost as used to happen?

The client is an older 11.2 one and upgrading it isn't really practical, but since it worked before the server upgrade I would've thought that version would be ok.

It seems that Transparent  Application Failover (TAF) is enabled for your connection. If TAF is enabled the Oracle client will
reconnect you to the database or an alternate database if the connection between the client and the server is disrupted.
You use an OCI client and for this it is possible to register a
failover callback function
that is called after the client reconnects.
In this callback function you can issue the 'ALTER SESSION' command you need. But maybe you don't want to use TAF.
So first the DBA should check if your session uses TAF. An appropriate query can be found in the
Net Services Administrator's Guide
SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*)
FROM V$SESSION
GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;

With the following sample outpu
MACHINE              FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER   COUNT(*)
-------------------- ------------- ----------      ---           ----------
sales1               NONE          NONE            NO            11
sales2               SELECT        PRECONNECT      NO             1

FAILOVER_TYPE NONE tells us, that no TAF is configured. FAILOVER_TYPE different to NONE tells us that TAF is configured for these sessions.
If your the session uses TAF then you should find out, where it is configured. It can be configured on client side or on server side.
You can check your 'tnsname.ora' file or use your 'tnsping' utlity to check your client configuration. If you find something similar to this  example from
Net Services Administrator's Guide
sales.us.example.com=
 (DESCRIPTION=
  (LOAD_BALANCE=on)
  (FAILOVER=on)
  (ADDRESS=
       (PROTOCOL=tcp)
       (HOST=sales1-server)
       (PORT=1521))
  (ADDRESS=
       (PROTOCOL=tcp)
       (HOST=sales2-server)
       (PORT=1521))
  (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com)
     (FAILOVER_MODE=
       (TYPE=select)
       (METHOD=basic))))

then TAF is configured because of FAILOVER_MODE=(TYPE=select). If this is the case you should configure an appropriate connect string without TAF.
But even if no TAF is configure on client side it could be configured on server side for this service. So do the following query
select NETWORK_NAME, FAILOVER_METHOD, FAILOVER_TYPE
from DBA_SERVICES
where NETWORK_NAME is not null;

The following example output shows that TAF will be used for (SERVICE_NAME=sales.us.example.com) even if it is not configured on the client.
NETWORK_NAME             FAILOVER_METHOD      FAILOVER_TYPE
------------------------ -------------------- --------------------
sales.us.example.com     BASIC                SELECT
sales3.us.example.com    NONE                 NONE

In this case TAF will be used for (SERVICE_NAME=sales.us.example.com) even if TAF is not configured on the client. So you  have to use a service that does not have configured TAF, in out example 'sales3.us.example.com'
is such a service.
Service can be created with the DBMS_SERVICE package but in most cases they are created by some additional tools like Oracle Real Application Cluster.
One can display the services created by the RAC for a database 'salesdb' by executing the following command on the database server
srvctl config service -d salesdb

The following output (that depends on the database version)  shows that RAC will create a TAF service after start of the database
Service name: sales.us.example.com
Service is enabled
Server pool: SALESDB_sales.us.example.com
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 5
TAF failover delay: 2
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Preferred instances: sales2-server,sales2-server
Available instances:
Service is enabled on instances: SALESDB1,SALESDB2

you can create a new service 'sales3.us.example.com' without TAF with the following command
srvctl add service -d salsedb -s sales3.us.example.com


How often do you back up the distribution database for transactional replication when sync with backup is set to true for the distribution database? I'm trying to figure out how often to back up the distribution database when "sync with backup" is set to true.  If I understand this correctly, sync with backup ensures the distribution database can retrieve all transaction from the publisher's transaction log when/if the distribution database needs to be recovered.  It does this by preventing the publisher's transaction log from truncating records until the distribution database has been backed up.

If this is the case then in order to prevent outrageous growth in the publisher's transaction log, the distribution database needs to be backed up almost as frequently as the publisher's transaction log.

Does this make sense?  If I decide to do back up the distribution database almost as often as I back up the publisher's transaction log is there a good way to estimate the performance impact?

I back up all of my transaction logs at the same time, every two hours.  I haven't had any issues with Log growth since I set that up, and I haven't had any issues with replications because of the log backups.

I think the best thing is to watch for how fast your logs grow, and plan your log backups accordingly.  Alternatively, how fast you want to be able to restore databases will also affect how often you make backups.


How do I login to a Postgres database without a password? Right now my Postgres' pg_hba.conf file has the following lines:

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
#Should allow connection without password
host    all all 0.0.0.0/0   trust
host    all all ::0/0   trust

(I know that this is insecure. I'm just testing, and once it is successful, I'll keep it only specific users & databases)

My understanding was that trust should allow a user to connect without a password.

But when I do psql -U postgres -d postgres -h 127.0.0.1 -w, I get the error message: psql: fe_sendauth: no password supplied

What do I need to do to allow users to connect without a password?

When pg_hba.conf is checked for an authentication request, the first match determines the rule.

Quote from the manual

  The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no “fall-through” or “backup”: if one record is chosen and the authentication fails, subsequent records are not considered

(Emphasis mine)

In your case the lines:

host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

are matched before the trust lines you have added, therefor Postgres requires a password. If you want to disable password authentication completely you have to disable the md5 authentication, e.g. by commenting those lines.


Formal name for functions that use the OVER clause Is there a formal/academic name for the family of functions in Sql Server like ROW_NUMBER(), LAG, LEAD, RANK etc, that make use of the OVER(ORDER BY <PARTITION BY> ) syntax? How much of this is covered by the ansi standard?

I can see on MSDN that some of those are listed as analytic functions, but others are ranking functions.

I ask using Sql Server names, but one of the reasons I want to know is to help me find out how to translate some of these queries for other databases, and gauge support among those databases. The other reason is to help me wrap my head around the concept more completely. Most of my experience pre-dates their Sql Server use, and I find lately I'm too used to old workarounds when one of these functions might be the easy fit. Knowing how to refer to them will help me when reading other things.

I believe the term you are looking for is window functions. Most databases have support for these types of functions.

Window functions belong to a type of function known as a ‘set function’, which means a function that applies to a set of rows. The word ‘window’ is used to refer to the set of rows that the function works on.
Windowing functions were added to the standard SQL:2003 that is managed by the ISO and it was specified in more detail in SQL:2008 For some time, other DBMSs such as Oracle, Sybase and DB2 have had support for window functions. Even the open source RDBMS PostgreSQL has a full implementation. SQL Server has had only a partial implementation up to now, but it is coming in SQL 2012.

Source
A window function is one of:

A rank function.
A distribution function.
The row number function.
A window aggregate function.
The ntile function.
The lead function.
The lag function.
The first-value function.
The last-value function.
The nth-value function.


Need help configuring Memcached to store values in MySQL Cluster I am working on a Memcached implementation with MySQL Cluster 7.2.
I have installed and configured memcached with following parameters:

PORT="11211"
USER="mysql"
MAXCONN="1024"
CACHESIZE="1024"
OPTIONS=""

but Memcached is working irrespective to the MySQL engine
(i.e. when inserting to Memcached it is not storing into database)

Can any one help me out giving parameters so that it will access the ndb engine and store the values in both memcache and also in database?

I am not able to figure out the relation between the two.

The basic docs for using the Memcached API for MySQL Cluster 7.2 start at http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-ndbmemcache.html

On the memcached command line, you need to supply an option like "-E ndb_engine.so".  Memcached has a "pluggable engine" architecture, and the MySQL Cluster component is implemented as an NDB Engine.  When memcached starts up with the correct engine you should see a log message like this:

01-Mar-2012 11:27:27 PST NDB Memcache 5.5.20-ndb-7.2.5 started [NDB 7.2.5; MySQL 5.5.20]
Contacting primary management server