Hi all.

Just a quick question about some behaviours I am seeing with a datawindow
I've developed.

I was handed an existing report for an application and asked to make it more
efficient and run faster.  The existing report is an external that derives
its data from a SQL Server 7 stored procedure and it ran like a pig.  The
stored procedure creates a temp table, selects data into it, and then
supplies that data to the external.

Not being a huge fan of stored procedures to begin with, I rewrote the SQL
for the report's data just as a SQL Select in a freeform dw and now the
report runs fast, at least 3 times faster than the existing report.

Now, here's the question.  I know that a lot of people tout SPs as a
transaction and processing saver since all of the work is done on the server
but if the general case is that a straight Select will run so much faster
why would anyone bother to use them other than in the case that a straight
Select would be so complex that the SP makes sense from a conceptual point
of view?

Just curious about this.  My place of employment likes to use SPs and if I
can have some evidence that they aren't "better" then that would be cool.

TIA,
Erin

PS:  I've using PB6.5.1 and MS SQL Server 7.

Stored procedures are used for other reasons. For example, changes can be
made to the sp logic with needing to redeploy the application.

Poorly designed stored procedures can explain the performance you describe.
If you are able to write a SQL statement that yields the equivalent results
as the stored procedure that is 3 times faster, you may need to investigate
the design and implementation of the stored procedure. You also need to
consider where the performance is being lost. In your example, the TEMP
table is a contributor to the problem. However, the use of an external
datawindow (assuming I read the last sentence of the 2nd paragraph
correctly) may be the bigger problem. Have you tried using the SP as a
direct source to a datawindow?

/ck

"Erin Peterson" <erin.peterson@gnb.ca> wrote in message
news:sy5U0FCJCHA.1020@forums.sybase.com...
> Hi all.
>
> Just a quick question about some behaviours I am seeing with a datawindow
> I've developed.
>
> I was handed an existing report for an application and asked to make it
more
> efficient and run faster.  The existing report is an external that derives
> its data from a SQL Server 7 stored procedure and it ran like a pig.  The
> stored procedure creates a temp table, selects data into it, and then
> supplies that data to the external.
>
> Not being a huge fan of stored procedures to begin with, I rewrote the SQL
> for the report's data just as a SQL Select in a freeform dw and now the
> report runs fast, at least 3 times faster than the existing report.
>
> Now, here's the question.  I know that a lot of people tout SPs as a
> transaction and processing saver since all of the work is done on the
server
> but if the general case is that a straight Select will run so much faster
> why would anyone bother to use them other than in the case that a straight
> Select would be so complex that the SP makes sense from a conceptual point
> of view?
>
> Just curious about this.  My place of employment likes to use SPs and if I
> can have some evidence that they aren't "better" then that would be cool.
>
> TIA,
> Erin
>
> PS:  I've using PB6.5.1 and MS SQL Server 7.
>
>

Just to add to Chris' comments a bit:

Temp tables are not always the culprit, sometimes they are the savior.  I 
rewrote a stored proc that was taking 40+ seconds to use a temp table and 
got the performance down to 3 seconds.

The bottom line for any piece of SQL is to see what is necessary to 
optimize its execution.

In your particular case, you could have left the call in a stored proc and 
simply replaced the existing logic with your single SQL statement.  This 
would pretty much satisfy everyone's needs.  Then, as Chris pointed out, 
simply make the stored proc the data source of a dw and you're done.

steve 
[TeamSybase]

>>Stored procedures are used for other reasons. For example, changes can be 
made to the sp logic with needing to redeploy the application. Poorly 
designed stored procedures can explain the performance you describe. If you 
are able to write a SQL statement that yields the equivalent results as the 
stored procedure that is 3 times faster, you may need to investigate
the design and implementation of the stored procedure. You also need to 
consider where the performance is being lost. In your example, the TEMP 
table is a contributor to the problem. However, the use of an external 
datawindow (assuming I read the last sentence of the 2nd paragraph 
correctly) may be the bigger problem. Have you tried using the SP as a 
direct source to a datawindow?

/ck

"Erin Peterson" <erin.peterson@gnb.ca> wrote in message
news:sy5U0FCJCHA.1020@forums.sybase.com...
> Hi all.
>
> Just a quick question about some behaviours I am seeing with a datawindow
> I've developed.
>
> I was handed an existing report for an application and asked to make it
more
> efficient and run faster.  The existing report is an external that 
derives
> its data from a SQL Server 7 stored procedure and it ran like a pig.  The
> stored procedure creates a temp table, selects data into it, and then
> supplies that data to the external.
>
> Not being a huge fan of stored procedures to begin with, I rewrote the 
SQL
> for the report's data just as a SQL Select in a freeform dw and now the
> report runs fast, at least 3 times faster than the existing report.
>
> Now, here's the question.  I know that a lot of people tout SPs as a
> transaction and processing saver since all of the work is done on the
server
> but if the general case is that a straight Select will run so much faster
> why would anyone bother to use them other than in the case that a 
straight
> Select would be so complex that the SP makes sense from a conceptual 
point
> of view?
>
> Just curious about this.  My place of employment likes to use SPs and if 
I
> can have some evidence that they aren't "better" then that would be cool.
>
> TIA,
> Erin
>
> PS:  I've using PB6.5.1 and MS SQL Server 7.
>
>

<<

If you create simple DW to represent one or two tables you simply use SQL 
Query or Quick select to get the source for your DW. It's really good and 
fast way of design. But often your logic is so difficult and you need to 
use SP as a DW's source. I'm not sure I could make the most of my DWs 
without SP by the PB's embeded SQL.
Another case of using SP is the security. Having SP you can asign rights to 
call it to some users but not any. So I often use SPs.
As for fast of execution: In general it's big part of exploration. For 
exsample SP works as you need but was designed not competently. So to 
optimize it you need to rewrite your SP.

There are many reasons why to use SPs.
Some important among them.

1) Performance 1. Even relatively short SPs are faster
    than the corresponding SQL statement because of the precompiled
    nature of an SP. If you manage to write an SQL statement
    which performs faster than the corresponding SP then replace
    the SQL statement in SP to this better one and you will have
    the same or better performance.

2) Performance 2. You save on the network trip from the client
    to server. Almost always the call string is shorter then
    the equivalent SQL statement.

3) Performance 3. If your task requires multiple steps (several
    SQL statements) then you will save more on the network trip
    mentioned above plus you will not transfer massive volumes
    of data between client and server.

4) Security. In order to perform data manipulation you do not
    need to grant table access (all levels). You will grant
    execute on the corresponding SPs. It has one more advantage.
    Nobody will access tables from ANY tools (ISQL, third party
    tools) or applications and destroy data integrity.

5) All validation and business rules are in one place (recall that
    not all of them can be declared and even implemented in triggers)
    so all applications accessing these tables comply with the same
    rules. Besides if you need to change them it will be done in
    one place which simplify maintenance and guarantee consistency.

6) Predictability. If your DB server is running in cost based
    optimization mode then your SQL statements performance can vary
    in broad range depending on available statistics which is often
    unacceptable especially for OLTP DBs. Precompiled SPs use the same
    execution plans which calculated at the compile time.

It is necessary to point out that there are some other considerations
related to multitier application which shift role of SP to middle
tier - application server. But still it is ONE place for the DB access.




Erin Peterson wrote:
> Hi all.
> 
> Just a quick question about some behaviours I am seeing with a datawindow
> I've developed.
> 
> I was handed an existing report for an application and asked to make it more
> efficient and run faster.  The existing report is an external that derives
> its data from a SQL Server 7 stored procedure and it ran like a pig.  The
> stored procedure creates a temp table, selects data into it, and then
> supplies that data to the external.
> 
> Not being a huge fan of stored procedures to begin with, I rewrote the SQL
> for the report's data just as a SQL Select in a freeform dw and now the
> report runs fast, at least 3 times faster than the existing report.
> 
> Now, here's the question.  I know that a lot of people tout SPs as a
> transaction and processing saver since all of the work is done on the server
> but if the general case is that a straight Select will run so much faster
> why would anyone bother to use them other than in the case that a straight
> Select would be so complex that the SP makes sense from a conceptual point
> of view?
> 
> Just curious about this.  My place of employment likes to use SPs and if I
> can have some evidence that they aren't "better" then that would be cool.
> 
> TIA,
> Erin
> 
> PS:  I've using PB6.5.1 and MS SQL Server 7.
> 
> 

All good points.
Disadvantages:
1. SPs are DBMS-specific, so if you need to support multiple DBMSs or you
need to change DBMS then you're in for a lot of work.
2. Re point (1) below.  The precompiled nature of an SP can often slow down
performance if you're not careful.  The execution plan is determined using
the data that was there when the SP was compiled.  The data at runtime could
be very different and require a different execution plan, and this could
slow things down enormously.  For instance, compile with no data in the
database and the plan will call for full table scans, run it with 1,000,000
rows, you'll still get a full table scan.  So you need to make sure that for
any SPs where the underlying data is quite dynamic, the SPs are recompiled
regularly.

S.

--
All views expressed in this message are my own and not necessarily those of
my employer


"Vladimir Gendler" <vgendler@socal.rr.com> wrote in message
news:3D28A5A7.1060407@socal.rr.com...
> There are many reasons why to use SPs.
> Some important among them.
>
> 1) Performance 1. Even relatively short SPs are faster
>     than the corresponding SQL statement because of the precompiled
>     nature of an SP. If you manage to write an SQL statement
>     which performs faster than the corresponding SP then replace
>     the SQL statement in SP to this better one and you will have
>     the same or better performance.
>
> 2) Performance 2. You save on the network trip from the client
>     to server. Almost always the call string is shorter then
>     the equivalent SQL statement.
>
> 3) Performance 3. If your task requires multiple steps (several
>     SQL statements) then you will save more on the network trip
>     mentioned above plus you will not transfer massive volumes
>     of data between client and server.
>
> 4) Security. In order to perform data manipulation you do not
>     need to grant table access (all levels). You will grant
>     execute on the corresponding SPs. It has one more advantage.
>     Nobody will access tables from ANY tools (ISQL, third party
>     tools) or applications and destroy data integrity.
>
> 5) All validation and business rules are in one place (recall that
>     not all of them can be declared and even implemented in triggers)
>     so all applications accessing these tables comply with the same
>     rules. Besides if you need to change them it will be done in
>     one place which simplify maintenance and guarantee consistency.
>
> 6) Predictability. If your DB server is running in cost based
>     optimization mode then your SQL statements performance can vary
>     in broad range depending on available statistics which is often
>     unacceptable especially for OLTP DBs. Precompiled SPs use the same
>     execution plans which calculated at the compile time.
>
> It is necessary to point out that there are some other considerations
> related to multitier application which shift role of SP to middle
> tier - application server. But still it is ONE place for the DB access.
>
>
>
>
> Erin Peterson wrote:
> > Hi all.
> >
> > Just a quick question about some behaviours I am seeing with a
datawindow
> > I've developed.
> >
> > I was handed an existing report for an application and asked to make it
more
> > efficient and run faster.  The existing report is an external that
derives
> > its data from a SQL Server 7 stored procedure and it ran like a pig.
The
> > stored procedure creates a temp table, selects data into it, and then
> > supplies that data to the external.
> >
> > Not being a huge fan of stored procedures to begin with, I rewrote the
SQL
> > for the report's data just as a SQL Select in a freeform dw and now the
> > report runs fast, at least 3 times faster than the existing report.
> >
> > Now, here's the question.  I know that a lot of people tout SPs as a
> > transaction and processing saver since all of the work is done on the
server
> > but if the general case is that a straight Select will run so much
faster
> > why would anyone bother to use them other than in the case that a
straight
> > Select would be so complex that the SP makes sense from a conceptual
point
> > of view?
> >
> > Just curious about this.  My place of employment likes to use SPs and if
I
> > can have some evidence that they aren't "better" then that would be
cool.
> >
> > TIA,
> > Erin
> >
> > PS:  I've using PB6.5.1 and MS SQL Server 7.
> >
> >
>

Agree.

Some additions.

1. SPs in almost all major DBs (MS is exception) can be written
    in Java. It will minimize (but not eliminate) some work
    during migration. Still "native" SPs are faster from DBs
    accessibility point of view.

2. As usual to solve the problem of different DBs with different
    SQL flavors one additional layer may be introduced. Now is
    popular to use three (and more) tiers solutions for this and
    many other purposes.




Simon Caldwell [TeamSybase] wrote:
> All good points.
> Disadvantages:
> 1. SPs are DBMS-specific, so if you need to support multiple DBMSs or you
> need to change DBMS then you're in for a lot of work.
> 2. Re point (1) below.  The precompiled nature of an SP can often slow down
> performance if you're not careful.  The execution plan is determined using
> the data that was there when the SP was compiled.  The data at runtime could
> be very different and require a different execution plan, and this could
> slow things down enormously.  For instance, compile with no data in the
> database and the plan will call for full table scans, run it with 1,000,000
> rows, you'll still get a full table scan.  So you need to make sure that for
> any SPs where the underlying data is quite dynamic, the SPs are recompiled
> regularly.
> 
> S.
> 
> --
> All views expressed in this message are my own and not necessarily those of
> my employer
> 
> 
> "Vladimir Gendler" <vgendler@socal.rr.com> wrote in message
> news:3D28A5A7.1060407@socal.rr.com...
> 
>>There are many reasons why to use SPs.
>>Some important among them.
>>
>>1) Performance 1. Even relatively short SPs are faster
>>    than the corresponding SQL statement because of the precompiled
>>    nature of an SP. If you manage to write an SQL statement
>>    which performs faster than the corresponding SP then replace
>>    the SQL statement in SP to this better one and you will have
>>    the same or better performance.
>>
>>2) Performance 2. You save on the network trip from the client
>>    to server. Almost always the call string is shorter then
>>    the equivalent SQL statement.
>>
>>3) Performance 3. If your task requires multiple steps (several
>>    SQL statements) then you will save more on the network trip
>>    mentioned above plus you will not transfer massive volumes
>>    of data between client and server.
>>
>>4) Security. In order to perform data manipulation you do not
>>    need to grant table access (all levels). You will grant
>>    execute on the corresponding SPs. It has one more advantage.
>>    Nobody will access tables from ANY tools (ISQL, third party
>>    tools) or applications and destroy data integrity.
>>
>>5) All validation and business rules are in one place (recall that
>>    not all of them can be declared and even implemented in triggers)
>>    so all applications accessing these tables comply with the same
>>    rules. Besides if you need to change them it will be done in
>>    one place which simplify maintenance and guarantee consistency.
>>
>>6) Predictability. If your DB server is running in cost based
>>    optimization mode then your SQL statements performance can vary
>>    in broad range depending on available statistics which is often
>>    unacceptable especially for OLTP DBs. Precompiled SPs use the same
>>    execution plans which calculated at the compile time.
>>
>>It is necessary to point out that there are some other considerations
>>related to multitier application which shift role of SP to middle
>>tier - application server. But still it is ONE place for the DB access.
>>
>>
>>
>>
>>Erin Peterson wrote:
>>
>>>Hi all.
>>>
>>>Just a quick question about some behaviours I am seeing with a
>>
> datawindow
> 
>>>I've developed.
>>>
>>>I was handed an existing report for an application and asked to make it
>>
> more
> 
>>>efficient and run faster.  The existing report is an external that
>>
> derives
> 
>>>its data from a SQL Server 7 stored procedure and it ran like a pig.
>>
> The
> 
>>>stored procedure creates a temp table, selects data into it, and then
>>>supplies that data to the external.
>>>
>>>Not being a huge fan of stored procedures to begin with, I rewrote the
>>
> SQL
> 
>>>for the report's data just as a SQL Select in a freeform dw and now the
>>>report runs fast, at least 3 times faster than the existing report.
>>>
>>>Now, here's the question.  I know that a lot of people tout SPs as a
>>>transaction and processing saver since all of the work is done on the
>>
> server
> 
>>>but if the general case is that a straight Select will run so much
>>
> faster
> 
>>>why would anyone bother to use them other than in the case that a
>>
> straight
> 
>>>Select would be so complex that the SP makes sense from a conceptual
>>
> point
> 
>>>of view?
>>>
>>>Just curious about this.  My place of employment likes to use SPs and if
>>
> I
> 
>>>can have some evidence that they aren't "better" then that would be
>>
> cool.
> 
>>>TIA,
>>>Erin
>>>
>>>PS:  I've using PB6.5.1 and MS SQL Server 7.
>>>
>>>
>>
> 
>