question

35400361 avatar image
0 Votes"
35400361 asked 35400361 commented

INSERT INTO MULTIPLE TABLES

Hello, I need your help

I need your help. I am facing a problem, I don't know why.
For example, I have 1000 rows. For every row, I need to write in other five tables. We use SQL Server 2016 express.
I make five queries for every row for each table. The query is complex with many inner joins and subqueries and pivot and cte.
It takes about 10 min and I think it is extremely slow for 1000 rows.

In those five tables, I have indexes and clustered primary keys, I have not foreign keys.

Which you believe is the appropriate solution?
To drop indexes before insert?
To split every query of five tables into smaller and perhaps put them in views?
To use stored procedures?
To load a whole query of 1000 rows in a datatable and loop through every row?
I believe from some checks for example when I drop the indexes it was not made a big difference, it was still slow.

sql-server-generaldotnet-csharpsql-server-reporting-services
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @35400361

Can you paste your query statement ,for I can reproduce your issue and find a solution.

0 Votes 0 ·

Hello thank you for your response

I make first below query:

142476-queries.txt


0 Votes 0 ·
queries.txt (29.1 KiB)

1 Answer

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered 35400361 commented

These queries are indeed complex, and maybe beyond what is realistic to assist with in this forum.

It may help to split up the query in parts and produce intermediate results in temp tables. This does not the least apply if there are elements common between those two queries.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks you for your answer

0 Votes 0 ·