Request Advice: Big Tables, Expr. Complex Error

[Guest]

It was suggested to me the reason why I'm getting a "Expression is to
Complex" error message in my local ADO.Net application is because the
generated update query has too many AND's in the WHERE clause and the the MS
Access Jet provider is limited for this many fields (columns) ( max is 100
fields for DA).

The data source/set is a MS-Access Main table with 110 fields (columns).
The table grew from a little table to a big table overnight because of spec
changes. I know how to normilize the table in Access but not in ADO.Net
environment plus, that seems like a lot of work since the application is
almost done.

What are my options to remove the Expression is to Complex error message ?
Since I'm using MS-Access on the server, is it time to step-up to MS-SQL
Server? Does SQL Serverl use the Jet provider? Do you think the complex
error will go away with SQL Server? Comments?

Steve

 

[Rene]

Perhaps you could make a query that filters most of your records (not all of
them) and then you can use the power of your programming language to loop
through all of the columns and only pick the records that match your
criteria.

Remember that if you are using such big criteria then you won't be able to
build a small index and you will end up scanning the WHOLE table every time
you query your table. This is even worst with Jet because if your database
is in a network server Jet will have to return the whole table to your
computer so it can run the query (because of the lack of a good Index).

The best thing for you to do is to build an Index with the columns that
matter most on your query and then use that index to retrieve the minimum
amount of row that you can. After that you can do what I suggested at the
beginning of my respond to filter out the rows that are a real match.

Just a suggestion.

 

[Guest]

Thanks Rene for the recommendation. The problem is almost all the fields are
filled with different data.

In general, these are aircraft parts. The parts have about 20 fields of
general info and then 30 characteristics of manufacture. Each characteristic
has 3 fields/characteristic so for a typical part you have 20 + (30 *3)
fields containig different info. However, I could do what you suggest for
the 20 fields. As a matter of fact maybe I could ask the user if they want
to see the 15 or 30 characteristics.

Thanks Steve

 

[Rene]

How is the part narrowed? I doubt that one person searches for a part using
all 110 criteria fields do they??

I don't know anything about your system but if I was designing an inventory
system I would probably begin by asking the user to filter the key elements
of the part. for example: Part (Wing, Tire, Bolt), Manufacturer, etc. This
key criteria items could be easily loaded to your application into a combo
box dropdown list.

Once the user selected this key elements you will run your query and return
all the matching rows. The user will then have to pick the part from the
list. You could allow him or her to filter the result using your grid filter
and sort capabilities.

I don't think is a good idea to have such huge criteria, I would suggest
rethinking the problem.

 

[luxspes]

Thanks Rene for the recommendation. The problem is almost all the fields are
filled with different data.

In general, these are aircraft parts. The parts have about 20 fields of
general info and then 30 characteristics of manufacture.

I think you should change the design of you database, and have a table
"AircraftParts" with a to many relationship with "Characteristics"


AircraftParts
=============
PartID (PrimaryKey)
PartName


Characteristics
=============
CharacteristicsID (Primary Key)
CharacteristicName
CharacteristicField1
CharacteristicField2
CharacteristicField3
PartID(Foreing key to AircraftParts)


Each characteristic

 

[Cor Ligthert [MVP]]

Steve,

Going to another database don't help you.
The 100 columns limit is in ADONET a limit for all wizards.

Better is to rearange your database in more tables.

(Otherwise you have to do all by hand, by instance naming all first 100 and
than adding the other 10 in the code)

I hope this helps,

Cor

 

[Guest]

What about upgrading to SQL Server? Does SQL Server use the Jet provider? Do
you think the complex error will go away with a SQL Server provider?
Comments?

Currentlly, I'm using Access on the server. Note: I just stripped the the
DA/DS from the wizard/designer and put it in code. I haven't tested it yet
but I guess I'm still limited by Jet provider??

Also, as everybody is suggesting, if I normalize the Table into smaller
tables how do I incorporate multiple tables in to the ADO.Net Interface? Web
site available with info?

Steve

 

[Rene]

What about upgrading to SQL Server? Does SQL Server use the Jet provider?

You could if you wanted to but that won't be the most sensible thing to do.
If you are going to use SQL Server then you should use the "Microsoft OLE DB
Provider for SQL Server" provider.

 

[Guest]

Thanks, thats my question how much different is Microsoft OLE DB
Provider for SQL Server then the Microsoft Jet provider AND will the
Microsoft OLE DB Provider address my Expression to Complex error message

Steve

 

[Rene]

Thanks, thats my question how much different is Microsoft OLE DB

Provider for SQL Server then the Microsoft Jet provider AND will the
Microsoft OLE DB Provider address my Expression to Complex error message

I doubt that anyone here can answer that question, you will have to try it
and see what happens. I believe that Access (at least the one on the Office
XP version) come with an Upsizing Wizard to move your database to an SQL
server easily, after that all you have to do (theorically) is to change the
connection string to connect to the SQL server instead of Access and try it.

For better results you should use the SQL data objects.

 

[Guest]

Hi Rene

It's strange you say that because just yesterday I was talking to somebody
about the problem and then I remembered reading a MSDN newsletter that you
could get 180 day trial version of the new SQL Server/VS. So, in about 5
minutes I'm going to order the trial version of SQL Server and try it on the
table. FYI - MS Access allows 256 columns I really don't think 110 fields is
too many but, as I order SQL Server/VS evaluation copy I'm going to break up
the table just in case SQL Server doesn't solve my problem and do what the
experts say (normalize the table).

I'm also going to a SQL Server/VS launch event on 12/15 in Boston

https://www.tryvs2005.com/profile.aspx

Thanks Rene

Steve