Pagination (edit)

Một số kinh nghiệm khi tối ưu hóa câu SQL Query

+ Không nên dùng nhiều bảng tạm khi truy vấn nhiều dữ liệu

+ Việc đầu tiên là xóa bảng tạm ngay lập tức và thay bằng Sub-Query

https://kipalog.com/posts/Mot-so-kinh-nghiem-khi-optimize-sql-query

https://azlogs.wordpress.com/2016/07/02/mot-so-kinh-nghiem-khi-optimize-sql-query-sua-bai-viet/

Fastest way: Keyset Pagination

SELECT ...
FROM ...
WHERE ...
  AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLY

Better way

SELECT TOP 10 first_name, last_name, score, COUNT(*) OVER()
FROM players
WHERE (score < @previousScore)
   OR (score = @previousScore AND player_id < @previousPlayerId)
ORDER BY score DESC, player_id DESC

For SQL Server 2012: OFFSET + FETCH

http://dbadiaries.com/new-t-sql-features-in-sql-server-2012-offset-and-fetch

-- CREATING A PAGING WITH OFFSET and FETCH clauses IN "SQL SERVER 2012"
DECLARE @PageNumber AS INT, @RowspPage AS INT

SET @PageNumber = 2
SET @RowspPage = 10 

SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE
FROM TB_EXAMPLE
ORDER BY ID_EXAMPLE
OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
FETCH NEXT @RowspPage ROWS ONLY;

For SQL Server 2005: ROW_NUMBER

https://techtalk.vn/phan-trang-trong-co-so-du-lieu-sql-server.html

SELECT COUNT(*) FROM Orders WHERE OrderDate >= '1980-01-01'

SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
 FROM Orders
 WHERE OrderDate >= '1980-01-01'
 ) AS RowConstrainedResult
WHERE RowNum >= 1
 AND RowNum < 20
ORDER BY RowNum

For SQL Server 2000: ROW_NUMBER

http://nguyennhatcuongit.blogspot.com/2017/10/dung-ajax-e-phan-trang-trong-datatable.html

WITH OrderedOrders AS
(
 SELECT
 ROW_NUMBER() OVER(ORDER BY FirstName DESC) AS RowNumber, 
 FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"
 FROM [dbo].[vSalesPerson]
) 
SELECT RowNumber, FirstName, LastName, Sales YTD 
FROM OrderedOrders 
WHERE RowNumber > 50 AND RowNumber < 60;

For SQL Server 2000: WHERE

http://www.gacoder.info/hien-thi-va-phan-trangtim-kiem-trong-laravel-va-datatables/

-- Nhược điểm lớn nhất là nó đưa hết data vào bảng tạm trong tempdb database cho nên bộ nhớ sẽ được sử dụng tối đa cho mục đích phân trang.

DECLARE @pageNo int -- 1 based
DECLARE @pageSize int
SET @pageNo = 51
SET @pageSize = 20
 
DECLARE @firstRecord int
DECLARE @lastRecord int
SET @firstRecord = (@pageNo - 1) * @pageSize + 1 -- 1001
SET @lastRecord = @firstRecord + @pageSize - 1 -- 1020
 
DECLARE @orderedKeys TABLE (
 rownum int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
 TableKey int NOT NULL
)
 
SET ROWCOUNT @lastRecord

INSERT INTO @orderedKeys (TableKey) SELECT ID FROM Orders WHERE OrderDate >= '1980-01-01' ORDER BY OrderDate
 
SET ROWCOUNT 0
 
SELECT t.*
FROM Orders t
 INNER JOIN @orderedKeys o ON o.TableKey = t.ID
WHERE o.rownum >= @firstRecord
ORDER BY o.rownum

Kiến thức cần nắm vững

Variable tables

DECLARE @TableName TABLE
(
 ID INT IDENTITY PRIMARY KEY NOT NULL, 
 Name VARCHAR(10) NOT NULL, 
 DOB DATETIME null
) 
 
INSERT INTO @TableName
 ( Name, DOB )
VALUES ( 
 'TONA', -- Name - varchar(10)
 GETDATE() -- DOB - datetime
) 

SELECT * FROM @TableName

Biến bảng là biến dữ liệu kiểu bảng, được khởi tạo và lưu trong RAM như một biến bình thường chỉ khác là nó kiểu bảng. Do đó biến bảng cũng đóng vai trò như một bảng tạm. Chỉ khác là bảng tạm chỉ tạo một lần và có thể sử dụng trong suốt phiên đăng nhập, còn biến bảng sẽ mất khi khi kết thúc đoạn truy vấn.

Temporary tables

Bảng tạm là các có cấu trúc và chức năng như một bảng cố định bình thường trong SQL Server. Nhưng thay vì tạo ra một bảng trong Database, bảng tạm được tạo ra và lưu trữ trong tempdb. Chúng ta thường tạo bảng tạm trong một câu truy vấn, trong xử lý của một procedure hoặc trong một function (chỉ sử dụng được biến kiểu bảng).

Local temporary table là bảng tạm được tạo ra và tồn tại trong 1 kết nối. Khi kết thúc kết nối thì bảng tạm này sẽ tự động được xóa. Tên của bảng tạm kiểu Local được bắt đầu bằng ký tử #.

Global temporary table là bảng tạm có phạm vi ảnh hưởng trong tất cả các kết nối và nó chỉ tự động được xóa đi khi tất cà kết nối đã được ngắt. Tên bảng tạm kiểu Global được bắt đầu  bằng ##.

Bảng tạm được tạo ra trong Database Tempdb khác với Database của bạn vì thế có thể gây ra các vấn đề về hiệu suất. Do đó, bạn cần tính toán kỹ số lượng các Fields cần thiết phải lấy ra.

Bảng tạm (Local)

CREATE TABLE #LocalTmpTable
(
  Id int, -- int identity(1 1) primary key not null,
  Name nvarchar(50)
)

Bảng tạm (Global)

CREATE TABLE ##GlobalTmpTable
(
 Id int, -- int identity(1 1) primary key not null,
 Name nvarchar(50)
)

Thao tác với bảng tạm

SELECT Id, Name INTO #LocalTmpTable   FROM Users WHERE ...

SELECT Id, Name INTO ##GlobalTmpTable FROM Users WHERE ...

Angular 2 CRUD, modals, animations, pagination, datetimepicker and much more

BY CHRISTOS S. on JUNE 27, 2016 • ( 60 )

Angular 2 and TypeScript have fetched client-side development to the next level but till recently most of web developers hesitated to start a production SPA with those two. The first reason was that Angular was still in development and the second one is that components that are commonly required in a production SPA were not yet available. Which are those components? DateTime pickers, custom Modal popups, animations and much more.. Components and plugins that make websites fluid and user-friendly. But this is old news now, Angular is very close to release its final version and community been familiarized with the new framework has produced a great ammount of such components.

What this post is all about

This post is another step by step walkthrough to build Angular 2 SPAs using TypeScript. I said another one, cause we have already seen such a post before. The difference though is that now we have all the knowledge and tools to create more structured, feature-enhanced and production level SPAs and this is what we will do on this post. The Schedule.SPA application that we are going to build will make use of all the previously mentioned components following the recommended Angular style guide as much as possible. As far as the back-end infrastructure (REST API) that our application will make use of, we have already built it in the previous post Building REST APIs using ASP.NET Core and Entity Framework Core. The source code for the API which was built using .NET Core can be found here where you will also find instructions how to run it. The SPA will display schedules and their related information (who created it, attendees, etc..). It will allow the user to manimulate many aspects of each schedule which means that we are going to see CRUD operations in action. Let’s see the in detail all the features that this SPA will incorporate.

• HTTP CRUD operations
• Routing and Navigation using the new Component Router
• Custom Modal popup windows
• Angular 2 animations
• DateTime pickers
• Notifications
• Pagination through Request/Response headers
• Angular Forms validation
• Angular Directives
• Angular Pipes

Not bad right..? Before start building it let us see the final product with a .gif (click to view in better quality).

Start coding

One decision I ‘ve made for this app is to use Visual Studio Code rich text editor for development. While I used VS 2015 for developing the API, I still find it useless when it comes to TypeScript development. Lots of compile and build errors may make your life misserable. One the other hand, VS Code has a great intellisense features and an awesome integrated command line which allows you to run commands directly from the IDE. You can use though your text editor of your preference. First thing we need to do is configure the Angular – TypeScript application. Create a folder named Scheduler.SPA and open it in your favorite editor. Add the package.json file where we define all the packages we are going to use in our application.

We declared all the Angular required packages (Github repo will always be updated when new version releases) and some others such as ng2-bootstrap which will help us incorporate some cool features in our SPA. When some part of our application make use of that type of packages I will let you know. Next add the systemjs.config.js SystemJS configuration file.

I ‘ll make a pause here just to ensure that you understand how SystemJS and the previous two files work together. Suppose that you want to use a DateTime picker in your app. Searching the internet you find an NPM package saying that you need to run the following command to install it.

What this command will do is download the package inside the node_modules folder and add it as a dependency in the package.json. To use that package in your application you need to import the respective module in the app.module.ts (as of angular RC.6 and later). in the component that needs its functionality like this.

In most cases you will find the import statement on the package documentation. Is that all you need to use the package? No, cause SystemJS will make a request to http://localhost:your_port/ng2-bootstrap which of course doesn’t exist.
Modules are dynamically loaded using SystemJS and the first thing to do is to inform SystemJS where to look when a request to ng2-datetime dispatches the server. This is done through the map object in the systemjs.config.js as follow.

From now on each time a request to ng2-bootstrap reaches the server, SystemJS will map the request to node_modules/ng2-bootstrap which actually exists since we have installed the package. Are we ready yet? No, we still need to inform SystemJS what file name to load and the default extension. This is done using the packages object in the systemjs.config.js.

Our SPA is a TypeScript application so go ahead and add a tsconfig.json file.

This file will be used by the tsc command when transpiling TypeScript to pure ES5 JavaScript. We also need a typings.json file.

Those are mostly Angular dependencies plus jquery and lodash that our SPA will make use of. We are going to use some client-side external libraries such as alertify.js and font-awesome. Add a bower.json file and set its contents as follow.

At this point we are all set configuring the SPA so go ahead and run the following commands:

npm install will also run typings install as a postinstall event. Before start typing the TypeScript code add the index.html page as well.

Angular & TypeScript in action

Add a folder named app at the root of the application and create four subfolders named home, schedules, users and shared. The home folder is responsible to display a landing page, the schedules and users are the basic features in the SPA and the latter shared will contain any component that will be used across the entire app, such as data service or utility services. I will start pasting the code from bottom to top, in other words from the files that bootstrap the application to those that implement certain features. Don’t worry if we haven’t implement all the required components while showing the code, we will during the process. I will however been giving you information regarding any component that haven’t implemented yet.

Bootstrapping the app

Add the main.ts file under app.

As of Angular RC.6 and later it is recommended to create at least three basic files to init an Angular 2 app. An app.component.ts to hold the root container of the app, an app.module.ts to hold the app’s NgModuleand the previous main.ts to bootstrap the app. Go ahead and create the app.component.ts under the appfolder.

We need the viewContainerRef mostly for interacting with ng2-bootstrap modals windows. The app.module.ts is one of the most important files in your app. It declares the NgModules that has access to, any Component, directive, pipe or service you need to use accross your app. Add it under app folder as well.

We imported Angular’s modules, ng2-bootstrap modules, custom components, directives, pipes and services that we are going to create later on. The DataService holds the CRUD operations for sending HTTP request to the API, the ItemsService defines custom methods for manipulating mostly arrays using the lodash library and last but not least the NotificationService has methods to display notifications to the user. Now let us see the routing in our app. Add the app.routes.ts file as follow.

This is how we use the new Component Router. At the moment you can understand that http://localhost:your_port/ will activate the HomeComponent and http://localhost:your_port/users the UserListComponent which display all users. RxJS is a huge library and it’ s good practice to import only those modules that you actually need, not all the library cause otherwise you will pay a too slow application startup penalty. We will define any operators that we need in a rxjs-operators.ts file under app folder.

The AppComponent is the root component which means has a router-outlet element on its template where other children components are rendered. Add the app.component.html file under app folder.

Shared services & interfaces

Before implementing the Users and Schedules features we ‘ll create any service or interface is going to be used across the app. Create a folder named shared under the app and add the interfaces.ts TypeScript file.

In case you have read the Building REST APIs using .NET and Entity Framework Core you will be aware with most of the classes defined on the previous file. They are the TypeScript models that matches the API’s ViewModels. The last interface that I defined is my favorite one. The Predicate interface is a predicate which allows us to pass generic predicates in TypeScript functions. For example we ‘ll see later on the following function.

This is extremely powerfull. What this function can do? It can remove any item from an array that fulfills a certain predicate. Assuming that you have an array of type IUser and you want to remove any user item that has id<0 you would write..

Add a pipes folder under shared and create a DateTime related pipe.

The pipe simply converts a date to a JavaScript datetime that Angular understands. It can be used either inside an html template..

.. or programatically..

We proceed with the directives. Add a folder named directives under shared. The first one is a simple one that toggles the background color of an element when the mouse enters or leaves. It ‘s very similar to the one described at official’s Angular’s website.

The second one though is an exciting one. The home page has a carousel with each slide having a font-awesome icon on its left.

The thing is that when you reduce the width of the browser the font-image moves on top giving a bad user experience.

What I want is the font-awesome icon to hide when the browser reaches a certain width and more over I want this width to be customizable. I believe I have just opened the gates for responsive web design using Angular 2.. Add the following MobileHide directive in a mobile-hide.directive.ts file under shared/directivesfolder.

What this directive does is bind to window.resize event and when triggered check browser’s width: if width is less that the one defined or the default one then hides the element, otherwise shows it. You can apply this directive on the dom like this.

The div element will be hidden when browser’s width is less than 772px..

You can extend this directive by creating a new Input parameter which represents a class and instead of hiding the element apply a different class!

Shared services

@Injectable() services that are going to be used across many components in our application will also be placed inside the shared folder. We will separate them though in two different types, core and utilities. Add two folders named services and utils under the shared folder. We will place all core services under services and utilities under utitlities. The most important core service in our SPA is the one responsible to send HTTP requests to the API, the DataService. Add the data.service.ts under the services folder.

The service implements several CRUD operations targeting the API we have built on a previous post. It uses the ConfigService in order to get the API’s URI and the ItemsService to parse JSON objects to typed ones (we ‘ll see it later). Another important function that this service provides is the handleError which can read response errors either from the ModelState or the Application-Error header. The simplest util service is the ConfigService which has only one method to get the API’s URI. Add it under the utils folder.

Make sure to change this URI to reflect your back-end API’s URI. It’ s going to be different when you host the API from the console using the dotnet run command and different when you run the application through Visual Studio. The most interesting util service is the ItemsService. I don’t know any client-side application that doesn’t have to deal with array of items and that’s why we need that service. Let’s view the code first. Add it under the utils folder.

We can see extensive use of TypeScript in compination with the lodash library. All those functions are used inside the app so you will be able to see how they actually work. Let’s view though some examples right now. The setItem(array: Array, predicate: Predicate, item: T) method can replace a certain item in a typed array of T. For example if there is an array of type IUser that has a user item with id=-1 and you need to replace it with a new IUser, you can simply write..

Here we passed the array of IUser, the predicate which is what items to be replaced and the preplacement new item value. Continue by adding the NotificationService and the MappingService which are pretty much self-explanatory, under the utils folder.

Features

Time to implement the SPA’s features starting from the simplest one, the HomeComponent which is responsible to render a landing page. Add a folder named home under app and create the HomeComponent in a home.component.ts file.

Despite that this is the simplest component in our SPA it still make use of some interesting Angular features. The first one is the Angular animations and the second is the the MobileHideDirective directive we created before in order to hide the font-awesome icons when browser’s width is less than 772px. The animation will make the template appear from left to right. Let’s view the template’s code and a preview of what the animation looks like.

Add a folder named Schedules. As we declared on the app.routes.ts file schedules will have two distinct routes, one to display all the schedules in a table and another one to edit a specific schedule. The ScheduleListComponent is a quite complex one. Add the schedule-list.component.ts under schedules as well.

Firstly, the component loads the schedules passing the current page and the number of items per page on the service call. The PaginatedResult response, contains the items plus the pagination information. The component uses PAGINATION_DIRECTIVES and PaginationComponent modules from ng2-bootstrap to render a pagination bar under the schedules table..

The next important feature on this component is the custom modal popup it uses to display schedule’s details. It makes use of the ModalDirective from ng2-bootstrap. This plugin requires that you place a bsModal directive in your template and bind the model properties you wish to display on its template body. You also need to use the @ViewChild(‘childModal’) for this to work. Let’s view the entire schedule-list.component.html template and a small preview.

The ScheduleEditComponent is responsible to edit the details of a single Schedule. The interface used for this component is the IScheduleDetails which encapsulates all schedule’s details (creator, attendees, etc..). Add the schedule-edit.component.ts file under the schedules folder.

The interesting part about this component is the validations it carries on the template schedule-edit.component.html.

Don’t forget that we have also set server-side validations, so if you try to edit a schedule and set the start time to be greater than the end time you should receive an error that was encapsulated by the server in the response message, either on the header or the body.

The Users feature is an interesting one as well. I have decided on this one to display each user as a card element instead of using a table. This required to create a user-card custom element which encapsulates all the logic not only for rendering but also manipulating user’s data (CRUD ops..). Add a folder named Usersunder app and create the UserCardComponent.

The logic about the modal and the animations should be familiar to you at this point. The new feature to notice on this component are the @Input() and @Output() properties. The first one is used so that the host component which is the UserListComponent pass the user item foreach user in a array of IUser items. The two @Output() properties are required so that a user-card can inform the host component that something happend, in our case that a user created or removed. Why? It’s a matter of Separation of Concerns. The list of users is maintained by the UserListComponent and a single UserCardComponent knows nothing about it. That’s why when something happens the UserListComponent needs to be informed and update the user list respectively. Here’s the user-card.component.html.

Also add the user-list.component.ts and notice the usage of the ItemsService for manipulating items.

The removeUser and userCreated are the events triggered from child UserCardComponent components. When those events are triggered, the action has already finished in API/Database level and what remains is to update the client-side list. Here’s the template for the UserListComponent.

The SPA uses some custom stylesheet styles.css which you can find here. Add it in a new folder named assets/styles under the root of the application. At this point you should be able to run the SPA. Make sure you have set the API first and configure the API’s endpoint in the ConfigService to point it properly. Fire the app by running the following command.

Conclusion

That’s it we have finished! We have seen many Angular 2 features on this SPA but I believe the more exciting one was how TypeScript can ease client-side development. We saw typed predicates, array manipulation using lodash and last but not least how to install and use 3rd party libraries in our app using SystemJS.

Source Code: You can find the source code for this project here where you will also find instructions on how to run the application.

In case you find my blog’s content interesting, register your email to receive notifications of new posts and follow chsakell’s Blog on its Facebook or Twitter accounts.