May 11, 2008 at 5:01 pm
I have a table with 6 fields, and I will have all 6 parameters passed in - is there any way to write a query to give me rows based on matching ANY combination of 4 fields out of the 6 parameters passed in ? This is driving me crazy... short of doing an OR statement for all the different combinations - I have no idea how to do this....
May 11, 2008 at 5:15 pm
ahh..criteria math. Try this
WHERE
case when criterion1 then 1 else 0 end +
case when criterion2 then 1 else 0 end +
case when criterion3 then 1 else 0 end +
case when criterion4 then 1 else 0 end +
case when criterion5 then 1 else 0 end +
case when criterion6 then 1 else 0 end =4 --when you want EXACTLY 4, otherwise >3
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 11, 2008 at 9:40 pm
Wow - genius solution and it works ! - But unfortunately, its not using my indexes anymore, and I have over 1 million rows - and growing 🙁
May 11, 2008 at 10:17 pm
MR - why don't you post what you came up with, and let's see if we can't make it behave somehow....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 12, 2008 at 4:06 am
try:
create procedure dbo.myTable_Search
@f1 int = null, -- or whatever type
@f2 int = null,
@f3 int = null,
@f4 int = null,
@f5 int = null,
@f6 int = null
as
select
f1,f2,f3,f4,f5,f6
from
myTable t
where
(
@f1 is null
or
f1 = @f1
)
and
(
@f2 is null
or
f2 = @f2
)
and
(
@f3 is null
or
f3 = @f3
)
and
(
@f4 is null
or
f4 = @f4
)
and
(
@f5 is null
or
f5 = @f5
)
and
(
@f6 is null
or
f6 = @f6
)
go
/Richard
May 12, 2008 at 5:51 am
The mutiple OR clauses will not always get you to hit indexes, so that is not a good idea.
Kimberly Trip recommends to perform UNION ALL instead of OR statements.
Otherwise you might want to consider to use a dynamic SQL in the stored proc using sp_executesql and using parameters for the sp_executesql. This is not nice to maintain, but I have seen great performance from it. Look in BOL for more details try ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/a8d68d72-0f4d-4ecb-ae86-1235b962f646.htm as where you look in BOL.
May 12, 2008 at 8:26 am
Richard, unfortunately, when the parameters don't match, they will not be null - they might have other values in it, so the conditions inside where (@f1 is null or @f1 = f1) will fail....:(
I tried dynamic queries, but I run into the same problem - because in the end, my query is pretty simple.... its just that I have a lot of rows and I need to hit the indexes and be very fast....
This is what I have so far -
SELECT @nodeMachineType = nodeMachineType,
@nodePKID = NodePKID,
@biosVar = Bios,
@computerNameVar = ComputerName,
@diskVolumeVar = DiskVolume,
@guidVar = Guid,
@macAddressVar = MacAddress,
@motherboardVar = motherboard
FROM Nodes_Active
WHERE
case when Bios = @bios then 1 else 0 end +
case when ComputerName = @computerName then 1 else 0 end +
case when DiskVolume = @diskVolume then 1 else 0 end +
case when guid = @GUID then 1 else 0 end +
case when macAddress = @macAddress then 1 else 0 end +
case when MotherBoard = @motherboard then 1 else 0 end >= 4
I have tried with an index on all the fields, and each of the fields separately - but there is s clutered index on the dateEntered field, and it ALWAYS looks at that. I tried removing that index, and at that point it just does a table scan.
May 13, 2008 at 11:12 am
If you're in a stored procedure I don't see why you wouldn't create a temp table and then make 7 passes at inserts into it using specific criteria each time that can be indexed.
Todd Fifield
May 13, 2008 at 1:25 pm
If you have a primary key on the table, I'd recommend creating a temporary table and doing something like this:
create table #T (
ID int)
insert into #t (ID)
select ID
from MyTable
where Col1 = @Input1
union all
select ID
from MyTable
where Col2 = @Input2
... and so on through all 6 inputs
select ID
from #t
group by ID
having count(ID) > 3
The last select can be joined to your real table for other columns. You might want indexing on the temporary table, you'll have to test that for speed.
Try that, see if it does what you need with the speed you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 14, 2008 at 11:44 pm
I did it ! I saw the first suggestion as the best one and the only reason I was having issues with that is because of the index.... so I looked it up and BOL says that case statement does not use indexes at all... so I tried to trick by adding a where clause -
SELECT @nodeMachineType = nodeMachineType,
@nodePKID = NodePKID,
@biosVar = Bios,
@computerNameVar = ComputerName,
@diskVolumeVar = DiskVolume,
@guidVar = Guid,
@macAddressVar = MacAddress,
@motherboardVar = motherboard
FROM Nodes_Active
WHERE
(Bios = @bios OR ComputerName = @computerName OR DiskVolume = @diskVolume OR guid = @GUID OR macAddress = @macAddress OR MotherBoard = @motherboard )
AND
case when Bios = @bios then 1 else 0 end +
case when ComputerName = @computerName then 1 else 0 end +
case when DiskVolume = @diskVolume then 1 else 0 end +
case when guid = @GUID then 1 else 0 end +
case when macAddress = @macAddress then 1 else 0 end +
case when MotherBoard = @motherboard then 1 else 0 end >= 4
And it works ! It uses the indexes on each of the fields - it searches a table of 2 million rows under 500ms....
I did think about temp tables, but this stored proc gets hit about 10,000 per minute, so it needed to be REALLY fast..
Thanks everybody for you help...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply