InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 04:24:36 GMT20http://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxThanks Grant,I will try with TOP and ORDER BY and see the difference.Thanks again,RishiThu, 06 Mar 2008 23:46:32 GMTRishi Girdharhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspx[quote][b]Rishi Girdhar (3/6/2008)[/b][hr]Many times we need to use function [b]'MAX'[/b] in 'Select' clause specially for dates. Does anyone has a thought on different approach.For example [quote]select [b]max[/b](a.closedate) as closedate, a.offerid from a inner join b on a.id =b.idinner join c on b.xx = c.xxgroup by a.offerid[/quote]Thanks,Rishi[/quote]We did a series of tests and found, depending on how you do your indexing, TOP actually works a bit better than MAX. You have to include an ORDER BY and get the ASC/DESC right.Thu, 06 Mar 2008 12:29:44 GMTGrant Fritcheyhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxMany times we need to use function [b]'MAX'[/b] in 'Select' clause specially for dates. Does anyone has a thought on different approach.For example [quote]select [b]max[/b](a.closedate) as closedate, a.offerid from a inner join b on a.id =b.idinner join c on b.xx = c.xxgroup by a.offerid[/quote]Thanks,RishiThu, 06 Mar 2008 11:33:24 GMTRishi Girdharhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxGreg,How can we improve the following query?[quote]---------------------------------------------------WHERE filter 1 AND filter 2 AND CONVERT(DATETIME,A.DateValue) BETWEEN (GetDate()-30) AND (GetDate()+30)[/quote] Column DateValue in table A has datatype (varchar/nvarchar) but datetime.Thanks in advance.RishiThu, 06 Mar 2008 11:23:56 GMTRishi Girdharhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxJust another test... please ignore.Thu, 06 Mar 2008 11:00:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxJust a test... please ignore...Thu, 06 Mar 2008 09:12:51 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspx[quote][b]Michaell (3/5/2008)[/b][hr]Would it be the time itself is pretty much an unique value already?So it don't produce a good index on it?[/quote]Absolutely not... the more unique a value it is, the better an index can be made from it.Thu, 06 Mar 2008 04:07:22 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxSorry Michaell,I dont understand the question here...GAJThu, 06 Mar 2008 03:53:03 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxWould it be the time itself is pretty much an unique value already?So it don't produce a good index on it?Wed, 05 Mar 2008 20:01:32 GMTMichaellhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspx[quote][b]GregoryAJackson (3/5/2008)[/b][hr]Rishi,You're very welcome.I never imagined this trivial little article would have generated so much interest and such a vigorous discussion thread.I'm very glad that SQLServerCentral published the article now.cheers,[/quote]Apprently, it wasn't so trivial... Thanks for taking the time to write it, Greg.Wed, 05 Mar 2008 10:47:33 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxRishi -Glad it's been useful. I learned a lot from it for myself...Wed, 05 Mar 2008 10:43:04 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxRishi,You're very welcome.I never imagined this trivial little article would have generated so much interest and such a vigorous discussion thread.I'm very glad that SQLServerCentral published the article now.cheers,Wed, 05 Mar 2008 10:37:46 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxYou bet, Rishi... thank you for the feedback.Wed, 05 Mar 2008 10:21:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxThanks GAJ, Matt and Jeff for the valuable information on functions usage.RishiWed, 05 Mar 2008 00:10:29 GMTRishi Girdharhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxThanks a bunch Matt,I definately willGAJTue, 04 Mar 2008 08:07:06 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxIf you want to check out the thread I was referring to - it's over here:[url=http://www.sqlservercentral.com/Forums/Topic454846-371-2.aspx#bm455633]http://www.sqlservercentral.com/Forums/Topic454846-371-2.aspx#bm455633[/url]Tue, 04 Mar 2008 08:00:04 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxAhh....good catch.I was Assuming that the function used in the SELECT depended on the Row (as in a correlated subQuery).Yes it very much depends on the function, what it's doing, how it's used, etc.I needed to be more clear.GAJTue, 04 Mar 2008 07:59:48 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxCarl Federl and I did some pretty good testing a couple of years back and it very much supports what Matt says... it depends on the function, how it's written, how it's used, where it's used, etc. Carl and I found out that some functions actually operated quicker than the equivalent inline code while others cause huge drags... and everything in between. It also depends on whether or not parallelism is spawned and for what reason.Tue, 04 Mar 2008 07:57:28 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspx[quote][b]GregoryAJackson (3/4/2008)[/b][hr]function use in the SELECT List and or on the JOIN can also cause severe problems (maybe even worse then in the WHERE Clause).by placing a Function in the select, that function will execute 1x per row.We had these at my work also when I first got there. They were causing terrible performance system wide.Removing them all helped immensely. typically you can replace the functions with some other mechanism of retrieving the data needed (Derived tables, correlated subQueries, etc)you really need to pay close attention to the Query Plan and specifically to the SubTree cost of the plan. If the SubTree cost approaches 10.0 (in my experience), you need to consider modifying the query.Cheers,GAJ[/quote]Actually - there was a thread recently on this - and that is by no means true in general. Meaning - there are lots of exceptions. Simple scalar functions, especially deterministic ones, tend to be treated as scalar values that simply replicate on down the line.It's largely a matter of what goes in (inputs), whether the inputs are based on the row, whether the function is in effect deterministic AND is marked correctly as such (as I came to find out).Now - if you were using functions to mask a CSQ - then yes - that can be a terrible drag on your performance, but still - it depends a LOT on what the function is doing.Tue, 04 Mar 2008 07:47:39 GMTMatt Miller (#4)http://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxfunction use in the SELECT List and or on the JOIN can also cause severe problems (maybe even worse then in the WHERE Clause).by placing a Function in the select, that function will execute 1x per row.We had these at my work also when I first got there. They were causing terrible performance system wide.Removing them all helped immensely. typically you can replace the functions with some other mechanism of retrieving the data needed (Derived tables, correlated subQueries, etc)you really need to pay close attention to the Query Plan and specifically to the SubTree cost of the plan. If the SubTree cost approaches 10.0 (in my experience), you need to consider modifying the query.Cheers,GAJTue, 04 Mar 2008 06:40:29 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxIt's certainly no Panacea, but the following article has some insight as to why you can have performance problems with some UDF's in the SELECT list...[url]http://www.sqlservercentral.com/articles/Test+Data/61572/[/url]Tue, 04 Mar 2008 06:29:54 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxGreg, a very nice and useful article.I understand the demerit of using functions in 'Where' clause.Do/does you/anybody have any idea/s on usage of function in 'Select' clause OR in 'JOINS' (User defined tabular functions)on the performance of a query?Thanks in advanceRishiMon, 03 Mar 2008 22:36:29 GMTRishi Girdharhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspx[quote][b]ruben ruvalcaba (2/29/2008)[/b][hr]Hi, First of all thank you, it's a good point to be considered. In fact, I use functions on the WHERE clause very often, specially when filtering dates ... how do you optimize a query like this:SELECT SUM(Amount) FROM myEntries WHERE YEAR(myEntries.myDate) = @Year AND MONTH(myEntries.myDate) <= @MonthAny clue? thank you regards [/quote]Not positive nobody's replied yet... and just doing this off the cuff,...Assuming @year and @month are parms...Instead of @year-- declare @gedate and init as jan 1 of whatever @year is...Instead of @month, declare @ltdate and init as the first of the month AFTER @month...SO let's say you had @year=2008 and @month = 3 Set @gedate=1/1/2008 and @ltdate=4/1/2008. (easily derived from old parms)Your WHERE turns to:...WHERE myentries.mydate >= @gtdate AND myentries.mydate < @ltdate[b][i]EDIT[/i][/b]Ahh... as it turns out, I am third or fourth on the "response" list to this one. One thing I do prefer (and I *thought* I'd benchmarked it to confirm it causes no additional overhead) is to use ...WHERE foo >= @gedate and foo < @ltdaterather than ...WHERE foo BETWEEN (@d1 and @d2) -- or is that "IS BETWEEN"; I never use this construct anyway...I like to use the appropriately named GEdate (greater or equal) and LTdate (lessthan) so I don't get messed up based on the time portion of a (small)datetime. In environments I've worked in, sometimes we will zero out the time portion of a (small)datetime-- (Expiration Dates); in others (Phone Call Taken) we do not. If I write a routine I can use the logic above without worrying about whether a date is 3/3/2008 00:00:00 or 3/3/2008 23:59:59.890Mon, 03 Mar 2008 12:04:02 GMTmstjeanhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxI did some tests on the use of IsNull in the Where clause and found the following:If the column being tested allows nulls, it will use an index or table scan (depending on whether there's an index available, of course), even if there are no nulls in the column. If the column does not allow nulls, SQL server ignores the isnull function and uses an index seek.This made me wonder if a check constraint on a column would have similar behavior.[code]create table dbo.CKTest (Date datetime primary key)goinsert into dbo.CKTest (date)select distinct datefrom common.dbo.dateswhere datepart(year, date) = 2007[/code]Then I ran:[code]select datefrom dbo.CKTestwhere datepart(year, date) = 2007[/code]Got a clustered index scan (of course).Then added:[code]alter table dbo.CKTestadd constraint CK_Date_2007 check(datepart(year, date) = 2007)[/code]Ran the same select. Still a clustered index scan. Not a seek.So there's a slightly different mechanism in place for making SQL ignore IsNull vs DatePart, even when it could know before-hand what the result is going to be.Good to know on the IsNull thing. Would never have thought of checking that, since I'd never run IsNull on a non-nullable column. Not sure what practical use it is, but definitely interesting to know.Mon, 03 Mar 2008 09:06:46 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxThanks to all, As some of you suggested, I been using a computed column for month and year and doing the queries over this columns, but I was looking for an alternative, because I don't know if this approach could be faster than another alternative. I'll try with the solution proposed by SSChasing Mays and SSC Rookie and compare the performance.RegardsFri, 29 Feb 2008 12:37:43 GMTruben ruvalcabahttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxBrandie,Any chance something like this would work for you? [code]If Day(GetDate()) > 26 and Day(GetDate()) <= 31 Set @LastMonthEnd = (Select Max(EndDate) from tblAccountingPeriods where BeginDate < DateAdd(mm,-1,GetDate()) and endDate BETWEEN DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0) AND DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))Else Set @LastMonthEnd = (Select Max(EndDate) from tblAccountingPeriods where BeginDate < DateAdd(mm,-2,GetDate()) and EndDate BETWEEN DATEADD(mm, DATEDIFF(mm,0,getdate())-2, 0) AND DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm,0,getdate())-1, 0)) [/code]Fri, 29 Feb 2008 12:37:05 GMTLuke Lhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxI agree with you Brandie,The Point is, I guess, is to try hard to reduce or limit the use of functions in a WHERE clause when and where you can.If you cant, you cant.same concept as using Temp Tables and cursors.Reduce them as much as possible but sometimes you gotta do what you gotta do.As DBAs, Architects, Engineers, we just need to have a good understanding of the ramifications of our decisions. BUT,.....at some point the rubber hits the road and we gotta make a choice. Sometimes the choices arent ideal.Great discussions here.GAJFri, 29 Feb 2008 12:26:29 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxFYI, I know I need to reverse the BeginDate in the above code with the DateAdd. But it was when I got to the AND part of the WHERE that I realized I would still have a function on both sides.Fri, 29 Feb 2008 12:15:13 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxOkay, but we're still running under the assumption that @StartDate and @EndDate are constants set by the coder/end user. What if they're dynamic?I have a situation where I actually use variables to compare later table dates with, but the variables are based on the current date & time verses values in a table.tblAccountingPeriods has an ID field (int identity), a StartDate and an EndDate, both datetime. We use this table for all our month end stuff, since our month end doesn't necessarily coincide with calendar month end.[code]Declare @LastMonthEnd datetime;If Day(GetDate()) > 26 and Day(GetDate()) <= 31 Set @LastMonthEnd = (Select Max(EndDate) from tblAccountingPeriods where DateAdd(mm,-1,GetDate()) > BeginDate and Month(DateAdd(mm,-1,GetDate())) = Month(EndDate))Else Set @LastMonthEnd = (Select Max(EndDate) from tblAccountingPeriods where DateAdd(mm,-2,GetDate()) > BeginDate and Month(DateAdd(mm,-2,GetDate())) = Month(EndDate));[/code]I use this code to set the variables so that in the SELECT later on, I can say "WHERE FieldX = @LastMonthEnd". The point is, we're talking about ways to improve functionality on the main SELECT statement's WHERE clause, but if you have a situation where your dates are dynamic and you have to get them from a source to create your variables to begin with, you're still going to eventually have to use a function in a WHERE clause at some point. Even if it's not in the actual query, but in the setting of the variable as above.So, unless I'm wrong, there are just some situations where you have to determine the best place to put the function and then bite the bullet and accept the processing cost.Thoughts?Fri, 29 Feb 2008 12:13:05 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspx[quote][b]ruben ruvalcaba (2/29/2008)[/b][hr]Hi, First of all thank you, it's a good point to be considered. In fact, I use functions on the WHERE clause very often, specially when filtering dates ... how do you optimize a query like this:SELECT SUM(Amount) FROM myEntries WHERE YEAR(myEntries.myDate) = @Year AND MONTH(myEntries.myDate) <= @MonthAny clue? thank you regards [/quote]If I were doing this It's look something like this I would imagine, although I'd put it in a stored procedure and just pass in year and month.[code]DECLARE @startDate DATETIME, @endDate DATETIME, @Year CHAR(4), @Month INT--Setting your requested values This would be passed into a sproc etc.SELECT @Year = '2000', @Month = 4 SELECT @startDate = @year --'2000-01-01 00:00:00.000'SELECT @endDate = DATEADD(MONTH,@Month ,DATEADD(ms,-3,@startDate))SELECT SUM(Amount) FROM myEntries WHERE myEntries.myDate between @startDate AND @endDate[/code]Fri, 29 Feb 2008 11:57:25 GMTLuke Lhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxRuben,I'd build a @StartDate and an @EndDate VariableBuild them using your two parameters of Month and YearStart date = 1st of MonthEndDate = last day of monththen use the Between Operator in the WHERE Clause (Or a variation of = as discussed in other posts)GAJFri, 29 Feb 2008 11:20:51 GMTGregoryAJacksonhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspx[quote][b]tymberwyld (2/29/2008)[/b][hr]Ok, I've looked through all the posts here, but I don't see anyone bringing up the SOUNDEX / DIFFERENCE functions. We're currently using this to help "enhance" fuzzy searches in the database. Granted this will slow the performance but that's the trade off and it's not a heavily used procedure (only issued when the user specifically executes it via a button click).So, is there any way to enhance this?[code]ALTER PROCEDURE [Company].[usp_get_Companies]( @Name VarChar(255) = Null)ASSet NoCount OnSet Transaction Isolation Level Read UncommittedDeclare @SoundExName VarChar(255)Select @Name = Replace(Replace(IsNull(@Name, ''), ' ', '%') + '%', '%%', '%'), @SoundExName = Replace(Replace(Replace(Replace(Replace(Replace(@Name, ' ', ''), '.', ''), '[', ''), ']', ''), '/', ''), '\', '')/*NOTE:At this point the @Name should either be just '%' or '%My%Company%'.The latter probably not the most efficient but used to pick up more matches.@SoundExName is trying to optimize the name for better soundex matchingTODO:Determine if there is a way to split the @Name into "words" and seach on the individual words.*/Select C.ID, C.NameFrom [Company].[Companies] CWhere (C.[Name] Like @Name)Or ( (Difference( Replace(Replace(Replace(C.[Name], ' ', ''), '.', ''), '[', ''), @SoundExName ) = 4 ) )Set Transaction Isolation Level Read CommittedSet NoCount Off[/code][/quote]After more consideration, I would actually create an indexed persisted computed column on your customer table...--Would be a computed column on a real table or suchCreate Table Customer( EmpId int not null, CustomerNm varchar(50) not null, SoundExCustomerNm as SoundEx(CustomerNm) Persisted )Create Clustered Index IX_EmpId on #Customer(EmpId)Create Nonclustered Index IX_CustomerNm on #Customer(CustomerNm)Create Nonclustered Index IX_SoundExCustomerNm on #Customer(SoundExCustomerNm)--Then all you have to do is the following (assuming that my hardcoded values are your replaced values...thats a different topic, but i would use a lookup table for that)Select EmpIdFrom #CustomerWhere SoundEx('Smith,') = SoundExCustomerNmUNION--Run QuerySelect EmpIdFrom #CustomerWhere CustomerNm Like 'Smith,%'Fri, 29 Feb 2008 11:14:42 GMTJeremy Giacohttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxMaybe I don't understand completely but why wouldn't you just create a computed column in the table and then just index that? Seems pretty straight forward.Fri, 29 Feb 2008 11:14:28 GMTsmonarchhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxThat's what I'm wondering, Ruben. I asked that in an earlier post and haven't seen a response yet.I can't think of a good way to optimize that one. I might be missing something obvious, though.Fri, 29 Feb 2008 11:11:44 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxHi, First of all thank you, it's a good point to be considered. In fact, I use functions on the WHERE clause very often, specially when filtering dates ... how do you optimize a query like this:SELECT SUM(Amount) FROM myEntries WHERE YEAR(myEntries.myDate) = @Year AND MONTH(myEntries.myDate) <= @MonthAny clue? thank you regards Fri, 29 Feb 2008 11:07:31 GMTruben ruvalcabahttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxIf you want to go with Jeremy's suggestion, you can also throw an index on the Temp table to speed your searching. Though, it might not be worth it.Regardless, check your execution plans carefully every time you make a change so that you optimize for your configuration. Fri, 29 Feb 2008 09:00:41 GMTBrandie Tarvinhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxThis is NOT what I would do, but depending on the size of your company table...its something differentDeclare @SoundExName varchar(50)Set @SoundExName = 'Smith, John'Select a.EmpId, Difference(a.AssociateNm, @SoundExName) DiffInto #TempFrom Associate aSelect EmpId from #Temp where diff = 4Fri, 29 Feb 2008 08:01:55 GMTJeremy Giacohttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxOk, I've looked through all the posts here, but I don't see anyone bringing up the SOUNDEX / DIFFERENCE functions. We're currently using this to help "enhance" fuzzy searches in the database. Granted this will slow the performance but that's the trade off and it's not a heavily used procedure (only issued when the user specifically executes it via a button click).So, is there any way to enhance this?[code]ALTER PROCEDURE [Company].[usp_get_Companies]( @Name VarChar(255) = Null)ASSet NoCount OnSet Transaction Isolation Level Read UncommittedDeclare @SoundExName VarChar(255)Select @Name = Replace(Replace(IsNull(@Name, ''), ' ', '%') + '%', '%%', '%'), @SoundExName = Replace(Replace(Replace(Replace(Replace(Replace(@Name, ' ', ''), '.', ''), '[', ''), ']', ''), '/', ''), '\', '')/*NOTE:At this point the @Name should either be just '%' or '%My%Company%'.The latter probably not the most efficient but used to pick up more matches.@SoundExName is trying to optimize the name for better soundex matchingTODO:Determine if there is a way to split the @Name into "words" and seach on the individual words.*/Select C.ID, C.NameFrom [Company].[Companies] CWhere (C.[Name] Like @Name)Or ( (Difference( Replace(Replace(Replace(C.[Name], ' ', ''), '.', ''), '[', ''), @SoundExName ) = 4 ) )Set Transaction Isolation Level Read CommittedSet NoCount Off[/code]Fri, 29 Feb 2008 05:50:52 GMTtymberwyldhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxYeaup, I'll be one of the first to agree and I don't think anyone will argue with that basic rule... my point is that Jeremy produced some code that's completely sargeable and it defies that basic rule... that's worth an experiment or two :)Fri, 29 Feb 2008 04:28:29 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic461426-143-1.aspxselect * from table_name where created_date>=convert(varchar(11), Created_Date, 106) and created_date<convert(varchar(11), dateadd(d,-1,Created_Date), 106) completly sargeable Fri, 29 Feb 2008 03:41:25 GMTMVDBA