Solved

Query String vs Stored Procedure preformance (500 points)

Posted on 2004-03-26
1
741 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 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

717 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