locked
odbc driver woes RRS feed

  • Question

  • Hello!

    I've been using the vfp odbc driver to upsize somewhat large vfp 9 data files to sql server 2008 and also in some 9.0 applications with good success however, when I used the oledb driver on the sql side to 'pull' the files, the turnaround time was astronomically faster (upsizing a 1/4 gigabyte table took over an hour while importing the same vfp table with sql's oledb driver took a couple minutes).  Just wondering if there's a method I could use in my application to speed up queries when dealing with large numbers of sql object rows in vfp.  Even executing sql stored procedures bogs down the application.  I've been forced to come up with alternate ways to handle this problem, like storing data locally.  This causes other challenges like keeping the local tables up to date in a timely fashion and  I've had to come up with alternate ways of having these local tables keep up with the ever-changing sql tables.  It'd be so much simpler if I can just get that turnaround time down to a reasonable level, however, managers don't want to hear that converting to client'server comes with these slowdowns.  Ideas welcome!  Thank you in advance!

    Dan


    Daniel C. Bowler

    Sunday, March 24, 2013 3:29 AM

Answers

  • When querying from VFP are you using SQL Passthrough or CursorAdapter?

    Do you have SQL Server properly indexed?

    Have you used the VFP Coverage Profiler to determine if the query is the actual slow part or if it's loading data into the form and displaying it or something else?


    Craig Berntson
    MCSD, Visual C# MVP
    INETA Community Speaker
    www.craigberntson.com

    Sunday, March 24, 2013 1:57 PM
  • What would help to investigate the reason:

    1. What driver (DLL/version) are you using?

    2. What queries / Skripts do you execute?

    Even if you do the same things from VFP command window and from SSMS, if you always start in VFP SSMS profits from caching, so this comparison will always be in favor of what you use second.

    If you can reproduce the bad performance from VFP that's an indicator something is wrong, in general VFP developers do use VFP in combination with SQL Server with good performance for decades and my personal experience for 5 years also isn't showing a general problem with the combination, so it must be a very specific reason in your case. More details will help find out what's wrong.

    Bye, Olaf.


    Olaf Doschke (Setmics)

    Friday, March 29, 2013 1:03 PM
  • I just reread this and it doesn't make sense. "importing the same vfp table with sql's oledb driver". Which oledb driver did you use here? I'm also wondering why you didn't use the VFP Upsizing Wizard, more specifically, the new one available on CodePlex. Why do you use local tables that you then need to continually update? There may be other ways to do what you want.

    Craig Berntson
    MCSD, Visual C# MVP
    INETA Community Speaker
    www.craigberntson.com

    Saturday, March 30, 2013 11:36 AM

All replies

  • When querying from VFP are you using SQL Passthrough or CursorAdapter?

    Do you have SQL Server properly indexed?

    Have you used the VFP Coverage Profiler to determine if the query is the actual slow part or if it's loading data into the form and displaying it or something else?


    Craig Berntson
    MCSD, Visual C# MVP
    INETA Community Speaker
    www.craigberntson.com

    Sunday, March 24, 2013 1:57 PM
  • SQL Passthrough.

    I may not have sql properly indexed, however, when I run the very same sql stored procedure in the management studio, it takes less than one second to execute the query and return every last row.  When I run the vfp spt command that calls the stored procedure, and I'm executing individual commands from the vfp command window, it took over 5 minutes to return control.

     

    I'm not familiar with the vfp Coverage Profiler, but I'm fairly certain the bottlenect is somewhere between vfp, the odbc driver and sql.  Dan


    Daniel C. Bowler

    Sunday, March 24, 2013 10:36 PM
  • Any more ideas for Daniel?

    Thanks!


    Ed Price (a.k.a User Ed), SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Wednesday, March 27, 2013 10:21 PM
  • Thank you Ed!

    Daniel C. Bowler

    Thursday, March 28, 2013 7:13 PM
  • What would help to investigate the reason:

    1. What driver (DLL/version) are you using?

    2. What queries / Skripts do you execute?

    Even if you do the same things from VFP command window and from SSMS, if you always start in VFP SSMS profits from caching, so this comparison will always be in favor of what you use second.

    If you can reproduce the bad performance from VFP that's an indicator something is wrong, in general VFP developers do use VFP in combination with SQL Server with good performance for decades and my personal experience for 5 years also isn't showing a general problem with the combination, so it must be a very specific reason in your case. More details will help find out what's wrong.

    Bye, Olaf.


    Olaf Doschke (Setmics)

    Friday, March 29, 2013 1:03 PM
  • I just reread this and it doesn't make sense. "importing the same vfp table with sql's oledb driver". Which oledb driver did you use here? I'm also wondering why you didn't use the VFP Upsizing Wizard, more specifically, the new one available on CodePlex. Why do you use local tables that you then need to continually update? There may be other ways to do what you want.

    Craig Berntson
    MCSD, Visual C# MVP
    INETA Community Speaker
    www.craigberntson.com

    Saturday, March 30, 2013 11:36 AM