How to deny access to see the logins in a database I create a login in SQL Server 2008 R2, and when that user connects to a database, it can see all the logins in the security logins.  Can I deny access to watch the logins of the database?

Either if it's a Windows or SQL login, then by default, if it has only the PUBLIC role, won't be able to see anything except SA and himself. No other login (Windows or SQL) on the machine will be visible, if he hasn't some other server permissions.

Example for that behavior (both test logins have only the Public role):

So, if a login can see more than himself, it must've had some privileges assigned. It should be in the login properties.

Some other easy way to see it is by scripting the login: right click -> script login as - create to - and see if it has any server role assigned to it (role assignments are at the end of the script).


Query to get data from 2 different tables is taking long time I have this query,

select Column1,Column2, etc..
from Db1.dbo.Tb1 b Where b.Id1 in (
    select distinct Column3 as Id1 from Db2.dbo.Tb2 s where s.Id2 <> ''
    union
    select distinct Column4 as Id1 from Db2.dbo.Tb2 s where s.Id2 <> ''
)

It's taking more then 10 minutes and still processing on a good spec database.

I can't change existing DB or add indexes, Is there anyway I could improve this query ?

It's getting data 2 columns from a table and then based on results getting data from another database's table.

Rewrite your query such a way:

SELECT Column1,
       Column2,
       etc..
FROM Db1.dbo.Tb1 b
WHERE EXISTS (
        SELECT TOP (1) 1
        FROM Db2.dbo.Tb2 s
        WHERE s.Id2 <> ''
            AND b.Id1 IN (s.Column3, s.Column4)
    )


MRG_MyISAM with myisampack tables blocking inserts I'm using INSERT_METHOD=FIRST and compressing all tables that are members of the union but when I try to insert into the MRG_MyISAM table it tries to insert into the second one (instead of the first).  Why?  When I uncompress the 2nd table and insert again, it tries to insert into the 3rd.  It gives an error that the table is read-only which I'm fully aware.  This behavior is unexpected.

mysql  Ver 15.1 Distrib 5.5.37-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1

My MRG_MyISAM table looks like:

CREATE TABLE `ah_sales` (
 `server` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `itemId` smallint(5) unsigned NOT NULL DEFAULT '0',
 `countof` tinyint(1) NOT NULL DEFAULT '0',
 `seller` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `buyer` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `price` int(10) unsigned NOT NULL DEFAULT '0',
 `saleon` int(10) unsigned NOT NULL DEFAULT '0',
 KEY `buyer` (`buyer`),
 KEY `itemserver` (`countof`,`server`,`itemId`),
 KEY `seller` (`seller`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`ah_sales_4_2014`,`ah_sales_4_2013`,`ah_sales_8_2012`,`ah_sales_4_2012`,`ah_sales_12_2011`,`ah_sales_6_2011`,`ah_sales_3_2011`,`ah_sales_12_2010`,`ah_sales_6_2010`,`ah_sales_3_2010`,`ah_sales_11_2009`,`ah_sales_8_2009`,`ah_sales_5_2009`,`ah_sales_2_2009`,`ah_sales_11_2008`,`ah_sales_8_2008`,`ah_sales_5_2008`,`ah_sales_2_2008`,`ah_sales_11_2007`,`ah_sales_9_2007`,`ah_sales_8_2007`,`ah_sales_6_2007`,`ah_sales_5_2007`,`ah_sales_3_2007`,`ah_sales_2_2007`,`ah_sales_1_2007`,`ah_sales_12_2006`,`ah_sales_11_2006`,`ah_sales_10_2006`,`ah_sales_9_2006`,`ah_sales_8_2006`)

ah_sales_4_2014 is uncompressed.

This might be a bug. In essence, it sounds like the Mrg_MyISAM definition must be using the structure of the first member of the UNION clause as the basis for the rest the tables in the list.

Before you report the bug, try reversing the order of the UNION clause so that all the compressed tables are first and the uncompressed one is last

CREATE TABLE `ah_sales` (
 `server` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `itemId` smallint(5) unsigned NOT NULL DEFAULT '0',
 `countof` tinyint(1) NOT NULL DEFAULT '0',
 `seller` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `buyer` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `price` int(10) unsigned NOT NULL DEFAULT '0',
 `saleon` int(10) unsigned NOT NULL DEFAULT '0',
 KEY `buyer` (`buyer`),
 KEY `itemserver` (`countof`,`server`,`itemId`),
 KEY `seller` (`seller`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1
INSERT_METHOD=LAST
UNION=(`ah_sales_4_2013`,`ah_sales_8_2012`,
`ah_sales_4_2012`,`ah_sales_12_2011`,`ah_sales_6_2011`,
`ah_sales_3_2011`,`ah_sales_12_2010`,`ah_sales_6_2010`,
`ah_sales_3_2010`,`ah_sales_11_2009`,`ah_sales_8_2009`,
`ah_sales_5_2009`,`ah_sales_2_2009`,`ah_sales_11_2008`,
`ah_sales_8_2008`,`ah_sales_5_2008`,`ah_sales_2_2008`,
`ah_sales_11_2007`,`ah_sales_9_2007`,`ah_sales_8_2007`,
`ah_sales_6_2007`,`ah_sales_5_2007`,`ah_sales_3_2007`,
`ah_sales_2_2007`,`ah_sales_1_2007`,`ah_sales_12_2006`,
`ah_sales_11_2006`,`ah_sales_10_2006`,`ah_sales_9_2006`,
`ah_sales_8_2006`,`ah_sales_4_2014`);

Then, see if the problem persists. Either way, you may have to submit a short or long bug report.

IMHO, running myisampack across all MyISAM tables is a good idea PROVIDED YOU ONLY SELECT FROM ALL THE TABLES. I do not believe it was meant to be used the way you are doing it (performing INSERTs when there is a mix of compressed and uncompressed). I do not think you will have this problem if all the MyISAM tables were uncompressed.


Conversion failed when converting the varchar value to data type int in SQL Server While getting some data input, for below query, encountered Convertion failed error:-

SELECT a.ProcessInstanceAppianID,
       a.ProcessInstanceDescription,
       CONVERT (INT, RIGHT(a.ProcessInstanceDescription, 6))AS KPI,
       i.KeyPersonKPILead,
       p.BestPersonName                                     AS 'Lead Person'
FROM   InternalUseOnly..processinstance a
       LEFT JOIN InternalUseOnly..ProjectInput i
              ON i.KeyProjectInput = CONVERT (INT, RIGHT(a.ProcessInstanceDescription, 6))
       LEFT JOIN snledit..Person p
              ON p.keyperson = i.KeyPersonKPILead
                 AND p.UpdOperation < 2
WHERE  a.KeyProcessStream = 1827
       AND a.ProcessInstanceCompleted IS NULL
       AND a.UpdOperation < 2

It would be very aiding & adding, if anyone can guide me here.

Thanks in anticipation!

The first thing I would do is identify rows where those 6 characters can't possibly be an integer.

SELECT ProcessInstanceAppianID, ProcessInstanceDescription
  FROM InternalUseOnly.dbo.processinstance
  WHERE RIGHT(ProcessInstanceDescription, 6)
    NOT LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]';

Then fix that data or somehow exclude it from the results. However this gets tricky because you have no control over whether SQL Server will try the conversions before or after the filter (even if the filter is in a CTE or subquery). So you could dump the filtered results to a #temp table, for example, and then run the conversions on the output.

In SQL Server 2012+ you can use TRY_CONVERT() instead.


How do I know what indexes to create for a table? Is there a way I can figure out the best way to know which indexes to create for a table?

Short rules of thumb. (Some of these are created automatically, but can possibly be manually dropped later, depending on your dbms. Don't assume you will always work on PostgreSQL.)

Index every primary key.
Index every foreign key.
Index every column used in a JOIN clause.
Index every column used in a WHERE clause.
Study your documentation to learn the "esoteric" indexing options your dbms supports.

Every primary key means that multi-column primary keys should have a single index covering all columns. PostgreSQL will create this index automatically if you declare a multi-column primary key.

There are many cases in which a single multi-column index gives you better performance than several single-column indexes. Monitor slow queries and do testing to figure out which is which.

Assume that any change to indexing will improve some database activities and degrade others. I find it helpful to have a set of SQL statements that I can profile before and after making changes to indexes. This set includes SELECT, INSERT, UPDATE, and DELETE statements.

There's no substitute for studying the docs for your particular dbms.

CREATE INDEX
Indexes (Note especially the sections on indexing expressions, on partial indexes, and on examining index usage)


SSIS Derived column giving error on CAST function I am trying to use (DT_DATE) DATEADD(dd, -1, GETDATE()) in a derived column. However, by the time I can start writing my expression with (DT_DATE) it turns red and shows an error. Whether it's just (DT_DATE) sitting there or the full expression it throws an error.

Here is a screen shot of the error

Also:

If I remove this one derived column expression the rest of my package works fine, including a different derived column
All I'm doing is taking an Excel file, adding two derived columns to it, then putting into an SQL database

Basically it seems to not want to accept (DT_DATE). I'm sure I'm making some small error here but I've tried everything I can think of and any help would be greatly appreciated.

You are not using the expression you are showing in your question as evidenced by the error message.

  Attempt to parse the expression "(DT_DATE)DATEADD(DAY,-1GETDATE())"
  failed and returned an error

Your expression with problems is the one for Batch_as_of_Date

(DT_DATE)DATEADD(DAY, -1, GETDATE())

If you don't put double quotes around the first argument SSIS tries to parse it as a column. Since you don't have a DAY column in your input columns so you get this error message:

  Attempt to find the input column named "DAY" failed ....

So your expression would need to be

DATEADD("Day", -1, GETDATE())

This is clearly documented in DATEADD (SSIS Expression)


Understanding "Date Last Modified" for "System Stored Procedures" Whilst reviewing code on a SQL 2008R2 Instance I inspected the Date Last Modified of the System Stored Procedures of the databases. The ones in the DBO schema had a datetime that predates the installation however the ones in the dbo schema come later than the installation date. Specifically the machine was built December 2013 and the date of these SPs is 09/07/2014 17:16 (both for Date Last Modified and Create Date). The DB in question was also created December 2013.

Now I assume this was something benign, but how can I understand and determine what caused this date stamp?

I've checked the Windows logs but can't find any installations going on at that time.

According to BOL, the modify_date from sys.all_objects shows:

  Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or modified.

However, if you have a maintenance plan that recompiles stored procedures, using sp_recompile, or have system stored procedures that run with the WITH RECOMPILE option, this will also cause the modify_date to update.

There was a Connect Item raised about this, but it was closed as 'Won't Fix'.

This could potentially explain your Date Last Modified issue.


Avoiding Key Lookup when using MIN and MAX I have a somewhat simple table that takes

 Id guid (pk)
 SoftwareId guid (FK to software table)
 OnlineUsers int
 OnlineDevices int
 SampleDate datetime
 SDHour (persisted computed field, holding just the hour)
 SDMinute (persisted computed field, holding just the minute / 5)

The goal is between two dates draw a line graph of samples every 5 minutes, sometimes there is more than one sample every 5 minutes so to deal with that we just take the max amount of OnlineUsers and OnlineDevices

SELECT
    MAX(OnlineUsers), -- Only select the most users
    MAX(OnlineDevices), -- Only select the most devices
    SDHour, -- Used to allow creating the graph split into 5 minute increments
    SDMinute, -- Used to allow creating the graph split into 5 minute increments
    SoftwareId,
    Software.FriendlyName as FriendlyName, -- Each software peice has a name
    Software.MaxUsers as MaximumSupportedUsers, -- Maximum supported users
    Software.MaxDevices as MaximumSupportedDevices, -- Maximum supported devices
    DATEDIFF(SECOND, @dtStartDate, min(SampleDate)) as OrderTime -- Used to sort
FROM SoftwareUserSamples
JOIN Software on Software.Id = SoftwareUserSamples.SoftwareId-- Get sw details
LEFT OUTER JOIN SoftwareTypes on Software.SoftwareTypesId = SoftwareTypes.Id
WHERE Software.ClientId = @gClientID -- Multi tannancy
AND (@bFilterHiddenRows = 0 OR Software.HideByDefault = 0) -- hide some rows
AND SoftwareUserSamples.SampleDate > @dtStartDate -- Don't get all rows
AND SoftwareUserSamples.SampleDate < @dtEndDate -- Don't get all rows
GROUP BY
    SDHour,
    SDMinute,
    SoftwareId,
    Software.FriendlyName,  --Need to group so we can use data in the select
    Software.MaximumSupportedUsers,
    Software.MaximumSupportedDevices,
    UserDivsor
ORDER BY
    OrderTime Asc,
    SoftwareId

This query was perfectly fine when we had a couple of hundred thousand rows. However now the data has grown to 42,000,000 rows it started to take aronud 10 seconds for the query to run for just a 20 day period.

Looking at the query plan it looks like because I'm using the MAX() on both the OnlineUsers and OnlineDevices. If I remove these then the time to execute drops to <1second if I put them back in it jumps to >10 seconds. NOTE Most of the time there is actually only 1 sample per 5 minutes

Any suggestions on how to avoid this either by modifying the query or addition of indexes. The only thing I can't do is change the output format.

The Indexes I currently have are

Clustered on
Id

Non-Clustered on
SoftwareId
SampleDate

I would rewrite the query like this:

SELECT
    sus.MaxOnlineUsers, -- Only select the most users
    sus.MaxOnlineDevices, -- Only select the most devices
    sus.SDHour, -- Used to allow creating the graph split into 5 minute increments
    sus.SDMinute, -- Used to allow creating the graph split into 5 minute increments
    s.Id AS SoftwareId,
    s.FriendlyName AS FriendlyName, -- Each software peice has a name
    s.MaxUsers AS MaximumSupportedUsers, -- Maximum supported users
    s.MaxDevices AS MaximumSupportedDevices -- Maximum supported devices
    -- DATEDIFF(SECOND, @dtStartDate, min(sus.SampleDate)) as OrderTime -- Used to sort
FROM dbo.Software AS s -- Get sw details
--Not used: LEFT JOIN dbo.SoftwareTypes AS st ON s.SoftwareTypesId = st.Id

--- Run this once for each software:
CROSS APPLY (
    SELECT x.SDHour, x.SDMinute, x.UserDivsor,
           MIN(x.SampleDate) AS OrderColummn,   --- Just order by this column
           MAX(x.OnlineUsers) AS MaxOnlineUsers,
           MAX(x.OnlineDevices) AS MaxOnlineDevices
    FROM dbo.SoftwareUserSamples AS x
    WHERE x.SoftwareId=s.Id
      AND x.SampleDate > @dtStartDate -- Don't get all rows
      AND x.SampleDate < @dtEndDate -- Don't get all rows
    GROUP BY x.SDHour, x.SDMinute, x.UserDivsor) AS sus

WHERE s.ClientId = @gClientID -- Multi tannancy
  AND (@bFilterHiddenRows = 0 OR s.HideByDefault = 0) -- hide some rows

ORDER BY
    sus.OrderColumn,
    s.Id;

The CROSS APPLY construct lets SQL Server "loop" over each row in dbo.Software and for each query in that table, it runs an aggregate query on the much larger dbo.SoftwareUserSamples table, for which we'll optimize with the following index:

INDEX ON dbo.SoftwareUserSamples (SoftwareId, SampleDate, SDMinute, SDHour, UserDivsor)
    INCLUDE (OnlineUsers, OnlineDevices)

This should give you a nice, efficient seek (range scan, really) on (SoftwareId, SampleDate).

I removed the DATEDIFF expression and just sorted by the MIN(SampleDate) expression instead. It'll make your code a little more readable and perhaps even save you a few microseconds of CPU time. :)

Just crossing Ts and dotting Is, here's an index recommendation on dbo.Software as well, although I suspect this table doesn't really contain that many rows:

INDEX ON dbo.Software (ClientId, SoftwareTypesId)
    INCLUDE (Id, FriendlyName, HideByDefault, MaxUsers, MaxDevices)

A few best practices that I could gleam from your post:

Every table (with very few exceptions) should have a clustered index. It doesn't absolutely have to be unique if that's a problem.
Always prefix the table with the schema name.
Qualify all the column names with table aliases, so you can easily see which column belongs to which table.
If you're on SQL Server 2014/2016 Enterprise Edition or on 2016 SP1+, you should really try putting a columnstore index on the SoftwareUserSamples table for performance.


Why mysql 5.5 slower than 5.1 (linux,using mysqlslap) my.cnf (5.5 and 5.1 is the same) :

back_log=200
max_connections=512
max_connect_errors=999999
key_buffer=512M
max_allowed_packet=8M
table_cache=512
sort_buffer=8M
read_buffer_size=8M
thread_cache=8
thread_concurrency=4
myisam_sort_buffer_size=128M
interactive_timeout=28800
wait_timeout=7200

mysql 5.5:

..mysql5.5/bin/mysqlslap -a --concurrency=10 --number-of-queries 5000
      --iterations=5 -S /tmp/mysql_5.5.sock --engine=innodb
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 15.156 seconds
        Minimum number of seconds to run all queries: 15.031 seconds
        Maximum number of seconds to run all queries: 15.296 seconds
        Number of clients running queries: 10
        Average number of queries per client: 500

mysql5.1:

..mysql5.5/bin/mysqlslap -a --concurrency=10 --number-of-queries 5000
      --iterations=5 -S /tmp/mysql_5.1.sock --engine=innodb
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 13.252 seconds
        Minimum number of seconds to run all queries: 13.019 seconds
        Maximum number of seconds to run all queries: 13.480 seconds
        Number of clients running queries: 10
        Average number of queries per client: 500

Why mysql 5.5 slower than 5.1 ?

BTW:I'm tried mysql5.5/bin/mysqlslap and mysql5.1/bin/mysqlslap,result is the same

You may find this surprising, but MySQL 5.1 may outperform MySQL 5.5 under certain circumstances.

Percona performed a bake-off among multiple releases of MySQL

MySQL 4.1
MySQL 5.0
MySQL 5.1 (with built-in InnoDB)
MySQL 5.1 with InnoDB-plugin
MySQL 5.5
MySQL 5.6

All tests were performed with MySQL unconfigured (In other words, no my.cnf was made). The results?

MySQL 4.1 performs the best single-threaded
MySQL 5.1 with InnoDB plug-in scales on multiple cores better than 5.1 InnoDB built, 5.5 and 5.6

If you want newer versions of MySQL to perform better, you must tune for it. In fact, I described in the DBA StackExchange the idea of performing a MySQL Bakeoff.

What do I mean tune for it?

In MySQL 5.5, there are new InnoDB options for utilizing more dedicated read threads, write threads, and overall I/O capacity. This can engage more CPUs in multicore servers. Left unconfigured, MySQL 5.5 would operate on the same level playing field, in most cases, as older versions of MySQL. Sometimes, it could perform worse.


Spool query output to file (SQLPlus) This is not a duplicate of "Spool query to file without output on screen"

Imagine I am spooling the below in a query:

SQL> select 'alter user '||USERNAME|| ' account lock password expire;' "Expiring Password" from dba_users where lower(username) in ('gunner','gunnersa','gunnerx3');

Expiring Password
--------------------------------------------------------------------------------
alter user GUNNER account lock password expire;

Now I have spooled off the value and saved it to a file. I will be running it as a separate file for the expiring users. When I do that, I get some basic errors about the SQL command on the dynamic query.

Is there a way that we can get only the - "alter user GUNNER account lock password expire;" from the output from SQLPlus to a file?

I use Solaris (SunOS sintscdldmu004 5.11 11.3 sun4v sparc sun4v)

To turn that off, use set heading off

It then should result in:

SQL> select 'alter user '||USERNAME|| ' account lock password expire;' "Expiring Password" from dba_users where lower(username) in ('gunner','gunnersa','gunnerx3');

alter user GUNNER account lock password expire;

You probably also want to use:

SET FEEDBACK OFF
SET TRIMSPOOL ON
SET TERM OFF
SET NEWPAGE 0

To get a "cleaner" SQL script when there are more than just three rows.


What platform for a large record based mathematical database? Hi I am a civil engineer with some experience of programming but I am not familiar with the vast range of options available today. Hope you could give me any pointers the best way to proceed.

I want to make and query a database of ground level survey measurements in a grid format. There will be many measurements for each grid location at various times through the earthmoving job so there is a 4th dimension of time.

The observations will most likely be read in from a text file. In each record there will be an (2 x integer) grid position (row and column) a (floating point) ground level and various string information codes (maybe up to 30 characters in total).

The grids could be about 10000 rows x 10000 columns. Not every location on the grid would be have a record in each survey, but they would typically have up to a hundred records. A lot of the grid locations would have no records at all (the site will not be perfectly rectangular).

I want to search the records, extract data and do calculations eg calculate the lowest or highest ground level for each grid location.
I am fairly confident I would have the ability to program this fairly simply in a language like FORTRAN, BASIC or C using arrays. A lot of the array elements would be empty though and I am guessing that this is not the right way to do it though and big databases like this need special tools that I will have to learn how to use.

I am thinking of possible options for the platform -

Use a database program. I am not familiar with how powerful these can be but I imagine they would have a lot of overhead with GUI.
Use SQL? This I don't know much about but it seems to be the language for databases. I have always used imperative languages rather than declarative and as I understand from wikipedia that SQL is declarative I am a bit nervous of the change. I don't fully understand the process for using it. Is there a compiler that makes console programs? Is the database stored on disk? Sorry for such stupid questions.
Use an API like c-treeACE? I think this may be the way to go offering me the familiarity of a "do this, then do that" language (unfortunately this is the way I think as an engineer!). But I am hoping that the behind the scenes memory and processing management offered by the API would be superior to what I could achieve with huge arrays.
Or could i do it with an object orientated language and let the computer worry about the storage requirements. eg if i stored the records as objects with methods and properties that would help me get the results I need out of each record - would it be a huge bloated program compared to 3)

There are likely to be hundreds of millions of records and I want to be able to query and process them in minutes not hours (preferably seconds!) on a modern PC running windows.
To be more specific mine is an i7 processor with 6Gb ram and 120Gb SSD running Windows 7 64 bit.

Hope someone has time to share a couple of words of wisdom with a newbie.

There are a number of options and please don't limit yourself to my answer here.  In particular you may find array-native databases to be of help.  My answer is going to be specifically about your questions on SQL-based databases.

It sounds to me like this is a question of geospacial information.  SQL-based databases are in fact used in such fields quite well, but this is also a specialist field within databases.

Among the SQL databases in this area, PostgreSQL, with the PostGIS add-on is considered one of the best.  If I were you, this is where I would start.  The primary advantage of SQL is that it preserves flexibility down the road regarding re-use of your data for uses you haven't thought of yet.  Doing this with good geospacial support means that you can calculate distance across a large area without worrying about the specifics of spherical trig.

Of course this only becomes a factor with very large grids.  For smaller grids, where the curvature of the earth can be disregarded, PostgreSQL also has a range of geometric types including points on a coordinate system which can be used.  I mention this because it isn't clear how large of an area is being surveyed and whether one can assume plane geometry or not.

Even so PostGIS may still simplify things by allowing representations and calculations on 3- and 4-dimensional geometric coordinate systems.

Also note that you say your sites are not necessarily square.  In PostgreSQL one thing you can do (either using the geometric types or PostGIS) is define a non-rectangular boundary to each site so you can check to make sure a point is inside the bounds of the site before saving the measurement.

Declarative Language Impacts

This concern I think is overblown.  People can and do write SQL queries as if they are a part of the imperative language of the program they are calling them from.  For most of your queries it won't matter.

What people mean by a declarative language is that within a query, the structure tells the database what information you want, not how to get it.  This is important when you want complex information from the database because basically it means that if you can ask the right question (and your data is valid) you will get the right answer.

The big difference that occurs however is that long SQL queries are easier to debug than long imperative subroutines, simply because one can more quickly narrow down where in the query the malfunction occurs.

How this would work

Chances are if you go this route you'd have a database and a program written in a language of your choice.  The program would send queries to the database and get the answers back.  You can also (in PostgreSQL and many other relational DB's) put your queries inside functions which can then be called by the application, giving more of an imperative or functional interface.  Data would be stored on disk and accessed from a separate piece of software than your program.  You could also connect with another program (from MS Access to pgAdmin) and run queries or generate reports.

In essence you can think of the RDBMS as a "math engine" which manages your data, and your program interacts with it to do what you need.


MySQL restore dump takes too long to complete, how DBAs deal/replicate large databases? Due to coronavirus my company is forced to scale down and cut costs. Long story short, I went the route of least disruption I set the original database server (that's going to be turned off for good in 2 weeks) as a MASTER replicator, locked all tables for reading, dumped the data which resulted in a 34GB .sql file and unlocked the tables. Afterwards I copied the file over to the other server and ran mysql import through mysql-client using the following code

cat default-start-import.sql newdatabase.sql default-end-import.sql | mysql -uroot -p newdatabase

With the contents of default-start-import.sql and default-end-import.sql being the following

default-start-import.sql

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;

default-end-import.sql

COMMIT;
SET autocommit=1;
SET unique_checks=1;
SET foreign_key_checks=1;

The problem is, this 34GB dump has been running for 2 days and by looking at the processlist and the amount of tables I have, I'd say its at about 50% import. And this supposedly slave server is not doing anything else at the moment. It's idling.

The CPU usage my MySQL is at 9% at worst and memory usage is at 44% at worst. Looking at the queries it takes about 1-2 seconds to insert a row. I have tables with millions of rows. If each row takes a second this import will take years to complete. I've set vm.swappiness to 10 in sysctl to reduce hard drive swapping and the server runs with steady 2.4 load, which is largely - fine. I wouldn't say it's overburdened.

I've disabled slow query log

I've set innodb_flush_method = O_DIRECT I've given innodb a buffer of 20 gigabytes

Is there anything else I could do to speed up the process. This feels like an insane amount of timewaste. It's just 34GB, I'm sure there are organizations that bring up slave servers with hundreds of gigabytes of databases in no time and here I am struggling on 30gb database. How?!?

Large databases should be backed up physically (i.e by copying the physical database files) instead of  logically (i.e by exporting in SQL or something similar). If you use Oracle MySQL Community Edition, you should use xtrabackup. If you use MariaDB there is a integrated feature in the last releases: https://mariadb.com/kb/en/mariabackup/.


Fix Oracle Database chained rows A Oracle 12c Database has a table with Chained rows:

select
   owner              c1,
   table_name         c2,
   pct_free           c3,
   pct_used           c4,
   avg_row_len        c5,
   num_rows           c6,
   chain_cnt          c7,
   chain_cnt/num_rows c8
from dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name not in
 (select table_name from dba_tab_columns
   where
 data_type in ('RAW','LONG RAW','CLOB','BLOB','NCLOB')
 )
and
chain_cnt > 0
order by chain_cnt desc
;
RESULT:
C1  C2  C3  C4  C5  C6  C7  C8
SCHEMA_XY   PERSON  10      662 98.930  85.872  1

I understand that this table has 85.872 % Chained rows. I tried:

alter table SCHEMA_XY.PERSON move;

alter table SCHEMA_XY.PERSON move pctfre 30;

SELECT 'alter index '||OWNER||'.'||INDEX_NAME||' rebuild online ;'FROM DBA_INDEXES where TABLE_NAME='PERSON';

Chained Rows remain high. The table has 250 columns mostly varchar2 and number. How can I fix it?

You have a lot of columns and a high average row length (662).
Maybe you even have rows longer than the block size.

With long rows, it is inevitable to have chained rows. Example with 8K block size:

SQL>  create table t1(c1 char(2000 byte), c2 char(2000 byte), c3 char(2000 byte), c4 char(2000 byte), c5 char(2000 byte));

Table created.

SQL> insert into t1 select 'c1', 'c2', 'c3', 'c4', 'c5' from dual connect by level <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL>  exec dbms_stats.gather_table_stats(user, 'T1');

PL/SQL procedure successfully completed.

SQL> select chain_cnt from user_tables where table_name = 'T1';

 CHAIN_CNT
----------
         0

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> select chain_cnt from user_tables where table_name = 'T1';

 CHAIN_CNT
----------
     10000

SQL> alter table t1 move;

Table altered.

SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> select chain_cnt from user_tables where table_name = 'T1';

 CHAIN_CNT
----------
     10000

Also make sure you run ANALYZE TABLE, because that is what updates CHAIN_CNT.


move a database on sql server 2017 with memory OLTP feature I have a database on sql server 2017 standard edition with in memory OLTP feature enabled, I have to move it on another disk, which is the best way to do it? are there any possible problems?

Can I manage to get rid of this OLTP feature? Somebody said to drop an re- create the db ? but how ... preserving the data ?

I have this script :

   SELECT type_desc, name, physical_name from sys.database_files
   USE master
   EXEC sp_detach_db mydb
   CREATE DATABASE mydb ON
   PRIMARY ( NAME = mydbdata,
       FILENAME = 'Y:\Database\SQLServer\DATA\mydbdata.mdf'),
   ( NAME = mydbdata2,
       FILENAME = 'Y:\Database\SQLServer\DATA\mydbdata2.mdf'),
   FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = mydbdata_mod1,
       FILENAME = 'Y:\Database\SQLServer\DATA\mydbdata_mod1')
   LOG ON  ( NAME = mydbdata_log,
       FILENAME = 'Z:\Database\SQLServer\DATA\mydbdata_log.ldf')
   ,  ( NAME = mydbdata_log2,
       FILENAME = 'Z:\Database\SQLServer\DATA\mydbdata_log2.ldf')
   FOR ATTACH
   GO

update:
this dba.stackexchange.com/questions/52007/… replies to the question how to move , and it's ok but there are a lot of methods ... although I am confident with all methods. My fear it's all about the OLTP feature and the OLTP files

Once a memory-optimized filegroup is added to a database, it's not possible to remove it. You would need to migrate your "traditional" data to a new database, then drop the original database, and rename the new to the old.

Why do you want to remove the memory-optimized filegroup?


Dataguard the datafiles are created in another diskgroup I have a recently made physical dataguard configuration and everything works correctly so far, but I've noticed that the datafiles that are created in the primary are created in the standby in a different route, the environment is as follows:

Primary - db_name: oradb, db_unique_name: oradb. Oracle 11gR2. ASM storage. Diskgoups: SYS, SYS2 and DATA
Standby - db_name: oradb, db_unique_name: oradb2. Oracle 11gR2. ASM storage. Diskgoups: SYS, SYS2 and DATA.

When creating a tablespace in the primary, example:
CREATE TABLESPACE TBS_DATA DATAFILE
  '+DATA' SIZE 50M AUTOEXTEND ON NEXT 1M MAXSIZE 100M ...
the respective datafile is created in the diskgroup DATA ... +DATA/oradb/datafile/tbs_data...
all good, the doubt that I have is that I realized that in the Standby database the datafile is created in the diskgroup SYS +SYS/oradb2/datafile/tbs_data...
, what should I do to create the datafiles in the correct path, that is +DATA/oradb2/datafile/tbs_data...?
Thank you very much in advance

OK, resuelto, el problema era el parametro 'db_create_file_dest' que al tenerlo definido en SYS por defecto creaba los datafile en dicha ruta. Espero a alguien le hay sido de utilidad esto.


Replication of databases with specific prefix on master There are options to dynamically set variables on replication slaves, like replicate_wild_do_table and replicate_wild_ignore_table.
But, are there any options for replication master to dynamically set database names which begin with same prefix, and not repeating:

replicate-do-db=prefix_db1
replicate-do-db=prefix_other_db
replicate-do-db=prefix_third_database
...

in my.cnf.

Dynamically, no.

You could, however, write a SELECT against information_schema.TABLES to create the replicate-do-db list for subsequent manual (or programmatic) placement into the config file.

Are you really adding more databases frequently?

Would it be better to, instead, do replicate-ignore-db on the rest of the dbs?

Also, consider using binlog-... so that the filtering is done sooner (on the Master instead of on the Slave).  (Yes, there are valid reasons to delay the filtering until the Slave.)


Index not used with `=any()` but used with `in` Table t has two indexes:

create table t (a int, b int);
create type int_pair as (a int, b int);
create index t_row_idx on t (((a,b)::int_pair));
create index t_a_b_idx on t (a,b);

insert into t (a,b)
select i, i
from generate_series(1, 100000) g(i)
;

No index is used with the any operator:

explain analyze
select *
from t
where (a,b) = any(array[(1,1),(1,2)])
;
                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=1000 width=8) (actual time=0.042..126.789 rows=1 loops=1)
   Filter: (ROW(a, b) = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
   Rows Removed by Filter: 99999
 Planning time: 0.122 ms
 Execution time: 126.836 ms

But one of them is used with the in operator:

explain analyze
select *
from t
where (a,b) in ((1,1),(1,2))
;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Index Only Scan using t_a_b_idx on t  (cost=0.29..8.32 rows=1 width=8) (actual time=0.028..0.029 rows=1 loops=1)
   Index Cond: (a = 1)
   Filter: ((b = 1) OR (b = 2))
   Heap Fetches: 1
 Planning time: 0.161 ms
 Execution time: 0.066 ms

It uses the record index if the record is cast to the correct type:

explain analyze
select *
from t
where (a,b)::int_pair = any(array[row(1,1),row(1,2)])
;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Index Scan using t_row_idx on t  (cost=0.42..12.87 rows=2 width=8) (actual time=0.106..0.126 rows=1 loops=1)
   Index Cond: (ROW(a, b)::int_pair = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
 Planning time: 0.208 ms
 Execution time: 0.203 ms

Why doesn't the planner use the non record index for the any operator as it uses it for the in operator?

Internally, there are two separate forms of IN, as well as for the ANY construct.

One of each, taking a set, is equivalent to the other and expr IN (<set>) also leads to the same query plan as expr = ANY(<set>) that can use a plain index. Details:

IN vs ANY operator in PostgreSQL

Consequently, the following two queries are equivalent and both can use the plain index t_a_b_idx (which can also be the solution if you are trying to get your query to use the index):

EXPLAIN ANALYZE
SELECT *
FROM t
WHERE (a,b) = ANY(VALUES (1,1),(1,2));

Or:

...
WHERE (a,b) IN (VALUES (1,1),(1,2));

Identical for both:

                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.33..16.71 rows=1 width=8) (actual time=0.101..0.101 rows=0 loops=1)
   ->  Unique  (cost=0.04..0.05 rows=2 width=8) (actual time=0.068..0.070 rows=2 loops=1)
         ->  Sort  (cost=0.04..0.04 rows=2 width=8) (actual time=0.067..0.068 rows=2 loops=1)
               Sort Key: "*VALUES*".column1, "*VALUES*".column2
               Sort Method: quicksort  Memory: 25kB
               ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=8) (actual time=0.005..0.005 rows=2 loops=1)
   ->  Index Only Scan using t_plain_idx on t  (cost=0.29..8.32 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=2)
         Index Cond: ((a = "*VALUES*".column1) AND (b = "*VALUES*".column2))
         Heap Fetches: 0
 Planning time: 4.080 ms
 Execution time: 0.202 ms

However, this cannot easily be passed to a function, since there are no "table variables" in Postgres. Which leads to the problem that started this topic:

Pass multiple sets or arrays of values to a function

There are various workarounds for that problem. One being the alternative answer I added there. Some others:

Insert values from a record variable into a table
Declare variable of table type in PL/pgSQL

The second form of each is different: ANY takes an actual array, while IN takes a comma separated list of values.

This has different consequences for typing the input. As we can see in the EXPLAIN output of the question, this form:

WHERE (a,b) = ANY(ARRAY[(1,1),(1,2)]);

is seen as shorthand for:

ROW(a, b) = ANY (ARRAY[ROW(1, 1), ROW(1, 2)])

And actual ROW values are compared. Postgres is not currently smart enough to see that the index on the composite type t_row_idx is applicable. Nor does it realize that the simple index t_a_b_idx should be applicable as well.

An explicit cast helps to overcome this lack of smarts:

WHERE (a,b)::int_pair = ANY(ARRAY[(1,1),(1,2)]::int_pair[]);

Casting the right operand (::int_pair[]) is optional (though preferable for performance and to avoid ambiguities). Once the left operand has a well-known type, the right operand is coerced from "anonymous record" to a matching type. Only then, the operator is defined unambiguously. And Postgres picks applicable indexes based on the operator and the left operand. For many operators that define a COMMUTATOR, the query planner can flip operands to bring the indexed expression to the left. But that's not possible with the ANY construct.

Related:

Is there a way to usefully index a text column containing regex patterns?

WHERE (a,b) IN ((1,1),(1,2));

.. values are taken as elements and Postgres is able to compare individual integer values as we can see in the EXPLAIN output once more:

Filter: ((b = 1) OR (b = 2))

Hence Postgres finds that the simple index t_a_b_idx can be used.

Consequently, there would be another solution for the particular case in the example: since the custom composite type int_pair in the example happens to be equivalent to the row type of the table t itself, we could simplify:

CREATE INDEX t_row_idx2 ON t ((t));

Then this query would use the index without any more explicit casting:

EXPLAIN ANALYZE
SELECT *
FROM   t
WHERE  t = ANY(ARRAY[(1,1),(1,2)]);

                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t  (cost=40.59..496.08 rows=1000 width=8) (actual time=0.19
1..0.191 rows=0 loops=1)
   Recheck Cond: (t.* = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
   ->  Bitmap Index Scan on t_row_idx2  (cost=0.00..40.34 rows=1000 width=0) (actual time=0.188..0.188 rows=0 loops=1)
         Index Cond: (t.* = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
 Planning time: 2.575 ms
 Execution time: 0.267 ms

But typical use cases won't be able to utilize the implicitly existing type of the table row.


Semantic version type on PG 11.5 deployed on RDS. ltree? I've got some semantic version details to store, and figured a dedicated type would be a bit help with searches and sorts. At least one implementation exists:

https://github.com/theory/pg-semver

This is not supported on RDS. why we're on RDS. Is there an alternative that's RDS enabled? I'm considering either ltree or simply using a text or citext and grinding out the necessary queries and sorts, as needed.

Thanks

You could use an array of integers e.g. version 1.2.3 would be array[1,2,3].

Such an array compares (and sorts) correctly. To format it as a string, you could write some simple functions.

Parsing is simple as well: string_to_array('1.2.3', '.')::int[]


Connecting to postgres from windows to virtual machine I have the following setup

Windows 8
Oracle VM virtual box with arch Linux running on it
Installed and configured PostgreSQL on Linux
Setup the ports in virtual box so that

45432 port on windows goes to 5432 on Linux
40022 port on windows goes to 22 on Linux

On Linux PostgreSQL, I have created user erjik and database mydb.
Firewall is shut down on windows.

The problem is that I am trying to connect from intellij idea using JDBC PostgreSQL  driver, just trying test connection to address:

jdbc:postgresql://127.0.0.1:45432/mydb

username: erjik
password: xx

However, the test connection fails.

pg_hba.conf goes unchanged after installation with following settings:

local all all trust
// IPv4
host all all 127.0.0.1/32 trust
// IPv6
host all all ::1/128 trust

In postgresql.conf I have set only this settings:

listen_addresses = '*'

and

port = 5432

What could be the reason of connection attempt fail? Maybe I missed some settings.

EDIT1:
on Linux side the command:

psql -h localhost -U erjik mydb

executes successfully.

When you connect to the PostgreSQL instance via the forwarded port in the VM you're not really connecting to localhost, the port is being forwarded to the actual IP of the virtual network interface. You need to modify pg_hba.con to allow connections from IP addresses other than 127.0.0.1 for this to work.

To open up to everything add the following to pg_hba.conf. You can optionally  restrict the address to your local machine's IP address (or a subset of it):

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             all                     md5

Once it's updated restart the server or send the master process a SIGHUP so it reloads the pg_hba.conf file.

FYI, you can verify that this is what happening by creating a "server" using nc and trying to connect to it from your local machine.

On the VM forward the port 12345 to your local machine and try the following. Note that the "Connection from ..." output will come after you do the next step. See how it's not 127.0.0.1:

your-pgvm$ nc -v -l 12345
Connection from 10.0.2.2 port 12345 accepted

On your Windows machine:

your-comp$ nc localhost 12345


Database Design question, calculate or save financial positions As the question states I'm currently internally strugling with a database design question. I work as a software developer (and I tend to do everything database related as well since we're rather small) for a financial company and we're doing a revamp of our current system.

Our system is starting to become outdated and the biggest issue we have with the current system is the database. My predecessors have made some "challenges" for me in the current design and I'd like to not fall into the same pits in the new design.

Basically we've got things like invoices, adminstration costs and all this fancy stuff.

Added on this we have Creditinvoice, administrational adjustments on payments.

The current system is WAY too redundant having financial values all over the place and it's sometimes a nightmare to actually figure out what is the current position of a debtor/invoice.

In order to not have the same problem I'm currently struggling with the question, do I want to store the position of an invoice somewhere, or do I want to calculate this through a view (indexed maybe; unsure since I've never indexed my views)?

Simply put I have the following tables (it's a slightly simplified example but I think it paints the picture);

Invoices
Invoicelines
TaxPercentages
BankstatementLines
Entries (a table that shows what value of a statement is "booked" on the invoice)

Now, InvoiceLines, BankstatementLines & Entries are currently the only tables with a financial value in them, being NettAmount (InvoiceLines has this column, which is the amount before taxes). Bankstatmentlines holds the actual amount recieved on the bank account. Entries hold which amount (could be partial) from the bankstatmentline has been booked on the invoice.

In order to calculate the position I'd have to do the following;

Calculate the Tax values for the invoice (Adding this to the Nettamount gives me the grossamount)
Subtract the amount from the entries from the GrossAmount so I end up with an "OwedAmount"

This is somewhat complex, however I'm 100% sure that I will never have a wrong value in my database, since I'm not storing any "GrossAmount" or "OpenAmount".

Personally I'm thinking I should add a "TaxAmounts" Table which simply holds the calculated tax amounts (these numbers never change so calculating them over and over seems redundant). This also goes for the GrossAmount. If I have the TaxAmounts & the NettAmount I know the GrossAmount, which should also NEVER change.

I think doing those two things is the right way to go as those values should NEVER change. If they do something went horribly wrong anyway so recalculating on the fly to fix an error shouldn't be an issue.

My current plan is to calculate the owed amount in a view (most likely indexed on the invoice PK). I wonder if this is the best way to go at this "problem" though? Maybe there is some way of doing this that is more efficient but has the same end result (not having an "OwedAmount" column somewhere that needs to be kept up to date and has a risk of becoming erroneous).

Other information; We'll be hosting this on SQL Server 2012 (currently designing in 2005 but the licenses for 2012 have been ordered and are on their way!)

You want to store the details of the transaction at the time it happens and not calculate at the time you select the record. This is because the information the calculation is based on (such as tax rate ) is subject to change. So yes, store the tax calculation. This is not an issue of performance, it is an issue of temporal data that must be shown as of the date of the action not as it is today. The alternative design is to put dates on everything you use to calulate and then calculate on the fly joining not only on the id but on the date, but this is far easier to make a mistake on and far more likely to be a performance problem.


Best Practices When Inserting Data (opinion question) I'd just like to see what's the best practice when interfacing with a database.
When inserting/modifying data in a database, is it best practice to do logic checking prior to interfacing with the DB (for instance in php), or just let MySQL do the checking for instance using foreign key constraints, NOT NULL, and so on?

Example: I could check ahead of time that a primary key exists in one table before inserting in another table, or just attempt an insert and let the foreign key constraint error if it doesn't exist.

Best practice?

YES

check data at the client

Check the data on the client side (eg in JavaScript) for the stupid simple checks. eg NOT NULL, attribute < someValue, strings are in the correct format (regex), etc.  Feel free to make some REST calls for the validation.

Also, use good GUI designs.  The use of Drop Downs help limit what PK values the end users can use.

check data at the middle tier

Check the data at the middle tier because:  Not everyone uses web browsers.  Not everyone will send "clean data".
https://xkcd.com/327/

enforce data rules inside the DB

On of the primary purpose of a database is to store valid data.

Always enforce constraints within the database.  There could be more than one application accessing the database.  You want those rules to apply to all applications.

For an application assertion, use a function/procedure.


What can I use for the PowerShell notin operator in v2.0? I developed a PowerShell script in v4.0 and then learned that the -notin operator isn't supported on v2.0 (which is the version that I need to run it in). What is the best alternative to the -notin operator for v2.0? I'd prefer to minimize code refactoring, if possible.

Here is a snippet of code where I am using the operator:

$NoBackups += $BackupList |
            Where-Object { $_.DatabaseName -notin ($NoBackups.DatabaseName) } |
            Where-Object { ($_.RecoveryModel.ToString().ToUpper() -ne "SIMPLE") -and ($_.LastLogBackupDate -lt $CompareDate.AddHours($LOGCompareHours)) } |
            SELECT InstanceName, DatabaseName, LastBackupDate, LastDifferentialBackupDate, LastLogBackupDate, `
                @{Name='Message';Expression={"Transaction Log Backup is more than $([Math]::Abs($LOGCompareHours)) hours old"}}

You can use -NotContains in PowerShell v2.0 and up. The only thing you will need to do is just reverse your check. Using -NotIn you are comparing your current value to the collection, with -NotContains you do the reverse by comparing your collection to your current value.

So just change this:

Where-Object { $_.DatabaseName -notin ($NoBackups.DatabaseName) }

To

Where-Object { $NoBackups.DatabaseName -NotContains $_.DatabaseName }

A rough example:


Is it much more expensive to use procedures that get lists and deriving a count in the application vs. a separate stored procedure for the count? I would like to know if returning a count is much more expensive than returning a dataset in regard to processing performance? I assume there is a bandwidth increase in delivery to the application logic.

I am basically trying to determine if it is more worth my time to create separate procedures to get counts or just utilize the procedures returning datasets and count how many rows are in the dataset in the application logic.

Not 100% sure I understand your question but - if you are already selecting the data, will also selecting a count be that much more expensive? No, I don't think you'd have an issue doing that. Selecting just a count should incur the same cost as far as the scans/seeks and joins/filtering/aggregating you had to do in your query but also show some time saved in processing/formatting and sending the actual rows as well. If you your select takes 10ms, I'd expect the count to also take 10 or less ms.

Alternatively you can get the rowcount returned by operations as well and return that without even incurring the additional 10 ms.

This SO question deals with the same basic question I think you are driving at and gives a few different answers on technique.


Trying to reduce number of table scans when querying on the same column but different values I'm having a mental block. I'm using a sample MySQL customer database...

http://www.mysqltutorial.org/mysql-sample-database.aspx

And would like to query for any customers that bought two products (product codes S18_1589 and S24_1628). I wrote this query...

SELECT customerNumber
FROM orders o
WHERE customerNumber IN (SELECT customerNumber
                         FROM orders o
                         JOIN orderdetails od
                         ON o.orderNumber = od.orderNumber
                         WHERE od.productCode = 'S18_1589')
AND customerNumber IN (SELECT customerNumber
                         FROM orders o
                         JOIN orderdetails od
                         ON o.orderNumber = od.orderNumber
                         WHERE od.productCode = 'S24_1628')
GROUP BY customerNumber
ORDER BY customerNumber;

That gets the results that I want but I'd like to do this without using two sub queries if possible. The customers can buy any other products but they need to have purchased the two mentioned.

In order to try to make the query more efficient, I changed it to this...

SELECT customerNumber
FROM orders o
JOIN orderdetails od
ON o.orderNumber = od.orderNumber
WHERE od.productCode IN ('S24_1628', 'S18_1589')
GROUP BY customerNumber
HAVING COUNT(*) >= 2
ORDER BY customerNumber;

which gives me the same result as the first query but I think it's just a coincidence. I believe the query will also include customers that have purchased only one of the products but 2 or more times.

Anyone know a better way / more efficient way of handling?

NOTE: I was given this question and was told my query was not performant because of the two subqueries but no further feedback was given. It's a different dataset / schema than what I'm using now but size is similar and what I'm trying to query is the same type of question.

The join to 'Products' in both your sub queries is redundant and can be removed. You can filter directly on "Where od.ProductCode = 'S18_1589'" There is a FK between Products and OrderDetails, so there can't be a ProductCode that is not in Products.
The general challenge you are trying to solve is called 'Relational Division'. Your query will work for only 2 products, but what about 3, 10, or 30?? Search the forum here and you will find many excellent discussions on it. For your case, I would use the following solution. It relies on rephrasing the question in double negative form - "Show me all customers, for which there isn't a single ProductCode from our ProductsList, which is not in the customer's order items"

WITH prodctsList
AS
(
SELECT *
FROM   (VALUES ('S24_1628'), ('S18_1589')) AS products(productCode)
/* Add as many products as needed */
)
SELECT customerNumber
FROM   customers AS C
WHERE NOT EXISTS (
                  SELECT productCode
                  FROM   productList
                  EXCEPT
                  SELECT productCode
                  FROM   orders AS O
                         INNER JOIN
                         orderDetails AS OD
                         ON O.orderNumber = OD.orderNumber
                  WHERE  O.customerNumber = C.customerNumber
);

A solution without EXISTS and VALUES table constructor

WITH prodctsList
AS
(
    SELECT 'S24_1628' AS productCode
    UNION ALL
    SELECT 'S18_1589'
/* Add as many products as needed */
)
SELECT O.customerNumber
FROM   orders AS O
       INNER JOIN
       orderDetails AS OD
       ON O.orderNumber = OD.orderNumber
       INNER JOIN
       productList AS PL
       ON PL.productCode = OD.productCode
GROUP BY O.customerNumber
HAVING COUNT(*) = (SELECT COUNT(*) FROM productsList);


How does Sybase store datetime information? We have a Sybase database that currently stores datetime values in some of the tables.  If we move that server with the Sybase database to another country which is in another time zone, will the datetime values in the database be represented differently?

In other words, consider the scenario:

The Sybase database runs in timezone +2:00.

From my Java app that lives in time zone +1:00, it writes to the database 1 Jan 1970, 1:00 which is epoch 0 in the app's time zone.

preparedStatement.setTimestamp(3, new Timestamp(0));

However, when I query the database using SQL Workbench/J client (in time zone +1:00), I see

1970-01-01 01:00:00

in my table.  In fact, it does not matter to which time zone I change my computer's clock, SQL Workbench/J client always reads 1970-01-01 01:00:00 from the database.

Another Java client that lives in time zone +3:00 reads the same value as

1970-01-01 01:00:00

which translates to epoch -7200000 or -2:00 in the client's time zone!

So to get back to my original question, does Sybase store datetime values as e.g. strings or epochs?  I do not have a free Sybase server that I can experiment on, so what can we expect to happen when we move the server to another country with a different time zone?

The type we use is "datetime", for example:

CREATE TABLE "dbo"."whatever" (eventDate datetime NOT NULL);

The answer below was taken from Sybase official technical documentation available at http://www.sybase.com/detail?id=1048699 (SAP has removed support for many links at sybase.com; however The Way Back Machine seems to have a copy of the page even if it is a bit old).

Sybase Servers support the use of date and time data through the datetime and smalldatetime datatypes (and, with newer server, the date and time datatypes), as well as the getdate(), dateadd(), datediff(), and datepart() functions. The getutcdate() function was also added to some servers to provide the current datetime value in Coordinated Universal Time regardless of the time zone the server is otherwise running under.
The datetime and smalldatetime datatypes, however, do not store time zone information and the products are entirely ignorant of the concepts of time zones and daylight saving time. Sybase Servers only recognize and store the date and time portions of the values provided by the operating system, which are based on the time zone configured at the operating system level (typically though the TZ environment variable setting in Unix or the Date/Time function of the Windows Control Panel) for the user who started the product. The calculations behind the dateadd and datediff functions are aware of leap years (using the rule of every 4th year, except for every 100th year, except for every 400th year), but do not include any adjustments for leap seconds or transitions from daylight saving time to regular time.

I would therefore expect that changing the underlying OS timezone will not have any appreciable effect on the data stored in the datetime fields in your database.


is it relatively safe to union two tables with UUID PK? I want to union two tables whose primary keys are UUID(V4) and I want to preserve them in the result. I work with PostgreSQL

Is it safe to do this?
What are the pitfalls may be encountered?

I guess your concern is that the UUIDs could no longer be unique after the UNION.
That is of course theoretically possible, but the danger is no bigger that if you use version-4 UUIDs as primary keys.
In practice, you don't have to worry about that. Wikipedia describes it well:

with [...] random version-4 UUIDs, collisions can occur even without implementation problems, albeit with a probability so small that it can normally be ignored. This probability can be computed precisely based on analysis of the birthday problem.
For example, the number of random version-4 UUIDs which need to be generated in order to have a 50% probability of at least one collision is 2.71 quintillion, computed as follows:

This number is equivalent to generating 1 billion UUIDs per second for about 85 years. A file containing this many UUIDs, at 16 bytes per UUID, would be about 45 exabytes.

If you paranoid enough to be afraid of that, you shouldn't use version-4 UUIDs at all, but use a different, collision-free UUID algorithm like version 1 or 2.


How to join the latest previous record with SQL I have a table whose schema is like this:

CREATE TABLE product_shipping(
    product_id CHAR(10),
    product_name CHAR(10),
    deal_dt DATETIME,
    deal_reason_no SMALLINT,
    cancel_dt DATETIME,
    cancel_reason_no SMALLINT
);

Once the shipping date is arranged, the deal_dt and deal_reason_no will be set and cancel_dt  and cancel_reason_no is null. However product_id is not unique in the table since the every product shipping can be cancelled. Once cancelled cancel_dt and cancel_reason_no will be set. If the cancelled product is bought by other customer then the shipping is recorded in the new row, and product_name can be changed in the new shipping.

I want to query the product_id and deal_dt whose deal_reason_no is 1, 3 or 5 and deal_dt is between '2014-04-01 00:00:00' and '2014-04-11 00:00:00' and the latest previous deal_dt and latest  previous cancel_dt whose cancel_reason_no is 2 or 4 for the product_id.

I think the SQL could be like this:

SELECT
C.product_id as product_id,
C.deal_dt as deal_dt,
R.deal_dt as previous_deal_dt,
R.cancel_dt as previous_cancel_dt,
FROM product_shipping C
LEFT JOIN product_shipping R ON (???)
WHERE TO_CHAR(C.deal_reason_no ) in ('1', '3', '5')
AND C.deal_dt between '2014-04-01 00:00:00' and '2014-04-11 00:00:00'

But I'm not sure how to join to get latest previous record.

I'm using Informix 11.70.

Edit: Add my idea. Is this reasonable?

SELECT
C.product_id as product_id,
C.deal_dt as deal_dt,
MAX(R.deal_dt) as previous_deal_dt,
MAX(R.cancel_dt) as previous_cancel_dt,
FROM product_shipping C
LEFT JOIN product_shipping R ON (C.product_id = R.product_id and TO_CHAR(R.cancel_reason_no) MATCHES ('2', '4') and R.cancel_dt <= C.deal_dt)
WHERE TO_CHAR(C.deal_reason_no ) in ('1', '3', '5')
AND C.deal_dt between '2014-04-01 00:00:00' and '2014-04-11 00:00:00'
GROUP BY 1,2

I suggest you join to previous products that match criteria and (if any exist) narrow down to the one where no later row exists:

SELECT P.product_id AS product_id,
      ,P.deal_dt    AS deal_dt
      ,C.deal_dt    AS previous_deal_dt
      ,C.cancel_dt  AS previous_cancel_dt
FROM   product_shipping p
LEFT   JOIN product_shipping C ON C.product_id = P.product_id
                              AND C.cancel_reason_no IN (2, 4)
                              AND C.cancel_dt < P.deal_dt
WHERE  P.deal_reason_no IN (1, 3, 5)
AND    P.deal_dt BETWEEN '2014-04-01 00:00:00' AND '2014-04-11 00:00:00'
AND    NOT EXISTS (
   SELECT 1
   FROM   product_shipping C1
   WHERE  C1.product_id = C.product_id
   AND    C1.cancel_reason_no IN (2, 4)
   AND    C1.cancel_dt < P.deal_dt
   AND    C1.cancel_dt > C.deal_dt
   );

Assuming (product_id,cancel_dt) to be UNIQUE, else you need tiebreaker criteria to be unambiguous. This way you do not need GROUP BY, because only one (or no) row is left-joined.

Also assuming deal_dt and cancel_dt are supposed to come from the same cancelled deal.

This would be simpler with the window function lag(), but your version does not seem to support that according to the discussion under the duplicate post.


Mysql LEFT JOINing same table twice mysql> SELECT * FROM customers;
+----+------+
| id | name |
+----+------+
|  1 | Matt |
|  3 | John |
+----+------+

mysql> SELECT * FROM purchases;
+----------+------+-------+
| owner_id | type | quant |
+----------+------+-------+
|        3 | cat  |     3 |
|        3 | cat  |     2 |
|        1 | cat  |     4 |
|        1 | dog  |     1 |
|        1 | dog  |     2 |
+----------+------+-------+

So i have two tables and want to see a column with a customer name then a column with total number of dogs purchased followed by a column for cats and this is what i came up with

mysql> SELECT c.name, sum(dogs.quant) AS dogs, SUM(cats.quant) AS cats
    -> FROM customers AS c
    -> LEFT JOIN purchases AS dogs ON c.id=dogs.owner_id AND dogs.type = 'dog'
    -> LEFT JOIN purchases AS cats ON c.id=cats.owner_id AND cats.type = 'cat'
    -> GROUP BY c.name;
+------+------+------+
| name | dogs | cats |
+------+------+------+
| John | NULL |    5 |
| Matt |    3 |    8 |
+------+------+------+

Can someone explain why Matt has 8 cats when i expect it to be 4? This is a boiled down version of my problem

Perhaps it is easiest to see why this is happening by removing the aggregate functions:

SELECT c.name
     , dogs.quant
     , cats.quant
FROM customers AS c
LEFT JOIN purchases AS dogs
    ON c.id=dogs.owner_id
    AND dogs.type = 'dog'
LEFT JOIN purchases AS cats
    ON c.id=cats.owner_id
    AND cats.type = 'cat'

+------+-------+-------+
| name | quant | quant |
+------+-------+-------+
| Matt |     1 |     4 |
| Matt |     2 |     4 |
| John |  NULL |     3 |
| John |  NULL |     2 |
+------+-------+-------+

You get a "multiplier effect" since you are joining the purchase table twice. Here's an alternative solution:

SELECT c.name
    , sum(case when type = 'dog' then quant end) AS dogs
    , sum(case when type = 'cat' then quant end) AS cats
FROM customers AS c
JOIN purchases p
    ON c.id=p.owner_id
GROUP BY c.name;


MySQL Scheduled Event and server offline? I have an event planned to run a 00:00. But the server won't be online at that point, seeing it's a local development server. Will it run the next time the server is online, or will it just be forgotten?

The event is as follows:

    CREATE DEFINER=`root`@`localhost`
    EVENT `winners`
    ON SCHEDULE EVERY 1 DAY STARTS '2013-02-19 00:00:00'
    ON COMPLETION PRESERVE ENABLE
    DO
    UPDATE `data`
    SET wins = wins + 1
    ORDER BY cur_score DESC
    LIMIT 1

It should run just fine.

However, it is just like configuring Windows Updates. If you configure Windows Updates for 12:00 AM, shutting down the Windows Machine at 11:59 PM and starting it back at 12:01 AM prevents Windows from checking for Updates.

When such a shutdown is occasional, leaving everything alone will cause the DB server to bring up the MySQL Event at midnight so long as mysqld is running at midnight.


Oracle 12c Express Enterprise Manager on Centos 6.5 is not reachable I have Centos 6.5, and installed Oracle 12c. As dbca utility says after configuring new db, EM Express should be available on https://hostname:5500/em
In fact, it is not. Neither on localhost nor by ip.
I also did SQL> EXEC dbms_xdb_config.sethttpport(5501); because DBMS_XDB.GETHTTPPORT() showed "0", and set 5500 was impossible due to the error - 5500 seemed busy.
After that I tried both ports and different host names - nothing works.

DB is up and working, here is output of the lsnrctl status(I removed listener.ora to be sure that it's not wrong, but the output is the same):

 LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-JAN-2015
 10:27:42

 Copyright (c) 1991, 2014, Oracle.  All rights reserved.

 Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the
 LISTENER
 ------------------------ Alias                     LISTENER Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production Start Date
 18-JAN-2015 10:27:20 Uptime                    0 days 0 hr. 0 min. 21
 sec Trace Level               off Security                  ON: Local
 OS Authentication SNMP                      OFF Listener Log File
 /u01/app/oracle/diag/tnslsnr/hpsa/listener/alert/log.xml Listening
 Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hpsa)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hpsa)(PORT=5501))(Presentation=HTTP)(Session=RAW))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=hpsa)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/main/xdb_wallet))(Presentation=HTTP)(Session=RAW))
 Services Summary... Service "main" has 1 instance(s).   Instance
 "main", status READY, has 1 handler(s) for this service... Service
 "mainXDB" has 1 instance(s).   Instance "main", status READY, has 1
 handler(s) for this service... The command completed successfully

Also, here is my /etc/hosts:

 127.0.0.1  localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1     localhost localhost.localdomain
 localhost6 localhost6.localdomain6
 192.168.56.101 hpsa hpsa.hpsa hpsa.localdomain node1

hostname:

 [root@hpsa ~]# hostname

hpsa.hpsa

All that I have now for https://192.168.56.101:5501/em : browsers show that they are connected to the host and trying to retrieve data, but in few long minutes I am getting connection reset. Firefox also showed ssl_error_rx_record_too_long

What am I doing wrong?

0> "and set 5500 was impossible due to the error - 5500 seemed busy"

This means that the port 5500 is already "in use". Well, I bet it is not in use but Oracle thinks that. There are a lot of ports reserved in /etc/services.

Open the file and change the following lines:

fcp-addr-srvr1  5500/tcp                # fcp-addr-srvr1
fcp-addr-srvr1  5500/udp                # fcp-addr-srvr1

Change them into:

#fcp-addr-srvr1  5500/tcp                # fcp-addr-srvr1
#fcp-addr-srvr1  5500/udp                # fcp-addr-srvr1

Retry to set the port with SQL> EXEC dbms_xdb_config.sethttpport(5500);.

EDIT 1:

Your listener shows that it's listening on port 5500 for https requests. So maybe you already did what I just posted.
Could you please share the output of the following command?

service iptables status

EDIT 2:

I just activated EM Express on one of my 12c databases. Here is what I did:

OS: Oracle Linux 6.5
DB: Oracle Database 12.1.0.2.0
service iptables status --> OFF

Action log:

$ sqlplus / as sysdba
SQL> SELECT DBMS_XDB_CONFIG.gethttpport FROM dual;

GETHTTPSPORT
------------
        5500

SQL> SELECT DBMS_XDB_CONFIG.gethttpsport FROM dual;

GETHTTPPORT
-----------
          0

I'm going to set https port to 5501 and overwrite the http port with 5500.

SQL> EXEC DBMS_XDB_CONFIG.sethttpport(5500);
PL/SQL procedure successfully completed.

SQL> EXEC DBMS_XDB_CONFIG.sethttpsport(5501);
PL/SQL procedure successfully completed.

Afterwards the listener shows that I's listening on these ports (before the 5500 record was there already but it was not working)

$ lsnrctl status | grep PORT
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=***)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=***)(PORT=5501))(Security=(my_wallet_directory=/opt/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=***)(PORT=5500))(Presentation=HTTP)(Session=RAW))

I did the test in a browser:

Browse to http://***:5500/em --> login screen appears
Browse to https://***:5500/em --> login screen appears

Note the tailing "/em" is important.


Difference between the following postgresql indexes On a table with roughly 15 million rows, I've got a bunch of boolean fields indicating performed actions and also timestamps of when the action happened for a row.
The following 2 indexes, I would think will act the exact same way, but they don't:
1.
"cleanupAndAlerted001" btree (doneCleanup, alertedUser, doneCleanupTimestamp DESC) WHERE doneCleanup = true AND alertedUser = true;

"cleanupAndAlerted002" btree (doneCleanupTimestamp DESC) WHERE doneCleanup = true AND alertedUser = true;

The query performed is of the form:
select distinct id, field1, field2, ... from my_table
WHERE
doneCleanup = true AND
alertedUser = true AND
doneCleanupTimestamp<='2021...validtimestamp'
order by id asc, initialTimestamp asc limit 100 offset 50000;

Considering the conditions, I would expect identical behavior. Yet, the first index, even when the 2nd doesn't exist, does not get picked up.
Creating the 2nd index and doing the query again works, the new index gets used.
Am I assuming things I shouldn't be assuming? If so, what should I know about this behavior?
Edit: using postgres 11.2

Check for table and index sizes. Your first index uses 3 columns and its size is probably very big so optimizer chooses full table scan.
Index scan vs full table scan also depends on random_page_cost (default value 4).
If random_page_cost is set lower optimizer will chose index scans over full table scans.


What is the main difference between '[[:space:]]' and '\s' in Oracle pl-sql regular expression? I have a string like this x=y AND z=y AND p=q , I need to replace every (space)AND(space)  pattern with * so that the original string turns into x=y*z=y*p=q. I'm using Regular expression for this cause the important thing is that there might by more that one space before and after each AND .
I've written two different queries for this
 query1:

 select regexp_replace('x=y AND z=y AND p=q','[[:space:]]AND[[:space:]]','*')
 from dual;

query2:

 select regexp_replace('x=y AND z=y AND p=q', '\s+AND\s+', '*')
 from dual;

I want to know :

Are there better ways to do that?
What is the main difference between these two queries? I mean[[:space:]] and \s? The important thing is that I want to know In what sort of condition these two queries will produce different result?

Thanks in advance

The Oracle documentation confirms that the POSIX expression [[:space:]] is equivalent to the Perl-style \s. I personally prefer \s because it's shorter, and also supported by many text editors.
In your example you have [[:space:]] (one whitespace character) but \s+ (one or more whitespace characters). Maybe that should have been [[:space:]]+.


How to identify hung queries, their importance, and then take action using command line? From a production support perspective, how can we find hung queries and then find how important they are and then how to deal with them?

I tried only processlist which gives currently running queries but how to decide their importance and then act accordingly?

We don't have any MySQL performance tool with UI in existence in our environment, so how can I do this using command line?

There are two utilities that you may want to look at:

1) mytop

2) innotop

They are, as their names imply, the MySQL equivalent of the "top" system utility.

They won't help you figure out how "important" a query is because this requires deep knowledge of the system issuing the queries.

For a deeper, longer term (rather than real-time, catch-it-while-it's-running) insight, you can capture the slow log (adjust long_query_time appropriately to your opinion on what is problematic), and analyze the slow log using one of the following two tools:

1) mysqldumpslow

2) pt-query-digest

These will give you insight into which query "templates" are the ones consuming the largest amount of the server's CPU time.

When you outgrow the capabilities of the command line tools and you find you need a deeper long-term insight, I suggest you look at PMM (Percona Monitoring and Management), it is an extremely useful took for identifying performance issues on database (and not just database) servers.


Best way to move sybase IQ table to Sybase ASE I use bcp to copy Sybase IQ table to sybase ASE server. Actually bcp first creates some bulk file with extracdet sybase IQ table there, and after just loads that file to Sybase ASE. Is it the one and the best (considering accuracy, speed) way to do that?

IQ has the ability to output data directly from the data server to a local filesystem.  It's faster than returning data over the network to a client tool.  Create the output file that way, and then bulkcopy it into Sybase ASE using bcp -c :

set temporary option Temp_Extract_Name1='my_output_file';
-- Set column tab delimiter via hex value for use by bcp -c
-- Note, character between the single quotes needs to be an actual
-- tab character. \t and \0x09 don't work.  Might need to be
-- in a script where you can insert a tab character using vi or
-- something similar.  Stupid IQ.
set temporary option TEMP_EXTRACT_COLUMN_DELIMITER=' ';
select * from my_table;
set temporary option Temp_Extract_Name1='';

This should produce a file that in a suitable format for bcp with the -c option.  Be careful about tabs embedded in the data.  I'm not sure if they'll be escaped.


How to clear SQL Agent Job Scheduler Cache I have to submit tickets to alter job scheduling, so rather than a walk through of the GUI I try to update msdb..sysschedules via tsql script.  I always see the alterations immediately via the GUI.  The first time I used this method though, it took a day for the changes to propagate.  More recently it's been longer.  I suspect something's cached in agent/scheduler, but I can't find documentation on what to do to flush this in the least intrusive manner on a production system.  I was hoping something rudimentary like disable/enable the job would trigger a refresh, but it did nothing and I'd rather issue something in T-SQL with my tickets to ensure changes get used.  I've requested a restart of agent when nothing's on the horizon to see if that works, but ideally would prefer something less intrusive.  This is a 2014 instance.

A sample script:

USE msdb;
update s set
    s.freq_subday_interval=3
from msdb..sysjobs j
    left join msdb..sysjobschedules js on j.job_id = js.job_id
    left join msdb..sysschedules s on js.schedule_id = s.schedule_id
    where j.name ='SomeDb.RunMeEvery3MinsBusta';

----edit-----

I guess taking from answers provided I tested below and it seems to cascade immediately.  Just wanted to make it a little more dynamic.

declare @jobid nvarchar(50), @scheduleid int
select @jobid=j.job_id,@scheduleid=s.schedule_id from msdb..sysjobs j
    left join msdb..sysjobschedules js on j.job_id = js.job_id
    left join msdb..sysschedules s on js.schedule_id = s.schedule_id
    where j.name ='test';

EXEC msdb.dbo.sp_attach_schedule @job_id=@jobid,@schedule_id=@scheduleid
EXEC msdb.dbo.sp_update_schedule @schedule_id=@scheduleid,
        @freq_subday_interval=1
GO

You may use below script to alter the schedule:

EXEC msdb.dbo.sp_attach_schedule @job_id=N'533fb186-72e9-40c5-af88-c8e57f097126',@schedule_id=16
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_schedule @schedule_id=16,
        @active_start_time=24500
GO

you may find job_id using below script for the job you want to change schedule:

select job_id from msdb..sysjobs where name = 'SomeDb.RunMeEvery3MinsBusta'

To find schedule_id, please use below script:

select * from msdb..sysjobschedules where job_id = '533FB186-72E9-40C5-AF88-C8E57F097126'

Here job_id is taken from previous script of table sysjobs.

I have specified this job to run at 2:45AM on daily basis, you may specify other options too.

EXEC msdb.dbo.sp_attach_schedule @job_id=N'533fb186-72e9-40c5-af88-c8e57f097126',@schedule_id=16
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_schedule @schedule_id=16,
        @freq_type=8,
        @freq_interval=64,
        @freq_recurrence_factor=1,
        @active_start_time=153000
GO

Above script is to change job type to weekly, running at 3:30PM on Saturdays.

Hope above helps.


Using LIKE or Full-text when searching database I am fairly new to SQL and I am currently trying to create a search function for my website, much like the one on imdb.com. I have 3 tables and I need to search two rows (nvarchar) in each table when a user makes a search. The data to be searched is maximum 20 letters long but there are a lot of rows, and hopefully many more will be added.

I want to "future-proof" the website and I was looking up Full-text and FREETEXT searches. I have been using LIKE before but it says this is not very optimal for performance when the search has to look through a lot of data.

So basically before I start working on this very important part of my website I want to know what experts like you think. Is it acceptable to use LIKE when making a big search or should I learn how to use Full-text indexes and FREETEXT searches?

My current way of searching:

SELECT table1.col1, table1.col2
FROM table.1
WHERE table1.col1 LIKE '%@search%'
UNION ALL
SELECT table.2.col1, table2.col2
...

Database version:
Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.3  (Build 9600: )

EDIT: The data are always just 1 word, so there will be no use for stopwords that are provided by full-text.

Having a wildcard in front of your search term will prevent SQL Server from being able to use an index and thus become painful very fast. 2 things you could do, besides going for full text search:

The obvisous one, do not put a wildcard at the start of the keyword requiring the user to enter a search query matching the start of col1.
Splitting col1 from both tables into keywords and put the distinct keywords in a sperate table and add two junction tables joining table1 and table2 to the keyword table.


SP to return last ID and insert into a column using a trigger I have written an SP to do the below:

i have a table called T1:
AS below:

CREATE TABLE [dbo].[T1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [QNo] [nvarchar](50) NULL DEFAULT ('0'),
PRIMARY KEY CLUSTERED

T1 stores a number value in the Qno Column. AS so:
ID: 1            Qno: 0

When i insert into table QUDF, the last number (in this case currently is '0'), needs to be inserted into the QUDF column in the QUDF table. (it needs to fetch last number from T1 when inserting in QUDF)

columns in QUDF are:
Client_id, Contact_number, Code_ID, Type_id, QUDF

Once inserted, the Qno column in T1 needs to update to 1 (increment by 1 each time)

I have been on the forums but still nothing has resolved this. Please take note, only 1 row is being inserted at a time through the GUI

I have then created a trigger on the Opportunities table as this all needs to happen when an Opportunity is created by the user via the GUI.

SP:

ALTER PROCEDURE [dbo].[SP1_Test] @Opp_Id varchar (24), @Opp_Number int
    -- Add the parameters for the stored procedure here

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Declare @Qno varchar (50)
    Declare @NewQno varchar (50)

    SET @Qno= '0';

SELECT @NewQno =@Qno + 1 FROM T1 where ID = 1;

Insert into O_QUDF(Client_id,Contact_Number,Type_Id,Code_Id,O_QUDF)
values(@Opp_Id,@Opp_Number,1,0,@Qno)

Update T1 set Qno = @NewQno where ID = 1

END

GO

Trigger:
Trigger must only fire when Opp_type = 0

create TRIGGER [dbo].[Trg] ON  [dbo].[AMGR_Opportunity_Tbl] AFTER INSERT
AS
BEGIN

Declare @Opp_type varchar(24)
Declare @Opp_id int
Declare @Opp_number int

Select @Opp_type = Opp_Type from inserted
select @Opp_id = Opp_id from inserted
Select @Opp_number = Opp_Number from inserted

If @Opp_type = '0'

 BEGIN

Execute dbo.SP1_Test @Opp_id,@Opp_number

END

If someone can assist me in getting my scenario right - i am very new at this logic. I would greatly appreciate any suggestions.

SOLUTION to my issue was to simply add the following:

select @Opp_id = client_id from AMGR_User_Fields_Tbl;
Select @OppNo = Contact_number from AMGR_User_Fields_Tbl
 SELECT @NewQno = Convert(VARCHAR(750), Convert(INT, Qno) + 1)
    FROM T1 WHERE ID = 1;

To the code example below:

BEGIN
Declare @Opp_id varchar (24)
Declare @OppNo int
Declare @Opp_type int
DECLARE @NewQno VARCHAR(750)
declare @Qno varchar (50)

select @Opp_id = client_id from AMGR_User_Fields_Tbl;
Select @OppNo = Contact_number from AMGR_User_Fields_Tbl
 SELECT @NewQno = Convert(VARCHAR(750), Convert(INT, Qno) + 1)
    FROM T1 WHERE ID = 1;

If @Opp_type = '0'

 SELECT @NewQno = Convert(VARCHAR(750), Convert(INT, Qno) + 1)
    FROM T1 WHERE ID = 1;

    INSERT INTO O_QUDF(Client_id,Contact_Number,Type_Id,Code_Id,O_QUDF)
    values(@Opp_id,@OppNo,1,0,@NewQno)

UPDATE T1 SET Qno = @NewQno WHERE ID = 1

    End

Based on the limited information in your question, I made a few changes to your stored procedure.  If I've misunderstood something, let me know.  This code was not fully tested on my side.

ALTER PROCEDURE [dbo].[SP1_Test] @Opp_Id VARCHAR(24)
    ,@Opp_Number INT
    -- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @NewQno VARCHAR(50)

    --The Qno column will contain the current value, whatever that may be
    INSERT INTO O_QUDF (Client_id,Contact_Number,Type_Id,Code_Id,O_QUDF)
    SELECT @Opp_Id,@Opp_Number,1,0,Qno
    FROM T1 WHERE ID = 1;

    --While you can probably get away with doing simple arithmetic on NVARCHAR columns,
    --I think it's better to implicitly convert it to a numeric data type to do the arithmetic.
    --I chose Integer.
    --Then, you need to convert it back to nvarchar for the upcoming UPDATE
    SELECT @NewQno = Convert(NVARCHAR(50), Convert(INT, Qno) + 1)
    FROM T1 WHERE ID = 1;

    UPDATE T1 SET Qno = @NewQno WHERE ID = 1
END
GO


representation in ms-access I have a database in microsoft access.  I want to know how to look up a singular datum from a reference table giving a  dynamic set of values.  Here is a representation of what I mean:

I have the following tables:

Points for Pushups(m):

Reps      Age 17-21,          Age 22-26,         Age 27-31

1            6                  7                    8

2            7                  9                    9

3            9                  11                  12

Fitness Tests:

Name  Reps   Test Date

Bob      2            1 jan 2009

Jill     1            5 may 2010

People:

Name         DOB

Bob      1 jan 1987

Jill     2 feb 1985

Sal      3 Mar 1991

I want the query to use People.DOB and the Test date to find the age the person was during the test.  I then want the query to use this value to determine which column to look in, and the value from reps to determine which row to look in coming back with the singular value and naming it points.

for example I want bob to show

Query:

Name      DOB            Age AtTest   Reps      Points

Bob      1 Jan 1987         22         2          9

Does anyone know how to do the dynamic reference part?

I know how to make the query and I know how to get age I just don't know how to use the values as columns in the reference table, I've seen it done, but long ago and never looked into it.

The PointsForPushups table needs to be normalized first.  I suggest breaking it into the two following tables:

tblPointsForPushups
----------
reps | ageGroup | pushups
1 | A | 6
1 | B | 7
1 | C | 8
2 | A | 7
2 | B | 9
2 | C | 9
3 | A | 9
3 | B | 11
3 | C | 12

tblAgeGroups
----------
ageGroup | ageMin | ageMax
A | 17 | 21
B | 22 | 26
C | 27 | 31

With these tables in place then DateDiff and Between can easily be used in a query to find the required information.


Are database connections from an application expensive from a performance point of view? A colleague tells me that connections to a database are expensive and could harm performance of the application. They ask me to create a procedure that calls say another 8 procedures, that way they call one procedure and get back 8 datasets. From my viewpoint SQL is having to wait for each dataset to be returned before it can start to retrieve the next one so that will have a negative effect too.

Can anybody quantify the cost of the connection from an application and let me know whether the best approach is to call multiple procedures or one master procedure?

Well, there are four main aspects to this:

Why are you asking the general Interwebs for an opinion when there are three other sources that should be checked first, one of which will be definitive and not an opinion:

When someone tells you a technical / scientific piece of information, whenever possible, ask them how they know that to be the case. Did they read it somewhere? If so, was it a reputable source or was it mentioned in passing in a conversation they had several years ago that they were only partially paying attention to and also never asked that person how they happened to know this information. If it was an article / blog post, ask to see it so you can read it for yourself. If they did some testing, ask to see the test scripts so you can run them yourself.

The point is, a lot of people say a lot of things, sometimes being honestly convinced of the validity of information that is absolutely not true. So it is good of you to not just take their word for it, but whoever conveyed the information should be asked why they believe it to be true. Their answer could prove to be very insightful on a few levels :-).
Whether or not you are able to get an answer from the person making technical claims, do your own research. Do some searches to see if technical articles on this topic have already been written. Simply searching on "database connection performance" returns quite a few articles. Just be wary of random blog posts as people sometimes post information that they believe to be true but in fact isn't. And that brings us to....
The absolute best source of information for technical matters, especially with regards to "is approach A faster or slower than approach B" types of questions, is the system itself. Who cares what your colleague says, or even anyone else, no matter how well respected they may be, when your system will behave the way it is going to behave and it cannot be argued with. If someone says calling 8 procs in one call to the database is faster than 8 connections, well, try both and see which is faster. Of course, you may or may not be doing things correctly, but once you have a test set up and some results, then you can present those to see if somehow you missed something. And you can check against articles that have been written to see if their is a trick or option that you missed.

Generally speaking, yes, establishing new connections is expensive, which is why Connection Pooling exists. It keeps the connection open so that it can be re-used by the same client. Making a whole new connection requires allocating memory, checking security, which might involve making a request to yet another server if using Windows Authentication in an environment that uses Active Directory (since the auth info, if not yet cached, resides on the Domain Controller). All of this overhead is skipped when using Connection Pooling. If you are using .NET and connecting to Microsoft SQL Server, then Connection Pooling should be enabled by default. But you should check to make sure that it is used being.

Regardless of whether or not Connection Pooling is being used, are multiple calls in a single query batch faster than individual calls made via separate query batches? Well, if the issue is one of closing and re-opening the connection being expensive, then why close the connection? Why not just make 8 separate stored procedure calls on the same connection? At that point the only performance difference is the time it takes to make the 7 additional stored procedure requests. And that would be so minimal as to not even be worth thinking about unless this group of 8 stored procedures is being called many times per second. And that brings us too...
Most often, performance is a function of scale. There are many inefficient operations that do not show their inefficiency when only being called once or twice per day, or once or twice per month. But perform those operations multiple times per minute, or even second, and that same poor choice can have a drastic effect on performance. There are many bad choices people make when choosing datatypes for new fields (whether new table or just a new column on an existing table). They say, "disk is cheap so it doesn't matter than an INT is 3 bytes larger than a TINYINT and this field will only ever hold values of 1 - 10". An extra 3 bytes for 100 or even 1000 rows will never (or maybe I should just say "almost never" ;-) degrade performance. But that same field, if a PK, copied into 100+ tables as a Foreign Key, and those 100+ tables having a combined row count in the hundreds of millions range, will have an impact, and not just on query performance, but also backup and restore times, etc.

So, how often will these 8 stored procedures be called? If only a few times per hour or less, then you probably won't see much of a performance difference whether or not you call all 8 in one shot, or make 8 calls on the same connection, or make 8 calls on separate connections using Connection Pooling, or even making 8 separate calls on separate connections not using Connection Pooling. In this case you can also ask your colleagues why they are even concerned about connection overhead and why over-complicate a process for almost no gain.
Finally, since you mentioned using DataSets, does this mean that if you do make these 8 stored procedure calls in one shot, that you are loading all 8 result sets fully into memory at that time? If so, how large are these result sets? And not just in terms of rows, but also the size of each row? Are all 8 result sets needed at the same time? And are all 8 result sets always needed or are some only needed sometimes? This could be a situation where someone is trying to gain some efficiency on the connection overhead, but actually sacrificing a good amount of memory in order to do so. And if the connection overhead is really that much (due to the 8 procs being called many times per second AND no ability to use Connection Pooling), then maybe the additional memory overhead is worth it. But if not, then you could be wasting memory in order to solve a connection performance issue that might not even exist in the first place.


Speed up ORDER BY and LIMIT in MySQL I have a table with contact records, and clients wish to search this table to find records with a matching email address (filtered by account ID). The results are paginated by a RESTful API.

This results in queries like the below being run:

SELECT * FROM CONTACT
WHERE deleted is null AND email like '%Matt%' AND accountid=1
ORDER BY lastname LIMIT 0,50;

In this table, there are indexes on email, accountid, and lastname. Overall, this table has 3.6 million rows, and in this particular example this accountid has 170,000 rows. This particular query is taking around 2 minutes 30 seconds to complete.

If I change the query to remove the ORDER BY clause, the query finishes in about 0.6 seconds. If I change the query to remove the LIMIT clause, the query finishes in about 0.23 seconds.

I have read this article but I'm not sure what else I can do to improve this query. I know that the email index won't be used because of the full wildcard search, but I thought the lastname index would help things with the ORDER BY anyway.

mysql> explain SELECT * FROM CONTACT WHERE deleted is null AND email like '%Matt%' AND accountid=1 ORDER BY lastname LIMIT 0,50;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | a     | index | accountid     | lastname | 53      | NULL | 5804 | Using where |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)

How can I improve this query to finish quicker with the ORDER BY and LIMIT 0,50 clauses?

This composite index may help:

INDEX(account_id, lastname)

When adding it, you may as well remove INDEX(account_id), since it will then be redundant.

This may be even better, but I am not sure:

INDEX(deleted, account_id, lastname)

However it does not supersede INDEX(account_id).


Vertical Elastic Query is not identifying Object I did create an External Datasource, identical to the guide described here https://www.sqlservercentral.com/articles/cross-database-queries-in-azure-sql-database .The process is pretty simple, so just for illustration.
CREATE MASTER KEY ENCRYPTION ...
CREATE DATABASE SCOPED CREDENTIAL ...

CREATE LOGIN <externaldbname> WITH PASSWORD = '<somepassword1>';
CREATE USER externaldbname FOR LOGIN externaldbname;

CREATE USER externaldbname FOR LOGIN externaldbname;

CREATE EXTERNAL DATA SOURCE ...
CREATE EXTERNAL TABLE ..

All executed successfully. Now when I try to select something from the external database, this error is raised

Msg 46823, Level 16, State 1, Line 3
Error retrieving data from sar*****.database.windows.net.carDB.  The underlying error message received was: 'Invalid object name 'SalesLT.Addresstest'.'.

I didn't get the error msg as the method I followed is used/shown in multiple website and tutorials.

It looks permissions/authentication related to me.
Assuming that the 2 databases are on the same logical server, then your checklist of things to validate:
There is a login in the master database named
There is a user in the "carDB" database named  mapped to the login
The SalesLT.Addresstest table exists in carDB
The user  in carDB has SELECT permissions on the SalesLT.Addresstest table
Test the above by connecting to carDB as the user and selecting from SalesLT.addresstest
There is a MASTER KEY created in the database that you are querying from
There is a CREDENTIAL in the database that you are querying from whose identity matches
the LOGIN that was created - same name and password.
The EXTERNAL DATA SOURCE is created in the database you are querying from
The EXTERNAL TABLE is created in the database you are querying from and matches the schema of SalesLT.Addresstest