Tuesday, January 15, 2019

Deleting old records in large table in SQL Server

Let's face it DELETE command is really slow. It is often quite a challenge to delete multiple records. If there is no WHERE clause then it is much better to use TRUNCATE, but if you have to use WHERE you are back to square one :). This is my approach how to use DELETE command on a large set of data:

DECLARE @rows INT = 1
SET @rows = 1

WHILE @rows > 0
DELETE TOP (5000) [dbo].[MyLog]
WHERE CreationDateTime <= DATEADD(MONTH, - 6, GETDATE())

SET @rows = @@ROWCOUNT;

Tuesday, April 3, 2018

ORA-01795: maximum number of expressions in a list is 1000

ORA-01795: maximum number of expressions in a list is 1000
01795. 00000 -  "maximum number of expressions in a list is 1000"
*Cause:    Number of expressions in the query exceeded than 1000.
           Note that unused column/expressions are also counted
           Maximum number of expressions that are allowed is 1000.
*Action:   Reduce the number of expressions in the list and resubmit.
Error at Line: 1,002 Column: 1

This is one of the differences between SQL server (management studio) and Oracle (Oracle SQL developer). It is especially problematic when I have a list of values in where statement. Use SQL server whenever possible.

Friday, March 9, 2018

github user set localy for the project

Often git user that I use on my machine is related with the company that I am working for. Sometimes I need to access my personal repositories to get some useful code, snipped or component that I can use. Sometimes during this process I modify something in that component and I want to push it to github as me and not as a different git user. Important part is to set local git preferences for the project; following commands are useful:

git config -llist all the config for git
git config -l --locallist config that is specific for given project
git config -l --globallist global configuration

Edit local config, set github username and email address to the one that is used in github.
git config -e --local

Set following fields:
 email = foobar@gmail.com 
 name = FirstName LastName 

Not being able to login to npm -- npm adduser not working

In various organisations it is common to code in environment that requires you to connect to a organisation specific repository or to use a specific user. Sometimes you want to push your package to a global npm (https://registry.npmjs.org/) repository and the process doesn't work. What I enjoy doing is to setup a local .npmrc file inside a project structure. Inside it I specify the registry configuration like:

registry = "https://registry.npmjs.org/"

adduser command runs in a scope of a registry. Often when you try to use it it tells you that your username doesn't exist, or user was incorrect because you were verifying against a server that is specified by registry parameter. Without running adduser command often your environment will pickup user credentials that are specified by your systeadmins and are related to a registry that is specific for the organization. That's why when you run following command w/o running ever adduser it often displays username:

If you run adduser command in a directory that has registry set to a different location your result will be different:

It is worth noticing that adduser command doesn't add any lines to local .npmrc file. File will only contain registry entry.

Wednesday, October 25, 2017

Windows Cannot Install Required Files. Make sure all the files required for installation are available, and restart the installation. Error code: 0x8007025D

Windows Cannot Install Required Files. Make sure all the files required for installation are available and restart the installation. Error code: 0x8007025D

Usually, people connect this problem with media error - your installation source is corrupted. Microsoft and various guids recommend creating new installation CD or USB. In my case, it was my hard drive. I had to delete the partition and experiment with splitting original partition into smaller ones. The goal is to have one as big partition as possible that is healthy - because Program Files folder grows exponentialy :) It took me some time (in days) to finally get it right.

Friday, September 8, 2017

Floating point arithmetic in 21 century

I really hope that entire concept will be dropped one day. Current approach was useful 30 years ago, now we have much more memory and we can do it in a much better way. And yet when I look at JavaScript:
0.1 + 0.2 === 0.3 //false
(0.1 + 0.2) + 0.3 === 0.1 + (0.2 + 0.3) //false

It is all beacause of 0.1 representation in IEEE standard.
s eeeeeeee mmmmmmmmmmmmmmmmmmmmmmm    1/n
0 01111011 10011001100110011001101
           |  ||  ||  ||  ||  || +- 8388608
           |  ||  ||  ||  ||  |+--- 2097152
           |  ||  ||  ||  ||  +---- 1048576
           |  ||  ||  ||  |+-------  131072
           |  ||  ||  ||  +--------   65536
           |  ||  ||  |+-----------    8192
           |  ||  ||  +------------    4096
           |  ||  |+---------------     512
           |  ||  +----------------     256
           |  |+-------------------      32
           |  +--------------------      16
           +-----------------------       2
The sign is positive, that's pretty easy.

The exponent is 64+32+16+8+2+1 = 123 - 127 bias = -4, so the multiplier is 2-4 or 1/16.

The mantissa is chunky. It consists of 1 (the implicit base) plus (for all those bits with each being worth 1/(2n) as n starts at 1 and increases to the right), {1/2, 1/16, 1/32, 1/256, 1/512, 1/4096, 1/8192, 1/65536, 1/131072, 1/1048576, 1/2097152, 1/8388608}.

When you add all these up, you get 1.60000002384185791015625.

When you multiply that by the multiplier, you get 0.100000001490116119384765625, which is why they say you cannot represent 0.1 exactly as an IEEE754 float.

Wednesday, September 6, 2017

choreography vs orchestration

I see in more and more articles that authors are using both terms interchangeably. As far as I can tell there is no formal definition, just like with terms agile, microservices, SOA, but... in ancient days when I was studding computer science on a university there was a difference:
  • choreography: distributed decision making
  • orchestration: centralised decision making