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

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

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

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

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

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

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

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