New SQL Server 2016 features to use in Acumatica

Hello everybody,

today I want to write about new additions for SQL Server T-SQL language, which are there starting from 2016. It is create or alter syntax.

In the past, if you've made some custom SQL view, it was needed to have constructions like this ( pseudo code ):

if view ( stored procedure, function, trigger ) exists

     create view ( stored procedure, function, trigger )

else

   alter view ( stored procedure, function, trigger )

But startring from 2016 SP1 you can use following syntax ( pseudo code ):

Create or alter view ( stored procedure, function, trigger ).

Or in code form it may look like this:

create or alter procedure yourProcedure
as
begin
 print (1)
end;
go
create or alter function yourFunction()
returns int
as
begin
 return(1)
end;
go
create or alter view yourSqlView
as
 select 1 as col;
go
create or alter trigger yourTrigger
on Product 
after insert, update 

Summary

With those SQL features your code of customizations may become much simpler and less prone to errors

How to deal with READ_COMMITED_SNAPSHOT error message in Acumatica?

Hello everybody,

today I want to leave SQL fix for error message:

"There are problems on database server side:

READ_COMMITTED_SNAPSHOT is not set for current database.

On your Acumatica instance it may look liike this:

For fixing run this SQL:

ALTER DATABASE Saddleback_staging SET READ_COMMITTED_SNAPSHOT ON

How to create unique index in MS SQL that will allow NULL

Hello everybody,

today I want to leave a post about the following case: you need to index rows in your database by some unique value, which can be null. And you don't want index to include those values which are null. 

How to achieve it? Following T-SQL can do this:

CREATE UNIQUE INDEX idx_studentcardid_notnull ON dbo.Students(studentcardid) WHERE studentcardid IS NOT NULL;

 

in this example MS SQL will create Unique index, but that index will be applied only to non null values, while null values will be ignored by WHERE condition.

What is Object_id in MS SQL

Hello everybody,

today I want to leave a short notice on OBJECT_ID in MS SQL.

Quite often I've seen sql like this:

IF OBJECT_ID(N'dbo.Students', N'U') IS NOT NULL DROP TABLE dbo.Students;

Today I have found time to discover what it actually means. 

OBJECT_ID is function, that checks wheather object exists or not. It accepts two parameters: object name and object type. In my case object name is dbo.Students, object type U is internal representation of table.

Performance difference in Acumatica between MS SQL and MySQL

Hello everybody,

today I want to write a few words about performance difference between MySQL and MS SQL in Acumatica.

Relatively recently Acumatica provided possibility to use MySQL as it's database. For some unknown reason

I didn't notice anybody who decided to use MySQL as their database solution. If you know any, please let me know,

how they like their experience. 

For me as developer it become interesting to check performance of MySQL vs MS SQL. Especially from standpoint of servers. 

Take note on how one of mine task managers look like:

as you can see I have 40 logical cores or 20 physical cores. Try to guess what is price of license for MS SQL for using at such a computer? According to this link one core costs 14 256 $, or 285 120$. Definetely prety costly.

And what would be price of MySQL for such a machine? Zero.

From this comparison MySQL looks very attractive. What about performance?

In order to test it, I've created small button code that executes creation of sales orders, and in the end gives some numbers. Below goes the code for cases if you wish to try it by yourself and compare my results with yours:



public class SOOrderEntryExt : PXGraphExtension<SOOrderEntry>
    {
        public PXAction<SOOrder> Test;
 
        [PXProcessButton]
        [PXUIField(DisplayName = "Test", MapEnableRights = PXCacheRights.Update, MapViewRights = PXCacheRights.Update)]
        public virtual IEnumerable test(PXAdapter adapter)
        {
            PXLongOperation.StartOperation(this, ()=>
            {
                var graph = PXGraph.CreateInstance<SOOrderEntry>();
                CreateSalesOrders(graph);
            });
            return adapter.Get();
        }
 
        private const int smallTest = 10;
        private const int mediumTest = 100;
        private const int bigTest = 500;
        private const int bigerTest = 1000;
        private const int bigerTest2 = 1500;
 
        public static void CreateSalesOrders(SOOrderEntry graph)
        {
            StringBuilder sb = new StringBuilder();
 
            var sw = new Stopwatch();
 
            ExecuteSOCreationInCycle(graph, sw, sb, smallTest);
            ExecuteSOCreationInCycle(graph, sw, sb, mediumTest);
            ExecuteSOCreationInCycle(graph, sw, sb, bigTest);
            ExecuteSOCreationInCycle(graph, sw, sb, bigerTest);
            ExecuteSOCreationInCycle(graph, sw, sb, bigerTest2);
 
            using (StreamWriter file =
                new StreamWriter(@"e:\Acumatica\results.txt"))
            {
                file.Write(sb.ToString());
            }
        }
 
        private static void ExecuteSOCreationInCycle(SOOrderEntry graph, Stopwatch sw, StringBuilder log, int numberOfCycles)
        {
            sw.Start();
            for (int i = 0; i < numberOfCycles; i++)
            {
                CreateSO(graph);
            }
            sw.Stop();
            log.Append($"{numberOfCycles} Sales orders = {sw.ElapsedMilliseconds} milliseconds\r\n");
        }
 
        private static void CreateSO(SOOrderEntry graph)
        {
            try
            {
                graph.Clear();
                var newSoOrder = graph.Document.Insert();
                graph.Document.SetValueExt<SOOrder.customerID>(graph.Document.Current, 4901);
                graph.Document.SetValueExt<SOOrder.status>(graph.Document.Current, "N");
                graph.Document.Update(newSoOrder);
 
                var transaction = graph.Transactions.Insert();
                graph.Transactions.SetValueExt<SOLine.inventoryID>(transaction, 456);
                graph.Transactions.SetValueExt<SOLine.orderQty>(transaction, 5.0m);
                graph.Transactions.SetValueExt<SOLine.curyUnitPrice>(transaction, 6.0m);
 
                graph.Transactions.Update(transaction);
 
                graph.Persist();
            }
            catch (Exception ex)
            {
                PXTrace.WriteError(ex);
            }
            
        }
    }

Basically that code adds button Test on screen Sales orders, and if you click on that button, it will execute single thread that will measure how many milliseconds it will take to create 10, 100, 500, 1000, 1500 sales orders. 

Below goes table which describes results on MS SQL and MySQL:

  MS SQL MySql
ms for 10 Sales Orders 9082 11005
ms for 100 Sales Orders 57059 70894
ms for 500 Sales Orders 332826 400403
ms for 1000 Sales Orders 1064203 1239445
ms for 1500 Sales Orders 2540225 2891575

As you can see from the table, MS SQL is as usually around 20% faster then MySql.

If you prefer to see diagrams, here is the one:

 

lower means better in this case.

Summary

As you see from the test, MySql is slower on 20% then MS SQL. It means that if you need something 20% faster then the other and can affort it, go on MS SQL. If you want to get cheaper, and don't plan to have huge loading on your Acumatica then MySQL can help you to save some money

How to make MS SQL stored procedure that is protected from SQL Injection

Hello everybody,

today I want to write a simple note on how to make stored procedures in MS SQL server which are protected from SQL injections. 

Below goes example of SQL stored procedure, that is vulnerable to SQL injection attacks:

-- Bad code, don't use it ever
CREATE PROCEDURE SearchCustomers
	@searchCust varcahr(50)
AS
BEGIN
	DECLARE @query VARCHAR(100)
	SET @query = 'SELECT * FROM Customer WHERE NAME LIKE ''%' + @searchCust + '%'''
	EXEC (@query)
END

Why it is bad? Because if somebody passes into @searchCust value or 1=1 --- then SQL will return all customers. If to add a bit more creativity it's possible to get from that database plenty of information.

If you wonder, what are safe ways of usage dynamic queries, take a look at another example:

-- Much more protected, you can use it
CREATE PROCEDURE SearchCustomers
	@searchCust varcahr(50)
AS
BEGIN
	DECLARE @query NVARCHAR(100)
	SET @query = 'SELECT * FROM Customer WHERE NAME LIKE ''%'' + @searchCust + '%''''
	EXEC sp_executesql @query, N'@searchCust varchar(50)', @searchCust = @searchCust
END

In this case passed parameter will be executed as part of stored procedure.

Also for those who are curious about tools that can automate SQL injection inspection, consider tool Havij SQL Injection

How to make back up of db smaller in MS SQL

Hello everybody,

today I want to document one hack that preserved me plenty of space on my hard drive.

Recently I've tried to make back up of production database and was shocked to see that it had almost 200 Gb on my hard drive.  So it made me wonder how can I have smaller backups. 

And here is the solution.

  1. In Microsoft SQL Server Management Studio click on database, and choose new query
  2. enter there following command: sp_configure 'backup compression default', 1
  3. then this: reconfigure WITH OVERRIDE;

After such manipulations I've got back up that had only 27 Gb.

Hierarchy for CTE in MS SQL

Hello everybdoy,

today I want to write some notes about hierarchy research in ms sql database. 

Imagine you have following structure in your table 

TableWithParentChild:

id               |           rootID   |     some additional fields

1                |            1          |      ----

2                |            1          |      ----

3                |            2          |      ----

4                |            2          |      ----

5                |            2          |      ----

 

and image that you need to get info like who is below 1. 

for this purpose following sql can be useful:

-- wrap up tree selection into tvf
create function HierarchyFunction(@parentID int)
returns table
as
return
with tree
as
(select id,  rootID from TableWithParentChild
         where id = @parentID
         union all
         select p.id, p.rootID from TableWithParentChild as P
         join tree on tree.id = P.rootID
         and P.id != P.rootID
)
select * from tree

select p.* from  TableWithParentChild as p
join HierarchyFunction(1) as tree
on p.id = tree.id

Query plan in MS SQL

Hello everybody,

some MS SQL internals.

Imagine, you want to see what MS SQL cached. How you can do it? One of the ways is to look into sys.dm_exec_cached_plans. 

For example like this:

select * from sys.dm_exec_cached_plans

this code will return to you all what is cached in your MS-SQL database. Most probably result will puzzle you. What to do with all of it?

Take look at the screenshot :

the area of interest for us is column plan_handle. 

You can use it for another sql request. Like this:

select * from sys.dm_exec_query_plan(0x06000C002D51D1304021F8AE000000000000000000000000);

if you click at column with xml you'll see the following picture:

How to interpret those results? I hope to describe in one of other notes on my blog.

For now another question, how to see the text of cached value? 

You can do it with another sql 

for example like this:

select * from sys.dm_exec_sql_text(0x06000C002D51D1304021F8AE000000000000000000000000)

in the column text you'll see column text which will represent what MS SQL cached. In my case it is following text:

select [first name], [last name], dense_rank() over (order by [last name]) as rn from employees

 

SQL Formatting tool

Few days ago I faced following challenge. SQL profiler gave me ugly SQL, which was unreadable for my eyes.

For example like this:

exec sp_executesql N'SELECT APAddress.AddressID, APAddress.VendorID, APAddress.VendorAddressID, APAddress.IsDefaultAddress, APAddress.RevisionID, APAddress.AddressLine1, APAddress.AddressLine2, APAddress.AddressLine3, APAddress.City, APAddress.CountryID, APAddress.State, APAddress.PostalCode, APAddress.IsValidated, APAddress.tstamp, APAddress.CreatedByID, APAddress.CreatedByScreenID, APAddress.CreatedDateTime, APAddress.LastModifiedByID, APAddress.LastModifiedByScreenID, APAddress.LastModifiedDateTime FROM APAddress APAddress WHERE (APAddress.CompanyID = 2) AND  ( APAddress.AddressID = @P0) ORDER BY APAddress.AddressID /* admin@AP.30.40.00 */',N'@P0 int',@P0=-2147483647

If you can read it, congratulations, you are genius and I'm envy of you so you can stop reading further. But if you are just programmer as me, then the next step is the following:

1. remove exec sp_executesql 

2. In some editor find and replace @P0 with value -2147483647

3. remove tail /* admin@AP.30.40.00 */',N'@P0 int',@P0=-2147483647

4. Navigate in your browser to http://www.dpriver.com/pp/sqlformat.htm

5. Copy/Paste in window and choose format mssql

6. Press format SQL.

 

Compare previous sql with the following:

SELECT apaddress.addressid,
       apaddress.vendorid,
       apaddress.vendoraddressid,
       apaddress.isdefaultaddress,
       apaddress.revisionid,
       apaddress.addressline1,
       apaddress.addressline2,
       apaddress.addressline3,
       apaddress.city,
       apaddress.countryid,
       apaddress.state,
       apaddress.postalcode,
       apaddress.isvalidated,
       apaddress.tstamp,
       apaddress.createdbyid,
       apaddress.createdbyscreenid,
       apaddress.createddatetime,
       apaddress.lastmodifiedbyid,
       apaddress.lastmodifiedbyscreenid,
       apaddress.lastmodifieddatetime
FROM   apaddress APAddress
WHERE  ( apaddress.companyid = 2 )
       AND ( apaddress.addressid = -2147483647 )
ORDER  BY apaddress.addressid 

IMHO the second option is easier to read.