![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
![]() |
Fri, Jul 16 2010 1:39 PM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Fri, Jul 16 2010 2:16 PM | Permanent Link |
Roy Lambert NLH Associates ![]() | Forgot to mention the subselects are optimised _ID is the primary key
Roy Lambert |
Fri, Jul 16 2010 6:08 PM | Permanent Link |
Charles Tyson | 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 |
Sat, Jul 17 2010 3:15 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Sat, Jul 17 2010 10:52 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Sat, Jul 17 2010 10:55 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Sat, Jul 17 2010 11:15 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 |
Wed, Jul 21 2010 5:29 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
This web page was last updated on Wednesday, August 14, 2024 at 03:12 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |