Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Dasman
Yak Posting Veteran
79 Posts |
 Posted - 2011-07-29 : 16:55:03
|
| Hello All,Hope everyone has a great weekend! I have the following questions: First - I come from a matlab programming background. Does SQL have anything such as a For/While Loop? Say I have a Stored Procedure and I want it to run 5 times entering 5 different parameters? Is this possible? Or until a desired result is achieved? Second - Multiple Queries in a stored procedure. What is the syntax for this? Also can multiple unrelated queries be put in a stored procedure? Is this faster to process then say all those queries were in Table Valued Functions which are in one stored procedure, than say a stored procedure with all the queries in it? Third - Any good links/ ideas on how to optimize my code? One of my most important functions takes 6 seconds execution time and my boss says it should be run in less because it might have to be process for over 1000 ppl which would then take 6000 sec -> 1.6 hrs, which is not desirable.Sry for all the questions - Im just super curious. Thanks for reading! :)Dasman==========================Pain is Weakness Leaving the Body. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-07-29 : 17:36:48
|
a) Yes you can, but it is an undesirable solution. Think set-based.b) No. Most often a "divide and conquer" solution is faster because SQL Server then works with smaller sets of data.c) SQL Server can run things in parallell, so your calculation is wrong.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
pduffin
Yak Posting Veteran
68 Posts |
Posted - 2011-07-29 : 21:14:36
|
| Don't think of SQL as programming, it's not. It's scripting to retrieve and manipulate data. You can processes data record by record like a programming language but that's very inefficient in MS sql. Try to get a feel for SQL more before you try to enforce programming structures and designs upon a language which is not a programming one. You may just find that there are better ways of doing things and SQL will become a friend and good companion for your programming skills. |
|
|
|
Jason W
Starting Member
19 Posts |
Posted - 2011-07-29 : 22:57:53
|
| Hi,Good questions. 1. Here is a tutorial on implementing a WHILE loop (second section): http://www.sqloptimizationsschool.com/Pages/Advanced%20Concepts/Loops%20vs.%20Cursors.aspx2. Not sure what you are going for, but you can certainly have several queries in a procedure. You might need to update/insert data or possibly select a few datasets to perform calculations on before returning data from the procedure.3. This site will take you through a lot of the basics for optimizing code: http://www.sqloptimizationsschool.com/ |
|
|
|
Dasman
Yak Posting Veteran
79 Posts |
Posted - 2011-08-05 : 16:06:54
|
| Hello All,Thanks everyone for the helpful hints!Now A - what does "set based" mean.B - Is "CASE" a For/While Loop method?Thanks,Dasman==========================Pain is Weakness Leaving the Body. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-08-06 : 00:56:19
|
A)Think instead what you would like to do with a column rather than what you would like to do with an individual row.B) No. CASE is operating on a column. It's like a "Column-If".
N 56°04'39.26"E 12°55'05.63" |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-08-06 : 17:31:00
|
| >> First - I come from a mathlab programming background. Does SQL have anything such as a For/While Loop? Say I have a Stored Procedure and I want it to run 5 times entering 5 different parameters? Is this possible? Or until a desired result is achieved? <<The short answer is NO!!! SQL is declarative so think of it as being like nested function calls or APL operations on arrays and vectors. You can go out to a 3GL like T-SQL and write loop there. That sucks. An SQL programmer is more apt to create a table whose rows are your parameter list, then invoke it all at once with “SELECT My_Function( .. ) FROM Parameter_List;” as one statement. >> Second - Multiple Queries in a stored procedure. What is the syntax for this? Also can multiple unrelated queries be put in a stored procedure? Is this faster to process than say all those queries were in Table Valued Functions which are in one stored procedure, than say a stored procedure with all the queries in it? <<You just write the queries one after the other. And yes, you can write stinking bad code in SQL, just like you are proposing. You never had a basic software engineering course, have you? A module of code in ANY LANGUAGE should have strong cohesion (i.e. does one thing and only one thing) with loose coupling (always acts the way). Think of mathematical functions – do you want a “sine()” function or a “sine_or_unpredictable_random_number()” function? The TVP are proprietary to T-SQL and experienced programmers avoid them. Noobs use them to mimic scratch tapes when we are writing 1950's magnetic tape programs in SQL. They make a temp table, pass a partial result to the next step of a sequential process, etc. UGH! SQL programmers use ONE statement without materializing any partial results. Let the optimizer worry about it for you. >> Third - Any good links/ ideas on how to optimize my code? One of my most important functions takes 6 seconds execution time and my boss says it should be run in less because it might have to be process for over 1000 ppl which would then take 6000 sec -> 1.6 hrs, which is not desirable. <<It is very hard to optimize code that we cannot see that runs on unknown schema. Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you? Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL. --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
|
|
General Qs about SQL - Loops and SPs