one huge query or several small ones

[kramers]

I need to store about 65 fields.

I'm wondering whether it'll be faster to do about 11 insert queries of about 6 fields or one insert query of about 65?

 

[maswien]

There is no way for you to insert 11 times to the same record. You have to insert all the fields in one shot.

 

[harebrain]

Let's assume kramers meant an insert followed by updates. That's still a bad idea: one complete insert gives the DBMS the opportunity to optimize the layout and distribution of the records from the get-go. Breaking the insert up could cause a less-than optimal distribution of data. The later updates could cause page faults, which would result in the DBMS needing to reorganize the data, thus doing more work than a complete insert.

 

[kramers]

I'm afraid I didn't explain myself very well. Let me try this again.

I have a database of information about one object. That object has about 65 attributes.

I could create one table and store all 65 items at once in a single insert statement or I could create about 10 or 11 tables and store different values (via several insert statements)

I'm wondering which would be more efficient

Example:
Storing information about a ball

One table method (tblBall)
BallNumber, Size, Speed, Direction, Color, NumberOfValves, IsSpherical, SportUsedIn, NumberNeededForSport, etc.

Multiple table method
(tblGeneral)
BallNumber, Size, Color, etc.
(tblMovement)
BallNumber, Speed, Direction
(tblSport)
BallNumber, SportUsedIn, NumberNeeded

etc.

I've temporarily made multiple tables to organize data and because in my limited experience a long query can take longer than several short ones.

Am I mistaken/crazy.

 

[gmmastros]

What you are describing is called "vertical partitions". Google will probably answer your question better.

Here's a couple articles:

http://www.sqlteam.com/Item.ASP?ItemID=684

http://searchwinit.techtarget.com/t...9153,00.html?track=NL-118&ad=483179&ad=483354

Basically, it will speed up your queries but has drawbacks with inserts updates and deletes.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kramers]

Thanks. That's exactly what I was talking about.

 

[gmmastros]

Sometimes it helps just to know what it's called.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom