0

In my stored procedure, I am returning 13 tables. In management studio, proc is taking around 150ms-250ms to execute which is fine. In my asp.net core (c#) project, I filled the dataset with that procedure and here it takes more than 20 seconds or sometimes 35+ seconds to fill dataset via sqldataadapter. Can anyone please help me optimize this, so that data can be filled quickly. Below is the code

Stored Procedure enter image description here

C# Code enter image description here

Update: I tried SqlDataAdapter with Add instead AddWithValue but result is same. No improvement at all. enter image description here

Jitendra Pancholi
  • 7,897
  • 12
  • 51
  • 84
  • You might want to rethink your approach and try not to load 13 tables in the dataset. It is not clear what you are trying to do with 13 tables in dataset it is difficult to suggest any improvement approach. – Chetan Dec 11 '21 at 12:14
  • you are using a transaction in c # code? – Xilmiki Dec 11 '21 at 12:22
  • It's a trading application and all that data is required. Cannot skip any table. And no transaction anywhere. @Xilmiki – Jitendra Pancholi Dec 11 '21 at 12:28
  • try adding MultipleActiveResultSets=True; in connection string. – Xilmiki Dec 11 '21 at 12:38
  • It's already set to true. @Xilmiki – Jitendra Pancholi Dec 11 '21 at 12:41
  • 1
    [`AddWithValue` isn't helping the situation.](http://www.dbdelta.com/addwithvalue-is-evil/) You might get a slight performance boost if you set parameter db types appropriately. – Crowcoder Dec 11 '21 at 12:53
  • If the data is read only then it would be much faster to use a SqlDataReader into a strongly typed collection than to incur all the overhead of adapters and data tables. – Crowcoder Dec 11 '21 at 12:55
  • let me try this and revert back @Crowcoder – Jitendra Pancholi Dec 11 '21 at 14:21
  • @Crowcoder I tried that approach but result is same. I added in my question, please check update section. I added screenshot also. – Jitendra Pancholi Dec 11 '21 at 15:01
  • I did say you "might" get a "slight" boost. And you probably did, just not enough to notice. Again, a reader would be more efficient if you don't need the change tracking features of data sets – Crowcoder Dec 11 '21 at 15:10
  • After running the query fetch the session wait stats into and print them out. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-session-wait-stats-transact-sql?view=sql-server-ver15 – David Browne - Microsoft Dec 11 '21 at 15:48
  • 1
    Maybe this link [Slow in the Application, Fast in SSMS?](https://www.sommarskog.se/query-plan-mysteries.html) can help you – GuidoG Dec 12 '21 at 14:32
  • Reading this article @GuidoG – Jitendra Pancholi Dec 15 '21 at 08:17

0 Answers0