Hello, I would like to know peoples opinion about some queries, so i made this short very simple example. two tables, one lists persons, their names and a linke to some photos, the other lists photos.

table 1
-------
name: persons
col1: id (tinyint)
col2: name (varchar)
col3: photo_set_id (tinyint)

table 2
-------
name: photos
col1: photo_set_id (tinyint)
col2: photo_id (tinyint)
col3: file_path (varchar)

So: I want to select all persons with their photos. Which of a) & b) would you prefer?

a) SELECT name, photo_set_id FROM persons

and on each resulting row (lets call it $row) run this:

SELECT file_path FROM photos WHERE photo_set_id=$row['photo_set_id']

b) SELECT a.name, b.file_path FROM persons a, photos b WHERE a.photo_set_id=b.photo_set_id

I can see advantages of each method.
Method a) has easier sql and therefor harder to make mistakes. Also if a person dont have photos (ie photo_set_id = null) he will still be listed
Method b) has more complex sql, so easier to make mistake, but probably runs faster as we make less queries. One _problem_ though is that if a person dont have photos, he will not be listed.

Thanks for any thoughts on the matter!

Add a third one there, with an outer join:

SELECT a.name, b.file_path 
FROM persons a
  left outer join photos b 
    on a.photo_set_id=b.photo_set_id

Gives you every peoples with each of their photos, and even if 1 person don't have a photo(set), it will be listed.
But if a user uploaded 5 photos, you will see him 5 times.

It all depend how you want to handle the datas later.
For simplicity, I'd suggest you to go with the first solution.

There will be a slight penalty hit, it's true, but it will be so low that it won't influence anything else.

Hey that was quick! I will look into that "left outer join" sql command (im not that advanced...yet) thanks a lot!

As you can guess this example is very simplified. In the real world, which would you prefere of a) and c) (c being your suggestion) ?

Initially i would think that more easy queries would be much easier to read for others that need to read your code, as well as easier not to make mistakes.

But there must be a reason some people make incredible complex sql queries?

But there must be a reason some people make incredible complex sql queries?

It all depends of who is writing/reviewing the query.
I work as an DBA, and this is an example of the queries I am used to use:

UPDATE tp
SET 
    blnSTATPOS2_UNUSED_1=CASE WHEN STATPOS2_UNUSED_1 IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_UNUSED_2=CASE WHEN STATPOS2_UNUSED_2 IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_UNUSED_3=CASE WHEN STATPOS2_UNUSED_3 IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_ABGABENONAME=CASE WHEN STATPOS2_ABGABENONAME IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_EXISTWARENZEICHEN=CASE WHEN STATPOS2_EXISTWARENZEICHEN IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_BESTDRINGEND=CASE WHEN STATPOS2_BESTDRINGEND IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_MWST_HALB=CASE WHEN STATPOS2_MWST_HALB IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_ZEILENRABATT=CASE WHEN STATPOS2_ZEILENRABATT IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_UNUSED_4=CASE WHEN STATPOS2_UNUSED_4 IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_NETTO=CASE WHEN STATPOS2_NETTO IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_WARNUNGOK=CASE WHEN STATPOS2_WARNUNGOK IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_UNUSED_5=CASE WHEN STATPOS2_UNUSED_5 IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_UNUSED_6=CASE WHEN STATPOS2_UNUSED_6 IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_PP=CASE WHEN STATPOS2_PP IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_ORIGIN_PP=CASE WHEN STATPOS2_ORIGIN_PP IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_ORIGIN_REFILL=CASE WHEN STATPOS2_ORIGIN_REFILL IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_ORDERED_ELSEWHERE=CASE WHEN STATPOS2_ORDERED_ELSEWHERE IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_WARENFAKTOR0=CASE WHEN STATPOS2_WARENFAKTOR0 IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_WARENFAKTORMINUS1=CASE WHEN STATPOS2_WARENFAKTORMINUS1 IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_AKTINFO=CASE WHEN STATPOS2_AKTINFO IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_NOABVK=CASE WHEN STATPOS2_NOABVK IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_HATWARNHINWEIS=CASE WHEN STATPOS2_HATWARNHINWEIS IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_HATWARNHINWEIS_TEST=CASE WHEN STATPOS2_HATWARNHINWEIS_TEST IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_AUTOMAT_REQ=CASE WHEN STATPOS2_AUTOMAT_REQ IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_AUTOMAT_OK=CASE WHEN STATPOS2_AUTOMAT_OK IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_AUTOMAT_ERR=CASE WHEN STATPOS2_AUTOMAT_ERR IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_ISTAUTOMAT=CASE WHEN STATPOS2_ISTAUTOMAT IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_ABDVGENEHMIGUNG=CASE WHEN STATPOS2_ABDVGENEHMIGUNG IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_ABDVNEGATIVLISTE=CASE WHEN STATPOS2_ABDVNEGATIVLISTE IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_ABDVKOSTENVORANSCHLAG=CASE WHEN STATPOS2_ABDVKOSTENVORANSCHLAG IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_CH_MEDIPUNKTE=CASE WHEN STATPOS2_CH_MEDIPUNKTE IS NOT NULL THEN 1 ELSE 0 end
    ,blnSTATPOS2_CH_PATPUNKTE=CASE WHEN STATPOS2_CH_PATPUNKTE IS NOT NULL THEN 1 ELSE 0 end
FROM pms.tblrezept_pos tp
INNER JOIN (
    SELECT fk_1, nr_2,
        STATPOS2_UNUSED_1
        ,STATPOS2_UNUSED_2
        ,STATPOS2_UNUSED_3
        ,STATPOS2_ABGABENONAME
        ,STATPOS2_EXISTWARENZEICHEN
        ,STATPOS2_BESTDRINGEND
        ,STATPOS2_MWST_HALB
        ,STATPOS2_ZEILENRABATT
        ,STATPOS2_UNUSED_4
        ,STATPOS2_NETTO
        ,STATPOS2_WARNUNGOK
        ,STATPOS2_UNUSED_5
        ,STATPOS2_UNUSED_6
        ,STATPOS2_PP
        ,STATPOS2_ORIGIN_PP
        ,STATPOS2_ORIGIN_REFILL
        ,STATPOS2_ORDERED_ELSEWHERE
        ,STATPOS2_WARENFAKTOR0
        ,STATPOS2_WARENFAKTORMINUS1
        ,STATPOS2_AKTINFO
        ,STATPOS2_NOABVK
        ,STATPOS2_HATWARNHINWEIS
        ,STATPOS2_HATWARNHINWEIS_TEST
        ,STATPOS2_AUTOMAT_REQ
        ,STATPOS2_AUTOMAT_OK
        ,STATPOS2_AUTOMAT_ERR
        ,STATPOS2_ISTAUTOMAT
        ,STATPOS2_ABDVGENEHMIGUNG
        ,STATPOS2_ABDVNEGATIVLISTE
        ,STATPOS2_ABDVKOSTENVORANSCHLAG
        ,STATPOS2_CH_MEDIPUNKTE
        ,STATPOS2_CH_PATPUNKTE
    FROM (
        SELECT x.strFlag, tp1.FK_1, tp1.StatusReserve_21, tp1.nr_2
        FROM pms.tblrezept_pos AS tp1
            CROSS APPLY (
                SELECT * FROM pms.funGetBitFlags('rezept', tp1.StatusReserve_21,'STATPOS2[_]%') AS fgbf
            )AS x
        WHERE x.strFlag != 'nill'
    ) AS src
    PIVOT (
        max(strflag) FOR strflag IN (
            id, nr,
            STATPOS2_UNUSED_1
            ,STATPOS2_UNUSED_2
            ,STATPOS2_UNUSED_3
            ,STATPOS2_ABGABENONAME
            ,STATPOS2_EXISTWARENZEICHEN
            ,STATPOS2_BESTDRINGEND
            ,STATPOS2_MWST_HALB
            ,STATPOS2_ZEILENRABATT
            ,STATPOS2_UNUSED_4
            ,STATPOS2_NETTO
            ,STATPOS2_WARNUNGOK
            ,STATPOS2_UNUSED_5
            ,STATPOS2_UNUSED_6
            ,STATPOS2_PP
            ,STATPOS2_ORIGIN_PP
            ,STATPOS2_ORIGIN_REFILL
            ,STATPOS2_ORDERED_ELSEWHERE
            ,STATPOS2_WARENFAKTOR0
            ,STATPOS2_WARENFAKTORMINUS1
            ,STATPOS2_AKTINFO
            ,STATPOS2_NOABVK
            ,STATPOS2_HATWARNHINWEIS
            ,STATPOS2_HATWARNHINWEIS_TEST
            ,STATPOS2_AUTOMAT_REQ
            ,STATPOS2_AUTOMAT_OK
            ,STATPOS2_AUTOMAT_ERR
            ,STATPOS2_ISTAUTOMAT
            ,STATPOS2_ABDVGENEHMIGUNG
            ,STATPOS2_ABDVNEGATIVLISTE
            ,STATPOS2_ABDVKOSTENVORANSCHLAG
            ,STATPOS2_CH_MEDIPUNKTE
            ,STATPOS2_CH_PATPUNKTE
        )
    ) AS pvt
) AS x ON x.fk_1=tp.fk_1 AND x.nr_2=tp.nr_2

...
SELECT 
    intRezeptFK=trz.idsRezeptID,
    intArtNr=tab.intArtNr,
    intArtSrcUID=tab.intSrcUID,
    numMenge=tp_ray.Menge_4,
    uniProfilFK=@intMdtUid,
    intUserFK=1,
    dtmUserMut=@dtmNow,
    intSrcUID=@intMdtUid,
    intRezeptZeileNr=tp_ray.Nr_2,
    intAnzAbgaben=NULL,
    numMengeBezogen=0,    --computed later
    dtmGueltigBis=tr.BisDatum_32,
    strArtTypCode=NULL,
    intRezeptZeileFK=NULL,
    strLangerName=COALESCE(fans.strLangerName,'-- EMI: Name was NULL --'),
    strNotiz=NULL,
    blnWiederholbar=tp_ray.blnstatpos_ch_wiederholbar,
    strCommentFacturation=NULL,
    intVersFK=NULL,
    blnPhysicallyDeleted=0,
    blnOhneLieferung=0,
    blnEinfachesRezept=0,
    intSrcRecID=NULL
FROM pms.tblrezept_pos AS tp_ray
    INNER JOIN (
        SELECT 
            tab.idsArtBasisID, tab.intPhCde, tami.strArtNr2 AS intArtNr, 'rayon' AS src, tab.intSrcUid
        FROM ppergo.dbo.tblArtMedInfo AS tami
            INNER JOIN ppergo.dbo.tblArtBasis AS tab
                ON tab.idsArtBasisID=tami.intArtBasisFK
                AND tami.intSrcUID IN (98,99)
                AND tab.blnPhysicalDeleted=0
                AND tami.strArtNr2 IS NOT NULL         
    ) AS tab 
        ON tab.intArtNr=tp_ray.RayonPzn_24
    INNER JOIN ppergo.dbo.funArtNameStandard(@intMdtUID,'fr') AS fans
        ON fans.intArtBasisFk=tab.idsArtBasisID
    INNER JOIN pms.tblrezept AS tr
        ON tp_ray.FK_1=tr.VerwNr_3
    INNER JOIN ppergo.dbo.tblRezept AS trz
        ON trz.intRezeptNr=tr.intErgoRezeptNr
WHERE tp_ray.RayonPzn_24!=0 AND tp_ray.RayonPzn_24!='' AND tp_ray.RayonPzn_24 IS NOT NULL 
AND tp_ray.Pzn_3!=0

The point is, that sometimes (and depending what your sql server is) it can speed up to use one big query.

The database uses what is called a "query plan", which could be explained as a map that the db engine creates to determine in which order the table will be queried, which indexes should be used and how the filter should be done.
It's a fairly complex process, and an estimate.
But, when you re-send a query that was already "planned", with just the modification of a deterministic parameter, the db can reuse the query plan that was already computed for the previous query.

For me, seeing one big query of a schema I know is sometime more understandable than being forced to look in the application code for several queries, and look how each one uses a part of the previous one.
It makes testing harder too.

That's it tripy. Next time I get stuck on a DB query, I'm talking to you! I don't even have to write queries that long!

Originally Posted by alf

Initially i would think that more easy queries would be much easier to read for others that need to read your code...

This is an irrelevant consideration. Code is written to achieve a task for the software, not for ease of reading. It is laid out in a way, through indentation, for example, which allows for ease of reading, but functional efficiency is the goal, not easy reading.

Originally Posted by alf

... as well as easier not to make mistakes.

Ever drop a semi-colon? Mistakes are easy to make, fixing them can be difficult, but I can't tell you how many times I've wasted hours trying to debug very small issues. Once debugged and running, however, it "just works" until someone asks for a change. But, that's what code comments are for and there's no harm in breaking apart a query like tripy's in the code comments for clarification in future editing. Again, the goal is functional efficiency -- how well does it do what it needs to do?

Choose the query which does the work it needs to do in the least amount of calculations and you will have done well. This isn't as easy to do as it is to write. You need to understand how the database uses your query to find the matching result set in order to make sure that you're not wasting calculations and that "how" is largely dependent on the engine and SQL server, so taking time to research your preferred engine and server will pay off.

Thank you both for your insights, I feel a wiser person already (at least when it comes to databases). The argument that once you're in "SQL mode" its easier to read it all there, than looking at the programming code is very good, I can see that clearly now that you said it.

I have written quite a few (fairly easy, but some more complex) queries in our company db which is about (20-30 tables) and as a "perfectionist" i always wonder which way is more fast, and which is more easy to program. In practical terms it really doesnt matter, cause of the relatively small database, but I like to do it the "proper" way, cause you never know how big your db can get (or when you change your job )

Originally Posted by JeremyMiller

Code is written to achieve a task for the software, not for ease of reading. It is laid out in a way, through indentation, for example, which allows for ease of reading, but functional efficiency is the goal, not easy reading.

I have to say I disagree a little with you here, as it all depends on the situation. For sure, if speed is _the_ issue, you are absolutely right, but if we are talking smaller databases (my case) it is more important for us to have easy readable and understandable code, as it would make it easier for a new employee with less skills to understand whats going on. Anyway, this can be discussed up and down for a long time, Im just saying it all depends.

Thanks again guys!

I love disagreements! They usually result in everybody learning (so long as they don't get personal).

I would hate to have to explain to a client that their code runs a bit slower b/c I was thinking about how easy it would be for someone else to learn the code. But, you raise a question in my head: When is speed not the issue?

Take

PHP Code:

<?php
$is_cool = rand(0,1);
for ($counter=0; $counter<1000000; $counter++) {
  if ($is_cool) {
    echo 'Cool factor #'.$counter.'<br />';
  } else {
    echo 'Un-cool factor #'.$counter.'<br />';
  }
}
?>

against

PHP Code:

<?php
$is_cool = rand(0,1);
if ($is_cool) {
  for ($counter=0; $counter<1000000; $counter++) {
    echo 'Cool factor #'.$counter.'<br />';
  }
} else {
  for ($counter=0; $counter<1000000; $counter++) {
    echo 'Un-cool factor #'.$counter.'<br />';
  }
}
?>

Which would you prefer and why?

Personally, I don't care if future coders can't understand the code. So long as I have followed my own rules: OOP, MVC, well-named variables, no loop-invariant expressions, etc. Those who can't figure out my code from a combination of the concepts and the coding are, in my never-humble opinion, not ready to call themselves coders.

Some people mistake such questions by me as confrontational, so I want to clarify that I'm looking for open discussion on this. Maybe we should do a different thread for such a conversation as I don't want to hijack your thread.

Oh, on topic then, you could have really complicated queries saved as stored procedures and views. Then the newbie's can read the code, the server has the query plan pre-saved, and updating is infinitely faster. Everybody gets what they want in that scenario and only those qualified at the upper levels of coding skill will be allowed to mess with the procedures/views.

Oh, on topic then, you could have really complicated queries saved as stored procedures and views. Then the newbie's can read the code, the server has the query plan pre-saved, and updating is infinitely faster. Everybody gets what they want in that scenario and only those qualified at the upper levels of coding skill will be allowed to mess with the procedures/views.

Exactly.
And those queries I copied here are coming from stored procedure.

Originally Posted by JeremyMiller

Take

PHP Code:

<?php
$is_cool = rand(0,1);
for ($counter=0; $counter<1000000; $counter++) {
  if ($is_cool) {
    echo 'Cool factor #'.$counter.'<br />';
  } else {
    echo 'Un-cool factor #'.$counter.'<br />';
  }
}
?>

against

PHP Code:

<?php
$is_cool = rand(0,1);
if ($is_cool) {
  for ($counter=0; $counter<1000000; $counter++) {
    echo 'Cool factor #'.$counter.'<br />';
  }
} else {
  for ($counter=0; $counter<1000000; $counter++) {
    echo 'Un-cool factor #'.$counter.'<br />';
  }
}
?>

Which would you prefer and why?

<offtopic>
This one:

PHP Code:

<?php
$is_cool = rand(0,1);
$sub = $is_cool ? "C" : "Un-c";
for ($counter=0; $counter<1000000; $counter++) {
    echo $sub.'ool factor #'.$counter.'<br />';
}
?>

</offtopic>

ROFL! Very well said, Abel. Ignore my positive TK comment. I had to reflect on the code more to fully grasp what you were communicating.

Graceful and employing the ternary operator.

Hehe, well replied wayfarer, that is the obvious answer !

Originally Posted by JeremyMiller

Personally, I don't care if future coders can't understand the code. So long as I have followed my own rules: OOP, MVC, well-named variables, no loop-invariant expressions, etc. Those who can't figure out my code from a combination of the concepts and the coding are, in my never-humble opinion, not ready to call themselves coders.

For the first part, I do care that my code is easy to read, not necessarily because i want others to read it, but i know that once im "out of the loop" and a year has passed, and i need to read and understand my code again, im gonna wonder "what the hell is going on here? what was i thinking??" if the code is not good As for your rulez i think they are great rules, and every coder should know them!
I guess your point with your example was not to make the "if" a million times, so yea, in those cases i definitely agree with you!

OK, on topic again, what do guys thing about this scenario:

We have now 1 million people the persons table. Then i have a subset of 1000 ids that i want to get info about. Also pretend that the persons table can contain more info about each person than just some photos.

which would be the best way to do it:

a) SELECT <what i want> FROM persons WHERE id IN (1,2...1000)

and for each row get the relevant data

b) in the code do somehing like this:

foreach ($persons as $id) {
SELECT <what i want> FROM persons WHERE id=$id
..get relevant data
}

This is a very close to many of my tasks when i program.. I can argue that a) will run faster, though im not sure how much, cause it returns a larger data set. while b) runs a query that returns a smaller data set, but of course its run n times more... b) is definitely for me logically the "easiest" way to program.

ps. i love the ternary operator!

Use the variant A, but modify it to not mention in (1, 2, 3 ... 1000).
This would make the query plan very complicated for nothing.

Use either the BETWEEN clause or filter it in the where clause

SELECT <what i want> FROM persons WHERE id BETWEEN 1 and 1000
or
SELECT <what i want> FROM persons WHERE id>=1 and id<=1000

This will allow a reuse of the query plan, and speed up the db work

Originally Posted by tripy

Use the variant A, but modify it to not mention in (1, 2, 3 ... 1000).
This would make the query plan very complicated for nothing.
Use either the BETWEEN clause or filter it in the where clause
This will allow a reuse of the query plan, and speed up the db work

I would have to use IN, as my ids often are not sequential (ie, could be 4,56,79,222...)..

I didnt quite get your reasoning for choosing A as appose to B, could you please elaborate? B is much more easy and clear to program for me, but
I'm willing to change, if there is a good reason to do so (ie significant performance advantages). I might add i program php if it is relevant.

I didnt quite get your reasoning for choosing A as appose to B

Simply because A is a simple query fired once, where B is a simple query fired (supposedly) many times.
Nothing more.

I'm willing to change, if there is a good reason to do so (ie significant performance advantages).

Performance is all relative.
You probably won't see any difference, but it depends of all the rest of the site too. The server having to share the CPU time that is available between all the queries, if you output 300 members, and fire this query 300 times, it will mobilize more resources at that moment. It may result with other, more important and heavy queries being delegated to wait that this batch ends.
But we are talking about milliseconds here...

Originally Posted by tripy

Simply because A is a simple query fired once, where B is a simple query fired (supposedly) many times. Nothing more.

Yes this is clear to me. But let say A returns a data set of 1000 rows on one query, this query would run much slower that a query that returns 1 row. I did some testing on this earlier and noticed that actually there was not a lot of difference. But i didnt test thoroughly when it comes to big datasets and many queries. You get my point though yes?

Originally Posted by tripy

Performance is all relative. You probably won't see any difference, but it depends of all the rest of the site too. The server having to share the CPU time that is available between all the queries, if you output 300 members, and fire this query 300 times, it will mobilize more resources at that moment. It may result with other, more important and heavy queries being delegated to wait that this batch ends.
But we are talking about milliseconds here...

In theory I am totally with you, and yes your last sentence is what I think is very interesting. Normally we are talking milliseconds, which for a normal sized site is quite ok. So then i would argue that writing easier code at the expense of a few milliseconds is ok. what i mean by easier code is that without complex sql's i can make much more reusable functions etc..

But let say A returns a data set of 1000 rows on one query, this query would run much slower that a query that returns 1 row

Not necessarily, no.
What would take most of the time would be the planning of the query, and the transfer of the result to the end point.
On the query itself, even by a millisecond precision, you probably would not see any difference.

So then i would argue that writing easier code at the expense of a few milliseconds is ok. what i mean by easier code is that without complex sql's i can make much more reusable functions etc..

Of course it is.
One of the task of a developer is to make his code readable, but not for others, for himself first.
When you open a function you coded 6 months ago, and you don't understand what the function do, there is a big problem.

The same principle applies (imho) to sql queries.
Sure, 1 big query can big quicker and save some cpu time.
But if you take 3 days to write it, and don't understand what it does when you look at it 2 days later, it's not worth it.

You have to gauge man-time with machine-time. If gaining a little machine-time on something that is already running correctly means taking several man-hours, don't waste your man-time on it.