Devart Blog

SQL Complete: Parsing performance improved!

Posted by on December 13th, 2010

Recently we have released a free SQL Complete tool that provides IntelliSense and script layout for SQL Server. If you like IntelliSense in Visual Studio and write T-SQL, you’ll probably appreciate the productivity improvements with SQL Complete.

However, we received many letters with complaints about SQL Complete work with relatively large scripts with thousands of lines of code. When opening such a script our tool could display the “System.StackOverflowException” message. On some DML statements our add-in even could hang up SSMS. For example, the following query with a large number of OR and AND keywords in the WHERE clause caused hang-ups:

SELECT
   A.*
FROM
   Table1 A INNER JOIN Table2 B ON A.Id = B.Id	
WHERE
   ISNULL(A.Column1, '') <> ISNULL(B.Column1, '')
      OR ISNULL(A.Column2, '') <> ISNULL(B.Column2, '')
      OR ISNULL(A.Column3, 0) <> ISNULL(B.Column3, 0)
      OR ISNULL(A.Column4, '') <> ISNULL(B.Column4, '')
      OR ISNULL(A.Column5, 0) <> ISNULL(B.Column5, 0)
      ...
      OR ISNULL(A.ColumnN, 0) <> ISNULL(B.ColumnN, 0)

The same could have happened because of large numbers of mathematical operators:

SELECT
      'A,' + 'B,' + @var1 + ',' + ',' + ',' + @var2 + ',' +
      ISNULL(REPLACE(CONVERT(VARCHAR(10) , column1 , 103) , '/' , '') , '') + ',' 
      + @var3 + ',' + '"' +
      ISNULL(CAST(column2 AS VARCHAR) , '') + ISNULL(CAST(column3 AS VARCHAR) , '') +
      CASE WHEN ABS(column4) > ABS(column5) THEN '-1'
           ELSE ''
      END + '"' + ',' + '"",' + ISNULL(column6 , '') + ',' + ',' + ',' + ',' +
      ISNULL(CAST(CAST(( -1 * column5 ) AS DECIMAL(20 , 4)) AS VARCHAR) , '') + ',' + '"' +
      REPLACE(REPLACE(@var4 , '@@KST' , column7) , '@@DEBITOR' , column6) 
      + '"' + ',' + ',' + ',' +
      ISNULL(column10 , 'EUR') + ',' + ',' + ',' + ',' + 
      ISNULL(REPLACE(CONVERT(VARCHAR(10) , column1 + CAST(column8 AS DATETIME) , 103) ,
         '/' , '') , '') + ',' +
      ISNULL(CAST('' AS VARCHAR) , '') + ',' + ISNULL(CAST('' AS VARCHAR) , '') + ',' +
		--',' + ',' + ',' + ',' + ',' + ',' + ',' + ',' + ',' + ',' +
      REPLICATE(',' , 10) + ISNULL('"' + column9 + '"' , '""') + ',' + ',' 
      + ',' + ',' + ',' + ',' + ',' + ',' +
      ',' + ',' + ',' + ',' + ',' + ',' + ',' + ',' AS _output_
  FROM 
      tabl3

We have done a lot of work optimizing parcing of large scripts, and all these inconveniences were fixed. Also the “Error HRESULT E_FAIL has been returned from a call to a COM component.” error that occurred when working with several documents and closing one of them was fixed.

Also users can take advantage of colour schemas that differ from the standard colour schema of SSMS. Because selected object was marked with yellow colour by default and could not be changed, in some cases in user-defined colour schemas the colour of symbols was the same as the schema colour. Now the colour of all elements of the completion drop-down list is selected from the current colour schema.

You can view a complete list of fixes in SQL Complete.

Download the free add-in for SSMS by Devart.

Leave a Reply