February 29, 2008 at 4:28 am
Yeaup, 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 🙂
--Jeff Moden
February 29, 2008 at 5:50 am
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?
ALTER PROCEDURE [Company].[usp_get_Companies]
(
@Name VarChar(255) = Null
)
AS
Set NoCount On
Set Transaction Isolation Level Read Uncommitted
Declare @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 matching
TODO:
Determine if there is a way to split the @Name into "words" and seach on the
individual words.
*/
Select
C.ID,
C.Name
From [Company].[Companies] C
Where
(C.[Name] Like @Name)
Or (
(Difference(
Replace(Replace(Replace(C.[Name], ' ', ''), '.', ''), '[', ''),
@SoundExName
) = 4
)
)
Set Transaction Isolation Level Read Committed
Set NoCount Off
February 29, 2008 at 8:01 am
This is NOT what I would do, but depending on the size of your company table...its something different
Declare @SoundExName varchar(50)
Set @SoundExName = 'Smith, John'
Select a.EmpId, Difference(a.AssociateNm, @SoundExName) Diff
Into #Temp
From Associate a
Select EmpId from #Temp where diff = 4
February 29, 2008 at 9:00 am
If 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.
February 29, 2008 at 11:07 am
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) <= @Month
Any clue? thank you
regards
February 29, 2008 at 11:11 am
That'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.
February 29, 2008 at 11:14 am
Maybe 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.
February 29, 2008 at 11:14 am
tymberwyld (2/29/2008)
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?
ALTER PROCEDURE [Company].[usp_get_Companies]
(
@Name VarChar(255) = Null
)
AS
Set NoCount On
Set Transaction Isolation Level Read Uncommitted
Declare @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 matching
TODO:
Determine if there is a way to split the @Name into "words" and seach on the
individual words.
*/
Select
C.ID,
C.Name
From [Company].[Companies] C
Where
(C.[Name] Like @Name)
Or (
(Difference(
Replace(Replace(Replace(C.[Name], ' ', ''), '.', ''), '[', ''),
@SoundExName
) = 4
)
)
Set Transaction Isolation Level Read Committed
Set NoCount Off
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 such
Create 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 EmpId
From #Customer
Where SoundEx('Smith,') = SoundExCustomerNm
UNION
--Run Query
Select EmpId
From #Customer
Where CustomerNm Like 'Smith,%'
February 29, 2008 at 11:20 am
Ruben,
I'd build a @StartDate and an @EndDate Variable
Build them using your two parameters of Month and Year
Start date = 1st of Month
EndDate = last day of month
then use the Between Operator in the WHERE Clause (Or a variation of = as discussed in other posts)
GAJ
Gregory A Jackson MBA, CSM
February 29, 2008 at 11:57 am
ruben ruvalcaba (2/29/2008)
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) <= @Month
Any clue? thank you
regards
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.
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
February 29, 2008 at 12:13 pm
Okay, 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.
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));
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?
February 29, 2008 at 12:15 pm
FYI, 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.
February 29, 2008 at 12:26 pm
I 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.
GAJ
Gregory A Jackson MBA, CSM
February 29, 2008 at 12:37 pm
Brandie,
Any chance something like this would work for you?
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))
February 29, 2008 at 12:37 pm
Thanks 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.
Regards
Viewing 15 posts - 61 through 75 (of 98 total)
You must be logged in to reply to this topic. Login to reply