Over a million developers have joined DZone.

Boost Your Data Layer Performance Using Bulk Select with Telerik Data Access

DZone's Guide to

Boost Your Data Layer Performance Using Bulk Select with Telerik Data Access

· Java Zone
Free Resource

Learn how to troubleshoot and diagnose some of the most common performance issues in Java today. Brought to you in partnership with AppDynamics.

Originally written by Ivailo Ivanov

Have you ever found yourself in a situation when you need to retrieve a bunch of database objects that are logically related to values you already collected in-memory? Currently, Telerik Data Access can help you with it through its special handling for the Contains() extension method. The characteristic behind this method is that it effectively overcomes the limitations imposed by the Relational Database servers related to the number of the parameters in the queries (here are the exact numbers for the different backends), while preserving the default behaviour of Telerik Data Access to issue parameterized statements only. This combination practically allows Bulk Select on the server side for the LINQ queries these methods participate in.

Let’s see that in action!

Suppose the following setup: the details about the users of an application are stored in one database and the permissions they have to modify the application’s data are stored in another. The task would be to find out who are the users that can drop a certain database object and who are the users that can delete records. To achieve this with Telerik Data Access, you can apply the following approach:

  1. From the Permissions database, you retrieve the user IDs with the necessary rights:

  2. List<int> userIDsDropDatabase =newList<int>();List<int> userIDsDeleteRecodrds =newList<int>();//Retrieve the userIDsusing(PermissionsModel dbContext =newPermissionsModel()){
       //Retrieve the users who can drop the database
       userIDsDropDatabase = dbContext.Permissions
       .Where(p => p.DBObjectName=="SofiaCarRentalDb"
         && p.GrantedPermission=="DROP")
       .Select(p => p.UserID).ToList();
       //Retrieve the users who can delete records from any database table
       userIDsDeleteRecodrds = dbContext.Permissions
         .Where(p => p.GrantedPermission=="DELETE")
        .Select(p => p.UserID).ToList();}
  3. With the help of Contains(), you filter the users in the Users database:

  4. using(UsersModel dbContext =newUsersModel()){   
        List<User> usersDropDatabase = dbContext.Users
       .Where(u => userIDsDropDatabase.Contains(u.UserID)).ToList();List<User> userDeleteRecords = dbContext.Users
       .Where(u => userIDsDeleteRecodrds.Contains(u.UserID)).ToList();}

The first two queries demonstrate the special handling of Contains(). The SQL statements generated for them would be:

--Containsfor users who can drop the database
FROM [User] a
WHERE (a.[UserID] IN (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22,@p23,@p24,@p25,@p26,@p27,@p28,@p29,@p30,@p31,@p32,@p33,@p34,@p35,@p36,@p37,@p38,@p39,@p40,@p41,@p42,@p43,@p44,@p45,@p46,@p47,@p48,@p49,@p50,@p51,@p52,@p53,@p54,@p55,@p56,@p57,@p58,@p59,@p60,@p61,@p62,@p63,@p64,@p65,@p66,@p67,@p68,@p69,@p70,@p71,@p72,@p73,@p74,@p75,@p76,@p77,@p78,@p79)) 


--Containsfor users who can delete records
CREATE TABLE [#TMPF283515A457B4CCA976FD1F2CD]([UserID]int)
INSERT INTO [#TMPF283515A457B4CCA976FD1F2CD]([UserID]) VALUES (@p0)
FROM [User] a
JOIN [#TMPF283515A457B4CCA976FD1F2CD] AS b ON (a.[UserID]= b.[UserID]) 

The difference between the scripts comes from the difference between the numbers of the elements in the userIDsDropDatabase and the userIDsDeleteRecords collections. In the first case, the IDs are 80, while in the other case they are 560. Given the fact that this example uses MS SQL Server, and that MS SQL Server has a limitation of 500 for the allowed parameters in the IN clause, Telerik Data Access takes this limitation into account when the queries are generated and uses a temporary table when the number of the parameter exceeds the limit.

What do you think? Do these methods work for you?

P.S. You can get the sample used here from our Data Access Samples repository in GitHub.

Understand the needs and benefits around implementing the right monitoring solution for a growing containerized market. Brought to you in partnership with AppDynamics.


Published at DZone with permission of Doug Winfield, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}