Angarak Posted January 4, 2009 Share Posted January 4, 2009 Just doing a bit of work on a website at the moment that uses SQL Server 2000 to store a fair amount of content. The DB contains several tables and I'm currently working on a search feature. I have it working as desired using a dynamic SQL Query (VBScript). The query pulls data from 4 tables using the data provided by the user from the search form that contains 5 fields: :: Therapist Name [Text Field] (works on a partial match) :: Therapy Offered [select Field] (using a hard coded list of therapies) :: Other Therapy [Text Field] (for therapies not provided in the select field above) :: Postcode [Text Field] (works on a partial match) :: Gender [select Field] (male/female) Since the search feature is likely to be used quite a bit I was looking into pro's and con's of converting my dynamic sql into a dynamic stored procedure. I came across the following quote on http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx that references 'cache execution plan' in SqlServer's Books Online: SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans. Going off the above, the authors argument is that there would be no real performance benefit switching from a dynamic sql query to a dynamic stored procedure (though I guess it would still cut down on network bandwidth). Im not familiar with the in's and out's of SQL Server 2000, so I was wondering what you guys think with regards to the above from your experiences. In my case, the average length of my dynamic sql query (assuming user completes all fields in form) is approx 1000 characters Cheers Simon Quote Link to comment Share on other sites More sharing options...
grahamc Posted January 4, 2009 Share Posted January 4, 2009 Any query that is used multiple times, will benefit from being a stored procedure. Dynamic SQL is a BIG no-no. It plays havoc security and opens you up to SQL injection attack. It also makes performance tuning a tad more difficult, as the execution pan will not always be the same. Post or PM me the query that you are using, and I will mke some more recommendations. Quote Link to comment Share on other sites More sharing options...
RobUK Posted January 4, 2009 Share Posted January 4, 2009 Hi Simon, Dynamic stored proceedures don't tend to offer improvements in speed. They are more secure though. Saying that you can use prepared statements to achieve better security. The best way to test this for yourself is to use SQL Profiler which comes with enterprise manager, this enables you to see exactly what resources etc that each will use... Hope this helps Rob Quote Link to comment Share on other sites More sharing options...
Angarak Posted January 4, 2009 Author Share Posted January 4, 2009 Thanks Graham and Rob for your prompt and helpful replies. Graham, I will PM you with my current dynamic query in a moment. Any recommendations would be greatly appreciated. Rob, I'll look into it, like I said Im not that familiar with SQL Server in general, but I have just ordered a book from amazon that I hope will be a good SQL Server 'bible' Cheers Simon Quote Link to comment Share on other sites More sharing options...
grahamc Posted January 4, 2009 Share Posted January 4, 2009 Thanks Graham and Rob for your prompt and helpful replies. Graham, I will PM you with my current dynamic query in a moment. Any recommendations would be greatly appreciated. Rob, I'll look into, like I said Im not that familiar with SQL Server in general, but I have just ordered a book from amazon that I hope will be a good SQL Server 'bible' Cheers Simon no worries, send me your email, I have SAMS teach yourself sql in 21 days, its quite good and electronic Quote Link to comment Share on other sites More sharing options...
Angarak Posted January 4, 2009 Author Share Posted January 4, 2009 PM Sent The book I've just ordered from Amazon is Professional SQL Server 2000 Programming (Programmer to Programmer). I've had other books from the same publisher in the past and found them well written and structured. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.