Migrating Foxpro database programs to SQL 2005

Question

Migrating Foxpro database programs to SQL 2005

novice_coder

SSC-Addicted

Points: 496
More actions
August 1, 2009 at 10:39 am

#135040

Hello,
I have been assigned a task of migrating Foxpro databases and programs manipulating these databases to SQL server 2005. I have successfully migrated FoxPro databases using SSIS. Now I want to write SQL Code to work on the newly ported databases just as my previous foxpro programs did. I would like to know which is the best way for doing this ie Should I use standalone SQL Server Scripts or use stored procedures or write some stored procedures and call them thru SQL scripts. I am new to SQL Server and I am facing this design issue.
Thanks in advance for the help.
Best

Viewing 15 posts - 1 through 15 (of 40 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 1

With the exception of the occasional view or UDF, I'd say write everything into stored procedures and use scripts or a GUI to call them. You can also create SQL Server jobs to execute things on a scheduled basis.

Since you're new to SQL Server, take a look at the definition of RBAR in my signature below. There are, of course, the very, very rare exceptions but, basically, if it loops it's lame.;-)

If you need some help with a specific coding problem, take a look at the first link in my signature below to get better answers quicker.

Welcome aboard.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

novice_coder SSC-Addicted Points: 496 More actions · Answer 2

Hello Jeff,

You are the man. Everything is going in SPs and I am scheduling the scripts calling these SPs.

Thanks for the help.

On RBAR: I guess I have to go row by row, looping. Heres the issue, Say I have a TableA with FieldA and TableB with FieldB. Now the value of FieldA is determined by the value stored in FieldB of TableB. So to generate a value of FieldA for all the records in the TableA, I have to loop thru the whole of TableA. RBAR as u say it 🙂

Cheers

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

novice_coder

Read the article that Jeff has in his signature block. Remember SQL is set based and Jeff's post will show you how to avoid the row by agonizing row (RBAR). Might as well learn it now before you develope bad habits.

As Jeff stated if you have a problem post the table structures, and the items that link the tables together.

Some one will help you if you have further questions

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Allister Reid SSCrazy Points: 2666 More actions · Answer 4

Yeah,

definitely plan carefully on this one, learn to avoid RBAR (almost every time) and you'll be totally astonished by performance gain.

Even if you don't avoid bad habits problems may be masked as you will probably see a significant performance gain just by porting away from Foxpro (I am in the process of doing similar myself).

You'll find if you post questions here in the form detailed in the first links in both Bitbucket's and Jeff's signatures folks'll be really helpful, and you'll learn a lot.

It might be tempting to go for an 'it'll do' strategy, but it sounds like you have a great opportunity to build a system from scratch, and make your mark!

novice_coder SSC-Addicted Points: 496 More actions · Answer 5

Dear Allister Reid, bitbucket and Jeff Moden,

Thanks a lot for your invaluable suggestions and guidance. I have certainly been shown the right direction and I thank you all for that. I see some very good articles written by Jeff on this forum for improving performance. As Allister said, Its a great opportunity that I have got to build a system from scratch and I want to make the most of it. I'll try to stay away from 'it'll do' strategy as much as possible and hopefully my manager will see a performance gain that he is looking for. These foxpro programs take days to execute at present.

Thanks a lot for the help guys. Happy coding 🙂

Cheers

Allister Reid SSCrazy Points: 2666 More actions · Answer 6

Cool, well do you fancy posting ddls, data, query and output for the TableA TableB RBAR you refer to above?

Ah, go on!

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

novice_coder

As you have noted SSC is habited by people who are willing and able to help others in the community. I would sincerely hope that you have documented / remember all the detail of moving your database structure and data to SQL Server. For there will be others facing the same problem (myself in a short time) who will be posting questions on that very same task, and you can then assist them, or even writing an article detailing the procedures involved, either way making the community stronger.

By the way to see a list of all of Jeff's articles, type Jeff Moden in the search box in the upper right hand corner of this forums page, and then you can refine the displayed results by clicking on Articles. Each article is well worth the time devoted to reading same

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

novice_coder SSC-Addicted Points: 496 More actions · Answer 8

@ Allister Reid: 🙂 nope. At present I dont have any plans of doing that. I'm gonna read a few articles by Jeff and others on RBAR and hopefully I will be fine 🙂

@ bitbucket: As of now, I remember the procedure but I am very sure I will forget it soon 😛 I will document it so that I can help others later on. That should make me feel happy 🙂

And yes, I did search the way u mentioned and I saw some good articles to read.

Thanks for the help people.

Cheers

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 9

novice_coder (8/1/2009)
Hello Jeff,
You are the man. Everything is going in SPs and I am scheduling the scripts calling these SPs.
Thanks for the help.
On RBAR: I guess I have to go row by row, looping. Heres the issue, Say I have a TableA with FieldA and TableB with FieldB. Now the value of FieldA is determined by the value stored in FieldB of TableB. So to generate a value of FieldA for all the records in the TableA, I have to loop thru the whole of TableA. RBAR as u say it 🙂
Cheers

Ummmm.... nope... absolutely not. If you'll post a simple test setup with some readily consumable test data, I can show you how to avoid that performance problem just waiting to happen.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

novice_coder SSC-Addicted Points: 496 More actions · Answer 10

Hello,

Below is a script that can be used to generate the sample tables and data:

use Test

--===== If the test table already exists, drop it

IF OBJECT_ID('TableA','U') IS NOT NULL

DROP TABLE TableA

IF OBJECT_ID('TableB','U') IS NOT NULL

DROP TABLE TableB

--===== Create the test table with

CREATE TABLE TableA

(

ID1 INT PRIMARY KEY,

Value1 INT

)

CREATE TABLE TableB

(

ID2 INT PRIMARY KEY,

Value2 INT

)

--===== Insert the test data into the test table

INSERT INTO TableA

(ID1, Value1)

SELECT '4','1' UNION ALL

SELECT '37','8' UNION ALL

SELECT '44','11' UNION ALL

SELECT '54','2'

INSERT INTO TableB

(ID2, Value2)

SELECT '37','' UNION ALL

SELECT '44','11' UNION ALL

SELECT '54',''

Now my foxpro program at present loops through all the records in Table A (RBAR :P), searches ID1 of TableA in ID2 of TableB. If there is a match and if Value2 of that matched record in TableB is 0 then it replaces it with the Value1.

For eg: It will try to find 4(ID1) of TableA in ID2 field of TableB. Since its not peresnt, it will move on to the next record. Now it will look for 37 in ID2 field. It will find it and since value2 is 0, it will replace it with 8(value1)

How will i do it without looping through whole of TableA row by row?

Thanks in advance

Allister Reid SSCrazy Points: 2666 More actions · Answer 11

Hi,

you can do this with following:

UPDATE TableB

SET Value2 = A.Value1

FROM TableA A --Using 'A' as alias for TableA

WHERE

ID2 = A.ID1

AND Value2 = 0

Internally the SQL server has to perform an index scan of TableB (scans every entry in index) but only has to do an index seek on TableA, i.e. the SQL server looks through TableB and only looks at those records in TableA that has matches.

You can get this information from the estimated execution plan (highlight the query and Keystroke Ctrl+L).

This gives invaluable info for optimising your query — quick rule of thumb: index seek faster than index scan which is faster than table scan.

Allister Reid SSCrazy Points: 2666 More actions · Answer 12

Just out of interest, how was this being done in the Foxpro app? Using a while loop, indexseek and replace?

novice_coder SSC-Addicted Points: 496 More actions · Answer 13

Hello Allister,

Thanks for the help. Your code shows how to think column wise rather than going row by row. I am getting a hang of it now. And you are right. That's exactly what my FoxPro program does(while, seek index and replace). Pretty similar to what SQL does but we dont have to code all that stuff in SQL, it does it for you.

Really appreciate your help.

Cheers

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 14

novice_coder (8/2/2009)
Hello Allister,
Thanks for the help. Your code shows how to think column wise rather than going row by row. I am getting a hang of it now. And you are right. That's exactly what my FoxPro program does(while, seek index and replace). Pretty similar to what SQL does but we dont have to code all that stuff in SQL, it does it for you.
Really appreciate your help.
Cheers

Precisely... and that's the #1 recommendation I have for folks that use SQL Server. I even have it in my signature line below but I'll post it here to be absolutely clear...

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

Congratulations on taking the first step... 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)