Board index » delphi » Delphi 2.0 to MS-SQL 6.0 speed problem.

Delphi 2.0 to MS-SQL 6.0 speed problem.

I'm using Delphi 2.0 developer. version.  No SQL-Links and no Delphi
clients/server. Just straight delphi  2.0 link to NT 3.51 and a MS-SQL
6.0  backend. My app. takes about 20-30 minutes to load. I'm stumped as to
why
it takes so long. The app opens 6 tables on startup as well as 10 forms.
I'm using Ttable, TGrid components to connect via BDE to ODBC the problem
could be
here?

There are 24 actual SQL tables. But at the moment my app uses only 6.
Opening 5 on Startup.

Name                    Rows          Indexes
-------                         ----------        --------------
Cards                    3 679                  6
Customer                20 611          2
Customer Pocket         321 142            10
Order Header                20 376               2
Order Detail                 223 290                 4
Price Codes                        52                1

The number of "indexs for the Customer Pocket"  table seem high (overhead
problems). But if the old VB system (converting from VB to Delphi) uses
them all I'm in trouble,therefore,  I'm lothe to cull them. I'll do that
once the Delphi system is
up and running. The indexes were created by an MS-ACCESS Upsizing Wizard
and
therefore were in the original system.

The normalization seems ok. (Third Normal Form)

Server Side: MS-SQL 6.0 (60 users) on WIN Nt 3.51 server.  Pentium, 100 2x
1gig HD's, 64 megs ram. 48 megs set aside for SQL. SQL has been cofigured
with priority boost enabled.

Client Side: 8 PC's ranging from 486 DX  75  16megs ram 450 megs HD to
Pentium 100 16 megs all running WIN 95. Least amount of virtual memory is
32 megs.

Can anybody help? I'm desperate? My Clients want to skin me alive!!!

Thanks
John

 

Re:Delphi 2.0 to MS-SQL 6.0 speed problem.


Quote
John King wrote:

> I'm using Delphi 2.0 developer. version.  No SQL-Links and no Delphi
> clients/server. Just straight delphi  2.0 link to NT 3.51 and a
> MS-SQL 6.0  backend. My app. takes about 20-30 minutes to load. I'm
> stumped as to why it takes so long. The app opens 6 tables on startup
> as well as 10 forms. I'm using Ttable, TGrid components to connect
> via BDE to ODBC the problem could be here?

        The first thing you want to do is get rid of every single
        TTable.  This is most likely what is causing the main
        problem.  Do you really need to have all of your detail
        records open at once.  Next you will want to start
        using Stored Procedures, this will also speed things
        up dramatically.

        My Situation is similar:
        SQL 6.5
        BDE->ODBC32 (Version 2.65.X)
        NT 4.0
        My App takes about 15 seconds to load on a P75 with 16 MB

Quote
> There are 24 actual SQL tables. But at the moment my app uses only 6.
> Opening 5 on Startup.

        I have over 50 tables.

Quote

> Name                    Rows          Indexes
> -------            ----------        --------------
> Cards                    3 679          6
> Customer                20 611          2
> Customer Pocket        321 142         10
> Order Header            20 376          2
> Order Detail           223 290          4
> Price Codes                 52          1

        This doesn't seem extra large to me.  My Customer
        Table has 15K Records, My Detail table has 100K
        records (growing at 30K/month).

Quote
> Server Side: MS-SQL 6.0 (60 users) on WIN Nt 3.51 server.  Pentium,
> 100 2x 1gig HD's, 64 megs ram. 48 megs set aside for SQL. SQL has
> been cofigured with priority boost enabled.

        64 MB of Ram for 60 users seems a little low.  I have
        48 MB of Ram for 15 users.

        You might want to take a look at the SQL Current activity
        when your program loads, I am sure you will see a lot
        of SQL * FROM with no where conditions.

        brant
        ...

Re:Delphi 2.0 to MS-SQL 6.0 speed problem.


Hi John,

BDE Tables are known to be slow, esp. with certain SQL database systems.
Try using SQL selects instead of TTable, this should increase the speed of
your application dramatically.

Regards,
  Willo

John King <t...@dbn.lia.net> wrote in article
<01bc1110$6ef17c80$4f231bc4@client>...

Quote
> I'm using Delphi 2.0 developer. version.  No SQL-Links and no Delphi
> clients/server. Just straight delphi  2.0 link to NT 3.51 and a MS-SQL
> 6.0  backend. My app. takes about 20-30 minutes to load. I'm stumped as
to
> why
> it takes so long. The app opens 6 tables on startup as well as 10 forms.
> I'm using Ttable, TGrid components to connect via BDE to ODBC the problem
> could be
> here?
>> Snip! Sorry <<
> Can anybody help? I'm desperate? My Clients want to skin me alive!!!

> Thanks
> John

Other Threads