Click here to Skip to main content
15,907,281 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Hey guys
i am working on some project and fetch data from sql database and fill in to Datatable, it work fine, but their is performance issue!
how can boost and any suggestion regarding to the performance!


What I have tried:

using (SqlCommand cmd = new SqlCommand("sp_loadGuest", MainClass.con))
    cmd.CommandType = CommandType.StoredProcedure;
    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        DataTable dt = new DataTable();
        da.Fill(dt);               // it takes many sec
Updated 28-Jan-23 1:21am
[no name] 31-Oct-21 13:04pm    
You start by understanding what the stored procedure does.
Member 14921707 31-Oct-21 13:10pm    
it is just load my sql server it only take 0.01 sec
Dave Kreskowiak 31-Oct-21 13:18pm    
Without seeing the stored procedure code and understanding the database structure, it's impossible for anyone to tell you what's going on. The problem is not the C# code, but the database.
Pete O'Hanlon 31-Oct-21 14:49pm    
How much data are you trying to load?
Member 14921707 1-Nov-21 13:55pm    
30 rows with 27 column

A DataAdapter isn't the same as a DataReader: it does not return immediately and fetch data row by row from the DB. Instead, it waits for the data base server to completely load all data into an internal table, which is then transferred as a single stream to the presentation language app and loaded en masse into the DataTable. Only then when the complete set of rows is ready does the Fill method return to your code.

So if your SP collects a large chunk of data, that means a solid chunk of memory allocation on the server, followed by a good chunk of bandwidth between the server and the app PC, then a further chunk (or more likely a lot of chunks) of memory allocation on the PC before the saved data can be handed to you.

That's bot ten same as running a query directly on the server: that doesn't necessarily have to wait before starting to display rows, and may appear to be considerably faster purely because it starts displaying data immediately any is available.

We can't tell: any problem like this requires access to the code on both the PC and the DB server, together with the actual DB data it is processing while the code is running on the actual systems that show a problem. And we have access to none of that!
Share this answer
Member 14921707 1-Nov-21 13:48pm    
here is my sp


ALTER PROCEDURE [dbo].[sp_loadGuest]
-- Add the parameters for the stored procedure here
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

-- Insert statements for procedure here
select as 'id', as 'Name',
g.host_by as 'Host By',
g.purpose_of_visit as 'Purpose Of Visit',
g.companyName as 'Company Name',
g.occupication as 'Occupication',
g.address as 'Address', as 'City', as 'Country',
g.contact_no as 'CountactNo' , as 'Email',
g.profile_image as 'profile Image' ,
g.doc1_image as 'Doc1 Image',
g.doc2_image as 'Doc2 Image',
g.gender as 'Gender',
g.religion as 'Religion',
idType.card_type as 'Id Type',
g.id_no as 'Id No',
g.note as 'Note',
g.created_at as 'Created At',
g.updated_at as 'Updated At', as 'idTypeId', as 'vechicleId',
vi.brand_id as 'Vechilebrand',
vi.model as 'VechileModel',
vi.vechile_no as 'VechileNo',
vi.color as 'Color'
from guest_table g
inner join id_type_table idType on g.id_type_id
LEFT JOIN vechile_info_table vi on ( OR ( g.vehicle_id IS NULL AND IS NULL))
order by g.created_at desc

Member 14921707 1-Nov-21 13:52pm    
currently i am working on 30 list, imagine what if i have 200,000 guest, which is common in my case! i know it related with my computer memory, some time it work like charm and some time it take more than 7 sec! after the problem i run in my sql which fast then test in from the app it became ok!
OriginalGriff 2-Nov-21 3:22am    
Which probably answers it: caching. After the first query, SQL Server already has the data in memory and doesn't need to "go looking for it" on the disk, which will always be slower ...
Herman<T>.Instance 2-Nov-21 8:40am    
Exaclty: A DataAdapter first fetches all data before it exposes to the client. SqlDataReader streams your data so it can be exposed faster to the client. It exposes as long as the stream is running, making your application more fluid.
Use that one
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
DataTable dt = new DataTable();
cmd.CommandTimeout = 3600;
Share this answer
Richard Deeming 30-Jan-23 5:07am    
Increasing the command timeout might avoid an exception, but it won't help the OP with improving the performance of their query.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900