Разработка бизнес приложений (5)

  • Published on
    15-Jun-2015

  • View
    480

  • Download
    3

Embed Size (px)

DESCRIPTION

. 2011 .

Transcript

  • 1. (1). . 5

2. (in process db) ( , , ) (DDD) ( ) 3. : 4. ( ) ( ) (!!) () - 5. , , (. ) . (, -) , 6. On-line transaction processing (OLTP) , update / insert On-line analytical data processing , , , insert /update 7. NoSQL scalability (noACID) () Google, Facebook, Amazon http://nosql-database.org/ 8. OLTP 9. , : ( ) ( + ) ( .. ) ( / ) 10. (. ) ? Id Id Id IdId/ 11. ERD . , . , 1-* : FK NOT NULL 0..1 - *: FK NULL *..* -> 12. SQL: . , (SQL 92 / 99) , , . (, , , .). T/SQL, PL/SQL 13. SQL* SELECT [DISTINCT] [TOP ()] < > ( / ) [ FROM { < / > } [ ,...n ] ] [ WHERE ] [ GROUP BY [ ,...n ]] [ HAVING < > ] [ ORDER BY { / } [ ,...n ] ]* 14. selectSELECT *FROM PrizesWHERE SystemName = @p0 15. selectSELECT [t0].[Id], [t0].[UserName], [t0].[Password], [t0].[TempPassword], [t0].[TempPasswordEmail], [t0].[TempPasswordMobilePhone], [t0].[UserType], [t0].[Email], [t0].[IsEmailConfirmed],[t0].[EmailToBeConfirmed], [t0].[MobilePhone], [t0].[IsMobilePhoneConfirmed], [t0].[MobilePhoneToBeConfirmed], [t0].[MobilePhoneConfirmationCode], [t0].[IsBlocked], [t0].[IsDeleted],[t0].[RowVersion], [t0].[LastName], [t0].[FirstName], [t0].[MiddleName], [t0].[Sex], [t0].[BirthDate], [t0].[HomePhone], [t0].[HasAgreedToAdvertisement], [t0].[MobileRegionId], [t0].[OperatorId],[t0].[CityId], [t0].[PostAddressId], [t0].[DoesRequireMobileInfoUpdate], [t0].[RequestToDeleteDateTime], [t0].[RequestToDeleteChannel], [t16].[test], [t16].[Id] AS [Id2], [t16].[PostIndex],[t16].[RegionId], [t16].[RayonId], [t16].[CityId] AS [CityId2], [t16].[StreetId], [t16].[House], [t16].[IsEstate], [t16].[Building], [t16].[Stroenie], [t16].[Flat], [t16].[IsOffice], [t16].[Comments],[t16].[IsCorrect], [t16].[RowVersion] AS [RowVersion2], [t16].[Id2] AS [Id3], [t16].[TypeId], [t16].[Name], [t16].[DisplayName], [t16].[OrderNum], [t16].[UtcOffset], [t16].[FederalRegionId],[t16].[Id3] AS [Id4], [t16].[FullName], [t16].[ShortName], [t16].[DisplayName2], [t16].[OrderNum2], [t16].[test2], [t16].[Id4] AS [Id5], [t16].[RegionId2], [t16].[TypeId2], [t16].[Name2],[t16].[ContainsRegionCapitol], [t16].[IsVerified], [t16].[Id22] AS [Id6], [t16].[FullName2], [t16].[ShortName2], [t16].[DisplayName3], [t16].[OrderNum3], [t16].[Id5] AS [Id7], [t16].[RegionId3],[t16].[RayonId2], [t16].[TypeId3], [t16].[Name3], [t16].[Status], [t16].[OrderNum4], [t16].[UtcOffset2], [t16].[IsVerified2], [t16].[StreetRequired], [t16].[Id6] AS [Id8], [t16].[TypeId4], [t16].[Name4],[t16].[DisplayName4], [t16].[OrderNum5], [t16].[UtcOffset3], [t16].[FederalRegionId2], [t16].[Id7] AS [Id9], [t16].[FullName3], [t16].[ShortName3], [t16].[DisplayName5], [t16].[OrderNum6],[t16].[test3], [t16].[Id8] AS [Id10], [t16].[RegionId4], [t16].[TypeId5], [t16].[Name5], [t16].[ContainsRegionCapitol2], [t16].[IsVerified3], [t16].[Id23] AS [Id11], [t16].[FullName4],[t16].[ShortName4], [t16].[DisplayName6], [t16].[OrderNum7], [t16].[test4], [t16].[Id9] AS [Id12], [t16].[CityId2] AS [CityId3], [t16].[TypeId6], [t16].[Name6], [t16].[IsVerified4], [t16].[Id24] AS[Id13], [t16].[FullName5], [t16].[ShortName5], [t16].[DisplayName7], [t16].[OrderNum8]FROM [dbo].[Users] AS [t0]LEFT OUTER JOIN ( SELECT 1 AS [test], [t1].[Id], [t1].[PostIndex], [t1].[RegionId], [t1].[RayonId], [t1].[CityId], [t1].[StreetId], [t1].[House], [t1].[IsEstate], [t1].[Building], [t1].[Stroenie], [t1].[Flat], [t1].[IsOffice],[t1].[Comments], [t1].[IsCorrect], [t1].[RowVersion], [t2].[Id] AS [Id2], [t2].[TypeId], [t2].[Name], [t2].[DisplayName], [t2].[OrderNum], [t2].[UtcOffset], [t2].[FederalRegionId], [t3].[Id] AS [Id3],[t3].[FullName], [t3].[ShortName], [t3].[DisplayName] AS [DisplayName2], [t3].[OrderNum] AS [OrderNum2], [t6].[test] AS [test2], [t6].[Id] AS [Id4], [t6].[RegionId] AS [RegionId2], [t6].[TypeId] AS[TypeId2], [t6].[Name] AS [Name2], [t6].[ContainsRegionCapitol], [t6].[IsVerified], [t6].[Id2] AS [Id22], [t6].[FullName] AS [FullName2], [t6].[ShortName] AS [ShortName2], [t6].[DisplayName] AS[DisplayName3], [t6].[OrderNum] AS [OrderNum3], [t7].[Id] AS [Id5], [t7].[RegionId] AS [RegionId3], [t7].[RayonId] AS [RayonId2], [t7].[TypeId] AS [TypeId3], [t7].[Name] AS [Name3], [t7].[Status],[t7].[OrderNum] AS [OrderNum4], [t7].[UtcOffset] AS [UtcOffset2], [t7].[IsVerified] AS [IsVerified2], [t7].[StreetRequired], [t8].[Id] AS [Id6], [t8].[TypeId] AS [TypeId4], [t8].[Name] AS [Name4],[t8].[DisplayName] AS [DisplayName4], [t8].[OrderNum] AS [OrderNum5], [t8].[UtcOffset] AS [UtcOffset3], [t8].[FederalRegionId] AS [FederalRegionId2], [t9].[Id] AS [Id7], [t9].[FullName] AS[FullName3], [t9].[ShortName] AS [ShortName3], [t9].[DisplayName] AS [DisplayName5], [t9].[OrderNum] AS [OrderNum6], [t12].[test] AS [test3], [t12].[Id] AS [Id8], [t12].[RegionId] AS[RegionId4], [t12].[TypeId] AS [TypeId5], [t12].[Name] AS [Name5], [t12].[ContainsRegionCapitol] AS [ContainsRegionCapitol2], [t12].[IsVerified] AS [IsVerified3], [t12].[Id2] AS [Id23],[t12].[FullName] AS [FullName4], [t12].[ShortName] AS [ShortName4], [t12].[DisplayName] AS [DisplayName6], [t12].[OrderNum] AS [OrderNum7], [t15].[test] AS [test4], [t15].[Id] AS [Id9],[t15].[CityId] AS [CityId2], [t15].[TypeId] AS [TypeId6], [t15].[Name] AS [Name6], [t15].[IsVerified] AS [IsVerified4], [t15].[Id2] AS [Id24], [t15].[FullName] AS [FullName5], [t15].[ShortName] AS[ShortName5], [t15].[DisplayName] AS [DisplayName7], [t15].[OrderNum] AS [OrderNum8] FROM [dbo].[PostAddresses] AS [t1] INNER JOIN ([dbo].[Regions] AS [t2] INNER JOIN [dbo].[ObjectTypes] AS [t3] ON [t3].[Id] = [t2].[TypeId]) ON [t2].[Id] = [t1].[RegionId] LEFT OUTER JOIN ( SELECT 1 AS [test], [t4].[Id], [t4].[RegionId], [t4].[TypeId], [t4].[Name], [t4].[ContainsRegionCapitol], [t4].[IsVerified], [t5].[Id] AS [Id2], [t5].[FullName], [t5].[ShortName], [t5].[DisplayName],[t5].[OrderNum] FROM [dbo].[Rayons] AS [t4] INNER JOIN [dbo].[ObjectTypes] AS [t5] ON [t5].[Id] = [t4].[TypeId] ) AS [t6] ON [t6].[Id] = [t1].[RayonId] INNER JOIN ([dbo].[Cities] AS [t7] INNER JOIN ([dbo].[Regions] AS [t8]INNER JOIN [dbo].[ObjectTypes] AS [t9] ON [t9].[Id] = [t8].[TypeId]) ON [t8].[Id] = [t7].[RegionId] LEFT OUTER JOIN (SELECT 1 AS [test], [t10].[Id], [t10].[RegionId], [t10].[TypeId], [t10].[Name], [t10].[ContainsRegionCapitol], [t10].[IsVerified], [t11].[Id] AS [Id2], [t11].[FullName], [t11].[ShortName],[t11].[DisplayName], [t11].[OrderNum]FROM [dbo].[Rayons] AS [t10]INNER JOIN [dbo].[ObjectTypes] AS [t11] ON [t11].[Id] = [t10].[TypeId]) AS [t12] ON [t12].[Id] = [t7].[RayonId]) ON [t7].[Id] = [t1].[CityId] LEFT OUTER JOIN ( SELECT 1 AS [test], [t13].[Id], [t13].[CityId], [t13].[TypeId], [t13].[Name], [t13].[IsVerified], [t14].[Id] AS [Id2], [t14].[FullName], [t14].[ShortName], [t14].[DisplayName], [t14].[OrderNum] FROM [dbo].[Streets] AS [t13] 16. (. ) ( FK) () : b-, ( .) 17. , (IDENTITY) (!) ( . ) http://sqlskills.com/BLOGS/KIMBERLY/category/Clustering-Key.aspx 18. SQL ( ) ( . , ) : scan / seek / join (nested loops, merge,hash) - 19. join Nested loops Merge Hash 20. 6 5 3SELECT COUNT(*) 2FROM PrizesWon as pw 1INNER JOIN Prizes AS prizes ON prizes.Id = pw.PrizeIdWHERE (pw.OrderId IS NOT NULL) AND (pw.PrizeType = Payment) AND (pw.UserId =544496) AND (prizes.ActionName = NCP 2011 Baltika23) 4 31 6 5 24 21. BEGIN TRANSACTIONSql-1Sql-2...Sql-nCOMMINT or ROLLBACK TRANSACTION 22. (ACID) Atomic (): Durable (): , Consistency (): Isolation (): 23. : ( ) ( ), .. 24. 25. 26. 1015128 14T1: 10%T2: .. ( ) 27. ( ) . R/W . 28. Select avg() () .T1:lock(a), T2:lock(b), T1:locl(b),T2:lock(a) ( ) , () 29. 10 15 12 8 14T1: 10%T2: , ( scan) Range locks (Index) / TABLE locks. (WHERE IS NULL, ) 30. OLTP OLAP Read uncommitted ( , ) Read committed (R , W ) Repeatable read (W, R ) Serializable (W, R + Range locks) 31. (2) ( ) ( ) , 32. Full / incremental backup mirror / realtime / master / slave 33. MySQL OS, , , oracle PostgreSql OS, Oracle Enterprise , MS SQL Server 34. MS SQL Server (Demo) Enterprise Studio Intellisense Books online SQL Server profiler Tuning advisor 35. ? ( / ) 36. SQL Server books online http://msdn.microsoft.com/en-us/library/ms130214.aspx http://www.microsoft.com/sqlserver/en/us/editions/express.aspx Wikipedia http://softwarestencils.com/ (visio stencils) 37. AOP Kanban / Lean SCRUM: Team / ScrumMaster (DS, Retro, SprintPlan, Demo) Portfolio management, BMG (Alex Ostervald),Scrum of Scrum NoSql 38. http://www.apps4russia.ru/, http://apps4russia.reformal.ru/, http://data.worldbank.org/ : http://minenergo.gov.ru/activity/statistic/,http://www.fms.gov.ru/abo ut/ofstat/, http://www.federalspace.ru/main.php?id=10, http://ivan.begtin.name/2011/10/02/gosuslugijson/ : ( , ) ( / ) MS: C#, MS MVC, MS SQL Server

Recommended

View more >