Board index » delphi » Does anybody know why some indexes can't be seen with Database desktop

Does anybody know why some indexes can't be seen with Database desktop

When I check the Database file (SQL2000) with database desktop (info
structure).  There are no indexes I can see I defined with Create table
function. Only indexes created by SQL server.

When I go and check the database with the query analyzer there are indexes
on that table and those indexes are the ones I created.

Does anybody know what SQL server does with these indexes and how I can
still use them in Delphi ? Now I get the error message that says it can't
open the table because it can't find the index I gave it to use.

Geert Gruwez

 

Re:Does anybody know why some indexes can't be seen with Database desktop


Geert,

   Database Desktop is geared towards desktop dbs like Paradox and Dbase,
not SQL based servers like MSSQL or Oracle.   With SQL based servers, your
application doesn't use indexes, the server uses indexes based on whatever
query optimization plan it decides (all RDBMS work like this).

Sounds like your also trying to use TTables against a SQL based server, so
here is an article from Wayne N of TeamB...

=================================================
TTables vs. TQuerys

The whole reason there is both a TTable and a TQuery component is due to the
fact there table-oriented databases like Dbase, Paradox, or Access, and
there are set-oriented databases like Interbase, Oracle, and MSSQL. These
different types of database systems work and behave completely different
from one another and the same methods of access cannot be equally applied.

TTable is specifically designed to work best with table-oriented systems -
it is "native" to them. Using a TQuery against such databases is slower
because they do not understand SQL and so the BDE must interpret the SQL and
convert it to table calls for that database.

TQuery is specifically designed to work best with set-oriented databases
that understand SQL directly and were designed to work this way. Using a
TTable against such a system is slower because the BDE must convert the
table functions into SQL instructions to be sent off to the database.

Some of the things that TTables do that eat time and resources over a
network with an SQL system are:
- on Opening, always sends many queries to the database to get all the
metadata for all fields and indexes in the selected table in order to
provide you with a selection of these (only *Live* TQuerys do this).
- if you have large records with many fields, TTable will always select ALL
fields even if you only want one or two. This is especially bad if the table
contains blobs and you do not need them.
- Using Locate or FindKey or RecordCount forces all records to be fetched
because such searching / counting has to be done on the client side. This
can be eased by using a good filter (in the Filter property, not the
OnFilter event) to limit the records that need to be fetched (Filters are
turned into SQL where clauses by the BDE).
- if used in a grid, TTable must frequently execute multiple queries to fill
the grid whenever you change record positions.
- Tables prevent you from using the power of SQL when working against a real
SQL server - they only see physical tables (or views in SQL systems),
whereas you can write TQueries to slect any raltionships between and number
of tables and get only *exactly* the data you need.

With TQuerys, you still need to use them right to get the most out of them,
but the point is that you *can* use them right with regard to SQL databases.
- with the exception of extremely small "lookup" type tables (e.g. State
codes) *always* use where clauses to limit the number of records brought
back, if you do not then you are defeating the whole purpose of using them.
- unless you *really* need to every field in a table, always specify the
fields you actually need (e.g. "select cust_id, cust_name from...", not
"select * from..."). A tip here is to avoid editing records in a grid, use
grids only for selection. This allows you to only select the minimum fields
needed for selection, then use another query to select all fields for that
ONE selected record for editing purposes.
- Never use the Filter property or OnFilter event, or call RecordCount with
a TQuery, this forces the entire record set to be fetched. If you really
need the record count, use another query to get it so the server will do the
counting and send back the count itself instead of all the records.

--
Wayne Niddery (WinWright Inc.)
RADBooks - http://members.home.net/wniddery/
I love deadlines. I like the whooshing sound they make as they pass by -
Douglas Adams

Good luck,
krf

Re:Does anybody know why some indexes can't be seen with Database desktop


Hey thanks, this is exactly what I needed to know.  The thing to do now is
just rewrite an application of about roughly 500.000 lines of code.

May the source be with you.

"Kevin Frevert" <kfrev...@midwayusa.com> schreef in bericht
news:3c3d94e5_1@dnews...

Quote
> Geert,

>    Database Desktop is geared towards desktop dbs like Paradox and Dbase,
> not SQL based servers like MSSQL or Oracle.   With SQL based servers, your
> application doesn't use indexes, the server uses indexes based on whatever
> query optimization plan it decides (all RDBMS work like this).

> Sounds like your also trying to use TTables against a SQL based server, so
> here is an article from Wayne N of TeamB...

> =================================================
> TTables vs. TQuerys

> The whole reason there is both a TTable and a TQuery component is due to
the
> fact there table-oriented databases like Dbase, Paradox, or Access, and
> there are set-oriented databases like Interbase, Oracle, and MSSQL. These
> different types of database systems work and behave completely different
> from one another and the same methods of access cannot be equally applied.

> TTable is specifically designed to work best with table-oriented systems -
> it is "native" to them. Using a TQuery against such databases is slower
> because they do not understand SQL and so the BDE must interpret the SQL
and
> convert it to table calls for that database.

> TQuery is specifically designed to work best with set-oriented databases
> that understand SQL directly and were designed to work this way. Using a
> TTable against such a system is slower because the BDE must convert the
> table functions into SQL instructions to be sent off to the database.

> Some of the things that TTables do that eat time and resources over a
> network with an SQL system are:
> - on Opening, always sends many queries to the database to get all the
> metadata for all fields and indexes in the selected table in order to
> provide you with a selection of these (only *Live* TQuerys do this).
> - if you have large records with many fields, TTable will always select
ALL
> fields even if you only want one or two. This is especially bad if the
table
> contains blobs and you do not need them.
> - Using Locate or FindKey or RecordCount forces all records to be fetched
> because such searching / counting has to be done on the client side. This
> can be eased by using a good filter (in the Filter property, not the
> OnFilter event) to limit the records that need to be fetched (Filters are
> turned into SQL where clauses by the BDE).
> - if used in a grid, TTable must frequently execute multiple queries to
fill
> the grid whenever you change record positions.
> - Tables prevent you from using the power of SQL when working against a
real
> SQL server - they only see physical tables (or views in SQL systems),
> whereas you can write TQueries to slect any raltionships between and
number
> of tables and get only *exactly* the data you need.

> With TQuerys, you still need to use them right to get the most out of
them,
> but the point is that you *can* use them right with regard to SQL
databases.
> - with the exception of extremely small "lookup" type tables (e.g. State
> codes) *always* use where clauses to limit the number of records brought
> back, if you do not then you are defeating the whole purpose of using
them.
> - unless you *really* need to every field in a table, always specify the
> fields you actually need (e.g. "select cust_id, cust_name from...", not
> "select * from..."). A tip here is to avoid editing records in a grid, use
> grids only for selection. This allows you to only select the minimum
fields
> needed for selection, then use another query to select all fields for that
> ONE selected record for editing purposes.
> - Never use the Filter property or OnFilter event, or call RecordCount
with
> a TQuery, this forces the entire record set to be fetched. If you really
> need the record count, use another query to get it so the server will do
the
> counting and send back the count itself instead of all the records.

> --
> Wayne Niddery (WinWright Inc.)
> RADBooks - http://members.home.net/wniddery/
> I love deadlines. I like the whooshing sound they make as they pass by -
> Douglas Adams

> Good luck,
> krf

Other Threads