Posted by Gert-Jan Strik on 06/18/07 22:33

Hi Uncle Rico,

Writing your entire message in UPPER CASE is considered shouting, and
thus inappropriate.

I have had a quick look at your SP, but stopped reading after the first
cursor loop. What applies to normal text also applies to SQL: if it is
entirely written in upper case, then it is very had to read (especially
with the automatic wrapping because of the usenet post).

I have two questions for you:
1. Are you serious? Do you seriously expect someone to decode these 1K
lines of procedural code?
2. How fast is the SP running now (number of seconds), and how much
faster do you want it to be?


You did not post any DDL, nor an explanation what you are trying to do.
Although I don't know if it would have mattered...

So I will give give you some generic tips here that apply to your SP.

1. SQL is a set oriented language, not a procedural language. It works
fastest if you process set and avoid cursors, loops, unnecessary
variable assignments, etc. It is quite likely that you can completely
avoid some (or even all) of your cursors and a few of your temp tables,
by writing the statements set oriented, in combination with the use of
the CASE expression.

2. Make sure your database is properly normalized. Only then will the
RDBMS be able to use the proper indexes.
For example, there are a few substrings of columns that seem to have a
special meaning. From a database design point of view, that is
incorrect. These substrings should be columns. Some of your current
columns seems to be a composites. For example:
- SUBSTRING(SENTNAME,15,2)
- SUBSTRING(@STR_FILENAME,13,4)

3. Make sure the proper indexes are in place.

4. Use SQL Profiler to examine which parts of your stored procedure take
the longest. Often one statement takes a very large part of the total
run time.

HTH,
Gert-Jan


Uncle Rico wrote:
>
> HI IS THERE A WAY TO GET MY PROC FASTER IT RUNS OK NOW BUT I JUST NEED
> IT FASTER THANKS.
>
> CREATE PROCEDURE SP_ORDERSUBMISSION @SENDRECORD BIT, @TYPE CHAR(3) AS
> SET NOCOUNT ON
>
[snipped a massive 919 lines]

[Back to original message]