The Developer’s Community

1. PR.Net • 2011 Topics • Team Foundation • WPF • Silverlight The Developer’s Community

2. May 25, 2011 PR.Net Presents Improving Performance inClient-Side Data Access 26 Tips to consider for Design and/or Performance Eduardo Sobrino, MVP 2011 PR.Net Contributor

3. Client – Server Architecture Cloud Client MS-SQL Server

4. Where to Start? Good performance start at the server (database/MsSql-Instance), with a good… • Database Design; • Data Access Security and Strategy; • Database Developers & DBA’s working together; • Well configured instance and database.

5. after the server,What about the Client-Side? Good performance start at… • Data Access Security and Strategy; • Database conscious Developer; • Well prepared requests that minimize network traffic and maximize on data catching as/when possible.

6. Where do you start today? • Evil doers • Lucifer • El Diablo If you don’t know, have doubts about what is the best way to access your data in VS-2010… What you will do? Ask the experts and industry leaders!

7. What “leaders” are telling you? Tell me about the last Web-Cast, Blog or book that you view or read about Client-Side Data-Access… • Tell me details about the Client-Side data-access sample they were showing. • What Client-Side data-access technology they where promoting? • What coding practice they where using?

8. What “leaders” are telling you? • Industry / technology “leaders” advice you about how you should be coding or what technology you should be using. • Those “leaders” show or provide you with demos, white-papers, web-casts, books on what you should be (and how) coding to access the data in your Client-Apps.

9. What “leaders” are telling you? • Are promoted best practices “Best Practices”? • If (today meaning now) you search for a sample on how to access the data in your vs-2010 project using MS-SQL, Oracle, or other you will likely see that most of those available in the web will show-case EDM, LINQ 4 SQL, or RIA-Services. • Does what is shown in the endless Web-Casts, Forums, Blogs, and other resources is what you should be doing?

10. CurrentMicrosoft Data Access Technologies

11. ADO(including ADOMD and ADOX) • OLE DB(including OLE DB Core Services, SQL Server OLE DB Provider, Oracle OLE DB Provider, OLE DB Provider for ODBC Drivers, Data Shape Provider, and Remote Data Provider) • ODBC (including ODBC Driver Manager, SQL ODBC Driver and Oracle ODBC Driver) CurrentMicrosoft Data Access Technologies

12. Anatomy of Data Providers All Native and Manage Providers Require to Manage • Connection – Get access to SQL Instance • Command – Prepare Request Batch and Parameter Binding • Data-Reader – Data Binding and Fetching • Clean-Up – Free allocated resources…

13. Tradeoffs, Things to Balance… While Coding Consider… • Performance– Execution/Runtime Speed. • Control – How much control do you have and how easy to manage the required control is provided by API. • Security – Ability and control that you will have to secure your requests, data or objects. • Maintainability – After coding how easy can be maintained. • Agnosticity– Your ability to move from one technology to another. • Changeability – How easy can you adapt your code to deal with new requirements, situation or new data provider feature. • Ease of Coding – How easy is to code. • Deployment – How easy is to deploy.

14. Native vs. Manage Providers FAQ • Is (C/C++) Native Faster? The performance of a Client-App database request is greatly affected by network issues (latency, band-width, others) where the request will be travelling and servicing the request on the remote host. Therefore most of the time spent by your Native or Manage request will be spent on the network and the remote-server while serving the request. Whatever time difference between Native or Manage request is left will depend on the ability to bind and consume the fetched data.

15. Native vs. Manage Providers FAQ • Is (C/C++) Native Data-Access coding difficult? If you use “raw” ODBC or OLE-DB API’s and you don’t have any experience, it will be extremely hard and you will find your self that to do a simple thing will take quite a few lines of code. If you need or like to code in ODBC, OLE-DB or Oracle-OCI API’s you could download wrapped classes for each available in cpp-mini-framework in Codeplex. This framework have classes and types that substantially ease coding on those. Samples for various situation are provided within the Framework to get you started.

16. C++ voidTestOdbcClientFetchRecords() { // prepare datasource Kif::Win32::Data::DataSourceInfodsrc; dsrc.SetSourceName(_T("DataDb")); dsrc.AuthenticationOption = Kif::Win32::Data::Authentication_NT; // connecttodatabase Kif::Win32::Data::OdbcClient::OdbcConnectioncnt; cnt.Database.Verbose = Kif::Win32::Diagnostics::Verbose_Trace; if (cnt.Connect(dsrc)) { // **** SEE CODE ON NEXT SLIDE **** // cmd.Dispose(); cnt.Disconnect(); } } // end of TestOdbcClientFetchRecords Native Cpp-MiniFramework sample CONNECT CLEAN-UP

17. C++ COMMAND Kif::Win32::Data::OdbcClient::OdbcCommandcmd( &cnt,_T("{ CALL SubmissionRegistryGetRecords (?) }"), Kif::Win32::Data::Command_StoredProcedure); TCHAR *sid = _T("01234"); TCHAR *estr = _T(""); cmd.Parameters.Add(_T("@SubmissionId"),sid,20); Kif::Win32::Data::OdbcClient::OdbcParameter *c1,*c2; Kif::Win32::TIMESTAMP_STRUCT dtime; //cmd.Columns.Add(_T("CreatedDate",&dtime); c1 = cmd.Columns.Add(_T("SubmissionId"),estr,20); c2 = cmd.Columns.Add(_T("SubmissionFileName"),estr,20); Kif::Win32::Data::OdbcClient::OdbcDataReader *r = cmd.OpenReader(); if (r != NULL) while(r->Read()) { Kif::Win32::Diagnostics::Log::ConsoleWriteMessage( c1->AsWideString()); Kif::Win32::Diagnostics::Log::ConsoleWriteMessage( c2->AsWideString()); } Native Cpp-MiniFramework sample PARAM-BINDING DATA-READER

18. How to develop my apps? You Provide More Coding Details Client-Side Options • Data Models – DataSet, EDM, LINQ for SQL • WCF RIA Services– Bridge Technology between ASP.NET and Silverlight • POCO – Plain-Old CLR Objects • DbCommon– Use of ADO.Net Provider Factories • ADO.Net– Direct Use of Provider • C++ Native Access – ODBC, Ole-DB, Oracle OCI, Sybase Open-Client, others… Higher Abstraction (Automated)

19. For Design and/or Performance Consider… • Learn about your options before you begin your project – Don’t hurry and start your App before carefully considering your Server and Client side’s data access options. Consider maintainability, performance, and costs. Try EDM, LINQ4SQL, RIA-Services and others before desiding. • Use Stored Procedures avoid Ad-Hoc Queries. • Pack as much requests as possible in a single batch instead of performing multiple requests.

20. For Design and/or Performance Consider… • Use connection pooling. • Use the data access type appropriate for the task at hand. Don’t make the mistake of using DataSet’s for every job or situation. In ASP.NET having just the DataReader is better since you can use it directly to write your HTML or ASPX avoiding intermediate copies of the data that will delay the operation.

21. For Design Consider… • Write classes that their sole purpose is to service data or performs a data related service. Don’t mix data access with anything else especially UI related objects. It is better if you write a class library that contains all of those data access, management or manipulation related classes. This could be your data layer.

22. For Design Consider… • Separate the data from its management, manipulation or access requests. For example if you need a Persons Class name it PersonInfo for the data and use another class let’s say PersonRecord to write the needed GetDataReader, and other data access and management operations/requests. This will help you in keeping the needed resources required for preparing resources from the actual data that you want to manage.

23. For Design Consider… • Never write data-access code only useful in a particular UI / environment. Contrary to what you heard (or will) from many Microsoft “Experts” Advice, Articles and others that do advise you to use a particular technology, helper or other resource for example ASP.NET data Catching, you should use UI agnostic resources, classes. If those classes don’t exist implement your own. You will thank me later when you decide to switch to a different UI and you don’t need to write code to do just the exact thing for example catch your data.

24. For Performance Consider… • Do Client-Side data Catch as much as possible or if possible.Don’t download data lists for your drop-down/combo boxes or lists more than once. Just download it once and store it on a local cache. This will reduce the unnecessary network chattering for common needed data-lists requests that any way hardly change.

25. For Performance Consider… • Train your end-users to provide as much details as possible when specifying the search criteria to keep result sets small. • Limit the size of the max result-set size that you will support.It is probable that if the end-user is searching let’s say for a person and needs a result-set size of 1000 rows to find it, it means that this user is fishing and don’t know anything about what is searching for.

26. For Design Consider… • Consider the use of the (agnostic) data provider factory and not direct access to a particular provider.Using the provider specific SqlClient (Oracle or others) will be a good performance-wise decision still you may end up with the need to use another and then you will suffer the consequences while moving away to this specific provider. This is especially true and needed in Enterprise environments / Apps.

27. For Performance Consider… • Don’t support (avoid) long running transactions that take more than a few seconds in your OLTP. Off-load those to a report or processing server moving the data they need to perform their calculations. If those are for accumulative, reporting and statistical data consider implementing a Data Warehouse.

28. For Performance Consider… • Don’t use EDM (“Entity Data Model”) or LINQ of SQL unless you really needs any of those. There are many Web-Cast’s and other web-resources that show how easy is to access your data using EDM or LINQ4SQL and use those to quick build UI Apps in Win-Forms, WPF, ASP.NET, Silverlight, and lately in Light-Switch. That is nice still you are a good candidate to have a bad performance-wise app. Understand that I do use EDM and at times LINQ4SQL but in those cases that I understand that are beneficial (meaning in a very few circumstances).. Your loose control and direct access to fetched data…

29. For Performance Consider… • As possible write Async Data Access classes. Your applications will perform a lot better in all circumstances using Async data access instead of blocking the App while waiting for a request to be served. Your loose control and direct access to fetched data…

30. End of Tips… Let’s hear about yours…

31. Developing a “new” Managed DB Client App.today what will be your options… • DB-Common Provider Factory • POCO • EDM (for local db-access) • + RIA-Services (Silverligh) • + WCF RESTfull Services • T4 (for Automation) What about your options?

32. Q&A Eduardo Sobrino . esob@openk.com . 787-764-1942