Fast SQLServer vs. slow(??) dotNet

Question

Hi I need your help. My problem is (probably) slow dotNet connection to Ms SQL Server. I have dotNet application working at IIS v.6 and connected to Ms Sql 2005 base. Base contains storage procedure called WSWyswietlNag. When I test this procedure in Sql Server Management Studio procedure works fast. When I connect to SQL Server from dotNet Application procedure works very very slow. I don't understand this phenomenon. Below is table generated from SQL Profiler. EventClass ApplicationName CPU Duration Error UserId Reads RowCounts TextData Writes 10 .Net SqlClient Data Provider 24329 25624636 0 sa 144935 9 exec WSWyswietlNag @IDNaglowk=0@Typ=N' '@Lok=0@IDKont=0@IDKateg=0 @DataRejOd = 'Sep 15 2010 12:00:00:000AM'@DataRejDo='Oct 6 2010 12:00:00:000AM'@Nazwa=N'BRAK' 0 12 Microsoft SQL Server Management Studio - Query 63 73398 0 sa 16802 11 exec WSWyswietlNag @IDNaglowk=0@Typ=N' '@Lok=0@IDKont=0@IDKateg=0 @DataRejOd = 'Sep 15 2010 12:00:00:000AM'@DataRejDo='Oct 6 2010 12:00:00:000AM'@Nazwa=N'BRAK' 0 Same procedure same parameters different aplication only. Environment: dotNet 3.0 in application to connect I used SqlDataSource. What you think about this problem what happened? Where is problem? Thank you for each advice. Wojciech

Sivaraman Dhamodaran · Answer

Sql server stores the Execution Plan of last executed statement. The next call for same procedure will be Fast. Your profiler shows Procedure executed First through client application through .net data providers. The highest duration is because of preparation of the Execution Plan. Next time the duration is less. Because your server already cached the Execution plan. Try to make the execution in reverse order. That is; First from the Server then through you data provider. If you still see a performance difference. Well don't pay you attension on why SQL server is faster. Tune your query to get better performance. Try to use indexes and other performance related factors.

Wojciech · Answer

In compliance to your recommendation: - I made the new form without Ajax - I placed in the form only two controls: SqlDataSource and GridView GridView is supplyes from SqlDataSource. All values of parameters to SP I placed in SqlDataSource (as default). Afterwards I checked SQL Profiler results. Values Reads Duration and CPU are still huge...:( Is one very interested thing. During the wizard-configuration of the control SqlDataSource is realisability the test of the correctness. Then VS2008 connects to database execute SP and returns records (consequential from parameters). It works very quickly (!) and SQL Profiler recognizes ApplicationName as Net SqlClient Data Provider. It is same as during the work of the web-application (only very and very faster). Also values Duration Read and CPU are smaller than in case of web-application. I do not know what to think about.

Suthish Nair · Answer

try couple of things.. 1. limit retrival of rows count to minimum 50 records. 2. remove all ajax stuff

Wojciech · Answer

To thank for attentions I wrote nothing about the environment. The joining to SQL happens by ASP.NET and the standard control SqlDataSource provided with Visual Studio 2008. Records presented are in the standard control GridView . GridView has engaged options SELECT and DELETE. Parameters for the storage procedure received are from controls Ddl and TextBox . The control SqlDataSource has no code in code behind . This control was configured by wizard. All controls (DDL TextBox  SqlDataSource and GridView ) are found in UpdatePanel In behind code are found the event for: a) GridView1_RowDeleting (for the option DELETE) b ) GridView1_Load (serves the memorization GridView1.PageIndex ) c ) GridView1_RowDataBound (serves the formatting of cells ) d ) GridView1_SelectedIndexChanged ( serves to switch to other page ) In event PageLoad checked is PostBack and binded data-lists to ddl . The delay appears at the first load of the page jumpering between PageIndex and each change of post-back controls ( eg. button or ddl ). Every post-back causes the prolonged loading UpdatePanel. At that time SQL server goes mad :-(  Try are debug your code where you are calling SP and see how of flies time that takes  - how I can this make? I do not know whether I wrote comprehensibly:( I to apologize for the chaos in the post.

Mahesh Chand · Answer

If your other pages are working efficiently that means SQL Server connection is just fine. Is this first time getting data slow? First time when creating a connection object it might be slow but not noticeable. Here are some other pointers. How much data are you getting? I am assuming it's an ASP.NET app. Where is that code placed in your page that is slow? Is this code being executed multiple times say if you put code on page load and do not check PostBacks. What control is displaying the data? Data may already be in the app and it may be slow in UI. Try to debug your code where you are calling SP and see how much time that takes.

Wojciech · Answer

The procedure has parameters and chooses records from the view called View01. The procedure WSWyswietlNag is a procedure which only reads records (SELECT). The procedure does not perform nor the operation of the recording nor the modification nor deleting. The quantity returned records is at times only several records (eg.7.. 20). Is visible this in table from SQL Profiler. Instead differences in Duration and Read are huge. I reflect why dotNet so strongly utilise SQLserver but Sql Management Studio not. Perhaps something in the IIS configuration ?

Suthish Nair · Answer

so might the procedure o/p might be huge. can you update something about your procedure.

Wojciech · Answer

Other pages work efficiently. I have a trouble only with this one. I reflect from where themselves take so large differences among SQL Management Studio and the Application? Perhaps this is some problem with the efficiency of the connection dotNet to SQLServera? I have no idea. :(

Suthish Nair · Answer

you meant to say... while fetching only you facing this problem... How about other pages interaction with database?

EventClass	ApplicationName	CPU	Duration	Error	UserId	Reads	RowCounts	TextData	Writes
10	.Net SqlClient Data Provider	24329	25624636	0	sa	144935	9	exec WSWyswietlNag @IDNaglowk=0,@Typ=N' ',@Lok=0,@IDKont=0,@IDKateg=0, @DataRejOd = 'Sep 15 2010 12:00:00:000AM',@DataRejDo='Oct 6 2010 12:00:00:000AM',@Nazwa=N'BRAK'	0
12	Microsoft SQL Server Management Studio - Query	63	73398	0	sa	16802	11	exec WSWyswietlNag @IDNaglowk=0,@Typ=N' ',@Lok=0,@IDKont=0,@IDKateg=0, @DataRejOd = 'Sep 15 2010 12:00:00:000AM',@DataRejDo='Oct 6 2010 12:00:00:000AM',@Nazwa=N'BRAK'	0