Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Fromper
Starting Member
33 Posts |
 Posted - 2011-07-15 : 13:43:55
|
| We have a varchar(4) field in a view with over 3.5 million records. If there are any numerals in this field, then the first position will definitely be one of them. ie You can't have a letter in the first position and a numeric digit later.We want to search for something in this view, but only on records that have a digit somewhere in that field. Having heard that isnumeric() is slow, I've been using this:where left(fieldname,1) between '0' and '9'But my query takes about 5 hours, compared to under 5 minutes for the same query without that where clause. Does anyone have recommendations for faster ways to search for a numeric digit in the first position (or any position) of this field? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-07-15 : 13:50:22
|
| [code]where fieldname like '[0-9]%'[/code]Do you have an index on fieldname column? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-07-15 : 13:52:15
|
| The problem is with all functions in the where clause like that. So LEFT has the same issue as ISNUMERIC as do all functions. You aren't able to use an index for things like that. Take a look at your execution plan to see what I mean. You may also be able to use LIKE with a %. I think it's something like this Column1 LIKE '[0-9]%'. If that doesn't pan out for you, consider adding a column to your table to indicate what type of data that existing column is so you can search that instead.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-07-15 : 13:57:59
|
| in your view mayb do thiscreate view dbo.vw_my_viewAsSELECT your_fields, CASE WHEN fieldname like '[0-9]%' THEN 1 ELSE 0 END isNumeric left(fieldname, 1) as numeric_value FROM tablesThen use that isNumeric as boolean and boolean might be faster?If you don't have the passion to help people, you have no passion |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-07-15 : 14:15:26
|
| There's also:where fieldname BETWEEN '0' AND '9' + CHAR(255)The LIKE condition will usually be optimized to the same or similar condition, but only if there are no wildcards before the end of the pattern (i.e. '[0-9]%' is good, '%[0-9]%' is not)You could further improve performance with a filtered index that contains the same WHERE condition. |
|
|
|
Fromper
Starting Member
33 Posts |
Posted - 2011-07-15 : 15:22:42
|
| Thanks for the suggestions, all. I'm messing around with it now to see what's fastest for this scenario. I really don't want to have to start adding indexes to the view for something this silly, even though I know that would definitely get the best results. If dumping the functions (left and isnumeric) is enough to speed this thing up to half the 5 hours it took yesterday, then that's probably good enough for now. |
|
|
|
|
|
|
|
|
Fastest way to see if char field contains digits?
because I added too much detail. 
Tara Kizer