Freezes when trying to run large SQL script

[KHVBui]

• Issue Type: Bug
• Extension Name: sqltools
• Extension Version: 0.23.0
• OS Version: Darwin x64 20.6.0
• VS Code version: 1.67.2

{
	"messages": [],
	"activationTimes": {
		"codeLoadingTime": 59,
		"activateCallTime": 14,
		"activateResolvedTime": 12,
		"activationReason": {
			"startup": true,
			"extensionId": {
				"value": "mtxr.sqltools",
				"_lower": "mtxr.sqltools"
			},
			"activationEvent": "*"
		}
	},
	"runtimeErrors": [],
	"runningLocation": {
		"affinity": 0,
		"kind": 1
	}
}

[gjsjohnmurray]

Which driver? By large do you mean one that returns a lot of rows? Or that selects a small number of records from a large table?

[KHVBui]

I basically had a script that created table and populated it with many lines of values using a lot of lines of "INSERT INTO". The SQL file was 1.9 MB so it's a lot of queries.

When I ran chunks of the script at a time instead of the whole thing at once, it worked.

I attached the SQL script below as a txt file.

Chinook_PostgreSql.txt

[davehowell]

This is not an issue with SQL Tools, you're going to run into this same issue no matter which SQL client you use. There is no strict limit in postgres for how large the query can be but its a lot of parsing to do and there are a few workarounds or alternatives.

1. Chunk it into a few scripts - as you already tried.
2. use psql and/or COPY FROM to do bulk inserts, see https://dba.stackexchange.com/a/130211 and https://stackoverflow.com/a/36879218/1335793
3. Make the queries shorter by inserting multiple rows in a single statement as per the postgreSQL docs
   e.g.

INSERT INTO films (code, title, did, date_prod, kind) VALUES
    ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
    ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

[Anutrix]

Same issue when I just open any large(25000+ lines) .sql file in VSCode editor.
renderer1.log has:

[2022-09-15 01:06:03.179] [renderer1] [info] UNRESPONSIVE extension host: starting to profile NOW
[2022-09-15 01:06:08.316] [renderer1] [warning] UNRESPONSIVE extension host: 'mtxr.sqltools' took 88% of 5087.599ms, saved PROFILE here: '<REDACTED>' [{"id":"gc","total":592679,"percentage":12},{"id":"mtxr.sqltools","total":4494678,"percentage":88},{"id":"self","total":135,"percentage":0}]

VSCode doesn't freeze but all actions like global searching, git features, etc. just don't finish. As if they are waiting for some action to be finished. Making it unusable till restart.

Closing and opening the folder/vscode fixes it until I open the large file again.
Same error is seen in rendered1.log after each slow down.

Using SQLTools with MySQL driver on Intel Mac.

[gjsjohnmurray]

@Anutrix if your issue is about the time it takes to open and format a very large SQL query, #977 may be relevant.

[Anutrix]

@Anutrix if your issue is about the time it takes to open and format a very large SQL query, #977 may be relevant.

@gjsjohnmurray Not exactly. I don't need to format it. Just opening a large .sql file makes VSCode completely unuseably slow. Searches never start/end. Every extension actions gets stuck. As if waiting for some previous action to end.

[gjsjohnmurray]

@Anutrix please open a new issue for your problem. This one is about running a large SQL script.

If possible please attach a .sql that demonstrates the problem to your new issue. Make sure it doesn't contain any confidential data.