Yusuf Zorlu

<<
select * from mytable1, mytable2
Be sure that mytable1 and mytable2 has >1000 rows in it.
>>

If I understand it right, this code will produce a million (or more) lines (records) just multiplying records from mytable1 and mytable2 because 1000 * 1000 = 1M

This SQL statement will produce a canned table and *ALL* records from mytable1 and *ALL* records from mytable2 will be related one by one because there is no WHERE clause and it can take a while (minutes or even hours) to produce the result.

This is just for test or it is a production code ?

I am a DBISAM user a while (since 1998) and ElevateDB for "just" two years. I have some very big applications that handle more than a hundred users and for now 50Gb of data. If you understand the idea behind Delphi, DBISAM, ElevateDB and build an appropriate code this will run very fast but if the technique used was not so good then your application will suffer to run.

Tim has helped a lot in many situations giving me hints and pointing me to a right direction. But like he said and I as a developer knows that if you can reproduce the problem this is easier to fix or at least understand the problem but if you don´t it will become very difficult specially if this is a random situation.

Regards
Eduardo

Jose Eduardo Helminsky wrote:

<< If I understand it right, this code will produce a million (or more) lines (records) just multiplying records from mytable1 and mytable2 because 1000 * 1000 = 1M

<< This SQL statement will produce a canned table and *ALL* records from mytable1 and *ALL* records from mytable2 will be related one by one because there is no WHERE clause and it can take a while (minutes or even hours) to produce the result.

<< This is just for test or it is a production code ?


Hi Jose, yes this is a TEST only to see what happens when the server has to do a lot and at the same time some other sessions want data! We're migrating our app not for more than 6 months to ElevateDB and YES, if WE do correct SQL it is *very very* fast compared to product before.

But do I think false that the server should be still working normal for other sessions while it creates that million rows for this TEST-Query with a own session?

Can you check this on your side if the server is blocked while it creates the data? If it works on your side without blocking other sessions we must have the bug in our server's extension (created by us) ...

Yusuf Zorlu
MicrotronX

I have just tested this here with two existing tables which produce a recordset of 7'500'000 records. While this query is run (it takes about 35 sec.) i was still able to query this involved tables and others with no problems. I did also run the same query on two different sessions at the same time and they worked perfectly together. Bot queries toke about 35 sec. at the same time.

What is different here is, that I don't use buffered mode, as this looks to dangeours fo me. If the server get turned off by power outage or a crash (freeze) this may result in your seen errors, as EDB was not able to flush the buffer at the moment, where the server was incorerctly shutdown. (I believe, as it is documented this way)

So the first what I would do in your case, is to turn off the buffering mode and use the normal mode and see if this makes a difference.

Some additional Info:

I have done the test a little bit wrong. So I ran one query in local mode and the other in C/S mode. This resulted in the same run time running at the same time.

Now I have retested with 2 and 3 connection to the Server. Now I get this run times:
1 Session: 35 seconds
2 Sessions running the query at the same time: 45 seconds
3 Sessions running at the same time: 60 seconds.

What is suprising me is that I can't see a noticable time difference if I run one query in C/S Mode and the second in local mode on the same data. In this case, both queries return the resultset in about 35 seconds. If I run this two queries in C/S Mode, it increases the run time from 35 to 45 seconds, but I think this is normal.

If  I run the two queries at the same time in Local Mode, the run time stays by 35 seconds.

BUT: I did never see, what you see on your side. Other queries run all fine while this queries are running.

PS. Everything was tested with the EDB Manager.

Rolf


Must say I'm impressed - are you running on SSD or HDD cos if its HDD I'm really iimpressed with 7.5 million records in 35 seconds

With the local part of your test was it LAN or really local?

Roy Lambert

Yusuf


When you run this query what happens to the CPU usage?

Roy Lambert

Roy Lambert wrote:

<< When you run this query what happens to the CPU usage?

Hi Roy, surprisingly on my side (we have buffered io ENABLED) i'm not able to query while the first query is running the configuration.serversessions ... but yes, you are correct a query to same table runs in the same time, so i think only some tables (internal tables in configuration) are blocked ??

The server cpu is going up to 25% on a xeon cpu e3-1270v3
and a click to "manage server sessions" in a new edbmanager shows this after round about 30 seconds hang:

ElevateDB Error #506 Cannot lock the session manager (ID: 3)

Yusuf Zorlu
MicrotronX

Yusuf


>Hi Roy, surprisingly on my side (we have buffered io ENABLED) i'm not able to query while the first query is running the configuration.serversessions ... but yes, you are correct a query to same table runs in the same time, so i think only some tables (internal tables in configuration) are blocked ??
>
>The server cpu is going up to 25% on a xeon cpu e3-1270v3

If the CPU is only hitting 25% that suggests there's plenty of spare capacity even running your million row result set query. That in turn suggests that its something in your server modifications. Have you tried the same experiment with a standard server?

Roy

Roy Lambert wrote:

<< If the CPU is only hitting 25% that suggests there's plenty of spare capacity even running your million row result set query. That in turn suggests that its something in your server modifications. Have you tried the same experiment with a standard server?

I have started the demo-server from Rio-Installation: \servers\edbsrvr\win64
1. created two tables and inserted a lot of data
2. run select * from table1, table2
3. open a new edbmanager
4. connect to session and click to "manage server sessions"
5. ElevateDB Error #506 Cannot lock the session manager (ID: 3)

I have used default configuration of server with buffering DISABLED and default configuration.

Yusuf Zorlu
MicrotronX

Roy Lambert wrote:

Must say I'm impressed - are you running on SSD or HDD cos if its HDD I'm really iimpressed with 7.5 million records in 35 seconds

With the local part of your test was it LAN or really local?

Roy Lambert
<<<


Its running on a HDD. I had my tests done on my PC so edbsrv (C/S) and Local mode running on the same system. No LAN involved in my tests.