Solved

Query String vs Stored Procedure preformance (500 points)

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to resize column length with primary ket 4 32
Search Text in Views 2 27
SQL Server Pivot 5 37
Restore a log backup compressed 3 14
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

737 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