0

I have a simple web part that I've added to my SharePoint 2007 site that containes a few labels, textboxes, a gridview, and a detailsview. This web part connects to SQL 2005 to retrieve data after clicking a button. There are no datasource objects except within the C# where they are created when they are needed (after button click). The issue is that the initial loading takes forever. We have about 10 other custom apps on our site and none of them load nearly as slowly as this one and it's probably the simplest of all of them (170 lines in C#, 165 lines in ASP.Net)

Does anyone have any idea why I am getting this degraded performance with such a simple application? I've done everything I could think of to improve the performance with no luck. If more information is required, I will do my best to provide it.

As I've mentioned, the application does not connect to SQL when it is initially loaded. the app is essentially a search of our internal data so until they fill out the search fields, no connection is made.

Thank You Corey

code:

string sql; sqlconnection con; con.Open(); cmd = new SqlCommand(sql,con); sqldataadapter dapter = new sqldataadapter(cmd); dataset ID = new dataset(); adapter.fill(ID,"ID"); sqldatareader reader = cmd.executereader(); gridview.datasource=ID; gridview.databind();
Improve this question
8
  • sounds like the database is the bottleneck. How many records? Just one table? How are you retrieving the data? ADO? EF? Linq? Commented Jul 3, 2014 at 20:45
  • I should mention, first, that there is no connection to the DB until they click a button, the web part is slow even on the initial page load. the table being called after the first click contains around 500k records (the query in SQL takes only a second to run). the second click touches 4 tables with varying sizes from 200-500k (this query in SQL takes roughly 20 seconds). We are using a Linq connection, I believe. Commented Jul 3, 2014 at 20:52
  • OK. How are you displaying / binding the records? Have you considered caching the result set? What about BCS? I can help you if they are options for you. Commented Jul 3, 2014 at 21:02
  • @Colbs BCS? I'm not familiar with that. to get and bind the data I am using a sqlconnection string, sqlcommand, sqldataAdapter, filling the adapter with a dataset, using dataset as datasource then gridview.databind(). I've even set the gridview/detailsview to clear with a null datasource after they are done being used. Commented Jul 3, 2014 at 21:05
  • This code doesn't tell much and there are typos. What line there takes 20 secs when you debug? Commented Jul 3, 2014 at 22:05

1 Answer 1

Reset to default
0

Sounds like your data hindrance has nothing to do with SharePoint. You can create a console application with the same code (Try just the button event with SQL connection / query first) to confirm this delay.

Business Data Connectivity (BDC - now called BCS) would be an alternative, but unless you want to do a re-write and have configuration overhead, I wouldn't go this route

http://msdn.microsoft.com/en-us/library/office/bb736296(v=office.12).aspx

If there is a big query on a huge table, ADO will not handle it as well if you push that processing to the SQL side. Consider creating a View or Stored Proc with parameters

http://www.codeproject.com/Articles/38560/Overview-of-View-in-SQL-Server

If the result set doesn't change and you have no form parameters, a quick and dirty suggestion would be to cache the result set:

https://stackoverflow.com/questions/9363907/caching-result-of-sql-call

Sorry, but without seeing the code I cannot give a definitive answer. Hopefully I have helped point you in the right direction.

Improve this answer
1
  • I understand the idea that it could be a bottleneck when retrieving data from the db, but I have applications that pull from tables with millions of records and the apps don't run as slowly as this one. I will attempt to add the code to my original post. Commented Jul 3, 2014 at 21:48

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.