Sunday, May 4, 2014

SQL Server - Truncate tables in a SQL Server database

Problem
As a DBA I have found many occasions in testing of various SQL Server deployments and scripts where I need to load a database with data from a lower environment or where I need to alter a table that might already have data in it in such a way that I need to first eliminate the data before I can proceed. Depending on the foreign key constraints in place, clearing old data can be a tedious process.
Solution
When I don't care about existing data in a SQL Server database, but I don't want to resort to dropping and re-creating the database and all the additional tasks that come with it, I have found truncating all the data in all the tables to be an effective solution. I prefer truncating over deleting the data because of the various advantages truncating has by nature when you want to reset to a clean state.
Unfortunately, in most complex database designs there are numerous foreign key relationships that make it impossible to just use a simple "for each" loop and issuing a TRUNCATE command.
To illustrate this problem and my solution, we first need to create a database with several tables with foreign key constraints.

CREATE DATABASE ForeignKeyTest
GO
USE ForeignKeyTest
GO
CREATE TABLE T1
       (
        T1Id INT IDENTITY
                 PRIMARY KEY
                 NOT NULL
       ,Customer VARCHAR(50) NOT NULL
       )
GO
CREATE TABLE T2
       (
        T2Id INT IDENTITY
                 PRIMARY KEY
                 NOT NULL
       ,T1Id INT NOT NULL
       ,OrderNum INT NOT NULL
       )
GO
CREATE TABLE T3
       (
        T3Id INT IDENTITY
                 PRIMARY KEY
                 NOT NULL
       ,T2Id INT NOT NULL
       ,ItemId INT NOT NULL
       ,Qty INT NOT NULL
       )
GO
CREATE TABLE Item
       (
        ItemId INT IDENTITY
                   PRIMARY KEY
                   NOT NULL
       ,Item VARCHAR(50)
       )
GO
ALTER TABLE dbo.T2
ADD CONSTRAINT FK_T2_T1 FOREIGN KEY( T1Id) REFERENCES dbo.T1 ( T1Id)
GO
ALTER TABLE dbo.T3
ADD CONSTRAINT FK_T3_T2 FOREIGN KEY( T2Id) REFERENCES dbo.T2 (T2Id)
GO
ALTER TABLE dbo.T3  
ADD CONSTRAINT FK_T3_Item FOREIGN KEY(ItemId) REFERENCES dbo.Item(ItemId)
GO
INSERT dbo.T1
        ( Customer )
SELECT 'FirstCust'
UNION
SELECT 'SecondCust'
UNION
SELECT 'ThirdCust' ;
INSERT dbo.T2
        ( T1Id, OrderNum )
SELECT 1, 1
UNION
SELECT 1, 2
UNION 
SELECT 2, 3
UNION
SELECT 3, 4
UNION
SELECT 3, 5 ;
INSERT dbo.Item
        ( Item )
SELECT 'Gunk'
UNION
SELECT 'Slop'
UNION
SELECT 'Glop'
UNION
SELECT 'Crud' ;
INSERT dbo.T3
        ( T2Id, ItemId, Qty )
SELECT 1,3,5
UNION
SELECT 1,2,2
UNION
SELECT 2,1,4
UNION
SELECT 3,3,10;
This creates tables that look like this:
Create a database with several tables with foreign key constraints
The data in the tables look like:
T1
Truncate all tables in a database
T2
T3
Just use a simple "for each" loop and issuing a TRUNCATE command.
ItemTable
Unfortunately, in most complex database designs there are numerous foreign key relationships
If you attempt to truncate any of the tables you get this error:
BEGIN TRAN
TRUNCATE TABLE dbo.T2
ROLLBACK
If you attempt to truncate any of the tables you get this error:
If you attempt to delete data from the tables you get this error:
BEGIN TRAN
DELETE dbo.T1
ROLLBACK
If you attempt to delete data from the tables you get this error:
If you only want to use the DELETE statement against your tables, you can disable the constraints, delete your data and re-enable your constraints.
BEGIN TRAN
ALTER TABLE dbo.T1 NOCHECK CONSTRAINT ALL
ALTER TABLE dbo.T2 
NOCHECK CONSTRAINT ALL
DELETE dbo.T1
SELECT * FROM dbo.T1 AS T

ALTER TABLE dbo.T1 CHECK CONSTRAINT ALL
ALTER TABLE dbo.T2 CHECK CONSTRAINT ALL
ROLLBACK
If you only want to use the DELETE statement against your tables, you can disable the constraints, delete your data and re-enable your constraints.
This works, but if you try do the same thing using TRUNCATE you get the following:
BEGIN TRAN
ALTER TABLE dbo.T1 NOCHECK CONSTRAINT ALL
ALTER TABLE dbo.T2 NOCHECK CONSTRAINT ALL
TRUNCATE TABLE  dbo.T1
SELECT * FROM dbo.T1 AS T
ROLLBACK
This works, but if you try do the same thing using TRUNCATE you get the following:

A solution that can TRUNCATE all tables

In order to truncate all tables in your database you must first remove all the foreign key constraints, truncate the tables, and then restore all the constraints.
The below script accomplishes this in an automated fashion, by executing the following steps:
  1. Create a table variable to store the constraint drop and creation scripts for the database
  2. Load the data for all tables in the database
  3. Execute a cursor to drop all constraints
  4. Truncate all tables
  5. Recreate all the constraints
 /* TRUNCATE ALL TABLES IN A DATABASE */
DECLARE @dropAndCreateConstraintsTable TABLE
        (
         DropStmt VARCHAR(MAX)
        ,CreateStmt VARCHAR(MAX)
        )
/* Gather information to drop and then recreate the current foreign key constraints  */
INSERT  @dropAndCreateConstraintsTable
        SELECT  DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema
                + '].[' + ForeignKeys.ForeignTableName + '] DROP CONSTRAINT ['
                + ForeignKeys.ForeignKeyName + ']; '
               ,CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema
                + '].[' + ForeignKeys.ForeignTableName
                + '] WITH CHECK ADD CONSTRAINT [' + ForeignKeys.ForeignKeyName
                + '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn
                + ']) REFERENCES [' + SCHEMA_NAME(sys.objects.schema_id)
                + '].[' + sys.objects.[name] + ']([' + sys.columns.[name]
                + ']); '
        FROM    sys.objects
        INNER JOIN sys.columns
                ON ( sys.columns.[object_id] = sys.objects.[object_id] )
        INNER JOIN ( SELECT sys.foreign_keys.[name] AS ForeignKeyName
                           ,SCHEMA_NAME(sys.objects.schema_id) AS ForeignTableSchema
                           ,sys.objects.[name] AS ForeignTableName
                           ,sys.columns.[name] AS ForeignTableColumn
                           ,sys.foreign_keys.referenced_object_id AS referenced_object_id
                           ,sys.foreign_key_columns.referenced_column_id AS referenced_column_id
                     FROM   sys.foreign_keys
                     INNER JOIN sys.foreign_key_columns
                            ON ( sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.[object_id] )
                     INNER JOIN sys.objects
                            ON ( sys.objects.[object_id] = sys.foreign_keys.parent_object_id )
                     INNER JOIN sys.columns
                            ON ( sys.columns.[object_id] = sys.objects.[object_id] )
                               AND ( sys.columns.column_id = sys.foreign_key_columns.parent_column_id )
                   ) ForeignKeys
                ON ( ForeignKeys.referenced_object_id = sys.objects.[object_id] )
                   AND ( ForeignKeys.referenced_column_id = sys.columns.column_id )
        WHERE   ( sys.objects.[type] = 'U' )
                AND ( sys.objects.[name] NOT IN ( 'sysdiagrams' ) )
/* SELECT * FROM @dropAndCreateConstraintsTable AS DACCT  --Test statement*/
DECLARE @DropStatement NVARCHAR(MAX)
DECLARE @RecreateStatement NVARCHAR(MAX)
/* Drop Constraints */
DECLARE Cur1 CURSOR READ_ONLY
FOR
        SELECT  DropStmt
        FROM    @dropAndCreateConstraintsTable
OPEN Cur1
FETCH NEXT FROM Cur1 INTO @DropStatement
WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @DropStatement
            EXECUTE sp_executesql @DropStatement
            FETCH NEXT FROM Cur1 INTO @DropStatement
      END
CLOSE Cur1
DEALLOCATE Cur1
/* Truncate all tables in the database in the dbo schema */
DECLARE @DeleteTableStatement NVARCHAR(MAX)
DECLARE Cur2 CURSOR READ_ONLY
FOR
        SELECT  'TRUNCATE TABLE [dbo].[' + TABLE_NAME + ']'
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE   TABLE_SCHEMA = 'dbo'
                AND TABLE_TYPE = 'BASE TABLE'
  /* Change your schema appropriately if you don't want to use dbo */
OPEN Cur2
FETCH NEXT FROM Cur2 INTO @DeleteTableStatement
WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @DeleteTableStatement
            EXECUTE sp_executesql @DeleteTableStatement
            FETCH NEXT FROM Cur2 INTO @DeleteTableStatement
      END
CLOSE Cur2
DEALLOCATE Cur2
/* Recreate foreign key constraints  */
DECLARE Cur3 CURSOR READ_ONLY
FOR
        SELECT  CreateStmt
        FROM    @dropAndCreateConstraintsTable
OPEN Cur3
FETCH NEXT FROM Cur3 INTO @RecreateStatement
WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @RecreateStatement
            EXECUTE sp_executesql @RecreateStatement
            FETCH NEXT FROM Cur3 INTO @RecreateStatement
      END
CLOSE Cur3
DEALLOCATE Cur3
GO   
When the above script is run against any database, all the tables are emptied and reset.

Change the COLLATION of a SQL Server Column

Problem
You've installed SQL Server with all the defaults, created your database and schema then loaded a bunch of data into your database. Now you realize that some or all of the columns in your database needed to have the COLLATION set to be case sensitive. This tip will take you through the steps required to change the column COLLATION of your tables from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP1_CS_AS.
Solution

Table Setup

Let's first setup a couple of tables in order to walkthrough these steps required to update the column collation. The following script will create two tables as well as some constraints, indexes and statistics in order to illustrate each of the steps that may be required depending on the design of your schema. For this example we won't add any data to our tables as it wouldn't have any effect on the update process since we are going from case insensitive to case sensitive. It's important to note that if you were performing the reverse update that is from case sensitive to case insensitive, you would also have an extra step of resolving any data issues as the update could result in duplicate data in columns with unique constraints. That said, here is the complete setup script listing.
CREATE TABLE dbo.MainTable
 (PKColumn int NOT NULL IDENTITY (1, 1),
  CharColumn char(10) NULL,
  NCharColumn nchar(10) NULL,
  VarcharColumn varchar(50) NULL,
  NVarcharColumn nvarchar(50) NULL,
  VarcharMaxColumn varchar(MAX) NULL,
  NVarcharMaxColumn nvarchar(MAX) NULL,
  ComputedColumn  AS CharColumn + VarcharColumn)
GO

ALTER TABLE dbo.MainTable ADD CONSTRAINT CK_Table_NVarCharMaxColumn
   CHECK (NVarCharMaxColumn IN ('Apple','Pear','Orange','Banana'))
GO

ALTER TABLE dbo.MainTable ADD CONSTRAINT PK_MainTable PRIMARY KEY CLUSTERED (PKColumn)
GO

CREATE NONCLUSTERED INDEX IX_Table_NVarcharColumn ON dbo.MainTable (NVarcharColumn) 
GO

CREATE STATISTICS Stats_MainTable_VarcharMaxColumn
    ON dbo.MainTable (VarcharMaxColumn) WITH FULLSCAN
GO

CREATE TABLE dbo.FKTable
 (CharColumn char(10) NOT NULL,
  DataColumn int NULL)
GO

ALTER TABLE dbo.FKTable ADD CONSTRAINT PK_FKTable PRIMARY KEY CLUSTERED (CharColumn) 
GO

ALTER TABLE dbo.MainTable ADD CONSTRAINT
 FK_MainTable_FKTable FOREIGN KEY
 (CharColumn) REFERENCES dbo.FKTable
 (CharColumn)
  ON UPDATE  NO ACTION 
  ON DELETE  NO ACTION 
GO
Identify columns
Depending on your situation you may need to only change the collation of a few columns or possible every column in your database. You can use the following query which will list any columns in your database that have the default collation.
SELECT t.name "Table Name",
       c.name "Column Name",
       c.collation_name "Collation"
  FROM sys.tables t INNER JOIN
       sys.columns c ON c.object_id=t.object_id INNER JOIN
       sys.types s ON s.user_type_id=c.user_type_id
 WHERE c.collation_name LIKE 'SQL_Latin1_General_CP1_CI_AS'
   AND t.type like 'U'
   AND t.name not like 'spt%'
   AND t.name not like 'MSrep%'
For this tip let's assume that we are changing the collation of every column in our database. If you read the following link, Set or Change the Column Collation, from TechNet you'll notice that there are some restrictions on when you can change the collation of a column. Basically, if a column is referenced by any of the following objects you will not be able to change the collation without first removing these references.
  • A computed column
  • An index
  • Distribution statistics, either generated automatically or by the CREATE STATISTICS statement
  • A CHECK constraint
  • A FOREIGN KEY constraint
Identify referencing objects
Let's now identify which objects need to be removed before we can make the collation update to our columns. The following queries, one for each category listed above, will identify objects that have a dependency on a column that we are going to be updating. Since we are updating all the columns in the database the only filter I have is "WHERE c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'". If your requirements are different you can expand on this as required. I've also include a sample output after each query for reference. One other item to note is with the foreign key section. We need to remove the referenced primary key index in order to make the collation update to that column as well so that the section includes two queries. One to identify the foreign key and the other for the primary key.
-- computed columns
SELECT OBJECT_NAME(c.object_id) "Table Name",
       COL_NAME(sd.referenced_major_id, sd.referenced_minor_id) "Column Name",
       c.collation_name "Collation",
       definition "Definition"
  FROM sys.computed_columns cc INNER JOIN
       sys.sql_dependencies sd ON cc.object_id=sd.object_id AND 
                                  cc.column_id=sd.column_id AND 
                                  sd.object_id=sd.referenced_major_id INNER JOIN
       sys.columns c ON c.object_id=sd.referenced_major_id AND 
                        c.column_id = sd.referenced_minor_id
 WHERE c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'
   AND sd.class=1

Table NameColumn NameCollationDefinition
MainTableCharColumnSQL_Latin1_General_CP1_CI_AS([CharColumn]+[VarcharColumn])
MainTableVarcharColumnSQL_Latin1_General_CP1_CI_AS([CharColumn]+[VarcharColumn])

--foreign keys
SELECT f.name "Foreign Key Name",
       OBJECT_NAME(f.parent_object_id) "Table Name",
       COL_NAME(fc.parent_object_id,fc.parent_column_id) "Column Name",
       c1.collation_name "Collation",
       OBJECT_NAME (f.referenced_object_id) "Reference Table Name",
       COL_NAME(fc.referenced_object_id,fc.referenced_column_id) "Reference Column Name",
       c2.collation_name "Collation"
  FROM sys.foreign_keys AS f INNER JOIN
       sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id INNER JOIN
       sys.columns c1 ON c1.object_id=fc.parent_object_id AND
                         c1.column_id=fc.parent_column_id INNER JOIN
       sys.columns c2 ON c2.object_id=fc.parent_object_id AND
                         c2.column_id=fc.parent_column_id
 WHERE c1.collation_name like 'SQL_Latin1_General_CP1_CI_AS'
    OR c2.collation_name like 'SQL_Latin1_General_CP1_CI_AS'

-- primary keys
SELECT i.name AS "Primary Key Name",
       OBJECT_NAME(ic.object_id) "Table Name",
       COL_NAME(ic.object_id,ic.column_id) "Column Name",
       c.collation_name "Collation"
  FROM sys.indexes AS i INNER JOIN 
       sys.index_columns AS ic ON i.object_id = ic.object_id AND 
                                  i.index_id = ic.index_id INNER JOIN
       sys.columns c ON ic.object_id=c.object_id AND
                        c.column_id=ic.column_id
 WHERE i.is_primary_key=1
   AND c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'

Foreign Key NameTable NameColumn NameCollation
FK_MainTable_FKTableMainTableCharColumnSQL_Latin1_General_CP1_CI_AS
Reference Table NameReference Column NameCollation
FKTableCharColumnSQL_Latin1_General_CP1_CI_AS

Primary Key NameTable NameColumn NameCollation
PK_FKTableFKTableCharColumnSQL_Latin1_General_CP1_CI_AS

--indexes
SELECT i.name AS "Index Name",
       OBJECT_NAME(ic.object_id) "Table Name",
       COL_NAME(ic.object_id,ic.column_id) "Column Name",
       c.collation_name "Collation"
  FROM sys.indexes AS i INNER JOIN 
       sys.index_columns AS ic ON i.object_id = ic.object_id AND 
                                  i.index_id = ic.index_id INNER JOIN
       sys.columns c ON ic.object_id=c.object_id AND
                        c.column_id=ic.column_id
 WHERE c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'
   AND i.is_primary_key <> 1
   AND OBJECT_NAME(ic.object_id) NOT LIKE 'sys%'

Index NameTable NameColumn NameCollation
IX_Table_NVarcharColumnMainTableNVarcharColumnSQL_Latin1_General_CP1_CI_AS

-- statistics
SELECT s.name "Statistics Name",
       OBJECT_Name(c.object_id) "Table Name",
       COL_NAME(c.object_id,c.column_id) "Column Name",
    c.collation_name "Collation"
  FROM sys.stats s INNER JOIN
       sys.stats_columns sc on s.stats_id=sc.stats_id AND 
                               s.object_id=sc.object_id INNER JOIN
       sys.columns c ON c.object_id=sc.object_id AND 
                        c.column_id=sc.column_id 
 WHERE c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'
   AND s.user_created=1
   AND OBJECT_NAME(c.object_id) NOT LIKE 'sys%'
   AND OBJECT_NAME(c.object_id) NOT LIKE 'MSrep%'

Statistics NameTable NameColumn NameCollation
Stats_MainTable_VarcharMaxColumnMainTableVarcharMaxColumnSQL_Latin1_General_CP1_CI_AS

-- check constraints
SELECT OBJECT_NAME(cc.object_id) "Constraint Name",
       OBJECT_Name(c.object_id) "Table Name",
       COL_NAME(sd.referenced_major_id, sd.referenced_minor_id) "Column Name",
       c.collation_name "Collation",
       definition "Definition"
  FROM sys.check_constraints cc INNER JOIN
       sys.sql_dependencies sd ON cc.object_id=sd.object_id INNER JOIN
       sys.columns c ON c.object_id=sd.referenced_major_id AND 
                        c.column_id = sd.referenced_minor_id
 WHERE c.collation_name like 'SQL_Latin1_General_CP1_CI_AS'
   AND cc.type = 'C'
   AND sd.class=1

Constraint NameTable NameColumn NameCollation
CK_Table_NVarCharMaxColumnMainTableNVarcharMaxColumnSQL_Latin1_General_CP1_CI_AS
Definition
([NVarCharMaxColumn]='Banana' OR [NVarCharMaxColumn]='Orange' OR [NVarCharMaxColumn]='Pear' OR [NVarCharMaxColumn]='Apple')

Drop referencing objects

Now that we've identified everything that we need to remove we can use any tool, including SSMS, to create drop scripts for these objects. Remember to at the same time create a script that will recreate these objects so we can run that after our column definition update is done. Here is the script to drop the objects identified above.
ALTER TABLE dbo.MainTable DROP COLUMN ComputedColumn
ALTER TABLE dbo.MainTable DROP CONSTRAINT FK_MainTable_FKTable
ALTER TABLE dbo.FKTable DROP CONSTRAINT PK_FKTable
DROP INDEX dbo.MainTable.IX_Table_NVarcharColumn
DROP STATISTICS dbo.MainTable.Stats_MainTable_VarcharMaxColumn
ALTER TABLE dbo.MainTable DROP CONSTRAINT CK_Table_NVarCharMaxColumn

Update the collation

After all that work we can now issue simple ALTER TABLE ... ALTER COLUMN ... statements to update the collation for each column in our database. Here is the script to perform this operation.
ALTER TABLE dbo.MainTable
 ALTER COLUMN NCharColumn nchar(10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
ALTER TABLE dbo.MainTable
 ALTER COLUMN CharColumn char(10) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
ALTER TABLE dbo.FKTable
 ALTER COLUMN CharColumn char(10) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL
ALTER TABLE dbo.MainTable
 ALTER COLUMN VarcharColumn varchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
ALTER TABLE dbo.MainTable
 ALTER COLUMN NVarcharColumn nvarchar(50) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
ALTER TABLE dbo.MainTable
 ALTER COLUMN VarcharMaxColumn varchar(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS NULL
ALTER TABLE dbo.MainTable 
 ALTER COLUMN NVarcharMaxColumn nvarchar(MAX) COLLATE SQL_Latin1_General_CP1_CS_AS NULL

Recreate referencing objects

Once the schema update completes we can run the script we generated above to recreate all of the referencing objects that we dropped earlier. Here is the script for our example.
ALTER TABLE dbo.MainTable ADD ComputedColumn AS CharColumn + VarcharColumn
GO
ALTER TABLE dbo.MainTable ADD CONSTRAINT CK_Table_NVarCharMaxColumn
   CHECK (NVarCharMaxColumn IN ('Apple','Pear','Orange','Banana'))
GO
CREATE NONCLUSTERED INDEX IX_Table_NVarcharColumn ON dbo.MainTable (NVarcharColumn) 
GO
CREATE STATISTICS Stats_MainTable_VarcharMaxColumn
    ON dbo.MainTable (VarcharMaxColumn) WITH FULLSCAN
GO
ALTER TABLE dbo.FKTable ADD CONSTRAINT PK_FKTable PRIMARY KEY CLUSTERED (CharColumn) 
GO
ALTER TABLE dbo.MainTable ADD CONSTRAINT
 FK_MainTable_FKTable FOREIGN KEY
 (CharColumn) REFERENCES dbo.FKTable
 (CharColumn)
  ON UPDATE  NO ACTION 
  ON DELETE  NO ACTION 
GO

Tuesday, April 8, 2014

Delphi - Records

What are records?
Records are a useful and distinguishing feature of delphi. They provide a very neat way of having named data structures - groups of data fields. Unlike arrays, a record may contain different types of data.

Records are fixed in size - the definition of a record must contain fixed length fields. We are allowed to have strings, but either their length must be specified (for example a : String[20]), or a pointer to the string is stored in the record. In this case, the record cannot be used to write the string to a file. The TPoint type is an example of a record. Before we go any further, let us look at a simple example.
 type
   TCustomer = record
     name : string[30];
     age  : byte;
   end;
  

 var
   customer : TCustomer;
 

 begin
   // Set up our customer record
   customer.name := 'Fred Bloggs';
   customer.age  := 23;
 end;
When we define a record, each field is simply accessed by name, separated by a dot from the record variable name. This makes records almost self documenting, and certainly easy to understand.
Above, we have created one customer, and set up the customer record fields.
Using the with keyword
type
   TCustomer = record
     name : string[30];
     age  : byte;
   end;
 
 
 var
   John, Nancy : TCustomer;
 
 
 begin
   // Set up our customer records
   with John do
   begin
     name := 'John Moffatt';               // Only refer to the record fields
     age  := 67;
   end;
 
 
   with Nancy do
   begin
     name := 'Nancy Moffatt';              // Only refer to the record fields
     age  := 77;
   end;
 end;
A more complex example
In practice, records are often more complex. Additionally, we may also have a lot of them, and might store them in an array. The following example is a complete program that you may copy and paste into your Delphi product, making sure to follow the instructions at the start of the code.

Please note that this is quite a complex piece of code - it uses a procedure that takes a variable number of parameters, specially passed in square brackets (see Procedure for more on procedures).
// Full Unit code.
 // -----------------------------------------------------------
 // You must store this code in a unit called Unit1 with a form
 // called Form1 that has an OnCreate event called FormCreate.
 
 unit Unit1;
  

 interface
  

 uses
   Forms, Dialogs;
  

 type
   TForm1 = class(TForm)
     procedure FormCreate(Sender: TObject);
     procedure ShowCustomer(const fields: array of string);
   end;
 
 
 var
   Form1: TForm1;
 
 
 implementation
 {$R *.dfm} // Include form definitions
 
 
 procedure TForm1.FormCreate(Sender: TObject);
 type
   // Declare a customer record
   TCustomer = Record
     firstName : string[20];
     lastName  : string[20];
     address1  : string[100];
     address2  : string[100];
     address3  : string[100];
     city      : string[20];
     postCode  : string[8];
   end;
  

 var
   customers : array[1..3] of TCustomer;
   i : Integer;
 
 
 begin
   // Set up the first customer record
   with customers[1] do
   begin
     firstName := 'John';
     lastName  := 'Smith';
     address1  := '7 Park Drive';
     address2  := 'Branston';
     address3  := 'Grimworth';
     city      := 'Banmore';
     postCode  := 'BNM 1AB';
   end;
  

   // Set up the second and third by copying from the first
   customers[2] := customers[1];
   customers[3] := customers[1];
 
 
   // And then changing the first name to suit in each case
   customers[2].firstName := 'Sarah';
   customers[3].firstName := 'Henry';
  

   // Now show the details of these customers
   for i := 1 to 3 do
     with customers[i] do ShowCustomer([firstName,
                                        lastName,
                                        address1,
                                        address2,
                                        address3,
                                        city,
                                        postCode]);
 end;
  

 // A procedure that displays a variable number of strings
 procedure TForm1.ShowCustomer(const fields: array of string);
 var
   i : Integer;
 
 
 begin
   // Display all fields passed - note : arrays start at 0
   for i := 0 to Length(fields)-1 do
     ShowMessage(fields[i]);
 
 
   ShowMessage('');
 end;
  

 end.
Packing record data
By default, Delphi will pad out the record with fillers, where necessary, to make sure that fields are aligned on 2, 4 or 8 byte boundaries to improve performance. You can pack the data with the packed keyword to reduce the record size if this is more important than performance. See Packed for more on this topic.

Records with variant parts
Things get very interesting now. There are times when a fixed format record is not useful. First, we may wish to store data in the record in different ways. Second, we may want to store different types of data in a part of a record.
The Delphi TRect type illustrates the first concept. It is defined like this:
type 
   TRect = packed record
     case Integer of
       0: (Left, Top, Right, Bottom: Integer);
       1: (TopLeft, BottomRight: TPoint);
   end; 
Here we have a record that holds the 4 coordinates of a rectangle. The Case clause tells Delphi to map the two following sub-sections onto the same area (the end) of the record. These variant sections must always be at the end of a record. Note also that the case statement has no end statement. This is omitted because the record finishes at the same point anyway.
The record allows us to store data in two ways:
var
   rect1, rect2 : TRect;
 begin
   // Setting up using integer coordinates
   rect1.Left   := 11;
   rect1.Top    := 22;
   rect1.Right  := 33;
   rect1.Bottom := 44;
 

   // Seting up rect2 to have the same coordinates, but using points instead
   rect2.TopLeft     := Point(11,22);
   rect2.BottomRight := Point(33,44);
 end;
The TRect record showed two methods of reading from and writing to a record. The second concept is to have two or more record sub-sections that have different formats and lengths.
This time we will define a fruit record that has a different attribute section depending on whether the fruit is round or long:
type
   // Declare a fruit record using case to choose the
   // diameter of a round fruit, or length and height ohterwise.
   TFruit = Record
     name : string[20];
     Case isRound : Boolean of // Choose how to map the next section
       True  :
         (diameter : Single);  // Maps to same storage as length
       False :
         (length   : Single;   // Maps to same storage as diameter
          width    : Single);
   end;
 
 
 var
   apple, banana : TFruit;
 
 
 begin
   // Set up the apple as round, with appropriate dimensions
   apple.name     := 'Apple';
   apple.isRound  := True;
   apple.diameter := 3.2;
 
 
   // Set up the banana as long, with appropriate dimensions
   banana.name    := 'Banana';
   banana.isRound := False;
   banana.length  := 7.65;
   banana.width   := 1.3;
  

   // Let us display the fruit dimensions:
   if apple.isRound
   then ShowMessageFmt('Apple diameter = %f',[apple.diameter])
   else ShowMessageFmt('Apple width = %f , length = %f',
                       [apple.width, apple.length]);
   if banana.isRound
   then ShowMessageFmt('Banana diameter = %f',[banana.diameter])
   else ShowMessageFmt('Banana width = %f , length = %f',
                       [banana.width, banana.length]);
 end;
Note that the Case statement now defines a variable, isRound to hold the type of the variant section. This is very useful, and recommended in variable length subsections, as seen in the code above.

Developing Facebook Fan Page Requires Effort and Creativity

At this point of time, each and every individual is aware about Facebook. Promoting your products as well as services have become very necessary in today's business scenario and for that Facebook is one of the finest platforms. Either you must already be having a Facebook Fan Page for your business, or you must be planning to create a new one. Nowadays, it has become really very important to make use of these social networking sites to their fullest prospective. Moreover, setting up a Fan Page is not just the only activity; it must be customized attractively in order to achieve better results.

Facebook is one of the most substantial advertising platforms where you can create lot of businesses. You can also expand your business if you concentrate on Facebook Fan Page Development. The default Facebook page comes with very general features like the other pages and consists of wall, photos, basic info, videos and many more things. These are essential things that ought to be filled by the users in order to make an attractive page. These features will be visible to everyone who is registered on Facebook. If you want, you can also customize it as per your wish.

What Valid Specifications you need to install in Facebook page?
There is a specific segment on Facebook page which is referred as ‘tab'. Besides, every tab is available with some kind of particular feature. It may also be known as ‘widget or ‘module' as per the varying semantics. The generic features that are mentioned above, actually act as tab and serves the feature that it is built for.
Capable and talented Facebook application developers are free to develop wonderful customized tabs which are unique as well as applicable to the Facebook Fan page.

 The tabs may be designed and modified in such a manner that it will assist you to create an interactive user experience. More to the point, adding of these custom tabs will leave the Facebook page with much better and enhanced functionalities.

Expand your Business Opportunities with Facebook Fan Page development
You can very easily grow your business opportunities if the fan page is designed in a proper manner; thereby specifically depicting the business products and services. You ought to be well aware about the layouts of Facebook as well as the backgrounds of the profile. Even though, it is really very easy and simple to apply for any sort of layout, but it ought to be extremely creative to attract the attention of the visitors. Internet is the best solution to find the most apt layout for your business as per your needs and requirements.

It is significant to design Facebook Fan Pages by keeping in mind your business interests and hence maintaining the standard as well as quality is necessary. Adding of face apps also makes the page more and more attractive, and because of that the visitors keep visiting the page with supreme attention. The pictures or images of the products must be very clearly demonstrated and it must precisely be designed in order to attract more and more customers. The ideal size of the images is 200 x 400 pixels large and use of excessive images must be avoided. On the other hand, the page must be informative, speaking about the products and services the business is offering along with the contact information

 From : articlesbase.com

SQL Server - OLE DB Deprecated :(

As you may already know, the SQL Native Client OLE DB provider is being deprecated. This doesn't affect other OLE DB providers or the OLE DB API. Also, the SSIS OLE DB components are not being deprecated.

In SQL Server 2012 SSIS, you can continue to use OLE DB connections and will need to for components such as the Lookup transformation and the Slowly Changing Dimension transformation that require OLE DB. In the post-Denali release, you’ll be able to upgrade and continue to operate these packages without needing to do additional work to explicitly remove OLE DB from the packages.

SQL Server - Result paganation in sql server


A lot of SQL Server database users at some time faced a big obstacle, especially web sites programmers when need to read data from the database in case of a partial data was a fairly large size. After a long and arduous journey made in the search for the best method that can solve the problem very haunting, I've experimenting with dozens of ways that were not satisfactory at first, especially if the data is very large size.

In fact,i did't invent magic solution, but I found the best located after many modifications and tests, all web solutions was talked about using  these methods (ROW_NUMBER() , TOP Rows ,CTE and Temp tables) in sql server 2005 and older versions wich i prefer using TOP method to done this job , because it's flexible and no limitation or conflicts for using TOP with your query criteria's and orders data results .


Using TOP IN SQL Server 2005
i prefer using sql stored procedure with parameters like @page_num , @page_size and @query_type that i use @query type to switch between counting results and query reaults it self .
Create PROCEDURE [dbo].[GetArchive]
(@username NVARCHAR(100),@page_num int,@page_size int,@proc_type nvarchar(20))AS
BEGIN


declare @page_count int ,@StartRow int , @EndRow int ,@sql nvarchar(max)
 

if (@proc_type='get_count')
begin
 set @sql = 'SELECT Count(*) as AllCount FROM Archive where id > 0'


end
else
begin
 set @sql = 'SELECT TOP ' + cast(@page_size as nvarchar(100)) 
 set @sql = @sql + ' Name,Mobile,Email,[Datetime] '
 set @sql = @sql +' FROM Archive '
 set @sql = @sql + 'WHERE ID NOT IN(SELECT TOP ('+cast(@page_size as nvarchar(100))+' * ('+cast(@page_num as nvarchar(100))+' - 1)) ID FROM Archive where id > 0 AND Username ='''+@username+''' order by id desc) '
end
set @sql = @sql + ' and username = '''+@username +'''';


if (@proc_type<>'get_count')
   set @sql = @sql + ' order by id desc '




exec (@sql)

 

END
Executing procedure will generate sql query like this
SELECT TOP 10 Name,Mobile,Email,[Datetime] FROM Archive WHERE ID NOT IN(SELECT TOP (10 * (2 - 1)) ID FROM Archive where id > 0 AND Username ='User' order by id desc)  and username = 'User' order by id desc 
Paganation IN SQL Server 2012
Sql server 2012 intreduce new keyword to do this job in easy and fast way like any other databsaes especially LIMIT in Mysql, using OFFSET ended this long conflict by add this keyword to your query
SELECT
     *
FROM  [dbo].[Archive]
ORDER BY id
OFFSET 10 ROWS
FETCH NEXT 20 ROWS ONLY

Sunday, April 6, 2014

PHP -MySQLCrank



MySQLCrank

Provides a number of functions to make it easier to work with MySQL databases, without using a lot of mysqli_xxxx functions, making it easier to switch to a different database. Supports SELECT, INSERT, UPDATE, and DELETE queries only, first because of security reasons, second - other queries are just not really needed for web development. 

Using MySqli_ extension (as described in php.net):

The mysqli extension, or as it is sometimes known, the MySQL improved extension, was developed to take advantage of new features found in MySQL systems versions 4.1.3 and newer. The mysqli extension is included with PHP versions 5 and later. The mysqli extension has a number of benefits, the key enhancements over the mysql extension being:
  • Object-oriented interface
  • Support for Prepared Statements
  • Support for Multiple Statements
  • Support for Transactions

Supporting Parameters :

Through the support of the parameters you can use the various operations on the database in the same format, you can only change the type of operation required.

Classes

  • TDatabase
  • Includes all database operations function like SELECT , INSERT , UPDATE and DELETE .
  • TSQL
  • Includes structure of Parameters.

Setup database configuration

from TDatabase Class find these line and configure it as your database config.

$this->db_host "__SERVER_" ;$this->db_username "__DATABASE_USERNAME__";$this->db_password "__DATABASE_PASSWORD__";$this->db_database "__DATABASE_NAME__";?>

Basic Usage

Creating new instance of TDatabase class .

require_once(
"includes/TDatabase.php");$db = new TDatabase() ; /* Create new instance */
unset(
$db);  /* unset when finish using it. */
?>

Basic Select

Using Basic Select operation without any conditions.

$db = new TDatabase() ;$result $db->select("movies") ;
unset(
$db);?>

Select with where clause

Using Basic Select operation without any conditions

$db = new TDatabase() ;$db->where("movie_year","2013","i") ;$db->where("movie_rate","8","i") ;$result $db->select("movies") ;
unset(
$db);?>

where clause



/* where($name,$value,$type,$operand="=",$nextCondition="and") */
$db = new TDatabase() ;$db->where("movie_year","2013","i") ;$db->where("movie_rate","8","i") ;$result $db->select("movies") ;
unset(
$db);?>

Select with where and like operand



/* where($name,$value,$type,$operand="=",$nextCondition="and") */
$db = new TDatabase() ;$db->where("movie_name","Trek","s","like") ; $result $db->select("movies") ;
unset(
$db);?>

Select count for paging results



$db = new TDatabase() ;$db->where("movie_name","Trek","s","like") ;$resultCount $db->selectCount("movies") ; /* get count for same criterias*/
$result $db->select("movies") ;
unset(
$db);?>

Select and Limits



$db = new TDatabase() ;$db->limit(0,10);$result $db->select("movies") ;
unset(
$db);?>

Select and Order



$db = new TDatabase() ;$db->order("order by id desc");$result $db->select("movies") ;
unset(
$db);?>

Select with specified Columns

In case parameters added in select mode ,will be considered as columns must show only.

$db = new TDatabase() ;$db->addParameter("movie_rate","","i"); /* Parameters in select clause used as columns  */
$db->addParameter("movie_year","","i");$db->order("order by id desc");$result $db->select("movies") ;
unset(
$db);?>

Insert Operation



$db = new TDatabase() ;$db->addParameter("movie_name","Frozen (2013)","s");$db->addParameter("movie_rate","8","i");$db->addParameter("movie_year","2013","i");$db->addParameter("movie_story","Movie Story Here","s");$rows_affected $db->insert("movies") ;
unset(
$db);?>

Insert if not found and delete if found

An example of how easy to execute multi operations at the same time.

$db = new TDatabase() ;$db->addParameter("movie_name","Movie name","s");$db->addParameter("movie_rate","9","i");$db->addParameter("movie_year","2014","i");$db->where("movie_name","Movie name","s") ;

if(
$db->selectCount("movies")<=0)$db->insert("movies");
else
$db->delete("movies");

unset(
$db);?>

Update Operation with where



$db = new TDatabase() ;$db->addParameter("movie_rate","8","i");$db->addParameter("movie_year","2013","i");$db->where("id","1","i") ;$rows_affected $db->update("movies") ;
unset(
$db);?>

Delete Operation with where



$db = new TDatabase() ;$db->where("id","1","i") ; $rows_affected $db->delete("movies") ;
unset(
$db);?>

Safe Mode

You can activate safe mode to avoid overall effect on the records in operations (delete, modify) ,in this mode you must add one criteria at least .

$db = new TDatabase() ;$db->setSafeMode(true);$db->delete("movies") ;
unset(
$db);?>

Debug Query

You can see the query executed in database by calling $db->getLastQuery();

$db = new TDatabase() ;$db->select("movies") ;
echo  
$db->getLastQuery();
unset(
$db);?>

Download now ...

Saturday, April 5, 2014

SQL Server - Table Partitioning technique

Technical concept :
partitioning in simple word is spreads your data in multi file groups , file groups it's physical files on your Hard drive .

Usage:
This technique is used heavily in case of very large tables which are hard to get rid of this data on a regular basis, so it would be difficult to work out on the tables of millions of data that are used only when needed.

Therefore stems need to provide a mechanism to keep all the data available, while maintaining the good
performance of the database.
Partitioning a SQL Server database table is four-steps process:

1-Create the partition function
2-Create File Groups
3-Create the partition scheme
4-Partition the table


The remainder of this article explores each of those steps in further detail.
Step 1: Creating a Partition Function

partition defines the function how you want SQL Server to split the data. At this point, we are not concerned with any particular table, we select only the general technique to split the data.

Define partitions to determine the limits of each partitions .For example, suppose we have a customer table contains information on all of our customers, identified by a unique customer number, ranging from 1 to 1,000,000. We may decide to split the table into four partitions equally spaced, using the following partition function (I call it customer_func):

CREATE PARTITION FUNCTION customer_func (int 
AS RANGE RIGHT 
FOR VALUES (250000, 500000, 750000)


These limits determine four partitions. The first contains all values ​​less than 250,000. The second contains values ​​between 250,000 and 499,999. The third contains values ​​between 500 000 and 749 999. All values ​​greater than or equal to 750,000 to go in the fourth section.

Note that I used the phrase "RANGE RIGHT" in this example. This indicates that the threshold value itself should go in the partition on the right side. Alternatively, if you have used the "LEFT RANGE", the first partition has included all values ​​less than [U] or equal to [/ U] 250,000; second section will be included values ​​between 250,001 and 500,000, and so on .

Step 2:Create File Groups
Create and defines 4 file groups .
ALTER DATABASE Customers
ADD FILEGROUP fg1;
GO

ALTER DATABASE Customers
ADD FILEGROUP fg2;
GO

ALTER DATABASE Customers
ADD FILEGROUP fg3;
GO

ALTER DATABASE Customers
ADD FILEGROUP fg4;
GO

ALTER DATABASE Customers 
ADD FILE 
(
    NAME = fg1_dat,
    FILENAME = 'C:\Databases\Customersdb\fg1_dat.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP fg1;

ALTER DATABASE Customers 
ADD FILE 
(
    NAME = fg2_dat,
    FILENAME = 'C:\Databases\Customersdb\fg2_dat.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP fg2;


ALTER DATABASE Customers 
ADD FILE 
(
    NAME = fg3_dat,
    FILENAME = 'C:\Databases\Customersdb\fg3_dat.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP fg3;


ALTER DATABASE Customers 
ADD FILE 
(
    NAME = fg4_dat,
    FILENAME = 'C:\Databases\Customersdb\fg4_dat.ndf',
    SIZE = 5MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP fg4;
Step 3: Creating a Partition Scheme

Once you have a partition function describing [u]how[/u] you want to split your data, you need to create a partition scheme defining [u]where[/u] you want to partition it. This is a straightforward process that links partitions to filegroups. For example, if I had four filegroups named "fg1" through "fg4", the following partition scheme would do the trick:

 CREATE PARTITION SCHEME customer_partscheme

 AS PARTITION customer_func

 TO (fg1, fg2, fg3, fg4)


Notes that now link the partition function of the partition scheme, but we are still not linked to the partition scheme to any specific database table. Since the re-use of force comes into play. We can use this partition scheme (or simply a function of the division, and if we wish) on any number of database tables.

Step 4: Partitioning a Table

After defining the partition scheme, and you are now ready to create a partitioned table. This is the simplest step of the process. You can simply add the condition "ON" for the statement to create a table to determine the partition scheme and table column that applies to it. You do not need to determine the function of the partition because the partition scheme sets already.

For example, if you want to create the customer table using the partition scheme we have, and you use Transact-SQL statement following:

 CREATE TABLE customers (FirstName nvarchar(40), LastName nvarchar(40), CustomerNumber int)

 ON customer_partscheme (CustomerNumber)


That’s everything you need to know about partitioning tables in Microsoft SQL Server! Remember to leverage the power of reusability by writing generic partition functions and partition schemes that might apply to multiple tables!

Friday, April 4, 2014

PHP - Steps to increase the efficiency of PHP code


If you're a developer, it is necessary for you to improve your script in the development process itself early. Following the best practices while coding your PHP script is a good starting point to write PHP code optimization as well.

This tutorial provides few tips to optimize PHP code from a developer point of view.

1. Use Native PHP Functions
As much as possible, try to use the functions of the original PHP instead of writing your own functions to achieve your goal. For example, you can use range (b, k) to get of alphabets ranging from b to k in sequence, if necessary only once in the script instead of the Declaration of an array with the values ​​in the function and return the call.
2. Use Single Quotes
Using single quotes ( ‘ ‘ ) is faster than using double quotes( ” ” ) if you are going to keep only the string inside it avoiding any variables. Double quotes checks for the presence of variable and adds little bit of overhead.
3. Use = = = in your conditions
Use “= = =” instead of “= =”, as the former strictly checks for a closed range which makes it faster.
4. Use Appropriate Str Functions

str_replace is faster than preg_replace, but strtr is faster than str_replace by a factor of 4.
5. Calculate Only Once
Account and set the value to a variable if it is getting used many times instead of calculating it over and over again where it is used.
For example, the following will degrade the performance.
for( $i=0; i< count($arr); $i++){
  echo count($arr);
}
The script below will perform much better.
$len = count($arr);
for( $i=0; i< $len; $i++){
  echo $len;
}
6. Pass Reference to Function
Pass the reference to function if it does not affect your logic. The function of manipulating the signal faster than those that have been passed to manipulate the value as here is getting more and create a single copy of the value. Especially as it adds overhead when the value that was passed by you is a large group.
For example, let us create a function in two different way to increment by 1, each element of an array having values 0 to 99.
 
  // passing by reference
  function  computeValue( &$param ){
   // Something goes here
   foreach( $param as $k => $value){
     $param[$k] = $value + 1;
   }
  }
  $x = array();
  for( $i =0; $i<99; $i++){
    $x[$i] = $i;
  }
  computeValue( $x);
  
  // array with 100 elements each incremented by 1
  print_r( $x );

The function above works faster than the function below although both will produce the same result ( increment each element of the array by 1. )
 
   // passing by value
    function  computeValue( $param ){
      // Something goes here
      foreach( $param as $k => $value){
       $param[$k] = $value + 1;
      }
      
      return $param;
    }
    $x = array();
    for( $i =0; $i<99; $i++){
      $x[$i] = $i;
    }
 // array with 100 elements each incremented by 1
    print_r(computeValue( $x));
    
7. Create Classes Only When its Required
Don’t create classes and method until and unless its really needed, used and reused as well.
8. Disable Debugging Messages
File operations are expensive. So, if you have written lot of custom functions to log errors and warning during your development process, make sure you remove them before you push the code to production.
9. Use Caching Techniques
Use cache to reduce the load of database operations as well as the script compilation. We can use memcache for the reducing database load and APC for opcode caching and intermediate code optimization.
10. Close the Connection
Get into the habit to unset the variables and close database connection in your PHP code. It saves memory.
11. Reduce Number of Hits to DB
Try to reduce the number of hits to the database. Make queries aggregate so that you call the database less number of times. For example:
 
  $con=mysqli_connect("localhost","username","somepassword","anydb");
  
  if (mysqli_connect_errno())
  {
    echo "Failed to connect to MySQL" ;
 mysqli_connect_error(); 
  }

  function insertValue( $val ){
    mysqli_query($con,"INSERT INTO ttt (someInteger) VALUES ( $val )");
  }
  
  for( $i =0; $i<99; $i++){
    //  Calling function to execute query one by one 
    insertValue( $i );
  }     
  // Closing the connection as best practice  
  mysqli_close($con);


The script above is much slower than the script below:
 
  $con=mysqli_connect("localhost","username","somepassword","anydb");
  if (mysqli_connect_errno())
  {
   echo "Failed to connect to MySQL" ;
   mysqli_connect_error(); 
  }
   
  function insertValues( $val ){
     // Creating query for inserting complete array in single execution.
    $query= " INSERT INTO tableX(someInteger) VALUES .implode(',', $val)";      
    mysqli_query($con, $query);
  }
  
  $data = array();
  for( $i =0; $i<99; $i++){
    // Creating an array of data to be inserted.
    $data[ ]  =   '(" ' . $i. '")' ;
  }
  // Inserting the data in a single call
  insertValues( $data );
  // Closing the connection as a best practice
  mysqli_close($con);

 
12. Frequently Used Switch Cases
Keep most frequently used switch cases on the top.
13. Use Methods in Derived Classes
Methods in derived classes are faster than base classes. For example, let there be a function in both base class and derived class for performing task1. It is named as “forTask1″ in base class and “forTask1again” in derived class, so that they will not override.

Call to the function “forTask1again( )” which is in derived class will work faster than call to the function “forTask1( )” as it is from base class.
 
  class someBaseClass
  {
   public function forTask1($string)
   {
    // perform task 1
   }
   public function forTask2( )
   {
    // perform task 2
   }
  }
  
  class derivedClass extends someBaseClass
  {
   public function forTask1again($string)
   {
    //perform task 1 same as the function in base class.
   }
   public function forTask3($string)
   {
    //perform task 3
   }
  }
  //Instantiating the derived class below.
  $objDerivedClass = new derivedClass( ); 
  
  // The call below works slow for task1 as it is from base class.
  $resultTask1 = $objDerivedClass->forTask1( );
  
  // The call below works faster for task1 as 
  // it is from derived class.
  $sameResultTask1 = $objDerivedClass->forTask1again();
 
14. Use JSON
Use JSON instead of XML while working with web services as there are native php function like json_encode( ) and json_decode( ) which are very fast. If you are bound to have XML form of data, then use regular expression to parse it instead of DOM manipulation.
15. Use isset
Use isset( ) where ever possible instead of using count( ), strlen( ), sizeof( ) to check whether the value returned is greater than 0.
For example, let us assume that you have a function which returns an array with values or a NULL array. Now you want to check whether the returned array is with values or not, then use the following:
if(isset($returnValue)){
  // do something here
}
In this case, use the above code block, instead of the following:
if(count($returnValue) > 0){
  // do something here
}