1

Looking for a bit of advice on how to optimise one of our projects. We have a ASP.NET/C# system that retrieves data from a SQL2008 data and presents it on a DevExpress ASPxGridView. The data that's retrieved can come from one of a number of databases - all of which are slightly different and are being added and removed regularly. The user is presented with a list of live "companies", and the data is retrieved from the corresponding database.

At the moment, data is being retrieved using a standard SqlDataSource and a dynamically-created SQL SELECT statement. There are a few JOINs in the statement, as well as optional WHERE constraints, again dynamically-created depending on the database and the user's permission level.

All of this works great (honest!), apart from performance. When it comes to some databases, there are several hundreds of thousands of rows, and retrieving and paging through the data is quite slow (the databases are already properly indexed). I've therefore been looking at ways of speeding the system up, and it seems to boil down to two choices: XPO or LINQ.

LINQ seems to be the popular choice, but I'm not sure how easy it will be to implement with a system that is so dynamic in nature - would I need to create "definitions" for each database that LINQ could access? I'm also a bit unsure about creating the LINQ queries dynamically too, although looking at a few examples that part at least seems doable.

XPO, on the other hand, seems to allow me to create a XPO Data Source on the fly. However, I can't find too much information on how to JOIN to other tables.

Can anyone offer any advice on which method - if any - is the best to try and retro-fit into this project? Or is the dynamic SQL model currently used fundamentally different from LINQ and XPO and best left alone?

KenD
  • 5,280
  • 7
  • 48
  • 85

3 Answers3

2

Before you go and change the whole way that your app talks to the database, have you had a look at the following:

  • Run your code through a performance profiler (such as Redgate's performance profiler), the results are often surprising.

  • If you are constructing the SQL string on the fly, are you using .Net best practices such as String.Concat("str1", "str2") instead of "str1" + "str2". Remember, multiple small gains add up to big gains.

  • Have you thought about having a summary table or database that is periodically updated (say every 15 mins, you might need to run a service to update this data automatically.) so that you are only hitting one database. New connections to databases are quiet expensive.

  • Have you looked at the query plans for the SQL that you are running. Today, I moved a dynamically created SQL string to a sproc (only 1 param changed) and shaved 5-10 seconds off the running time (it was being called 100-10000 times depending on some conditions).

Just a warning if you do use LINQ. I have seen some developers who have decided to use LINQ write more inefficient code because they did not know what they are doing (pulling 36,000 records when they needed to check for 1 for example). This things are very easily overlooked.

Just something to get you started on and hopefully there is something there that you haven't thought of.

Cheers,

Stu

Stuart Blackler
  • 3,732
  • 5
  • 35
  • 60
  • Hi Stu, thanks for the very useful reply. I've already checked most of the above; the issue, as I understand it, is that the web server pulls the entire table from the SQL server, and then paginates the data and sends the results to the browser. Therefore, every page the user clicks on results in an entire table read. XPO and LINQ seem to avoid this by customising the query to only read the actual displayed rows from the database - it seems like I'll need to recode my dynamic SQL query to simulate this behaviour. – KenD Mar 02 '11 at 08:55
  • Hi Kenny, in that case, could you not modify the query to pull say the top 50 and rebind when you need the next 50 for example? To do this, I would personally create a sproc with something similar to the following `USE AdventureWorks; GO WITH OrderedOrders AS ( SELECT SalesOrderID, OrderDate, ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber' FROM Sales.SalesOrderHeader ) SELECT * FROM OrderedOrders WHERE RowNumber BETWEEN 10 AND 20;` changing 10 and 20 to the range of data you want. Ill admit I have never used the DevExpress controls so don't know how much control you have. – Stuart Blackler Mar 02 '11 at 09:21
2

As far as I understand you are talking about so called server mode when all data manipulations are done on the DB server instead of them to the web server and processing them there. In this mode grid works very fast with data sources that can contain hundreds thousands records. If you want to use this mode, you should either create the corresponding LINQ classes or XPO classes. If you decide to use LINQ based server mode, the LINQServerModeDataSource provides the Selecting event which can be used to set a custom IQueryable and KeyExpression. I would suggest that you use LINQ in your application. I hope, this information will be helpful to you.

DevExpress Team
  • 11,338
  • 2
  • 24
  • 23
  • Thanks for the reply - however, the issue I'm anticipating with Linq is that I seem to need to define a class for each database I'm going to access first. The nature of this system is that the database being read is defined randomly - I read parameters from a configuration database which then defines which customer database and which fields are read. Am I missing something; can I define Linq classes "on the fly", and then generate the Linq statement dynamically? – KenD Mar 02 '11 at 08:52
  • Generally, it is possible to generate LINQ classes dynamically at runtime. You can find some information about this at http://stackoverflow.com/questions/2763158/how-to-create-dynamic-table-in-dbml-file. Also, it is possible to create XPO classes at runtime based on the DB content (http://www.devexpress.com/Support/Center/Issues/ViewIssue.aspx?issueid=Q230742). But, we do not recommend doing this. It is much easier and more effective to create all required classes at design time and use them. – DevExpress Team Mar 02 '11 at 10:57
0

I guess there are two points where performance might be tweaked in this case. I'll assume that you're accessing the database directly rather than through some kind of secondary layer.

First, you don't say how you're displaying the data itself. If you're loading thousands of records into a grid, that will take time no matter how fast everything else is. Obviously the trick here is to show a subset of the data and allow the user to page, etc. If you're not doing this then that might be a good place to start.

Second, you say that the tables are properly indexed. If this is the case, and assuming that you're not loading 1,000 records into the page at once and retreiving only subsets at a time, then you should be OK.

But, if you're only doing an ExecuteQuery() against an SQL connection to get a dataset back I don't see how Linq or anything else will help you. I'd say that the problem is obviously on the DB side.

So to solve the problem with the database you need to profile the different SELECT statements you're running against it, examine the query plan and identify the places where things are slowing down. You might want to start by using the SQL Server Profiler, but if you have a good DBA, sometimes just looking at the query plan (which you can get from Management Studio) is usually enough.

kprobst
  • 16,165
  • 5
  • 32
  • 53
  • Thanks for the reply - I think the SELECT statement is as fast as it's going to get, the issue (as I've described in the reply to Stu above) is that the web server is pulling the entire table over from the SQL table every time, even when it only needs to display 10 rows. – KenD Mar 02 '11 at 08:59