Optimum method for read/write from SQL Server tables 1

[pmscraiova]

I have an old application that I used for control access based on bar code. Now I must move the databases to SQL Server. I must read from the employees table and write to the control access table. I am not sure what is the best way to read/write into SQL Server tables. I think that there are two possibilities:
1. at each code bar read to open a connection with SQL Server and read/write there through a remote view
2. periodically open a connection and make a local copy of the employees table, interrogate it and insert into a control access local table. After that I’ll update the SQL server control access table from the local table.

From employees table I must read the following records:
- employees Id
- first name
- last name
- birthdate
- parents name
- employee’s photo

Into control access table I must write the following records:
- employees Id
- datetime() of the record
- reason of pass through the control point
- kind of pass (input or output)

I have about 10 pass / minute through the control point.

 

[Mike Lewis]

pmscraiova,

It really depends on how up to date you want the SQL Server table to be. Your second option would give better performance, but the data would not be as up to date as with the first option.

I suggest you do the updating as soon as the bar code is read. However, instead of using a remote view, you should send the UPDATE (or INSERT or whatever) command via SQL pass-through (using SQLEXEC()). Don't get a new connection each time. Open the connection at the start of the application, and keep it open throughout.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[pmscraiova]

Mike,
I think that I’ll use a local copy of the employees table and update the access table via SQL pass-through (as you said). But you said “Open the connection at the start of the application, and keep it open throughout”. My application must run continuous for a long period of time (maybe month, I hope). This will not be a problem for the connection with SQL Server?
Thank you,
Eugen

 

[Mike Lewis]

Eugen,

My application must run continuous for a long period of time (maybe month, I hope). This will not be a problem for the connection with SQL Server?

No, it shouldn't be a problem. The only reason some people prefer to open the connection each time they use it and then closee it again is because SQL Server has a limit on the number of open connections supported. This limit is defined by the administrator. Assuming the server does not normally run close to that limit, it is always more efficient to keep a connection open than to keep on opening it and closing it.

By the way, I've written a freeware class which does some of the work of managing an open connection. If you'd like a copy, you can grab it from:

http://www.ml-consult.demon.co.uk/foxst-25.htm

Mike



Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[pmscraiova]

Thank you very much, Mike. You helped me again! Thank you. I’ll try your advice
Best regards,
Eugen