Hello,
What is the best approach or ways to solve a very long SQL statement, like maybe 50 pages etc that does vast amounts of complex calculations with many different types of conditions.
Would it be better to do it in store procedures such as cursors and views that would make the query more concise or what is the better approach, method to write this types of query or problem.
Break it down into distinct units you can call for example in parallel using ssis. 50 pages is not maintainable nor sustainable. That's just crazy big.
Thanks for your advice,
True, just saying it’s big.
But why SSIS, I am new to SSIS. I thought SSIS is use for ETL, data migration, and integration. Also not sure if I have SSIS for this one, will have to see.
And this query is and can be use repeatedly by possibly many people. And has the chance of changing over time when we move in different time periods as the time pass by.
Wouldn’t a store procedure and a view better for this. For example an executable that automatically runs a store procedure to update the view for instance monthly and the view can be call upon by whoever it wishes ?
SSIS because you can run many different pieces in parallel in different Work Flow Tasks.
procedure and view would be much more better.....if they were not 50 pages long. Are you saying the code itself is 50 pages long? or the result of the complex query.
Eventually you want a final data result you want to use to answer a question, am I correct? or are you looking to get different results in different tables and they depend on each other?
The code is 50 pages long not result.
The results might just be like 400 to 500 rows and 25 to 30 columns. We like the result to be just one instead of multiple pieces. I think the result is better to be a view because so we can call on it with connection tools and use it to yes answer questions, use, or analyze or charts.
I thought is better to use store procedure and view because you’re right it would be hard to maintain the code and you have to change the date as time pass by so the calculation is right and who knows how long it can take to run it. Store procedure to create the view and use a program to update the store procedure. The code can be concise because you can use cursor in store procedure. Would that work ?
I don’t know if I have SSIS, but with SSIS how does that work if we want just one result, and the query is still big and complex.
hi johnse
what yosiasz is saying one idea !!!
Another idea .......... is
you have 50 pages .
..over all layout ...
each part drill down another level ..
each part drill down to another level
to the most base
the concept is
to be able to understand the whole picture
and also the most minute details
very very quickly and easily
the below link has .. HOW TO DO IT !!
it will take time and pratice for you to understand how to do it
then it becomes VERY EASY and ROUTINE
hope it helps
You should not be editing code based on changing dates, it should handle this dynamically
So you have a stored procedure that dynamically creates views?
Cursors could be notorious for performance.
Can you please in simple terms what this 50 page stored procedure does?
you break up the sql code into distinct piece and then you could have one last sql code that aggregates all of the results from each piece to give you your final answer.
The reason why I say a store procedure, view and executable to run it is because is not someone running it every day or few times a day. It’s run like once a month and the result is a view that is good for one month until the next months update. When new month have the executable update the big store procedure query again. It can be run over night but only once a month.
I don’t know SSIS to know if that can do it better, maybe it can. Also I don’t think I have SSIS, would be nice if I am good at it and have it though.
I am still working on the logic of this. But will have it and let you know.
So you have a stored procedure that dynamically creates views?
No updates the view via store procedure.
I still do not understand why you are updating views via stored procedures. the idea of a view is that it is static unless underlying schema/business rules/etc has changed.
stored procedure should have variables that could dynamically change such as dates. the view stays as is, static. then no auto generated code needed
I was thinking of a view because it allows usage of it unless a store procedure can be allow usage too.
Sorry I am not understanding what you mean by usage?
Could you tell us what you are trying to accomplish. what is the end result. when you get the end result who uses the end result. what does the end result look like. is it financial, is it statistics on sports, ....
Hi johnse.. I would like to see your query, for sure it can be replaced by a stored procedure and a good planned table/view schema. Let me know if you accept the challenge.
Sorry I will let you know, just working on a project right now. By usage I meant create a view that other people can just call upon it. The end result is to get this view that updates with new data so when someone access it they are getting the updated result.