How to effectively create dynamic queries?

Let's say I have a search screen in my application that allows users to
do various AND OR conditions to about 14 pieces of criteria. That is a
complicated query to build dynamically and will be super slow because
it is dynamic.
Is there an efficient way to do this type of quering?
Thanks,
BrettHi
You can search sp_executesql in BOL.
That sp may reuse Execute plan than EXEC.
Also, You can access .xp_execresultset in Master database stored procedures.
"brett"?? ??? ??:

> Let's say I have a search screen in my application that allows users to
> do various AND OR conditions to about 14 pieces of criteria. That is a
> complicated query to build dynamically and will be super slow because
> it is dynamic.
> Is there an efficient way to do this type of quering?
> Thanks,
> Brett
>|||On 24 Apr 2006 16:02:36 -0700, "brett" <account@.cygen.com> wrote:
in <1145919756.043300.318930@.u72g2000cwu.googlegroups.com>

>Let's say I have a search screen in my application that allows users to
>do various AND OR conditions to about 14 pieces of criteria. That is a
>complicated query to build dynamically and will be super slow because
>it is dynamic.
>Is there an efficient way to do this type of quering?
>Thanks,
>Brett
Of course not. But you can always use a sophisticated CASE function
statement.
This posting is provided "AS IS" with no warranties and no guarantees either
express or implied.
Stefan Berglund|||I think what you want is the ability to load tables with criteria and
not have to use dynamic SQL. Let's say you want to search for job
candidates based on their skills.
skill = Java AND (skill = Perl OR skill = PHP)
becomes the disjunctive canonical form:
(Java AND Perl) OR (Java AND PHP)
which we load into this table:
CREATE TABLE Query
(and_grp INTEGER NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (and_grp, skill));
INSERT INTO Query VALUES (1, 'Java');
INSERT INTO Query VALUES (1, 'Perl');
INSERT INTO Query VALUES (2, 'Java');
INSERT INTO Query VALUES (2, 'PHP');
Assume we have a table of job candidates:
CREATE TABLE Candidates
(candidate_name CHAR(15) NOT NULL,
skill CHAR(4) NOT NULL,
PRIMARY KEY (candidate_name, skill));
INSERT INTO Candidates VALUES ('John', 'Java'); --winner
INSERT INTO Candidates VALUES ('John', 'Perl');
INSERT INTO Candidates VALUES ('Mary', 'Java'); --winner
INSERT INTO Candidates VALUES ('Mary', 'PHP');
INSERT INTO Candidates VALUES ('Larry', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Larry', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Perl'); --winner
INSERT INTO Candidates VALUES ('Moe', 'PHP');
INSERT INTO Candidates VALUES ('Moe', 'Java');
INSERT INTO Candidates VALUES ('Celko', 'Java'); -- loser
INSERT INTO Candidates VALUES ('Celko', 'Algol');
INSERT INTO Candidates VALUES ('Smith', 'APL'); -- loser
INSERT INTO Candidates VALUES ('Smith', 'Algol');
The query is simple now:
SELECT DISTINCT C1.candidate_name
FROM Candidates AS C1, Query AS Q1
WHERE C1.skill = Q1.skill
GROUP BY Q1.and_grp, C1.candidate_name
HAVING COUNT(C1.skill)
= (SELECT COUNT(*)
FROM Query AS Q2
WHERE Q1.and_grp = Q2.and_grp);
You can retain the COUNT() information to rank candidates. For example
Moe meets both qualifications, while other candidates meet only one of
the two. You can Google "canonical disjunctive form" for more details.
This is a form of relational division.|||Celko wrote:

> I think what you want is the ability to load tables with criteria and
> not have to use dynamic SQL. Let's say you want to search for job
> candidates based on their skills.
Just out of interest, what's if you had multiple types of criteria to
search on. For example, let's say you wanted:
skill = Java AND (skill = Perl OR skill = PHP) AND
birth_date between '1 Jan 1960' and '31 Dec 1970' AND
exists at least 2 entries in CandidateJobHistory where (role =
'Programmer' or role = 'BusinessAnalyst')
Would you have multiple Query tables, or make the Query table handle
multiple types of crtieria?
Kris|||http://www.sommarskog.se/dyn-search.html
"brett" <account@.cygen.com> wrote in message
news:1145919756.043300.318930@.u72g2000cwu.googlegroups.com...
> Let's say I have a search screen in my application that allows users to
> do various AND OR conditions to about 14 pieces of criteria. That is a
> complicated query to build dynamically and will be super slow because
> it is dynamic.
> Is there an efficient way to do this type of quering?
> Thanks,
> Brett
>|||I think that one has got to take the biscuit for an over complicated kludge
at keeping the solution within standard sql.
Not only will this not scale you have still to address optional parameters
at which point you will realise 14 tables all with left outer joins just
will not scale.
Dynamic SQL or a ridiculous number of IF ELSE statements are required,
unfortunetly you'll never use IF ELSE because its procedural so that puts
you into the COALESCE camp which will give a very general and very poor
query plan that will not scale.
Your answer only serves to demonstrate your lack of real programming
exposure, get out of the class room, these solutions will not work in the
real world.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145932636.689321.112960@.j33g2000cwa.googlegroups.com...
>I think what you want is the ability to load tables with criteria and
> not have to use dynamic SQL. Let's say you want to search for job
> candidates based on their skills.
> skill = Java AND (skill = Perl OR skill = PHP)
> becomes the disjunctive canonical form:
> (Java AND Perl) OR (Java AND PHP)
> which we load into this table:
> CREATE TABLE Query
> (and_grp INTEGER NOT NULL,
> skill CHAR(4) NOT NULL,
> PRIMARY KEY (and_grp, skill));
> INSERT INTO Query VALUES (1, 'Java');
> INSERT INTO Query VALUES (1, 'Perl');
> INSERT INTO Query VALUES (2, 'Java');
> INSERT INTO Query VALUES (2, 'PHP');
> Assume we have a table of job candidates:
> CREATE TABLE Candidates
> (candidate_name CHAR(15) NOT NULL,
> skill CHAR(4) NOT NULL,
> PRIMARY KEY (candidate_name, skill));
> INSERT INTO Candidates VALUES ('John', 'Java'); --winner
> INSERT INTO Candidates VALUES ('John', 'Perl');
> INSERT INTO Candidates VALUES ('Mary', 'Java'); --winner
> INSERT INTO Candidates VALUES ('Mary', 'PHP');
> INSERT INTO Candidates VALUES ('Larry', 'Perl'); --winner
> INSERT INTO Candidates VALUES ('Larry', 'PHP');
> INSERT INTO Candidates VALUES ('Moe', 'Perl'); --winner
> INSERT INTO Candidates VALUES ('Moe', 'PHP');
> INSERT INTO Candidates VALUES ('Moe', 'Java');
> INSERT INTO Candidates VALUES ('Celko', 'Java'); -- loser
> INSERT INTO Candidates VALUES ('Celko', 'Algol');
> INSERT INTO Candidates VALUES ('Smith', 'APL'); -- loser
> INSERT INTO Candidates VALUES ('Smith', 'Algol');
> The query is simple now:
> SELECT DISTINCT C1.candidate_name
> FROM Candidates AS C1, Query AS Q1
> WHERE C1.skill = Q1.skill
> GROUP BY Q1.and_grp, C1.candidate_name
> HAVING COUNT(C1.skill)
> = (SELECT COUNT(*)
> FROM Query AS Q2
> WHERE Q1.and_grp = Q2.and_grp);
> You can retain the COUNT() information to rank candidates. For example
> Moe meets both qualifications, while other candidates meet only one of
> the two. You can Google "canonical disjunctive form" for more details.
> This is a form of relational division.
>|||Tony Rogerson wrote:

> Your answer only serves to demonstrate your lack of real programming
> exposure, get out of the class room, these solutions will not work in the
> real world.
Actually we've used a similar approach for one of our products. It was
for a set of marketing plans for which the user selected a set of
customer demographic criteria (birth date, ethnicity, location, etc
etc). The reason we didn't just generate dynamic SQL was because these
marketing plans had to be saved and reusable, therefore we had to store
the actual criteria in a set of tables, similar to the Query table Joe
used except we stored all criteria types in that table.
Then when actually generating the customer list, we used a query
similar to Joe's and it works reasonably well. The main divantage is
that the optimizer can't use the best indexes, but since the act of
generating a customer list isn't time criticial it didn't matter.
I doubt this solution is suitable for the OP's problem (dynamic SQL is
probably better IMO), but in some cases it is useful and not just a
theoretical solution.
Kris|||Hi Kris,
I think you made the point that you get a general plan.
Also, consider 10 concurrent users doing this, suddenly you have shifted
your select centric access model to a transaction one so you'll face
increased write IO in the logs and disk, also there may well be a lot of
blocking problems which will cause significant scalability problems which
I'm suprised you have not noticed yet - perhaps there aren't many concurrent
users on your particular app using this functionality at the same time?
Dynamic SQL is the only way to go, I've done something similar in terms of
spec myself for a knowledge management system we developed, basically I
build the SQL dynamically and store the text, the query can be very easily
resubmitted without any work apart from a select from the table holding the
query, no constants used - all parameterised too and you get the best plan
for the job instead of a general one.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<kriskirk@.hotmail.com> wrote in message
news:1145947385.605291.160600@.y43g2000cwc.googlegroups.com...
> Tony Rogerson wrote:
>
> Actually we've used a similar approach for one of our products. It was
> for a set of marketing plans for which the user selected a set of
> customer demographic criteria (birth date, ethnicity, location, etc
> etc). The reason we didn't just generate dynamic SQL was because these
> marketing plans had to be saved and reusable, therefore we had to store
> the actual criteria in a set of tables, similar to the Query table Joe
> used except we stored all criteria types in that table.
> Then when actually generating the customer list, we used a query
> similar to Joe's and it works reasonably well. The main divantage is
> that the optimizer can't use the best indexes, but since the act of
> generating a customer list isn't time criticial it didn't matter.
> I doubt this solution is suitable for the OP's problem (dynamic SQL is
> probably better IMO), but in some cases it is useful and not just a
> theoretical solution.
> Kris
>|||Hi Brett,
SQL Server does a lot of auto-parameterisation so query plans will be
cached, obviously you might end up with 'x' different query plans but thats
good because they will be highly efficient based solely on what you are
trying to do instead of kludging the query to get round the lack of dynamics
within the SELECT statement.
The string concatenation won't even be measurable.
Build your query and parameterise it, Aaron links to a really good article
on dynamic SQL by Erland (http://www.sommarskog.se/dyn-search.html)
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"brett" <account@.cygen.com> wrote in message
news:1145919756.043300.318930@.u72g2000cwu.googlegroups.com...
> Let's say I have a search screen in my application that allows users to
> do various AND OR conditions to about 14 pieces of criteria. That is a
> complicated query to build dynamically and will be super slow because
> it is dynamic.
> Is there an efficient way to do this type of quering?
> Thanks,
> Brett
>