2

I wonder about that can I write native SQL to add or delete operations instead of using Query Expression or FetchXML etc. I know Query Expression is very useful but my real concern is performance and I've thought writing SQL can be faster than the others.

zato
  • 133
  • 1
  • 10
  • 1
    You can't use `QueryExpression` or `FetchXml` to create/delete database rows - they're query mechanisms. And for querying, these mechanisms will eventually translate into direct SQL, so there's virtually 0 speed difference between direct SQL access and the CRM API (for the queries supported by the various SQL providers). – Peter Majeed Jul 05 '12 at 13:35
  • @PeterMajeed Somehow, I failed to pick up on that (!) Seems that I hit the red panic button without spotting the subtleties ;-) Good spot! – Greg Owens Jul 05 '12 at 14:51
  • @Greg: I think your red panic button is worth the accepted answer + upvotes anyway. Despite the API's SQL CRUD limitations, trying to cheat with native SQL is more of a headache than a help in any enterprise solution. – Peter Majeed Jul 05 '12 at 17:39
  • Hi! you all right it causes unwanted results.My question is that I want to update huge amount of crm activitis, is there any difference between service.update() and using SQL directly in point of speed or performance.Please forget the dont use warnings. – zato Jul 06 '12 at 07:08
  • Undoubtedly there will be some overhead when comparing the supported (web service) methods over direct SQL. As Peter mentioned, the supported mechanisms ultimately translate to SQL statements so by definition it will be slightly slower. I doubt that you will find anyone with metrics to quantify this because (and sorry to re-iterate the point) it's so unsupported, nobody would do it. I think the way to go is to start with the supported method, see how long that takes and if it is unacceptable, post a new question for ideas on how to improve thoughput. – Greg Owens Jul 06 '12 at 08:00
  • 1
    Thanks for your interests and clear responses. I've already done an application with using CRM API (I also accept that using the API is the best way) but my superior insists me to research about using SQL, so days full of misery are waiting to me :) – zato Jul 06 '12 at 08:13
  • @zato: Thanks for clarifying. I recommend using SQL Profiler to see all the SQL that goes behind a single update segment, then do it again for multiple updates. If the SQL is manageable, and you're feeling good about your chances, it's *your* database, so you can do whatever you want with it. – Peter Majeed Jul 06 '12 at 14:34
  • 1
    @Peter, I discovered SQL Profiler and for one update there are 3 updates on Profiler side. You're right, I must be sure that what I do while playing my db. Thanks again for your response. – zato Jul 06 '12 at 14:50

3 Answers3

10

To put it simply, using direct SQL (especially for create/update actions) is not supported. DO NOT DO IT!

The database model for CRM is complex and updates to data can have effects that extend beyond a simple update to a single table or two.

my real concern is performance

Have you validated this concern? Take a look at this link which documents performance tests on CRM. This is an enterprise-level, scalable platform. If you have proven performance issues then perhaps your code needs optimising or your kit needs beefing up...? :)

Greg Owens
  • 3,878
  • 1
  • 18
  • 42
4

I totally agree with Greg's answer, this is just as a side note regarding performance. If you really are seeing "performance issues", maybe you should spend your time focusing on seeing if adding an index would be helpful. Although database indexes aren't included within CRM solutions, and will require manual propagation between dev, qa, staging, and prod environments, and are only supported for on-site installations, they can make some queries, 10 or 100 times faster... (of course if they are abused, they can slow everything down as well. Know what you are doing before you use them)

Daryl
  • 18,592
  • 9
  • 78
  • 145
0

On top of what @Greg & @Daryl have said, when you say performance do you mean its quicker for you to write sql?

Regardless, CRM has some unique ways of doing things.

For example activate/deactivate a record, invoice related actions or the way CRM converts an Opportunity.

It's not that hard to do. You should spend some time in the sdk...

Christian Payne
  • 7,081
  • 5
  • 38
  • 59
  • i intend to say that using BULK INSERT in MSSQL is faster than service.create(). However writing sql correctly is so hard – zato Jul 17 '12 at 09:38