INSERT INTO MULTIPLE TABLES

2021-10-21T00:15:34.017+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,484 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,772 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,097 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 100.1K Reputation points MVP
    2021-10-21T22:09:04.437+00:00

    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.


0 additional answers

Sort by: Most helpful