Wednesday, September 30, 2009

Exact Globe compatible with Windows 7



On 22 october 2009 Windows 7 will become commercial available. Exact Software has become one of the first European software vendors to receive this certification. By the time the Microsoft Worldwide Partner Conference took place in July, Exact Globe was certified and ready to run on Windows 7. More information about Exact Globe running on Windows 7 can be found on the Exact Product Blog and on the Ready.Set.7 site of Microsoft.
On the Ready.Set.7 website you’ll find the Exact logo and behind it, a short piece describing the advantages.
A short film featuring Aad ‘t Hart can also be viewed, in which he gives his views on what Windows 7 can do for our customers. “Windows 7 increases the productivity of the Exact Globe end user,” Aad says.

With Exact Globe you are now able to run on the most recent available technology with Windows 7 and SQL Server 2008. Don't forget that you can buy SQL 2008 licenses for the use with Exact products with a big discount via Exact.

Tuesday, September 15, 2009

Use batches to delete large number of records in a table

Sometimes it can happen that you need to delete a lot of records in a table. This can happen for instance in log tables. With a simple DELETE statement like:

DELETE FROM MyTable WHERE COL1 = 1

it can take a while when you have for instance 1 million records to delete. It can results in a table lock which has a negative impact on the performance of your application.

As of SQL2005/2008 you can delete records in a table in batches with the DELETE TOP (BatchSize) statement.  This method has 3 advantages
  1. It will not create one big transaction.
  2. It avoids a table lock.
  3. If the delete statement is canceled, only the last batch is rolled back. Records in previous batches are deleted.
You can use next script to test this DELETE TOP () statement.

CREATE TABLE DEMO (COL1 INT,COL2 INT)


DECLARE @COUNTER INT
SET @COUNTER = 1

INSERT INTO DEMO (COL1,COL2) Values (2,2)

WHILE @COUNTER < 50000
BEGIN

INSERT INTO DEMO (COL1,COL2) Values (1,@COUNTER)
SET @COUNTER = @COUNTER + 1

END

/*
-- Show content of the table
SELECT COL1, COUNT(*) FROM DEMO GROUP BY COL1
*/

-- Deleting records in batches of 1000 records

DECLARE @BatchSize INT
SET @BatchSize = 1000

WHILE @BatchSize <> 0

BEGIN

DELETE TOP (@BatchSize)
FROM DEMO
WHERE COL1 = 1

SET @BatchSize = @@rowcount

END

-- SELECT * FROM Demo -- Now we have only 1 record left