August 8, 2010 at 12:43 pm
I have a problem to solve revolving around a concept I'll call "rules". Think of rules
like Outlook mail rules: a sample rule might be "if an incoming message contains the subject "Hi Joe", then put the message in the "Personal" folder."
I have a table of rules as well as an interface that allows administrators the ability to add and edit these rules. For the sake of my issue, let's say the table contains the following fields and data:
ruleIDtablenamecolumnnameexpressionvalue
1memberslastnameequalsSmith
2membersyearAddedequals2010
3memberscountyequalsSmithville
I have a procedure who's job is to run the rules over my member information and tell me which members satisfy which rules. (I do something with this data, of course, but that part isn't important here.)
The problem I have is that running the rules one by one in a loop takes too long.
Lets say we have 300 rules. I loop over each rule, create a dynamic sql statement based on the rule record that basically says:
select memberid
from members
where lastname = 'Smith'
(as in the case of rule 1 above) and then execute that dynamic sql statement, adding the result to a holding table.
I'm looking for a smarter or more clever way to process these rules.
I've tried one other approach to get away from the looping--unpivot. This solution basically went like this:
Knowing I needed to process rules 1-3 above, unpivot my members table so the columnname was a field:
select memberid, pvt.dbfield, pvt.dbvalue
from members
UNPIVOT (dbvalue For dbfield in ([lastname],[yearAdded],[county])) as pvt
And then I can join my rules table against my unpivoted members table. This approach has its own issues, of course, like the unpivot columns must be the same datatype and length, etc.
My example above is quite simplistic -- in reality, our rules can have multiple conditions that must be satisfied, but I'm really just focused on these simple rules now and can expand the solution into something bigger as needed.
Anyone want to take on a challenge and offer some good options to solving this problem?
August 9, 2010 at 1:03 pm
Have you considered constructing a single dynamic query based upon the rules table? You may have to construct some parsing tables and use some table variables, but based upon the relatively small number of rules should execute very quickly.
August 9, 2010 at 4:00 pm
Thanks for the stab at it.
Right now, our admins have about 2000 rules defined (spanning all our clients) so one query to do it all doesn't seem optimal.
So far we have tried two approaches:
1) Loop over each rule (approx 2000 iterations), plug our rule syntax into the where condition of a dynamic sql statement and execute it. PROS - This runs fine; it supports all the different expressions and logic we have in the rules. CONS - Very slow, and slower with each new set of rules added.
2) We identified the "easy" rules -- where a specified field equals a specified value. Then we unpivot our members table so we can perform one inner join against our rules table (unfortunately having to dynamic sql because unpivot requires the pivoted column names to be hardcoded). PROS - this runs, but... CONS - because UNPIVOT requires all the columns to be the same length and datatype, we really have one block of code devoted to each datatype involved in the rules. It doesnt seem flexible, but it does cut the iterations down to a minimum.
We are going to try another approach this week and will post back with an update.
August 9, 2010 at 4:13 pm
Why don't you put your easy rules into Table variables so that you can generate something like:
Where members.colname1 in (SELECT colname1 FROM @COLNAME1)
and members.colname2 in (SELECT colname2 FROM @COLNAME2)
.
.
.
Equal = in
not Equals = 'not in'
That should account for a lot of the rules.
How many columns are we talking about?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply