SQL Server – Data Encryption – Compare Bulk Data Retrieval

Background

As a follow up to a foundational post on data encryption, let us compare the cost of reading and persisting the read data.

DDL

We will create Views and Stored Procedures.

DDL – Retrieve Data – Declarative



if object_id('[dataPlain].[vw_accountHolder]') is null
begin

	exec('create view [dataPlain].[vw_accountHolder] as select 1/0 as [shell]' )

end
go


alter view [dataEncrypted].[vw_accountHolder]
as

alter view [dataPlain].[vw_accountHolder]
as

	SELECT
		  [id]
		, tblAH.[entityID]
		, tblAH.[firstname]
		, tblAH.[lastname]

		, tblAH.[addedBy]   
		, tblAH.[addedOn]   

	from    [dataPlain].[accountHolder] tblAH

go

DDL – Retrieve Plain Data – Procedural


if object_id('[dataPlain].[usp_accountHolder_Retrieve]') is null
begin

	exec('create procedure  [dataPlain].[usp_accountHolder_Retrieve] as select 1/0 as [shell]' )

end
go


alter procedure [dataPlain].[usp_accountHolder_Retrieve]
as

	set nocount on;

	SELECT
		  [id]
		, tblAH.[entityID]
		, tblAH.[firstname]
		, tblAH.[lastname]

		, tblAH.[addedBy]   
		, tblAH.[addedOn]   

	from    [dataPlain].[accountHolder] tblAH

go

DDL – Retrieve Encrypted Data – Declarative



if object_id('[dataEncrypted].[vw_accountHolder]') is null
begin

	exec('create view [dataEncrypted].[vw_accountHolder] as select 1/0 as [shell]' )

end
go


alter view [dataEncrypted].[vw_accountHolder]
as

   SELECT
	  [id]
	, cast
		(
		    DecryptbyKeyAutoCert
		    (
			  cert_id('certPIA')
			, null
			, tblAH.[entityID]
			, 1
			, CONVERT(varbinary(8000), tblAH.[id])
		    )
			as varchar(80)
		) as [entityID]

	, cast
		(
		    DecryptbyKeyAutoCert
		    (
			  cert_id('certPIA')
			, null
			, tblAH.[firstname]
		        , 1
			, CONVERT(varbinary(8000), tblAH.[id])
		    )
			as nvarchar(80)
		) as [firstname]


	, cast
	       (
		   DecryptbyKeyAutoCert
		   (
			  cert_id('certPIA')
			, null
			, tblAH.[lastname]
			, 1
			, CONVERT(varbinary(8000), tblAH.[id])
		  )
			as nvarchar(80)
		) as [lastname]

	, tblAH.[addedBy]   
	, tblAH.[addedOn]   

  from    [dataEncrypted].[accountHolder] tblAH


go

 

DDL – Retrieve Encrypted Data – Procedural

 




if object_id('[dataEncrypted].[usp_accountHolder_Retrieve]') is null
begin

	exec('create procedure  [dataEncrypted].[usp_accountHolder_Retrieve] as select 1/0 as [shell]' )

end
go


alter procedure [dataEncrypted].[usp_accountHolder_Retrieve]
as

   set nocount on;

   SELECT
	  [id]
	, cast
		(
		   DecryptbyKeyAutoCert
		   (
		      cert_id('certPIA')
		    , null
		    , tblAH.[entityID]
		    , 1
		    , CONVERT(varbinary(8000), tblAH.[id])
		   )
		   as varchar(80)
		) as [entityID]

	, cast
	   (
		DecryptbyKeyAutoCert
		(
	  	    cert_id('certPIA')
		  , null
		  , tblAH.[firstname]
		  , 1
		  , CONVERT(varbinary(8000), tblAH.[id])
		)
		  as nvarchar(80)
	   ) as [firstname]


	, cast
		(
	  	  DecryptbyKeyAutoCert
		  (
		     cert_id('certPIA')
	 	  , null
		  , tblAH.[lastname]
		  , 1
		  , CONVERT(varbinary(8000), tblAH.[id])
		 )
		  as nvarchar(80)
	     ) as [lastname]

	, tblAH.[addedBy]   
	, tblAH.[addedOn]   


  from    [dataEncrypted].[accountHolder] tblAH

go

Performance Measurement

Read data

Simple read data and display result on Screen/Grid.

Notice this is quite in-efficient and we are only attempting to do so with the fore-thought that our data set is only a thousand records.

Code


set nocount on;
go

set statistics io on
go


print '*****************************************************************************************'
print 'Plain data via Declarative Code'
print '*****************************************************************************************'
select *
from   [dataPlain].[vw_accountHolder]

print ''

print '*****************************************************************************************'
print 'Plain data via Procedure Code'
print '*****************************************************************************************'
exec [dataPlain].[usp_accountHolder_Retrieve]

print ''

print '*****************************************************************************************'
print 'Encrypted data via Declarative Code'
print '*****************************************************************************************'

select *
from   [dataEncrypted].[vw_accountHolder]

print ''

print '*****************************************************************************************'
print 'Encrypted data via Procedural Code'
print '*****************************************************************************************'
exec [dataEncrypted].[usp_accountHolder_Retrieve]


Query Plan

displayData

 

Interpretation

  • When we read plain data view direct database table query and through Stored Procedure, our Query Cost is 16%
  • When we read encrypted data via view and through Stored Procedure our Query Cost is 34%
  • We see that encrypted query is twice as expensive
  • Also, queries targeting Encrypted  Objects bears “Restricted Text” for Stored Procedures

 

IO

StatisticsIO

Interpretation

  • When we read plain data view direct database table query and through Stored Procedure, our Statistics IO is 19
  • When we read encrypted data via view and through Stored Procedure our Statistics IO is 43
  • We see that encrypted query is twice as expensive in terms of IO

 

 

 

Read & Capture Data into a table

Code



set nocount on;
go

set statistics io on
go


declare @accountHolder TABLE
	(
		  [id]		bigint not null --identity(1,1)
		, [entityID]    varchar(60) not null  
		, [firstname]   nvarchar(60) not null
		, [lastname]    nvarchar(60) not null

		, [addedBy]     nvarchar(60) not null

		, [addedOn]     datetime not null

	)

print '*****************************************************************************************'
print 'Plain data via Declarative Code'
print '*****************************************************************************************'
insert into  @accountHolder
(
	  [id]	
	, [entityID]
	, [firstname] 
	, [lastname]  
	, [addedBy]   
	, [addedOn]   
)
select *
from   [dataPlain].[vw_accountHolder]

print ''

print '*****************************************************************************************'
print 'Plain data via Procedure Code'
print '*****************************************************************************************'
insert into  @accountHolder
(
	  [id]	
	, [entityID]
	, [firstname] 
	, [lastname]  
	, [addedBy]   
	, [addedOn]   
)
exec [dataPlain].[usp_accountHolder_Retrieve]

print ''

insert into  @accountHolder
(
	  [id]	
	, [entityID]
	, [firstname] 
	, [lastname]  
	, [addedBy]   
	, [addedOn]   
)
select *
from   [dataEncrypted].[vw_accountHolder]

print ''

print '*****************************************************************************************'
print 'Encrypted data via Procedural Code'
print '*****************************************************************************************'

insert into  @accountHolder
(
	  [id]	
	, [entityID]
	, [firstname] 
	, [lastname]  
	, [addedBy]   
	, [addedOn]   
)
exec [dataEncrypted].[usp_accountHolder_Retrieve]

 

Query Plan

QueryPlan

 

Interpretation

  • When we consumed plain and encrypted data via views our Query Cost is at 41% and 44% respectively
  • When we consumed same data via Stored Procedures our Query Cost is 2% for plain data and 8% for encrypted data
  • It seems that the SQL Engine is not properly computing the costs of accessing encrypted data

 

Statistics IO

StatisticsIO

Interpretation

  • Plain data
    • Consuming plain data read from the view resulted in about 19 logical reads
    • Same data when captured from a Stored Procedure costed same 19, but there is an added on 1013 for our targeted table or 2150 from a work table
  • Encrypted data
    • When reading encrypted data from a view, we expended 43 logical reads
    • It costed an additional 1013 for our destination table, and 2140 for a worktable

 

More on Insert Into / Exec Stored Procedure

Let us dig more into Insert Into/Exec SP.

This time rather than to place data into temporary or Table Variable, we will place data into an actual table.

Also, no encrypted data, just plain data!

Code




use [dbLAB]
go

set nocount on;
set statistics io on
go


if object_id('zzzAccountHolder') is null
begin

	create table zzzAccountHolder
	(
		  [id]		bigint not null --identity(1,1)
		, [entityID]    varchar(60) not null  
		, [firstname]   nvarchar(60) not null
		, [lastname]    nvarchar(60) not null

		, [addedBy]     nvarchar(60) not null

		, [addedOn]     datetime not null

	)

end

print '*****************************************************************************************'
print 'Plain data via Declarative Code'
print '*****************************************************************************************'
insert into  zzzAccountHolder
(
	  [id]	
	, [entityID]
	, [firstname] 
	, [lastname]  
	, [addedBy]   
	, [addedOn]   
)
select *
from   [dataPlain].[vw_accountHolder]


truncate table zzzAccountHolder

print ''
print '********************************************************************************************************************************************************************************'
print ''


print '*****************************************************************************************'
print 'Plain data via Procedural Code'
print '*****************************************************************************************'
insert into  zzzAccountHolder
(
	  [id]	
	, [entityID]
	, [firstname] 
	, [lastname]  
	, [addedBy]   
	, [addedOn]   
)
exec [dataPlain].[usp_accountHolder_Retrieve]


Query Plan

QueryCost

Explanation

  1. View
    • Querying and Consuming the view comes in at 88%
  2. Stored Procedure
    • Querying and Consuming the SP is a two step process
      • In the SP we query the data
      • In the Insert Statement, the yielding of the SP data is indicated as a “Parameter Table Scan

Statistics I/O

IOStats

The blindside is the Worktable I/O

WorkTable

The most expensive IO task.

Summary

Interesting results.

Expected the more expensive reads when querying encrypted data.

But, did not expect that consuming data from Stored Procedure will be far more expensive than reading same from a View.

 

Listening

Listening to Miranda…

Miranda Lambert – More like her

Miranda On Stage

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s