Filling tableadapter with stored procedure takes signifigantly longer than running stored procedure in SQL

Question

I have been trying to fill my ultracombo box with data yet it takes a signifigant amount of time. However when I run the same stored procedure from withing sql server it takes relatively no time. The line of code that the program gets hung up on is:

SprocPatientsTableAdapter.Fill(Me.PatientsDataSet.sprocPatients, SelectedCustomerID)

By signifigant differnce of time I mean the difference of less than 5 seconds in sql server and more than three hours from within the program.

Answer 1

Try using the SQL Server Profiler to see what query your program is sending exactly to the database. You may see some other parameters being passed that are affecting the query and slowing it down dramatically.

Answer 2

How big is the result set it's returning? If you're sending 2.5GB over the wire, storing it in memory and spinning thru it to fill a collection, then yeah, of course it will be slow.

I guess the big question though, is: Are you really using all the data that's being handed back? Please tell me you're not grabbing the whole table, then filtering down to seven rows and showing ID and UserName!

Answer 3

For this particular result the database is sending 45,522 rows (~9.08mb)

As for the big question of whether or not we need all this data no we do not and I have argued that one until I went past blue into realms of extremely dark navy colors in my face, but somehow it’s “mission critical” that we get everything (not just what is needed at a given moment). The first clue that we are grabbing more than what we need is the fact we are using a multicolumn combo box in the first place (spent over 1k just to get that one control), sorry rant over.

The process is: the user enters their user name and id --> selects the hospital they want to view (and have access to) --> then all the patients that are in that hospital that use our service get loaded into the ultracombobox auto filled by account number (hidden). The ultracombobox (multicolumn) is bound to a few other text boxes (last name, first name, middle etc….) so the info can be edited.

Answer 4

See my (accepted) answer to a very similar question.

Answer 5

I am going to give credit RoadWarrior for directing me to my eventual resolution of disabling constraints on the dataset. The UltraCombo box that uses the dataset is only displaying data not manipulating it and with the constraints disabled my ultracombo box fills extraordinarily fast