InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 19:31:36 GMT20http://www.sqlservercentral.com/Forums/Topic1363492-391-1.aspx[quote][b]GilaMonster (9/26/2012)[/b][hr]Yes, those two will be identical.While the string manipulation's in the select, that derived column is used in the where, so it's absolutely the same as having the string manipulation in the where.Both of these, for example, are not SARGable and will simplify to the same query structure.[code="sql"]SELECT <columns> FROM SomeTableWHERE Substring(SomeColumn,2,3) = 'abc'SELECT * FROM ( SELECT <columns>, Substring(SomeColumn,2,3) as TrimmedString FROM SomeTable) subWHERE sub.TrimmedString = 'abc'[/code][/quote]Thanks for that explanation.Just thinking out loud...could there ever be a benefit to having a calculated column that converts any nulls to something non-null for queries/indexes? I suppose the best solution is to not allow nulls but I import lots of data where I'm stuck with what I get. Would it be better to convert the data to a blank or some other value during insert/update even if the value is truly an unknown? What other options are there for avoiding an IsNull conversion or a 'WHERE col = val [or/and] col [is/is not] null"...or is that even something to avoid? Wed, 26 Sep 2012 08:39:56 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1363492-391-1.aspxYes, those two will be identical.While the string manipulation's in the select, that derived column is used in the where, so it's absolutely the same as having the string manipulation in the where.Both of these, for example, are not SARGable and will simplify to the same query structure.[code="sql"]SELECT <columns> FROM SomeTableWHERE Substring(SomeColumn,2,3) = 'abc'SELECT * FROM ( SELECT <columns>, Substring(SomeColumn,2,3) as TrimmedString FROM SomeTable) subWHERE sub.TrimmedString = 'abc'[/code]Wed, 26 Sep 2012 01:52:38 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1363492-391-1.aspx[quote][b]Steven Willis (9/25/2012)[/b][hr]Here's another option that moves the string manipulation and identification of NULLs to the SELECT statement instead of using it in a JOIN or WHERE clause where the 'SARG-ability' of the statement may be an issue. It sets blanks and NULLs to some value (in this example it's '0') and then the Where clause can exclude that value. This option would need to be tested against other options of course.[code="sql"]SELECT [Desc] ,[Num] ,[Batch] ,[CodeId] ,[Type] ,[Id]FROM ( SELECT DISTINCT CD.[Desc] AS [Desc] ,SD.[Num] AS [Num] ,SD.[Batch] AS [Batch] ,CD.[CodeId] AS [CodeId] ,SD.[Type] AS [Type] ,CH.[Id] AS [Id] ,ISNULL(NULLIF(RTRIM(LTRIM(SD.[Code])),''),'0') AS [Code] FROM Table1 SD JOIN Table2 CH ON CH.Num = SD.Num AND CH.Batch = @Batch AND CH.Flag = 0 AND SD.Batch = @Batch AND SD.Flag = 0 JOIN Table3 M ON M.Code = SD.Code AND (Map = 1 OR Map = @Map) AND CH.Date1 BETWEEN M.Date2 AND M.Date3 JOIN Table4 CD ON M.CodeId = CD.CodeId ) AS ResultWHERE [Code] <> '0'[/code][/quote]There's nothing wrong with your reasoning, Steven - it looks like a winner. However, I strongly suspect that the execution plan of your query would be identical to this:[code="sql"] SELECT DISTINCT CD.[Desc] AS [Desc] ,SD.[Num] AS [Num] ,SD.[Batch] AS [Batch] ,CD.[CodeId] AS [CodeId] ,SD.[Type] AS [Type] ,CH.[Id] AS [Id] ,ISNULL(NULLIF(RTRIM(LTRIM(SD.[Code])),''),'0') AS [Code] FROM Table1 SD JOIN Table2 CH ON CH.Num = SD.Num AND CH.Batch = @Batch AND CH.Flag = 0 AND SD.Batch = @Batch AND SD.Flag = 0 JOIN Table3 M ON M.Code = SD.Code AND (Map = 1 OR Map = @Map) AND CH.Date1 BETWEEN M.Date2 AND M.Date3 JOIN Table4 CD ON M.CodeId = CD.CodeIdWHERE ISNULL(NULLIF(RTRIM(LTRIM(SD.[Code])),''),'0') <> '0'[/code]Wed, 26 Sep 2012 01:43:52 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1363492-391-1.aspx[quote][b]GilaMonster (9/24/2012)[/b][hr][quote][b]laurie-789651 (9/24/2012)[/b][hr]2. You could consider using [code="sql"]FROM Table1 SD WITH (NOLOCK) JOIN Table2 WITH (NOLOCK) etc.[/code]as long as the table isn't being updated - this saves time as no read locks are issued.[/quote]And incorrect results are possible, not just dirty reads, duplicate or missing rows. If there are no changes being made, there's little gain from nolock, the overhead of taking locks is not that high, and it introduces the potential for incorrect results when changes are being made.See - [url]http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx[/url][/quote]A quick little demo to show incorrect results from NOLOCK, even when the data you are reading is static.http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/Tue, 25 Sep 2012 14:53:59 GMTtim_harkinhttp://www.sqlservercentral.com/Forums/Topic1363492-391-1.aspxHere's another option that moves the string manipulation and identification of NULLs to the SELECT statement instead of using it in a JOIN or WHERE clause where the 'SARG-ability' of the statement may be an issue. It sets blanks and NULLs to some value (in this example it's '0') and then the Where clause can exclude that value. This option would need to be tested against other options of course.[code="sql"]SELECT [Desc] ,[Num] ,[Batch] ,[CodeId] ,[Type] ,[Id]FROM ( SELECT DISTINCT CD.[Desc] AS [Desc] ,SD.[Num] AS [Num] ,SD.[Batch] AS [Batch] ,CD.[CodeId] AS [CodeId] ,SD.[Type] AS [Type] ,CH.[Id] AS [Id] ,ISNULL(NULLIF(RTRIM(LTRIM(SD.[Code])),''),'0') AS [Code] FROM Table1 SD JOIN Table2 CH ON CH.Num = SD.Num AND CH.Batch = @Batch AND CH.Flag = 0 AND SD.Batch = @Batch AND SD.Flag = 0 JOIN Table3 M ON M.Code = SD.Code AND (Map = 1 OR Map = @Map) AND CH.Date1 BETWEEN M.Date2 AND M.Date3 JOIN Table4 CD ON M.CodeId = CD.CodeId ) AS ResultWHERE [Code] <> '0'[/code]Tue, 25 Sep 2012 09:58:49 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1363492-391-1.aspxThank you all for your help!Mon, 24 Sep 2012 14:25:27 GMTnewbie2http://www.sqlservercentral.com/Forums/Topic1363492-391-1.aspxAs previously stated this is high potential of the issue.[code]WHERE SD.Code IS NOT NULL AND RTRIM(LTRIM(SD.Code ))<>''[/code]That renders your query nonSARGable. You could change that be simply:[code]WHERE SD.Code > ''[/code]This will still find any value that is not '' and NULL will already be excluded.--EDIT--Had a desk meeting while posting and got pulled away. Seems that Gail already posted much the same as I did. :-PMon, 24 Sep 2012 09:01:08 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1363492-391-1.aspx[quote][b]newbie2 (9/24/2012)[/b][hr] AND RTRIM(LTRIM(SD.Code ))<>'';[/quote]The RTRIM and LTRIM are unnecessary here. SQL ignores trailing spaces when checking string equality, hence '' = ' ', true no matter how many spaces you have.That predicate can be reduced to AND SD.Code != ''. That != will also eliminate nulls, so you can remove the SD.Code IS NOT NULL as well.Neither of the nonclustered indexes that you've created look optimal. Rather considerTable1: Index key (Code, Batch, Flag, Num) Include (Type)Table2: Index key (Flag, Batch, Num, Date1) Include (ID)Mon, 24 Sep 2012 08:42:01 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1363492-391-1.aspxHere is a quick example of how moving the filter from the join to the WHERE clause can have an effect on the result of your queries (if you're using OUTER JOINS):Although there is a filter in the first query on the Forename, SQL still includes the other records. This is because SQL server does the inner join internally and applies the filter then adds all missing rows from the left table.Hope this makes sense.[code="sql"]CREATE TABLE #Employee (ID INT IDENTITY(1, 1), Forename VARCHAR(20))INSERT INTO #Employee(Forename)SELECT 'Abu Dina' UNION ALL SELECT 'SQL4n00bs' UNION ALL SELECT 'Test' UNION ALL SELECT '1337'CREATE TABLE #Location (ID INT IDENTITY(1, 1), Employee_ID INT, Location VARCHAR(50))INSERT INTO #Location(Employee_ID, Location)SELECT 1, 'UK' UNION ALL SELECT 2, 'Manchester'SELECT a.* , b.*FROM #Employee AS aLEFT JOIN #Location AS b ON a.id = b.employee_id and a.forename = 'Abu Dina' SELECT a.* , b.*FROM #Employee as aLEFT JOIN #Location as b ON a.id = b.employee_id WHERE a.forename = 'Abu Dina' DROP TABLE #Employeea DROP TABLE #Location[/code]Mon, 24 Sep 2012 08:39:56 GMTAbu Dinahttp://www.sqlservercentral.com/Forums/Topic1363492-391-1.aspxYou'll have better performance if you change the following statement[code="sql"]WHERE SD.Code IS NOT NULL AND RTRIM(LTRIM(SD.Code ))<>''[/code]with[code="sql"]WHERE SD.Code <> ''[/code]The results are the same and you can test them if you want.Here's a script.[code="sql"]DECLARE @Table table( mystring char(15))INSERT @Table VALUES( ''),( ' '),( ' '), ( 'a'),( ' b'),( 'c '), ( ' '), (NULL)SELECT * FROM @TableWHERE mystring <> ''[/code]Mon, 24 Sep 2012 08:26:15 GMTLuis Cazareshttp://www.sqlservercentral.com/Forums/Topic1363492-391-1.aspxSo your saying that this code still produces the same result in with the same efficiency? (I took out another join and moved it to the where clause)INSERT INTO Table5 SELECT DISTINCT CD.Desc AS Desc, SD.Num AS Num, SD.Batch AS Batch, CD.CodeId AS CodeId, SD.Type AS Type, CH.Id AS Id,FROM Table1 SDJOIN Table2 CH ON CH.Num=SD.Num JOIN Table3 M ON M.Code=SD.Code AND (Map=1 OR Map=@Map) AND CH.Date1 BETWEEN M.Date2 AND M.Date3 JOIN Table4 CD ON M.CodeId=CD.CodeIdWHERE SD.Code IS NOT NULL AND RTRIM(LTRIM(SD.Code ))<>'' AND CH.Batch = @BatchAND CH.Flag= 0AND SD.Batch = @Batch AND SD.Flag= 0Mon, 24 Sep 2012 08:16:40 GMTnewbie2http://www.sqlservercentral.com/Forums/Topic1363492-391-1.aspx[quote][b]laurie-789651 (9/24/2012)[/b][hr]2. You could consider using [code="sql"]FROM Table1 SD WITH (NOLOCK) JOIN Table2 WITH (NOLOCK) etc.[/code]as long as the table isn't being updated - this saves time as no read locks are issued.[/quote]And incorrect results are possible, not just dirty reads, duplicate or missing rows. If there are no changes being made, there's little gain from nolock, the overhead of taking locks is not that high, and it introduces the potential for incorrect results when changes are being made.See - [url]http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx[/url]Mon, 24 Sep 2012 08:06:34 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1363492-391-1.aspxFor inner joins there is no difference whatsoever. For outer joins, moving the filter from where to join changes the logic of the query. Hence it's not a matter of performance, it's a matter of which gives you the correct results.Mon, 24 Sep 2012 08:03:44 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1363492-391-1.aspx1. Using[code="sql"]RTRIM(LTRIM(SD.Code ))<>''[/code]will be very slow - if you've got an index on SD.Code it probably won't be used. Can you tidy the data in the table so you don't need RTRIM(LTRIM() & add an index?2. You could consider using [code="sql"]FROM Table1 SD WITH (NOLOCK) JOIN Table2 WITH (NOLOCK) etc.[/code]as long as the table isn't being updated - this saves time as no read locks are issued.3. Not sure about the pros & cons of including the filters in the WHERE clause...Mon, 24 Sep 2012 08:02:33 GMTlaurie-789651http://www.sqlservercentral.com/Forums/Topic1363492-391-1.aspxI'm new to the TSQL world (coming from visual basic) and am working with large tables (125+ million rows) and having perfomance issues. This sp takes over an hour to run. Is there a better way to optimize this code? Table2 has 40,525,850 rows and Table1 ends up with 125,350,605 rows.I'm creating two indexes within the code:CREATE NONCLUSTERED INDEX IDX_Table2ON Table2 (Batch,Flag,Num,Date1 )INCLUDE (Id); CREATE NONCLUSTERED INDEX IDX_Table1ON Table1 (Batch,Flag,Num);INSERT INTO Table5 SELECT DISTINCT CD.Desc AS Desc, SD.Num AS Num, SD.Batch AS Batch, CD.CodeId AS CodeId, SD.Type AS Type, CH.Id AS Id,FROM Table1 SDJOIN Table2 CH ON CH.Num=SD.Num AND CH.Batch= @Batch AND CH.Flag= 0 AND SD.Batch = @Batch AND SD.Flag= 0JOIN Table3 M ON M.Code=SD.Code AND (Map=1 OR Map=@Map) AND CH.Date1 BETWEEN M.Date2 AND M.Date3 JOIN Table4 CD ON M.CodeId=CD.CodeIdWHERE SD.Code IS NOT NULL AND RTRIM(LTRIM(SD.Code ))<>'';Also, will the following code produce the same result? Or will moving the filters to the where clause make the join take longer? How does SQL process the code?INSERT INTO Table5 SELECT DISTINCT CD.Desc AS Desc, SD.Num AS Num, SD.Batch AS Batch, CD.CodeId AS CodeId, SD.Type AS Type, CH.Id AS Id,FROM Table1 SDJOIN Table2 CH ON CH.Num=SD.Num AND CH.Batch= SD.BatchJOIN Table3 M ON M.Code=SD.Code AND (Map=1 OR Map=@Map) AND CH.Date1 BETWEEN M.Date2 AND M.Date3 JOIN Table4 CD ON M.CodeId=CD.CodeIdWHERE SD.Code IS NOT NULL AND RTRIM(LTRIM(SD.Code ))<>'' AND CH.Flag= 0 AND SD.Batch = @Batch AND SD.Flag= 0 Mon, 24 Sep 2012 07:52:52 GMTnewbie2