c# - Can SQL query affect my blazor app rendering perfomance? - Stack Overflow

admin管理员组

文章数量:1122832

OVERVIEW

I know this question is obvious but let me give some overview. So I wanted to delete data from multiple tables in my SQL Server database since some of those tables have FKs, constraints, etc... deleting them manually would've been a pain in the head so I made a little script to do all that (I'm using Dapper, btw).

-- Disable restrictions from FKs
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';

-- Delete data from tables
DELETE FROM dbo.Table1;
DELETE FROM dbo.Table2;
DELETE FROM dbo.Table3;
DELETE FROM dbo.Table4;
DELETE FROM dbo.Table5;
DELETE FROM dbo.Table6;
DELETE FROM dbo.Table7;
DELETE FROM dbo.Table8;
DELETE FROM dbo.Table9;
DELETE FROM dbo.Table10;
DELETE FROM dbo.Table11;

-- Enable restrictions from FKs
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL';

-- Reset Identities counters to 0
DBCC CHECKIDENT ('dbo.Table1', RESEED, 0);
DBCC CHECKIDENT ('dbo.Table2', RESEED, 0);
DBCC CHECKIDENT ('dbo.Table3', RESEED, 0);
DBCC CHECKIDENT ('dbo.Table4, RESEED, 0);
DBCC CHECKIDENT ('dbo.Table5, RESEED, 0);
DBCC CHECKIDENT ('dbo.Table6, RESEED, 0);
DBCC CHECKIDENT ('dbo.Table7, RESEED, 0);
DBCC CHECKIDENT ('dbo.Table8, RESEED, 0);
DBCC CHECKIDENT ('dbo.Table9, RESEED, 0);
DBCC CHECKIDENT ('dbo.Table10, RESEED, 0);
DBCC CHECKIDENT ('dbo.Table11, RESEED, 0);

THE PROBLEM

This was the script that I ran, after I ran it everything was fine until I started noticing when ever my app rendered data from my database (doing a call to retrieve my data) the render took to show the data took about 2 seconds and before I ran the script wouldn't even took a second it was instantly.

I don't have complex queries, a lot of data or changed my queries in my project and this. Here I show an example of how I show my an example of how I'm rendering my data in the UI:

The code explain itself, this is how I'm managing displaying data to the UI and this was rendering in an instant but now like I said before after running that script my rendering takes about 2 seconds to show the data as if I'm running a complex query or something.

WHAT I'VE TRIED

-- Reordering indexes
EXEC sp_msforeachtable 'ALTER INDEX ALL ON ? REORGANIZE';

-- Updating statistics in all tables
EXEC sp_msforeachtable 'UPDATE STATISTICS ?';

But this doesn't seem to work. Thanks in advance for the help.

OVERVIEW

I know this question is obvious but let me give some overview. So I wanted to delete data from multiple tables in my SQL Server database since some of those tables have FKs, constraints, etc... deleting them manually would've been a pain in the head so I made a little script to do all that (I'm using Dapper, btw).

-- Disable restrictions from FKs
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';

-- Delete data from tables
DELETE FROM dbo.Table1;
DELETE FROM dbo.Table2;
DELETE FROM dbo.Table3;
DELETE FROM dbo.Table4;
DELETE FROM dbo.Table5;
DELETE FROM dbo.Table6;
DELETE FROM dbo.Table7;
DELETE FROM dbo.Table8;
DELETE FROM dbo.Table9;
DELETE FROM dbo.Table10;
DELETE FROM dbo.Table11;

-- Enable restrictions from FKs
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL';

-- Reset Identities counters to 0
DBCC CHECKIDENT ('dbo.Table1', RESEED, 0);
DBCC CHECKIDENT ('dbo.Table2', RESEED, 0);
DBCC CHECKIDENT ('dbo.Table3', RESEED, 0);
DBCC CHECKIDENT ('dbo.Table4, RESEED, 0);
DBCC CHECKIDENT ('dbo.Table5, RESEED, 0);
DBCC CHECKIDENT ('dbo.Table6, RESEED, 0);
DBCC CHECKIDENT ('dbo.Table7, RESEED, 0);
DBCC CHECKIDENT ('dbo.Table8, RESEED, 0);
DBCC CHECKIDENT ('dbo.Table9, RESEED, 0);
DBCC CHECKIDENT ('dbo.Table10, RESEED, 0);
DBCC CHECKIDENT ('dbo.Table11, RESEED, 0);

THE PROBLEM

This was the script that I ran, after I ran it everything was fine until I started noticing when ever my app rendered data from my database (doing a call to retrieve my data) the render took to show the data took about 2 seconds and before I ran the script wouldn't even took a second it was instantly.

I don't have complex queries, a lot of data or changed my queries in my project and this. Here I show an example of how I show my an example of how I'm rendering my data in the UI:

The code explain itself, this is how I'm managing displaying data to the UI and this was rendering in an instant but now like I said before after running that script my rendering takes about 2 seconds to show the data as if I'm running a complex query or something.

WHAT I'VE TRIED

-- Reordering indexes
EXEC sp_msforeachtable 'ALTER INDEX ALL ON ? REORGANIZE';

-- Updating statistics in all tables
EXEC sp_msforeachtable 'UPDATE STATISTICS ?';

But this doesn't seem to work. Thanks in advance for the help.

• c#
• sql
• .net
• sql-server
• blazor

Share Improve this question Follow edited Nov 22, 2024 at 20:31 marc_s 754k183183 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Nov 22, 2024 at 19:32 TheMax370TheMax370 4711 silver badge1212 bronze badges 3

• 3 Please do not upload images of code/data/errors when asking a question. – Thom A Commented Nov 22, 2024 at 19:35
• 1 You need tools or options to validate your database performance vs your rendering performance. Capture your queries via the profiler. run those queries in SSMS. Check the execution times. Add an option to preload your data. Use preloaded data from memory. Check rendering performance. – Bart McEndree Commented Nov 22, 2024 at 20:35
• Easier to just drop and recreate those tables than do that script? – flackoverstow Commented Nov 23, 2024 at 9:03

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Have you checked index fragmentation? Try launching this query that gives you fragmentation percentage per table.

SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS 
DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc

Then try to rebuild your indexes instead of reorganizing.

本文标签： cCan SQL query affect my blazor app rendering perfomanceStack Overflow