Query with multiple wildcard values

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query with multiple wildcard values

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2008-08-04 : 16:23:51

Hi,
I'm trying to see if there is a more efficient way to return a set of data. Unfortunately both the data itself and query params can have wildcard values.

Essentially I need to query a set of Vehicle VINs (Vehicle Identification Numbers). The first 10 digits of a vehicle's VIN defines the year, make, model and other characteristics of a vehicle. Given a set of VIN parameters I need to return a set of data that contains some unique data about that VIN. I have set up a sample to help illustrate the problem (the real solution is more complicated). Unfortunately, I'm not just dealing with a simple query. The data in the database can contains wildcard values ('-') as well as the input data. Essentially I need to return a match for any records that will either directly match or where the table data or query data contains a wildcard param. This is the query we currently have and although it works, I'm trying to see if there is a more efficient way to do it (this table gets pretty large so it is experiencing some performance issues). The real table is properly indexed so I'm not sure if that would help any. Anyway, I'm curious if there is a better way. (Note: the table currenlty has about 1.5 million records)


CREATE TABLE [VehicleTest] (
	[GroupName] [varchar] (20) NOT NULL ,
	[VIN1] [char] (1) NOT NULL ,
	[VIN2] [char] (1) NOT NULL ,
	[VIN3] [char] (1) NOT NULL ,
	[VIN4] [char] (1) NOT NULL ,
	[VIN5] [char] (1) NOT NULL ,
	[VIN6] [char] (1) NOT NULL ,
	[VIN7] [char] (1) NOT NULL ,
	[VIN8] [char] (1) NOT NULL ,
	[VIN9] [char] (1) NOT NULL ,
	[VIN10] [char] (1) NOT NULL ,
	[VehicleSymbol] [char] (2) NOT NULL 
)

INSERT INTO VehicleTest VALUES ('testgroup','A','E','1','3','-','3','4','F','T','9','TE')
INSERT INTO VehicleTest VALUES ('testgroup','A','F','1','3','-','3','4','F','-','9','TE')
INSERT INTO VehicleTest VALUES ('testgroup','A','F','2','-','T','3','4','F','T','9','TE')
INSERT INTO VehicleTest VALUES ('testgroup','A','E','1','3','-','3','4','F','T','9','TE')
INSERT INTO VehicleTest VALUES ('testgroup','A','E','1','-','-','3','R','F','T','9','TE')

DECLARE
	@GroupName varchar(20)
	,@VIN1 char(1)
	,@VIN2 char(1)
	,@VIN3 char(1)
	,@VIN4 char(1)
	,@VIN5 char(1)
	,@VIN6 char(1)
	,@VIN7 char(1)
	,@VIN8 char(1)
	,@VIN9 char(1)
	,@VIN10 char(1)

SET @GroupName = 'testgroup'
SET @VIN1 = 'A'
SET @VIN2 = 'F'
SET @VIN3 = '1'
SET @VIN4 = '3'
SET @VIN5 = '7'
SET @VIN6 = '3'
SET @VIN7 = '4'
SET @VIN8 = 'F'
SET @VIN9 = '-'
SET @VIN10 = '-'

SELECT 
	VehicleSymbol
FROM 
	VehicleTest
WHERE 
	(GroupName = @GroupName) AND
	(VIN1 = @VIN1 OR VIN1 = '-'  OR @VIN1 = '-') AND
	(VIN2 = @VIN2 OR VIN2 = '-' OR @VIN2 = '-') AND
	(VIN3 = @VIN3 OR VIN3 = '-' OR @VIN3 = '-') AND
	(VIN4 = @VIN4 OR VIN4 = '-' OR @VIN4 = '-') AND
	(VIN5 = @VIN5 OR VIN5 = '-' OR @VIN5 = '-') AND
	(VIN6 = @VIN6 OR VIN6 = '-' OR @VIN6 = '-') AND
	(VIN7 = @VIN7 OR VIN7 = '-' OR @VIN7 = '-') AND
	(VIN8 = @VIN8 OR VIN8 = '-' OR @VIN8 = '-') AND
	(VIN9 = @VIN9 OR VIN9 = '-' OR @VIN9 = '-') AND
	(VIN10 = @VIN10 OR VIN10 = '-' OR @VIN10 = '-') 

DROP TABLE VehicleTest

Nic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-04 : 16:30:44

(GroupName = @GroupName) AND
@VIN1 in ('-',VIN1) AND
@VIN2 in ('-',VIN2) AND
@VIN3 in ('-',VIN3) AND
etc.

You might want to consider dynamic sql as well to improve performance, just be careful in how you do it.

nic
Posting Yak Master

209 Posts

Posted - 2008-08-04 : 16:42:48

Hi,
When I run the execution plan using OR or IN, it doesn't seem to make a difference. Note: your statement leaves out the possibility of the table data containing wildcard params. Anyway, both of these statments appear to have no improvement over the original.




SELECT 
	VehicleSymbol
FROM 
	VehicleTest
WHERE 
	(GroupName = @GroupName) AND
	(VIN1 IN ('-',@VIN1) OR @VIN1 = '-') AND
	(VIN2 IN ('-',@VIN2) OR @VIN2 = '-') AND
	(VIN3 IN ('-',@VIN3) OR @VIN3 = '-') AND
	(VIN4 IN ('-',@VIN4) OR @VIN4 = '-') AND
	(VIN5 IN ('-',@VIN5) OR @VIN5 = '-') AND
	(VIN6 IN ('-',@VIN6) OR @VIN6 = '-') AND
	(VIN7 IN ('-',@VIN7) OR @VIN7 = '-') AND
	(VIN8 IN ('-',@VIN8) OR @VIN8 = '-') AND
	(VIN9 IN ('-',@VIN9) OR @VIN9 = '-') AND
	(VIN10 IN ('-',@VIN10) OR @VIN10 = '-')


SELECT 
	VehicleSymbol
FROM 
	VehicleTest
WHERE 
	(GroupName = @GroupName) AND
	(@VIN1 IN ('-',VIN1) OR VIN1 = '-') AND
	(@VIN2 IN ('-',VIN2) OR VIN2 = '-') AND
	(@VIN3 IN ('-',VIN3) OR VIN3 = '-') AND
	(@VIN4 IN ('-',VIN4) OR VIN4 = '-') AND
	(@VIN5 IN ('-',VIN5) OR VIN5 = '-') AND
	(@VIN6 IN ('-',VIN6) OR VIN6 = '-') AND
	(@VIN7 IN ('-',VIN7) OR VIN7 = '-') AND
	(@VIN8 IN ('-',VIN8) OR VIN8 = '-') AND
	(@VIN9 IN ('-',VIN9) OR VIN9 = '-') AND
	(@VIN10 IN ('-',VIN10) OR VIN10 = '-')

Nic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-04 : 16:50:21

Please give me some clarification

The '-' wildcard can be either passed as a parameter or it can be part of the table record, and if either the parameter or the record contains the '-' it is considered a wildcard?

Please confirm this.

nic
Posting Yak Master

209 Posts

Posted - 2008-08-04 : 16:54:43

Yes.

The '-' can be an input param OR in the data itself. I need to return the subset of data where the data matches exactly (ie. 'G' = 'G') OR either value is a wildcard (VIN1 = '-' OR @VIN1 = '-').

Thanks,
Nic

Nic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-04 : 17:52:18

This should work, just make sure to replace '-' values with '_', and change the value of each paramiter to [@myval,-], you can do this fairly easy when at the set MyVin line.

Let me know if there are any issues.



CREATE TABLE [VehicleTest] (
	[GroupName] [varchar] (20) NOT NULL ,
	[VIN1] [char] (1) NOT NULL ,
	[VIN2] [char] (1) NOT NULL ,
	[VIN3] [char] (1) NOT NULL ,
	[VIN4] [char] (1) NOT NULL ,
	[VIN5] [char] (1) NOT NULL ,
	[VIN6] [char] (1) NOT NULL ,
	[VIN7] [char] (1) NOT NULL ,
	[VIN8] [char] (1) NOT NULL ,
	[VIN9] [char] (1) NOT NULL ,
	[VIN10] [char] (1) NOT NULL ,
	[VehicleSymbol] [char] (2) NOT NULL 
)

INSERT INTO VehicleTest VALUES ('testgroup','A','F','1','3','-','3','4','F','T','9','TE')
INSERT INTO VehicleTest VALUES ('testgroup','A','F','1','3','-','3','4','F','-','9','TE')
INSERT INTO VehicleTest VALUES ('testgroup','A','F','2','-','T','3','4','F','T','9','TE')
INSERT INTO VehicleTest VALUES ('testgroup','A','E','1','3','-','3','4','F','T','9','TE')
INSERT INTO VehicleTest VALUES ('testgroup','A','E','1','-','-','3','R','F','T','9','TE')

DECLARE
	@GroupName varchar(20)
	,@VIN1 varchar(5)
	,@VIN2 varchar(5)
	,@VIN3 varchar(5)
	,@VIN4 varchar(5)
	,@VIN5 varchar(5)
	,@VIN6 varchar(5)
	,@VIN7 varchar(5)
	,@VIN8 varchar(5)
	,@VIN9 varchar(5)
	,@VIN10 varchar(5)

SET @GroupName = 'testgroup'
SET @VIN1 = '[A,-]'
SET @VIN2 = '[F,-]'
SET @VIN3 = '[1,-]'
SET @VIN4 = '[3,-]'
SET @VIN5 = '[7,-]'
SET @VIN6 = '[3,-]'
SET @VIN7 = '[4,-]'
SET @VIN8 = '[F,-]'
SET @VIN9 = '_'  -- '-' --Change this to '_'
SET @VIN10 = '_' -- '-'

Declare @MyVin varchar(60)
set @MyVin = @Vin1+@Vin2+@Vin3+@Vin4+@Vin5+@Vin6+@Vin7+@Vin8+@Vin9+@Vin10
SELECT ,*
	VehicleSymbol
FROM 
	VehicleTest
WHERE 
	(GroupName = @GroupName) 
AND
Vin1+Vin2+Vin3+Vin4+Vin5+Vin6+Vin7+Vin8+Vin9+Vin10 like @Myvin

DROP TABLE VehicleTest

nic
Posting Yak Master

209 Posts

Posted - 2008-08-07 : 11:33:04

Hi Vinnie,
I tried your latest approach (very interesting, I had never thought to do that). unfortunately using the "[param,-]" etc approach performs slower than the orginal (this or this or this). The execution plan indicates it took about twice as long. Anyway, I will try to focus on other ways to improve the performance of this query.

Anyway, thanks for your help.

Nic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-08-07 : 11:50:59

No problem, Sometimes trying a new approach works out, and sometimes you find out your original was right all along.

Hope you find something that works for you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources