When using MS VS2008, and creating a New Query, Select statements are being
rewritten with ElevateDB. I compared the behavior against VistaDB, and it
does not happen.

The following  Select statement is what I pasted in to VS's QBE:
SELECT        tCorps.varFullName, tPhNums.idPhNumId
FROM tCorps inner join tPhNums on tCorps.idCorpId=tPhNums.idCorpId
inner join tPhNums_Sec on tPhNums.idPhNumId=tPhNums_Sec.idPhNumId
inner join tsecGrp_tUsers_XRef on
tPhNums_Sec.idSecGrpid=tSecGrp_tUsers_XRef.idSecGrpId
where tCorps.varFullName like 'c%'


It rewritten in VS to become:
SELECT        tCorps.varFullName, tPhNums.idPhNumId
FROM            tCorps, tPhNums, tPhNums_Sec, tSecGrp_tUsers_XRef
WHERE        tCorps.idCorpID = tPhNums.idCorpId AND tPhNums.idPhNumId =
tPhNums_Sec.idPhNumId AND
                        tPhNums_Sec.idSecGrpId =
tSecGrp_tUsers_XRef.idSecGrpId AND (tCorps.varFullName LIKE 'c%')


My problem is with performance. The latter will hang the ElevateDB Unicode
server with my CPUs pegged @ 100% for several minutes before it errors out.
The  query I pasted in using Inner Joins returns results in <1 second.

Had I not noticed this behavior, I was beginning to think the product was a
joke because working selects in other DBs were hanging the Unicode server @
100% for minutes without any way to cancel the run on process(es).

Does anyone happen to know of any possible work arounds? I'd even settle for
some type of a 3rd party QBE that works with ElevateDB.

Thanks


jr

James,

<< When using MS VS2008, and creating a New Query, Select statements are
being rewritten with ElevateDB. I compared the behavior against VistaDB, and
it does not happen. >>

Are you saying that VistaDB queries that use the SQL-92 INNER JOIN syntax
are *not* changed to use SQL-89 join syntax ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Yes I am saying that.

I ran several selects with inner joins with 4-6 tables involved. VistaDB is
not optimized
at all for inner joins! I sat staring at an hourglass for over 6 minutes
with a pegged CPU before my results were returned. Changing the syntax to
what it looks like below returned my results in <1 second. Same holds true
for AdvantageDatabase should anyone else be wondering.

select field1, field2
from table1,table2
where table1.field3=table2.field4

I don't know which SQL standard's syntax this is.


jr



"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:2440C546-8485-4EFD-BEB7-7168A46D4DDB@news.elevatesoft.com...
> James,
>
> << When using MS VS2008, and creating a New Query, Select statements are
> being rewritten with ElevateDB. I compared the behavior against VistaDB,
> and it does not happen. >>
>
> Are you saying that VistaDB queries that use the SQL-92 INNER JOIN syntax
> are *not* changed to use SQL-89 join syntax ?
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com
>

James,

<< Yes I am saying that. >>

As indicated via email:

You'll be happy to know that I corrected both the GUID and the Query
Designer join issues, and a fix will be available hopefully by this evening
in a new build.  The Query Designer join issue was related to a bug in the
Visual Studio integration documentation:

http://social.msdn.microsoft.com/Forums/en-US/vsx/thread/c71863b4-bdd7-46c0-a189-c8f20b106ed3

Specifically, the message here:

"1.  The query designer uses ODBC escape sequences in JOIN operations when
you try and design a query using multiple tables.  The only way for it NOT
to do this is to actually have a DataSourceInformation derived-class
referenced in the registry key.  Just having a bunch of explicit entries in
the registy under the
Dataproviders\Guid\SupportedObjects\DataSourceInformation subkey isn't
enough.  You NEED a class, even an empty no-implementation class to get the
query designer to use non-ODBC join syntax.
To test that, just compile Sample2 from the DataSDK.  Create a connection,
then design a query.  JOIN syntax is proper (non-ODBC).  Now close down VS,
edit the registry and remove the reference to the SqlDataSourceInformation
object as the default value of the DataSourceInformation subkey for that
provider.  Re-run VS, and try and design a query.  It'll use ODBC escape
sequences."

This was not documented as necessary in the DDEX (VS data designer
integration) documentation.  The documentation only indicated that you
needed the registry entries, which is all that we initially provided.

You don't really need to understand any of the above, but I thought that I
should provide the rationale for the initial behavior.

--
Tim Young
Elevate Software
www.elevatesoft.com