SQL and client server

[Victorio]

Hi,

I have application, this open also SQL database with cca 2GB size.
For application I need some selected records which I create by SQL Express++ dataset method.
(data need only for read only access, no change, no write to database)
But this need about 20 seconds to creating.
Connecting to database (is in SQL 2008 or SQL 2000 server) is ok, about 1-2 seconds.
More time need Dataset().
Is here some way to prepare selected records before run my app and work only with less records ?
My app have only one module, all in one, no some server module and client module.

If I creating other dataset after one is created , time is only 1-2 seconds. Only first creating need 20 seconds.

I looked, this is HDD access problem, because better way will be prepare data first on server, and user will open only prepared filtered data. I do not know, how make this. ?

Any tips ?

[rdonnay]

What happens if you run the program, quit it and then run it again?

Does it still take 20 seconds on the 2nd iteration.

[Victorio]

If I run program, wait for create dataset about 20 seconds. When quit and run again, always wait for dataset 20 seconds.
But if I run program once, create dataset but without quit it and run same program again, creation dataset is for only 1-2 seconds.
It looks, as ODBC have prepared data in memory ? and another program can access data faster.

[c-tec]

Hello,
you can use MYLIBSQL for access without ODBC. There is a class from Hector in the OT4XB forum. I have tried it and works fine. But has not the power like SQLEXPRESS. If you then have still problems I would try to access with MYSQLWORKBENCH, if it takes also a longer time check the server connection.
regards
Rudolf

[Victorio]

Hi,
Actually I am testing at "normal" LAN environment with W2003 server and SQL 2000 server and my client PC with application.
Here is dataset creation time about 1-3 seconds.
I made some optimalisation code, disable duplicate GoTop , put readonly parameter to Dataset() and other.
On real server it is better than on local machine, where is everything (application, server...).

I will see, how it will work home on local PC after changes and write info.

Thanks.

[Victorio]

I think found problem. My PC has 4GB ram, some of this is reserved , cached,, some use SQL server.
Remains only 1.7GB, When I open database with 2GB size, system cannot have in memory, but write to hdd.
Confirmed me only when I expand memory in my PC , but now I have any module

Aj work, where I have W2003 server was speed disk, then it is not so visible however it has als only 4GB

[Auge_Ohr]

I think found problem. My PC has 4GB ram

RAM is always good for Windows but it is not the Problem why 1st Time it is slow.

as Boris ask : have you build "real" SQL Index ?
every Query will use Index ... if there is no Index it will build temporary and 2nd Time is much faster.

[Victorio]

Jimmy, thanks for tips,
I saw indexes in database /tables, but because this database is not my, and I have not autorisation for changes in this database, I can not create indexes.
Or maybe can create only temporary in memory, now I do not know.

I must look, analyse , if some from exist indexes can aplicable for me.

But in database are also created views, which I can use, this can be easily and faster.