Solved

Query String vs Stored Procedure preformance (500 points)

Posted on 2004-03-26
1
739 Views
Last Modified: 2008-02-20
I am trying to figure out the impact of passing a long querystring from my webpage to SQL Server, I know that it will slow things down a bit versus using a SQL Stored Procedure, but I need to do it this way.   .  It seems to work fine when I pass it from my web page to sql, I don't notice any slow down, but my concern is if I have alot of members running this query.  So if anyone has any Idea on the bandwidth usage and any other preformance issue that may arise with the web server or the Database Server.  It would be helpful.  

***This is the code***

sSQL ="SELECT M.MemberID , Z.Distance,M.Region,M.Smoker,M.Preference,M.Age,M.FAge, M.TAge,Distance FROM Member  M,(SELECT z.ZIPCode, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) as Distance FROM ZIPCodes z, RadiusAssistant(53151,40) r  WHERE z.Latitude <= r.MaxLat AND z.Latitude >= r.MinLat AND z.Longitude <= r.MaxLong AND z.Longitude >= r.MinLong AND CityType = 'D' AND ZIPCodeType <> 'M' AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= 40)as Z WHERE M.PostalCode = Z.Zipcode  Order by Z.Distance"


Thanks
mcsdguyian
0
Comment
Question by:mcsdguyian
1 Comment
 
LVL 12

Accepted Solution

by:
dfiala13 earned 500 total points
ID: 10692901
There are 6 impacts when using dynamic SQL vs stored procedures.

1) SQL Server Performance -- http://www.sql-server-performance.com/stored_procedures.asp
2) Bloated middle-tier/client code -- strings, especially large strings, can make for larger dll/application size and longer compilation times
3) Manipulation of large strings can be very memory intensive and affect performance and scalability
4) Maintenance -- intermingled SQL strings are a pain to debug and maintain.
5) Network traffic is increased (if the database is not on the same server)
6) Security -- using stored procedures allows you to restict permissions on the database to only allow execution of stored procedures.  Using dynamic SQL requires permissions to be granted to tables directly

That being said an occasional dynamic query isn't the worst thing in the world, and the one you are using is a 1/10 as huge as some I've seen.

BTW, you can always create the query dynamically in a stored procedure and execute it with
EXEC.  You don't get the benefits of an optimized query plan, but you do get the other benefits of using stored procedures.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question