search

ErikEJ / SqlCeToolbox

SQLite & SQL Server Compact Toolbox extension for Visual Studio, SSMS (and stand alone)
Other
843 stars 175 forks source link

Very slow response which cause Visual Studio to hang when selecting thousands of records #746

Closed ido-ran closed 6 years ago

ido-ran commented 6 years ago

When I use Sqlite Toolbox in Visual Studio Server Explorer and I open a table which has about 200k records Visual Studio become Not Responding. If I wait for about 2 minutes it will eventually show the data.

Steps to reproduce

  1. Create a Sqlite database with a table of 200k records.
  2. Open Sqlite/Sql Server Compact Toolbox in Visual Studio
  3. Connect to the Sqlite file
  4. Open the table using Edit first 200 records
  5. Click on the SQL button at the bottom of the table view
  6. Remove the end of the SQL where it says LIMIT 2000
  7. Click OK
  8. Wait... wait... wait... 😃

Further technical details

Version 4.7.534.0 - 1,107,749 downloads

SQL Server Compact 4.0 in GAC - Yes - 4.0.8876.1 SQL Server Compact 4.0 DbProvider - Yes

SQL Server Compact 4.0 DDEX provider - No SQL Server Compact 4.0 Simple DDEX provider - Yes

SQL Server Compact 3.5 in GAC - No SQL Server Compact 3.5 DbProvider - No

SQL Server Compact 3.5 DDEX provider - No

Sync Framework 2.1 SqlCe 3.5 provider - No

SQLite ADO.NET Provider included: 1.0.107.0 SQLite EF6 DbProvider in GAC - Yes System.Data.SQLite DDEX provider - No SQLite Simple DDEX provider - Yes

Database engine: SQlite

Microsoft Visual Studio Community 2017 Version 15.8.5 VisualStudio.15.Release/15.8.5+28010.2036 Microsoft .NET Framework Version 4.7.02556

Installed Version: Community

ErikEJ commented 6 years ago

Why do you want to view 200.000 rows on the same screen?

ido-ran commented 6 years ago

Good questions. I don't need to see all the 200k on the same screen, but it's useful to be able to browse them. Fetching by pages will probably help.

I also use DB Browser for SQLite and it handles the same table much better.

I prefer to stay within VS, that's why I tried SQLite Toolbox.

ErikEJ commented 6 years ago

Try "Script as SELECT" and select option: View as Classic grid

ido-ran commented 6 years ago

That was much faster. Only took 10 seconds to start populating the data grid. Even if I remove the use classic (plain) grid it is still much faster than the edit record view.

That solves my problem, should I close this issue?

ErikEJ commented 6 years ago

Thanks, glad you found a solution