SQL Comparison - MS TSQL Oracle PL/SQL and MySQL SQL

Comparison of particular techniques, even if trivial in scope, can reveal the different philosophy between different systems, in this post the techniques for extracting the filename (aka basename) from a string in compared in Oracle SQL, T-SQL and MySQL.

 Extracting filename from path.

The task - extract all text after the last path seperator, for simplicity in these examples I assume the DOS reverse slash character '\', however any technique should handle both.

MS T-SQL.

This uses the reverse function to find the last seperator, and then reverses the substring to return it correctly:

declare @filepath varchar(256)
set @filepath = '\\sydnet01.fileserver.com.au\mmfile02\118688\03151060720004\441002041v11.doc'
select reverse(@filepath), reverse(substring(reverse(@filepath),0, charindex('\', reverse(@filepath))))

Oracle SQL.

This uses in the instr function which can be passed a negative start position to search for the last occurrence:

VARIABLE filepath VARCHAR2(256);
EXEC :filepath := '\\sydnet01.fileserver.com.au\mmfile02\118688\03151060720004\441002041v11.doc';
SELECT :filepath  FROM dual;
SELECT substr(:filepath,(instr(:filepath,'\',-1,1)+1),length(:filepath))FROM dual;

MySQL.

MySQL is even simpler than Oracle, the SUBSTRING_INDEX function returns the substring portion from a match. Like the Oracle instr function it supports a negative index.

SET @filepath = '\\\\sydnet01.fileserver.com.au\\mmfile02\\118688\\03151060720004\\441002041v11.doc';
SELECT SUBSTRING_INDEX(@filepath, '\\', -1) as filename;

Format Numbers With Commas.

When reporting large numbers being able to format them with commas or spaces for thousand seperators is useful.

MS T-SQL.

T-SQL does not have the ability to do this easily. One reason for this is because presentation shouldn't be included in SQL running against the database. While true (as far as it goes), it is useful to be able to run a SQL query and format the results, so it can be easily read and interpreted.
The method below converts (casts) the number to the money type, which then supports string conversion with adding commas as a thousands seperator (CONVERT(varchar, 2).

SELECT replace(CONVERT(varchar, CAST(987654321 AS money), 1),'.00','')

As the money format adds 2 decimal places, the replace function removes them.

Oracle SQL*Plus

The command line utility SQL*Plus has reporting functions built into it. To do simple output of large figures:

col count format 999,999,999
select count(*) as count, to_char(date_created, 'YYYY-MON') as monthcreated from docs where date_Created >'01-JAN-2012' group by to_char(date_created, 'YYYY-MON');
       COUNT MONTHCRE
------------ --------
      71,852 2012-APR
      63,757 2012-JAN
      64,045 2012-JUN
      81,481 2012-MAY
      14,494 2012-JUL
      79,554 2012-FEB
      93,214 2012-MAR

MySQL

Bulk Loading of Data.

SQL Server.

SQL Server has the bcp utility:

 bcp {dbtable | query} {in | out | queryout | format} datafile

Oracle

Oracle has sqlload utility, full details are at Orafaq wiki, an example:

load data
 infile *
 replace
 into table pf.doc_load
fields terminated by "," optionally enclosed by '"'
 (  doc_id
 )
begindata
119589984
119279372

or in a seperate file:

load data
 infile '/data/ora102/temp/mydata.csv'
 replace
 into table pf.doc_load
fields terminated by "," optionally enclosed by '"'
 (  doc_id
 )

Adventures with Windows Server IIS

Sometimes you spend time searching for and fixing the same problem more than once.
I guess I could blame a non-intuitive interface or bad error logging, but as they say a good craftsman never blames his tools.

IIS has been getting more and more secure - which is great, but sometimes the tools or the logging doesn't keep up.
An example is the configuration of ASP.NET on IIS. There are all sorts of potential issues -for example make sure you are running a different application pool for sites running different versions of ASP.Net (which you are warned about in the interface), but sometimes the problem is much simpler.
ASP.NET runs as a Web Service extension (which other interpreters like php will do), and they can be individually enabled or disabled.
What can work ok on one server won't work on another if the particular version of ASP.Net is not enabled.

The utility %SystemRoot%\Microsoft.NET\Framework\v4.0.30319\aspnet_regiis is your typical windows program, running it with the flags -lk

C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319> aspnet_regiis -lk
W3SVC/1727045652/root/  2.0.50727.3053
W3SVC/2017800512/root/  4.0.30319.0
W3SVC/1934388718/root/  4.0.30319.0

Will list what sites you have and their ASP.Net version, it does a bunch of other stuff as well.

About the DOS command line

Yes there is now powershell, but if you have a Unix/Linux background it is hard to become motivated about another command line interface from Microsoft.

Having said that you can do some useful things with the MS Dos command line interface, many of them revolve around the find command.

Another useful tool is xcopy, which is good, but for me lacks one feature in particular; Pixelabs have the excellent xxcopy - which is a drop in replacement for xcopy.
The feature I wanted is to only copy files that are not on the destination.
To do this issue xxcopy with the /bb option:

c:\tools\xxcopy.exe l:\CZK01 t:\CZK01 /s /i /bb

The others are
/s Copies directories and subdirectories except empty ones.(or can use /e)
/i Create destination if it doesn't exist as a directory.

If you use xxcopy please purchase it, note that I don't think they have a Unix background -
Make each program do one thing well. To do a new job, build afresh rather than complicate old programs by adding new features.

Checking Error codes:

Always good to check the return code of a previous command before renameing, editing files:

c:
cd c:\datafilepath
if ERRORLEVEL 1 goto end
:rename
echo cd to c:\
datafilepath ok
rem do operations here
:end

Just Listing Directories:

dir /a:d  /s /b

C:\Temp\COMPAQ\NIC\PROSETDX
C:\Temp\COMPAQ\NIC\PROSETDX\DRIVERS
C:\Temp\COMPAQ\ProtectTools\BIOS_Configuration
C:\Temp\COMPAQ\ProtectTools\Device_Access_Mgr

Oracle SQL Techniques

Oracle SQL has the most features and flexibility of all of the implementation.
Oracle RDMS and SQL features usually appear downsteam in othe databases.

Functional Indices.

See http://www.akadia.com/services/ora_function_based_index_2.html

Firstly create a function:

create or replace function matterl0(matter_id in varchar2) return varchar2
deterministic
as
begin
  return '0'||matter_id;
end;
/

Then you can use this or any built in function in an index.

Keeping an eye on I/O waits

With the growth in use of shared storage and databases running in virtual machines, a common problem over the last couple of years has been slow disk I/O.
System administrators tend to keep an eye on CPU resources in virtual systems, and CPU starvation for a virtual machine is one of the first thinks looked at, however storage is less so.

This manifests itself in system problems:

• at a particular time of the day (say every evening at 10:00pm)
• systems do perform ok, then deteriorate.
• systems that are migrated to newer hardware, but perform poorly.

Apart from lots of memory, databases really need fast consistent disk I/O.

When looking at a poorly performing system you should ask:

• How much memory (relative to the size of the database) does the system have?
• What is the load - number of concurrent sessions, number of active sessions?
• What is the disk I/O sub system?

As an example when looking at a Unix/Linux system you should check exactly what the storage system is even if the disk devices look like the standard (sda, sdb, sdc etc).

Some configurations of virtual machines and shared storage do present standard hard disk devices even if they are virtualised.

Linux/Unix Systems.

Run iostat over time to see what the busy devices are, an in particular what the %iowait statistic is:

A 'healthy' system:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle

           0.00    0.00    1.60    9.22    0.20   88.98

A system to investigate:

avg-cpu:  %user   %nice    %sys %iowait   %idle

           4.00    0.00    1.10   49.25   45.65

IOStat gets its data from the kernel iostats module, which writes to /proc/doskstats.

The fields are:

Field 1	# of reads completed	This is the total number of reads completed successfully.
Field 2	# of reads merged, field 6	# of writes merged	Reads and writes which are adjacent to each other may be merged for efficiency. Thus two 4K reads may become one 8K read before it is ultimately handed to the disk, and so it will be counted (and queued) as only one I/O. This field lets you know how often this was done.
Field 3	# of sectors read	This is the total number of sectors read successfully.
Field 4	# of milliseconds spent reading	This is the total number of milliseconds spent by all reads (as measured from __make_request() to end_that_request_last()).
Field 5	# of writes completed	This is the total number of writes completed successfully.
Field 7	# of sectors written	This is the total number of sectors written successfully.
Field 8	# of milliseconds spent writing	This is the total number of milliseconds spent by all writes (as measured from __make_request() to end_that_request_last()).
Field 9	# of I/Os currently in progress	The only field that should go to zero. Incremented as requests are given to appropriate struct request_queue and decremented as they finish.
Field 10	# of milliseconds spent doing I/Os	This field increases so long as field 9 is nonzero.
Field 11	weighted # of milliseconds spent doing I/Os	This field is incremented at each I/O start, I/O completion, I/O merge, or read of these stats by the number of I/Os in progress (field 9) times the number of milliseconds spent doing I/O since the last update of this field. This can provide an easy measure of both I/O completion time and the backlog that may be accumulating.

Windows.

In performance manager look at the % Disk Time, Current Disk Queue Length counters. The Split I/O counter is also useful to see if there is a data block mismatch between Windows and the physical device.

Handling MS SQL Server requiring Large Numbers of worker threads

SQL server since version 2005 has had a default of 0 for the number of worker threads, meaning that the value is set by SQL Server. In the books online the number of threads set vary by the number of processors for 32bit and 64 bit systems:

Number of CPUs	32-bit computer	64-bit computer
<= 4 processors	256	512
8 processors	288	576
16 processors	352	704
32 processors	480	960

This value seems to work ok in most cases, however in certain situations, it needs to be checked and adjusted.

Number of threads in use:

The total number of system threads is found in performance meter or from the Windows tasks manager; in the performance tab:

The number of SQL Server running threads can be found from performance monitor, or from this query: 

select count(*) from sys.dm_os_threads

If the limit is being approached then you need to look at increasing the number of threads. Most threads are pooled - and only used when required, however operations such as replication, and in particular database mirroring, dedicate threads at database start that are never available to be reused.

I looked at system with over 100 databases that were being mirrored. The mirroring server was slow and unresponsive and when mirroring was configured on additional databases, any new connections to the database were denied.

Increasing the number of worker threads which was at 512 for a 64bit 4CPU system resolved the problem.

In the long term this site is likely to look at SQL 2012 Availability Groups.

When increasing the worker threads, Microsoft recommend 1024 as the maximum for 32-bit SQL Server and 2048 for 64-bit SQL Server.

for more information see MSDN - max worker threads option

HTTP Handlers Part 1 IIS - dotNet

This is the first part of a series of posts documenting HTTP Handlers across different web application servers.
The first example is ASP.Net running on IISv7.

The process as documented in MSDN <a href='http://msdn.microsoft.com/en-us/library/ms228090(v=vs.90).aspx'>Walkthrough: Creating a Synchronous HTTP Handler</a> and <a href='http://msdn.microsoft.com/en-us/library/bb515343(v=vs.90).aspx'>How to: Configure an HTTP Handler Extension in IIS</a>

1. Create a new website (e.g.  http://localhost/HttpHandler)
2. Create a handler class (e.g. public class HelloWorldHandler : IHttpHandler)
3. Edit the web.config file to register the handler.
4. Register the required application extension mapping for the new Website by using IIS Manager. (in MSDN this the extension is *.sample)
5. Test it, e.g. http://localhost/ HttpHandler/test.sample