Hi All
Is there any tricks I am missing which would speed up a dump of a VFP table into a SQL table.
I have predifined by SQL statement something like this
"insert into mytable (myfield) values (?m.myfield)"
I am then scanning through the VFP table performing a scatter memvar and then executing the SQL.
It works it just seems to take alot longer than I imagined it would.
Thanks IA
Chris.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
Hi Chris,
Well, it does have to iterate through each record. If no memo fields, I guess you could construct multi-line inserts using TEXTMERGE but that's probably no better for speed. Is this going across a network? How wide are the tables? Can you post the code so we can see if there's any tweaks that can be done to process it as fast as possible?
On 11/28/2019 11:55 AM, Chris Davis wrote:
Hi All
Is there any tricks I am missing which would speed up a dump of a VFP table into a SQL table.
I have predifined by SQL statement something like this
"insert into mytable (myfield) values (?m.myfield)"
I am then scanning through the VFP table performing a scatter memvar and then executing the SQL.
It works it just seems to take alot longer than I imagined it would.
Thanks IA
Chris.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
You could dump it to excel or CSV and then do an import in SQL
On 28 November 2019 12:55:01 GMT-04:00, Chris Davis chrisd@actongate.co.uk wrote:
Hi All
Is there any tricks I am missing which would speed up a dump of a VFP table into a SQL table.
I have predifined by SQL statement something like this
"insert into mytable (myfield) values (?m.myfield)"
I am then scanning through the VFP table performing a scatter memvar and then executing the SQL.
It works it just seems to take alot longer than I imagined it would.
Thanks IA
Chris.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
Post Messages to: ProFox@leafe.com Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: https://leafe.com/archives This message: https://leafe.com/archives/byMID/LOYP123MB31676F6AD36F7FAE59E027738F470@LOYP... ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Report [OT] Abuse: http://leafe.com/reportAbuse/LOYP123MB31676F6AD36F7FAE59E027738F470@LOYP123M...
Great idea, Frank...and easier too!!
On 11/28/2019 12:57 PM, Frank Cazabon wrote:
You could dump it to excel or CSV and then do an import in SQL
On 28 November 2019 12:55:01 GMT-04:00, Chris Davis chrisd@actongate.co.uk wrote:
Hi All
Is there any tricks I am missing which would speed up a dump of a VFP table into a SQL table.
I have predifined by SQL statement something like this
"insert into mytable (myfield) values (?m.myfield)"
I am then scanning through the VFP table performing a scatter memvar and then executing the SQL.
It works it just seems to take alot longer than I imagined it would.
Thanks IA
Chris.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
Post Messages to: ProFox@leafe.com Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: https://leafe.com/archives This message: https://leafe.com/archives/byMID/LOYP123MB31676F6AD36F7FAE59E027738F470@LOYP... ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
Report [OT] Abuse: http://leafe.com/reportAbuse/LOYP123MB31676F6AD36F7FAE59E027738F470@LOYP123M...
Or maybe just use the Upsizing wizard from vfpx.org ?
wOOdy
-----Ursprüngliche Nachricht----- Von: ProFox profox-bounces@leafe.com Im Auftrag von Chris Davis Gesendet: Donnerstag, 28. November 2019 17:55 An: ProFox Email List profox@leafe.com Betreff: SQL Dump
Hi All
Is there any tricks I am missing which would speed up a dump of a VFP table into a SQL table.
I have predifined by SQL statement something like this
"insert into mytable (myfield) values (?m.myfield)"
I am then scanning through the VFP table performing a scatter memvar and then executing the SQL.
It works it just seems to take alot longer than I imagined it would.
Thanks IA
Chris.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
Is this a one-off data take on, or an ongoing requirement?
If you have the ability to do it, you might see an improvement by disabling all *non-clustered* indexes on the target SQL Server tables before doing your inserts, and then re-enabling them afterward.
Another great tip! Dropping the indexes before adding records is a proven speed tip too if you can do it and then just re-add afterwards.
On 11/29/2019 4:48 AM, Alan Bourke wrote:
Is this a one-off data take on, or an ongoing requirement?
If you have the ability to do it, you might see an improvement by disabling all *non-clustered* indexes on the target SQL Server tables before doing your inserts, and then re-enabling them afterward.
On Fri, 29 Nov 2019, at 2:46 PM, MB Software Solutions, LLC wrote:
Another great tip! Dropping the indexes before adding records
Well, it's not dropping them per se, with MSSQL you can just disable them temporarily which is much easier.
ALTER INDEX myindex ON mytable DISABLE ALTER INDEX myindex ON mytable ENABLE
With the proviso that you do not want to do it on clustered and primary key indexes.
What incorrect answers these were.
IN the DB you made in SQL Server you can import the data at SSIS speed.
Rt click on the DB in the DB pane. Tasks and Import Data.
From there you tell it to use VFP data via your oledb engine. From there
it will make all tables and indexes from VFP and import it very fast.
Sorry I was driving when this was first posted. You can Youtube SQL Server Import data and see a few different vids on how to do this. The wizard has been around for decades and still does a great job of setting up what you need to do and allowing you to watch the process as it is cranking.
Your single row inserts will waste a lot of energy updating the log for each row you inserted. This skips that altogether.
On Thu, Nov 28, 2019 at 10:55 AM Chris Davis chrisd@actongate.co.uk wrote:
Hi All
Is there any tricks I am missing which would speed up a dump of a VFP table into a SQL table.
I have predifined by SQL statement something like this
"insert into mytable (myfield) values (?m.myfield)"
I am then scanning through the VFP table performing a scatter memvar and then executing the SQL.
It works it just seems to take alot longer than I imagined it would.
Thanks IA
Chris.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Thanks everyone for all the ideas and advice, I will give them a try.
Effectively what I am trying to create is a way to replicate a VFP DBC to a SQL Database daily.
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Stephen Russell Sent: Friday, 29 November 2019 16:08 To: profoxtech@leafe.com Subject: Re: SQL Dump
What incorrect answers these were.
IN the DB you made in SQL Server you can import the data at SSIS speed.
Rt click on the DB in the DB pane. Tasks and Import Data.
From there you tell it to use VFP data via your oledb engine. From there
it will make all tables and indexes from VFP and import it very fast.
Sorry I was driving when this was first posted. You can Youtube SQL Server Import data and see a few different vids on how to do this. The wizard has been around for decades and still does a great job of setting up what you need to do and allowing you to watch the process as it is cranking.
Your single row inserts will waste a lot of energy updating the log for each row you inserted. This skips that altogether.
On Thu, Nov 28, 2019 at 10:55 AM Chris Davis chrisd@actongate.co.uk wrote:
Hi All
Is there any tricks I am missing which would speed up a dump of a VFP table into a SQL table.
I have predifined by SQL statement something like this
"insert into mytable (myfield) values (?m.myfield)"
I am then scanning through the VFP table performing a scatter memvar and then executing the SQL.
It works it just seems to take alot longer than I imagined it would.
Thanks IA
Chris.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
As the backend for a website which can work off a day old copy of the data as various tasks performed in the vfp app take exclusive access of the data
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of MB Software Solutions, LLC Sent: Sunday, 01 December 2019 00:19 To: profoxtech@leafe.com Subject: Re: SQL Dump
For the purposes of a daily backup as a failsafe or what?
On 11/30/2019 2:51 PM, Chris Davis wrote:
Thanks everyone for all the ideas and advice, I will give them a try.
Effectively what I am trying to create is a way to replicate a VFP DBC to a SQL Database daily.
[excessive quoting removed by server]
That sounds like an ETL job that I would set up in SSIS
There are plenty of youtube vids on this.
We use SSIS to extract data from our ERP database nightly for inclusion in the data warehouse and then for our data marts.
On Sat, Nov 30, 2019 at 1:52 PM Chris Davis chrisd@actongate.co.uk wrote:
Thanks everyone for all the ideas and advice, I will give them a try.
Effectively what I am trying to create is a way to replicate a VFP DBC to a SQL Database daily.
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Stephen Russell Sent: Friday, 29 November 2019 16:08 To: profoxtech@leafe.com Subject: Re: SQL Dump
What incorrect answers these were.
IN the DB you made in SQL Server you can import the data at SSIS speed.
Rt click on the DB in the DB pane. Tasks and Import Data.
From there you tell it to use VFP data via your oledb engine. From there
it will make all tables and indexes from VFP and import it very fast.
Sorry I was driving when this was first posted. You can Youtube SQL Server Import data and see a few different vids on how to do this. The wizard has been around for decades and still does a great job of setting up what you need to do and allowing you to watch the process as it is cranking.
Your single row inserts will waste a lot of energy updating the log for each row you inserted. This skips that altogether.
On Thu, Nov 28, 2019 at 10:55 AM Chris Davis chrisd@actongate.co.uk wrote:
Hi All
Is there any tricks I am missing which would speed up a dump of a VFP table into a SQL table.
I have predifined by SQL statement something like this
"insert into mytable (myfield) values (?m.myfield)"
I am then scanning through the VFP table performing a scatter memvar and then executing the SQL.
It works it just seems to take alot longer than I imagined it would.
Thanks IA
Chris.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Thanks Russell I will get on google/youtube tonight and look into this , thanks for the heads up
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Stephen Russell Sent: Sunday, 01 December 2019 13:48 To: profoxtech@leafe.com Subject: Re: SQL Dump
That sounds like an ETL job that I would set up in SSIS
There are plenty of youtube vids on this.
We use SSIS to extract data from our ERP database nightly for inclusion in the data warehouse and then for our data marts.
On Sat, Nov 30, 2019 at 1:52 PM Chris Davis chrisd@actongate.co.uk wrote:
Thanks everyone for all the ideas and advice, I will give them a try.
Effectively what I am trying to create is a way to replicate a VFP DBC to a SQL Database daily.
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Stephen Russell Sent: Friday, 29 November 2019 16:08 To: profoxtech@leafe.com Subject: Re: SQL Dump
What incorrect answers these were.
IN the DB you made in SQL Server you can import the data at SSIS speed.
Rt click on the DB in the DB pane. Tasks and Import Data.
From there you tell it to use VFP data via your oledb engine. From there
it will make all tables and indexes from VFP and import it very fast.
Sorry I was driving when this was first posted. You can Youtube SQL Server Import data and see a few different vids on how to do this. The wizard has been around for decades and still does a great job of setting up what you need to do and allowing you to watch the process as it is cranking.
Your single row inserts will waste a lot of energy updating the log for each row you inserted. This skips that altogether.
On Thu, Nov 28, 2019 at 10:55 AM Chris Davis chrisd@actongate.co.uk wrote:
Hi All
Is there any tricks I am missing which would speed up a dump of a VFP table into a SQL table.
I have predifined by SQL statement something like this
"insert into mytable (myfield) values (?m.myfield)"
I am then scanning through the VFP table performing a scatter memvar and then executing the SQL.
It works it just seems to take alot longer than I imagined it would.
Thanks IA
Chris.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Sorry! Thanks Stephen even!
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Chris Davis Sent: Sunday, 01 December 2019 14:30 To: profoxtech@leafe.com Subject: RE: SQL Dump
Thanks Russell I will get on google/youtube tonight and look into this , thanks for the heads up
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Stephen Russell Sent: Sunday, 01 December 2019 13:48 To: profoxtech@leafe.com Subject: Re: SQL Dump
That sounds like an ETL job that I would set up in SSIS
There are plenty of youtube vids on this.
We use SSIS to extract data from our ERP database nightly for inclusion in the data warehouse and then for our data marts.
On Sat, Nov 30, 2019 at 1:52 PM Chris Davis chrisd@actongate.co.uk wrote:
Thanks everyone for all the ideas and advice, I will give them a try.
Effectively what I am trying to create is a way to replicate a VFP DBC to a SQL Database daily.
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Stephen Russell Sent: Friday, 29 November 2019 16:08 To: profoxtech@leafe.com Subject: Re: SQL Dump
What incorrect answers these were.
IN the DB you made in SQL Server you can import the data at SSIS speed.
Rt click on the DB in the DB pane. Tasks and Import Data.
From there you tell it to use VFP data via your oledb engine. From there
it will make all tables and indexes from VFP and import it very fast.
Sorry I was driving when this was first posted. You can Youtube SQL Server Import data and see a few different vids on how to do this. The wizard has been around for decades and still does a great job of setting up what you need to do and allowing you to watch the process as it is cranking.
Your single row inserts will waste a lot of energy updating the log for each row you inserted. This skips that altogether.
On Thu, Nov 28, 2019 at 10:55 AM Chris Davis chrisd@actongate.co.uk wrote:
Hi All
Is there any tricks I am missing which would speed up a dump of a VFP table into a SQL table.
I have predifined by SQL statement something like this
"insert into mytable (myfield) values (?m.myfield)"
I am then scanning through the VFP table performing a scatter memvar and then executing the SQL.
It works it just seems to take alot longer than I imagined it would.
Thanks IA
Chris.
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
-
Alan Bourke -
Chris Davis -
Frank Cazabon -
Jürgen Wondzinski -
MB Software Solutions, LLC -
Stephen Russell