Database

Re: Does access work on network?

30-Jul-03 4:49

Maybe I should rephrase:

It's quite difficult to do distributed locking on regions of a single file over the network. Historically, Jet hasn't always got it right, which can lead to inconsistent data in the data file.

You should ensure that all your clients have the latest service pack - currently Service Pack 7[^] - for Jet 4.0 in order to reduce the possibility of errors.

The situation does not so easily arise with MSDE or SQL Server because the file locking is all performed on the server computer, not distributed.

See this Knowledge Base article[^] for more details on how to minimise the possibility of corruption.

For preference, I would tend to use Access/Jet only for small databases with small volumes, with a limited number of concurrent operations, using a local data file. It makes a reasonable backing store for a web service if there aren't very many concurrent users.

Member 9630-Jul-03 5:28

Member 96

30-Jul-03 5:28

Ahh..you linked to a jet 3.0 article, it's possible that jet 3.0 had more problems, but we've been using jet 4.0 in a commercial application that is networked and we have thousands of users worldwide. Data corruption is exceedingly rare probably no more than 15 times have I seen it with our product since we released in early 2000 and in every case has come down to using an outdated jet driver or an external hardware failure / lockup of some sort.

That being said I would agree with everything else you said and in fact we are switching to msde now that enough of the world has a fast enough internet connection that we can include it with our software which is distributed over the internet only.

------------

Re: Does access work on network?

Member 9630-Jul-03 4:23

Member 96

30-Jul-03 4:23

Yes it does and all you need be able to do is "see" the file on the network from each computer and have full rights to it and the folder since there is a second .ldb file involved that must be created and destroyed by any one of the users sharing it.

Please disregard what the other person who answered your question said about corruption and locking. It's extremely rare and should not be a reason to not use it although I too would suggest MSDE for anything more than a test or in-house application.

------------

ADO Performance

RChin29-Jul-03 22:40

RChin

29-Jul-03 22:40

Does anyone here know of any site/document that has done some sort of analysis on the performance (specifically speed) expectations of various ADO connections (JET and SQL)?

I have been doing DB programming using ADO for a while now (C++), however I am never quite sure whether my software is performing optimally for the given task (saving HUGE number of records (100K+), deleting, modifying etc).

I have read all the MSDN 'tips' on using ADO, but I have never been able to compare my performance against what it should be. I always think that my db connection could perform better, but can never prove it.

Any response will be noted.
Cheers.

"..Even my comments have bugs!"

Inspired by Toni78

Re: ADO Performance

Mike Dimmick30-Jul-03 3:36

How to get the columns except one

30-Jul-03 3:36

ADO has the disadvantage of being a wrapper for Visual Basic and scripting languages. In order to support this, it does a lot of copying into small temporary objects.

Using the underlying OLE DB allows you to supply your own buffers for data rows, and also to use more advanced operations such as delayed updates (accumulating an update in memory, then performing the whole update in one network operation).

You may want to look into the OLE DB consumer templates. A helpful resource is the book OLE DB Consumer Templates: A Programmers Guide[^].

You should definitely check whether the time is being taken in generating the resultset or in passing the resultset across the connection. If more time is taken in fetching the resultset, you might want to consider using the adAsyncFetch option to the Command's Execute method, which will fetch up to the CacheSize before returning, then fetch the rest of the rows asynchronously. If you try to access a row not yet received, the operation will block until that row has been received.

Use only the cursors you need for an operation. If you're just reading a resultset, specify adLockReadOnly for the lock type, and adOpenForwardOnly for the cursor type. This allows SQL Server to simply squirt the rows down your connection (it's often called a firehose cursor for this reason).

For SQL Server, you should definitely get used to entering sample queries into Query Analyzer and looking at the Execution Plan that results. This can help you reformulate your queries to work better. The SQL Profiler tool can help you examine a workload and determine if you should add - or remove! - some indexes to improve performance.

For more on optimising SQL Server queries, see Inside SQL Server 2000[^].

Re: ADO Performance

RChin30-Jul-03 22:34

RChin

30-Jul-03 22:34

Thanks for your reply Mike.
I've already adapted certain techniques, like the firehose cursor, but using the ADO interface.
I will definitely have a look at your list of suggested books.

"..Even my comments have bugs!"

Inspired by Toni78

Exceter29-Jul-03 18:45

Exceter

29-Jul-03 18:45

is it possible to get the data of table except for one column?
that is, if I have a table with column names ID, Name, Address, ...
How can I get the data of all columns except for ID column?

and, how to get the column names(not the data) ?
for table names I found the following:
Select * from sysobjects where [type] in ('U')

Mike Dimmick30-Jul-03 3:07

30-Jul-03 3:07

Exceter wrote:
is it possible to get the data of table except for one column?
that is, if I have a table with column names ID, Name, Address, ...
How can I get the data of all columns except for ID column?

Only by specifying all the columns. Note that using the * operator can be a bit costly, because the server has to enumerate the columns - it's usually better to specify them all anyway.

and, how to get the column names(not the data) ?
for table names I found the following:
Select * from sysobjects where [type] in ('U')

See http://msdn.microsoft.com/msdnmag/issues/03/08/CodeGeneration/default.aspx[^] for more details.

Alexander Kojevnikov30-Jul-03 5:15

30-Jul-03 5:15

Mike Dimmick wrote:
How can I get the data of all columns except for ID column?

Just want to add that it's a bad practice to use SELECT *. Think what will happen with your code if you rename some of your table columns or reorder them.

Alexandre Kojevnikov
MCAD charter member
Leuven, Belgium

Philip Patrick31-Jul-03 2:38

Philip Patrick

31-Jul-03 2:38

Alexandre Kojevnikov wrote:
Think what will happen with your code if you rename some of your table columns or reorder them.

So what? If you'll specify all columns in SELECT, you will still need to change your code, then what's the matter? Mike actually pointed the right issue about performance of "*"

Philip Patrick
Web-site: www.stpworks.com
"Two beer or not two beer?" Shakesbeer

Alexander Kojevnikov31-Jul-03 4:16

Re: Detecting MDAC version

31-Jul-03 4:16

Philip Patrick wrote:
you will still need to change your code

Exactly. But if you don't use SELECT * you will get errors in your SQL query and will have one more possibility to think about consequences of column renaming and adapt the client code appropriately.

With SELECT * you might forget about adapting the client code. And in many cases you won't even see compile-time errors.

If you are just reordering columns, you won't have to change anything.

Performance is also an important issue of course.

Alexandre Kojevnikov
MCAD charter member
Leuven, Belgium

Detecting MDAC version

Thesisus29-Jul-03 8:04

Thesisus

29-Jul-03 8:04

Does anyone know how to detect what MDAC version a server is running?

Bo Hunter29-Jul-03 10:32

Bo Hunter

29-Jul-03 10:32

RegistryKey key = Registry.LocalMachine.OpenSubKey( @"SOFTWARE\Microsoft\DataAccess" );
string version = key.GetValue( "FullInstallVer", "0.0.0.0" ) as string;
key.Close();

All this kind of stuff is on MSDN.

Bo Hunter

Re: Detecting MDAC version

Mike Dimmick29-Jul-03 13:36

Re: indexof - wishing for

29-Jul-03 13:36

Programmatically, or for diagnostic purposes?

For diagnostics, use the MDAC Component Checker[^].

The snapshots included with the tool can be somewhat misleading - I don't think I've ever had any system installation, including a completely clean install, report no errors with this tool. But it'll make a fairly good identification.

indexof - wishing for

Megan Forbes29-Jul-03 5:07

Megan Forbes

29-Jul-03 5:07

I have a varchar(30) variable which holds data I want, a '$' and then some more data I want. Basically, I'm trying to cheat and return 2 dates from one function, and this seemed one way to do it.

My problem is this - I want to get the two dates in the sp this string is returned to. I was thinking of finding the position of the '$', then the characters before it will be the first date, and the characters between it and the end of the string will be the second date. The problem is, I'm having trouble getting the position of the '$'.

Any ideas? Smile | :)

I hope I'm making sense Unsure | :~

Look at the world about you and trust to your own convictions. - Ansel Adams

Photography[^]

jeff_martin29-Jul-03 5:16

jeff_martin

29-Jul-03 5:16

--first date
SUBSTRING(1, CHARINDEX('$', variablename))

--second date
SUBSTRING(CHARINDEX('$', variablename) + 1, LEN(variablename) - CHARINDEX('$', variablename))

You may have to adjust by adding/subtracting 1 to get proper results

Re: indexof - wishing for

Mike Dimmick29-Jul-03 13:47

What's wrong with Query String;

29-Jul-03 13:47

Thought of using a table-valued function?

CREATE FUNCTION dbo.fnGetTwoDates
(
   @searchCriteria varchar(30)
)
RETURNS @twoDates TABLE
   (
      FirstDate     datetime,
      SecondDate    datetime
   )
AS
   BEGIN
 
   INSERT INTO @twoDates
   SELECT
      /* Perform selection here */
   
   RETURN

   END
 
Usage:
SELECT FirstDate, SecondDate
FROM dbo.fnGetTwoDates( 'criteria' )

Now, if only we could pass tables INTO stored procedures as parameters - I'm stuck with using temporary tables with fixed names in order to modularise some very large (>1000 line) procedures.

Bo Hunter28-Jul-03 18:55

Bo Hunter

28-Jul-03 18:55

SELECT [ID], Title, NodeType, ParentID, [Language], Description FROM SourceCode WHERE Title LIKE '%Title%' OR Code LIKE '%Title%' AND Language = 1 OR Language = 2 OR Language = 3 OR Language = 4 OR Language = 5 AND NodeType = 1 OR NodeType = 2 OR NodeType = 3 OR NodeType = 4 OR NodeType = 5 OR NodeType = 6 AND ParentID = 4 OR ParentID = 6

Do the numbers have to be quoted?
Any Suggestions?

Thank You
Bo Hunter

Alexander Kojevnikov28-Jul-03 23:20

28-Jul-03 23:20

AND operator has a higher priority than OR. You might want to use parentheses:

SELECT [ID], Title, NodeType, ParentID, [Language], Description 
FROM SourceCode 
WHERE (Title LIKE '%Title%' OR Code LIKE '%Title%') 
AND (Language = 1 OR Language = 2 OR Language = 3 OR Language = 4 OR Language = 5)
AND (NodeType = 1 OR NodeType = 2 OR NodeType = 3 OR NodeType = 4 
OR NodeType = 5 OR NodeType = 6)
AND (ParentID = 4 OR ParentID = 6)

HTH,

Alexandre Kojevnikov
MCAD charter member
Leuven, Belgium

Jon Hulatt29-Jul-03 1:43

Jon Hulatt

29-Jul-03 1:43

Furthermore, you really ought to use "IN":-

SELECT [ID], Title, NodeType, ParentID, [Language], Description 
FROM SourceCode (nolock)
WHERE (Title LIKE '%Title%' OR Code LIKE '%Title%') 
AND Language IN (1,2,3,4,5)
AND NodeType IN (1,2,3,4,5,6)
AND ParentID IN (4,6)

#include <beer.h>

Bo Hunter29-Jul-03 10:21

Bo Hunter

29-Jul-03 10:21

Will the IN operator work in Access?
I forgot to mention that.
And also some of the idetifiers like Language
has to be exscaped. Does that also have to be
exscaped in OleDbParameter like so,
OleDbParameter param = new OleDbParameter( "[Language]", bla bla );
Is this correct?

Thank You
Bo Hunter

Alexander Kojevnikov29-Jul-03 22:07