Обсуждение: AW: Re: AW: case insensitive database ?

Thanks
No i can't use views dirctly, because in the
framwork ervery class maps to one table.
But i found that i can call stored precedures.
Maybe i can call the view in a stored procedure ?
does postgres allow this ?

here is a small sniplet of the castor doc:

The is also a special form of query that
gives a possibility to call stored procedures:

oql = db.getOQLQuery( "CALL sp_something($) AS myapp.Product" );

Here sp_something is a stored procedure returning one or
more ResultSets with the same sequence of fields as Castor-generated
SELECT for the OQL query "SELECT p FROM myapp.Product p"
(for objects without relations the sequence is: identity, then all
other fields in the same order as in mapping.xml).



-----Ursprüngliche Nachricht-----
Von: Joel Burton [mailto:jburton@scw.org]
Gesendet: Mittwoch, 25. April 2001 01:03
An: Thorsten Mauch
Cc: 'pgsql-novice@postgresql.org'
Betreff: [NOVICE] Re: AW: case insensitive database ?


On Wed, 25 Apr 2001, Thorsten Mauch wrote:

> Thanks for the answers.
> My prob is that i don't query the database
> directly. I use the CASTOR as a persitence-Framework.
> I create QOL-queries that are translated into
> SQL-Queries by the framework.
> So i can't use ILIKE or UPPER(expr) :(
> I also can't force the use of a special index.
> Howerver, i have to maintain a second field with upper
> letters, that i query instead of the orginal field

could you create a view and query that?

CREATE TABLE pers (fname);

INSERT INTO pers VALUES ('Joel');

CREATE VIEW pers_use_me AS SELECT fname, lower(fname) as lower_fname
FROM pers;

SELECT * FROM pers WHERE lower_fname = 'joel';


HTH,
--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Hello,

I have a great problem with performances of my database, on the SQL request:
---------------
select t_test.doc_id,  t_test.f_num_centre, t_test.f_num_candidat from
t_test, doc where t_test.doc_id=doc.id and doc.docsubset_id=2000077392
---------------
The doc table has 36.000 rows, and t_test the same. There is an index on doc
(docsubset_id) and an index on t_test (doc_id). There is others index too.

When I send this request, I must wait for about 45 sec. before the results
appears.
The server is a BI-PII 266, and there is about no activity on the database
neither on the server.

When I was using this request under MS-SQLServer (v6), the wait time was
about nothing.

What's wrong ?

Thank's a lot
Patrice Espie

On Wed, 25 Apr 2001, Patrice Espié wrote:

> Hello,
>
> I have a great problem with performances of my database, on the SQL request:
> ---------------
> select t_test.doc_id,  t_test.f_num_centre, t_test.f_num_candidat from
> t_test, doc where t_test.doc_id=doc.id and doc.docsubset_id=2000077392
> ---------------
> The doc table has 36.000 rows, and t_test the same. There is an index on doc
> (docsubset_id) and an index on t_test (doc_id). There is others index too.
>
> When I send this request, I must wait for about 45 sec. before the results
> appears.
> The server is a BI-PII 266, and there is about no activity on the database
> neither on the server.
>
> When I was using this request under MS-SQLServer (v6), the wait time was
> about nothing.
>
> What's wrong ?

A few questions:
  1) Which version of postgres are you using?
  2) Have you 'VACUUM ANALYZE t_test' and 'VACUUM ANALYZE doc' yet?

Try #2, it should help you quite a bit.

-- Dave