![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
![]() |
Thu, Sep 10 2009 11:09 AM | Permanent Link |
Michael Reisch | Hi there,
I wonder that my application starts sometimes slow and sometimes fast. My tables was saved on a NAS drive, and several users can access them with a local session connection. The config folder was set on each PC the same (\\192.168.2.3\buero$\Buero\gripsware\proReport2). I found out, that the first user who connect to the database needs 1 sec. to run my 1st. Query to show the overview, the second user needs 12 sec. for the same Query. I´m working at the moment with EDB 1.09 (Build 4), so I thought to upgrade to EDB 2.xx. I have Installed the EDB 2.xx Manager to check out the speed of EDB 2 for my case. I try to run my EDB 1 Query and get error #1011 (A scalar query can only return a single value). This is my query: SELECT pr.GUID_ID, pr.Report_ID, pr.Project_GUID, pr.Protocol_GUID, pr.Place_GUID, pr.Catalog_GUID, pr.Catalog_Type, pr.Address_GUID, pr.ContactName, pr.Date_ToDoFrom, pr.Date_ToDo, pr.Date_Done, pr.ObjType, pr.ReportState, pr.ReportPriority, e.Description AS PriorityText, pr.CreateDate, pr.ModifyDate, p.Description, p.ProjectShort, p.ProjectNo, p.ProjectLeader, p.ProjectLeaderSelect, LTrim(COALESCE(p.Country, '') + ' ' + COALESCE(p.ZIP, '') + ' ' + COALESCE(p.City, '')) As ProjectCity FROM "ProtocolReport" pr LEFT OUTER JOIN "Project" p ON (p.GUID_ID = pr.Project_GUID) LEFT OUTER JOIN "EnumValues" e ON (e.GUID_ID = pr.ReportPriority) WHERE pr.GUID_ID + pr.Protocol_GUID IN (COALESCE(SELECT PRX.GUID_ID+PRX.Protocol_GUID FROM "ProtocolReport" PRX LEFT OUTER JOIN Protocol PC ON PC.GUID_ID = PRX.Protocol_GUID WHERE PRX.GUID_ID = pr.GUID_ID ORDER BY PC.CreateDate DESC, pr.GUID_ID + pr.Protocol_GUID)) Thanks for your help. Regards Michael |
Thu, Sep 10 2009 3:58 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Michael,
<< I wonder that my application starts sometimes slow and sometimes fast. My tables was saved on a NAS drive, and several users can access them with a local session connection. The config folder was set on each PC the same (\\192.168.2.3\buero$\Buero\gripsware\proReport2). I found out, that the first user who connect to the database needs 1 sec. to run my 1st. Query to show the overview, the second user needs 12 sec. for the same Query. I´m working at the moment with EDB 1.09 (Build 4), so I thought to upgrade to EDB 2.xx. I have Installed the EDB 2.xx Manager to check out the speed of EDB 2 for my case. I try to run my EDB 1 Query and get error #1011 (A scalar query can only return a single value). >> ElevateDB 2 does not permit scalar queries to return more than one row. Your sub-query inside of the COALESCE() function is returning more than one row, which is invalid. As for the general performance issue, this is due to opportunistic locking and the SMB file-sharing protocol, which has been talked about non-stop on these newsgroups for years. Perform a search on "opportunistic locking" here: http://www.elevatesoft.com/newsgrp?action=search&group=0 for more information. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Sep 11 2009 4:33 AM | Permanent Link |
Michael Reisch | Tim,
thanks for your fast response. Unfortunately did both answers not help. As I understand is the oportunistic locking a Windows problem, and I cannot change anything. So I have to accept that my application slows down in multi user environment. Is this effect also if I use EDB Server and run my application in C/S mode? 2. Do you have an idea how can I change my edb1 select command to run on edb2? Because I´m still interrested to upgrade to edb2 in the near future. Is there any documentation about the breaking sql command changes between version 1 and 2, so that I can check if I run in more problems with version 2? Thanks in advance Michael "Tim Young [Elevate Software]" wrote: Michael, << I wonder that my application starts sometimes slow and sometimes fast. My tables was saved on a NAS drive, and several users can access them with a local session connection. The config folder was set on each PC the same (\\192.168.2.3\buero$\Buero\gripsware\proReport2). I found out, that the first user who connect to the database needs 1 sec. to run my 1st. Query to show the overview, the second user needs 12 sec. for the same Query. I´m working at the moment with EDB 1.09 (Build 4), so I thought to upgrade to EDB 2.xx. I have Installed the EDB 2.xx Manager to check out the speed of EDB 2 for my case. I try to run my EDB 1 Query and get error #1011 (A scalar query can only return a single value). >> ElevateDB 2 does not permit scalar queries to return more than one row. Your sub-query inside of the COALESCE() function is returning more than one row, which is invalid. As for the general performance issue, this is due to opportunistic locking and the SMB file-sharing protocol, which has been talked about non-stop on these newsgroups for years. Perform a search on "opportunistic locking" here: http://www.elevatesoft.com/newsgrp?action=search&group=0 for more information. -- Tim Young Elevate Software www.elevatesoft.com |
Fri, Sep 11 2009 12:34 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Michael,
<< thanks for your fast response. Unfortunately did both answers not help. As I understand is the oportunistic locking a Windows problem, and I cannot change anything. So I have to accept that my application slows down in multi user environment. >> Well, you can consider bumping up the buffering used for each table: http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=0&topic=9 see the "Modifying the Amount of Buffering" section. << Is this effect also if I use EDB Server and run my application in C/S mode? >> No, in that case EDB is handling all of the comms and does not transmit indexes and other internal structures across the network. << 2. Do you have an idea how can I change my edb1 select command to run on edb2? Because I´m still interrested to upgrade to edb2 in the near future. >> Your SELECT command is invalid, and EDB 1 was incorrect in letting it go without an error. If you have a query that returns more than one row and you want to use the resultant first column of the first row as a scalar value in a COALESCE() function, then you'll need to limit the number of rows in some fashion, either by adding new conditions to the WHERE clause or adding a RANGE clause. However, I must note that using the RANGE clause is ultimately just covering a possible issue that could come back and bite you in the future. << Is there any documentation about the breaking sql command changes between version 1 and 2, so that I can check if I run in more problems with version 2? >> The release notes for version 2 included with the product contain all of the information on breaking changes, but there weren't many for 2.0: - Executing the FETCH statement without specifying a fetch direction previously erroneously resulted in a FETCH NEXT in ElevateDB 1.x. In 2.x, this has been changed to correctly perform a fetch on the current row instead. - The BACKUP DATABASE, RESTORE DATABASE, IMPORT TABLE, and EXPORT TABLE statements now use stores instead of path names for their input and output. Please see the Stores topic for more information. - The TEDBEngine ConfigPath property now defaults to blank ('') and a design-time property editor now exists in the Object Inspector for selecting the configuration path. The change for scalar queries actually came much later in 2.02. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Sep 15 2009 11:12 AM | Permanent Link |
Michael Reisch | Tim,
<<Your SELECT command is invalid, and EDB 1 was incorrect in letting it go without an error.>> But this incorrect command works fine .... My Problem is quite complex. I will try to explain why I use this command. My application is used for snag flow on construction sites. When a site manager visit a construction site he starts a new protocol. In a new protocol he see snags without finish state of older protocols, so that he now what he have to check. New snags will be added to my ProtocolReport table. Each snag have a unique GUID and of course each protocol have a unique GUID. In ProtocolReport table is the combination of snag_GUID and protocol_GUID a unique value. A snag is saved in each protocol where you change the state. e.g. snag_GUID protocol_GUID snag_STATE snag_CHECKDATE ABC 1 OPEN 2009-09-01 DEF 2 OPEN 2009-09-02 ABC 3 OPEN 2009-09-07 ABC 4 CLOSED 2009-09-08 - This means I have found a snag "ABC" at 2009-09-01 and have saved that in protocol "1". - In protocol 2 I did´t check my snag "ABC", in this case it is not reported in that prototcol. But I find a new snag called "DEF". - In protocol 3 I check the snag "ABC", but I found it is still not finished - so STATE is still OPEN but CHECKDATE change to actual date. So I can remember if I have checked it. - In protocol 4 I found that the snag "ABC" was solved and I close it. When I start my application I need an overview of all OPEN snags with their latest states. E.g. when I start my app at 2009-09-07 I have to see the snag "ABC" with STATE from protocol 3 and the snag "DEF" with STATE from protocol 2. And if I start application at 2009-09-08 I will only see my snag "DEF" with STATE from protocol 2 (because "ABC" is closed). It would be great, if you have a better way how I can do that. Every other command I tried give all my snags back, because I found no way to say "only newest CHECKDATE". Thanks Michael "Tim Young [Elevate Software]" wrote: Michael, << thanks for your fast response. Unfortunately did both answers not help. As I understand is the oportunistic locking a Windows problem, and I cannot change anything. So I have to accept that my application slows down in multi user environment. >> Well, you can consider bumping up the buffering used for each table: http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=0&topic=9 see the "Modifying the Amount of Buffering" section. << Is this effect also if I use EDB Server and run my application in C/S mode? >> No, in that case EDB is handling all of the comms and does not transmit indexes and other internal structures across the network. << 2. Do you have an idea how can I change my edb1 select command to run on edb2? Because I´m still interrested to upgrade to edb2 in the near future. >> Your SELECT command is invalid, and EDB 1 was incorrect in letting it go without an error. If you have a query that returns more than one row and you want to use the resultant first column of the first row as a scalar value in a COALESCE() function, then you'll need to limit the number of rows in some fashion, either by adding new conditions to the WHERE clause or adding a RANGE clause. However, I must note that using the RANGE clause is ultimately just covering a possible issue that could come back and bite you in the future. << Is there any documentation about the breaking sql command changes between version 1 and 2, so that I can check if I run in more problems with version 2? >> The release notes for version 2 included with the product contain all of the information on breaking changes, but there weren't many for 2.0: - Executing the FETCH statement without specifying a fetch direction previously erroneously resulted in a FETCH NEXT in ElevateDB 1.x. In 2.x, this has been changed to correctly perform a fetch on the current row instead. - The BACKUP DATABASE, RESTORE DATABASE, IMPORT TABLE, and EXPORT TABLE statements now use stores instead of path names for their input and output. Please see the Stores topic for more information. - The TEDBEngine ConfigPath property now defaults to blank ('') and a design-time property editor now exists in the Object Inspector for selecting the configuration path. The change for scalar queries actually came much later in 2.02. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Sep 15 2009 2:12 PM | Permanent Link |
"John Hay" | Michael
> snag_GUID protocol_GUID snag_STATE snag_CHECKDATE > ABC 1 OPEN 2009-09-01 > DEF 2 OPEN 2009-09-02 > ABC 3 OPEN 2009-09-07 > ABC 4 CLOSED 2009-09-08 > > When I start my application I need an overview of all OPEN snags with their latest states. > E.g. when I start my app at 2009-09-07 I have to see the snag "ABC" with STATE from > protocol 3 and the snag "DEF" with STATE from protocol 2. And if I start application at > 2009-09-08 I will only see my snag "DEF" with STATE from protocol 2 (because "ABC" is closed). > > It would be great, if you have a better way how I can do that. Every other command I tried > give all my snags back, because I found no way to say "only newest CHECKDATE". > You can use a join with a subselect to get the result set you want. If you want to know which snags were open up till a certain date add a where clause to the subselect (eg where checkdate < date '2009-09-07') SELECT pr.* from ProtocolReport pr JOIN (SELECT snag_GUID,MAX(checkdate) maxdate FROM ProtocolReport GROUP BY snag_GUID) pr1 on pr.snag_GUID=pr1.snag_GUID AND pr.checkdate=pr1.maxdate WHERE snag_STATE='OPEN' John |
Wed, Sep 16 2009 8:32 AM | Permanent Link |
Michael Reisch | John,
thank you very much for this solution. It works perfect and 3 times faster than my command. I think I have to browse my SQL commands, to optimize also some other commands with this "subquery join". Michael "John Hay" wrote: Michael > snag_GUID protocol_GUID snag_STATE snag_CHECKDATE > ABC 1 OPEN 2009-09-01 > DEF 2 OPEN 2009-09-02 > ABC 3 OPEN 2009-09-07 > ABC 4 CLOSED 2009-09-08 > > When I start my application I need an overview of all OPEN snags with their latest states. > E.g. when I start my app at 2009-09-07 I have to see the snag "ABC" with STATE from > protocol 3 and the snag "DEF" with STATE from protocol 2. And if I start application at > 2009-09-08 I will only see my snag "DEF" with STATE from protocol 2 (because "ABC" is closed). > > It would be great, if you have a better way how I can do that. Every other command I tried > give all my snags back, because I found no way to say "only newest CHECKDATE". > You can use a join with a subselect to get the result set you want. If you want to know which snags were open up till a certain date add a where clause to the subselect (eg where checkdate < date '2009-09-07') SELECT pr.* from ProtocolReport pr JOIN (SELECT snag_GUID,MAX(checkdate) maxdate FROM ProtocolReport GROUP BY snag_GUID) pr1 on pr.snag_GUID=pr1.snag_GUID AND pr.checkdate=pr1.maxdate WHERE snag_STATE='OPEN' John |
Wed, Sep 16 2009 3:44 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | Michael,
<< thank you very much for this solution. It works perfect and 3 times faster than my command. I think I have to browse my SQL commands, to optimize also some other commands with this "subquery join". >> You can use sub-queries in a lot more places in ElevateDB than you previously could with DBISAM, or some other products. It takes some getting used to, but it can be very powerful stuff, especially with aggregation. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |