Tuning VFP SQL Pass-Through For Fastest Times Transferring Query Results

[Paul H. Tarver]

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
---

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/07c601d3c78f$eb60d980$c2228c80$@tpcqpc.com
** 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.

[Richard Kaye]

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-***@leafe.com> On Behalf Of Paul H. Tarver
Sent: Thursday, March 29, 2018 2:58 PM
To: ***@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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/***@DM5PR10MB1244.namprd10.prod.outlook.com
** 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.

[Ted Roche]

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.

Post by Richard Kaye
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-----
Sent: Thursday, March 29, 2018 2:58 PM
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CACW6n4tydF5KMLdK0mYMet4r9M8kEW-TCBY_ibc_+he81r=***@mail.gmail.com
** 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.

[Paul H. Tarver]

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-***@leafe.com] On Behalf Of Richard
Kaye
Sent: Thursday, March 29, 2018 2:36 PM
To: ***@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-***@leafe.com> On Behalf Of Paul H. Tarver
Sent: Thursday, March 29, 2018 2:58 PM
To: ***@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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/08cd01d3c7a2$b8ca9570$2a5fc050$@tpcqpc.com
** 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.

[Stephen Russell]

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.

Post by Paul H. Tarver
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-----
Kaye
Sent: Thursday, March 29, 2018 2:36 PM
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-----
Sent: Thursday, March 29, 2018 2:58 PM
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMY+5y+kwsnSBf5zQ1esrGgn2_EAKbPdhnTb-dHhSN-Ev+***@mail.gmail.com
** 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.

[Paul H. Tarver]

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-***@leafe.com] On Behalf Of Stephen
Russell
Sent: Thursday, March 29, 2018 4:23 PM
To: ***@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.

Post by Paul H. Tarver
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-----
Richard Kaye
Sent: Thursday, March 29, 2018 2:36 PM
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-----
Sent: Thursday, March 29, 2018 2:58 PM
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.

Post by Paul H. Tarver
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/090401d3c7ac$8d985770$a8c90650$@tpcqpc.com
** 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.

[Stephen Russell]

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.

Post by Paul H. Tarver
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]

_______________________________________________
Post Messages to: ***@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/CAJidMYJpx2J_7T02bLKNumEN36saq6D7F14QGe0RT34=***@mail.gmail.com
** 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.