Transcript Darren Shaffer Microsoft MVP Handheld Logic WMB403 Agenda SQL Compact Edition Architecture SQL Compact Edition Sweet Spot Measuring & Understanding Performance Optimal Query & DML Performance High-Performance Development Optimal.

Darren Shaffer
Microsoft MVP
Handheld Logic
WMB403
Agenda
SQL Compact Edition Architecture
SQL Compact Edition Sweet Spot
Measuring & Understanding Performance
Optimal Query & DML Performance
High-Performance Development
Optimal Data Synchronization
SQL Compact Edition Architecture
Native Stack
Managed Stack
VS 2005/2008 (C++)
VB.NET & C#
ADO.NET
OLEDB CE
SQL Server CE
Data Provider
OLEDB Provider
CLR / .NET CF
Query Processor
Storage Engine / Replication Tracking
SQL CE Query Processor
A Heuristic, Optimizing QP in under 1MB!
Heuristic
Rewrites query into semantically equivalent form that leads to a better execution plan
Based on syntax
Adjacent inner joins are merged together so alternative join orders may be considered
... FROM (Table_1 INNER JOIN Table_2 ON Table_1.Col = Table_2.Col) INNER JOIN Table_3 ON
Table_1.Col = Table_3.Col ...
... FROM Table_1, Table_2, Table_3 WHERE Table_1.Col = Table_2.Col AND Table_1.Col =
Table_3.Col ...
Cost-Based Optimization
Determines
Base table scan type (File scan / Index scan)
What indexes, if any, are used
Join order, join algorithm
Sort / filter placement
How it works
Enumerates a selected subset of all possible execution plans
Finds out the plan with lowest estimated cost
Generates executable data structures that implement the plan
SQL CE Storage Engine
Completely New in v3.1
4KB Page Size
ACID Transaction Support
Smart Device Awareness
Row-Level Locking
Automatic Reuse of Empty Pages
Improved Tools for Database Health
and Maintenance
Hardware/Drivers
OEM/IHV Supplied
BSP
(ARM, SH4, MIPS)
OEM Hardware and
Standard Drivers
Windows XP DDK
Device Building Tools
Windows Embedded
Studio
Programming Model
Data
Platform Builder
Lightweight
SQL Server 2005 Express Edition
EDB
SQL Server Compact Edition
Relational
Native
Managed
Server Side
Multimedia
Location Services
Development Tools
Communications
and Messaging
Standard PC Hardware
and Drivers
Win32
MFC 8.0, ATL 8.0
.NET Compact Framework
ASP.NET Mobile Controls
.NET Framework
ASP.NET
Windows Media
DirectX
MapPoint
Visual Studio 2005/2008
Internet Security and Acceleration Server
Exchange Server
Live Communications Server
Speech Server
Device Update Agent
Management
Tools
Image Update
Systems Management Server
Microsoft Operations Manager
Software Update Services
Actual SQL CE Newsgroup Post:
“Runs fine on SQL Server 2000, but not under SQL CE…”
SELECT IMEI, ProductCode, Quantity FROM (SELECT NULL AS IMEI, product AS
ProductCode, (physicalqty - allocatedqty) AS Quantity FROM importstock
WHERE (NOT mpstype IN(N'U', N'C', N'M', N'X', N'Y', N'P')) AND product IN
(SELECT ProductCode FROM (SELECT importstock.product AS ProductCode FROM
StockCountSchedule INNER JOIN StockCountProductCategories ON
(StockCountSchedule.ID = StockCountProductCategories.ID) INNER JOIN
importstock ON (StockCountProductCategories.Product_Type =
importstock.product_type) WHERE (StockCountSchedule.IsRecount = 0) AND
(StockCountSchedule.ID = 121231) UNION SELECT ProductCode FROM
StockCountSchedule INNER JOIN CrossDevice_ProductsToRecount ON
(StockCountSchedule.ID = CrossDevice_ProductsToRecount.StockCountID) WHERE
(StockCountSchedule.IsRecount = 1) AND (StockCountSchedule.ID = 121231)) AS
StockCountProducts) UNION SELECT IMEI.imei AS IMEI, NULL AS ProductCode,
NULL AS Quantity FROM importstock INNER JOIN IMEI ON importstock.product =
IMEI.product WHERE (mpstype IN(N'U', N'C', N'M', N'X', N'Y', N'P')) AND
importstock.product IN (SELECT ProductCode FROM (SELECT
StockCountSchedule.ID AS StockCountID, importstock. product AS ProductCode
FROM StockCountSchedule INNER JOIN StockCountProductCategories ON
(StockCountSchedule.ID = StockCountProductCategories.ID) INNER JOIN
importstock ON (StockCountProductCategories.Product_Type =
importstock.product_type) WHERE (StockCountSchedule.IsRecount = 0) UNION
SELECT StockCountSchedule.ID AS StockCountID, ProductCode FROM
StockCountSchedule INNER JOIN CrossDevice_ProductsToRecount ON
(StockCountSchedule.ID = CrossDevice_ProductsToRecount.StockCountID) WHERE
(StockCountSchedule.IsRecount = 1)) AS StockCountProducts)) AS
StockCountItems
SQL CE vs. SQL Express
The real story…
Footprint vs. capability
Schema complexity
Query complexity
Off-line experience
Data synchronization
Security
Ease of deployment
Mindset
Measuring Performance
Code instrumentation
System.Diagnostics.StopWatch (new in NET CF 3.5)
System.Environment.TickCount (.1 - .5 sec resolution)
System.DateTime.Now (1 sec or worse resolution)
.NET CF profiling tools
System Information on SOTI’s PocketController
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETCompactFramework\PerfM
onitor\  set (DWORD) Counters = 1
Performance Counters
http://blogs.msdn.com/davidklinems/archive/2005/12/09/502125.aspx
Remote Performance Monitor (.NET CF 3.5 PowerToys)
http://www.microsoft.com/downloads/details.aspx?FamilyID=c8174c14-a27d-4148-bf0186c2e0953eab&displaylang=en
SQL Server Management Studio
Display Query Execution Plan
Measuring Performance
Measurement tips
Discard the first measurement
Take sufficient number of samples
Restart the application between tests
Use realistic data, devices, and storage
Measuring SQL CE Performance
Darren Shaffer
Microsoft MVP
Handheld Logic
Optimal Query Performance
Big hitters
Match schema to SQL CE’s capabilities
Base Table Cursors/TableDirect
SqlCeResultSet versus SqlCeDataAdapter
Leverage useful indexes
Schema Tips
Minimize column count on tables
Use variable length columns, narrow as possible
Avoid Using:
Max
Footprint
ntext
nvarchar/nchar(big #)
varbinary/binary(big #)
536M
4000
8000
2n bytes
2n bytes
1n bytes
Consider de-normalizing schema
Keep JOINs to no more than 3 or 4 if possible
Pre-calculate, pre-aggregate when possible
Pre-Computing Impact
SELECT OrderID, SUM(UnitPrice * Quantity * (1.0 - Discount)) AS Total
FROM OrderDetails GROUP BY OrderID
SELECT OrderID, OrderTotal AS Total FROM Orders
On The Fly
8.94
Pre-Computed
3.97
seconds
Base Table Cursors/TableDirect
Bypasses the query processor
Returns all columns in a row
Fastest way to read from a table when you need all columns
Example:
// create and execute SqlCeCommand
SqlCeCommand cmd = new SqlCeCommand(“Authors",cnn);
cmd.CommandType = CommandType.TableDirect;
SqlCeDataReader dr = cmd.ExecuteReader();
// process results as usual
while(dr.Read())
{
MessageBox.Show("Name = " + dr["au_lname"]);
}
dr.Close();
dr.Dispose();
Seek/SetRange
Bypasses the query processor
Open a base table index
Fastest way to select a range of values
Example:
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = "Orders";
cmd.IndexName = "idxDateTime";
object[] start = new object[1];
object[] end
= new object[1];
start[0] = new SqlDateTime(2007, 1, 1);
end[0]
= new SqlDateTime(2008, 2, 3;
cmd.SetRange(DbRangeOptions.Match, start, end);
SqlCeDataReader dr = cmd.ExecuteReader();
dr.Seek(DbSeekOptions.FirstEqual, new SqlDateTime(2007,3,4));
while(dr.Read()) {// process results as usual }
Base Table Cursors/TableDirect
Base Table Seek versus Query
Base Table Seek
SELECT Query
47.904
5.069
2.3 0.642
10 Iterations (sec)
0.289
100 Iterations (sec)
2.592
1000 Iterations (sec)
SqlCeResultSet
Query Performance Similar to SqlCeDataReader
Excellent DML Performance
Bi-Directional Scrolling and Update in Place
private SqlCeResultSet resultSet = null;
private ResultSetView view1 = null;
private void _bindData()
{
this.command.CommandText = “SELECT * FROM Orders”;
ResultSetOptions options = ResultSetOptions.Scrollable |
ResultSetOptions.Updatable;
this.resultSet = this.command.ExecuteResultSet(options);
this.view1 = this.resultSet.ResultSetView;
int[] ordinals = new int[] { 1,3,5,8 };
this.view1.Ordinals = ordinals;
this.dataGrid.DataSource = view1;
}
ResultSet Vs. DataAdapter
1,078 Orders
SELECT * FROM Orders
SqlCeDataAdapter.Fill(DataSet)
SELECT * FROM Orders
cmd.ExecuteResultSet(ResultSetOptions)
SqlCeDataAdapter
SqlCeResultSet
1.759
1.079
seconds
Scrollability
SqlCeDataReader is forward-only
SqlCeDataAdapter SELECT does not leverage SetRange/Seek
SqlCeResultSet is scrollable & fast. This is even faster:
Example:
// Set the index range
cmd.SetRange(DbRangeOptions.InclusiveStart, start, end);
SqlCeDataReader dr = cmd.ExecuteReader();
// Seek to a value (customer name)
dr.Seek(DbSeekOptions.FirstEqual,”Shaffer”);
dr.Read();
// Process the row
// Seek to another value (customer name)
dr.Seek(DbSeekOptions.LastEqual,”Snerdley”);
dr.Read();
// Process the row
Leverage Useful Indexes
Aren't they all useful?
Useful = Selective and Chosen by the Query Processor
Selectivity is ratio of qualifying rows to total rows (low is good)
Index on Orders.OrderID is selective
Index on Orders.ShipVia is not selective
Use sp_show_statistics_steps ‘table’, ‘index’
SQL CE uses only one index per table in an execution plan
Indexes increase database size
Avoid indexing small tables; table scan is more efficient
Heavy DML, use fewer indexes
Heavy querying, use more indexes
Max of 249 indexes per table, 16 columns per index
Impact of Indexes
2,820 OrderDetails records
SELECT OrderID, ProductID FROM OrderDetails WHERE OrderID = 10900
ADD INDEX on OrderID
No Index
Indexed
.265
0.065
seconds
Query Performance
Miscellaneous recommendations
Limit Requested Columns
SELECT * FROM
Write SARGABLE Clauses
(70% better)
SELECT ColumnName FROM
(55% better)
SELECT OrderID FROM Orders WHERE DATEPART(YEAR,
OrderDate) = 1992 AND DATEPART(MONTH, OrderDate) = 4
SELECT Order ID FROM Orders WHERE OrderDate >=
'04/01/1992' AND OrderDate < '05/01/1992‘
NON-SARGABLE Clauses:
IS NULL, <>, !=, !>, !<, NOT, NOT EXISTS, NOT IN, NOT LIKE, LIKE %ABCD
Query Performance
Miscellaneous recommendations
JOINs versus SUBQUERIES
(88% better)
SELECT OrderID FROM Orders O WHERE EXISTS (SELECT
OrderID FROM OrderDetails OD WHERE O.OrderID =
OD.Order ID AND Discount >= 0.25)
SELECT DISTINCT O.OrderID FROM Orders O INNER JOIN
OrderDetails OD ON O.OrderID = OD.OrderID WHERE
Discount >= 0.25
Query Performance
Miscellaneous recommendations
Avoid Redundant DISTINCT
(55% better)
SELECT DISTINCT C.CustomerID, O.OrderID FROM Customers C
INNER JOIN Orders O ON C.CustomerID = O.CustomerID
SELECT C.CustomerID, O.OrderID FROM Customers C
INNER JOIN Orders O ON C.CustomerID = O.CustomerID
Use GetValues() versus GetXXX()
(17% better)
Index WHERE, ORDER BY, GROUP BY Columns
(39% better)
Optimum Query Performance
Darren Shaffer
Microsoft MVP
Handheld Logic
Optimal DML Performance
Big hitters
Pre-Load reference tables
Parameterized DML Queries (specify precision)
SqlCeResultSet update-in-place
Consider removing/re-adding indexes
Increase MaxBufferSize
Use faster storage
Optimum DML Performance
Darren Shaffer
Microsoft MVP, Chief Software Architect
Handheld Logic, LLC
High-Performance Development
Considerations
Version of SQL Compact Edition
Deployment Platform
SQL CE Connection String
Connection caching
Encryption
Options for deploying the “Starter Database”
Database maintenance
Recovery planning
High-Performance Development
What not to do…
Put SQL in the UI layer
Concatenate a bunch of strings to form SQL
Use string. Replace for parameter values
Use SqlCeDataAdapter (period)
Forget to close and dispose of SqlCeDataReaders
Forget to dispose of SqlCeCommands
Forget to use SqlCeTransactions for DML
Show users SqlCeExceptions
One connection, > 1 Thread
High-Performance Development
Client Application
Presentation
Logic
Data Access
DB Manager
SQL Compact
High-Performance Development
Darren Shaffer
Microsoft MVP, Chief Software Architect
Handheld Logic, LLC
Optimal Data Synchronization
Options
Batch-Mode SDF File Exchange
Remote data access
Merge replication
Custom web services
Sync services for ADO.NET (Devices)
New!
The Secret to Data Sync Success
Make a plan!
Database Table
Purpose
Table
All Columns Primary Indexes
Filtering Lookup/
Needed in
Required?
Key
Required? Possible? Reference
Local Cache?
only?
Add/Change Conflict Business Logic?
/Remove
Potential
Endpoints
CreditCardTypes
Enumerate valid
credit card types
Yes
Yes
GUID
No
No
Yes
server
none
N/A
NewSubscriptions
New subscriptions
are stored here
Yes
Yes
GUID
No
Yes
No
device only
none
Yes - authorize
credit card on
delivery to server
PostalCodes
Reverse Lookup City
and State based on
entering Zip Code
Yes
No
(can omit
Country)
GUID
Yes (PK is No
sufficient)
Yes
server
none
N/A
Products
Newspapers and
Yes
periodicals which
can be subscribed to
No
GUID
(can omit
PublicationT
ypeFk)
No
No
Yes
server
none
N/A
Yes
Yes
GUID
No
No
Yes
server
none
N/A
Promotions
A PremiumPack
Yes
offered for a specific
time period
Yes
GUID
No
No
Yes
server
none
N/A
PublicationTypes
Enumerate valid
publication types
No
N/A
N/A
N/A
N/A
N/A
N/A
N/A
N/A
Signatures
Subscriber's digital
signature
Yes
Yes
GUID
No
Yes
No
device only
none
N/A
Users
Authenticate the
mobile user
Yes
No (can
omit Name)
GUID
No
Yes
Yes
server
none
N/A
Vendors
Each user works for
a Sales Vendor
Yes
Yes
GUID
No
Yes
Yes
server
none
N/A
VendorsPromotions
Join Table
Yes
Yes
GUID
No
Yes
Yes
server
none
N/A
ProductsPromotions Join table
Choosing a Data Sync Strategy
CRITERIA
BEST CHOICE
VIABLE
CHALLENGING
Connectivity - Firewall Friendly
WS/SS
RDA/Merge
Connectivity - WWAN/Dialup
RDA
WS/SS/Merge
Connectivity - WLAN
RDA
WS/SS/Merge
Conflict Resolution
Merge
SS
WS/RDA
Code to Implement
RDA
Merge/SS
WS
Setup/Deployment Effort
RDA
WS
Merge
On-Going Administration
RDA
WS/SS
Merge
Enterprise Management
Merge
WS/SS
RDA
Large Data Volumes
Merge/RDA
Server DBMS Independence
WS/SS
Auto SQL CE DBMS Creation
Merge/SS
Ability to Secure Data Sync
Merge
RDA/WS/SS
Overall Complexity
RDA/SS
WS
WS/SS
Merge
Sync Services for ADO.NET (Devices)
Schedule some time to evaluate this!
V1 available now, new version coming
Sweet-spot
Atomic control over performance tuning
Great way to create starter SDF files
Concerns
Merge Replication Tuning
Important considerations
Take only what you need
Avoid implicit column type conversions
Save a column, eliminate identity range pain
Use uniqueidentifier PKs
Set IsRowGuid to True
Download-only vs. Bi-Directional articles
Defrag indexes on the distributor
DBCC INDEXDEFRAG (Driver, MSMerge_Contents, 1-4)
Defrag all indexes on MSmerge_*, MSrepl_*, Mssnapshot_*
Daily is not too often…
Merge Replication Tuning (cont'd)
Row-Level Tracking vs. Column-Level Tracking
MaxBuffer Size on Subscriber Database
Investigate lowering retention period from
default of 14 days
Set Merge Agent profile to match network
Investigate mixing Merge replication with other
techniques (RDA, Sync Services)
Replication Monitor does not tell the whole
story – instrument your subscriber code
Merge Replication Tuning
Hundreds of subscribers
Hardware matters
Separate distributor from publisher
Data and log files on separate, fast spindles
Increase SQL Server minimum memory
Tune IIS – the ISAPI DLL is the bottleneck
More information:
Resources
www.microsoft.com/teched
www.microsoft.com/learning
Sessions On-Demand & Community
Microsoft Certification & Training Resources
http://microsoft.com/technet
http://microsoft.com/msdn
Resources for IT Professionals
Resources for Developers
Windows Mobile® Resources
TechNet TechCenter – System Center Mobile Device Manager 2008
http://technet.microsoft.com/scmdm
TechNet TechCenter – Windows Mobile
http://technet.microsoft.com/windowsmobile
MSDN Center – Windows Mobile
http://msdn.microsoft.com/windowsmobile
Webcasts and Podcasts for IT – Windows Mobile
http://www.microsoft.com/events/series/msecmobility.aspx
General Information – Windows Mobile
http://www.windowsmobile.com
General Information – System Center Mobile Device Manager 2008
http://www.windowsmobile.com/mobiledevicemanager
Windows Marketplace Developer Portal
http://developer.windowsmobile.com
Windows Mobile®
is giving away
Blackjack IIs !
Stop by the
Windows Mobile
Technical Learning Center
to learn how to enter
Complete an
evaluation on
CommNet and
enter to win!
© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should
not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS,
IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.