Post reply

What is the best solution: a long stored procedure or several sql queries?

  • June 17, 2005 at 3:44 am

    #87761

     

    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

  • June 17, 2005 at 4:22 am

    #566347

    I'd say separate stored procedures

    More chance of compiled plan re-use and definitely easier maintenance

  • June 17, 2005 at 4:40 am

    #566357

    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 ).

  • June 17, 2005 at 5:23 am

    #566365

    Please dont multi post

    http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=120&messageid=191581

    http://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=191586



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • June 17, 2005 at 6:58 am

    #566385

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply