Download
optimizing database access and diving into net sqlclient n.
Skip this Video
Loading SlideShow in 5 Seconds..
Optimizing database access and diving into .Net SqlClient PowerPoint Presentation
Download Presentation
Optimizing database access and diving into .Net SqlClient

Optimizing database access and diving into .Net SqlClient

134 Vues Download Presentation
Télécharger la présentation

Optimizing database access and diving into .Net SqlClient

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Optimizing database access and diving into .NetSqlClient Mladen Prajdić SQL Server MVP mladenp@gmail.com @MladenPrajdic

  2. About me Welcome to Slovenia The sunny side of Alps

  3. DisclaimerI use SELECT * in demos only forbrevity 

  4. Agenda • Building foundation... Client Data Access • New exciting stuff! AsyncSqlDataReader • Consistency is the key! TransactionScope • Reduce, Reuse, Recycle! Connection Pooling • Make it go faster! Batching Commands • What’s it doing? Statistics • Multiple interleaves? MARS

  5. Correct Connection Use using (SqlConnection conn = newSqlConnection(_connString)) { conn.Open(); // dbaccesscodehere // return data reader or objects }

  6. Client Data AccessBuilding the foundation...

  7. TDS Data Flow “Done” token Result set “Done” token Result set “Done” token Result set using (SqlCommandcmd= newSqlCommand("SELECT * FROM Table1; SELECT * FROM Table2", conn)) { SqlDataReaderrdr= cmd.ExecuteReader(CommandBehavior.CloseConnection); // return the list of data objects returnCreateListOfBusinessObjects(rdr); // or return the SqlDataReader itself returnrdr; }

  8. TDS Data Flow “Done” token Result set “Done” token Result set “Done” token Result set Row Data Row Data Row Data “Row” token “Row” token “Row” token

  9. TDS Data Flow “Done” token Result set “Done” token Result set “Done” token Result set Row Data Row Data Row Data “Row” token “Row” token “Row” token Column Data Column Data Column Header Column Data Column Header Column Header Small datatype INT Large datatype NVARCHAR(MAX) Medium datatype CHAR(50)

  10. SqlDataReader Data Access • Default access (Non-Sequential) • Sequential access // default = non-sequentialaccess varrdr=cmd.ExecuteReader(); // sequentialaccess varrdr=cmd.ExecuteReader(CommandBehavior.SequentialAccess);

  11. Non-Sequential (Default) Access 1 2 0 Column Data Column Data Column Header Column Data Column Header Column Header 1 2 0 Column Data Column Data Column Header Column Data Column Header Column Header We can go back and read all previous columns’ data

  12. Non-Sequential (Default) Access • The full row is stored in client memory • Move back and forward in the row • Possible scalability issues • Possible Out of Memory exceptions

  13. Sequential Access 1 2 0 Column Data Column Data Column Header Column Data Column Header Column Header 1 2 0 Column Data Column Data Column Header Column Data Column Header Column Header ERROR Forward reading only. Trying to go back throws an error.

  14. Sequential Access • Only the current value is stored in client memory • Move only forward in the row • Get* methods for getting data • GetBytes, GetCharsfor BLOBs • Save to a stream by reading it in chunks // get first column value that is int rdr.GetInt32(0); // get second column value that is binary byte[] buffer=newbyte[100]; // loop this to get the whole column out in chunks rdr.GetBytes(0, 100, buffer, 0, buffer.Length); // save the byte buffer to file or something

  15. Sequential Access • Full streaming support in .Net 4.5 • GetStream, GetTextReader, GetXmlReader • No more chunking

  16. Sequential Access SqlCommandreadCmd=// get varbinary(MAX) from source table newSqlCommand("SELECT BinData FROM Source", readConn); SqlCommandwriteCmd=// insert varbinary(MAX) into target table newSqlCommand("INSERT INTO Target(BinData) VALUES (@BinData)", writeConn); // Size is set to -1 to indicate "MAX" SqlParameterstreamParameter= writeCmd.Parameters.Add("@BinData", SqlDbType.Binary, -1); SqlDataReaderreaderSource= readCmd.ExecuteReader(CommandBehavior.SequentialAccess); while (readerSource.Read()) { // Grab a stream to the binary data in the source database StreamdataStream=readerSource.GetStream(0); // Set the parameter value to the stream source that was opened streamParameter.Value=dataStream; // send the data to the target database writeCmd.ExecuteNonQuery(); }

  17. AsyncSqlDataReaderNew exciting stuff!

  18. Old “Run in Background” Ways SqlCommandcmd=newSqlCommand("SELECT * FROM Table", conn); // either this or a background worker IAsyncResultresult=cmd.BeginExecuteReader(); // for the reader to complete while(!result.IsCompleted) { // do UI operations without problems } using(SqlDataReaderreader=cmd.EndExecuteReader(result)) { // show readerresults ShowDbOperationResult(reader); }

  19. What exactly is the new stuff then? • Make C# async programming easier • Since .Net 4.5 • Supports easy cancellation • Uses the new async/await keywords • Reader’s *Async methods privateasyncvoidCustomEventHandler(object sender,EventArgse) { textBox.Text="Start long operation."+Environment.NewLine; // await returns control to the caller // this means that the UI won't be blocked // since CPU is free to do its stuff doubleduration=awaitMyLongIOBoundOperation(); // finish op and show duration in UI textBox.Text="Completed in "+ duration +" sec"; }

  20. What is it good for? • Make code async without complex threading code • No threads created by default • Use default for IO-bound workload • Use Task.Run to create background thread for CPU-bound workload • Supported by Entity Framework 6 • For more in depth details on this, search for “Asynchronous Programming with Async and Await” • http://msdn.microsoft.com/en-us/library/hh191443.aspx

  21. What to use when? • Prefer NextResultAsync in either access modes • TDS tokens between result sets are processed in async TDS Tokens TDS Tokens Result set Result set Result set Async Async

  22. What to use when? • Prefer ReadAsyncin either access modes • TDS tokens betweenmultiple packets are processed in async Single Row TDS Tokens Data Packet Data Packet Data Packet TDS Tokens Async Async

  23. What to use when? • Use IsDBNullandGetFieldValue<T> • If ReadAsync() in Default (Non-Sequential) mode • Since data is already in the client buffer • IsDBNullAsyncandGetFieldValueAsync<T> • Sequential mode • target column is large • needed to read past large columns • rdr.GetStream(i).CopyToAsync(outputStream) • for huge columns in Sequential mode

  24. DEMO

  25. TransactionScopeConsistency is the key!

  26. TransactionScope using (TransactionScopetxScope= newTransactionScope()) { using(SqlConnection conn2012AW = newSqlConnection(_connString2012AW)) { // do stuff } txScope.Complete(); }

  27. TransactionScope • Easy to use • Keeps code simple • Great transaction management out of the box • MSTDC escalation • Always, unless connecting to the same database with non concurrent connections • sys.dm_tran_active_transactions.transaction_uow • Default isolation level is SERIALIZABLE

  28. DEMO

  29. Connection PoolingReduce, Reuse, Recycle!

  30. Connection Pooling • Connection pools created • per process • per application domain • per connection string • per Windows identity (if integrated security) • Default max pool size = 100 • Audit Login, Audit Logout Events • EventSubClass • 1 = Nonpooled • 2 = Pooled

  31. Connection Pooling Resets • sp_reset_connection • Resets the connection for reuse • A long list of what it does on the next slide for reference • Does NOT reset the isolation level • Always explicitly specify the isolation level

  32. Connection Pooling Resets • All error states and numbers (like @@error) • Stops all EC's (execution contexts) that are child threads of a parent EC executing a parallel query • Waits for any outstanding I/O operations that is outstanding • Frees any held buffers on the server by the connection • Unlocks any buffer resources that are used by the connection • Releases all allocated memory owned by the connection • Clears any work or temporary tables that are created by the connection • Kills all global cursors owned by the connection • Closes any open SQL-XML handles that are open • Deletes any open SQL-XML related work tables • Closes all system tables • Closes all user tables • Drops all temporary objects • Aborts open transactions • Defects from a distributed transaction when enlisted • Decrements the reference count for users in current database which releases shared database locks • Frees acquired locks • Releases any acquired handles • Resets all SET options to the default values • Resets the @@rowcount value • Resets the @@identity value • Resets any session level trace options using dbcctraceon()

  33. DEMO

  34. Batching CommandsMake it go faster!

  35. Batching Commands • Optimizing network round trips • Consider Bulk Insert if possible • Streaming data insert • SqlBulkCopy Class • 10k inserts = 10k round trips • 1000 x 10 batches = 10 round trips • 1000 CSV SQL statements in one command • Or… a hidden gem 

  36. DEMO

  37. StatisticsWhat’s it doing?

  38. Client Statistics • Enable per connection • Cumulative • Good for tracking the actual db access from app • Save to a queue, process off-peak • Analyze historical access data

  39. DEMO

  40. MARSMultiple interleaves?

  41. Multiple Active Result Sets (MARS) • Statement multiplexing/interleaving • SELECT, FETCH • RECEIVE, READTEXT • BULK INSERT / BCP • Other statements must complete • Transaction savepoints aren’t allowed • Usually used for SELECT N+1 scenarios

  42. DEMO

  43. ?