Blog Post

• Posted by:
• Richard Lee
• 2 comments
• 

Application developers are often faced with slow performance plus a ton of security issues when writing sql queries. One of the reasons for this is the nature of the queries needed to support the application's user interface. Having a great user experience (UX) is paramount to having a successful product, whether internal to your company or on the open market; having an interactive and intuitive user interface plus a well thought out user experience with the queries to effectively power it is a must.

The scenario I will be addressing today is where the user makes a multi-valued selection along with other criteria and submits the request expecting a report to be returned before frustration kicks in.

Problem

The user has selected several countries and a date range using the very response and intuitive user interface and now clicks submit and expects a report showing all the orders from the countries selected within the specified period.

To solve this problem developers' needs to take the criteria and perform the necessary search(es) and then present the results to the user. Some ways developers have chosen to solve the problems are:

1. Dynamically construct and sql query and execute this query using for example ADO / ADO.NET
   Poor performance and subject to sql injection
   
2. Create a stored procedure that accepts a country, start and end date then execute this query for each country selected by the user
   Poor performance and application scale
   
3. Create a stored procedure that has more than one country parameters and the query then ignores the ones that are blank
   Poor application design. Extendability & maintainance issues
   
4. Create a stored procedure that takes a delimited string of country IDs and somehow incorporate the literal into the query(s) within the stored procedure.
   Possibly open to SQL injection through delimited string parameter and slight inferior performance

While these queries may work they would not provide the kick ass performance we need or in some cases abide by the security policies to prevent out application from being violated (those hackers) in ways we cannot imagine :).

Solution

We want to make one database request, take advantage of stored procedures, avoid sql injection and maintain good design practices

Here is our query:

CREATE PROCEDURE [dbo].[usp_Orders_ReadByCountryAndDate]
	@StartDate SMALLDATETIME,
	@EndDate SMALLDATETIME,
	
	/* The size can be changed or even 
		substituted with MAX, however this
		size should be sufficient. 300 countries
		X ISO Code 3 = 900 */
	@CountryIds VARCHAR(900),
	@Delimiter VARCHAR(20)
AS
BEGIN
	SET NOCOUNT ON;

	SELECT Orders.*, Country.Name
		FROM Orders
			INNER JOIN fn_SplitString(@CountryIds, @Delimiter) IdList
				ON Orders.CountryCode = IdList.Item
			INNER JOIN Country ON Orders.CountryCode = Country.ISOCode3
		WHERE Created BETWEEN @StartDate AND @EndDate
END

1. To make one database request and maintain good design practices we pass our country IDs as a delimited string - using a variant of option 4 above but avoiding the pitfalls. We need to be wary of SQL injection when constructing our query since we are passing text acquired from user interface into our query (Your UI layer should have validated the data, but attacker may well bypass this somehow so as a second defense your business layer should have validated your input and as a third line of defense don't allow your database to succumb to such attacks).
2. Sql injection survives on incorporating user input into your queries at a command definition level - this simply means the user input is injected into the command. To combat this we have transformed the user input into data and allow our command/query to act on the data.
3. The clever user of an inline table-valued function and joining this table in our query leverages the power of sql server and avoids loops, table scans, scalar functions, etc that will deteriorate the performance of our query.

Here is our supporting inline-table function:

This takes a delimited string and splits it into its various parts, inserting them into a table and returns the table to the caller.

CREATE FUNCTION [dbo].[fn_SplitString]
(
    @InputList VARCHAR(8000),		-- List of delimited items
	@Delimiter VARCHAR(20) = '|'	-- delimiter that separates items
) 
RETURNS @List TABLE (item VARCHAR(8000))
BEGIN
	DECLARE @Item VARCHAR(8000)

	WHILE CHARINDEX(@Delimiter,@InputList,0) <> 0
	BEGIN
		SELECT
			@Item=RTRIM(LTRIM(SUBSTRING(@InputList,1,CHARINDEX(@Delimiter,@InputList,0)-1))),
			@InputList=RTRIM(LTRIM(SUBSTRING(@InputList,CHARINDEX(@Delimiter,@InputList,0)+LEN(@Delimiter),LEN(@InputList))))

		IF LEN(@Item) > 0
			INSERT INTO @List SELECT @Item
	END

	IF LEN(@InputList) > 0
		INSERT INTO @List SELECT @InputList -- Put the last item in
		
	RETURN
END

Results

Executing this query on an orders table with 1,000,000 records using a date range from 01/01/1991 to 01/01/2005, filtering on the following countries PRT = Portugal, SPM = Saint Pierre and Miquelon, ITA = Italy, ESP = Spain and USA = United States of America with the result and time statistics as follows:

SET STATISTICS TIME ON 

EXEC	@return_value = [dbo].[usp_Orders_ReadByCountryAndDate]
		@StartDate = '1991-01-01',
		@EndDate = '2005-01-01',
		@CountryIds = N'PRT|SPM|ITA|ESP|USA',
		@Delimiter = N'|'
		
SET STATISTICS TIME OFF

SELECT	'Return Value' = @return_value

1st run

SQL Server parse and compile time:
CPU time = 31 ms, elapsed time = 92 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 633 ms.

SQL Server Execution Times:
CPU time = 171 ms, elapsed time = 725 ms.

2nd Run

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 219 ms, elapsed time = 466 ms.

SQL Server Execution Times: CPU time = 219 ms, elapsed time = 466 ms.

Here is our schema and create scripts (SQL Server 2008):

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Country](
	[ISOCode3] [nchar](3) NOT NULL,
	[ISOCode2] [nchar](2) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED 
(
	[ISOCode3] 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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Orders](
	[Id] [int] NOT NULL,
	[Created] [datetime] NOT NULL,
	[Product] [varchar](20) NOT NULL,
	[CountryCode] [nchar](3) NOT NULL,
 CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED 
(
	[Id] 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
CREATE NONCLUSTERED INDEX [IX_Orders] ON [Orders] 
(
	[Created] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_1] ON [Orders] 
(
	[CountryCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
ALTER TABLE [Orders]  WITH CHECK ADD  CONSTRAINT [FK_Order_Country] FOREIGN KEY([CountryCode])
REFERENCES [Country] ([ISOCode3])
GO
ALTER TABLE [Orders] CHECK CONSTRAINT [FK_Order_Country]
GO

Don't forget to leave your comments and updates.