Click here to Skip to main content
15,867,453 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!

thanks!

What I have tried:

C#
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
        ...
    }
}
Posted
Updated 28-Jan-23 1:21am
v2
Comments
[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 guest..in 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

Use that one
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
cmd.CommandTimeout = 3600;
da.Fill(dt);
}
 
Share this answer
 
Comments
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.
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
 
Comments
Member 14921707 1-Nov-21 13:48pm    
here is my sp

```
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

-- Insert statements for procedure here
select g.id as 'id',
g.name 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',
g.city as 'City',
g.country as 'Country',
g.contact_no as 'CountactNo' ,
g.email 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',
idType.id as 'idTypeId',
vi.id 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 =idType.id
LEFT JOIN vechile_info_table vi on ( g.vehicle_id=vi.id OR ( g.vehicle_id IS NULL AND vi.id IS NULL))
order by g.created_at desc

END
```
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.

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