This is a read-only archive!

SQL Server: Ugh.

I wrote a massive SQL query, which runs fine in Query Analyzer. I copied/pasted it verbatim into Enterprise Manager to try to make a View out of it.

When you copy a query into Enterprise Manager, it automatically tries to make a pretty graph out of it, drawing a box for each table, and arrows connecting the boxes, in a way that causes just a slight bit of permanent psychological damage every time I see it done. To facilitate this process, Enterprise Manager will mercilessly hack your code to bits.

(I've found out that it will sometimes actually cause a query to produce the wrong results by doing this. I had to change a WHERE clause to a JOIN condition because Enterprise Manager changed the SQL enough to completely change the meaning of the query otherwise. Guh.)

So I copy/pasted my query into a new View, and saved it. No problem, though the SQL somehow became about 3x as long after Enterprise Manager's mangle-fest.

Then I added some WHERE clauses at the bottom of the query like this:

AND NOT ( 
   (ID = '1' AND DATE = '2001-01-01') 
   OR (ID = '2' AND DATE = '2002-02-02') 
   OR ...
)

That's it. Nothing more to it than that. About 10 lines. When I was done and clicked Save, Enterprise manager instantly consumed 300MB of RAM (according to Task Manager) and the computer slowed to a crawl as everything froze. I let it go 5 minutes before I killed it. I reloaded and tried again, and same thing. A process called mmc.exe was the culprit.

The same query (including the added parts) runs in about 15 seconds in Query Analyzer, note. I have no doubt Enterprise Manager could run the query. It just can't turn it into pretty connected boxes, so I can't save it as a View. My solution was to turn the whole thing into two Views instead of one, the second one doing nothing but SELECT * from the first one, then applying the ID / date filters. I swear every hour of using this program takes a day off my life.

January 30, 2006 @ 1:29 PM PST
Cateogory: Rants
Tags: Windows