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 DhamodaranPosted Nov 3, 2010, 5:04 AM
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.
WojciechPosted Nov 2, 2010, 9:06 AM
- 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 NairPosted Oct 29, 2010, 1:57 PM
1. limit retrival of rows count to minimum 50 records.
2. remove all ajax stuff
WojciechPosted Oct 29, 2010, 12:32 PM
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 ChandPosted Oct 29, 2010, 10:39 AM
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.
WojciechPosted Oct 29, 2010, 10:11 AM
I reflect why dotNet so strongly utilise SQLserver but Sql Management Studio not. Perhaps something in the IIS configuration ?
Suthish NairPosted Oct 28, 2010, 3:23 PM
can you update something about your procedure.
WojciechPosted Oct 28, 2010, 12:52 PM
Suthish NairPosted Oct 28, 2010, 10:36 AM
How about other pages interaction with database?