Re: SQL VS ACCESS
It goes like this:
Select id_seq.nextval AS id
From dual
Yvette Ingram
Brainbench Certified ColdFusion 4.5 Programmer
Email: ingramrecruiting@erols or
[EMAIL PROTECTED]
ICQ: 21200397
- Original Message -
From: "Don Vawter" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, June 13, 2001 8:54 PM
Subject: Re: SQL VS ACCESS
> Concerning Oracle (and I don't have a lot of experience) you can set up
> autincrementing fields I don't remember the details because it has been
> about a year but "select from dual" or something similiar will get the
next
> value. You can also reset the starting pt etc.
>
> - Original Message -
> From: "Mark Warrick" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Wednesday, June 13, 2001 5:44 PM
> Subject: RE: SQL VS ACCESS
>
>
> > Costas,
> >
> > Your point is well taken, but I never meant to imply that I would allow
a
> > primary key field to be anything other than a number (INT).
> >
> > The name of the primary key field doesn't matter, however I typically do
> > what you do and prefix (or suffix) the primary key field name with "ID".
> >
> > I setup one table in the database called KEYS. (Very simple: ID_KEY
(INT)
> &
> > USED_DATETIME (date/time). Whenever I need a unique key, I call the
> > following template:
> >
> >
> >
> >
> >
> >
> >
> > SELECT max (ID_KEY) as maxid
> > FROM KEYS
> >
> >
> >
> >
> >
> > INSERT INTO KEYS (ID_KEY, USED_DATETIME) VALUES (#id_key#,
> > #CreateODBCDateTime(Now())#)
> >
> >
> >
> >
> >
> >
> > If I was using Oracle as the DB, I would call a stored procedure to have
> the
> > DB create a unique key for me.
> >
> > One of the benefits of this is that all numbers used for primary keys in
> all
> > tables are unique. So you never have to worry about the "scope"
(persay -
> > that's not the right term for that) of the number because you know it
> could
> > have only come from one of the tables in the system.
> >
> > The issue about joins is true as well. I'll RARELY join text fields.
> >
> > Your point about upsizing as an INT field and then changing it back to
an
> > INT PK SEED {n} field is also absolutely valid - if the database
supports
> > autonumbering. Last I checked, Oracle does not support autonumber
fields.
> > (I could be wrong about that.)
> >
> > ---mark
> >
> >
> > Mark Warrick - Fusioneers.com
> > Personal Email: [EMAIL PROTECTED]
> > Business Email: [EMAIL PROTECTED]
> > Phone: 714-547-5386
> > Efax: 801-730-7289
> > Personal URL: http://www.warrick.net
> > Business URL: http://www.fusioneers.com
> > ICQ: 125160 / AIM: markwarric
> >
> >
> > > -Original Message-
> > > From: Costas Piliotis [mailto:[EMAIL PROTECTED]]
> > > Sent: Wednesday, June 13, 2001 2:24 PM
> > > To: CF-Talk
> > > Subject: RE: SQL VS ACCESS
> > >
> > >
> > > Great, but what happens when you wish to change the name of a field in
a
> > > lookup table?
> > >
> > > Do you run update statements on EVERY foreign key field?
> > >
> > > Wouldn't it be easier to just change a value in the database?
> > >
> > > Further, requesting that user's supply indeces isn't the best way to
> work;
> > > user's have a tendancy to want to change the value of their indices
from
> > > time to time. For example, If I have table a:
> > >
> > > CustomerID int
> > > AccountingID char
> > > CompanyName char
> > >
> > > And I want to change the accounting ID (for whatever reason), I'd be
> okay.
> > > However, if I were to omit the customerID field and stick with the
> > > Accounting ID Field, the change would be acceptable.
> > >
> > > Further, joins on integer fields work much faster than joins on
> character
> > > fields. The database has that much less to work with matching up.
> > >
> > > You are right though, autonumber fields can be a real pain. What
> > > you should
> > > usually do in a migration from access to sql server is first go
> > > into all of
> > > your tables, turn off autonumber, and leave the field as an integer
> field,
> > > when
Re: SQL VS ACCESS
Concerning Oracle (and I don't have a lot of experience) you can set up
autincrementing fields I don't remember the details because it has been
about a year but "select from dual" or something similiar will get the next
value. You can also reset the starting pt etc.
- Original Message -
From: "Mark Warrick" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, June 13, 2001 5:44 PM
Subject: RE: SQL VS ACCESS
> Costas,
>
> Your point is well taken, but I never meant to imply that I would allow a
> primary key field to be anything other than a number (INT).
>
> The name of the primary key field doesn't matter, however I typically do
> what you do and prefix (or suffix) the primary key field name with "ID".
>
> I setup one table in the database called KEYS. (Very simple: ID_KEY (INT)
&
> USED_DATETIME (date/time). Whenever I need a unique key, I call the
> following template:
>
>
>
>
>
>
>
> SELECT max (ID_KEY) as maxid
> FROM KEYS
>
>
>
>
>
> INSERT INTO KEYS (ID_KEY, USED_DATETIME) VALUES (#id_key#,
> #CreateODBCDateTime(Now())#)
>
>
>
>
>
>
> If I was using Oracle as the DB, I would call a stored procedure to have
the
> DB create a unique key for me.
>
> One of the benefits of this is that all numbers used for primary keys in
all
> tables are unique. So you never have to worry about the "scope" (persay -
> that's not the right term for that) of the number because you know it
could
> have only come from one of the tables in the system.
>
> The issue about joins is true as well. I'll RARELY join text fields.
>
> Your point about upsizing as an INT field and then changing it back to an
> INT PK SEED {n} field is also absolutely valid - if the database supports
> autonumbering. Last I checked, Oracle does not support autonumber fields.
> (I could be wrong about that.)
>
> ---mark
>
>
> Mark Warrick - Fusioneers.com
> Personal Email: [EMAIL PROTECTED]
> Business Email: [EMAIL PROTECTED]
> Phone: 714-547-5386
> Efax: 801-730-7289
> Personal URL: http://www.warrick.net
> Business URL: http://www.fusioneers.com
> ICQ: 125160 / AIM: markwarric
>
>
> > -Original Message-
> > From: Costas Piliotis [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, June 13, 2001 2:24 PM
> > To: CF-Talk
> > Subject: RE: SQL VS ACCESS
> >
> >
> > Great, but what happens when you wish to change the name of a field in a
> > lookup table?
> >
> > Do you run update statements on EVERY foreign key field?
> >
> > Wouldn't it be easier to just change a value in the database?
> >
> > Further, requesting that user's supply indeces isn't the best way to
work;
> > user's have a tendancy to want to change the value of their indices from
> > time to time. For example, If I have table a:
> >
> > CustomerID int
> > AccountingID char
> > CompanyName char
> >
> > And I want to change the accounting ID (for whatever reason), I'd be
okay.
> > However, if I were to omit the customerID field and stick with the
> > Accounting ID Field, the change would be acceptable.
> >
> > Further, joins on integer fields work much faster than joins on
character
> > fields. The database has that much less to work with matching up.
> >
> > You are right though, autonumber fields can be a real pain. What
> > you should
> > usually do in a migration from access to sql server is first go
> > into all of
> > your tables, turn off autonumber, and leave the field as an integer
field,
> > when you upsize, go back in and revert to autonumber.
> >
> >
> >
> >
> > -Original Message-
> > From: Mark Warrick [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, June 13, 2001 11:27 AM
> > To: CF-Talk
> > Subject: RE: SQL VS ACCESS
> >
> >
> > I've learned to avoid using autonumber fields whenever possible. It
makes
> > the job of moving databases between platforms simple. At worst, I have
to
> > put the primary key constraints back on the index fields of each table -
> > which is no big deal.
> >
> > ---mark
> >
> >
> > Mark Warrick - Fusioneers.com
> > Personal Email: [EMAIL PROTECTED]
> > Business Email: [EMAIL PROTECTED]
> > Phone: 714-547-5386
> > Efax: 801-730-7289
> > Personal URL: http://www.warrick.net
&
RE: SQL VS ACCESS
Costas,
Your point is well taken, but I never meant to imply that I would allow a
primary key field to be anything other than a number (INT).
The name of the primary key field doesn't matter, however I typically do
what you do and prefix (or suffix) the primary key field name with "ID".
I setup one table in the database called KEYS. (Very simple: ID_KEY (INT) &
USED_DATETIME (date/time). Whenever I need a unique key, I call the
following template:
SELECT max (ID_KEY) as maxid
FROM KEYS
INSERT INTO KEYS (ID_KEY, USED_DATETIME) VALUES (#id_key#,
#CreateODBCDateTime(Now())#)
If I was using Oracle as the DB, I would call a stored procedure to have the
DB create a unique key for me.
One of the benefits of this is that all numbers used for primary keys in all
tables are unique. So you never have to worry about the "scope" (persay -
that's not the right term for that) of the number because you know it could
have only come from one of the tables in the system.
The issue about joins is true as well. I'll RARELY join text fields.
Your point about upsizing as an INT field and then changing it back to an
INT PK SEED {n} field is also absolutely valid - if the database supports
autonumbering. Last I checked, Oracle does not support autonumber fields.
(I could be wrong about that.)
---mark
Mark Warrick - Fusioneers.com
Personal Email: [EMAIL PROTECTED]
Business Email: [EMAIL PROTECTED]
Phone: 714-547-5386
Efax: 801-730-7289
Personal URL: http://www.warrick.net
Business URL: http://www.fusioneers.com
ICQ: 125160 / AIM: markwarric
> -Original Message-
> From: Costas Piliotis [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, June 13, 2001 2:24 PM
> To: CF-Talk
> Subject: RE: SQL VS ACCESS
>
>
> Great, but what happens when you wish to change the name of a field in a
> lookup table?
>
> Do you run update statements on EVERY foreign key field?
>
> Wouldn't it be easier to just change a value in the database?
>
> Further, requesting that user's supply indeces isn't the best way to work;
> user's have a tendancy to want to change the value of their indices from
> time to time. For example, If I have table a:
>
> CustomerIDint
> AccountingID char
> CompanyName char
>
> And I want to change the accounting ID (for whatever reason), I'd be okay.
> However, if I were to omit the customerID field and stick with the
> Accounting ID Field, the change would be acceptable.
>
> Further, joins on integer fields work much faster than joins on character
> fields. The database has that much less to work with matching up.
>
> You are right though, autonumber fields can be a real pain. What
> you should
> usually do in a migration from access to sql server is first go
> into all of
> your tables, turn off autonumber, and leave the field as an integer field,
> when you upsize, go back in and revert to autonumber.
>
>
>
>
> -Original Message-
> From: Mark Warrick [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, June 13, 2001 11:27 AM
> To: CF-Talk
> Subject: RE: SQL VS ACCESS
>
>
> I've learned to avoid using autonumber fields whenever possible. It makes
> the job of moving databases between platforms simple. At worst, I have to
> put the primary key constraints back on the index fields of each table -
> which is no big deal.
>
> ---mark
>
>
> Mark Warrick - Fusioneers.com
> Personal Email: [EMAIL PROTECTED]
> Business Email: [EMAIL PROTECTED]
> Phone: 714-547-5386
> Efax: 801-730-7289
> Personal URL: http://www.warrick.net
> Business URL: http://www.fusioneers.com
> ICQ: 125160 / AIM: markwarric
>
> > -Original Message-
> > From: Bud [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, June 13, 2001 4:02 AM
> > To: CF-Talk
> > Subject: RE: SQL VS ACCESS
> >
> >
> > On 6/12/01, Norman Elton penned:
> > >Make sure all your fields that should be indexed are indexed. I think
> > >the upsizing wizard forgets about this.
> >
> > The upsizing wizard does a pretty good job of rebuilding the indexes
> > and relationships. It's DTS that loses that stuff.
> > --
> >
> > Bud Schneehagen - Tropical Web Creations
> >
> > _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
> > ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED]
> > http://www.twcreations.com/
> > 954.721.3452
> >
> >
>
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL VS ACCESS
Great, but what happens when you wish to change the name of a field in a lookup table? Do you run update statements on EVERY foreign key field? Wouldn't it be easier to just change a value in the database? Further, requesting that user's supply indeces isn't the best way to work; user's have a tendancy to want to change the value of their indices from time to time. For example, If I have table a: CustomerID int AccountingIDchar CompanyName char And I want to change the accounting ID (for whatever reason), I'd be okay. However, if I were to omit the customerID field and stick with the Accounting ID Field, the change would be acceptable. Further, joins on integer fields work much faster than joins on character fields. The database has that much less to work with matching up. You are right though, autonumber fields can be a real pain. What you should usually do in a migration from access to sql server is first go into all of your tables, turn off autonumber, and leave the field as an integer field, when you upsize, go back in and revert to autonumber. -Original Message- From: Mark Warrick [mailto:[EMAIL PROTECTED]] Sent: Wednesday, June 13, 2001 11:27 AM To: CF-Talk Subject: RE: SQL VS ACCESS I've learned to avoid using autonumber fields whenever possible. It makes the job of moving databases between platforms simple. At worst, I have to put the primary key constraints back on the index fields of each table - which is no big deal. ---mark Mark Warrick - Fusioneers.com Personal Email: [EMAIL PROTECTED] Business Email: [EMAIL PROTECTED] Phone: 714-547-5386 Efax: 801-730-7289 Personal URL: http://www.warrick.net Business URL: http://www.fusioneers.com ICQ: 125160 / AIM: markwarric > -Original Message- > From: Bud [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, June 13, 2001 4:02 AM > To: CF-Talk > Subject: RE: SQL VS ACCESS > > > On 6/12/01, Norman Elton penned: > >Make sure all your fields that should be indexed are indexed. I think > >the upsizing wizard forgets about this. > > The upsizing wizard does a pretty good job of rebuilding the indexes > and relationships. It's DTS that loses that stuff. > -- > > Bud Schneehagen - Tropical Web Creations > > _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ > ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] > http://www.twcreations.com/ > 954.721.3452 > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL VS ACCESS
I've learned to avoid using autonumber fields whenever possible. It makes the job of moving databases between platforms simple. At worst, I have to put the primary key constraints back on the index fields of each table - which is no big deal. ---mark Mark Warrick - Fusioneers.com Personal Email: [EMAIL PROTECTED] Business Email: [EMAIL PROTECTED] Phone: 714-547-5386 Efax: 801-730-7289 Personal URL: http://www.warrick.net Business URL: http://www.fusioneers.com ICQ: 125160 / AIM: markwarric > -Original Message- > From: Bud [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, June 13, 2001 4:02 AM > To: CF-Talk > Subject: RE: SQL VS ACCESS > > > On 6/12/01, Norman Elton penned: > >Make sure all your fields that should be indexed are indexed. I think the > >upsizing wizard forgets about this. > > The upsizing wizard does a pretty good job of rebuilding the indexes > and relationships. It's DTS that loses that stuff. > -- > > Bud Schneehagen - Tropical Web Creations > > _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ > ColdFusion Solutions / eCommerce Development > [EMAIL PROTECTED] > http://www.twcreations.com/ > 954.721.3452 > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL VS ACCESS
On 6/12/01, Norman Elton penned: >Make sure all your fields that should be indexed are indexed. I think the >upsizing wizard forgets about this. The upsizing wizard does a pretty good job of rebuilding the indexes and relationships. It's DTS that loses that stuff. -- Bud Schneehagen - Tropical Web Creations _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ ColdFusion Solutions / eCommerce Development [EMAIL PROTECTED] http://www.twcreations.com/ 954.721.3452 ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: SQL VS ACCESS
Make sure all your fields that should be indexed are indexed. I think the upsizing wizard forgets about this. Hope this helps Norman -Original Message- From: Bruce, Rodney [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 12, 2001 1:51 PM To: CF-Talk Subject: OT: SQL VS ACCESS I thought that SQL was suppose to be faster than ACCESS. I am running CF 4.5 with ACCESS 2000 and SQL 7.0 I export/import my ACCESS tables into SQL Running the same pages/queries, SQL is on the average taking twice as long. Can anyone give me reasons for this or things I should look into to speed up the SQL. I have been trying to work with stored procedures to put more processing on the DB side, but this seems to be even slower, any ideas what I am doing wrong? Thanks ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
