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.

I swear I don't make this up ...

This is the latest software configuration option. It has a yes/no setting.

AllowBadAddressOnValidation




LOL