W. Matthew Wilson wrote:
> I'm sure I'm not the first person to end up with a gigantic query that
> does lots of left joins and subselects.
>
> It seems to work, but I would love to break it up into smaller chunks.
>
> I'm thinking about rewriting the query to make several temporary
> tables that are dropped on commit, and then joining them at the end.
>
> I can't just use views for everything because I use parameters passed
> in from the web app. I am using a few views where I can.
>
> Is there anything dangerous about making temporary tables in this way?
> I started two transactions simultaneously and they were both able to
> make their own temporary tables.
>
> More generally, how to tame this big ol' query?
>
> The temporary tables mean I'm only pulling data from the database one
> time. ORMs often pull data from one query and then use that data to
> write the next query. This seems slow to me.
>
> Matt
>
>
>
I'm a big fan of the temp table plan, even though they are the
epitome of the very thing of which you indict ORMs! And you might be
wrong on that thesis but who cares, you're in hand-craft mode. My very
biased take is that you will at worst break even performance-wise, and
probably come out on top, especially if a left join goes through an
intersection record.