Comment On Are You IN Or NOT IN?

could someone please draw me a map back to sanity?  i seem to be lost...

yeesh.

sozin:

...and somehow I doubt whoever coded it thought
about the 255 max # of elems you can have in the IN( ... ) clause

I have a sample query that was done in MS Sql Server.  that is 600 lines.

it executes in less than 1 second.  so it can't be Sql Server.  

I still can't figure out why that query (the WTF) is slow.

sozin:

...and somehow I doubt whoever coded it thought
about the 255 max # of elems you can have in the IN( ... ) clause

//Fixed

SELECT DISTINCT SMS_G_System_SYSTEM.Name 
  FROM SMS_R_System
       INNER JOIN SMS_G_System_SYSTEM
               ON SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceID 
 WHERE SMS_G_System_SYSTEM.Name NOT IN (
            'STC0017127', 'STC0015341', 'STC0015342', 'STC0015343', 
            'STC0015344', 'STC0015345', 'STC0015346', 'STC0015347', 
            'STC0015348', 'STC0017117', 'STC0017118', 'STC0017119', 
            'STC0017120', 'STC0017121', 'STC0017122', 'STC0017123', 
            'STC0017124', 'STC0017125', 'STC0017126', 'STC0017128', 
            'STC0019158', 'STC0019160' 
            )

And my guess is that he doesn't need to do the inner join... unless
he's really sure he needs records that are both in SMS_G and SMS_R,
which I think are fucked up table names.

The "SELECT DISTINCT" is fun; you almost always start to see SELECT
DISTINCT pop up a lot in queries after a system has aged for a while
(nobody goes to the trouble of normalization).

Fun fun fun!

Anonymous:

sozin:

...and somehow I doubt whoever coded it thought
about the 255 max # of elems you can have in the IN( ... ) clause

The fact that you know this means you probably weren't thinking it had any limits at some point in the past.

Rick:

At an interview recently, I was asked, "How many tables can you join in one Sybase query?"

I responded, "More than you should use, but it used to be 16. I don't know what the current limit is."

I wasn't impressed with the interviewer.

sozin:

...and somehow I doubt whoever coded it thought about the 255 max # of elems you can have in the IN( ... ) clause

Microsoft SQL Server has no such limitation.

Anonymous:

I have a sample query that was done in MS Sql Server.  that is 600 lines.

it executes in less than 1 second.  so it can't be Sql Server.  

I still can't figure out why that query (the WTF) is slow.

 

Indexing strategies and table row counts could fully account for the slow performance in this case.  The number of lines in the query is not a good indicator.  This query, although inane, would probably run well if there was indexes on the columns of interest. 

christoofar:

The "SELECT DISTINCT" is fun; you almost always start to see SELECT
DISTINCT pop up a lot in queries after a system has aged for a while
(nobody goes to the trouble of normalization).

Anonymous:

//Fixed

SELECT DISTINCT SMS_G_System_SYSTEM.Name 
  FROM SMS_R_System
       INNER JOIN SMS_G_System_SYSTEM
               ON SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceID 
 WHERE SMS_G_System_SYSTEM.Name NOT IN (
            'STC0017127', 'STC0015341', 'STC0015342', 'STC0015343', 
            'STC0015344', 'STC0015345', 'STC0015346', 'STC0015347', 
            'STC0015348', 'STC0017117', 'STC0017118', 'STC0017119', 
            'STC0017120', 'STC0017121', 'STC0017122', 'STC0017123', 
            'STC0017124', 'STC0017125', 'STC0017126', 'STC0017128', 
            'STC0019158', 'STC0019160' 
            )

And my guess is that he doesn't need to do the inner join... unless he's really sure he needs records that are both in SMS_G and SMS_R, which I think are fucked up table names.

The "SELECT DISTINCT" is fun; you almost always start to see SELECT DISTINCT pop up a lot in queries after a system has aged for a while (nobody goes to the trouble of normalization).

Fun fun fun!

lol, gee, your like 1 order smarter.

Anonymous:

At least he didn't use SELECT *[;)]

Anonymous:

[+o(] never seen anything horreble than this [8-)]

I'll probably get flamed for this but:  Without proper insight into the database design and the restrictions placed on the developers it's tough to say if the WTF is on the developers or the database.

Depending on the the structure or lack-there-of of the 2 tables and the business rules imposed on them, a query like this may be required to get the proper result. 

Now I'm not saying that the IN list shouldn't have been stored in a table somewhere, but the developers may not have been allowed to alter the schema in this case.

Anonymous:

//Fixed

SELECT DISTINCT SMS_G_System_SYSTEM.Name 
  FROM SMS_R_System
       INNER JOIN SMS_G_System_SYSTEM
               ON SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceID 
 WHERE SMS_G_System_SYSTEM.Name NOT IN (
            'STC0017127', 'STC0015341', 'STC0015342', 'STC0015343', 
            'STC0015344', 'STC0015345', 'STC0015346', 'STC0015347', 
            'STC0015348', 'STC0017117', 'STC0017118', 'STC0017119', 
            'STC0017120', 'STC0017121', 'STC0017122', 'STC0017123', 
            'STC0017124', 'STC0017125', 'STC0017126', 'STC0017128', 
            'STC0019158', 'STC0019160' 
            )

And my guess is that he doesn't need to do the inner join... unless he's really sure he needs records that are both in SMS_G and SMS_R, which I think are fucked up table names.

The "SELECT DISTINCT" is fun; you almost always start to see SELECT DISTINCT pop up a lot in queries after a system has aged for a while (nobody goes to the trouble of normalization).

Fun fun fun!

If case sensitivity is turned on on the database and the field name "ResourceId" in the sub-select of the original query isn't a typo from Alex's anonymizer the query may be joining to a completely seperate (albeit poorly named) field. 

I know there's a WTF in there but without more information we can't say where it is.

Ross Day:

...optimize an Access
query...

Anonymous:

lol, gee, your like 1 order smarter.

mizhi:

Ooh... trivia questions.  My rule of thumb is to treat those kinds
of limits like engineers treat max load limits on bridges and
elevators: you can hit the limits if you absolutely have to, but it's
better to stay well below them. [:)]

Anonymous:

Isn't funny how pretty much all WTFs are written in Microsoft products?

Anonymous:

This is also the cause of a lot of buffer overflow security bugs. 
Engineers tend to forget that people are not reasonable about obeying
limits.

Anonymous:

Isn't funny how pretty much all WTFs are written in Microsoft products?

Anonymous:

I beg to differ.  I see much WTF-ery in Java, Perl and tools like
Ant pretty regularly.  Programming languages don't kill
applications...$hitty programmers kill applications.  Maybe I'll port "theDaily" to Java.

I like the process of blaming everybody else before somebody finally goes to check what's actually happening...

About the abuse of "distinct" - sometimes businesses grow too fast and their IT teams are overstretched. Tool/table/db ownership is passed around amongst the IT team as some folks stop having the time to keep an eye on every single thing. Documentation never gets written because nobody has the time. Software systems grow and grow and nobody really knows what all affects what. So inevitably you wind up with seemingly innocuous changes or new tools or updates or whatnot that wind up fucking up the erstwhile integrity of your data, and once-unique keys become no longer unique, normalization gets lost, etc.

It's no excuse, but it happens in small IT shops, even when the average IQ is well above 150.

sozin:

...and somehow I doubt whoever coded it thought
about the 255 max # of elems you can have in the IN( ... ) clause

Anonymous:

Isn't funny how pretty much all WTFs are written in Microsoft products?

Oh shoot the tool maker.  That makes sense.

What is it with people who confuse a dislike of Microsoft with an opinion that their technology is poor?  Sloppy thinking.

SQL Server is actually a seriously good product, albeit one written by an arguably evil company.

Anonymous:

Isn't funny how pretty much all WTFs are written in Microsoft products?