InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 17 Jun 2013 21:41:01 GMT20http://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]Lynn Pettis (9/14/2012)[/b][hr]FYI, the way you editted the quote makes it seem I said something I didn't.[/quote]Sorry, that was a mess. I fixed it up.Fri, 14 Sep 2012 09:50:27 GMTscogebhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]CELKO (9/12/2012)[/b]SELECT A.column1, A.column2, MAX(A.column3) FROM Alpha AS A WHERE NOT EXISTS (SELECT * FROM Beta AS B [b] WHERE A.column1 = B.column1 AND A.column2 = B.column2[/b]) FROM Beta) AND A.column4 = 'Yes'GROUP BY A.column1, A.column2;[/quote]Just to stress something in Celko's input that might otherwise remain unnoticed.@scogeb: Your query would match ("A", "BC") with ("AB","C"). Are you ok with that?Fri, 14 Sep 2012 09:40:20 GMTLaurent Chttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]scogeb (9/14/2012)[/b][hr] ... (Editted)I agree 100% with that last statement. Programs and databases are all so different, along with the systems they run on. Not to mention there is usually 5 different ways to write a SQL statement to get the same results! :hehe:[/quote]FYI, the way you editted the quote makes it seem I said something I didn't.Fri, 14 Sep 2012 08:40:11 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]scogeb (9/14/2012)[/b][hr]...there is usually 5 different ways to write a SQL statement to get the same results! :hehe:[/quote]The same results but with different performance (most of the time). :-DFri, 14 Sep 2012 08:34:41 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]Lynn Pettis (9/13/2012)[/b][hr]And yet in practice, I have found that breaking things down and using temp tables instead of try to do everything in one query can in fact be more efficient and faster.Comes down to developing solutions and testing, testing, and testing again.[/quote]I agree 100% with that last statement. Programs and databases are all so different, along with the systems they run on. Not to mention there is usually 5 different ways to write a SQL statement to get the same results! :hehe:Fri, 14 Sep 2012 08:27:56 GMTscogebhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote] And this coming from Mr. Only-write-standard_SQL, actually extorting the value of using known quirks in the target RDBMS? I'm sorry, but did I just hear that a snowball isn't melting somewhere? [/quote]:-D I am not recommending it and I am not sure if it still works. 1) Don't do it2) If you do it, document it3) If you don't document, comment it.4) "Here a miracle occurs" is not a comment; it is prayer. Thu, 13 Sep 2012 16:51:26 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]CELKO (9/13/2012)[/b][hr][quote]I was under the impression it's best to avoid temp tables if you can though. Is that not accurate?[/quote]It is accurate. We have derived tables and CTEs which the optimizer can use. The temp tables write out to disk which is slower than keeping the data in main storage. But it is a generalization, and ALL generalizations are untrue :-)[/quote]1. Are you sure 100% that "the temp tables write out to disk? Sorry, but that is not true!SQL Server will not write anything (I mean data) out if it can perform required operation on the data in this table in memory.2. "keeping the data in main storage"? What exactly do you mean? So, writing data out to disk where the write is performed into database specific file is faster than writing data in case where it needs to write it to tempdb file? Or do you mean that in case of CTE and, unknown beasts to me, "derived tables", data never will be written to the disk? That is also wrong. SQL Server may have not enough memory in its disposal to handle it, and it will write data to disk, funny enough, most likely, it will use tempdb :-DThu, 13 Sep 2012 16:38:39 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]CELKO (9/13/2012)[/b][hr][quote]I was under the impression it's best to avoid temp tables if you can though. Is that not accurate?[/quote]It is accurate. We have derived tables and CTEs which the optimizer can use. The temp tables write out to disk which is slower than keeping the data in main storage. But it is a generalization, and ALL generalizations are untrue :-)[/quote]And yet in practice, I have found that breaking things down and using temp tables instead of try to do everything in one query can in fact be more efficient and faster.Comes down to developing solutions and testing, testing, and testing again.Thu, 13 Sep 2012 16:32:23 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote]I was under the impression it's best to avoid temp tables if you can though. Is that not accurate?[/quote]It is accurate. We have derived tables and CTEs which the optimizer can use. The temp tables write out to disk which is slower than keeping the data in main storage. But it is a generalization, and ALL generalizations are untrue :-)Thu, 13 Sep 2012 16:28:06 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]CELKO (9/13/2012)[/b][hr][quote] Who knows with the LTRIM/RTRIM. This was set up before my time here. We're trying to clean it up though. Trust me, I know it's a mess. :) Take for instance the left(table2.column2,2), if you're going to use the left 2 spaces for stuff all the time, why not create another column and index it, right? I'm not sure what the original developers were thinking. Probably just wanted to get it done and go home/didn't care.[/quote]After 3 decades of cleaning up crap SQL, I think it is COBOL. Even if the guy never wrote a line of COBOL in his life. In that language each record (not row) is a string of characters processed from left to right. Each program read the file pulls out the fields (not columns) it needs. If you do not need everything in the record, you use FILLER to skip over it. You also left extra space for growth, so that when a field grew, you were ready. Greater for punch cards and mag tape, but not so good for SQL. American Honda had programs that only allowed 10,000 dealerships and going back to expand files was a bitch. [/quote]Well, as a developer that used COBOL I take offense at your statements. Yes, I know all about FILLER (used it to pad "tables" to allow for future growth without have to rebuild them constantly as fields were added), SAME RECORD AREA, and all sorts of other fun stuff.I don't build my SQL databases the way I had to with COBOL because I know better. I will, however, use the terms row and record and column and field interchangeably, because they do relate to each other to me at those levels. Especially since I spent a lot of time and effort transfering data back and forth between the COBOL "databases" (ISAM to be exact) and a SQL Server database.Thu, 13 Sep 2012 16:25:29 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote]...Sorry, but IDENTITY is not a candidate key at all. But why would you actively try to program with proprietary features? [/quote]May be here you could finally suggest a good candidate natural keys for two simple entities: 1. Person - where person may not be US citizen, but from any country, and not marked by angels with world-wide identifier 2. Company - where again, company can be a company from any country and actually not listed on any stock exchange.Please!!![quote]Want to go back and pull out “LEFT OUTER JOIN” to replace it with *= (well, would give you job security)?[/quote]Nope, and I never suggested that. But it has nothing to with IDENTITY [b]feature[/b] of SQL 2008 and pre and new Oracle-like sequence "proprietary" feature in SQL 2012.[quote]Better interview questions would how to convert ISNULL() to COALESCE(), how to use the DATE and TIME data types, how UTC works, do a relational division, use the OVER() subclause, etc. [/quote]I guess there are definitely better topics to discuss on interview than above. It depends on the level of developer required. But why on earth would you want to convert ISNULL to COALESCE? What exactly will you gain? Does it worth of money? Please don't tell me about portability. When and [b]if[/b], system will need to be ported from MS SQL Server to other non-SQL Server product, the above will be of the least concern.Thu, 13 Sep 2012 16:25:17 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]scogeb (9/13/2012)[/b][hr]Well I did some more digging and it looks like the ltrim/rtrim is doing something because I get different results if I take them out. From what I gathered so far table1.column1 has trailing spaces in some cases and it looks like that causes the differences. Although there are quite a few nulls in column1 and column2 in both tables as well. It really is a mess. I question the data validation on this as well as I think the program wasn't written very well and now we have to deal with it.[/quote]You have my "business card" ;-)Thu, 13 Sep 2012 16:20:51 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote] Who knows with the LTRIM/RTRIM. This was set up before my time here. We're trying to clean it up though. Trust me, I know it's a mess. :) Take for instance the left(table2.column2,2), if you're going to use the left 2 spaces for stuff all the time, why not create another column and index it, right? I'm not sure what the original developers were thinking. Probably just wanted to get it done and go home/didn't care.[/quote]After 3 decades of cleaning up crap SQL, I think it is COBOL. Even if the guy never wrote a line of COBOL in his life. In that language each record (not row) is a string of characters processed from left to right. Each program read the file pulls out the fields (not columns) it needs. If you do not need everything in the record, you use FILLER to skip over it. You also left extra space for growth, so that when a field grew, you were ready. Greater for punch cards and mag tape, but not so good for SQL. American Honda had programs that only allowed 10,000 dealerships and going back to expand files was a bitch. Thu, 13 Sep 2012 16:18:01 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]CELKO (9/13/2012)[/b][hr][quote] Looks like he is not working in the "office". He is a TEACHER! I just can only image that his students love him to the bits, especially when they start to behave as their "teacher" and expose his bright ideas when trying to get a job - they must have huge success. [/quote]I do mostly industrial training and sometimes I am an invited lecturer at Universities; my students already have a job. But they are not doing it as well as they need to and [i]they know it[/i]. The last day is to bring in problem queries or DDL, and dissect it as a class. My “bright ideas” have been improving databases for decades now. [quote] If some one would tell me on T-SQL interview that "We, SQL developers, do not use IDENTITY as it's just MS SQL Server proprietary feature and it's completely crap candidate for PK", I would just ask one more question (just for having some fun): "What other crap and proprietary features of SQL Server you don't use?" then thanked candidate for attendance and get him out of office. [/quote]Sorry, but IDENTITY is not a candidate key at all. But why would you actively try to program with proprietary features? Want to go back and pull out “LEFT OUTER JOIN” to replace it with *= (well, would give you job security)?Better interview questions would how to convert ISNULL() to COALESCE(), how to use the DATE and TIME data types, how UTC works, do a relational division, use the OVER() subclause, etc. There was a short piece in the trade press about a job interview which involved debugging a bit of code. The interviewee knocked it down to a fraction of the original size and improved performance. But he did not know this was production code written by the interviewers.[/quote]Did he get the job or did they simply say thank you and send him on his way?Thu, 13 Sep 2012 16:12:56 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote] Looks like he is not working in the "office". He is a TEACHER! I just can only image that his students love him to the bits, especially when they start to behave as their "teacher" and expose his bright ideas when trying to get a job - they must have huge success. [/quote]I do mostly industrial training and sometimes I am an invited lecturer at Universities; my students already have a job. But they are not doing it as well as they need to and [i]they know it[/i]. The last day is to bring in problem queries or DDL, and dissect it as a class. My “bright ideas” have been improving databases for decades now. [quote] If some one would tell me on T-SQL interview that "We, SQL developers, do not use IDENTITY as it's just MS SQL Server proprietary feature and it's completely crap candidate for PK", I would just ask one more question (just for having some fun): "What other crap and proprietary features of SQL Server you don't use?" then thanked candidate for attendance and get him out of office. [/quote]Sorry, but IDENTITY is not a candidate key at all. But why would you actively try to program with proprietary features? Want to go back and pull out “LEFT OUTER JOIN” to replace it with *= (well, would give you job security)?Better interview questions would how to convert ISNULL() to COALESCE(), how to use the DATE and TIME data types, how UTC works, do a relational division, use the OVER() subclause, etc. There was a short piece in the trade press about a job interview which involved debugging a bit of code. The interviewee knocked it down to a fraction of the original size and improved performance. But he did not know this was production code written by the interviewers.Thu, 13 Sep 2012 16:10:03 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]CELKO (9/13/2012)[/b][hr][quote]You see that bit of text on the left, under the username - It says Forum Newbie. [/quote]Which is why I took the time to go into details with him. People seldom make random errors; they usually have the wrong mindset or incomplete information. They carry assumptions and are not even aware of it. [quote] stop shouting at people who are inexperienced and making them feel like idiots. If you did it in the office you would be up on a harassment charge - so consider yourself on one here as well. [/quote]That is a silly analogy! Nobody is getting paid here. We do not have a common business goal. And you are not a manager who can put me on notice. If someone feels that they are being harassed then they can reply directly to me. Do I ever call anyone stupid? I tell them they are doing it wrong. I quote standards and fundamentals that people need to know and use if they want to work in databases. What do you do? What bothers me is that we have a boiler plate about how to post to forums which is ignored. There is a discussion in the “anything but SQL” section on how the posting are getting dumber. I think it is true. [quote] I am bored of reading your rants at badly formed posts from new forum members and the spewing of technical answers from books which bear no relation to the question being asked. [/quote]You never took a class under Ed Dijkstra or Jim McCarthy, did you? :-)[/quote]Actually, you have insinuated that some of the OPs are either stupid, ignorant, or both. You have also advocated violence against those you have called morons for designing shoddy databases.Basically, you have in many cases demonstrated a serious lack of professionalism.If you don't care how you are perceived, that's fine, but this also reflects on this site as well. How about showing us all that you can actually behave in a professional manner. If not, perhaps this site really isn't for you.Thu, 13 Sep 2012 16:06:11 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote]You see that bit of text on the left, under the username - It says Forum Newbie. [/quote]Which is why I took the time to go into details with him. People seldom make random errors; they usually have the wrong mindset or incomplete information. They carry assumptions and are not even aware of it. [quote] stop shouting at people who are inexperienced and making them feel like idiots. If you did it in the office you would be up on a harassment charge - so consider yourself on one here as well. [/quote]That is a silly analogy! Nobody is getting paid here. We do not have a common business goal. And you are not a manager who can put me on notice. If someone feels that they are being harassed then they can reply directly to me. Do I ever call anyone stupid? I tell them they are doing it wrong. I quote standards and fundamentals that people need to know and use if they want to work in databases. What do you do? What bothers me is that we have a boiler plate about how to post to forums which is ignored. There is a discussion in the “anything but SQL” section on how the posting are getting dumber. I think it is true. [quote] I am bored of reading your rants at badly formed posts from new forum members and the spewing of technical answers from books which bear no relation to the question being asked. [/quote]You never took a class under Ed Dijkstra or Jim McCarthy, did you? :-)Thu, 13 Sep 2012 15:48:07 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]CELKO (9/13/2012)[/b][hr][quote] SQL ignores DISTINCT when specified in an IN/NOT IN subquery.[/quote]As an aside, some other SQLs have special optimizations for the IN (<expression list>) predicates. Most of the time, a simple sequential search is just fine because the list is short. The trick in old Oracle was to sort the list by likelihood of matching, but in reverse order because of the way the compiler worked. DB2 would sort the list and do a binary tree search at a certain size. Then at a much larger size, it built a hash table. A trick was to know the critical point, add a dummy value or two and get this to kick in. [/quote]And this coming from Mr. Only-write-standard_SQL, actually extorting the value of using known quirks in the target RDBMS? I'm sorry, but did I just hear that a snowball isn't melting somewhere?Thu, 13 Sep 2012 14:41:22 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote] SQL ignores DISTINCT when specified in an IN/NOT IN subquery.[/quote]As an aside, some other SQLs have special optimizations for the IN (<expression list>) predicates. Most of the time, a simple sequential search is just fine because the list is short. The trick in old Oracle was to sort the list by likelihood of matching, but in reverse order because of the way the compiler worked. DB2 would sort the list and do a binary tree search at a certain size. Then at a much larger size, it built a hash table. A trick was to know the critical point, add a dummy value or two and get this to kick in. Thu, 13 Sep 2012 14:32:40 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]scogeb (9/13/2012)[/b][hr][quote][b]Eugene Elutin (9/13/2012)[/b][hr]Who gave you such impression or where did you take it from?Quite often splitting tasks using temp tables will not only significantly boost performance of complicated processes, but also increase readability and therefore maintainability of code, simplifying testing and debugging.I'm trying to use the following simple rule:Use available technique where it is appropriate and produces best outcome. Don't use "best common practices" where they are irrelevant. :hehe:[/quote]Various websites over time. Something about temp tables using tempdb which requires more overhead on the server. Perhaps things have changed though with newer versions of SQL.[/quote]Browsing internet is not safe nowadays, there are various dangerous websites around where something bad can happen, be careful:hehe:... Yeah, quite few things have changed since SQL6.5As with everything else: you need to know how to use it properly (in regards to tempdb it includes how to configure it properly)Thu, 13 Sep 2012 12:48:26 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]Eugene Elutin (9/13/2012)[/b][hr]Who gave you such impression or where did you take it from?Quite often splitting tasks using temp tables will not only significantly boost performance of complicated processes, but also increase readability and therefore maintainability of code, simplifying testing and debugging.I'm trying to use the following simple rule:Use available technique where it is appropriate and produces best outcome. Don't use "best common practices" where they are irrelevant. :hehe:[/quote]Various websites over time. Something about temp tables using tempdb which requires more overhead on the server. Perhaps things have changed though with newer versions of SQL.Thu, 13 Sep 2012 12:39:38 GMTscogebhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote]...Yep, that works too and I get accurate results in 2 seconds or so. I was under the impression it's best to avoid temp tables if you can though. Is that not accurate?[/quote]Who gave you such impression or where did you take it from?Quite often splitting tasks using temp tables will not only significantly boost performance of complicated processes, but also increase readability and therefore maintainability of code, simplifying testing and debugging.I'm trying to use the following simple rule:Use available technique where it is appropriate and produces best outcome. Don't use "best common practices" where they are irrelevant. :hehe:Thu, 13 Sep 2012 12:28:59 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]scogeb (9/13/2012)[/b][hr][quote][b]Eugene Elutin (9/13/2012)[/b][hr]Can you try this:[code="sql"]SELECT column1,'99' [a],left(column2,2) [b], max(column3) [c], 'Yes' [d], 'Test' [e] ,ltrim(rtrim(column1))+ltrim(rtrim(left(column2,2))) as testINTO #pre1 FROM table1 WHERE column4 = 'Yes'GROUP BY column1, left(column2,2)SELECT DISTINCT ltrim(rtrim(column1))+ltrim(rtrim(left(column2,2))) as test INTO #pre2FROM table2SELECT p1.column1, p1.a, p1.b, p1.c, p1.d, p1.eFROM #pre1 p1WHERE NOT EXISTS(SELECT 1 FROM #pre2 p2 WHERE p2.test = p1.test)[/code]You may also try creating unique clustered index on #pre2 (test)[/quote]Yep, that works too and I get accurate results in 2 seconds or so. I was under the impression it's best to avoid temp tables if you can though. Is that not accurate?[/quote]Sometimes divided and conquer works better than trying to do it all in one.Thu, 13 Sep 2012 12:09:24 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]Eugene Elutin (9/13/2012)[/b][hr]Can you try this:[code="sql"]SELECT column1,'99' [a],left(column2,2) [b], max(column3) [c], 'Yes' [d], 'Test' [e] ,ltrim(rtrim(column1))+ltrim(rtrim(left(column2,2))) as testINTO #pre1 FROM table1 WHERE column4 = 'Yes'GROUP BY column1, left(column2,2)SELECT DISTINCT ltrim(rtrim(column1))+ltrim(rtrim(left(column2,2))) as test INTO #pre2FROM table2SELECT p1.column1, p1.a, p1.b, p1.c, p1.d, p1.eFROM #pre1 p1WHERE NOT EXISTS(SELECT 1 FROM #pre2 p2 WHERE p2.test = p1.test)[/code]You may also try creating unique clustered index on #pre2 (test)[/quote]Yep, that works too and I get accurate results in 2 seconds or so. I was under the impression it's best to avoid temp tables if you can though. Is that not accurate?Thu, 13 Sep 2012 12:04:36 GMTscogebhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspxIn this case: distinct and no where clause (full scan?!?!??!?! almost... Very slow!!)options: NOT EXISTS ... sometimes use: (SELECT TOP 1 ....) Query analyser and execution plan (performance, index - helps a lot). Thanks, CarlosP.S.: Sorry, my english is very, very BAD !!! long time just watching ..... I'll try to write ... NEED TO ASK, PARTICIPATE: NOLOCK, JOINS, MODELING ... I used Oracle for several years. I'm using SQLSERVER 1 YEAR AGO, I'm enjoying it ..Thu, 13 Sep 2012 11:57:01 GMTcarlosaamaralhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[url]http://sqlinthewild.co.za/index.php/2011/01/18/distincting-an-in-subquery/[/url]Thu, 13 Sep 2012 11:26:35 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]GilaMonster (9/13/2012)[/b][hr][quote][b]scogeb (9/13/2012)[/b][hr][quote][b]Jeff Moden (9/12/2012)[/b][hr]Have you tried the NOT IN without the DISTINCT?Also, what's with all the LTRIM/RTRIM stuff? Data should be properly stored without leading spaces and trailing spaces hardly ever matter except when determining the actual number of bytes a strinng may contain.[/quote]Yep, same deal with NOT IN without the DISTINCT, 100% CPU for way too long.[/quote]Yeah, it will be. SQL ignores DISTINCT when specified in an IN/NOT IN subquery.[/quote]Thanks, Gail. I learned something new today. Just to verify, I tried it a couple of different ways. On larger tables, it'll sometimes add a "Sort (Distinct Sort)" to the execution plan if the optimizer thinks it will help but I couldn't get it to add such a sort using DISTINCT on smaller tables.Thu, 13 Sep 2012 10:50:50 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspxCan you try this:[code="sql"]SELECT column1,'99' [a],left(column2,2) [b], max(column3) [c], 'Yes' [d], 'Test' [e] ,ltrim(rtrim(column1))+ltrim(rtrim(left(column2,2))) as testINTO #pre1 FROM table1 WHERE column4 = 'Yes'GROUP BY column1, left(column2,2)SELECT DISTINCT ltrim(rtrim(column1))+ltrim(rtrim(left(column2,2))) as test INTO #pre2FROM table2SELECT p1.column1, p1.a, p1.b, p1.c, p1.d, p1.eFROM #pre1 p1WHERE NOT EXISTS(SELECT 1 FROM #pre2 p2 WHERE p2.test = p1.test)[/code]You may also try creating unique clustered index on #pre2 (test)Thu, 13 Sep 2012 10:19:20 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]scogeb (9/13/2012)[/b][hr][quote][b]Jeff Moden (9/12/2012)[/b][hr]Have you tried the NOT IN without the DISTINCT?Also, what's with all the LTRIM/RTRIM stuff? Data should be properly stored without leading spaces and trailing spaces hardly ever matter except when determining the actual number of bytes a strinng may contain.[/quote]Yep, same deal with NOT IN without the DISTINCT, 100% CPU for way too long.[/quote]Yeah, it will be. SQL ignores DISTINCT when specified in an IN/NOT IN subquery.Thu, 13 Sep 2012 09:31:23 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]Lynn Pettis (9/13/2012)[/b][hr]What would really help us help you is if you would provide the DDL (CREATE TABLE statement) for the tables involved including the indexes currently defined on the tables. Some sample data (as INSERT INTO statements) for the tables, and the actual execution plan for the query (if possible) as a .sqlplan file.Right now, you are getting some help, but basically just shots in the dark.[/quote]Your shots in the dark have helped greatly! I think I'm good on this one. Thanks a ton!Thu, 13 Sep 2012 08:52:33 GMTscogebhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspxWell I did some more digging and it looks like the ltrim/rtrim is doing something because I get different results if I take them out. From what I gathered so far table1.column1 has trailing spaces in some cases and it looks like that causes the differences. Although there are quit a few nulls in column1 and column2 in both tables as well. It really is a mess. I question the data validation on this as well as I think the program wasn't written very well and now we have to deal with it.Thu, 13 Sep 2012 08:47:44 GMTscogebhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]scogeb (9/13/2012)[/b][hr][quote][b]Jeff Moden (9/12/2012)[/b][hr]Have you tried the NOT IN without the DISTINCT?Also, what's with all the LTRIM/RTRIM stuff? Data should be properly stored without leading spaces and trailing spaces hardly ever matter except when determining the actual number of bytes a strinng may contain.[/quote]Yep, same deal with NOT IN without the DISTINCT, 100% CPU for way too long.Who knows with the LTRIM/RTRIM. This was set up before my time here. We're trying to clean it up though. Trust me, I know it's a mess. :) Take for instance the left(table2.column2,2), if you're going to use the left 2 spaces for stuff all the time, why not create another column and index it, right? I'm not sure what the original developers were thinking. Probably just wanted to get it done and go home/didn't care.Thanks for the help![/quote]What would really help us help you is if you would provide the DDL (CREATE TABLE statement) for the tables involved including the indexes currently defined on the tables. Some sample data (as INSERT INTO statements) for the tables, and the actual execution plan for the query (if possible) as a .sqlplan file.Right now, you are getting some help, but basically just shots in the dark.Thu, 13 Sep 2012 08:39:46 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]Jeff Moden (9/12/2012)[/b][hr]Have you tried the NOT IN without the DISTINCT?Also, what's with all the LTRIM/RTRIM stuff? Data should be properly stored without leading spaces and trailing spaces hardly ever matter except when determining the actual number of bytes a strinng may contain.[/quote]Yep, same deal with NOT IN without the DISTINCT, 100% CPU for way too long.Who knows with the LTRIM/RTRIM. This was set up before my time here. We're trying to clean it up though. Trust me, I know it's a mess. :) Take for instance the left(table2.column2,2), if you're going to use the left 2 spaces for stuff all the time, why not create another column and index it, right? I'm not sure what the original developers were thinking. Probably just wanted to get it done and go home/didn't care.Thanks for the help!Thu, 13 Sep 2012 07:52:52 GMTscogebhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote]... If you did it in the office...[/quote]Looks like he is not working in the "office". He is a TEACHER! I just can only image that his students love him to the bits, especially when they start to behave as their "teacher" and expose his bright ideas when trying to get a job - they must have huge success. If some one would tell me on T-SQL interview that "We, SQL developers, do not use IDENTITY as it's just MS SQL Server proprietary feature and it's completely crap candidate for PK", I would just ask one more question (just for having some fun): "What other crap and proprietary features of SQL Server you don't use?" then thanked candidate for attendance and get him out of office.At the same time, may be his students are trained to be trainers? That is fine...Thu, 13 Sep 2012 07:46:48 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx@CelkoYou see that bit of text on the left, under the username - It says Forum Newbie.stop shouting at people who are inexperienced and making them feel like idiots. If you did it in the office you would be up on a harrassment charge - so consider yourself on one here as well. There is a big difference between passing on knowlege and showing off. Your posts indicate the latter and no one is impressed.I am bored of reading your rants at badly formed posts from new forum members and the spewing of technical answers from books which bear no relation to the question being asked.Please moderate your language or I will ask the board moderators to do it for you. I am sure your knowlege will be useful here, but not your attitude. AaronThu, 13 Sep 2012 07:19:29 GMTaaron.reesehttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspxWhere is the DDL? In SQL, each column of a row is a scalar value, representing an attribute. Back in COBOL and assembly language, we had to “cut out” fields from records. Just like you are doing with LEFT(), TRIM() and concatenation. Those functions prevent the optimizer from using indexes or doing much of anything with the data. Using “IN (SELECT DISTINCT ..)” is not needed; that is one thing the optimizer will handle. We do not put constants in a SELECT list without naming their column. And flags are another left-over from COBOL and assembly language. Too bad we have no specs, no DDL and have to guess at everything. Her is my guess, if you clean up the DDL and get the tables in First Normal Form (1NF). SELECT A.column1, A.column2, MAX(A.column3) FROM Alpha AS A WHERE NOT EXISTS (SELECT * FROM Beta AS B WHERE A.column1 = B.column1 AND A.column2 = B.column2) FROM Beta) AND A.column4 = 'Yes'GROUP BY A.column1, A.column2;Wed, 12 Sep 2012 19:42:04 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]scogeb (9/12/2012)[/b][hr]Thanks everyone. I learned a lot here. I went with the NOT EXISTS and it finishes in 2 seconds now. Very nice![/quote]Have you tried the NOT IN without the DISTINCT?Also, what's with all the LTRIM/RTRIM stuff? Data should be properly stored without leading spaces and trailing spaces hardly ever matter except when determining the actual number of bytes a strinng may contain.Wed, 12 Sep 2012 14:29:25 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspxThanks everyone. I learned a lot here. I went with the NOT EXISTS and it finishes in 2 seconds now. Very nice!Wed, 12 Sep 2012 09:28:42 GMTscogebhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspx[quote][b]carlosaamaral (9/11/2012)[/b][hr]SELECT table1.column1, '99', left(table1.column2, 2), max(table1.column3), 'Yes', 'Test'FROM table1 WHERE NOT EXISTS ( SELECT 1 FROM TABLE2 WHERE ltrim(rtrim(table2.column1)) + ltrim(rtrim(left(table2.column2, 2)))= ltrim(rtrim(table1.column1)) + ltrim(rtrim(left(table1.column2, 2))) ) AND table1.column4 = 'Yes'GROUP BY table1.column1, Left(table1.column2, 2)[/quote]I believe the only reason to use concatenation was IN construction.NOT EXISTS does not need that:[code="sql"]SELECT table1.column1, '99', left(table1.column2, 2), max(table1.column3), 'Yes', 'Test'FROM table1 WHERE NOT EXISTS ( SELECT * FROM TABLE2 WHERE table2.column1 = table1.column1 and left(table2.column2, 2) = left(table1.column2, 2) ) AND table1.column4 = 'Yes'GROUP BY table1.column1, Left(table1.column2, 2)[/code]I removed trims as most likely they are not required.RTRIM is not needed for sure, and LTRIM looks like just a sign of a bad habit.Please check your data to make sure my assumption is correct.Tue, 11 Sep 2012 20:35:02 GMTSergiyhttp://www.sqlservercentral.com/Forums/Topic1357636-392-1.aspxIs the ltrim and rtrim necessary? SQL ignores trailing spaces when comparing string columns, if you have leading spaces it's probably better to clean up the data if possible.If you can get rid of the functions (they're often used where not necessary), then use not exists or not in (the left join is slightly slower usually). Also consider that there's a behavioural difference between not in and not exists when nulls are involved (on non-nullable columns they perform identically)p.s. You don't need a DISTINCT in an IN or NOT IN subquery.Tue, 11 Sep 2012 16:29:09 GMTGilaMonster