You are not logged in.
- Topics: Active | Unanswered
#1 2024-05-12 11:45:50
How do I speed up initial access with external db to MS SQL
I used example projects from "ex\ThirdPartyDemos\martin-doyle\05-HttpDaemonORM" as the base and connect it to MS SQL by using TSqlDBOleDBMSSQL2018ConnectionProperties like below
Props := TSqlDBOleDBMSSQL2018ConnectionProperties.Create('MyAnotherPC','Training','sa','123');
SQLite3Log.Enter(self);
Model := CreateSampleModel;
VirtualTableExternalRegister(Model,[TOrmSample],Props);
SampleServer := TSampleServer.Create(Model, ':memory:'); The server and client were running on my laptop and the SQL server was running on another PC, these two machines were connected to the same Wifi network which I believe is 300M network.
Below server log shows it took 1s+ for reading the last ID and 3s+ for insert a record, is this normal (I have tried many times and it looks to me that it always like that after server is start up) or there is something I can tune up it to speed up?
20240512 11100250 " DB mormot.db.sql.oledb.TSqlDBOleDBStatement(027c6f70) Prepare t=1.08s q=select max(ID) from dbo.Sample
20240512 11100250 " SQL mormot.db.sql.oledb.TSqlDBOleDBStatement(027c6f70) Execute t=1.09s q=select max(ID) from dbo.Sample
20240512 11100251 " DB mormot.db.sql.oledb.TSqlDBOleDBStatement(027c7090) Prepare t=38us q=insert into dbo.Sample (ID,Name,Question,Time) values (?,?,?,?)
20240512 11100251 " SQL mormot.db.sql.oledb.TSqlDBOleDBStatement(027c7090) Execute t=11.49ms wr=1 q=insert into dbo.Sample (ID,Name,Question,Time) values (3,'','',135846605441)
20240512 11100252 " srvr Write POST root/Sample=201 out=0 B in 1.12s
20240512 11100252 " - 01.128.548
20240512 11122835 # + server.TSampleServer(02ac9b20).URI POST root/Sample in=54 B
20240512 11122835 # + mormot.db.sql.oledb.TSqlDBOleDBConnection(0283bb70).Create
20240512 11122835 # - 00.002.040
20240512 11122835 # + mormot.db.sql.oledb.TSqlDBOleDBConnection(0283bb70).Connect
20240512 11123143 # - 03.122.602
20240512 11123143 # DB mormot.db.sql.oledb.TSqlDBOleDBStatement(027c71b0) Prepare t=3.12s q=insert into dbo.Sample (ID,Name,Question,Time) values (?,?,?,?)
20240512 11123144 # SQL mormot.db.sql.oledb.TSqlDBOleDBStatement(027c71b0) Execute t=3.13s wr=1 q=insert into dbo.Sample (ID,Name,Question,Time) values (4,'','',135846605596)
20240512 11123144 # srvr Write POST root/Sample=201 out=0 B in 3.15s
20240512 11123144 # - 03.153.490but some time following reading could be much faster:
20240512 11202701 % DB mormot.db.sql.oledb.TSqlDBOleDBStatement(027c73f0) Prepare t=1.14s q=select top(1) ID,Name,Question,Time from dbo.Sample where Name=?
20240512 11202702 % SQL mormot.db.sql.oledb.TSqlDBOleDBStatement(027c73f0) Execute t=1.14s q=select top(1) ID,Name,Question,Time from dbo.Sample where Name=''
20240512 11202702 % srvr Read GET root=200 out=62 B in 1.17s
20240512 11202703 % ret mormot.rest.server.TRestServerRoutingRest(02a478f0) [{"ID":5,"Name":"m2","Question":"tst 1","Time":135846606086}]
20240512 11202703 % - 01.179.860
20240512 11203207 " + server.TSampleServer(02ac9b20).URI GET root in=68 B
20240512 11203207 " DB mormot.db.sql.oledb.TSqlDBOleDBStatement(027c7510) Prepare t=119us q=select top(1) ID,Name,Question,Time from dbo.Sample where Name=?
20240512 11203208 " SQL mormot.db.sql.oledb.TSqlDBOleDBStatement(027c7510) Execute t=8.94ms q=select top(1) ID,Name,Question,Time from dbo.Sample where Name=''
20240512 11203208 " srvr Read GET root=200 out=62 B in 21.97ms
20240512 11203209 " ret mormot.rest.server.TRestServerRoutingRest(02a478f0) [{"ID":5,"Name":"m2","Question":"tst 1","Time":135846606086}]
20240512 11203210 " - 00.056.932
20240512 11204361 # + server.TSampleServer(02ac9b20).URI POST root/Sample in=52 B
20240512 11204361 # SQL mormot.db.sql.oledb.TSqlDBOleDBStatement(027c71b0) Execute t=8.03ms wr=1 q=insert into dbo.Sample (ID,Name,Question,Time) values (6,'','',135846606123)
20240512 11204361 # srvr Write POST root/Sample=201 out=0 B in 20.70ms
20240512 11204361 # - 00.021.199Offline
#3 2024-05-13 12:55:56
Re: How do I speed up initial access with external db to MS SQL
tried with TADOConnection with 'Provider=SQLOLEDB;Data Source=...', it had the similar speed number, i.e., some time it went up to above 3s, but haven't seen as this slow in SQL studio which also running from my laptop and connect to same db on another PC. Will try ODBC next time.
Offline
#5 2024-05-13 15:01:28
- ttomas
- Member
- Registered: 2013-03-08
- Posts: 135
Re: How do I speed up initial access with external db to MS SQL
In your first log you have >3sec on connection, not on exec statement. First statement in thread must make connection 3sec,after that execution of statements is fast in same thread. Connection pool normal behavior
Offline
#6 2024-05-13 15:06:31
Re: How do I speed up initial access with external db to MS SQL
@ttomas
Since each web server thread has its own connection IIRC, it may make sense that each initial connection is slow, then the work is faster.
But anyway, 3 seconds to establish a connection is really slow.
@Bo did you try to specify a port number with the server ip to the connection string?
https://stackoverflow.com/a/24752620/458259
Offline
#9 2024-05-21 12:39:55
Re: How do I speed up initial access with external db to MS SQL
Looks like the problem is solved by using ip address instead of machine name, i.e.,
Props := TSqlDBOleDBMSSQL2018ConnectionProperties.Create('192.168.0.101','Training','sa','123');Offline