Re: NEED TO GO FASTER

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]
.

Relevant Pages

  • Re: Function based index not used with like operator
    ... search for is starting with the characters the user search for. ... SQL> set echo on linesize 132 trimspool on ... col2 from uppertest1 where upper ... 438 bytes received via SQL*Net from client ...
    (comp.databases.oracle.server)
  • Re: How to understand huge code
    ... product) I feel very difficult understanding the complete code flow. ... writing it in a semi-programming language helps reduce clutter. ... For things like SQL, I ask myself what the SQL is retrieving ...
    (comp.programming)
  • Re: Is there a way to keep the StreamWriter open?
    ... SQL that I can writing to a text file with some decient programming ... hardware and it would be able to keep up but not be faster. ... It is, in some ways, but the process writing the IIS logs is optimized ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Querying a DataSet/DataTable in .NET
    ... to writing an in-memory database engine. ... >>>I think it would be nice if we could have a SQL engine available on the ... >>> display only (I could put it in business objects so I can reuse the ...
    (microsoft.public.dotnet.general)
  • Re: Large dataset performance
    ... in SQL. ... Understand better set operations on SQL and ... application would be writing data to the database and this cannot ... of underlying principles. ...
    (comp.databases.theory)