2.03 b17

I need to speed this up

SELECT
(SELECT COALESCE(_Forename +' ','?')+COALESCE(_Surname,'?')  FROM Contacts WHERE Contacts._ID = _fkContacts) AS _Name,
(SELECT COALESCE(_Surname,'?')+COALESCE(_Forename +' ','?')  FROM Contacts WHERE _ID = _fkContacts) AS _SortName,
_fkContacts,
_fkSites,
_JobTitle,
IF(_Left IS NULL THEN TRUE ELSE FALSE) AS _StillThere,
_Left
FROM Career
WHERE _fkCompanies = :_ID
ORDER BY _SortName DESC


Take out the ORDER BY and get sensitive its c0.016secs otherwise >c0.5secs

The result set consists of 41 rows

It seems directly related to the sub selects since if I comment out the first one the time is halved.

Roy Lambert

Forgot to mention the subselects are optimised _ID is the primary key

Roy Lambert

On 7/16/2010 10:39 AM, Roy Lambert wrote:
> 2.03 b17
>
> I need to speed this up
>
> SELECT
> (SELECT COALESCE(_Forename +' ','?')+COALESCE(_Surname,'?')  FROM Contacts WHERE Contacts._ID = _fkContacts) AS _Name,
> (SELECT COALESCE(_Surname,'?')+COALESCE(_Forename +' ','?')  FROM Contacts WHERE _ID = _fkContacts) AS _SortName,
> _fkContacts,

Is EDB smart enough to realize that the 2 selects are going to return
the same row(s), or does it query Contacts to construct _Name, then
re-query it to construct _SortName?

What happens if you write it as a simple outer join and drop the subSELECTS?


> _fkSites,
> _JobTitle,
> IF(_Left IS NULL THEN TRUE ELSE FALSE) AS _StillThere,

As they used to say on Mystery Science Theater, "Sure, that makes
sens--huh???"

> _Left
> FROM Career
> WHERE _fkCompanies = :_ID
> ORDER BY _SortName DESC

Charles


>Is EDB smart enough to realize that the 2 selects are going to return
>the same row(s), or does it query Contacts to construct _Name, then
>re-query it to construct _SortName?

Dunno - but from what's happening here it doesn't look like it.

>What happens if you write it as a simple outer join and drop the subSELECTS?

Its faster, takes c0.3 secs which is still not really fast enough.

Currently I use a query with joins, but no sorting and split the results into three TMS AdvStringGrids. That takes c0.2secs. To replace it I need to run three queries hooking them up to three DBGrids, but as it stands that will take c1.5secs and I want it to be faster than the stringgrid solution.

Roy Lambert

Roy,

<< Forgot to mention the subselects are optimised _ID is the primary key >>

This second sub-select isn't correct:

(SELECT COALESCE(_Surname,'?')+COALESCE(_Forename +' ','?')  FROM Contacts
WHERE _ID = _fkContacts) AS _SortName,

You'll notice that you're missing the Contacts. table name prefix on the _ID
column, it should be:

SELECT COALESCE(_Surname,'?')+COALESCE(_Forename +' ','?')  FROM Contacts
WHERE Contacts._ID = _fkContacts) AS _SortName,

--
Tim Young
Elevate Software
www.elevatesoft.com

Charles,

<< As they used to say on Mystery Science Theater, "Sure, that makes
sens--huh???" >>

Cool, another MST3K fan - Volume 18 just arrived from Amazon yesterday.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>This second sub-select isn't correct:

That's because at one point I decided to AS _fkContacts as _ID so I added Contacts. to make sure there was no ambiguity. Then I removed the AS but only one of the Contacts.

Speed either way is about the same.

Roy Lambert

Roy,

<< Speed either way is about the same. >>

I'm afraid there isn't much else you can do with it.

--
Tim Young
Elevate Software
www.elevatesoft.com