Good morning Forum ,
I continue evaluating xsharp to migrate from visual foxpro.
Detect problems by running huge queries like
"select top 1000000 from dbo.bigtable"
It does not produce errors but it never ends eithe
I guess it is not a timeout problem xq the query run with System.Data.SqlCommand takes only 2 minutes
Any suggestion?
Thanks
JP
VFP TEST. Sqlexec big querys
VFP TEST. Sqlexec big querys
JP,
1 million rows ? And what about the size of each row ?
Even when executing of the command "only" takes 2 minutes, after that we will have to use the returned DataReader to read the rows into the local cursor. This might also take considerable time and memory.
Did you try this with FoxPro too?
Which makes me wonder why you are opening cursors like this.
When dealing with quantities of data like that, which are usually needed for reporting, I usually write code that runs on the server to prepare a report and only return the data that is really needed for the report.
Robert
1 million rows ? And what about the size of each row ?
Even when executing of the command "only" takes 2 minutes, after that we will have to use the returned DataReader to read the rows into the local cursor. This might also take considerable time and memory.
Did you try this with FoxPro too?
Which makes me wonder why you are opening cursors like this.
When dealing with quantities of data like that, which are usually needed for reporting, I usually write code that runs on the server to prepare a report and only return the data that is really needed for the report.
Robert
XSharp Development Team
The Netherlands
robert@xsharp.eu
The Netherlands
robert@xsharp.eu
VFP TEST. Sqlexec big querys
Hi Robert, a vfp cursor can be in memory or on disk depending on size. It is not decided by the programmer.
After executing that query you can see the file in a configurable location.
? sqlexec (1, "select ...", "SQLBIGRESULT")
? dbf ()
This returns C: USERS JUANPC APPDATA LOCAL TEMP 0000H6H50038.TMP. the size of the file is 600 MB in 40 seconds.
Precisely my intention is to know the behavior of xsharp to manipulate those situations that believe me are real and even more in contexts of multi-user execution (all users connected via rdp with the application server)
I wrote in the forum because I waited for hours thinking it was taking time but I never finished executin
jp
After executing that query you can see the file in a configurable location.
? sqlexec (1, "select ...", "SQLBIGRESULT")
? dbf ()
This returns C: USERS JUANPC APPDATA LOCAL TEMP 0000H6H50038.TMP. the size of the file is 600 MB in 40 seconds.
Precisely my intention is to know the behavior of xsharp to manipulate those situations that believe me are real and even more in contexts of multi-user execution (all users connected via rdp with the application server)
I wrote in the forum because I waited for hours thinking it was taking time but I never finished executin
jp
VFP TEST. Sqlexec big querys
JP,
I did a test on one of my own SQL Server databases.
I selected 1.8 million rows in ~ 34 seconds. I did a Copy to a DBF and this results in a 130 Mb DBF file (each record has ~ 70 bytes).
The copy to took ~20 seconds.
Of course these numbers depend on the network (in my case the SQL Server was on the same machine, so no network delays) and disk speed (my temp folder is on a SSD and I copied the DBF to a real HD).
At this moment we are keeping most of the SQL Cursors in memory for performance reasons.
We could probably also write them to disk when needed but so far there was no need to do this.
Robert
I did a test on one of my own SQL Server databases.
I selected 1.8 million rows in ~ 34 seconds. I did a Copy to a DBF and this results in a 130 Mb DBF file (each record has ~ 70 bytes).
The copy to took ~20 seconds.
Of course these numbers depend on the network (in my case the SQL Server was on the same machine, so no network delays) and disk speed (my temp folder is on a SSD and I copied the DBF to a real HD).
At this moment we are keeping most of the SQL Cursors in memory for performance reasons.
We could probably also write them to disk when needed but so far there was no need to do this.
Robert
XSharp Development Team
The Netherlands
robert@xsharp.eu
The Netherlands
robert@xsharp.eu