May 11, 2011 at 2:00 am
terrykzncs (5/10/2011)
That was my example to show that the IN statement would be quicker. No, there isnt an index on the called number, and the table is huge, but I am only allowed to run SELECT statements and create reports; altering the database is left to the senior staff. The script that my colleague had, would sometimes run for over 10mins. 😉
Ouch! In that case kudos to you for making any improvements at all - performance tuning is not easy if you can't even create indexes. I take it the senior staff have ruled out creating any to help you with your reports?
May 12, 2011 at 10:09 pm
I believe we don't have just the basic indexes because in our OLTP environment, there are just too many inserts into this table and the reports are run only by a few people.
May 13, 2011 at 3:32 am
Duncan Pryde (5/10/2011)
terrykzncs (5/10/2011)
Unfortunately it is a big table. If I use = and put the exact wording will that be faster? I will have to put about 7 or 8 more conditions in the query so will that cause it to take longer? Would I be better of sticking with unsing LIKE?
I have a query that searches for calls for a certain phone number that takes about 5 minutes to run if I use LIKE '%123456789'. If I edit the numbers first and search using a list such as IN ('0044123456789', '0944123456789', '2144123456789', '123456789') it runs for about 1min30 even if I have to search for 50 different numbers.
I would definitely recommend using the exact value if you have it.
How many records are in your table? If it's using an index I'd expect it to come back quicker than that if phone number is the only thing in your search criteria. I have a 60 million record table where using a LIKE query with a leading % would take hours (or at least tens of minutes), but with an IN clause and actual values takes less than a second. Of course there are all sorts of factors in play, but I'd expect a decent query to give speed increases of several orders of magnitude over the leading wildcard version.
LIKE '%SomethingOrOther' will generally perform badly because it's not SARGable. One fairly well known trick is to reverse the string, putting the wildcard to the RHS to create a SARGable expression. It requires a persisted computed column ...
ALTER TABLE MyHugeTable ADD enohp AS REVERSE(Phone) PERSISTED
...which is then indexed:
CREATE NONCLUSTERED INDEX [idx_enohp] ON [dbo].[MyHugeTable]
([enohp] ASC) INCLUDE ([Phone])
The query looks like this...
DECLARE @Phone VARCHAR(15)
SET @Phone = '%13405'
SELECT Phone
FROM MyHugeTable
WHERE enohp LIKE REVERSE(@Phone)
Here's the results of a couple of tests against a 500,000 row sample table:
-- no index: Table 'MyHugeTable'. Scan count 1, logical reads 2110, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- with index on 'phone': Table 'MyHugeTable'. Scan count 1, logical reads 1481, physical reads 0, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- with computed column 'enohp', indexed:
-- Table 'MyHugeTable'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Cheers
ChrisM
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 13, 2011 at 5:59 am
Good idea from Chris. I've not used the reverse technique before, so it looks interesting. What we have done on one large database is to create a indexed computed column for the last 8 characters of a particular character column because that's what some of the users needed to search on. So indexed computed columns can be used in several different ways to accomplish the task.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply