General discussion


Optimize your logic before writing the SQL

By MaryWeilage Editor ·
This week's SQL Server e-newsletter explains why you should optimize your logic before writing the SQL. Is this tip helpful to your development work? Please let us know.

If you aren't subscribed to the free SQL Server e-newsletter, you can automatically subscribe to this e-newsletter by pasting the following URL into your browser: &subs_channel=bldr_front_door&list_id=e046&tag=fb

* Please delete any extra spaces that appear when you paste this URL into your browser.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -


by baileyg In reply to Optimize your logic befor ...

"select people who know SQL or who do not SQL but do know Python."

I understand this is an example but in the real world I would moan at the analyst for not being explicit enough. Literally, yes it does break down to an "SQL or Python" but the last condition does suggest and exclusive or. I tend to find that once a problem is fully clarified and expressed that it is indeed possible to "code this exactly as it was expressed". Well most of the time anyway :)

Collapse -


by Roho In reply to Logic?

I expected a very clever approach to the problem and hoped for more efficient SQL after the first bit. But no, this was all. Not much really.

Sorry to say, but I have seen better here, much better.

Collapse -

eliminate or through clever use of function?

by doug.gelling In reply to Disappointment

Ignoring many variables (table size / indices, etc.) one might find the following solution clever.

Unlike the original solution, it has the added benefit of excluding Persons who know both SQL and Python which is called for by the requirement.

What took some thought and may not be obvious is the concatenation of a space in front of the SkillID in the charindex of Python which forces a value of 2 if the substring is found.

It may be clever at the expense of clarity.

select dbo.PersonSkills.PersonID
from dbo.PersonSkills
by PersonSkills.PersonID
having sum(charindex('SQL',PersonSkills.SkillID)+
charindex('Python',' '+PersonSkills.SkillID)) between 1 and 2

Collapse -

Not Quite Right

by wyo_dutchman In reply to Optimize your logic befor ...

Correct me if I am wrong, but it seems that using the code listed in this example will still return duplicates. To eliminate duplicates, you must return only distinct names (leaving out skills in the select list):

SELECT DISTINCT dbo.Persons.Name
FROM dbo.Persons
INNER JOIN dbo.PersonSkills ON dbo.Persons.PersonID =
INNER JOIN dbo.Skills ON dbo.PersonSkills.SkillID = dbo.Skills.SkillID
WHERE (dbo.Skills.Skill = 'SQL') OR (dbo.Skills.Skill = 'Python')

Collapse -

truth tables

by jrod In reply to Not Quite Right

I took philosophy and I've seen truth tables.. I'd like to see some actual uses for truth files in this instance

Collapse -

Not really duplicates

by HenryStinson In reply to Not Quite Right

Sure, it might return two records for certain people, but the records returned are two fields and would not really be duplicates in the techinical sense. If we want the query to show not only the people who had those skills but which of those skills they had, then we might want two records. However, see my comment about "Is Query Correct?" -- I think the way the original problem was worded, there should only be one record returned, because the query should leave out people who know both SQL and Python.

Collapse -

Further optimization?

by HenryStinson In reply to Optimize your logic befor ...

Could the SQL query be further optimized by specifying:

WHERE dbo.Skills.Skill IN ('SQL', 'PYTHON')

instead of using the "OR".

I had read (years ago -- maybe it no longer applies) that using an OR in the WHERE clause can cause a SQL optimizer to turn off using indexes. I'm not sure that this still applies (to modern optimizers).

Also, would it not optimize the query further to use aliases for the table names, especially for dbo.skills?

Collapse -

Is query correct?

by HenryStinson In reply to Optimize your logic befor ...

Given the original specification statement (and I paraphrase here using numbered items to try to clarify) where a person either 1. knows SQL or 2. doesn't know SQL but knows Python ---

Isn't the use of a simple "OR" in the "WHERE" clause wrong? It could get a bit more complicated if we had to exclude those people who knew both SQL and Python, right? Doesn't the intermediate table ("PersonSkills") allow multiple skills to be associated with a person?

Or did I miss something?

Related Discussions

Related Forums