How to optimize very long SQL script (all the scripting good practices has been done)

Question

1.00/5 (1 vote)

See more:

There is SQL script with almost 4000 lines of code. It sends the data back to C# object and mapping and report being done in RDLC.

The report is real time and most of the time gets time out for users. and user use this reports multiple times per day!

Any new technology can be used to make it more efficient?

Open to any idea

PS: the app is running on Azure!

What I have tried:

The script currently uses temp tables
all the indexes needed

Posted 6-Oct-21 5:33am

nicolasssssss

Updated 6-Oct-21 16:21pm

Add a Solution

Comments

CHill60 6-Oct-21 11:46am

Without seeing it or knowing anything about your database, we can hardly comment. I doubt all the scripting good practices have been done or it wouldn't be 4000 lines long and wouldn't time out.

dotarsoyak 6-Oct-21 12:17pm

Hi, Has you tried to use an framework, tool or caching mechanism for the report? , I heard that the SQL reporting services are useful.

Other causes can be the server has not the enough resources to process all requests. May be you need to scalate resources, even if you are on azure.

You need to find bottlenecks on your script with the sql profiler and sql query analizer.

Dave Kreskowiak 6-Oct-21 13:25pm

4000 lines of code in an SQL script is not "best practice", at all.

Beyond that, there's nothing else anyone can say given the information you posted.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2021-10-06T16:21:00

Quote:
How to optimize very long SQL script (all the scripting good practices has been done)

The problem is that you gave us exactly the right mix of told and untold information so that we can't give you useful tips.
Possibles solutions depend on:
- Database structure, tables and indexes
- size of tables
- rate of database changes (inserts, updates, deletes)
- meaning of database
- meaning of report
- The 4000 lines script itself

A solution will depend on all this and probably access to your database.
This is a job for database designer.