Over a million developers have joined DZone.

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

· Java Zone

Thrive in the application economy with an APM model that is strategic. Be E.P.I.C. with CA APM.  Brought to you in partnership with CA Technologies.

Originally written by

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
SELECT * 
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)) 
ORDER BY COL1

And

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

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.

Navigate the Maze of the End-User Experience and pick up this APM Essential guide, brought to you in partnership with CA Technologies

Topics:

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

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

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

{{ parent.tldr }}

{{ parent.urlSource.name }}