Geek asks and answers

Character matching queries in SQL

I am trying to optimize the T-SQL stored procedure that I have. It is for pulling records based on VIN (17-character alphanumeric string); usually people only know a few numbers - for example. the first digit can be "1", "2" or "J"; the second is “H” and the third may be “M” or “G”; etc.

This leads to a rather confusing query, whose suggestion is WHEREsimilar to

WHERE SUBSTRING(VIN,1,1) IN ('J','1','2')
AND SUBSTRING(VIN,2,1) IN ('H')
AND SUBSTRING(VIN,3,1) IN ('M','G')
AND SUBSTRING(VIN,4,1) IN ('E')
AND ... -- and so on for however many digits we need to search on

The table I'm querying is huge (millions of records), so the queries that I run that are of this type WHEREcan take hours to run if more than two digits are found in them, even if I only request the best 3000 records . I feel there must be a way to make this substring character work faster. The watch is completely unacceptable; I would like these requests to be completed in just a few minutes.

I do not have any edit permissions in the database, unfortunately, therefore I cannot add indexes or anything like that; all I can do is change my stored procedure (although I can try to ask the database administrators to change the table).

+3
source share
3 answers

you can use

WHERE VIN LIKE '[J12]H[MG]E%'

, , 3- JH%, 1H% 2H%, .

. , , , , VIN >= '1' and VIN < 'K' LIKE

, ,

WHERE (VIN LIKE 'JH%' OR  VIN LIKE '1H%' OR  VIN LIKE '2H%') 
        AND VIN LIKE '[J12]H[MG]E%'
+3

LIKE

SELECT
  *
FROM Table
WHERE VIN LIKE '[J12]H[MG]E%'

, , "A", [^ A] , :

WHERE VIN LIKE '[J12][^A][MG]E%'

http://msdn.microsoft.com/en-us/library/ms179859.aspx

+2

I like the answers LIKE, but here is another alternative (especially if your input is not always the same).

I would do it as a series of queries on ever smaller temporary tables (yes, I'm in love with temp tables-sue me.)

So, I would do something like

SELECT [Fields]
INTO #tempResultsFirstTwoDigits
FROM VIN
WHERE [Clause]

Then continue down the chain by number until you search for each of the provided characters. So you can do this:

if len(@input) > 2
SELECT [Fields]
INTO #tempResultsThreeDigits
FROM VIN
WHERE Substring(VIN, 3, 1) = Substring(@input, 3, 1)
//NOTE: That where clause might be sped up by initializing a variable at 
//      the beginning of the SP for each character you got.

Else Select * From #tempResultsFirstTwoDigits
GOTO Stop //Where "Stop" just defines the end of the SP to skip any further checks

Again, this LIKEmay be the best answer for you, but I would try to use both approaches and test both of them.

+1
source

More articles:


All Articles