fbpx
Welcome, Guest
Username: Password: Remember me
Welcome to the XSharp forum!

Tell us and our members who you are, what you like and why you became a member of this site.
We welcome all new members and hope to see you around a lot!
  • Page:
  • 1

TOPIC: VFP TEST. Sqlexec big querys

VFP TEST. Sqlexec big querys 29 Jun 2020 18:23 #15075

  • jpmoschi's Avatar

  • jpmoschi

  • Topic Author


  • Posts: 9
  • 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

    Please Log in or Create an account to join the conversation.

    VFP TEST. Sqlexec big querys 30 Jun 2020 09:04 #15076

  • robert's Avatar

  • robert


  • Posts: 1746
  • 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
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    VFP TEST. Sqlexec big querys 30 Jun 2020 14:55 #15077

  • jpmoschi's Avatar

  • jpmoschi

  • Topic Author


  • Posts: 9
  • 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

    Please Log in or Create an account to join the conversation.

    VFP TEST. Sqlexec big querys 30 Jun 2020 15:39 #15078

  • robert's Avatar

  • robert


  • Posts: 1746
  • 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
    XSharp Development Team
    The Netherlands

    Please Log in or Create an account to join the conversation.

    • Page:
    • 1