Re: [sqlite] Troubleshooting nested query performance issue

[Drake Wilson]

Quoth Petite Abeille , on 2014-02-03 23:49:14 +0100:
> Not directly related to your question, but… why oh why do people
> molest their queries by gratuitously and pointlessly aliasing
> perfectly good table name to meaningless random one letter codes?!?
> Masochism?

Because otherwise you wind up with things like

  long_table_name.foo, long_table_name.bar, long_table_name.baz,
  long_table_name.quux, long_table_name.plugh, long_table_name.plover

and that buries the changing part in a sea of sameness.

The "real" reason I use aliases-always in the first place (but which
wouldn't preclude longer aliases) is to semantically distinguish the
underlying table from the source as used to construct a particular
query; that also makes it more consistent when queries might have
multiple sources from the same table.  (I know not everyone thinks the
same way, so there's a subjective element.)

   ---> Drake Wilson

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Troubleshooting nested query performance issue

[James K. Lowden]

On Mon, 3 Feb 2014 23:49:14 +0100
Petite Abeille  wrote:

> > I have a query 
> 
> Not directly related to your question, but? why oh why do people
> molest their queries by gratuitously and pointlessly aliasing
> perfectly good table name to meaningless random one letter codes?!?
> Masochism?

Hey!  I'll allow gratuitious and pointless, but molest?  And definitely
not random.  

Even perfectly good table names can be quite long.  I'm sure you've
seen some more than 30 characters.  Just the physical act of typing
them is one drawback.  Yes, I have heard about that cut-and-paste
feature everyone's talking about these days.  Slows me down, say I.   

SQL is hardly a monument to succinct expression.  Shorter queries are
*easier* to understand, all thing being equal.  

The trick is to avoid meaningless and arbitrary aliases.  In a small
query, a single letter taken from the first letter of the tablename
usually works.  For larger queries I usually lean on 3-letter
abbreviations.  Three letters is space enough for mnemonic aliases;
it's  enough for, say,  all the world's airports and currencies.  

As Humpty-Dumpty said, "There's glory for you."  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Troubleshooting nested query performance issue

[Joseph L. Casale]

> Not directly related to your question, but… why oh why do people molest their 
> queries by
> gratuitously and pointlessly aliasing perfectly good table name to 
> meaningless random
> one letter codes?!? Masochism?

lol, you're not wrong.

This code is used in Python, and we are strict by-the-sword PEP8 shop.
Its a double edged sword at times, and plus I look at those tables so often the 
abbreviations
are second nature to me.

Funny,
jlc

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Troubleshooting nested query performance issue

[Joseph L. Casale]

> No.  It appears to be a correlated subquery.  It depends on the current row
> of the "d" table (diffset) because of the "ON r.guid_id=did" term and thus
> has to be reevalatued for every row of the "d" table.

Richard,

After a closer look, the subquery was useless and needed to be removed.

Thanks for the insight,
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Troubleshooting nested query performance issue

[Petite Abeille]

On Feb 3, 2014, at 11:30 PM, Joseph L. Casale  wrote:

> I have a query 

Not directly related to your question, but… why oh why do people molest their 
queries by gratuitously and pointlessly aliasing perfectly good table name to 
meaningless random one letter codes?!? Masochism?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Troubleshooting nested query performance issue

[Richard Hipp]

On Mon, Feb 3, 2014 at 5:30 PM, Joseph L. Casale
wrote:

> I have a query where if I hard code the results of the nested SELECT
> DICTINCT to a few
> static values, it completes very fast. Leaving the select causes this
> query to slow down
> badly. Running an explain query plan wasn't obvious with my weak sql
> experience.
>
> Is the nested query not evaluated only once?
>

No.  It appears to be a correlated subquery.  It depends on the current row
of the "d" table (diffset) because of the "ON r.guid_id=did" term and thus
has to be reevalatued for every row of the "d" table.


>
> SELECT
>a.value,
>COUNT(*) total,
>SUM(CASE WHEN r.status IS NULL THEN 1 ELSE 0 END)
> unprocessed,
>SUM(CASE WHEN r.status='processed' THEN 1 ELSE 0 END)
> processed,
>SUM(CASE WHEN r.status='error' THEN 1 ELSE 0 END) error
>   FROM diffset d
>   JOIN request r
>ON r.guid_id=d.id
>   JOIN action a
>ON a.req_id=r.id
>AND a.key='operationType'
>AND a.value IN (
> SELECT DISTINCT(a.value)
>   FROM action a
>   JOIN request r
>ON r.guid_id=d.id
>  WHERE a.key='operationType'
>)
>  WHERE d.id=?
>  GROUP BY value
>
> Thanks,
> jlc
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Troubleshooting nested query performance issue

[Joseph L. Casale]

I have a query where if I hard code the results of the nested SELECT DICTINCT 
to a few
static values, it completes very fast. Leaving the select causes this query to 
slow down
badly. Running an explain query plan wasn't obvious with my weak sql experience.

Is the nested query not evaluated only once?

SELECT
   a.value,
   COUNT(*) total,
   SUM(CASE WHEN r.status IS NULL THEN 1 ELSE 0 END) unprocessed,
   SUM(CASE WHEN r.status='processed' THEN 1 ELSE 0 END) processed,
   SUM(CASE WHEN r.status='error' THEN 1 ELSE 0 END) error
  FROM diffset d
  JOIN request r
   ON r.guid_id=d.id
  JOIN action a
   ON a.req_id=r.id
   AND a.key='operationType'
   AND a.value IN (   
SELECT DISTINCT(a.value)
  FROM action a
  JOIN request r
   ON r.guid_id=d.id
 WHERE a.key='operationType' 
   )
 WHERE d.id=?
 GROUP BY value

Thanks,
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users