I am actually surprised that postgres does not have a builtin function to deal with unix timestamps. After all it does run on linux which does use unix timestamps. So it must be a common problem. Here is a quick solution.

CREATE OR REPLACE FUNCTION parseunixtimestamp(int)
	RETURNS timestamp AS 
		'SELECT TIMESTAMP ''epoch'' + $1 * INTERVAL ''1 second'''
	LANGUAGE SQL;

Now you can use it in select or insert statements. Like this

INSERT INTO TABLE (name, createdon) VALUES("a value", parseunixtimestamp(55436437))

If you need to you can remove it again later by running

DROP FUNCTION parseunixtimestamp(integer);

Normally when people are trying to put a point across about using stored procedures in an application. The most common thing that is normally pointed out is about network bandwidth is normally presented. Some other things like memory saving are normally missed. This is an example on how to lower the memory footprint in sql server when using stored procedures instead of sql queries being stored in the application and being passed.

If you have looked into this before then you will be aware that using a stored procedure will save sql server compiling execution plans when using stored procedures. This also saves some memory. The following example will show how and just how much memory is being saved for a single query.

In the following example I have a service running collecting netflow packets from a cisco router and saving these in an sql table that has the following format.

CREATE TABLE [dbo].[Flow](
	[FlowID] [uniqueidentifier] NOT NULL,
	[Router] [varchar](15) NOT NULL,
	[Source] [varchar](15) NOT NULL,
	[SourcePort] [int] NOT NULL,
	[Destination] [varchar](15) NOT NULL,
	[DestinationPort] [int] NOT NULL,
	[InterfaceIn] [int] NOT NULL,
	[InterfaceOut] [int] NOT NULL,
	[Packets] [bigint] NOT NULL,
	[Bytes] [bigint] NOT NULL,
	[Protocol] [int] NOT NULL,
	[LoggedAt] [datetime] NOT NULL,
 CONSTRAINT [PK_Flow] PRIMARY KEY CLUSTERED 
(
	[FlowID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Flow] ADD  CONSTRAINT [DF_Flow_FlowID2]  DEFAULT (newsequentialid()) FOR [FlowID]
GO

The data is being stored with the following insert statement which is being called from the service each time.

INSERT INTO Flow (Router, Source, SourcePort, Destination, DestinationPort, InterfaceIn, InterfaceOut, Packets, Bytes, Protocol, LoggedAt) VALUES(@Router, @Source, @SourcePort, @Destination, @DestinationPort, @InterfaceIn, @InterfaceOut, @Packets, @Bytes, @Protocol, @LoggedAt)

This looks stright forward enough. However if you examine the query plan cache by using the following sql.

SELECT
	P.usecounts AS TotalCount,
	P.size_in_bytes,
	SqlText.text
	FROM sys.dm_exec_cached_plans AS P
		CROSS APPLY sys.dm_exec_sql_text(P.plan_handle) AS SqlText
	WHERE SqlText.text like '(@Router%'
	ORDER BY P.usecounts DESC

It will show how many times the sql has been compiled to an execution plan and cached. It will also give usage counts for each of the plan caches. The following has been trimmed. There was originally about 30 rows of output for a single query.

TotalCount	size_in_bytes	(No column name)
56192	65536	(@Router nvarchar(12),@Source nvarchar(10),@Sourc
32482	65536	(@Router nvarchar(12),@Source nvarchar(11),@Sourc
15267	65536	(@Router nvarchar(12),@Source nvarchar(10),@Sourc
14742	65536	(@Router nvarchar(12),@Source nvarchar(11),@Sourc
11676	65536	(@Router nvarchar(12),@Source nvarchar(10),@Sourc
10953	65536	(@Router nvarchar(12),@Source nvarchar(10),@Sourc
10612	65536	(@Router nvarchar(12),@Source nvarchar(10),@Sourc
9512 	65536	(@Router nvarchar(12),@Source nvarchar(12),@Sourc

A number of cached plans have been created because the passed sql from the program has a number of differences. In particular as the length of the strings change the sql is using different types because the data length of the type has changed and sql server considers this to be a different type being passed. This causes sql server to create a new execution plan for every different length of string. This can result in a single query producing multiple execution plans which is using more memory. In my example above the total memory usage for all the execution plans was around 5MBytes. This may not sound much but I was only passing 3 strings which are only variable in length between 8 and 15 long.

When using a stored procedure the above issue with the execution plans does not occur.

When changing the insert statement to a simple stored procedure. 

CREATE PROCEDURE [dbo].[FlowInsert]
	@Router VARCHAR(15),
	@Source VARCHAR(15),
	@SourcePort int,
	@Destination VARCHAR(15),
	@DestinationPort int,
	@InterfaceIn int,
	@InterfaceOut int,
	@Packets bigint,
	@Bytes bigint,
	@Protocol int,
	@LoggedAt datetime
AS

INSERT INTO Flow
(Router, Source, SourcePort, Destination, DestinationPort, InterfaceIn, InterfaceOut, Packets, Bytes, Protocol, LoggedAt)
VALUES(@Router, @Source, @SourcePort, @Destination, @DestinationPort, @InterfaceIn, @InterfaceOut, @Packets, @Bytes, @Protocol, @LoggedAt)

GO

Then looking into the execution plan cache again using the sql above it will be reduced to a single entry using a total of 57kbytes of memory. That is saving around 4-5MBytes of memory which sql server can use else where. This may not sound like much but considering that it is a single query and it can be common for even small applications to have 100 or more quries it can be saving quite significant amounts of memory.

In mssql server it is possible to find the last backup time as well as the largest size or compressed size of the backups. This can be down by listing the databases using the system table sys.databases and reading from the msdb.dbo.backupset which is one of the tables that is updated when a backup is taken on the server. The sizes returned will be in bytes.

SELECT db.name AS DBName,
	COALESCE(CONVERT(VARCHAR(MAX), MAX(bs.backup_finish_date), 101), 'N/A') as LastBackup,
	MAX(bs.backup_size) AS LargestBackup,
	MAX(bs.backup_size) AS LargestCompressedBackup
	FROM sys.databases db
	LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = db.name
GROUP BY db.name
ORDER BY db.name

It would also be possible to include the maximum time a backup has taken for a database by modifiing the sql above and using datediff and max on the start and finish time of the backup. So the following can be used to find out how much space and time a backup will be required for each database.

SELECT db.name AS DBName,
	COALESCE(CONVERT(VARCHAR(MAX), MAX(bs.backup_finish_date), 101), 'N/A') as LastBackup,
	COALESCE(CONVERT(VARCHAR(MAX), MAX(DATEDIFF(second, bs.backup_start_date, bs.backup_finish_date)), 101), 'N/A') as RunTime,
	MAX(bs.backup_size) AS LargestBackup,
	MAX(bs.backup_size) AS LargestCompressedBackup
	FROM sys.databases db
	LEFT OUTER JOIN msdb.dbo.backupset bs ON bs.database_name = db.name
GROUP BY db.name
ORDER BY db.name

The msdb.dbo.backupset is usful for finding out the entire history of backups taken on a database. More information can be found out about it at http://technet.microsoft.com/en-us/library/ms186299.aspx

Recently I seem to be running into a little bit of a spamming problem with backlink's being submitted to the blog. It looks like the way .net blog engine does post is really easy for the spammer to be able to post lots and lots of comments with bots (I guess the spammers are getting good at this now). This sounds quite bad but everything is being caught by the spam filters so its really not so bad. So this is a bit of a guide to attempt to protect a website from such action coming from abusive computers around the internet. More...