function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
KerrySainsburyKerrySainsbury 

SFDC *just* as a database?

Hi Folks,
 
I'm working on a project which is considering trying to use SFDC purely as a database -- nobody cares about its CRM abilities, or user interface.
 
The intention is to use (as yet undetermined) generic forms engines and generic reporting tools as the front end to this SFDC database.
 
A casual look at DBAmp (as a Linked Server in SQL Server) suggests that it could be used to glue these front end tools to SFDC, but a closer reading of the documentation suggests that this will incur large performance issues as the database grows in size -- because of the way that SQL Server will first pull down the entire content of the table being queried or updated.
 
I recognise that the solution to this is to rewrite the query to use the SQL Server's 'OPENQUERY' syntax, but it seems (to me) highly unlikely that an off-the-shelf forms engine or reporting tool would be capable of generating SQL with this 'OPENQUERY' syntax.
 
Have you encountered this sort of use of SFDC before? Is there something that I'm missing?
 
If there isn't any obvious solution to the issue above, I guess our options are:
 
- Using DBAmp to replicate data down to SQL Server, and just using the 'generic' tools against SQL Server
- Finding SFDC / DBAmp 'aware' tools for the forms and reporting engines (any pointers?)
- Not use SFDC like this
 
Has anybody been down this path before? Any suggestions?
 
Thanks,
Kerry
Ron HessRon Hess
There are several ways to use Apex as a database platform, many designs use tools which can consume the Salesforce WSDL and then genrerate forms or libs to integrate the salseforce data into a desired package.
forceAMPforceAMP
"SQL Server will first pull down the entire content of the table being queried or updated. "
 
This is not true.  The Distributed SQL optimizer will analyze the query statement and try to pull down only the data that is needed. The primary issue is the sargablility of the where clause predicate. 
 
For Update statements,  there are different techniques to enhance performance.  A lot of the decisions depend on whether you are using SQL 2000 or 2005.
 
If you have more questions about a specific use case, please let me know. Or better yet, take advantage of the free 30 day trial and try out the tools you would like to use on a fully functional installation.
 
Bill Emerson
DBAmp developer
KerrySainsburyKerrySainsbury

Hi Bill,

The DBAmp documentation (page 14, "Updating tells me that:

 
KerrySainsburyKerrySainsbury

Hi Bill,

The DBAmp documentation (page 14, "Updating and tells me that:

 
KerrySainsburyKerrySainsbury

Let's try that again... :-)

page 14 "Updating and Deleting rows using SQL" of the DBAmp Install Guide, using the following SQL sample:

Update SALESFORCE…Account 

   Set AnnualRevenue = 4000

          Where Id='00130000005ZsG8AAK'

Says "...that SQL Server will retrieve the entire Account table from Salesforce and then search for the one row that has the Id of 00130000005ZsG8AAK. Then, SQL Server will update the AnnualRevenue of that row"

It then says "Obviously, this UPDATE statement has poor performance which gets worse as the size of the Account table grows"

It goes on to explain how this performance issue can be removed using SQL Server's "OPENQUERY" clause, eg:

Update OPENQUERY(SALESFORCE,

     'Select AnnualRevenue from Account

      where Id=''00130000005ZsG8AAK'' ')

     set AnnualRevenue = 4000

Now this is entirely fine if I am in control of the SQL being used to query SFDC, but if I'm using some generic forms tool that is using simple ANSI SQL, then it seems to me that this isn't going to work very well. What I'm hoping to get from this group is confirmation that I've not missed something for this specific use-case.

Can I take it that I would get far better performance if I was talking to SFDC using OPENQUERY-ised SQL (possibly wrapped in a view), or by using direct webservices calls?

Also, how does the decision to use SQL Server 2000 vs. 2005 affect this?

I am working with a trial DBAmp now, but it turns out that my developer SFDC account maxes out at 2mb of space so isn't a great way to prove performance / scaleability issues :-)  I'm working on getting more space!

Thanks very much for your time. It's greatly appreciated.

Kerry
forceAMPforceAMP

Hi Kerry,

Based on your comments and your need to work with any 'generic' forms tool, I do not believe that the DBAmp / SQL Server solution is right for you.

Thanks,

Bill Emerson

forceAmp.com

 
RenatKhasanshynRenatKhasanshyn
Kerry, perhaps you could try Apatar, an open source data integration tool that has "Insert" and "Update" features within its salesforce.com connector. You can find more information on AppExchange or download it directly from Apatar's website http://www.apatar.com/data_integration_for_salesforce_com.html
 
Take it for a spin, perhaps it's performance would address your expecations. Should it not, post a trouble ticket at http://www.apatar.com/forums/viewforum.php?f=14 and my developer will take a look at the problem with the goal to see if Apatar can help.
Goku ZeusGoku Zeus
Resin and hardener are the two components of epoxy adhesive, a form of glue. For the purpose of forming a solid and long-lasting connection, these two components are combined in a certain ratio. https://www.epoxyadhesiveglue.com/