Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 11 to 17 of 17 total |
Is there a problem with this query? |
Thu, Apr 19 2012 8:51 PM | Permanent Link |
Adam H. | Whoops... sorry about that.
I was having error messages come up when I was sending them - and they were just staying on the screen.. I guess they were getting through after all. > Adam > > > Bad case of hiccoughs there. > > Roy Lambert > |
Thu, Apr 19 2012 8:53 PM | Permanent Link |
Adam H. | That was their data. Everything is identical except for the machine it's
running on. It really causes DBISam to have issues too. Once I start the query - the app (or DBSYS) doesn't respond. I can crash it - but even well after the time out period - the server doesn't disconnect the session - it's still there chugging away trying to retrieve the result (I guess). If I try to force a disconnect using SVRADMIN - it then hangs up too. (The SRVADMIN application). Effectively I need to close and restart the service to stop it from running, otherwise it just keeps going forever (it seems). Very odd... Cheers Adam. > Adam > > > You've tried the query on your machine. Was that with your data or their data? > > Roy Lambert Roy Lambert [Team Elevate] > |
Thu, Apr 19 2012 8:54 PM | Permanent Link |
Adam H. | Hi Eduardo,
Thanks for the suggestion. I tried that on my machine - and oddly it actually made the query run slower. Thanks for the suggestion. > Adam > > Have you tried to use "NOJOINOPTIMIZE" at the end of query statement ? > And, some times JOINOPTIMIZECOSTS helps too. > > Eduardo > > |
Fri, Apr 20 2012 4:10 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
Well that leaves either their machine or the version of DBISAM as the culprit. I'd drop an email to Tim asking if there could be a variance between their version of DBISAM and yours which could cause it. If not it sounds like its the machine. Probably some software installed rather than hardware. Just thinking about the latter. Asking users what's installed is generally non-productive. I know there are tools out there which will take a complete inventory. Why not track one down and have your customer run it. Something may be surfaced. Roy Lambert [Team Elevate] |
Mon, Apr 23 2012 7:21 PM | Permanent Link |
Adam H. | Thanks Roy,
They should be running the same version of DBISam that I am running - which effectively leaves the machine. (Either installed software as you've suggested, or possible hardware) As this application is pretty important to them and business critical, I'm considering the option of getting a second server solely to run the data on this. While I thought it was a bit of an overkill - I know that they would be more than happy to pay for a new dedicated server given how reliant they are on the uptime of the application. Cheers Adam. > Adam > > > Well that leaves either their machine or the version of DBISAM as the culprit. I'd drop an email to Tim asking if there could be a variance between their version of DBISAM and yours which could cause it. If not it sounds like its the machine. Probably some software installed rather than hardware. > > Just thinking about the latter. Asking users what's installed is generally non-productive. I know there are tools out there which will take a complete inventory. Why not track one down and have your customer run it. Something may be surfaced. > > Roy Lambert [Team Elevate] > |
Tue, Apr 24 2012 10:13 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
<< My clients are running 4.30 Build 4 whereas I still am running 4.28 Build 4 through DBSYS. (Although I have 4.30 in my IDE and it works the same) >> What does the query plan look like if you run the query using 4.30 B4 on your machine ? I suspect that the difference is with the version of DBISAM and how the tables and joins are being ordered. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Apr 26 2012 11:28 PM | Permanent Link |
Adam H. | Hi Tim,
Thanks for your reply... > Adam, > > << My clients are running 4.30 Build 4 whereas I still am running 4.28 > Build 4 through DBSYS. (Although I have 4.30 in my IDE and it works the > same) >> > > What does the query plan look like if you run the query using 4.30 B4 on > your machine ? I suspect that the difference is with the version of > DBISAM and how the tables and joins are being ordered. The original query plan when using 4.30 B4 on my machine executes fine. It appears as per below. Best Regards Adam ------------------------------ ================================================================================ SQL statement (Executed with 4.30 Build 4) ================================================================================ Select AC.ID as AppCourseID, AC.EstEndDate, AM.ModuleID, AM.Hrs HrsClaimable, Sum(Coalesce(AV.Hrs,0)) as HrsClaimed From ApprenticeCourse AC inner join apprenticemodules am on (am.appcourseid = ac.id) inner join apprenticevisits av on (av.appcourseid = am.appcourseid) and (av.moduleid = am.moduleid) Group By AC.ID, AM.ModuleID Tables Involved --------------- ApprenticeCourse (AC) table opened shared, has 19821 rows apprenticemodules (am) table opened shared, has 19267 rows apprenticevisits (av) table opened shared, has 290640 rows Result Set Generation --------------------- Result set will be canned Result set will consist of one or more grouped rows Result set will be grouped by the following column(s) using a temporary index: AppCourseID ModuleID Result set will be ordered by the temporary index created for the grouping Join Ordering ------------- The driver table is the ApprenticeCourse table (AC) The ApprenticeCourse table (AC) is joined to the apprenticemodules table (am) with the INNER JOIN expression: ac.id = am.appcourseid The apprenticemodules table (am) is joined to the apprenticevisits table (av) with the INNER JOIN expression: am.appcourseid = av.appcourseid and am.moduleid = av.moduleid 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 Optimized Join Ordering ----------------------- The driver table is the apprenticemodules table (am) The apprenticemodules table (am) is joined to the ApprenticeCourse table (AC) with the INNER JOIN expression: am.appcourseid = ac.id The apprenticemodules table (am) is joined to the apprenticevisits table (av) with the INNER JOIN expression: am.appcourseid = av.appcourseid and am.moduleid = av.moduleid 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: am.appcourseid = ac.id is OPTIMIZED The expression: am.appcourseid = av.appcourseid and am.moduleid = av.moduleid is OPTIMIZED ================================================================================ >>>>> 18864 rows affected in 10.125 seconds ================================================================================ -------------- |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |