Mutiple Inserts. Normal method VS Union ALL method

I know the eternal war between DBA’s and Developers rages on :D, While most developers prefer to make multiple inserts. DBA’s always use UNION all. While highly debatable which is fast performance wise. But if you are doing thousands of insert’s UNION ALL is definitely faster. I have narrowed down inserts from 40 second to milliseconds using UNION ALL.

How to do UNION ALL

  1. Consider a table MyTable with two columns : FirstCol, SecondCol
  1. Normal insert on this table you would do as follows

INSERT INTO MyTable  (FirstCol, SecondCol)

VALUES (‘First’,1);

INSERT INTO MyTable  (FirstCol, SecondCol)

VALUES (‘Second’,2);

INSERT INTO MyTable  (FirstCol, SecondCol)

VALUES (‘Third’,3);

INSERT INTO MyTable  (FirstCol, SecondCol)

VALUES (‘Fourth’,4);

INSERT INTO MyTable  (FirstCol, SecondCol)

VALUES (‘Fifth’,5);

COMMIT;

The clause INSERT INTO is repeated multiple times. Many times we copy and paste to save time. Now let’s do it the alternative way

  1. UNION ALL alternative method

INSERT INTO MyTable (FirstCol, SecondCol)
SELECT 'First' ,1
UNION ALL
SELECT 'Second' ,2
UNION ALL
SELECT 'Third' ,3
UNION ALL
SELECT 'Fourth' ,4
UNION ALL
SELECT 'Fifth' ,5

COMMIT;

If you are doing more than 1K inserts, then this is the simplest way. Anything above 10K inserts should use BULK INSERT method or Open Cursors in a loop.

Advertisements

MRemote multi-tab remote connection manager

If you have multiple remote connections like VNC/ RDP/ SSH/ TELNET. There is a good software to manage multiple connections and save it for future use. It keeps all connections in a uniform tab-based format. Very powerful and useful tool. The best part : It is completely free and open source !

mRemote : full-featured, multi-tab remote connections manager. You can create Remote desktop, SSH, telnet, VNC and many more connections within the same Window. It can be downloaded from below site.

http://www.mremote.org/wiki/Downloads.ashx

Alternative to GREP in Windows

C:\Program Files\Windows Resource Kits\Tools>findstr /?
Searches for strings in files.

FINDSTR [/B] [/E] [/L] [/R] [/S] [/I] [/X] [/V] [/N] [/M] [/O] [/P] [/F:file]
[/C:string] [/G:file] [/D:dir list] [/A:color attributes] [/OFF[LINE]]
strings [[drive:][path]filename[ …]]

/B         Matches pattern if at the beginning of a line.
/E         Matches pattern if at the end of a line.
/L         Uses search strings literally.
/R         Uses search strings as regular expressions.
/S         Searches for matching files in the current directory and all
subdirectories.
/I         Specifies that the search is not to be case-sensitive.
/X         Prints lines that match exactly.
/V         Prints only lines that do not contain a match.
/N         Prints the line number before each line that matches.
/M         Prints only the filename if a file contains a match.
/O         Prints character offset before each matching line.
/P         Skip files with non-printable characters.
/OFF[LINE] Do not skip files with offline attribute set.
/A:attr    Specifies color attribute with two hex digits. See “color /?”
/F:file    Reads file list from the specified file(/ stands for console).
/C:string  Uses specified string as a literal search string.
/G:file    Gets search strings from the specified file(/ stands for console).
/D:dir     Search a semicolon delimited list of directories
strings    Text to be searched for.
[drive:][path]filename
Specifies a file or files to search.

Use spaces to separate multiple search strings unless the argument is prefixed
with /C.  For example, ‘FINDSTR “hello there” x.y’ searches for “hello” or
“there” in file x.y.  ‘FINDSTR /C:”hello there” x.y’ searches for
“hello there” in file x.y.

Regular expression quick reference:
.        Wildcard: any character
*        Repeat: zero or more occurances of previous character or class
^        Line position: beginning of line
$        Line position: end of line
[class]  Character class: any one character in set
[^class] Inverse class: any one character not in set
[x-y]    Range: any characters within the specified range
\x       Escape: literal use of metacharacter x
\<xyz    Word position: beginning of word
xyz\>    Word position: end of word

For full information on FINDSTR regular expressions refer to the online Command
Reference.

 

netstat -ano | findstr LISTENING

TCP    0.0.0.0:7              0.0.0.0:0              LISTENING       1596
TCP    0.0.0.0:9              0.0.0.0:0              LISTENING       1596
TCP    0.0.0.0:13             0.0.0.0:0              LISTENING       1596
TCP    0.0.0.0:17             0.0.0.0:0              LISTENING       1596
TCP    0.0.0.0:19             0.0.0.0:0              LISTENING       1596
TCP    0.0.0.0:21             0.0.0.0:0              LISTENING       1848
TCP    0.0.0.0:135            0.0.0.0:0              LISTENING       904