Hi Martijn,

This is my current scenario.

I have a SP that has the WHERE clause and looks something like(simplified version):

PROCEDURE GET_DATA(IN_USERID INTEGER)
RETURNS(
OUT_NAME varchar(50),
OUT_SIZE integer)
AS
BEGIN
FOR
SELECT
NAME,
SIZE
FROM DIRS
WHERE USER_ID = IN_USERID --USER_ID is a primary key
INTO OUT_NAME, OUT_SIZE
END

I also have another SP that does ORDERing:

PROCEDURE GET_DATA_WRAPPER(IN_USERID INTEGER, IN_ORDERBY varchar(32))

RETURNS(

OUT_NAME varchar(50),

OUT_SIZE integer)

AS

BEGIN

FOR
EXECUTE STATEMENT 'SELECT * FROM GET_DATA(' || :IN_USERID || ') ORDER BY ' || :IN_ORDERBY
INTO OUT_NAME, OUT_SIZE

END

When I run SELECT * FROM GET_DATA(1) optimizer picks PLAN (DIRS INDEX (UNQ1_DIRS)).

When I run SELECT * FROM GET_DATA_WRAPPER(1) optimizer picks PLAN (GET_DATA_WRAPPER NATURAL)

Does the optimizer picks PLAN (DIRS INDEX (UNQ1_DIRS)) inside the GET_DATA() when I call SELECT * FROM GET_DATA_WRAPPER(1)?

Thank you in advance,
Vlad

----- Original Message ----
From: Martijn Tonies <m.tonies@...>
To: firebird-support@yahoogroups.com
Sent: Wednesday, January 10, 2007 1:47:11 AM
Subject: Re: [firebird-support] Dynamic ORDER BY in SP













Max,

> I'd rather only have one SP that does everything I want it to do.

>

> In any case, IN_ORDER_BY is a varchar(32) variable and not the actual

column names/aliases.

>

> Is it not possible to:

>

> SELECT * FROM GET_DATA(IN_ USER_ID) ORDER BY IN_ORDER_BY

>

> ie order outside the SP?

Yes, this is possible, but will be much slower.



In you put the ORDER BY and/or WHERE clause inside the procedure,

it becomes part of the query plan the optimizer can create to get the data.



If you put it outside the procedure, Firebird has to first get all rows from

your procedure and then apply the ORDER BY and/or WHERE clauses,

because a procedure is a "black box" that spits out data rows. The

optimizer cannot adjust the plan to get the data if you do it this way.



Martijn Tonies

Database Workbench - development tool for Firebird and more!

Upscene Productions

http://www.upscene. com

My thoughts:

http://blog. upscene.com/ martijn/

Database development questions? Check the forum!

http://www.database developmentforum .com














<!--

#ygrp-mlmsg {font-size:13px;font-family:arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg table {font-size:inherit;font:100%;}
#ygrp-mlmsg select, input, textarea {font:99% arial,helvetica,clean,sans-serif;}
#ygrp-mlmsg pre, code {font:115% monospace;}
#ygrp-mlmsg * {line-height:1.22em;}
#ygrp-text{
font-family:Georgia;
}
#ygrp-text p{
margin:0 0 1em 0;
}
#ygrp-tpmsgs{
font-family:Arial;
clear:both;
}
#ygrp-vitnav{
padding-top:10px;
font-family:Verdana;
font-size:77%;
margin:0;
}
#ygrp-vitnav a{
padding:0 1px;
}
#ygrp-actbar{
clear:both;
margin:25px 0;
white-space:nowrap;
color:#666;
text-align:right;
}
#ygrp-actbar .left{
float:left;
white-space:nowrap;
}
.bld{font-weight:bold;}
#ygrp-grft{
font-family:Verdana;
font-size:77%;
padding:15px 0;
}
#ygrp-ft{
font-family:verdana;
font-size:77%;
border-top:1px solid #666;
padding:5px 0;
}
#ygrp-mlmsg #logo{
padding-bottom:10px;
}

#ygrp-vital{
background-color:#e0ecee;
margin-bottom:20px;
padding:2px 0 8px 8px;
}
#ygrp-vital #vithd{
font-size:77%;
font-family:Verdana;
font-weight:bold;
color:#333;
text-transform:uppercase;
}
#ygrp-vital ul{
padding:0;
margin:2px 0;
}
#ygrp-vital ul li{
list-style-type:none;
clear:both;
border:1px solid #e0ecee;
}
#ygrp-vital ul li .ct{
font-weight:bold;
color:#ff7900;
float:right;
width:2em;
text-align:right;
padding-right:.5em;
}
#ygrp-vital ul li .cat{
font-weight:bold;
}
#ygrp-vital a {
text-decoration:none;
}

#ygrp-vital a:hover{
text-decoration:underline;
}

#ygrp-sponsor #hd{
color:#999;
font-size:77%;
}
#ygrp-sponsor #ov{
padding:6px 13px;
background-color:#e0ecee;
margin-bottom:20px;
}
#ygrp-sponsor #ov ul{
padding:0 0 0 8px;
margin:0;
}
#ygrp-sponsor #ov li{
list-style-type:square;
padding:6px 0;
font-size:77%;
}
#ygrp-sponsor #ov li a{
text-decoration:none;
font-size:130%;
}
#ygrp-sponsor #nc {
background-color:#eee;
margin-bottom:20px;
padding:0 8px;
}
#ygrp-sponsor .ad{
padding:8px 0;
}
#ygrp-sponsor .ad #hd1{
font-family:Arial;
font-weight:bold;
color:#628c2a;
font-size:100%;
line-height:122%;
}
#ygrp-sponsor .ad a{
text-decoration:none;
}
#ygrp-sponsor .ad a:hover{
text-decoration:underline;
}
#ygrp-sponsor .ad p{
margin:0;
}
o {font-size:0;}
.MsoNormal {
margin:0 0 0 0;
}
#ygrp-text tt{
font-size:120%;
}
blockquote{margin:0 0 0 4px;}
.replbq {margin:4;}
-->







__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

[Non-text portions of this message have been removed]