2

I have a code first create database table which I truncate and insert approximately 12000 records in with a c# script and Entity Framework Core 2.2.6.

I have 5 indexes on the table which I need to recreate after I do my database work. I can of course do this manually each time after I run my script, but being a programmer, that feels weird.

I tried finding a way to do this with EntityFrameworkCore, but I cannot seem to find it.

As a last resort I can execute a SQL command of course, but I was wondering whether there is some EntityFrameworkCore functionality that I am overlooking.

Or are there other ways of doing this more efficiently?

edit:

I run my script each time I receive a new DB from a third party to create our own from it, now in development that is roughly each month, later that will be less, so manually is an option, but I have an allergy for doing things manually

Community
  • 1
  • 1
Daniël Tulp
  • 1,745
  • 2
  • 22
  • 51
  • Do you need to insert the data every time the application runs? Or is this a one time thing? – Dawood Awan Mar 04 '20 at 11:02
  • EF Core is an ORM. Physical database maintenance (other than creating/migrating) is outside its responsibilities. Shortly, there is no other functionality than executing raw SQL. – Ivan Stoev Mar 04 '20 at 11:11
  • @DawoodAwan see my edit – Daniël Tulp Mar 04 '20 at 11:28
  • @IvanStoev I was thinking that this might be the case, so now I am looking into stored procedures, which I can call with https://www.entityframeworktutorial.net/efcore/working-with-stored-procedure-in-ef-core.aspx – Daniël Tulp Mar 04 '20 at 11:29
  • If you are creating a new database each time you get a new database from 3rd party - you can always include the script in a database migration. Drop indexes, Truncate, Insert then recreate the index. Like that you will always know that the script has run and is automated (so you don't have to anything manually) – Dawood Awan Mar 04 '20 at 11:34
  • The are no indexes in SQL Server. Some database have links between table. SQL Server you have to do a join to marge tables every time you use more than one table. – jdweng Mar 04 '20 at 12:03
  • 3
    @jdweng huh? there are definitely indexes in SQL Server database tables. Are you sure you are also talking about Microsoft SQL Server? – Daniël Tulp Mar 04 '20 at 13:39
  • For 12,000 rows I wonder if the indexes are even used. The only non T-SQL script solution I could think of would be SMO but I doubt that is included in EFCore. I guess you need to take the T-SQL option, but again, I doubt your indexes are even used. – Nick.Mc Mar 04 '20 at 13:43
  • I think you mean keys not index. The keys have a hash table to make lookup quicker. There is no actual address link between the tables that need updating. The hash is updated when a new key is added. – jdweng Mar 04 '20 at 15:12
  • @jdweng https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-ver15 – Daniël Tulp Mar 05 '20 at 07:55
  • The link says : Indexes are automatically maintained for a table or view whenever the table data is modified. – jdweng Mar 05 '20 at 08:07
  • that is correct, but they do get fragmented (I do not know why exactly) resulting in poor performance – Daniël Tulp Mar 05 '20 at 08:26

1 Answers1

1

I ended up using a stored procedure which does the below taken from here

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80 
DECLARE TableCursor CURSOR FOR
SELECT QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))+'.' + QUOTENAME(name) AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
Daniël Tulp
  • 1,745
  • 2
  • 22
  • 51