CODEWITHSUNDEEP
c#asp.netsqlgridview
user1186144
user1186144

Reputation: 133

Running a large SQL statement and populating a gridview

I have a fairly large SQL statement which has a number of inner joins and cross apply statements to do some calculations which creates new columns for each entry based on the previous query.

I was wondering what the most efficient / best way would be to run this query from C# in code to populate a gridview?

Breaking the SQL up and write some of the calculations in C#?

Creating a stored procedure?

Just passing the sql in as a string and creating a dataset and then binding the dataset to the gridview seems to be taking too long.

Upvotes: 5

Views: 1392

Answers (4)

TomTom
TomTom

Reputation: 62111

I was wondering what the most efficient / best way would be to run this query from C# in code to populate a gridview?

You mean from the only one possible version you have to start with?

The only way to execute a query that returns a table is running a Command that returns a DataReader. Point. If the SQL is given, that is it. No alternative exists.

Note: all the other stuff you may want to throw around here uses that internally. They wrap it.

Breaking the SQL up and write some of the calculations in C#?

What is my favourite food? You tell me. Depends a lot on hardware, data volume, and the query. Not able to answer.

Creating a stored procedure?

No difference.

Just passing the sql in as a string and creating a dataset and then binding the dataset to the gridview seems to be taking too long.

  • What is too long?
  • What takes so long? Loading 100 million rows? SQL Server processing? All that determines the optmization. Please provide relevant information - let us know where the time is spent.

THAT SAID: not using a data set but an observable collection of object that you fill in the background would at least allow results to appear while the list is populated.

Upvotes: -2

Jakub Konecki
Jakub Konecki

Reputation: 46008

It looks like your problem is that you are running some costly calculations on every SELECT. The proper solution to it is to precalculate the results of the calculation and store it in the database, so your grid an simply read data without any calculations.

You can trigger recalculation of your results when the underlying data changes. You an either use triggers or call the sproc that implements calculation from C# code.

Upvotes: 2

Paul Keister
Paul Keister

Reputation: 13077

Your core problem is that you database query must run faster. Once you have a solution for that, you can adjust your code accordingly.

The first thing I would look at is to optimize your database indexes to increase your query performance. If you can, use the SQL Profiler and Index Tuning Wizard (I don't think these tools are available in the Express edition of SQL Server). Please note that new indexes can also cause performance degradation with inserts, so if your database needs to support high transaction volume, you should be careful about using this approach.

If you can find a way to increase your query performance by breaking down the query into parts and aggregating the results with procedural code, then it would make sense to move to an ObjectDataSource. This will allow you maximum flexibility in how the data is acquired.

As a starting point, I would prototype your query and/or parts of your query in a SQL editor to that you can focus on just query optimization before you make any decisions about how your ASP.NET code will change.

I can't comment further on exactly how to optimize your data extraction without looking at the specifics of your schema and the queries you're using. But this should get you going in the right direction.

Upvotes: 3

user1017882
user1017882

Reputation:

Are you already executing any SQL scripts from anywhere else in your code (i.e. have you already arrived at a decision of how you are going to access the data)? The reason I ask is that I recently used ADO .net entities framework model to bridge the gap between my database and the conceptual objects I wanted to work with from the data in the database, and I highly recommend it. Really easy to get the hang of aswell. Once you've implemented an EF model it's simply a case of calling the stored procedure (yes I would advise you hold the query in a stored procedure) using the method that will be generated by the EF model and fetch into your classes (that are also automatically created by the model). The list of classes you just retrieved from the procedure can then obviously be bound to whatever control you like.

http://msdn.microsoft.com/en-us/data/ff191186

It's worth knowing.

Upvotes: 1

Related Questions