I think if you have 1000+ columns you've got a design problem regardless of
Firebird's performance, which (correct me if I'm wrong anyone) would degrade
with that many columns. Perhaps someone else can comment to that and what
the limit might be, I don't know personally.

A stored procedure would definitely help w/ performance.

Do you honestly need all 1000+ columns of the query you're after? Can't it
be broken down into more digestible queries? It sounds like you should
maybe re-consider some of your design decisions.

Also, sometimes it's better to query more than once to get values+sub-values
than it is to make a single monster of a joined/union query.

I'm speaking from the perspective of web applications but this logic should
apply anywhere.

-v

_____

From: Garrett Smith [mailto:garrett@...]
Sent: Friday, December 17, 2004 12:32 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Temporary tables


In most cases, yes, we can limit the results before the union. As for
paging, I think this has to occur after the union, as it will depend on
sort order.

Is a view with 1000+ columns utter stupidity? Or is it something
Firebird can handle without a terrible memory/performance hit?

At the end of the day, I suppose I could create a new view, run the
query, then drop it. This seems painful though.

OTOH, perhaps a store procedure is what I'm looking for. I could loop
through all the tables involved, performing the appropriate subquery,
adding to a result set, and then sort the result. The set returned by
the procedure contains a single ID column.

Storage Box wrote:

> Is there any way your usage of the query could measure your results,
> as in "paging" or selecting a range of values by date, getting the
> top 100 records, etc.
>
> Depending on the size of the table(s), a UNION, JOIN, or any other
> type of relationship can make for a large number of records, killing
> performance.
>
> _____
>
> From: Garrett Smith [mailto:garrett@...]
> Sent: Friday, December 17, 2004 12:13 PM
> To: firebird-support@yahoogroups.com
> Subject: RE: [firebird-support] Temporary tables
>
>
> I actually want three columns after the combination (I'm experimenting
> with unions now):
>
> name | foo_name | bar_name
>
> where 'name' is your 'foobar.name'. For foo records, foo_name
> contains a corresponding value, otherwise NULL. Same pattern for
> bar_name. There are no common IDs between tables.
>
> Looks like I could create a view that's a huge union, covering all
> permutations, and thus avoid a temporary table. But I'm a tad nervous
> about a) the size of the union SQL and b) the number of columns in the
> view.
>
> -- Garrett
>
> Storage Box wrote:
>> Sounds like you need a UNION - or - are you trying to treat both name
>> columns as a single column with a single unified name?
>>
>> before "temp" table:
>>
>> foo.name | bar.name
>>
>> after combination:
>>
>> foobar.name
>>
>> _____
>>
>> From: Garrett Smith [mailto:garrett@...]
>> Sent: Friday, December 17, 2004 11:46 AM
>> To: firebird-support@yahoogroups.com
>> Subject: RE: [firebird-support] Temporary tables
>>
>>
>> Ah, sorry, correction...the problem with joins (and I think this is
>> also a problem with the view solution) is that I want to select
>> something like:
>>
>> select * from ... where name = 'Blah'
>>
>> Note there's no table qualifier. I.e. I want the thing I'm querying
>> to have a name column that is a merging of foo.name and bar.name.
>> Thus the temporary table.
>>
>>
>>
>> -- Garrett
>>
>> Garrett Smith wrote:
>>> This could be the best approach -- I was just trying to keep the SQL
>>> simple. But I'm probably past that point :)
>>>
>>> -- Garrett
>>>
>>> Adomas Urbanavicius wrote:
>>>> Why not
>>>> View or query
>>>>
>>>> select * from foo
>>>> left join bar on
>>>> foo.id = bar.id
>>>> where foo.name = 'Baz'
>>>> or foo.status = 1
>>>> /*
>>>> or any other :
>>>> bar.status = 1
>>>> bar.name = 'Blah'
>>>> */
>>>>
>>>>
>>>>
>>>>>
>>>>> I have several tables that I want to query as if their rows were
>>>>> in a single table. My thinking was to use a temporary table and
>>>>> execute multiple 'select into' queries to populate it and then
>>>>> run a final select against it.
>>>>>
>>>>> E.g., given two tables:
>>>>>
>>>>> foo ( id integer, name varchar)
>>>>> bar ( id integer, name varchar, status integer)
>>>>>
>>>>> I want a structure looks like:
>>>>>
>>>>> some_structure (
>>>>> name varchar,
>>>>> status integer,
>>>>> foo_name varchar,
>>>>> bar_name varchar,
>>>>> bar_status integer)
>>>>>
>>>>> so I can run queries like:
>>>>>
>>>>> select id from some_structure where name = 'Baz' or status = 1;
>>>>> select id from some_structure where foo_name = 'Baz' or
>>>>> bar_status = 1;
>>>>>
>>>>> Rows from table foo would NULL values for status. The underlying
>>>>> applications lets users select objects (represented by id) by
>>>>> specifying property name values. We validate input to ensure that
>>>>> an unqualified properties (e.g. 'name', as opposed to 'bar_name')
>>>>> have compatible types.
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> ------------------------ Yahoo! Groups Sponsor
>>>> --------------------~--> $4.98 domain names from Yahoo!. Register
>>>> anything. http://us.click.yahoo.com/Q7_YsB/neXJAA/yQLSAA/67folB/TM
>>>>
>>>
>>
>

--------------------------------------------------------------------~->

>>>>
>>>>
>>>> Yahoo! Groups Links
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>> ------------------------ Yahoo! Groups Sponsor
>>> --------------------~--> $4.98 domain names from Yahoo!. Register
>>> anything. http://us.click.yahoo.com/Q7_YsB/neXJAA/yQLSAA/67folB/TM
>>>
>>
>

--------------------------------------------------------------------~->

>>>
>>>
>>> Yahoo! Groups Links
>>>
>>>
>>>
>>
>>
>>
>> Yahoo! Groups Sponsor
>>
>> ADVERTISEMENT
>>
>>
>

<http://us.ard.yahoo.com/SIG=129j18rva/M=298184.5639630.6699735.3001176/

> D=gr
>>
>

oups/S=1705115386:HM/EXP=1103395007/A=2495208/R=0/SIG=11egg01lg/*http://

> www.
>> netflix.com/Default?mqso=60188914> click here
>>
>>
>

<http://us.adserver.yahoo.com/l?M=298184.5639630.6699735.3001176/D=group

> s/S=
>>> HM/A=2495208/rand=815381014>
>>
>> _____
>>
>> Yahoo! Groups Links
>>
>>
>> * To visit your group on the web, go to:
>> http://groups.yahoo.com/group/firebird-support/
>>
>>
>> * To unsubscribe from this group, send an email to:
>> firebird-support-unsubscribe@yahoogroups.com
>>
>

<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe

>>
>>
>>
>> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of
>> Service <http://docs.yahoo.com/info/terms/> .
>>
>>
>>
>>
>> [Non-text portions of this message have been removed]
>>
>>
>>
>> ------------------------ Yahoo! Groups Sponsor
>> --------------------~--> Make a clean sweep of pop-up ads. Yahoo!
>> Companion Toolbar. Now with Pop-Up Blocker. Get it for free!
>> http://us.click.yahoo.com/L5YrjA/eSIIAA/yQLSAA/67folB/TM
>>
>

--------------------------------------------------------------------~->

>>
>>
>> Yahoo! Groups Links
>>
>>
>>
>
>
>
> Yahoo! Groups Sponsor
>
> ADVERTISEMENT
>
>

<http://us.ard.yahoo.com/SIG=129p5ej0e/M=298184.5639630.6699735.3001176/
D=gr

>

oups/S=1705115386:HM/EXP=1103396625/A=2495202/R=0/SIG=11evjk50a/*http://
www.

> netflix.com/Default?mqso=60188913> click here
>
>

<http://us.adserver.yahoo.com/l?M=298184.5639630.6699735.3001176/D=group
s/S=

>> HM/A=2495202/rand=469013377>
>
> _____
>
> Yahoo! Groups Links
>
>
> * To visit your group on the web, go to:
> http://groups.yahoo.com/group/firebird-support/
>
>
> * To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>

<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe

>
>
>
> * Your use of Yahoo! Groups is subject to the Yahoo! Terms of

Service

> <http://docs.yahoo.com/info/terms/> .
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~--> Make a clean sweep of pop-up ads. Yahoo!
> Companion Toolbar. Now with Pop-Up Blocker. Get it for free!
> http://us.click.yahoo.com/L5YrjA/eSIIAA/yQLSAA/67folB/TM
>

--------------------------------------------------------------------~->

>
>
> Yahoo! Groups Links
>
>
>

Yahoo! Groups Sponsor

ADVERTISEMENT

<http://us.ard.yahoo.com/SIG=1298eg03m/M=294855.5468653.6549235.3001176/D=gr
oups/S=1705115386:HM/EXP=1103397773/A=2455396/R=0/SIG=119u9qmi7/*http://smal
lbusiness.yahoo.com/domains/> click here

<http://us.adserver.yahoo.com/l?M=294855.5468653.6549235.3001176/D=groups/S=
:HM/A=2455396/rand=353799052>

_____

Yahoo! Groups Links


* To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/


* To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com
<mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
<http://docs.yahoo.com/info/terms/> .




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