I'm working on a project which involves issuing multiple queries across several SQL databases and servers. At this point, I'm polling about 30 databases and retrieving data from four tables from each database.
I really have no reason to complain as my procedure including retrieving the data synchronously over a VPN, processing results locally and then exporting the data to local files only takes about 6 minutes.
However, I see this as an opportunity to tune my sql back-end connection and was wondering if anyone had any suggestions say for setting the PacketSize or other changes that might optimize the retrieval of data from the server(s).
Thanks in advance!
Paul
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
I was trying to play with packet size recently and it appears that the current versions of the SQL ODBC driver have deprecated this parameter. At least my attempts to change were met with failure.
I think the best opportunity for optimization would be to create your queries as stored procedures and then call the stored procs. This will allow SQL to generate execution plans that can be reused. This also assumes your queries are written in a way that allows you to pass parameters to the SPs.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Paul H. Tarver Sent: Thursday, March 29, 2018 2:58 PM To: profoxtech@leafe.com Subject: Tuning VFP SQL Pass-Through For Fastest Times Transferring Query Results
I'm working on a project which involves issuing multiple queries across several SQL databases and servers. At this point, I'm polling about 30 databases and retrieving data from four tables from each database.
I really have no reason to complain as my procedure including retrieving the data synchronously over a VPN, processing results locally and then exporting the data to local files only takes about 6 minutes.
However, I see this as an opportunity to tune my sql back-end connection and was wondering if anyone had any suggestions say for setting the PacketSize or other changes that might optimize the retrieval of data from the server(s).
Thanks in advance!
Paul
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
You can also use SQLPrepare() on the Fox end if you're going to be repeatedly executing the same query with different parameters.
If you need the front end (VFP) to keep doing things while waiting for the backend, you can set the connection as asynchronous and use SQLMoreResults() to pull the data down when available. This is pretty rare.
You can also batch multiple queries together. It all depends (stop me if you've heard this before) on the specifics of your data, structure, infrastructure and performance metrics. The trick is figuring out where the slow parts are.
On Thu, Mar 29, 2018 at 3:36 PM, Richard Kaye rkaye@invaluable.com wrote:
I was trying to play with packet size recently and it appears that the current versions of the SQL ODBC driver have deprecated this parameter. At least my attempts to change were met with failure.
I think the best opportunity for optimization would be to create your queries as stored procedures and then call the stored procs. This will allow SQL to generate execution plans that can be reused. This also assumes your queries are written in a way that allows you to pass parameters to the SPs.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Paul H. Tarver Sent: Thursday, March 29, 2018 2:58 PM To: profoxtech@leafe.com Subject: Tuning VFP SQL Pass-Through For Fastest Times Transferring Query Results
I'm working on a project which involves issuing multiple queries across several SQL databases and servers. At this point, I'm polling about 30 databases and retrieving data from four tables from each database.
I really have no reason to complain as my procedure including retrieving the data synchronously over a VPN, processing results locally and then exporting the data to local files only takes about 6 minutes.
However, I see this as an opportunity to tune my sql back-end connection and was wondering if anyone had any suggestions say for setting the PacketSize or other changes that might optimize the retrieval of data from the server(s).
Thanks in advance!
Paul
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
I was just about to post a follow up note for the group because once I started testing with changing the default PacketSize, I realized that it wasn't changing no matter what I did.
In response to your comments regarding optimizing the queries, I think I should clarify that what I'm interested in speeding is up isn't the query itself, but rather the transfer of data from the server to my local instance of VFP. I am under the impression I will get a bit of a boost once I move my development off the VPN to a full internal network connection, but I guess I'm greedy enough to think perhaps I can squeeze just a few more milliseconds out of the data transfer process since with 30 separate databases to query the transfer time is taking up a significant portion of my total processing time.
The Fox has never let me down, so I just figured I would go back to the well one more time! :)
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: Thursday, March 29, 2018 2:36 PM To: profoxtech@leafe.com Subject: RE: Tuning VFP SQL Pass-Through For Fastest Times Transferring Query Results
I was trying to play with packet size recently and it appears that the current versions of the SQL ODBC driver have deprecated this parameter. At least my attempts to change were met with failure.
I think the best opportunity for optimization would be to create your queries as stored procedures and then call the stored procs. This will allow SQL to generate execution plans that can be reused. This also assumes your queries are written in a way that allows you to pass parameters to the SPs.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Paul H. Tarver Sent: Thursday, March 29, 2018 2:58 PM To: profoxtech@leafe.com Subject: Tuning VFP SQL Pass-Through For Fastest Times Transferring Query Results
I'm working on a project which involves issuing multiple queries across several SQL databases and servers. At this point, I'm polling about 30 databases and retrieving data from four tables from each database.
I really have no reason to complain as my procedure including retrieving the data synchronously over a VPN, processing results locally and then exporting the data to local files only takes about 6 minutes.
However, I see this as an opportunity to tune my sql back-end connection and was wondering if anyone had any suggestions say for setting the PacketSize or other changes that might optimize the retrieval of data from the server(s).
Thanks in advance!
Paul
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---
[excessive quoting removed by server]
The return of data speed is always a volume concept. If you select * and there are 40-100 columns and you really only need 20 only ask for them back. Your speed will improve for quantities > 1000 rows.
On Thu, Mar 29, 2018 at 4:13 PM, Paul H. Tarver paul@tpcqpc.com wrote:
I was just about to post a follow up note for the group because once I started testing with changing the default PacketSize, I realized that it wasn't changing no matter what I did.
In response to your comments regarding optimizing the queries, I think I should clarify that what I'm interested in speeding is up isn't the query itself, but rather the transfer of data from the server to my local instance of VFP. I am under the impression I will get a bit of a boost once I move my development off the VPN to a full internal network connection, but I guess I'm greedy enough to think perhaps I can squeeze just a few more milliseconds out of the data transfer process since with 30 separate databases to query the transfer time is taking up a significant portion of my total processing time.
The Fox has never let me down, so I just figured I would go back to the well one more time! :)
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: Thursday, March 29, 2018 2:36 PM To: profoxtech@leafe.com Subject: RE: Tuning VFP SQL Pass-Through For Fastest Times Transferring Query Results
I was trying to play with packet size recently and it appears that the current versions of the SQL ODBC driver have deprecated this parameter. At least my attempts to change were met with failure.
I think the best opportunity for optimization would be to create your queries as stored procedures and then call the stored procs. This will allow SQL to generate execution plans that can be reused. This also assumes your queries are written in a way that allows you to pass parameters to the SPs.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Paul H. Tarver Sent: Thursday, March 29, 2018 2:58 PM To: profoxtech@leafe.com Subject: Tuning VFP SQL Pass-Through For Fastest Times Transferring Query Results
I'm working on a project which involves issuing multiple queries across several SQL databases and servers. At this point, I'm polling about 30 databases and retrieving data from four tables from each database.
I really have no reason to complain as my procedure including retrieving the data synchronously over a VPN, processing results locally and then exporting the data to local files only takes about 6 minutes.
However, I see this as an opportunity to tune my sql back-end connection and was wondering if anyone had any suggestions say for setting the PacketSize or other changes that might optimize the retrieval of data from the server(s).
Thanks in advance!
Paul
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
Understood and I plan on doing a full review of each field before I'm done, but I am at a point in the development where the SQL queries are very stable and I thought it might be a good time to look for simple tweaks I could make to optimize the connection and be able do some direct comparisons of the results.
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: Thursday, March 29, 2018 4:23 PM To: profoxtech@leafe.com Subject: Re: Tuning VFP SQL Pass-Through For Fastest Times Transferring Query Results
The return of data speed is always a volume concept. If you select * and there are 40-100 columns and you really only need 20 only ask for them back. Your speed will improve for quantities > 1000 rows.
On Thu, Mar 29, 2018 at 4:13 PM, Paul H. Tarver paul@tpcqpc.com wrote:
I was just about to post a follow up note for the group because once I started testing with changing the default PacketSize, I realized that it wasn't changing no matter what I did.
In response to your comments regarding optimizing the queries, I think I should clarify that what I'm interested in speeding is up isn't the query itself, but rather the transfer of data from the server to my local instance of VFP. I am under the impression I will get a bit of a boost once I move my development off the VPN to a full internal network connection, but I guess I'm greedy enough to think perhaps I can squeeze just a few more milliseconds out of the data transfer process since with 30 separate databases to query the transfer time is taking up a significant portion of my total processing time.
The Fox has never let me down, so I just figured I would go back to the well one more time! :)
Paul
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Richard Kaye Sent: Thursday, March 29, 2018 2:36 PM To: profoxtech@leafe.com Subject: RE: Tuning VFP SQL Pass-Through For Fastest Times Transferring Query Results
I was trying to play with packet size recently and it appears that the current versions of the SQL ODBC driver have deprecated this parameter. At least my attempts to change were met with failure.
I think the best opportunity for optimization would be to create your queries as stored procedures and then call the stored procs. This will allow SQL to generate execution plans that can be reused. This also assumes your queries are written in a way that allows you to pass parameters to the SPs.
--
rk
-----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Paul H. Tarver Sent: Thursday, March 29, 2018 2:58 PM To: profoxtech@leafe.com Subject: Tuning VFP SQL Pass-Through For Fastest Times Transferring Query Results
I'm working on a project which involves issuing multiple queries across several SQL databases and servers. At this point, I'm polling about 30 databases and retrieving data from four tables from each
database.
I really have no reason to complain as my procedure including retrieving the data synchronously over a VPN, processing results locally and then exporting the data to local files only takes about 6 minutes.
However, I see this as an opportunity to tune my sql back-end connection and was wondering if anyone had any suggestions say for setting the PacketSize or other changes that might optimize the retrieval of data from the server(s).
Thanks in advance!
Paul
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
If you have multiple repetitive queries to run on same db, consider writing them to a working table on that server's db and then running
exec ProccessMyBatch
Of course, you have to create the sproc of ProcessMyBatch there as well. I have no idea what the return of the initial process is so this may be a good idea or not.
The point I am making is to do the most work where it is fastest and then query the results a tiny bit later.
We are implementing this in a rewrite of our pricing update that runs every month to do likewise right now.
On Thu, Mar 29, 2018 at 1:58 PM, Paul H. Tarver paul@tpcqpc.com wrote:
I'm working on a project which involves issuing multiple queries across several SQL databases and servers. At this point, I'm polling about 30 databases and retrieving data from four tables from each database.
I really have no reason to complain as my procedure including retrieving the data synchronously over a VPN, processing results locally and then exporting the data to local files only takes about 6 minutes.
However, I see this as an opportunity to tune my sql back-end connection and was wondering if anyone had any suggestions say for setting the PacketSize or other changes that might optimize the retrieval of data from the server(s).
Thanks in advance!
Paul
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html
[excessive quoting removed by server]
-
Paul H. Tarver -
Richard Kaye -
Stephen Russell -
Ted Roche