Subject Re[3]: [IBO] View, filter or ... ?
Author Cristian Ivan
Hello Helen,

Sunday, March 04, 2001, 4:15:21 PM, you wrote:

HB> At 12:24 PM 04-03-01 +0200, you wrote:
>>That's because right here
>>I'm programatically building the query (like q.sql.text='select * from
>>table where (somecondition)') and instead of using parambyname I'd
>>rather use something like q.sql.text=format('select * from table where
>>(someconditions) and (month=%d) and (year=%d)',[month,year]).

HB> I think you should definitely avoid it. Have you ever known a monster like this to **work** ?
Oh well... it's something wrong with me if I've got a positive answer
to your question ? :-) So generally speaking ParamByName should be the
way to pass parameters to the query and not with some
"q.sql.text=format(... " ?

>>This is exactly what I was trying to avoid but it seems there's no easy way
>>around it... This way, I only need to pay attention that all queries
>>have these "where" clauses :)

HB> Assuming you know the table you are querying, you can make the query something like
HB> select Field1, Field2, Field3 from TheTable (or a joined set)
HB> Then, at runtime, pick up the conditions and make them into SQLWhereItems, which you define in the OnPrepareSQL event.
Yes, this is the approach I'm using right now. Seems to be best fitted
for me.

>>The problem was that creating a view as "select ... where month=3 and
>>year=2001" whould force all users to use only that month,year or
>>dropping the view and creating another one (which can't be done if the
>>view is already in use by somebody else, of course).

HB> Oh my!! that is NOT what views are for! and you *certainly donot* drop and create objects in client/server. Forget viewaltogether, except for a static, perhaps denormalized view of some
data HB> that a lot of people use frequently.

Now I understand why I wasn't feeling very confortable with
dropping/creating views :) I got the idea.

HB> Oh, and disavow yourself of the belief that if one user is "using" a view, others can't. In client/server, users never physically touch the database; and InterBase's versioning engine takes
HB> care of who is looking at what.
I was refering to the fact that I could not "drop" a view if another
client was using it. It's obsolete now, because I got the idea of
avoiding views :)

One more question (and I definately don't promise it will be the last
one :) ):
can I find on the Internet a document with some performance tips for Interbase ?

I mean for example that I've created some month ago a program for
media monitoring (radio,tv,newspapers) and stuff. Everything is going
fine, but the database is like 60Mb now, filled especially with BLOB
fields containing RTF files (the articles, in fact; RTF because I
needed alignments, colours etc). Getting a report out of that database
was under 1 min in the first weeks...not it's like 10 minutes and more
each day. What can I do to improve performance (i'm testing now
various indexm but without much improvements) ? What BLOB type should
I choose for RTF streams ? (right now the client wants to move into
multimedia world and make the database with AUDIO and eventually VIDEO
fields...which whould make the whole thing even slower :( ).

I hope I'm not abusing your patience but I've noticed your quick reaction and
I'm taking advantage of it :))

Best regards,
Cristian mailto:cris@...