Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL deletes all rows and creates new rows for each change to the data #1589

Open
Loizzus opened this issue Sep 19, 2019 · 3 comments
Open

SQL deletes all rows and creates new rows for each change to the data #1589

Loizzus opened this issue Sep 19, 2019 · 3 comments

Comments

@Loizzus
Copy link

@Loizzus Loizzus commented Sep 19, 2019

When using the SQL database I have found that everytime any data is updated and saved the program deletes all existing rows from dbo.tblCons and creates a full set of new rows.

Problem Description

This causes the database to grow to a tremendous size very quickly when working with a small team even though the total number of rows is small. For example in 4 months my database of 40 rows is sitting at ID number 6619 as the latest increment. This causes simple queries like (select * from tblCons) to take up to 15 minutes to run. Shrinking the database did not help, however (UPDATE STATISTICS tblCons) did reduce the query time down to 5 minutes for the duration of the session.

Workaround

Workaround has been to delete the database periodically and reimport the rows into a fresh database.

Possible Solution

To solve I believe that changes to existing rows should be made by performing an UPDATE statement for that value on that row rather than deleting all the rows from the database and creating 40 new rows.
I was not able to test maintence plans to see if that could improve performance as I do not have it on my versions of MSSQL.

Your Environment

  • Version used: MSSQL server express installed on Windows or MSSQL server developer installed on linux docker
@farosch farosch pinned this issue Oct 3, 2019
@farosch farosch unpinned this issue Oct 4, 2019
@Filippo125

This comment has been minimized.

Copy link
Contributor

@Filippo125 Filippo125 commented Dec 28, 2019

I fix the issue in branch sql_update#1589. It has already been tested with mysql. As soon as i can i'll test with mssql.

@Filippo125

This comment has been minimized.

Copy link
Contributor

@Filippo125 Filippo125 commented Dec 29, 2019

I check mssql and fixed some issue on it.

@Loizzus

This comment has been minimized.

Copy link
Author

@Loizzus Loizzus commented Jan 1, 2020

Thank you so much!

@Loizzus Loizzus closed this Jan 1, 2020
@farosch farosch reopened this Jan 15, 2020
@farosch farosch mentioned this issue Jan 15, 2020
5 of 6 tasks complete
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.