SQL Queries, How to deal with complicated Queries?

Question

Im taking DBMS course this semester and i want to be sure that im doing good progress and practice good thinking, my own approach for dealing with a query is kinda like divide and conquer - i split the query to sub queries and make them views, then using those views to simply collect the info i need, in general, is it a good approach?

what are the alterantives?

whats your own approach? if you think it is better then mine, why?

EXAMPLE :

GIVEN : DB : http://sqlfiddle.com/#!2/cdd98/1

(Q7) Find patients who visited all orthopedists (specialty) associated with their Insurance Companies.

.

CREATE VIEW Orthos AS
SELECT  d.cid,d.did
FROM Doctors d
WHERE d.speciality='Orthopedist';

CREATE VIEW OrthosPerInc AS
SELECT  o.cid, COUNT(o.did) as countd4i
FROM Orthos o
GROUP BY o.cid;

CREATE VIEW OrthoVisitsPerPat AS
SELECT v.pid,COUNT(o.did) as countv4d
FROM Orthos o,Visits v,Doctors d
WHERE o.did=v.did and d.did=o.did
GROUP BY v.pid,d.cid;

SELECT  p.pname,p.pid,p.cid
FROM  OrthoVisitsPerPat v, OrthosPerInc i,Patient p
WHERE i.countd4i = v.countv4d and p.pid=v.pid and p.cid=i.cid

since you are in school, you should learn modern join syntax — KM., May 01 '12 at 17:17
I have had a lot of problems with nested views in SQL Server, performance and troubleshooting-wise. I recommend against doing this unless you really need to. Also, I'd recommend you move to the newer explicit syntax for joins; implicit join syntax is supposed to be deprecated. — Jeremy Holovacs, May 01 '12 at 17:21
[JOIN vs WHERE](http://stackoverflow.com/questions/121631/inner-join-vs-where) — ypercubeᵀᴹ, May 01 '12 at 17:23
Implicit joins are terrible tecnique too. Whoever is teaching you this junk is incompetnt. Find a better instructor. — HLGEM, May 01 '12 at 17:53

score 1 · Answer 1 · answered May 01 '12 at 17:15

1

This depends on the database really. Some databases (like MySQL for example) are terrible at optimizing queries that use views. Others are not so bad.

But your strategy of breaking a query down into pieces then combining those into more complex queries is a good one. I would just (generally) end up making one large query that combines the parts.

answered May 01 '12 at 17:15

Eric Petroelje

59,820
9
127
177

in some cases it is nearly immpossible... at least from where i see it its way harded then my approach... got any tips? – Ofek Ron May 01 '12 at 17:17
@Ofek - not impossible, but certainly harder, and something that you'll just get better at with experience :) – Eric Petroelje May 01 '12 at 17:25

score 1 · Accepted Answer · answered May 01 '12 at 18:57

Do not use views for this, use derived tables, temp tables or CTES. Views are permananent objects in the database and the overuse of them is a real problem especially when you start to write views that call other views. We almost lost a multimillion dollar client because developers who were misinformed that views were a good thing made system so slow it was unusable oince the data was loaded. A beginner should never need to write a view.

Learn to use real joins - it will stand you in far better stead.

However, yes it is ok to break complex things into chunks. Just don't do it through using views.

Implied joins are an extremely bad technique and they are 20 years out of date and there is no excuse for using them in 2012. Promise yourself will will never use them again. Do not learn poor techniques, learn the correct ones.

I would have done something like (not tested):

SELECT  MDByPatient.pname,MDByPatient.pid,MDByPatient.cid
FROM 
    (
    SELECT   p.pname,p.pid,p.cid, count(v.did) as CountMDByPatient
    FROM  Patient p  
    INNER JOIN (SELECT DISTINCT v.pid, v.did FROM Visits) v
        ON p.pid = v.pid
    INNER JOIN Doctors d 
        ON d.did = v.did and p.cid = d.cid
    WHERE d.speciality='Orthopedist' 
    GROUP BY p.pname,p.pid,p.cid
    ) MDByPatient
INNER JOIN 
    (
    SELECT cid, count(did)as CountMDByIns
    FROM Doctors 
    WHERE d.speciality='Orthopedist' 
    GROUP BY cid
    ) MDByInsurance
        ON  MDByPatient.cid = MDByInsurance.cid
where MDByPatient.CountMDByPatient = MDByInsurance.CountMDByIns

You see it is still broken up in chunks, one to get the number of othopedists by insurance company and one to get the number of orthopedists by insurance company and person where teh person has made a visit. I further do a 'SELECT DISTINCT v.pid, v.did FROM Visits' because I don't want the count of doctors to be incorrect because I visited the same guy more than once.

well i am currently reading this book : http://pages.cs.wisc.edu/~dbbook/ Tho i barely get to learn much from it, have you got a better book or a guide for SQL? — Ofek Ron, May 01 '12 at 19:12

SQL Queries, How to deal with complicated Queries?

2 Answers2