Creating Tables on the Fly

I have an Access 2000 MDB with ODBC linked tables to a SQL Server 7 back
end. I currently have a selections table in the front end file which the
users use to make selections of records. The table has two fields -- primary
key (which matches primary key of main, SQL Server table), and a boolean
field. The table is linked to the main table in a heterogeneous inner join.
I'm looking to move the table to the back end, while still giving each
machine a unique set of selections. Using one large table with machine name
as part of the primary key actually slows things down. So I'm considering
using a series of tables, where each machine has its own table in the back
end for selections. The machine name would be incorporated in the particular
selections table name, and the front end link would be modified on the fly
when the database is opened to point to that machine's back end selections
table.
This would require having about 50-100 individual selections tables in the
back end database. Also, if a machine doesn't have a table when the database
is opened on that machine, then that table would be created on the fly,
populated, and pointed to via the ODBC link.
Anyone see any problems with this approach, specifically creating the table
on the fly and then immediately using it, as well as having that many little
tables running around? Thanks for any input!
Neil> machine a unique set of selections. Using one large table with machine
> name as part of the primary key actually slows things down. So I'm
> considering
> ...
> This would require having about 50-100 individual selections tables in the
> back end database.
You have 50-100 rows in the table and you think that slows it down? Even
without a clustered index or any index at all, I find it hard to believe
that you can perceive any slowness whatsoever based on a scan of rows in the
three figure range. And to sacrifice manageability for that seems absurd,
at least to me.|||Neil (nospam@.nospam.net) writes:
> I have an Access 2000 MDB with ODBC linked tables to a SQL Server 7 back
> end. I currently have a selections table in the front end file which the
> users use to make selections of records. The table has two fields --
> primary key (which matches primary key of main, SQL Server table), and a
> boolean field. The table is linked to the main table in a heterogeneous
> inner join.
> I'm looking to move the table to the back end, while still giving each
> machine a unique set of selections. Using one large table with machine
> name as part of the primary key actually slows things down. So I'm
> considering using a series of tables, where each machine has its own
> table in the back end for selections. The machine name would be
> incorporated in the particular selections table name, and the front end
> link would be modified on the fly when the database is opened to point
> to that machine's back end selections table.
>...
> Anyone see any problems with this approach, specifically creating the
> table on the fly and then immediately using it, as well as having that
> many little tables running around? Thanks for any input!
Yes, I see problems. Simply don't go there. This is not the way you use a
relational database. Make that machine name part of the PK in the single
table. If having one single table, slows things down, investigate why
instead of resorting to kludges.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You misread my post. I said 50-100 tables, not 50-100 rows.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:ewqxaaL3FHA.3636@.TK2MSFTNGP09.phx.gbl...
> You have 50-100 rows in the table and you think that slows it down? Even
> without a clustered index or any index at all, I find it hard to believe
> that you can perceive any slowness whatsoever based on a scan of rows in
> the three figure range. And to sacrifice manageability for that seems
> absurd, at least to me.
>|||OK, point well taken. I don't see any workaround, though, since I'm using
ODBC linked tables. I can't use a pass-through query and pass the table name
as a parameter, because pass-throughs return read-only sets. And a view
wouldn't be able to return just the records for that machine.
Here's an idea. What if there were one table, but a series of views, with
each view returning records for a particular unique ID. When the app is
opened, it's assigned an available ID and its link is set to the view that
returns records for that ID. Still kind of a kludge, but perhaps a little
better.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96FED3AD11D99Yazorman@.127.0.0.1...
> Neil (nospam@.nospam.net) writes:
> Yes, I see problems. Simply don't go there. This is not the way you use a
> relational database. Make that machine name part of the PK in the single
> table. If having one single table, slows things down, investigate why
> instead of resorting to kludges.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||You are sooooooo screwed up I have to use this in a book! I am looking
for anything you did right and I cannot find it.
[vbcol=seagreen]
field [sic] . The table is linked to the main table in a heterogeneous i
nner join[sic] . <<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. These are FUNDAMENTALLY
DIFFERENT CONCEPTS. SQL does not have Boolean data types -- they woudl
screw up the 3VL that is the foundations of SQL's model.
[vbcol=seagreen]
Then these will not be the same table, will they? Duh!
[vbcol=seagreen]
Machine name' Have you ever read anything on data modeling? Logical
versus Physical' The basics!! The basics!!
[vbcol=seagreen]
Sure, split the LOGICAL design over the PHYSICAL implementation. To
hell with the foundations of RDBMS, Data Modeling , etc. You are doing
1950's tape system in SQL.
[vbcol=seagreen]
You have just implemented a design we found to be a nightmare in the
1950's with magnetic tape file names. It is one of the reasons we went
to disk systems and then to navigation database and then to RDBMs
systems.
[vbcol=seagreen]
a the ODBC link. <<
Back to basics. A table models a set of one kind of entity or a
relationship. Then schema models the reality of the data model in
those terms. Creating them on the fly is the logical equivalent of
having an elephant drp out of the sky.
[vbcol=seagreen]
Dr. Codd,. Chris Date, me, anyone in RDBMS?
Please get some help before you hurt people. Everything you are doing
is wrong|||Neil (nospam@.nospam.net) writes:
> OK, point well taken. I don't see any workaround, though, since I'm
> using ODBC linked tables. I can't use a pass-through query and pass the
> table name as a parameter, because pass-throughs return read-only sets.
> And a view wouldn't be able to return just the records for that
> machine.
As for how to sort out the linked tables, you will have to ask in an
Access newsgroup.

> Here's an idea. What if there were one table, but a series of views, with
> each view returning records for a particular unique ID. When the app is
> opened, it's assigned an available ID and its link is set to the view that
> returns records for that ID. Still kind of a kludge, but perhaps a little
> better.
A table-valued function with the machine name as parameter would be better.
But without knowing Access, I'm quite sure that this can be solved by
adding the machine name on the Access side as well. After all, that is
also an RDBMS, and should be fitted for relational solutions.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||--CELKO-- (jcelko212@.earthlink.net) writes:
> You are sooooooo screwed up I have to use this in a book! I am looking
> for anything you did right and I cannot find it.
He is nowhere near as screwed up as you are. At least Neil knows how
to behave properly and politely.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes, it can be done on the Access side, but, as noted, things are slowed
down. When I had a main SQL table (50,000 records) joined to a local Access
table (also 50,000 records, with an index and a boolean field), the form
took about 13 seconds to open (with the join being, of course, in the front
end).
With the new solution, the main SQL table still has 50,000 records, but, as
a sample, I populated the selections table with 50,000 x 25 records,
simulating 25 machines having records in the table. I created a view,
joining the two tables on the back end, and linked the view (with the
machine name as a field) to the front end. Using that 1,250,000 record view
with a parameter to only return the 50,000 records that match the machine
name, the form took about 17 seconds to open.
One of the reasons to move the selections table to the back end was for
speed in opening the form. But the resulting 1.25 mil record table is
slowing things down than the smaller table in the front end did, even with a
heterogeneous join.
Thus, I'm left with no solution, unless I can use a smaller table, of if
there's something I haven't seen re. using the view in Access (which I don't
think there is).
Neil
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96FF7327EABEYazorman@.127.0.0.1...
> Neil (nospam@.nospam.net) writes:
> As for how to sort out the linked tables, you will have to ask in an
> Access newsgroup.
>
> A table-valued function with the machine name as parameter would be
> better.
> But without knowing Access, I'm quite sure that this can be solved by
> adding the machine name on the Access side as well. After all, that is
> also an RDBMS, and should be fitted for relational solutions.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Hi
I probably know less about access than Erland, but you may want to look at
just what is happening on the SQL Server when you have everything on the
server. SQL Profiler may give you some information that we may be missing
and how each solution works. Also look at the Query Plans for the executed
SQL and check your indexes are being used and that you have up-to-date
statistics.
You may be able to simplify things with the HOST_NAME function in your view.
John
"Neil" <nospam@.nospam.net> wrote in message
news:cZU8f.3368$Rl1.2750@.newsread1.news.pas.earthlink.net...
> Yes, it can be done on the Access side, but, as noted, things are slowed
> down. When I had a main SQL table (50,000 records) joined to a local
> Access table (also 50,000 records, with an index and a boolean field), the
> form took about 13 seconds to open (with the join being, of course, in the
> front end).
> With the new solution, the main SQL table still has 50,000 records, but,
> as a sample, I populated the selections table with 50,000 x 25 records,
> simulating 25 machines having records in the table. I created a view,
> joining the two tables on the back end, and linked the view (with the
> machine name as a field) to the front end. Using that 1,250,000 record
> view with a parameter to only return the 50,000 records that match the
> machine name, the form took about 17 seconds to open.
> One of the reasons to move the selections table to the back end was for
> speed in opening the form. But the resulting 1.25 mil record table is
> slowing things down than the smaller table in the front end did, even with
> a heterogeneous join.
> Thus, I'm left with no solution, unless I can use a smaller table, of if
> there's something I haven't seen re. using the view in Access (which I
> don't think there is).
> Neil
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96FF7327EABEYazorman@.127.0.0.1...
>