• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 749
  • Last Modified:

Query String vs Stored Procedure preformance (500 points)

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
mcsdguyian
Asked:
mcsdguyian
1 Solution
 
dfiala13Commented:
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

Technology Partners: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now