Why is SQL Server 7 so slow? I only have about 11500 rows in the table 1

[Guest_imported]

I wonder how SQL Server 7 can be so slow!! I use an external application (that we made) which reads information from SQL Server 7 databases. What I mean is, one of my allications reads information about "users" and there is about 11500 rows in the table with 34 columns in each. My application shows information about one person at a time. And then there's a scrollbox at the bottom where one can scroll to see other users (about 11500 different users). When I'm using the scrollbars to move down and get information about other users, I see that the CPU-usage is 100% all the time. And the "hour-glass" (windows thingy to show that there's a delay) is shown for maybe a second. And at first it takes almost 10 seconds to (I suppose?) read in the information from the table into my application and show information about the first user.

I don't think it should take almost a second or so to just scroll this list of users. How can SQL Server be so slow? I have 196 megabyte ram on this computer that I use for development. All databases together are less than 15 megabytes. Less than 10 megabytes I suppose.

I mean, in Access97 it was much faster but whas gettins slower for certain occurances with much more data int he tables, and the whole idea of converting the system to SQL Server7 was to got fast responsetimes.

What is the big problem here?

regards, Bob Nachbar.

 

[danpep]

Sound like an applicationd design problem. You should rather specify some criteria for the client (surname for example) and bring back only clients with mathing criteria. That is the whole point of using client server tools. That is: get the database to find the data and send it back instead of sending all the data back (across the network!) and asking the user to find the data.

As to why SQL is slower than access in this regard I can only guess that previously the access database was on the same PC as the client.

The delay in scrolling your box can only be blamed on the client tool, but the poor thing has to manage 11500 records!

Hope that helps

Daniel

 

[Guest_imported]

Actually, right now I am running the client-application on the same PC as the SQL Server 7 resides on, so they don't have to go over any kind of network etc.

I'll come back with more information as soon as I get some things clarified.

-Bob

 

[FLUZZI]

Olá Bob Nachbar!

hummm...let's see...I think i have a similar problem one day. Here go's something that can help you:

1-Do not make a "SELECT * FROM MYTABLE", instead use something like this
" SELECT ID,DESCRIPTION FROM MYTABLE WHERE DESCRIPTION = 'TEST' "

If you are working whit delphi or similar (VB) i'm sure this could be a problem!

2- Take a look and see if your select clause have some column that is many times is the "where" stament. may be a create index is needed for this column!

If you continue to experince this problem give me more information so I can try to help you!

see a!!!
Sorry about my poor english. I'm a brazilian a I'm still learnig.

 

[foxdev]

Fluzzi's point about bringing back only the necessary columns will certainly help.

But I wonder if you are going through ODBC, and if so, are you instructing it to bring back only one row at a time? IOW, each time you scroll to the next person, another fetch is made to the database.

The alternate approach is to fetch all the records at once and cache them locally. A bit slower up front (though with 1150 records it shouldn't take more than 2 seconds), but your UI will seem much snappier as you scroll.

 

[Guest_imported]

Hey, thanks for your tips. I've had some talking with a few people that have been developing the original database-structure and applications.

I suppose they've had the same slow response-times with Access97 before, cause, what the application actually does is, it reads all the data from quite a few rows in the database into a grid in the program, and that's quite a lot of data. I've been wondering about the acutal use of this grid-thingy and the programmer-guy told me he doesn't want that grid there but it has been decided to be there as it somehow is easier for normal people to use the system that way.

I've talking about using some kind of criteria, like firstname or lastname or something like that, and send back only the affected rows, cause that would probably be a lot faster. But we'll see about that.

Yes, it's going through ODBC to an external application that's built in C++.

Well, I suppose this is some other kind of problem that someone else has to solve or let it be.

Thanks guys!

-Bob Nachbar

 

[dpellegrini]

Aside from software, make sure that your server is optimized for performance and RAM usage. In NT you can also change the priority of the application to processor utilization in the task bar properties. 196 meg is plenty, but if the server is not optimized, there will be performance issues no matter how much ram. Check the page file size and location too. Hope this helps.
Dom

 

[TeaAddictedGeek]

"I wonder how SQL Server 7 can be so slow!! I use an external application (that we made) which reads information from SQL Server 7 databases. What I mean is, one of my allications reads information about "users" and there is about 11500 rows in the table with 34 columns in each."

I think you just answered your own question

I would look into optimizing the table: making sure it has a primary key, add a table index for faster sorting. Overall, it definitely means a little redesign of your database, but it's not a difficult thing to do.

Good luck.
[sig]<p> <br><a href=mailto:aberman@thebiz.net>aberman@thebiz.net</a><br><a href= > </a><br>Database web programmer and developer, fueled by peach snapple and mochas.[/sig]