| Author |
Topic |
|
jhermiz
3564 Posts |
 Posted - 2005-05-18 : 10:49:51
|
The software your management team bought is performing like crap..you get their IT / programmer guys to look at the problems..you say "why didn't you guys use stored procedures" and the developers say "whats that"
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-05-18 : 10:57:35
|
| I hear ya.We bought a timeclock program with a SQL backend, worked ok in the begining but got slower and slower.I got into the code and found loop upon loop at the front end!Said the heck with it and built my own front end.JimUsers <> Logic |
 |
|
|
jhermiz
3564 Posts |
Posted - 2005-05-18 : 11:03:49
|
Doing the same thing :( it gets worse and worse...Here is a compiled list I had to discuss with them:- Inline SQL (built on client) vs Stored ProcedureWhen you first run you stored procedure it gets compiled much like a program. Compiling means that the sproc gets parsed by the sql server parsers, all it's statements get optimized in the optimizer and based on that the execution plan is built (ctrl+K in QA). By using the sproc over and over again the process of compiling isn't done anymore because the sproc is already compiled and it’s execution plans are cached in servers memory (RAM). This gets flushed when server restarts of course.In inline sql the compilation must happen each time the statement is run. Also sproc’s are more secure than inline sql because of sql injection attacks. When a sproc is built/ compiled its execution plan is cached so there's no need to create it again on each run.- Proper indexing This is important for speed and performance in general. With well set indexes a performance boost can be even 10-fold, that is because if there aren't any indexes the entire table scans are used. This means that the data in the table is accessed and not the data in the index. When joining or filtering data it is very handy to ensure indexing is done whether it is clustered or not. Think of this like a phonebook, if the last name is indexed then one can easily go directly to the person otherwise an entire scan is done in the phonebook just to find that name.- SQLWith non parameterized sql statements the plans are also cached but they expire way more quickly. (what we seem to have right now)- Separation of data (tiering)Stored procedures separate the processing of data on the SQL Server box and do not communicate directly with the client. The web server only knows how to call the stored procedure, the store procedure gets executed on the database server and the result setis returned to the application. The application should only be a GUI dummy interface with nothing more than graphics / buttons. This is like a black box concept, the web server doesn’t care about the details of the sproc, it just knows how to display the data.- NormalizationDuplication of fields in database, key items are found in other tables that should not be there (redundancy, proper normalization techniques).This could be eliminated by relating the tables to one another, there are no true relationship (primary key to foreign key).This means relational integrity is enforced on the client. In other words data can easily be corrupted (deleting parents have children that remain). -Cascade updates and deletes are not enabled on any of the tables- ODBC vs. OLECurrent application uses ODBC type of connection. ODBC is no longer the preferred method to connect to MS SQL Server database. The current preferred method is to use OLE-DB driver connection. It is more reliable and much faster than the odbc version.References: http://www.positron.gr/support/win2000/asp_ado.html"OLEDB is the successor to ODBC, a set of software components that allow a "front end" such as GUI based on VB, C++, Access or whatever to connect with a back end such as SQL Server, Oracle, DB2, mySQL etal. In many cases the OLEDB components offer much better performance than the older ODBC." (http://www.microsoft.com/data.)
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url] |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2009-04-14 : 14:52:57
|
| http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=123776Old thread, but look, its alive again![Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQL or How to sell Used CarsFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-04-17 : 06:44:48
|
| >> In inline sql the compilation must happen each time the statement is run.Not true>> With well set indexes a performance boost can be even 10-foldOften more like hundreds of thousands fold.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2009-04-17 : 09:20:44
|
maybe the cursor-lovers come from the oracle world? I hear cursors are the way to do things there.
elsasoft.org |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-04-17 : 11:32:18
|
| It is. Oracle triggers are cursor-based. I'm pretty sure all operations are, they have less overhead on cursors than SQL Server does. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-20 : 06:18:19
|
You know you're in trouble when...You have a stored procedure which starts a job, which calls a stored procedure, which opens a command window, which executes osql against same database as the stored procedure originally executed.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-04-20 : 06:44:43
|
| You know you're in trouble when...Developers finally listen to you when you say cursors are bad and then you find out they spent weeks replacing them with while loops that do exactly the same thing instead.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-04-20 : 09:53:57
|
| Been there, done that, although in my case I was taking over for a DBA who was proud to show me his "cursor-free" code, which employed a series of nested WHILE loops instead.I spent the day after he left dumping the cursors and reduced execution time from six hours to six minutes.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2009-04-20 : 23:55:56
|
quote:
Originally posted by Peso
You know you're in trouble when...You have a stored procedure which starts a job, which calls a stored procedure, which opens a command window, which executes osql against same database as the stored procedure originally executed.
E 12°55'05.63"N 56°04'39.26"
does the osql call the original proc? I hope so!
elsasoft.org |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-04-21 : 00:37:32
|
Luckily no. It just does an insert from one table to another.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
|
You know you're in trouble when...