Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 13 total |
Should Joins be this slow. |
Sun, May 7 2006 9:51 PM | Permanent Link |
"Adam H." | Hi,
I have a query that seems to be suspiciously slow, and I just wanted to confirm that I was doing everything right. I have two tables, one called Tickets, and the other called Names. The Tickets table has 4200 records, and the names table has only 24. If I execute the SQL: select T.ID, T.Ticket from Tickets T The query executes immediately (all 4200 records). (DBSys shows 0 seconds). However, If I execute the SQL: select T.ID, T.Ticket from Tickets T left outer join Names S on (S.ID = T.FROMID) The query takes 1.3 seconds to load. Both S.ID and T.FromID are indexed (not compressed). I'm using DBISam 4.21b11 Are joins supposed to slow queries down that much? Thanks & Regards Adam. -- There is a principle which is a bar against all information, which cannot fail to keep a man in everlasting ignorance-- that principle is contempt prior to investigation." - Herbert Spencer, British philosopher. |
Sun, May 7 2006 10:28 PM | Permanent Link |
"Clive" | Can you post the query plan?
"Adam H." <ahairsub4@rREMOVEMEspamSTOPPER.jvxp.com> wrote in message news:D8A9C3FA-0DDC-4C6C-9BFE-FE58F03727D5@news.elevatesoft.com... > Hi, > > I have a query that seems to be suspiciously slow, and I just wanted to > confirm that I was doing everything right. > > I have two tables, one called Tickets, and the other called Names. > > The Tickets table has 4200 records, and the names table has only 24. > > If I execute the SQL: > > select T.ID, T.Ticket > from Tickets T > > The query executes immediately (all 4200 records). (DBSys shows 0 > seconds). > > However, If I execute the SQL: > > select T.ID, T.Ticket > from Tickets T > left outer join Names S on (S.ID = T.FROMID) > > The query takes 1.3 seconds to load. > > Both S.ID and T.FromID are indexed (not compressed). > > I'm using DBISam 4.21b11 > > Are joins supposed to slow queries down that much? > > Thanks & Regards > > Adam. > > -- > There is a principle which is a bar against all information, which cannot > fail to keep a man in everlasting ignorance-- that principle is contempt > prior to investigation." - Herbert Spencer, British philosopher. > > |
Sun, May 7 2006 11:30 PM | Permanent Link |
"Adam H." | Hi Clive,
> Can you post the query plan? Sure, see below. BTW - I upgraded dbsys to 4.24, but didn't seem to make any difference. Regards Adam. ============================================================================ ==== SQL statement (Executed with 4.24 Build 1) ============================================================================ ==== select T.ID, T.Ticket from Tickets T left outer join Names S on (S.ID = T.FROMID) Tables Involved --------------- Tickets (T) table opened shared, has 4225 rows Names (S) table opened shared, has 26 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more rows Join Ordering ------------- The driver table is the Tickets table (T) The Tickets table (T) is joined to the Names table (S) with the LEFT OUTER JOIN expression: T.FROMID = S.ID Optimizer will attempt to re-order the joins to a more optimal order Use the NOJOINOPTIMIZE clause at the end of the SQL statement to force the optimizer to leave the joins in their declared order The joins are already in optimal order and cannot be optimized any further Join Execution -------------- Costs ARE NOT being taken into account when executing this join Use the JOINOPTIMIZECOSTS clause at the end of the SQL statement to force the optimizer to consider costs when optimizing this join The expression: T.FROMID = S.ID is OPTIMIZED ============================================================================ ==== >>>>> 4225 rows affected in 1.406 seconds ============================================================================ ==== |
Sun, May 7 2006 11:34 PM | Permanent Link |
"Adam H." | Hi Clive,
Upon a second look, I think I found out what possibly may be the cause. I notice that the query runs 'instantly', but only when I have it set to a Live Result Set. When I turn this feature off, then the 'quick' query, instead of running instantly - takes around 1.25 seconds to complete. Of course, queries with joins can't be returned as a live result set, so I'm assuming that this particular query will normally take 1.25 seconds to complete, to return the 4200 odd records, so in effect, maybe the queries not slow at all, and it's moreso the other way around, that the live result set setting is quick. Best Regards Adam. -- There is a principle which is a bar against all information, which cannot fail to keep a man in everlasting ignorance-- that principle is contempt prior to investigation." - Herbert Spencer, British philosopher. |
Mon, May 8 2006 12:11 AM | Permanent Link |
"Clive" | Did you try using this option
JOINOPTIMIZECOSTS At the end of the query ?. "Adam H." <ahairsub4@rREMOVEMEspamSTOPPER.jvxp.com> wrote in message news:20945118-C45D-414F-8C89-32A8B8869CE6@news.elevatesoft.com... > Hi Clive, > > Upon a second look, I think I found out what possibly may be the cause. > > I notice that the query runs 'instantly', but only when I have it set to a > Live Result Set. When I turn this feature off, then the 'quick' query, > instead of running instantly - takes around 1.25 seconds to complete. > > Of course, queries with joins can't be returned as a live result set, so > I'm > assuming that this particular query will normally take 1.25 seconds to > complete, to return the 4200 odd records, so in effect, maybe the queries > not slow at all, and it's moreso the other way around, that the live > result > set setting is quick. > > Best Regards > > Adam. > > -- > There is a principle which is a bar against all information, which cannot > fail to keep a man in everlasting ignorance-- that principle is contempt > prior to investigation." - Herbert Spencer, British philosopher. > > |
Mon, May 8 2006 1:42 AM | Permanent Link |
"Adam H." | Hi Clive,
In this situation, joinoptimizecosts doesn't seem to make much difference. Best Regards Adam. |
Mon, May 8 2006 2:54 AM | Permanent Link |
"Clive" | It does seem slow.. Hopefully tim has an answer
Cheers Clive. "Adam H." <ahairsub4@rREMOVEMEspamSTOPPER.jvxp.com> wrote in message news:900F3658-CB0C-4D29-B7C0-3EE461A22E3D@news.elevatesoft.com... > Hi Clive, > > In this situation, joinoptimizecosts doesn't seem to make much difference. > > Best Regards > > Adam. > > |
Mon, May 8 2006 3:12 AM | Permanent Link |
"Adam H." | Hi Clive,
> It does seem slow.. Hopefully tim has an answer I just realise, I should have added this is over a 100mb network, and not locally. In my original post, I was confused, because the 1st query ran lightning fast, but the 2nd one didnt - until I realised about the live result property. (Got me again dang nabit! I'm not sure if a couple of fields, with 4000 records over 100mb lan should run any quicker or not, so I would be interested if anyone else has an input, but I'm relatively satisfied that I found the problem. (Being that I was comparing with a live result query) Best Regards Adam. -- There is a principle which is a bar against all information, which cannot fail to keep a man in everlasting ignorance-- that principle is contempt prior to investigation." - Herbert Spencer, British philosopher. |
Mon, May 8 2006 9:50 AM | Permanent Link |
Steve Forbes Team Elevate | Got me again dang nabit!
You turning Texan on us mate? <bg> -- Best regards Steve "Adam H." <ahairsub4@rREMOVEMEspamSTOPPER.jvxp.com> wrote in message news:6FB937C6-9D72-42C8-8A67-BB3694E4A71A@news.elevatesoft.com... |
Mon, May 8 2006 2:57 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< Of course, queries with joins can't be returned as a live result set, so I'm assuming that this particular query will normally take 1.25 seconds to complete, to return the 4200 odd records, so in effect, maybe the queries not slow at all, and it's moreso the other way around, that the live result set setting is quick. >> Bingo ! Couldn't have said it better myself. It's very much like the OpLocks issue - the issue is not that having multiple users is particularly slow, it's that having a single user is particularly fast. There's a certain overhead to creating the result set and populating it for canned result sets. -- Tim Young Elevate Software www.elevatesoft.com |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Thursday, September 26, 2024 at 10:04 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |