>
> I have tried through iSQL and it is doing the same.

OK, so we have eliminated client connection component caching (eg IBO).

>
>
> >* Make sure your database does not have a gdb file extension.
>
>
>
> I had gdb extension! I changed it now.

Windows thinks that gdb is important for system restore, so it will
take a snapshot during the first connection. You are not clear whether
you have run your tests after making the change, so if not, test again.

>
>
> >* Perform a backup-restore cycle on your database. It is possible the
> >15 second of the initial connection is actually the garbage collection
> >as it is discovered.
>
>
>
> Similar delays are occurred on other databases with fewer records on

their

> table (2000-3000 recs).
>
>
>
> Is there need for any kind of database repair? Like this repair you said
> (backup and restore), is there any other repair procedure that is

required

> on large databases?

Well backup and restore will cure the lot. You can set the statistics
on all the indices to make sure the best plan is used, but in your
case I doubt the plan is the problem.

>
>
>
> From which point do you think I have to search bigger sql server

like Oracle

> for faster performance?
>
>

... after a lot of TLC, you probably will get that performance yes.
Believe me, 200000 records is not a large database.

Lets just look first at your problem.

There is just too big a gap between 15 seconds and 0.1 seconds for the
same query on the same data.

A similar query on a similar sized table on my laptop is under 2
seconds on initial run. On an adequately resourced server it would be
well subsecond.

My theory is that your initial run is collecting garbage. Subsequent
runs do not encounter this garbage. A backup-restore will fix that.
This can happen if you do something like

delete from mytable;

before your test. As a test, try something like:

delete from mytable;
commit;
select count(*) from mytable;

and see how long it takes for it to discover there are 0 records. This
is garbage collection at work, which in turn is the downside of MGA.
The upside is that you don't have to worry about locks causing a huge
bottleneck.

Adam