What is the best solution: a long stored procedure or several sql queries?
-
Hi there!!
I'm developing a Web application in ASP that uses several MS SQL databases.
I have to extract information from those different databases. So I wonder if I should do it in a single stored procedure that will last about 10 minutes or in different sql queries from my ASP code??
Please, let know your opinions!!!
Thanks in advance!
Sansan
-
I'd say separate stored procedures
More chance of compiled plan re-use and definitely easier maintenance
-
Why do you only want to have the one stored procedure.
If it were me i'd create a seperate SP from each distinct task and then call them via the ASP. That way you benefit from caching and the results return quicker (hopefully
). -
Honest answer... It depends.
However, if your query is taking 10 minutes, I'd examine exactly what it is that you're doing as far as the basic process goes. Are you moving data unnecessarily? Do you have a correct indexing strategy? Could targeted denormalization in the design reduce the number of joins? Can you create stored procs instead of using ad hoc queries? Are you using tabular functions on data sets bigger than a dozen rows or so? Are you using cursors innappropriately? From the other post you said this was getting data from 100+ databases, why? Cross database joins are going to be pretty costly and don't have any good mechanisms for maintaining data integrity.
Simply asking lots of queries or one big query doesn't address the fundamentals of the problem. Turn on SQL Profiler to determine where the principal pain points are located. That'll get you started.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply