September 29, 2005 at 1:18 pm
should i use LIKE or LEFT() ? both fields are varchar datatypes. i am thinking that left is the way to go for speed.
SELECT StaffID
FROM StaffInfo
WHERE FirstName LIKE 'Dan%'
AND LastName LIKE 'Smi%'
--OR--
SELECT StaffID
FROM StaffInfo
WHERE LEFT(FirstName, 3) = 'Dan'
AND LEFT(LastName, 3) = 'Smi'
There are ll types of people: those who understand Roman Numerals, and those who do not.
September 29, 2005 at 1:32 pm
I guess just test it.
when I execute a query similar to yours.
With index on column in where clause this still produces an index seek
SELECT StaffID
FROM StaffInfo
WHERE FirstName LIKE 'Dan%'
whereas this query produces a clustered index scan.
SELECT StaffID
FROM StaffInfo
WHERE LEFT(FirstName, 3) = 'Dan'
So I am thinking the left would be a poorer performing solution
September 29, 2005 at 1:39 pm
good point about the indexes...however, in our database (which is SQL Server 2000) these columns do not have indexes. is there a built in way to easier benchmark queries through query analyzer? Thanks!
There are ll types of people: those who understand Roman Numerals, and those who do not.
September 29, 2005 at 1:50 pm
searching 31,253 records, both queries seemed to hover around 13-16 milliseconds on our server. sometimes the LEFT() query would spike up to 186 ms and sometimes the LIKE query would take 0 ms.
here is how i benchmarked the queries, any suggestions would be appreciated!
=================================================
DECLARE @Start AS DATETIME SET @Start = GetDate()
SELECT StaffID
FROM StaffInfo
WHERE 1=1
--AND FirstName LIKE 'D%'
AND LEFT(FirstName,1) = 'D'
DECLARE @End AS DATETIME SET @End = GetDate()
SELECT DateDiff(ms,@Start, @End)
==========================================
There are ll types of people: those who understand Roman Numerals, and those who do not.
September 29, 2005 at 3:13 pm
You can also use query analyzer,
Go to Query --> Show Server trace.
Run the query, when its done executing you can see the duration/cpu used/reads and writes in the trace tab. Duration is in miliseconds.
Take that number divided by 1000 and will give you seconds.
This is all valuable information when analyzing performance.
If there are no indexes, then your queries will perform very poorly when the recordcount grows to hundreds of thousands or millions of rows if your using wildcard.
September 29, 2005 at 3:41 pm
Like is faster since it can use indexes. (with the wildcard at the right)
Left is applied to all records. (no index)
September 30, 2005 at 2:23 am
Unless you have a very good reason DON'T use string functions in SQL Server. You are much better off using LIKE.
If you are going to return a recordset then fight tooth and nail to have the client app do the string manipulation. LEFT, RIGHT, SUBSTRING etc will really hurt performance.
September 30, 2005 at 9:20 am
Try this
1. Query->Show Execution Plan
2. Run both queries in one window
3. it will tell you how much time each query took out of execution of hole Batch,
plus you see what was the leg and where...
Borik
Hope this helps
September 30, 2005 at 9:57 am
You need to be careful with this.
The execution plan will tell you the costs but actual execution time can be very inconsistent even when running on your local box with only you accessing it.
I tried to model the actual execution times and was expecting a bell shaped curve to develop however a more or less uniform distribution resulted.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply