Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
MS VS rewrites Selects |
Sun, Jan 25 2009 12:54 AM | Permanent Link |
"James Relyea" | 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 |
Sun, Feb 8 2009 10:19 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Fri, Feb 13 2009 10:02 PM | Permanent Link |
"James Relyea" | 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 > |
Sat, Feb 14 2009 9:18 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |