You couldn't handle the text file

OK - so the deal is to figure out why a simple 2300 line SQL query that took 1:08 to run in the SQLServer Management tool during testing, is running for over an hour on another site and doesn't seem to be any where near completion. They can see it running,  but nothing is changing in the database. Testing was on a copy of the same database. Two other running remote databases that are smaller are also not complete and running forever.

Guess?

Turns out PRINT statements can be evil if you use enough of them. The update SQL was outputing some good-old-fashioned debugging information using PRINT "something" generously. These were withing a cursor, that was then in a loop that ran another loop - each of these with their own generous set of debugging data for the world to review upon completion.

While reviewing the output, I noticed that there were quite a few of them. In management studio, you can see the number of lines. In this case, it was over five million. Lines. Figure each line had 20-30 characters on it. This wasn't an issue in the GUI, but the SMO used had to marshall all that data somewhere. Takes some time. If you run it remote across the country, well, that's a long drive.

We killed the job, remoted locally and ran it in th GUI on the target server. Done in 1:10. Go figure.

Further testing showed that
a) even the GUI couldn't actually save the output results it showed. It would get an exception error.
b) SMO would probably eventually finish some day on its own - but no one in testing wanted to wait to see and killed it after two hours
c) Taking the print statements out allowed the SMO to finish in 1:20

Couldn't find any Microsoft reference on a MAX text results size ...

It gets easier with the title ...

The SENIOR DB developer removing duplicates swore that this was going to be much faster:

DELETE #someLocalTempTable
WHERE     CONVERT(VARCHAR(20),SomePKIDEN_Id)
    + NamesStringColumn
    + CONVERT(VARCHAR(20),SomeFKIDEN_Id)
    + CONVERT(VARCHAR(20),SomeOtherFKIDEN_ID)
   IN (
    SELECT CONVERT(VARCHAR(20),SomePKIDEN_Id)
    + NamesStringColumn
    + CONVERT(VARCHAR(20),SomeFKIDEN_Id)
    + CONVERT(VARCHAR(20),SomeOtherFKIDEN_ID)
    FROM ##AGlobalTempTable
    )

AS OPPOSED TO THIS:

DELETE t
FROM #someLocalTempTable     t (NOLOCK)
JOIN ##AGlobalTempTable     s (NOLOCK)
ON t.SomePKIDEN_Id         = s.SomePKIDEN_Id
    AND t.NamedStringColumn = s.NamedStringColumn
    AND t.SomeFKIDEN_Id     = s.SomeFKIDEN_Id
    AND t.SomeOtherFKIDEN_ID= s.SomeOtherFKIDEN_ID

Fast correlated sub-queries. Nice concept.

No we won't learn

Not more than a couple days of dealing with an obscure OSQL parsing bug (see link) that we should have known about, we QA-catch another that we already knew about.

If you fully qualify a table in another database and give it an alias, OSQL command parsing will not use the alias. That is you cannot

SELECT something FROM foobarDB.dbo.footable AS otable WHERE otable.someColumn IS NULL

The "otable.xxx" will cause an error as it cannot find that alias.
2005 et.al. seem to be fine.

Will we ever learn?

A while back I posted about a Microsoft bug in 2000 server installations. Caused a production error again, then this was followed by another (SELECT 1 FROM SYS.SYSINDEXES) the next day.

Turns out it is related to the OSQL/ISQLW/ISQL installed and not the server. You can run this same script from a machine with the 2005 tools installed and point to the 2000 server and there is no error. As we deploy using processes that run the file via an isql.exe -I command line, it is a big deal.

Point is then, when we are testing for compatibility of our scripts, we can't run it from the 2005 management studio. We will get a false positive. We have to run it through the local version of the tools!

ERROR 1
PRINT 'The error is in parsing the GO incorrectly'
/*
This is a comment
GO
*/

ERROR 2
--The error here is the prefix of the SYS. that will not parse correctly
SELECT * FROM SYS.SYSINDEXES

Bad Table!

Someone knowing that I do SQL sent me this ...




Source

Wonky and still logically incorrect

This is another result from the school of cut-and-paste polymorphism. The original IF statement checked for two items in an OR condition, explaining the apparent redundancy in the second IF - but not why anyone would check @@error twice and expect the same value...

IF (@@error <> 0)

BEGIN

IF @@error <> 0

BEGIN

Set @intResultCode = @@error

Set @chvResultText = 'Error when registering the Station/Date. ' +

(SELECT description from master..sysmessages where error = @intResultCode and msglangid = 1033)

END
END

Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement being verified, or save it to a local variable that can be checked later. Source: MSDN

Please use comments ...

Many management types continually rant about leaving comments in the code so that the next person will be able to figure out what is going on. Sometimes it just proves something else ...

'* Comments: IMPORTANT: The global constants are being temporarily prefaced with "" until
'* all the reports can be integrated with calls to the delegate methods. Afterwards,
'* a global search/replace of "" with "" should rename the constants to coding
'* standards compliancy.

This is going to hurt a little

Imaging a 5100 line stored procedure that is doing a conversion of some data. After hundreds of site updates, you get called in to find a wee little bug that seems to have no consistent behavior but it is definitely in the conversion. Then some where in that 5100 lines of code you find these snippets - several hundreds of lines apart but interacting just as viciously:

CREATE TABLE #Duplicates
(
RecID INT IDENTITY (1,1)
,i_id INT
, and other things
)

..eventually some insert code fills this temp table. Later we find this new table:

CREATE TABLE #Itemslist
(
RecID INT IDENTITY(1,1)
,INo INT
, and other things
)

...which also gets properly loaded. Eventually final results are loaded into yet another temporary table. I will spare you the details and just point out the join here;

SELECT some stuff
FROM #Itemslist il
JOIN #Duplicates d ON il.RecID = d.RecID
JOIN etcetera

Fortunately the duplicate table was very small which is the only thing that contained the error that propagated from this.

Where else does one equal zero?

From more of the old code from Hyberia: We figure that this is the lazy man's (person's) method to making insert tables without all that messy typing.


SELECT rlk.*, rlk.i_id as i_id2 --CREATE #RSLAG table
INTO #RSLAG --note.. the i_id2 column was added to
FROM RSLAG rlk --cause the original i_id column to be
WHERE 1 = 0 --created without the IDENTITY property

SELECT rlk.* --CREATE #RSLAG_total table
INTO #RSLAG_total
FROM #RSLAG rlk
WHERE 1 = 0

Query to NoWhere

In a three hundred line stored procedure (not bad) one developer noted that the performance issue came down to the corrolated sub-query. Upon inspection, this left-joined monster was not even referenced by any output column, where clause or reference.

What a FIND!

SELECT
a_bunch_of_stuff
FROM #RPT_ORDERS ot
LEFT JOIN (SELECT
Another_long_painful_query
FROM #RPTEXTRA toc
INNER JOIN A_BUNCH_OF_STUFF_TO_MAKE_IT_EVEN_SLOWER tm
WHERE toc.x = 400
GROUP BY toc.y
,tm.starts
) mt ON tmt.ord = mt.ord
AND tmt.starts = mt.starts
ORDER BY more_stuff

Code Blurg

When you are not really sure what you want, you can try it in more than one place! At the worst you get an additional line of code for your metrics.

insert #DeleteSpots

Select os.Linked_ID, os.BID

from #olspot ols

join dbo.ORDERED os on os.bid = ols.bid

AND os.B_Position IN(0,1,3)

where os.Linked_ID is not null

and os.B_Position = 3

Why have all the commission rates gone?

I liked this one. This is what happens when you start cutting and pasting code without looking at the end result real close.

In this situation, the developer was removing a variable that had a default value of zero. Guess they figured that the safest thing to do was to replace it with that number. There were a couple of places in this 460 line procedure to change. They must have checked the top half which was just fine, but the original designer had basically copied the first 200 lines and duplicated them in the second half - and he did not use the variable the same way. The maintenance developer did not check this second half which ended up as below.

Snippet:

SELECT
round(convert(money,sum(r.rate) * 0),2)
,0
,round(convert(money,sum((r.rate * 0) * isnull(r.ag_com_pct,0))),2)
,count(*)
FROM
Whatever

The French know the secret to destroying database performance!

I saw this on GOOGLE VIDEOS and thought it was the funniest SQL performance technical explanation I had ever seen. His name is Stephane Faroult at RoughSea Ltd. It is also nice in that he turned the problem on its head and looked at the performance problem backwards. How to make it worse.

Less is more

This is the story of a man named Jed
A poor programmer that had been hit in the head
Then one day he did a query for a home
Did a top one and ended in Oklahom (a)

Thing is, this guy correctly figured that for every address on a street, there was only one possible number. But there could be more than one person registered at a household and he really only wants one of them. So he does

SELECT TOP 1 FROM Adresses WHERE Street = @a and Address = @b

Thing he forgot about was that there might possibly be a 123 Main Street in more than one city. In a different state.
They're still digging themselves out of the huge data corruption that this created.

But but but

Another SQL 2000 failure that seems to be OK anywhere else you try it.

-- Do something
GOGO
-- Do other stuff

Why that isn't an error in 2005 is a little curious

Where am I

Here is the Microsoft way to figure the version and servicepack levels

Declare @V varchar(10), @sp varchar(5), @ed varchar(32), @nm varchar(32)
SELECT @v=convert(varchar(10),SERVERPROPERTY('productversion'))
,@sp=convert(varchar(5),SERVERPROPERTY ('productlevel'))
,@ed=convert(varchar(32),SERVERPROPERTY ('edition'))
,@nm=convert(varchar(32),@@servername)
Print 'SERVER:' + @nm
Print 'Version ' + @v
Print 'Level ' + @sp
Print @ed

My local 2008 server for example produces the following:

SERVER:WEB-TESTMULE
Version 10.0.1049. <====Note the extra period! Level CTP Developer Edition

What's in a name?

IN everything but SQL2000 this is not a problem. Seems in 2000, the tableName does not get
an automatic name reference, such that the use of tableName.xxx results in a syntax error.


You must alias the table name even if it is the same name.


SELECT tableName.Col FROM tableName tableName

What was that name again?

In SQL 2005 this is OK


SELECT 1
FROM sysobjects (nolock) o
JOIN syscolumns (nolock) c ON o.id = c.id


But we build in SQL 2000 and it must be like this


SELECT 1
FROM sysobjects o (nolock)
JOIN syscolumns c (nolock) ON o.id = c.id


So unless you alias the table before the hint, you will get a syntax error in some cases.


Incorrect syntax near 'o'.

GO speedracer GO

PRINT 'Here is a test'
/*
Here is a place for a comment to
GO
*/


The commented GO statement within the /* */ still executes and as such will cause a syntax error. This is corrected in 2005 servers but you will have to watch out for it on current boxes.

Update August fifthe 2009
We got hit by this again so I did some further experiments. Turns out it is related to the OSQL/ISQLW/ISQL installed and not the server. You can run this same script from a machine with the 2005 tools installed and point to the 2000 server and there is no error.

Point is then, when you are testing for compatibility of your scripts, don't run it from the 2005 management studio. You will get a false positive.

Do AS I say

Try this on a SQL 7 or 2000 box


DECLARE @Fred AS TABLE (ID INT)


and what you will get is a syntax error. Another oddity that works fine once you move to SQL 2005 but that little AS in the declaration is a syntax error anywhere else. Seems most people do not run into this little gem because the AS is not required, and why would you type it if its not needed? That would be like using a LET assignment keyword :(