[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Query String vs Stored Procedure preformance (500 points)

Posted on 2004-03-26
1
Medium Priority
?
747 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 12

Accepted Solution

by:
dfiala13 earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

656 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