Code Project

Link Unit

Wednesday, September 03, 2014

Performance Improvement

 
Update Statistics: 

Let's look at following query.

SELECT *
FROM customer
WHERE city = 'Pune'
 AND phone = '2020666666' -- dummy Number 
 
Above query contain two fields in the "WHERE" clause and suppose there are two indexes defined, each containing one field. One very important notion to remember is that the optimizer can only use ONE index per table.

Therefore, it has to make a decision as to which index to use. Ideally, optimizer should pick index created on phone but in absence of updated statistics it can pick cityIndex.

This decision can be informed one if statistics are up to date.


Avoid foreign key constraints

Foreign keys constraints ensure data integrity at the cost of performance. Therefore, if performance is your primary goal you can push the data integrity rules to your application layer. A good example of a database design that avoids foreign key constraints is the System tables in most databases. Every major RDBMS has a set of tables known as system tables. These tables contain meta data information about user databases. Although there are relationships among these tables, there is no foreign key relationship. This is because the client, in this case the database itself, enforces these rules.

Monday, September 01, 2014

Find Range of continous values

There are many ways to find out gaps & range, but this standard gaps-and-island solution that I found on net is coolest one. Basic idea of this solution is to group by (value minus row_number), since that is invariant within a consecutive sequence. The start and end dates are just the MIN() and MAX() of the group.

The value could be int or date.


DECLARE @data TABLE (i INT)

INSERT INTO @data
VALUES (1)
 ,(2)
 ,(4)
 ,(5)
 ,(10)
 ,(11)
 ,(15)
 ,(16)
 ,(17)
 ,(18)
 ,(19)
 ,(20)
 ,(21);

WITH tab
AS (
 SELECT i d
  ,ROW_NUMBER() OVER (
   ORDER BY i
   ) r
 FROM @data
 )
SELECT min(d)
 ,max(d)
FROM tab
GROUP BY d - r
 
Output: 
 
 

Hope it helps

Wednesday, July 23, 2014

Easy way to split string

We generally use function to split a delimited string into rows. The code for same is listed below
CREATE FUNCTION
[dbo].[fnSplitString] ( @string NVARCHAR(MAX) ,@delimiter CHAR(1) ) RETURNS@output
TABLE (splitdata NVARCHAR(MAX)) BEGIN DECLARE @start INT ,@end INT SELECT @start = 1 ,@end = CHARINDEX(@delimiter, @string) WHILE @start < LEN(@string) + 1 BEGIN IF @end = 0 SET @end = LEN(@string) + 1 INSERT INTO @output (splitdata) VALUES (SUBSTRING(@string, @start, @end - @start)) SET @start = @end + 1 SET @end = CHARINDEX(@delimiter, @string, @start) END RETURN END GO We will be using XQuery method to split the string in easy and efficient way. Following steps are taken to split the string. #1. Convert the string into formatted XML,
for that replace the delimitor with any tag (used in example). #2. Remove empty nodes like '' or '' #3. list value of all nodes USE tempdb DECLARE @x VARCHAR(MAX) DECLARE @x1 XML DECLARE @t1 DATETIME DECLARE @t2 DATETIME SET @x = '100|101|102|' SET @x = replicate(@x, 1) SELECT @x1 = cast
(
replace
(
replace
(
'<x>' + replace
(
@x, '|', '</x><x>')
+ '</x>', '<x ></x>', '')
, '<x></x>', '')
AS XML)
SET @t1 = getdate() -- start time of process SELECT X.x1.value('.', 'varchar(max)') FROM @x1.nodes('//x') X(x1) -- XQuery SET @t2 = getdate() -- End time of process SELECT datediff(ms, @t1, @t2) -- Find time difference in milliseconds SET @t1 = getdate() -- start time of process SELECT * FROM [dbo].[fnSplitString](@x, '|') SET @t2 = getdate() -- End time of process SELECT datediff(ms, @t1, @t2) Benchmarks for XQuery method to split the string -- XQuery -- 0ms for 3 -- 0ms for 30 -- 6ms for 300 -- 36ms for 3000 -- 350ms for 30000 -- 7s for 300000 Benchmarks for Procedure method to split the string -- 3ms for 3 -- 10ms for 30 -- 100ms for 300 -- Procedure 1m for 3000 -- Procedure 11s for 30000 -- Procedure 2m15s for 300000
Conclusion: Looking at performance and simplicity of XQuery, I will recommend it over procedural way. Hope it helps.

Friday, June 20, 2014

Easy Way to remove HTML tags in SQL Server

We were looking for a way to remove/strip HTML tags from varchar columns. On making search found some code samples, which suggested to create a stored procedure like following.

Click to view Stored Procedure StripHtml

CREATE FUNCTION [dbo].[StripHtml] (@HTMLText VARCHAR (MAX))
RETURNS VARCHAR (MAX)
AS
BEGIN
DECLARE @Start  int
DECLARE @End    int
DECLARE @Length int

-- Replace the HTML entity & with the '&' character (this needs to be done first, as
-- '&' might be double encoded as '&amp;')
SET @Start = CHARINDEX ('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, '&')
SET @Start = CHARINDEX ('&', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity < with the '<' character
SET @Start = CHARINDEX ('<', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, '<')
SET @Start = CHARINDEX ('<', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity > with the '>' character
SET @Start = CHARINDEX ('>', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, '>')
SET @Start = CHARINDEX ('>', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity & with the '&' character
SET @Start = CHARINDEX ('&amp;', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, '&')
SET @Start = CHARINDEX ('&amp;', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END

-- Replace the HTML entity   with the ' ' character
SET @Start = CHARINDEX (' ', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, ' ')
SET @Start = CHARINDEX (' ', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
END

-- Replace any
tags with a newline
SET @Start = CHARINDEX ('<br />', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, CHAR(13) + CHAR(10))
SET @Start = CHARINDEX ('<br />', @HTMLText)
SET @End = @Start + 3
SET @Length = (@End - @Start) + 1
END

-- Replace any
tags with a newline
SET @Start = CHARINDEX ('<br />', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, 'CHAR(13) + CHAR(10)')
SET @Start = CHARINDEX ('<br />', @HTMLText)
SET @End = @Start + 4
SET @Length = (@End - @Start) + 1
END

-- Replace any
tags with a newline
SET @Start = CHARINDEX ('<br />', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, 'CHAR(13) + CHAR(10)')
SET @Start = CHARINDEX ('<br />', @HTMLText)
SET @End = @Start + 5
SET @Length = (@End - @Start) + 1
END

-- Remove anything between tags
SET @Start = CHARINDEX ('<', @HTMLText)
SET @End = CHARINDEX ('>', @HTMLText, CHARINDEX ('<', @HTMLText))
SET @Length = (@End - @Start) + 1

WHILE (@Start > 0 AND @End > 0 AND @Length > 0) BEGIN
SET @HTMLText = STUFF (@HTMLText, @Start, @Length, '')
SET @Start = CHARINDEX ('<', @HTMLText)
SET @End = CHARINDEX ('>', @HTMLText, CHARINDEX ('<', @HTMLText))
SET @Length = (@End - @Start) + 1
END

RETURN LTRIM( RTRIM(@HTMLText))

END

GO



This stored procedure does what it says, but it is lot of code and procedural. So, thought of using XML data type and value method of SQL Server as embedded below.










NB: HTML text must be properly formatted otherwise we will get XML Parsing error.

Hope it Helps.

Monday, April 14, 2014

0x8004271A ODSOLE Extended Procedure Error in srv_convert.

We were trying to make a hosted web service call and retrieve the response. Following code segment was working fine for smaller responses.

set nocount on
declare @objWinHttp int
declare @strLine varchar(8000)
declare @hr int

exec @hr =sp_OACreate 'WinHttp.WinHttpRequest.5.1', @objWinHttp out
print @hr
EXEC sp_OAMethod @objWinHttp, 'Open',NULL,'GET','http://www.webservicex.com/stockquote.asmx/GetQuote?symbol=MSFT',false
EXEC sp_OAMethod @objWinHttp, 'Send',NULL
--- Problematic call
exec @hr =sp_OAGetProperty @objWinHttp, 'ResponseText',@strLine OUtPUT
---
print @hr
print @strLine
exec sp_OADestroy @objWinHttp

but when response text is larger than 4000 bytes it failed with following error
-2147211494

OLE Automation Error Information
  HRESULT: 0x8004271a
  Source: ODSOLE Extended Procedure
  Description: Error in srv_convert.
Reason:
Length of response > 4000.
Microsoft has description about the issue at http://support.microsoft.com/kb/325492

Solution:
As a workaround we created a temporary table with nvarchar column having size as max. We inserted the output of procedure to the table

--- Changed the call : removed @strLine OUtPUT
Create table #tmp(dt nvarchar(max))
insert into #tmp
exec @hr =sp_OAGetProperty @objWinHttp, 'ResponseText' --,@strLine OUtPUT
 Select dt from #tmp -- single column/single row.
Drop Table #tmp -- clean up
---

It worked for us. Hope it help others.


Thursday, March 06, 2014

SQL Agent + Excel issue

We have a SSIS package which is suppose to execute macro. This SSIS package works fine when it is executed from the development environment but fails with following message when package is scheduled as job.


System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file 'XYZ.xls'. There are several possible reasons:

The file name or path does not exist.
The file is being used by another program.
The workbook you are trying to save has the same name as a currently open workbook.


Solution/Workaround:
it is a workaround for a bug in Windows:

1. Create directory "C:\Windows\SysWOW64\config\systemprofile\Desktop " (for 64 bit Windows) or "C:\Windows\System32\config\systemprofile\Desktop " (for 32 bit Windows)
2. Set Full control permissions for directory Desktop (for user "SYSTEM")

Please do this for both 32 and 64 bit folder.

Hope it helps